Database fundamentals practice questions.

A room booking table identifies each row by the combination of ROOMID and BOOKINGDATE, because neither attribute is unique on its own. What is that combination called?

• A composite primary key
• A pair of foreign keys
• A secondary index on the table
• A surrogate key for the table

Answer: A composite primary key

A primary key may be formed from more than one attribute provided the combination is unique and no smaller part of it would do. A surrogate key is the alternative design, in which an artificial identifier is added so that a single attribute can serve as the key.

A membership table is searched by surname hundreds of times a day, and new members are added to it every few minutes. The designer creates an index on surname. Which pair of effects does this have?

• Searches on surname become slower, and each new insertion becomes faster.
• Searches on surname become faster, and each new insertion becomes slower.
• Searches on surname become faster, and each new insertion becomes faster as well.
• Searches on surname are unchanged, and each new insertion becomes slower.

Answer: Searches on surname become faster, and each new insertion becomes slower.

An index is a second, ordered structure over the surname values, so a search can go straight to the matching entries instead of examining every record. That structure has to be maintained on every insertion, so the gain in reading is paid for in writing.

A database management system enforces referential integrity. An administrator tries to delete a course record while enrolment records still refer to that course, and the deletion is refused. Which single condition would allow the same deletion to succeed?

• The course record is given a composite primary key.
• The enrolment table is indexed on its course attribute.
• No enrolment record refers to that course any more.
• The course and enrolment tables are stored in one file.

Answer: No enrolment record refers to that course any more.

Referential integrity forbids a foreign key that points at nothing. While enrolment rows still carry that course's identifier, removing the course would leave them orphaned, so the deletion is refused; once those rows are deleted or repointed, it is allowed.

One attribute of a table is being examined. Which single observation shows that it is acting as a foreign key rather than as the primary key of that table?

• Every value in it is different and no row leaves it empty.
• Its values are whole numbers issued in order as new rows arrive.
• It is named first in the table definition and carries an index of its own.
• The same value occurs in many rows and names a row of another table.

Answer: The same value occurs in many rows and names a row of another table.

A primary key identifies the rows of its own table, so its values are unique and never empty. A foreign key is a copy of another table's primary key, so the same value may appear in many rows here while each value points at exactly one row over there.

A stock table stores a reorderLevel for each product. For several products the attribute is left empty, because no reorder level has been agreed. Which statement about those empty values is correct?

• A null records that no value is known, and it is not the same as zero.
• A null is held as the number zero, so the two of them can never be told apart.
• A null is forbidden in every attribute of every relational table.
• A null in reorderLevel breaks entity integrity for that record.

Answer: A null records that no value is known, and it is not the same as zero.

Null means that no value has been recorded. Zero is a value, and it would claim that the product is reordered only once the shelf is empty. Entity integrity forbids nulls in the primary key only, so a null in reorderLevel is permitted.

A club is deciding whether to keep its records in a set of separate spreadsheet files or in a relational database run by a database management system. Which single fact about the club's working practice settles the question in favour of the database?

• The club prints a summary of its records once each year.
• Several officers change the same details at the same time.
• The club's treasurer prefers to read data laid out as a grid.
• The club holds fewer than one thousand records in total.

Answer: Several officers change the same details at the same time.

A database management system controls access by several users at once and applies one set of rules to all of them, which separate files cannot do. Volume, printing habits and personal preference do not decide the question; shared editing does.