11.02 The Relational Database

A relational database resolves file-based problems by organizing data into structured relational tables (relations) governed by explicit rules.

Logical View of Relational Tables

Member Table Schema: Member(MemberID, MemberGivenName, MemberFamilyName, BandName*)

Band Table Schema: Band(BandName, AgentID)

11.03 & 11.04 Entity-Relationship (E-R) Modelling

Entity-Relationship modelling is a top-down database design method used to represent real-world entities and the logical connections between them.

Steps to Create an E-R Diagram

1. Identify Entities: Nouns in system requirements that have multiple instances (e.g., Booking, Band, Member, Venue).
2. Identify Relationships: Logically link entities (e.g., Band has Members, Booking is made at Venue).
3. Determine Cardinalities: Specify 1:1, 1:M, M:1, or M:M bounds and participation constraints.
4. Resolve M:M Relationships: Relational databases cannot directly execute Many-to-Many relationships. A Link Entity (Associative Table) must be created to split the M:M into two 1:M relationships.

Entity-Relationship (E-R) Cardinality Types

• One-to-One (1:1): Each record in Entity A relates to at most one record in Entity B .
• One-to-Many (1:M): Each record in Entity A relates to multiple records in Entity B .
• Many-to-One (M:1): Multiple records in Entity A relate to a single record in Entity B .
• Many-to-Many (M:N): Multiple records in Entity A relate to multiple records in Entity B (requires a junction table to resolve into two 1:M relationships) .

Logical Key Table Specification

11.05 Database Normalisation

Normalisation is a systematic design technique that organizes attributes into tables to eliminate redundant data and prevent insertion, update, and deletion anomalies.

Normal Forms Overview

11.06 Database Management System (DBMS) Architecture

A DBMS is software that acts as an interface between users/applications and the underlying stored database.

ANSI-SPARC Three-Level Architecture

DBMS Core Features & DBA Functions

Security & Management Features

• Access Control & Passwords: Restricts system access to authorized users via credentials .
• User Access Rights & Views: Defines read, write, append, or delete rights per user or group, presenting tailored logical schemas (views) .
• Backup Procedures & Audit Logs: Automated backup scheduling and continuous activity logs to trace modifications and maintain recovery paths .
• Data Encryption: Encodes stored and transmitted data to guard against interception .

DBMS Software Components

11.07 Structured Query Language (SQL)

SQL is the standard language used to define (DDL) and manipulate (DML) relational database structures.

Data Definition Language (DDL) Commands & Data Types

Data Manipulation Language (DML) Commands

• SELECT ... FROM ... WHERE: Interrogates and filters table data .
• ORDER BY / GROUP BY: Sorts output ascending/descending or groups rows for aggregation .
• INNER JOIN ... ON: Combines related records across multiple tables based on key matching .
• SUM(), COUNT(), AVG(): Aggregate functions operating on targeted dataset columns .
• INSERT INTO / UPDATE / DELETE FROM: Adds, updates, or removes records within tables .

1. Data Definition Language (DDL)

DDL establishes or alters database structures.

-- Create Database
CREATE DATABASE BandBooking;

-- Create Tables
CREATE TABLE Band (
    BandName VARCHAR(25),
    NumberOfMembers INTEGER
);

CREATE TABLE Booking (
    BookingID VARCHAR(8),
    BookingDate DATE,
    VenueName VARCHAR(25)
);

-- Primary and Foreign Key Constraints
ALTER TABLE Band ADD PRIMARY KEY (BandName);
ALTER TABLE Booking ADD PRIMARY KEY (BookingID);
ALTER TABLE Booking ADD FOREIGN KEY (VenueName) REFERENCES Venue(VenueName);

2. Data Manipulation Language (DML)

DML populates, alters, deletes, and retrieves operational record data.

-- Populate Data
INSERT INTO Band (BandName, NumberOfMembers) VALUES ('ComputerKidz', 5);
INSERT INTO Band (BandName, NumberOfMembers) VALUES ('ITWizz', 3);

-- Update Record
UPDATE Band SET NumberOfMembers = 6 WHERE BandName = 'ComputerKidz';

-- Delete Record (Observe foreign key dependency sequence)
DELETE FROM Band-Booking WHERE BandName = 'ITWizz';
DELETE FROM Band WHERE BandName = 'ITWizz';

-- Complex Multi-Table INNER JOIN Query
SELECT Booking.VenueName, Booking.BookingDate
FROM Booking
INNER JOIN Band-Booking ON Booking.BookingID = Band-Booking.BookingID
WHERE Band-Booking.BandName = 'ComputerKidz';

Interactive SQL Execution Engine

Test live SQL queries against the pre-populated Theatrical Agency database in real-time.

Query Execution Result

Chapter 11 Core Knowledge Summary