Databases practice questions.

A sports club replaces its collection of separate spreadsheet files with a relational database run by a database management system. Which task is now handled by the DBMS instead of by each application program?

• Controlling how records are stored on disk
• Deciding which discount a returning family receives
• Choosing the layout of the printed membership report
• Wording the error message shown to the clerk on screen

Answer: Controlling how records are stored on disk

The DBMS takes over storage, indexing and controlled access to the data, which is what gives every application one consistent view of it. Business rules such as discounts, and the presentation of reports or messages, stay in the application.

A club stores the same coach's mobile number in three separate tables. After the coach changes number only one copy is edited. What is the most direct consequence for the stored data?

• The tables can no longer be joined on a shared attribute
• The primary key of each table stops being unique
• The database loses referential integrity between the tables
• The copies disagree, so the data becomes inconsistent

Answer: The copies disagree, so the data becomes inconsistent

Redundant copies of one fact drift apart as soon as a single copy is edited, and the database then holds two different answers to the same question. No key relationship is broken, so referential integrity and key uniqueness are untouched.

A database administrator moves a database onto faster storage and changes the way its files are indexed. Every existing application program keeps working without being rewritten. Which property does this demonstrate?

• Logical data independence
• Physical data independence
• Referential integrity enforcement
• Automatic data verification

Answer: Physical data independence

Physical data independence means the internal storage level can be changed without altering the logical schema that programs are written against. Logical data independence is the stronger property, needed when the logical schema itself changes.

A running club records every race entry in one flat file, holding the member's name, address and the race details on the same line. Which problem is characteristic of that flat-file approach rather than of a relational database?

• Each row must be given a unique identifier
• Every field must be given a data type before use
• A member's address is retyped for every event
• The file cannot be sorted on more than one field

Answer: A member's address is retyped for every event

A flat file cannot hold a fact once and refer to it, so the member's address is duplicated for every entry and can easily be corrected in one line but not another. Requiring a data type before use is a feature of the relational approach rather than a flat-file weakness, and unique identifiers or multi-field sorting are possible either way.

When a new member joins, the clerk types the email address twice and the system compares the two entries before storing it. Which process has the club applied?

• Verification by double entry
• Validation by presence check
• Validation by format check
• Verification by a check digit routine

Answer: Verification by double entry

Verification checks that data was transferred correctly from its source, and double entry is one of its two usual forms alongside proofreading. Validation would only test whether the address looks reasonable, not whether it matches the source document.

Which item is held in the data dictionary of a database rather than in one of its data tables?

• The surname of the member who joined most recently
• The maximum length allowed for the surname field
• The number of members who joined during March
• The address of the club's main training venue

Answer: The maximum length allowed for the surname field

The data dictionary stores metadata: attribute names, data types, field sizes and constraints. Anything that is a stored value belongs in the tables; a figure such as the March total is not stored at all but derived by a query over them.