Databases and SQL

Databases and SQL — CIE A Level Computer Science 9618

1. Database Concepts

Database: An organised collection of related data managed by a Database Management System (DBMS).

DBMS advantages over flat-file systems:

• Data independence: Data structure can change without affecting applications
• Reduced data redundancy: Centralised storage avoids duplication
• Improved data integrity: Constraints enforced centrally
• Concurrent access: Multiple users simultaneously with ACID transactions
• Security: Access control at field/table level
• Backup and recovery: Centralised point for backups

Key terminology:

• Entity: A thing with data worth storing (e.g., Student, Course)
• Attribute: Property of an entity (e.g., StudentID, Name)
• Primary key: Attribute that uniquely identifies each record — must be unique and NOT NULL
• Foreign key: Attribute in one table that references the primary key of another — enforces referential integrity
• Candidate key: Any attribute(s) that could serve as primary key

2. Entity-Relationship (ER) Diagrams

ER diagrams model the relationships between entities before designing tables.

Relationship types:

• One-to-one (1:1): Each entity A relates to at most one B (e.g., Person has one Passport)
• One-to-many (1:M): One A relates to many B (e.g., one Teacher teaches many Students)
• Many-to-many (M:N): Many A relate to many B (e.g., Students take many Courses; Courses have many Students)

Resolving M:N: Create a **junction/link table** with foreign keys to both entities.

Example: University database

• Student(StudentID, Name, DateOfBirth)
• Course(CourseID, CourseName, Credits)
• Enrollment(StudentID [FK], CourseID [FK], Grade, EnrollDate) — resolves M:N

3. SQL: Data Querying (SELECT)

Basic SELECT:

~~~sql

SELECT Name, Age FROM Students WHERE Age > 18;

~~~

Sorting:

~~~sql

SELECT Name, CGPA FROM Students ORDER BY CGPA DESC;

~~~

Filtering with multiple conditions:

~~~sql

SELECT * FROM Students

WHERE City = 'Karachi' AND CGPA >= 3.5;

~~~

Pattern matching (LIKE):

~~~sql

SELECT Name FROM Students WHERE Name LIKE 'Ali%';

-- % = zero or more characters; _ = exactly one character

~~~

Aggregate functions:

~~~sql

SELECT COUNT(*) FROM Students;

SELECT AVG(CGPA), MAX(CGPA), MIN(CGPA) FROM Students;

SELECT Department, COUNT(*) AS StudentCount

FROM Students GROUP BY Department;

~~~

HAVING (filter after GROUP BY):

~~~sql

SELECT Department, AVG(CGPA)

FROM Students

GROUP BY Department

HAVING AVG(CGPA) > 3.0;

~~~

JOINs (combining tables):

~~~sql

-- INNER JOIN: only matching rows

SELECT Students.Name, Courses.CourseName, Enrollment.Grade

FROM Students

INNER JOIN Enrollment ON Students.StudentID = Enrollment.StudentID

INNER JOIN Courses ON Enrollment.CourseID = Courses.CourseID;

-- LEFT JOIN: all rows from left table, NULLs where no match

SELECT Students.Name, Enrollment.Grade

FROM Students

LEFT JOIN Enrollment ON Students.StudentID = Enrollment.StudentID;

~~~

4. SQL: Data Manipulation (DML)

~~~sql

-- INSERT

INSERT INTO Students (StudentID, Name, CGPA)

VALUES (1001, 'Ahmed Khan', 3.7);

-- UPDATE

UPDATE Students SET CGPA = 3.8 WHERE StudentID = 1001;

-- DELETE

DELETE FROM Students WHERE StudentID = 1001;

~~~

Warning: UPDATE/DELETE without WHERE clause affects ALL rows.

5. SQL: Data Definition (DDL)

~~~sql

-- CREATE TABLE

CREATE TABLE Students (

StudentID INTEGER PRIMARY KEY,

Name VARCHAR(50) NOT NULL,

CGPA REAL CHECK (CGPA >= 0.0 AND CGPA <= 4.0),

DepartmentID INTEGER,

FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)

);

-- ALTER TABLE

ALTER TABLE Students ADD Email VARCHAR(100);

-- DROP TABLE (permanent!)

DROP TABLE Students;

~~~

6. Normalisation

Purpose: Organise tables to reduce redundancy and avoid update anomalies.

First Normal Form (1NF):

• All attributes are atomic (no repeating groups or multi-valued attributes)
• Each row uniquely identifiable (primary key exists)

Second Normal Form (2NF):

• In 1NF AND no partial dependency (no non-key attribute depends on only part of a composite key)

Third Normal Form (3NF):

• In 2NF AND no transitive dependency (non-key attribute B depends on another non-key attribute A, which depends on the key)
• Rule: "Every non-key attribute must depend on the key, the whole key, and nothing but the key."

Example of transitive dependency (violates 3NF):

Table: Student(StudentID, Name, CourseID, CourseName)

• CourseName depends on CourseID (not StudentID directly) — transitive dependency
• Fix: split into Student(StudentID, Name, CourseID) and Course(CourseID, CourseName)

7. ACID Transactions

A transaction is a logical unit of work that must be completed atomically.

ACID properties:

• Atomicity: All operations succeed or all are rolled back — no partial completion
• Consistency: Database moves from one valid state to another — constraints maintained
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Committed transactions persist even after system failure

COMMIT: Makes all changes in a transaction permanent.

ROLLBACK: Reverts all changes in a transaction back to the start.

Example: Bank transfer of PKR 50,000:

1. Deduct from account A: A = A − 50,000
2. Add to account B: B = B + 50,000

If step 2 fails, ROLLBACK undoes step 1. Without atomicity, money vanishes.