A3.2 · Book pp. 174–185
Database design
A good database is designed before it is built. Most of the skill lies in deciding what goes in which table.
What you need to know
Points A3.2.1 to A3.2.7 are for both SL and HL. Normalization is the big skill here, so practise it on paper.
A3.2.1 Database schema
A schema shows how data is organized and how the relationships are managed. A database management system (DBMS) creates, maintains and controls access to the database.
| Schema | Shows | Example |
|---|---|---|
| Conceptual | Entities, attributes and relationships, with no technical detail | Customers place orders |
| Logical | Tables, fields, data types, keys and constraints, for no specific DBMS | OrderDate: DATE |
| Physical | How one DBMS stores it, with indexes and partitions | INDEX on LastName |
Schemas improve organization, security (with permissions and views), integrity (with constraints) and scalability.
✏️ Book correction
The book’s sales schema puts one ProductID in Orders, yet says an order includes one or more products. That cannot work, since a foreign key holds one value. Orders and products are many-to-many, so add a linking table: OrderItem(OrderID*, ProductID*, Quantity).
A3.2.2 Entity-relationship diagrams
An ERD shows entities and their relationships. It helps people discuss, document and maintain the design.
- Cardinality is the maximum: one or many.
- Modality is the minimum: zero (optional) or one (mandatory).
| Symbol at the line’s end | Meaning |
|---|---|
| Two bars | One and only one |
| Circle and bar | Zero or one |
| Bar and crow’s foot | One or many |
| Circle and crow’s foot | Zero or many |
✏️ Book correction
The book’s club example says a student can join many clubs and a club has many students, but calls this one-to-many. It is many-to-many, so it needs a linking table such as Membership(StudentID*, ClubID*).
✍️ Practice
Draw an ERD for a clinic: patients, doctors and appointments. Each appointment has one patient and one doctor. A new patient may have no appointments yet.
Model answer
Patient (exactly one) to Appointment (zero or many). Doctor (exactly one) to Appointment (zero or many). Appointment is the linking entity between patients and doctors.
A3.2.3 Data types
| Type | Holds | Example use |
|---|---|---|
CHARACTER(n) | Fixed-length text | A code that is always 8 characters |
VARCHAR(n) | Variable-length text, up to n characters | Name, email |
INTEGER | Whole number | Quantity |
REAL | Number with a decimal part | Price |
DATE | YYYY-MM-DD | Date of birth |
TIME | HH:MM:SS | Departure time |
BOOLEAN | True or False | Has paid |
Types control which operations work, and how efficiently. If you store a price as text, the application must convert it before any calculation. Data of the wrong type is rejected on insertion, and using different types for the same attribute across systems causes errors.
A3.2.4 Constructing tables
Each table needs a primary key, sensible field names and suitable types. Where no single field is unique, you have three options:
- Use a composite key of two or more fields, which is usually best.
- Add a new ID field, called a surrogate key.
- Use a concatenated key, which joins values into one field. This breaks atomicity, so avoid it.
⚠️ Common mistake
A name like “Boris Brown” should usually be split into FirstName and LastName, so that each field is atomic.
A3.2.5 Normal forms
Normalization organizes data to reduce redundancy and improve integrity, so that each fact lives in one place.
A functional dependency means one attribute determines another: StudentID determines LastName.
| Form | Rule | Removes |
|---|---|---|
| 1NF | Has a primary key, with atomic values and no repeating groups | Lists in cells |
| 2NF | In 1NF, and every non-key field depends on the whole key | Partial dependencies |
| 3NF | In 2NF, and no non-key field depends on another non-key field | Transitive dependencies |
Every field depends on the key, the whole key, and nothing but the key.
A3.2.6 Normalizing to 3NF
✏️ Book corrections
- The book’s library example removes author names at the 2NF step. With a single-field key like BookID, no partial dependency is possible, so the table is already in 2NF. Author names depending on AuthorID is a transitive dependency, which belongs in the 3NF step.
- It describes partial dependency using a single StudentID. Partial dependency only exists with a composite key.
- Its CLUB table repeats ClubID 105 even though ClubID is the primary key, which cannot happen.
Check yourself
Sale(SaleID*, ProductID, ProductName, Price, CashierID, CashierName). Which fields move out for 3NF?
ProductName and Price move to Product(ProductID*, ProductName, Price). CashierName moves to Cashier(CashierID*, CashierName). Sale keeps SaleID, ProductID and CashierID.
A3.2.7 Denormalizing
Denormalization deliberately allows redundancy to speed up queries.
| Normalized | Denormalized | |
|---|---|---|
| Redundancy | Minimal | Deliberately allowed |
| Integrity | Easier to protect, with each fact in one place | Copies can drift out of step |
| Updates | Change one row | Change every copy |
| Reads | Often need several joins | Fewer joins, so faster |
| Storage | Less repeated data, but more tables | More, due to the copies |
| Schema | Many tables can be complex | Simpler to query, harder to maintain |
When it makes sense
- Read-intensive applications, where data is read far more than it is changed.
- Reporting and analytics, where complex reports join many tables.
- Data warehousing, where historical data is analysed but rarely changed.
To balance the two, analyse whether the system mainly reads or updates. Check that the speed gain outweighs the risk, partly denormalize if needed, and then validate data and monitor performance.
✏️ Book correction
The book lists “updating records may require changing data in different places” as a drawback of normalization. That problem belongs to denormalized data, which is why it appears in the table above on that side.
Quick quiz
Exam-style questions
These are written for this site in the style of Paper 1. Answer on paper first, then open the mark points.
-
Define the term “schema”. [1]
Mark points
- An architecture showing how data is organized and how the relationships between data are managed [1].
-
Distinguish between a conceptual schema and a logical schema. [2]
Mark points
- A conceptual schema shows entities and relationships at a high level, with no technical detail [1].
- A logical schema adds tables, data types, keys and constraints [1].
-
Identify two issues caused by data redundancy. [2]
Mark points
- Wasted storage space [1].
- Inconsistency, when one copy is updated and another is not [1].
-
State the requirements of 1NF, 2NF and 3NF. [3]
Mark points
- 1NF: atomic values, no repeating groups, a primary key [1].
- 2NF: in 1NF, with no partial dependencies [1].
- 3NF: in 2NF, with no transitive dependencies [1].
-
A table has Loan(LoanID*, BookID, BookTitle, MemberID, MemberName). Normalize it to 3NF. [4]
Mark points
- BookTitle depends on BookID, and MemberName on MemberID, which are transitive dependencies [1].
- Book(BookID*, BookTitle) [1].
- Member(MemberID*, MemberName) [1].
- Loan(LoanID*, BookID, MemberID), with foreign keys [1].
-
Evaluate denormalizing a data warehouse. [3]
Mark points
- Fewer joins make complex analytical queries faster [1].
- Historical data rarely changes, so updates are few [1].
- The cost is extra storage and careful loading to keep copies consistent, which is usually acceptable here [1].