4.10.1 Databases — AQA 7517

Why Databases?

A flat file stores all data in a single table (e.g. a spreadsheet). Problems with flat files:

Data redundancy: same data repeated in multiple rows (e.g. customer address repeated for every order)
Data inconsistency: redundant copies can get out of sync — one updated, another not
Update anomalies: changing a value in one place must be done in many rows
Insertion/deletion anomalies: can't add an order without a customer; deleting a row may lose important data

A relational database splits data into multiple related tables — eliminating redundancy and these anomalies.

Key Database Terminology

Term	Definition	Example
Entity	A real-world object or concept about which data is stored	Student, Course, Teacher
Attribute	A property or characteristic of an entity	StudentID, FirstName, DOB
Table (relation)	A structured set of rows and columns storing data about one entity	Student table
Field (column)	One attribute in a table	FirstName column
Record (row/tuple)	One instance of an entity — a complete row of data	One student's data
Primary key	Unique identifier for each record in a table — must be unique and not null	StudentID
Foreign key	A field in one table that is the primary key of another — creates a link	CourseID in Student table links to Course table
Composite key	A primary key made from two or more fields combined	StudentID + CourseID in an Enrolment table

Entity Relationship Diagrams (ERDs)

ERDs show the relationships between entities. Three types of relationship:

Relationship	Symbol	Example
One-to-one (1:1)	Each entity has at most one related entity	Person ↔ Passport (one person has one passport)
One-to-many (1:M)	One entity relates to many of another	Customer → Orders (one customer makes many orders)
Many-to-many (M:M)	Many of each entity can relate to many of the other	Student ↔ Course (student takes many courses; course has many students)

Resolving many-to-many: Create a link/junction table with a composite key of both primary keys. E.g. StudentID + CourseID in Enrolment table.

Database Management Systems (DBMS)

A DBMS is software that manages access to a database. Functions:

Data definition: creating and modifying table structures (DDL — Data Definition Language)
Data manipulation: querying and updating data (DML — SQL SELECT, INSERT, UPDATE, DELETE)
Security: user authentication and access control (different users see different data)
Integrity: enforces constraints (primary keys, foreign keys, data types)
Concurrency control: allows multiple users simultaneously — prevents conflicts
Backup and recovery: transaction logs allow database recovery after failure

Examples of DBMS: MySQL, PostgreSQL, Microsoft SQL Server, Oracle, SQLite.

Exam tip: AQA 7517 often asks you to explain the advantages of a relational database over a flat file. Key: no redundancy, no update/insertion/deletion anomalies. Know all terms: entity, attribute, record, field, primary key, foreign key, composite key. For ERDs: be able to draw entity boxes and label relationships (1:1, 1:M, M:M). Know how to resolve many-to-many using a link table.

▶

Click through the slides at your own pace. Use arrow keys or click to advance.

Click slide or press arrow keys to navigate

Worksheet — 4.10.1 Database Concepts

8 questions · instantly marked · AQA 7517 standard

Q1Explain the difference between a flat file database and a relational database. State two problems with flat files.[2]

✅ Mark scheme

Mark scheme

Flat file: single table storing all data [1]; relational database: multiple related tables eliminating redundancy [1]. Problems with flat files (any 2): data redundancy — same data repeated in many rows [1]; data inconsistency — redundant copies get out of sync [1]; update anomalies — must change same value in many places [1]; insertion/deletion anomalies [1].

Q2Define the following database terms: (a) entity, (b) attribute, (c) primary key, (d) foreign key.[3]

✅ Mark scheme

Mark scheme

Entity: a real-world object about which data is stored (e.g. Student, Course) [1]. Attribute: a property of an entity (e.g. StudentID, FirstName) [1]. Primary key: a field (or combination) that uniquely identifies each record in a table — must be unique and not null [1]. Foreign key: a field in one table that is the primary key of another table — creates a link/relationship between tables [1].

Q3A school has Students, Courses, and Teachers. Identify and explain the relationship between (a) Student and Course, and (b) Teacher and Course.[2]

✅ Mark scheme

Mark scheme

(a) Student-Course: many-to-many (M:M) — a student can enrol on many courses, and a course can have many students [1]; must be resolved with a link/junction table (e.g. Enrolment) with composite key of StudentID + CourseID [1]. (b) Teacher-Course: one-to-many (1:M) — one teacher teaches many courses, but each course has one teacher [1]; CourseID has TeacherID as a foreign key [1].

Q4Explain what a composite key is and give an example of when one would be used.[2]

✅ Mark scheme

Mark scheme

Composite key: a primary key formed by combining two or more fields [1]; no single field is unique on its own, but the combination is unique [1]. Example: in an Enrolment link table, StudentID + CourseID together uniquely identify which student is enrolled on which course [1].

Q5Describe four functions of a Database Management System (DBMS).[3]

✅ Mark scheme

Mark scheme

Any 4: Data definition (DDL) — creating/modifying table structures [1]; Data manipulation (DML/SQL) — querying, inserting, updating, deleting data [1]; Security — user authentication and access control [1]; Integrity — enforcing constraints (primary keys, data types, foreign keys) [1]; Concurrency control — allows multiple simultaneous users without conflicts [1]; Backup and recovery — transaction logs for recovery [1].

Q6Explain what a deletion anomaly is in a flat file database. Give an example.[2]

✅ Mark scheme

Mark scheme

Deletion anomaly: deleting a record from a flat file unintentionally destroys other data [1]. Example: if customer and order data are in one table, deleting a customer's last order also deletes the customer's details [1]; in a relational database this is avoided because customer and order data are in separate tables [1].

Q7In a library database, books and members are entities. Describe the relationship between them and explain how it should be implemented.[3]

✅ Mark scheme

Mark scheme

Relationship: many-to-many — a member can borrow many books; a book can be borrowed by many members (over time) [1]. Implementation: create a link/junction table (e.g. Loan) [1]; containing BookID (FK) and MemberID (FK) as a composite primary key [1]; can also include attributes like LoanDate, ReturnDate [1].

Q8Explain why a primary key must be unique and not null. What would happen if two records had the same primary key?[3]

✅ Mark scheme

Mark scheme

Unique: primary key uniquely identifies each record — two records having same PK would make it impossible to distinguish them [1]; queries and foreign key references would be ambiguous — unclear which record is meant [1]. Not null: a null (empty) primary key would be unidentifiable [1]; foreign keys in other tables reference the primary key — a null or duplicate would break referential integrity [1].

Topic Quiz

Question 1 of 15

You scored

out of 15

Card 1 of 9

Click to reveal definition

🎉

All cards reviewed!

Term	Definition

Why Databases?

Key Database Terminology

Entity Relationship Diagrams (ERDs)

Database Management Systems (DBMS)

Worksheet — 4.10.1 Database Concepts

Mini Test — Database Concepts