N5CS: Database Implementation

Before studying the implementation of relational databases, you should already be familiar with the concept that information can be stored in what type of database?

• Hierarchical database

• Object-oriented database

• Flat-file database

• NoSQL database

Answer explanation

Before studying relational databases, you should know information can be stored in a flat-file database, making it the correct type of database to be familiar with.

Which of the following SQL operations are you expected to describe and implement for pre-populated relational databases with a maximum of two linked tables?

• SELECT, CREATE, JOIN, DELETE

• SELECT, INSERT, UPDATE, DELETE

• SELECT, ALTER, DROP, INSERT

• CREATE, INSERT, UPDATE, TRUNCATE

Answer explanation

The correct operations for pre-populated relational databases with two linked tables are SELECT, INSERT, UPDATE, DELETE.

When constructing queries, which of these SQL keywords are you expected to use to filter results based on specific conditions?

• FROM, HAVING, GROUP BY

• SELECT, JOIN, LIMIT

• FROM, WHERE, ORDER BY

• INSERT, UPDATE, DELETE

Answer explanation

When constructing queries, you are expected to use the SQL keywords FROM, WHERE, and ORDER BY to filter results based on specific conditions.

What type of join is used to select data from two tables based on a common field?

• Outer join

• Cross join

• Equi-join

• Natural join

Answer explanation

Equi-join is used to select data from two tables based on a common field, making it the correct choice for this scenario.

What is the primary key of the hairdresser table in the given database schema?

• firstname

• contactnumber

• hairdresserid

• salon

Answer explanation

The primary key of the hairdresser table in the given database schema is hairdresserid, which uniquely identifies each record in the table.

What is the foreign key in the client table that references the hairdresser table?

• clientid

• clientfirstname

• hairdresserid

• phonenumber

Answer explanation

The foreign key in the client table that references the hairdresser table is 'hairdresserid'.

If the row for Huda Quhshi is removed from the hairdresser table and referential integrity is enforces, what impact will it have on the client table?

• There will be no impact on the client table.

• A new hairdresser will be automatically assigned to the clients.

• All records with the related foreign key will be deleted

• The contactnumber for clients will be removed.

Answer explanation

All records with the related foreign key will be deleted as a result of enforcing referential integrity when the row for Huda Quhshi is removed from the hairdresser table.

Which of the following best defines referential integrity in the context of a relational database?

• It is the uniqueness of the primary key in any given table.

• It is the relationship between two tables that checks that a foreign key in one table is a primary key in another table.

• It is the relationship between two tables that allows for duplicate foreign keys.

• It is the process of creating new tables from existing ones.

Answer explanation

The best definition of referential integrity in a relational database is the relationship between two tables that checks that a foreign key in one table is a primary key in another table.

What does enforcing referential integrity ensure in a database?

• It ensures that all primary keys have unique values.

• It ensures that there cannot be foreign key values which do not have a corresponding primary key value in the linked table.

• It ensures that all foreign keys have unique values.

• It ensures that all tables must have the same number of rows.

Answer explanation

It ensures that there cannot be foreign key values which do not have a corresponding primary key value in the linked table.

What is the consequence of deleting a row in the 'hairdresser' table that has a primary key value referenced in the 'client' table if cascade delete is not enforced?

• It will update the corresponding rows in the 'client' table.

• It will create inconsistent rows in the 'client' table.

• It will delete the 'client' table entirely.

• It will prevent new rows from being added to the 'hairdresser' table.

Answer explanation

It will create inconsistent rows in the 'client' table.