A-level Computer Science Year 12 · Topic 15
Databases
Databases at A-level covers how to design, query and protect large collections of data.
We will use keys, entity relationship diagrams and indexing, and normalise data to third normal form.
Then we write S Q L to query, define and change data, keep data consistent with transactions and locking, and exchange data using X M L and J S O N.
- 6 subtopics
- 151 questions
- Free: 15.1 notes and 10 questions
In this topic
- 15.1Relational databases: flat files, keys, entity-relationship diagrams and indexingFree below · 29 questions
- 15.2Normalisation to third normal formOpen in Full Marks · 21 questions
- 15.3SQL queries: SELECT, WHERE, ORDER BY and JOINOpen in Full Marks · 24 questions
- 15.4SQL data definition and manipulation: CREATE, INSERT, UPDATE and DELETEOpen in Full Marks · 22 questions
- 15.5Referential integrity, transaction processing, ACID and record lockingOpen in Full Marks · 30 questions
- 15.6Capturing, selecting, managing and exchanging data: XML and JSONOpen in Full Marks · 25 questions
15.1 · Free notes
Relational databases: flat files, keys, entity-relationship diagrams and indexing
Key points
- A flat file stores all data in a single table with one record per row. Repeated data causes data redundancy and inconsistency, and gives insertion, deletion and update anomalies.
- A relational database splits data into linked tables (relations). Each table holds one entity, each column is an attribute (field) and each row is a record (tuple).
- A primary key uniquely identifies each record in a table. It must be unique and never null, e.g. a system-generated StudentID.
- A composite key combines two or more attributes to give uniqueness, e.g. (StudentID, CourseID) in an enrolment table.
- A foreign key is an attribute in one table that is the primary key of another table. It creates the link between the two tables.
- Referential integrity means every foreign key value must match an existing primary key. This prevents orphan records, so deletes or updates that would break it are blocked or cascaded.
- A secondary key is an attribute that is indexed to speed up searching and sorting, e.g. Surname. It does not have to be unique.
- Relationships are one-to-one, one-to-many or many-to-many. The foreign key goes in the 'many' table, and a many-to-many relationship is resolved with a link table holding a composite key.
- Entity-relationship (ER) diagrams show entities as rectangles joined by relationship lines, and a crow's foot marks the 'many' end. In exams, name the entities, relationships and keys.
- An index is a separate ordered structure on an attribute, so records can be found without scanning the whole table. It speeds up searches but uses extra storage and slows inserts, updates and deletes.
- Compared with a flat file, a relational design gives less redundancy, better data integrity, easier updates and flexible querying with SQL.
In more detail
- Flat file problems: repeating a customer's address in every order wastes space and risks inconsistent copies.
- Worked keys: OrderID is the primary key of Orders; CustomerID in Orders is a foreign key; (StudentID, CourseID) is a composite key; an indexed Surname field is a secondary key.
- Worked ERD: Customer one-to-many Order; Order many-to-many Product, resolved by an OrderLine link table.
- Worked index trade-off: an index on Surname speeds up searches but makes inserts and updates slightly slower.
Practice
Relational databases: flat files, keys, entity-relationship diagrams and indexing: 10 questions
Pick an answer to check it, or open the answer for the explanation.
1. Which is a main disadvantage of storing data in a single flat file?
- Only one user can ever read the file
- Records cannot be searched
- Data is duplicated, risking inconsistency
- Every field needs a foreign key
Show answer
C: Data is duplicated, risking inconsistency. Repeated data (redundancy) must be changed in many places, so copies can disagree (inconsistency). Flat files can be searched and have no foreign keys.
2. Which property must a primary key have?
- It can repeat if other fields differ
- It is always a number
- It uniquely identifies each record and is never null
- It is made of exactly two fields
Show answer
C: It uniquely identifies each record and is never null. A primary key must identify exactly one record, so it must be unique and not null. It can be text and can be one field or several.
3. In Orders(OrderID, CustomerID, OrderDate), CustomerID is the primary key of the Customer table. What is CustomerID in Orders?
- Primary key
- Secondary key
- Foreign key
- Composite key
Show answer
C: Foreign key. An attribute that is the primary key of another table and is used to link the tables is a foreign key.
4. Students take many courses and each course has many students. How should this be implemented?
- Add CourseID to Student as a foreign key
- Store all courses in one Student field
- Use a link table holding StudentID and CourseID
- Make StudentID the primary key of Course
Show answer
C: Use a link table holding StudentID and CourseID. A many-to-many relationship is resolved with a link table. Its composite key is the two foreign keys, giving two one-to-many relationships.
5. Referential integrity is enforced. A user tries to delete a customer who still has rows in Orders. What should happen?
- It is blocked, or cascaded, so no orphan orders exist
- The CustomerID in Orders is reset to 0
- Orders keep a blank CustomerID
- The delete always succeeds and orders remain
Show answer
A: It is blocked, or cascaded, so no orphan orders exist. Referential integrity means every foreign key value must match an existing primary key, so orphan records are not allowed.
6. Which statement about indexing is correct?
- It speeds up both searching and inserting records
- It speeds up searches but needs extra storage and slows inserts
- It makes duplicate records impossible
- It removes the need for a primary key
Show answer
B: It speeds up searches but needs extra storage and slows inserts. An index is an ordered structure on an attribute. Searching is faster, but it uses space and must be updated on every insert, update or delete.
7. Enrolment(StudentID, CourseID, Grade) records one grade per student per course. Which is the best primary key?
- Grade
- CourseID
- StudentID and CourseID together
- StudentID
Show answer
C: StudentID and CourseID together. Neither ID alone is unique, as students take many courses and courses have many students. The pair is unique, so a composite key is needed.
8. Which attribute is the best primary key for a Student table?
- Date of birth
- A system-generated StudentID
- Surname
- Postcode
Show answer
B: A system-generated StudentID. A primary key must be unique and stable. Surnames, birthdays and postcodes can be shared or change, whereas an assigned ID is unique.
9. In an ER diagram, a line has a single bar at Customer and a crow's foot at Order. What does it show?
- A many-to-many relationship
- A one-to-one relationship
- One order has many customers
- One customer has many orders
Show answer
D: One customer has many orders. The crow's foot marks the 'many' end, so each customer can have many orders but each order belongs to one customer.
10. One teacher teaches many classes, but each class has one teacher. Where does the foreign key go?
- Both tables hold both IDs
- TeacherID in the Class table
- No foreign key is needed
- ClassID in the Teacher table
Show answer
B: TeacherID in the Class table. In a one-to-many relationship the foreign key goes in the 'many' table, so each Class row stores the TeacherID of its one teacher.
Keep going in Full Marks
Full Marks has 29 questions on Relational databases: flat files, keys, entity-relationship diagrams and indexing (19 more than here), set out in practice levels. You also get notes and 3D models for all 6 subtopics of Databases, plus games, timed exams and past papers.
On your exam board
- OCRTopic 1.3.2: Databases
- CambridgeTopic 8: Databases
Not in the AQA specification.