A3.2 Database Design
Schemas, ERDs, data types, tables, normalization to 3NF, denormalization.
A3.2.1 Database Schema
A schema is the blueprint of a database: entities, attributes, relationships and constraints. It contains no actual data. There are three levels, each adding more detail. All three are drawn as ERDs.
| Schema | Shows | Used by |
|---|---|---|
| Conceptual (least detail) | Entities and relationships only. | Users, administrators, developers |
| Logical | + attributes, primary keys, foreign keys. | Administrators, developers |
| Physical (most detail) | + data types, files, indexes, storage. | Developers, to build the database |
1. Conceptual schema
2. Logical schema
3. Physical schema
A3.2.2 Entity Relationship Diagrams (ERDs)
An ERD is a universally understood diagram showing the database schema.
- Entity: a noun, drawn as a rectangle. Student, Car, Product.
- Attribute: data stored about the entity. Make, Model, Fuel_Type.
- Relationship: a verb linking entities. Student studies Course. Read in one direction.
- Every relationship shows cardinality (1:1, 1:N, M:N) and modality (optional or mandatory).
Three notations, same meaning
All three show: one band records many songs.
Crow's foot symbols
The symbol next to an entity says how many of that entity take part. The inner mark is the minimum (modality), the outer mark is the maximum (cardinality).
How to build an ERD
- Decide the level: conceptual, logical or physical.
- Find the entities: the nouns in the scenario.
- Find the relationships: the verbs linking them.
- Add cardinality: ask "one X has how many Y?" then the reverse.
- Add modality: ask "must every X have a Y?"
- For logical or physical level: add attributes, keys, and data types.
Worked example: "A doctor treats several patients per day"
- Entities: DOCTOR, PATIENT.
- Relationship: doctor treats patient.
- Cardinality: one doctor treats many patients, and one patient can see many doctors. That is M:N.
- Fix the M:N: relational databases can't store M:N directly. Add a link entity in the middle (APPOINTMENT). M:N becomes two 1:N relationships.
- Modality: each appointment must have exactly one doctor and one patient (mandatory). A doctor or patient may have zero appointments (optional).
- Keys: the link entity holds both primary keys as foreign keys.
Read it as: one DOCTOR has zero or more APPOINTMENTS. Each APPOINTMENT has exactly one PATIENT.
A3.2.3 Data Types (MySQL)
| Type | Stores | Example |
|---|---|---|
| String | ||
| CHAR(size) | Fixed length text, 0 to 255 | Student ID "AB1234" |
| VARCHAR(size) | Variable length text, up to 65,535 | Name |
| TEXT / MEDIUMTEXT / LONGTEXT | Long text: ~64 KB / ~16 MB / ~4 GB | Description, book backup |
| ENUM(a, b, c) | Exactly one value from a fixed list | Title: Mr, Ms, Dr |
| SET(a, b, c) | Zero or more values from a list | Interests: Sport, Art |
| Numeric | ||
| BIT(size) | 1 to 64 bits | 110 |
| BOOL | 0 = false, non zero = true | Is_Vegan |
| INT | Whole number | Quantity 392 |
| FLOAT(p) | Decimal number | Height 1.92 |
| Date / time | ||
| DATE | YYYY-MM-DD | 2010-10-29 |
| DATETIME | YYYY-MM-DD hh:mm:ss | 2010-10-29 21:03:45 |
| TIMESTAMP | Current date and time added automatically | Record created |
| TIME / YEAR | hh:mm:ss / YYYY | 13:04:39 / 2015 |
- fsp (fractional seconds precision) stores up to 6 decimal places of a second. Needed for race timing, fair ticket queues, coordinating distributed databases.
- Wrong type = problems. Numbers stored as text can't be calculated. Dates stored as text can't be queried as dates. An ENUM blocks any value not on its list.
- Phone numbers and IDs with leading zeros are text, not INT.
A3.2.4 Constructing Tables
Tables are linked with keys so related data is treated as related. This protects data integrity.
TRIP(Destination, Flight, Description, Teacher_ID*)
TEACHER(Teacher_ID, First_Name, Family_Name, Subjects)
Underlined = primary key. * = foreign key.
- Cascade delete: delete a row with a primary key and every row that references it through a foreign key is deleted too. No orphan data left behind, which prevents delete anomalies.
- Cascade update: change a primary key and every matching foreign key updates automatically. Prevents update anomalies.
A3.2.5 Normalization Explained
Normalization splits one messy table into several clean tables so that every fact is stored once. This removes insert, delete and update anomalies.
The three words you need
| Term | Meaning | Example |
|---|---|---|
| Functional dependency | A determines B. If you know A, you know B. | Patient_ID → Patient_Name |
| Partial dependency | An attribute depends on only part of a composite key. | Key is (Patient_ID, Date). Patient_Name only needs Patient_ID. |
| Transitive dependency | An attribute depends on another non key attribute, not on the key. | Price depends on Treatment, not on the appointment. |
| Form | Rule | What you remove |
|---|---|---|
| UNF | Not normalized. Has repeating groups. | Nothing yet |
| 1NF | Atomic values (one value per cell), no repeating groups, every table has a primary key. | Repeating groups |
| 2NF | In 1NF and no partial dependencies. | Attributes that need only part of a composite key |
| 3NF | In 2NF and no transitive dependencies. | Attributes that depend on a non key attribute |
2NF only matters when a table has a composite key. A table with a single column key is automatically in 2NF if it is in 1NF.
A3.2.6 Worked Example: Dentist
Start data. One dentist, several patients treated.
| Dentist ID | Dentist Name | Practice | Patient ID | Patient Name | Address | Treatment | Treatment Detail | Date | Price |
|---|---|---|---|---|---|---|---|---|---|
| 192 | Luis Reorder | Heaton | 190 | Wesley Kyle | 12 Sunny Close | Filling | Filling with technical assistance | 22/06/25 | 150 |
| 138 | Ana Torres | 87 Lake Lane | Check Up | Check up and cleaning | 22/06/25 | 80 | |||
| 193 | Henry Stewart | Gosfield | 190 | Wesley Kyle | 12 Sunny Close | Filling | Filling with technical assistance | 25/06/25 | 150 |
| 148 | Camille Manon | 98 Alma Grove | Crown | Crown including X ray | 25/06/25 | 300 |
Step 0: UNF
Check every cell holds one value (atomic). Pick a starting key. Find the repeating group: the columns you fill in again for every new row under the same dentist.
Patient_ID, Patient_Name, Address, Treatment, Treatment_Detail, Date, Price)
Problem: the italic group repeats for each dentist.
Step 1: UNF to 1NF
How: move the repeating group into its own table. Find a key for it. Copy the original key across as a foreign key.
One appointment is identified by patient + date, so the new key is the composite (Patient_ID, Date).
APPOINTMENT(Patient_ID, Date, Patient_Name, Address, Treatment, Treatment_Detail, Price, Dentist_ID*)
Problem left: Patient_Name and Address only need Patient_ID, not the Date. That is a partial dependency. Wesley's address is still stored on every visit.
Step 2: 1NF to 2NF
How: for each attribute ask "does this need the whole key?" Move the ones that need only part of it into a new table keyed by that part.
PATIENT(Patient_ID, Patient_Name, Address)
APPOINTMENT(Patient_ID*, Date, Treatment, Treatment_Detail, Price, Dentist_ID*)
Problem left: Treatment_Detail and Price depend on Treatment, which is not a key. That is a transitive dependency. A filling's price is still stored on every filling appointment, so changing the price means editing many rows.
Step 3: 2NF to 3NF
How: for each attribute ask "does this depend on another non key column?" Move it into a new table keyed by that column. That column stays behind as a foreign key.
PATIENT(Patient_ID, Patient_Name, Address)
TREATMENT(Treatment, Treatment_Detail, Price)
APPOINTMENT(Patient_ID*, Date, Treatment*, Dentist_ID*)
Done: every attribute depends on the key, the whole key and nothing but the key. Each fact lives in one place.
Quick test for any table
- Any cell with more than one value, or a group that repeats? Not 1NF.
- Composite key, and some column needs only one part of it? Not 2NF.
- Any column you could work out from another non key column? Not 3NF.
An online shop stores every sale in one table. Each customer has several purchases.
Item_ID, Item_Description, Quantity, Cost, Date, Artist_ID, Artist_Name, Artist_Email)
1NF: move the repeating group out. A sale is identified by customer + item + date.
SALE(Customer_ID*, Item_ID, Date, Quantity, Item_Description, Cost, Artist_ID, Artist_Name, Artist_Email)
2NF: Item_Description, Cost and the artist only need Item_ID (partial dependency). Move them out.
SALE(Customer_ID*, Item_ID*, Date, Quantity)
ITEM(Item_ID, Item_Description, Cost, Artist_ID, Artist_Name, Artist_Email)
3NF: Artist_Name and Artist_Email depend on Artist_ID, not on Item_ID (transitive dependency). Move them out.
SALE(Customer_ID*, Item_ID*, Date, Quantity)
ITEM(Item_ID, Item_Description, Cost, Artist_ID*)
ARTIST(Artist_ID, Artist_Name, Artist_Email)
A3.2.7 Denormalization
Denormalization means deliberately adding some redundancy back into a normalized database, usually by merging tables or storing copied or pre calculated values.
| Why do it | Cost |
|---|---|
| Faster reads: fewer JOINs needed per query. | Duplicated data comes back, so anomalies can return. |
| Simpler queries for reports and dashboards. | Updates are slower and must change several places. |
| Pre calculated totals avoid repeating heavy calculations. | More storage used. |
| Suits read heavy systems such as data warehouses and analytics. | Harder to keep data consistent and maintain. |
Rule of thumb: normalize for systems with lots of writes and updates (banking, bookings). Denormalize selectively when read speed matters more than avoiding duplication.
- Conceptual = entities and relationships. Logical = add attributes and keys. Physical = add data types and storage.
- 1NF removes repeating groups. 2NF removes partial dependencies. 3NF removes transitive dependencies. Learn this sentence.
- In exams, show each stage separately and say which dependency you removed and why.
- After a split, the column that defined the new table stays behind as a foreign key.
- Denormalization questions are "evaluate": give speed benefits and anomaly risks, then conclude.