Flat File vs Relational
- Database: a systematically stored collection of data.
- Flat file: all data in one table. Data repeats, wastes storage, becomes inconsistent, hard to scale.
- Relational: data split into several linked tables. Each fact stored once.
- Normalization: organising data to minimise the risk of anomalies.
Anomalies
An anomaly is an issue caused by incorrect handling of data in a database.
| Anomaly | Problem | Example (vet flat file) |
|---|
| Insert | Can't add data without unrelated data. | A new vet can't be added until they treat a patient. |
| Delete | Deleting one thing removes data you need. | Deleting a vet also deletes the pet and owner in that row. |
| Update | Change missed in some rows, data becomes inconsistent. | Vet moves house, only some rows updated, two addresses exist. |
Features
| Term | Meaning | Example |
|---|
| Entity | A table. | OWNER, PET |
| Attribute | A characteristic of the data (column). | Pet_Name |
| Primary key | Unique value for each row. | Pet_ID |
| Foreign key | A primary key placed in another table to create a relationship. | Owner_ID inside PET |
| Composite key | Two or more whole attributes together form a unique key. | Patient_ID + Date |
| Concatenated key | Parts of attributes joined into one unique value. | First letters of name + surname + date of birth |
| Relationship | The link between two entities. | OWNER owns PET |
| OWNER: Owner_ID (PK) | Name |
|---|
| 393939 | Andrea |
| 5893021 | Amalia |
| PET: Pet_ID (PK) | Name | Owner_ID (FK) |
|---|
| H849 | Paolo | 393939 |
| C192 | Felix | 393939 |
| C059 | Bao | 5893021 |
Cardinality and Modality
Cardinality (how many):
- 1:1 one person, one passport.
- 1:N one vet, many treatments.
- M:N many students, many courses.
Modality (must it exist):
- Optional: actor and TV show. Some actors only do stage.
- Mandatory: receipt and item. A receipt needs at least one item.
Benefits and Limitations
| Benefits | Limitations |
|---|
| Reduced duplication and redundancy | Poor for big data scalability |
| Data consistency | Design complexity |
| Data integrity | Rigid schema |
| Easy retrieval with SQL | Inefficient for deep hierarchical data |
| Concurrency control (ACID) | Struggles with unstructured data |
| Reliable transaction processing | Object relational impedance mismatch (tables don't map neatly onto OOP objects) |
| Built in multiuser security | |
| Scales better than flat file | |
| Community support | |
Ninja Notes:
- Know all three anomalies with an example each.
- Composite = whole attributes combined. Concatenated = pieces glued into one value.
- Cardinality = how many. Modality = must it exist.
- Most benefits come from one root cause: less duplication.