Chapter 8 – DataBases

1. Database Basics

1.1 Key Definitions

Term	Definition	Example
Database	Organized collection of structured data.	A holiday booking system storing customer and holiday details.
Table	A set of data organized in rows (records) and columns (fields).	CUSTOMER (CustomerID, FirstName, LastName)
Field (Attribute)	A single piece of data in a table.	FirstName in the CUSTOMER table.
Record (Tuple)	A single row in a table representing an entity.	(1, "John", "Doe") in the CUSTOMER table.
Primary Key (PK)	A unique identifier for each record in a table.	CustomerID in CUSTOMER.
Foreign Key (FK)	A field that links to a primary key in another table.	CustomerID in RENTAL links to CustomerID in CUSTOMER.

---

2. SQL (Structured Query Language)

2.1 Basic SQL Commands

Data Definition Language (DDL) – Creating/Modifying Tables

Command	Description	Example
CREATE DATABASE	Creates a new database.	CREATE DATABASE SHOPORDERS;
CREATE TABLE	Defines a new table.	CREATE TABLE CUSTOMER (CustomerID INT PRIMARY KEY, FirstName VARCHAR(50));
ALTER TABLE	Modifies an existing table.	ALTER TABLE DEVICE ADD COLUMN Returned BOOLEAN;
DROP TABLE	Deletes a table.	DROP TABLE CUSTOMER;

Data Manipulation Language (DML) – Querying/Modifying Data

Command	Description	Example
SELECT	Retrieves data from a table.	SELECT FirstName, LastName FROM STAFF;
INSERT	Adds new records.	INSERT INTO CAR VALUES ("123AA", "Tiger", "Lioness", 10500, "12BSTREET");
UPDATE	Modifies existing records.	UPDATE STAFF SET Salary = 50000 WHERE StaffID = "BC";
DELETE	Removes records.	DELETE FROM CUSTOMER WHERE CustomerID = 101;

Aggregate Functions & Filtering

Function	Description	Example
COUNT()	Counts records.	SELECT COUNT(*) FROM SCHEDULE WHERE Morning = TRUE;
SUM()	Sums values.	SELECT SUM(Mark) FROM STUDENT_TEST WHERE TestID = "A1";
AVG()	Calculates average.	SELECT AVG(Mark) FROM STUDENT_TEST WHERE TestID = "A7";
GROUP BY	Groups results.	SELECT Size, COUNT(BirdID) FROM BIRD_TYPE GROUP BY Size;

Joins (Combining Tables)

Join Type	Description	Example
INNER JOIN	Returns matching records.	SELECT STAFF.FirstName FROM STAFF INNER JOIN SCHEDULE ON STAFF.StaffID = SCHEDULE.StaffID;
LEFT JOIN	Returns all left table records + matches.	SELECT CUSTOMER.FirstName, RENTAL.MonthlyCost FROM CUSTOMER LEFT JOIN RENTAL ON CUSTOMER.CustomerID = RENTAL.CustomerID;

---

3. Normalization (1NF, 2NF, 3NF)

3.1 Purpose of Normalization

• Eliminates data redundancy (duplicate data).
• Ensures data integrity (no anomalies).
• Organizes data efficiently.

3.2 Stages of Normalization

Normal Form	Rule	Example Fix
1NF	No repeating groups, atomic values.	Split a single field like "Hobbies: Reading, Swimming" into separate rows.
2NF	Must be in 1NF + no partial dependencies (all non-key fields depend on the whole PK).	If (StudentID, CourseID, CourseName), move CourseName to a separate table since it depends only on CourseID.
3NF	Must be in 2NF + no transitive dependencies (non-key fields depend only on PK, not other non-key fields).	If (OrderID, CustomerID, CustomerAddress), move CustomerAddress to a CUSTOMER table since it depends on CustomerID.

3.3 Example: Normalizing a Booking System

Unnormalized Table:

BOOKING (CarRegistration, StartDate, EndDate, CarModel, CarColour, CustomerFirstName)

Normalized to 3NF:

CUSTOMER (CustomerID, FirstName, LastName, Email, Phone)  
CAR (CarRegistration, CarModel, CarColour)  
BOOKING (BookingID, CarRegistration, CustomerID, StartDate, EndDate)

• Why?
  • CarModel and CarColour depend only on CarRegistration (not the booking).
  • CustomerFirstName should be in a separate CUSTOMER table.

---

4. Entity-Relationship (E-R) Diagrams

4.1 Components

• Entity → A table (e.g., CUSTOMER, HOUSE).
• Attribute → A field (e.g., CustomerID, HouseNumber).
• Relationship → How tables connect (e.g., “A customer rents a house”).

4.2 Example E-R Diagram for HOUSE_RENTALS

CUSTOMER (CustomerID, FirstName, LastName, ...)  
    ↓ (1-to-Many)  
RENTAL (RentalID, CustomerID, HouseID, ...)  
    ↑ (Many-to-1)  
HOUSE (HouseID, HouseNumber, Road, ...)

---

5. Data Dictionary & DBMS Features

5.1 What is a Data Dictionary?

A metadata repository storing:

1. Table names (e.g., CUSTOMER, BOOKING).
2. Field names & data types (e.g., CustomerID INT).
3. Constraints (e.g., PRIMARY KEY, NOT NULL).

5.2 DBMS Tools

Feature	Purpose
Query Processor	Executes SQL queries (e.g., SELECT, INSERT).
Logical Schema	Defines database structure (tables, relationships).
Access Rights	Controls who can read/write data (e.g., GRANT SELECT ON CUSTOMER TO Manager;).

---

6. Common Exam Questions & How to Answer Them

Q1: “Explain why a relational database is better than a file-based system.”

✅ Answer:

• Avoids data duplication (normalization reduces redundancy).
• Supports complex queries (SQL allows JOIN, GROUP BY).
• Ensures data integrity (PK/FK constraints prevent orphaned records).
• Allows concurrent access (multiple users can safely edit data).

Q2: “Write SQL to find all staff working on 22/05/2020.”

✅ Answer:

SELECT STAFF.FirstName, STAFF.SecondName  
FROM STAFF INNER JOIN SCHEDULE ON STAFF.StaffID = SCHEDULE.StaffID  
WHERE SCHEDULE.WorkDate = '22/05/2020';

Q3: “Is this database in 3NF? Justify.”

✅ Answer:

1. Check for repeating groups (must be 1NF).
2. Check for partial dependencies (must be 2NF).
3. Check for transitive dependencies (must be 3NF).
   Example:

• If HOUSE_RENTALS has (RentalID, CustomerID, CustomerAddress), it violates 3NF because CustomerAddress depends on CustomerID, not the PK (RentalID).

---

Summary for Quick Revision

✔ SQL Basics: SELECT, INSERT, UPDATE, DELETE, JOIN.
✔ Normalization: 1NF → No repeating groups, 2NF → No partial dependencies, 3NF → No transitive dependencies.
✔ E-R Diagrams: Show relationships (1:1, 1:Many, Many:Many).
✔ Data Dictionary: Stores metadata (table/field definitions).