Relational Database Concepts
1. Flat File vs. Relational Databases
A database is a structured collection of data. There are two primary ways to organize this data:
Flat File Database
Stores all data in a single table or file.
- Redundancy: Data is often repeated.
- Errors: High risk of data entry mistakes.
- Storage: Inefficient for large amounts of data.
Relational Database
Splits data into multiple tables linked by common fields.
- Integrity: Reduces data redundancy.
- Efficiency: Faster to search and update.
- Security: Easier to control access to specific tables.
2. Key Database Terminology
| Term | Definition |
|---|---|
| Table | The overall structure containing data about one specific "entity" (e.g., Customers). |
| Record (Row) | A collection of data about one item (e.g., One specific customer). |
| Field (Column) | A single category of data (e.g., "Surname" or "DateOfBirth"). |
| Primary Key | A unique field that uniquely identifies each record in a table. |
| Foreign Key | A primary key from one table used in another table to create a relationship. |
3. Database Data Types
Database systems use specific data types to ensure data is stored correctly:
- Text / Alphanumeric: Stores words, symbols, and numbers not used for calculations (e.g., Phone Numbers).
- Number (Integer/Decimal): Stores numeric values used in math.
- Date / Time: Stores dates in a specific format (e.g., DD/MM/YYYY).
- Boolean / Logical: Stores Yes/No or True/False values.
- Currency: Automatically formats numbers with a currency symbol.
4. Single Table Design Example
Below is a design for a Books table. The BookID is the Primary Key.
| BookID (PK) | Title | Author | Price | InStock |
|---|---|---|---|---|
| B001 | The Great Gatsby | F. Scott Fitzgerald | 12.99 | Yes |
| B002 | 1984 | George Orwell | 10.50 | No |
| B003 | The Hobbit | J.R.R. Tolkien | 15.00 | Yes |
⚠️ Exam Tip: When asked why a Primary Key is needed, always say: "To uniquely identify each record in a table and prevent duplicate data."