Flat file and relational databases, keys and indexing

Overview

Recall questions on flat file and relational databases, entities and attributes, primary, composite, foreign and secondary keys, choosing a primary key, and indexing and why not every field is indexed.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What is a flat file database?

   Model answer

   A flat file database stores all its data in a single table or file, with every field held together in one record structure and no links to any other table. It is simple to set up and adequate for a small, single-purpose set of data such as one class's contact details.

2. 2

   A school keeps every student's details, every course they take and the tutor for each course in one single table. Describe the problems this causes.

   Model answer

   Data redundancy: a student's name and address are repeated on every row for every course they take, wasting storage. Update anomalies: if the student moves house, every one of those rows must be changed and missing one leaves the database inconsistent. Insertion anomalies: a new course cannot be recorded until at least one student enrols on it, because there is nowhere to put it. Deletion anomalies: removing the last student on a course deletes the only record of that course and its tutor. The data is also harder to search and validate, and inconsistent duplicates make results unreliable.

3. 3

   What is a relational database?

   Model answer

   A relational database stores data in several linked tables, each holding data about a single entity such as students, courses or bookings. Each table has a primary key, and tables are linked by foreign keys, so data about one entity is stored once and referred to from elsewhere rather than repeated. Relationships between the tables let data from several of them be combined in a query.

4. 4

   Explain three advantages a relational database has over a flat file.

   Model answer

   Any three of: much less redundancy, because each fact is stored in one place only, saving storage; better consistency, because an update is made once and is immediately correct everywhere; no insertion or deletion anomalies, because independent entities live in their own tables; easier and more flexible querying, because data from several tables can be combined as needed; and better security and control, because access can be granted to individual tables rather than to everything at once.

5. 5

   In database terms, what is an entity and what is an attribute?

   Model answer

   An entity is a thing about which data is stored, such as a student, a book or an order; in a relational database each entity typically becomes a table, and each individual instance of it becomes a record. An attribute is a single item of data recorded about that entity, such as a student's date of birth, and becomes a field, that is a column, in the table.

6. 6

   What is a primary key and what must be true of it?

   Model answer

   A primary key is the field, or combination of fields, that uniquely identifies each record in a table. Its value must be unique across the table and must never be blank, so that every record can be picked out unambiguously. It is also the value that other tables refer to in order to link to this one.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.