Study Guide¶

Based on lecture content and past exam papers (2022–2026)

Table of Contents¶

1. Introduction to Databases
2. Database Architecture
3. Entity Relationship Modelling
4. Mapping to Logical Design
5. Relational Algebra
6. Relational Database Design & Normalisation
7. Database Constraints
8. Database Security & Access Control
9. GDPR
10. NoSQL Databases
11. Transactions & Concurrency Control
12. Exam Strategy & Past Paper Analysis
13. Quick-Reference Summary Tables

1. Introduction to Databases¶

Core Concepts¶

Data vs Information — Data is raw facts; information is processed, meaningful data.

Database — A persistent collection of related data supporting several different applications within an organisation. Organised to model aspects of reality in a way that supports processes requiring this information.

Metadata — Data about data. Adds context: data type, name, size, restrictions. Stored centrally in the catalog.

DBMS (Database Management System) — Software that simplifies storage and access to data. Supports:

• Definition (DDL) — defining database structure
• Manipulation (DML) — modifying stored data
• Querying (DQL) — retrieving information
• Control (DCL) — access/permissions

SQL Command Categories¶

Key Properties¶

• Data Independence — Logical (view changes without affecting organisation) and Physical (application view changes without affecting physical storage)
• Data Integrity — Consistency and accuracy. Threatened by redundancy.
• Advantages — Reduced redundancy, greater integrity, independence from applications, improved security, reduced costs
• Disadvantages — Training required, complex design, cost, loss of autonomy, inflexibility

Database Users¶

2. Database Architecture¶

Database System (DBS)¶

DBS = DBMS + DB (application data + metadata) + Application programs

Three-Level Architecture¶

DBMS Components¶

• DDL Compiler — processes schema definitions, stores in catalogue
• Catalogue — names/sizes of files, data types, storage details, mappings, constraints
• Stored Data Manager (SDM) — controls disk access, buffer management
• Query Compiler — parses/validates queries, optimises, generates executable code
• Precompiler — extracts DML from host language programs
• Run-time Database Processor — handles privileged commands, queries, transactions, concurrency control, backup/recovery

Data Dictionary vs System Catalogue¶

• System Catalogue — syntactic definition, accessed by DBMS
• Data Dictionary — semantic support, augmented catalogue, accessed by users/DBA
• Integrated — Data Dictionary is part of DBMS, fully active at run-time
• Independent — Free-standing, passive, generates DDL automatically

3. Entity Relationship Modelling¶

ER Model Basics¶

The ER model is an abstract, high-level conceptual representation using Entity Relationship Diagrams (ERDs). Describes data as entities, relationships, and attributes.

Entity Types vs Entity Sets¶

Attribute Types¶

ERD Notation¶

Relationships¶

• A relationship captures how entity types are related (verb linking nouns)
• Degree — number of entity types participating (binary = 2, ternary = 3)
• Relationship type — the named relationship
• Relationship set — collection of all instances
• Role — the part each entity type plays in a relationship

Cardinality Constraints¶

Participation Constraints¶

Relationship Attributes¶

Recursive Relationships¶

Same entity type participates more than once in a relationship (e.g., Employee supervises Employee).

4. Mapping to Logical Design¶

ER to Relational Mapping Rules¶

Example: Cinema Database¶

ER: THEATRE ──1── LOCALE ──N── SCREEN ──1── DISPLAY ──N── SCREENING ──1── SHOW ──M── MOVIE

Mapped:
THEATRE (theatre_id, name, street, town, county, phone_no, website)
SCREEN (number, screen_type, capacity, location, theatre_id [FK])
SCREENING (screening_id, time, date, movie_id [FK], screen_number [FK])
MOVIE (movie_id, title, synopsis, director, cert, running_time, release_day, release_month, release_year)
GENRE (movie_id [FK], genre [PK part])

5. Relational Algebra¶

Operations Overview¶

SELECT (σ) — Horizontal Partition¶

σ<sub>(condition)</sub>(Relation)

• Filters rows based on a Boolean condition
• Same degree as original relation
• Number of tuples ≤ original
• Commutative and cascading: σ₁(σ₂(R)) = σ₁ AND σ₂(R)

PROJECT (π) — Vertical Partition¶

π<sub>(attribute list)</sub>(Relation)

• Selects specific columns
• Automatically eliminates duplicates (key difference from SQL — use DISTINCT)
• Degree = number of attributes in the list

Set Operations¶

Requirement: Relations must be union compatible (same degree, same domains for corresponding attributes).

JOIN (⋈)¶

R ⋈<sub>(condition)</sub> S

• Combines tuples from two relations satisfying a join condition
• Result has n + m attributes
• Makes use of Foreign Keys and Referential Integrity

SQL Equivalents¶

6. Relational Database Design & Normalisation¶

Design Guidelines¶

Functional Dependency (FD)¶

X → Y means: for any two tuples with the same X values, they must have the same Y values.

• X = left-hand side (determinant)
• Y = right-hand side
• If X is a candidate key, then X → all attributes

Anomalies¶

Normal Forms¶

Normalisation Process¶

1. Evaluate each relation against 1NF criteria → decompose if needed
2. Evaluate against 2NF → decompose partial dependencies
3. Evaluate against 3NF → decompose transitive dependencies
4. Evaluate against BCNF → decompose non-superkey determinants

Boyce-Codd Rule¶

"Every determinant must be a candidate identifier." "All attributes should be dependent on the key, the whole key, and nothing but the key."

Superkey vs Candidate Key vs Primary Key¶

Denormalisation¶

In practice, fully normalised tables can slow performance. The correct process:

1. Design fully normalised tables (correctness)
2. Denormalise only where needed for performance

7. Database Constraints¶

Three Types of Integrity Constraints¶

Constraint Violations¶

Referential Integrity Actions¶

SQL Constraint Syntax¶

-- Primary Key
PRIMARY KEY (column)
-- or inline: column INT PRIMARY KEY

-- Composite Primary Key
PRIMARY KEY (col1, col2)

-- Foreign Key
FOREIGN KEY (col) REFERENCES OtherTable(col)
FOREIGN KEY (col) REFERENCES OtherTable(col) ON DELETE CASCADE

-- Unique
UNIQUE (column)

-- Not Null
column INT NOT NULL

-- Check
CHECK (column > 0 AND column < 100)
CHECK (date1 <= date2)

-- Assertion (cross-table)
CREATE ASSERTION name CHECK (condition)

-- Trigger
CREATE TRIGGER name BEFORE/AFTER event ON table
FOR EACH ROW BEGIN ... END

8. Database Security & Access Control¶

Integrity vs Security¶

Access Control Types¶

Privileges¶

SQL Access Control¶

-- Grant privileges
GRANT privilege ON relation TO user;
GRANT SELECT, UPDATE ON Employees TO user1;

-- Attribute-level (INSERT/UPDATE only)
GRANT UPDATE (Salary) ON Lords TO Henry;

-- With propagation
GRANT SELECT ON relation TO user WITH GRANT OPTION;

-- Revoke
REVOKE privilege ON relation FROM user;

Views for Fine-Grained Access¶

Views allow partial access:

• Restricted attributes (columns)
• Restricted rows (WHERE clause)

CREATE VIEW RestrictedView AS
SELECT Name, Age, Address
FROM Lords
WHERE Decoration = 'Victoria Cross';

GRANT SELECT ON RestrictedView TO Victoria WITH GRANT OPTION;

Privilege Propagation¶

• WITH GRANT OPTION — recipient can grant to others
• Danger: privileges can propagate without owner's knowledge
• Revocation: DBMS should cascade revocation, but not if overlapping grant paths exist

Role-Based Access Control¶

CREATE ROLE manager;
GRANT SELECT, UPDATE, DELETE ON Employees TO manager;
GRANT manager TO user1, user2;

9. GDPR¶

Key Definitions¶

Personal Data Includes¶

Names, identification numbers, location data, online identifiers (IP addresses), and factors specific to physical, physiological, genetic, mental, economic, cultural, or social identity.

Special Categories (Additional Protection)¶

Racial/ethnic origin, political opinions, religious/philosophical beliefs, trade union membership, genetic/biometric data, health data, sex life/sexual orientation.

Six GDPR Principles¶

1. Lawful, fairness & transparency — Processed lawfully, fairly, transparently
2. Purpose limitation — Collected for specified, explicit, legitimate purposes only
3. Data minimisation — Adequate, relevant, limited to what is necessary
4. Accuracy — Accurate and up to date; inaccurate data erased or rectified without delay
5. Storage limitation — Kept only as long as necessary
6. Integrity & confidentiality — Appropriate security

Lawful Bases for Processing (Article 6)¶

1. Consent
2. Contract performance
3. Legal obligation
4. Vital interests
5. Public interest
6. Legitimate interests

Data Subject Rights¶

Penalties¶

Up to €20 million or 4% of annual worldwide turnover (whichever is greater).

10. NoSQL Databases¶

Why NoSQL?¶

• Impedance mismatch — difference between relational model and in-memory data structures
• Scale out — cheaper horizontal scaling vs expensive vertical scaling
• 3 Vs of Big Data: Volume, Velocity, Variety

SQL vs NoSQL Paradigms¶

CAP Theorem¶

Only 2 of 3 can be guaranteed:

Four NoSQL Types¶

Distributed Computing Concepts¶

11. Transactions & Concurrency Control¶

Transaction¶

A logical unit of DB processing completed in its entirety.

Termination¶

ACID Properties¶

Concurrency Control Problems¶

Schedules¶

Serializability¶

Two operations conflict if: different transactions, same item, at least one is a write.

Locking Protocols¶

Two-Phase Locking (2PL)¶

Theorem: Any schedule from 2PL is conflict-serializable.

Deadlock¶

Circular wait where transactions wait forever for locks held by each other.

Starvation¶

Transaction delayed indefinitely. Solutions: age-based priority, timeouts, fair queuing, limit retries.

Timestamp Ordering¶

Lock-free protocol assigning unique timestamps. Each data item maintains read-timestamp and write-timestamp.

12. Exam Strategy & Past Paper Analysis¶

Exam Structure (Consistent Across Papers)¶

• 3 questions total — Question 1 is mandatory
• Answer any 2 from Questions 2, 3, 4
• Each question worth 25 marks
• 2-hour exam (2024: 09:30–11:30; 2023: 14:00–16:00)
• 2026 format: Part 1 (pen & paper, 50 marks) + Part 2 (MCQ on computer, 50 marks)

Recurring Question Types¶

ER Modelling (Questions 1 in 2022, 2023, 2026)¶

• Given a scenario, draw an ER diagram
• Include: entities, attributes, PKs, multi-valued attributes, weak entities, specialisation/generalisation, relationship attributes, cardinality, participation
• State assumptions clearly
• Mark allocation: ~8–30 marks depending on complexity

Relational Mapping (Questions in 2022, 2023, 2026)¶

• Map ER to relational schema
• Show functional dependencies, PKs, FKs
• Normalise to BCNF
• Mark allocation: ~6–20 marks

SQL Queries (Questions in 2022, 2023, 2026)¶

• INSERT statements
• SELECT with WHERE, JOIN, GROUP BY, HAVING
• UPDATE/DELETE operations
• Mark allocation: ~3–12 marks

Integrity Constraints (2024 Q2, 2023 Q2)¶

• Given a schema and data, evaluate operations
• Identify which constraints are violated
• Suggest enforcement methods (CASCADE, SET NULL, etc.)
• Mark allocation: ~5 marks per sub-question

Access Control / Privileges (2024 Q3, 2022 Q2, 2023 Q4)¶

• Write SQL GRANT/REVOKE statements
• Use views for fine-grained access
• Handle WITH GRANT OPTION
• Mark allocation: ~5 marks per sub-question

GDPR Scenario (2024 Q4, 2023 Q4, 2022 Q3)¶

• Apply GDPR principles to a scenario
• Identify data subject rights
• Identify roles (controller, processor)
• Discuss repercussions and actions
• Mark allocation: ~8–10 marks per sub-question

Transactions & Concurrency (2023 Q3, 2022 Q2)¶

• Explain ACID properties
• Identify concurrency problems (lost update, dirty read)
• Analyze schedules for serializability
• Mark allocation: ~8 marks per sub-question

Multiple Choice (2026 Sample)¶

• 10 questions, 5 marks each
• Topics: GRANT/REVOKE, serializability, NoSQL use cases, SQL queries, GDPR, CHECK constraints, 2PL deadlocks, Wait-Die/Wound-Wait

Mark Distribution Patterns¶

Study Priority (Based on Frequency)¶

1. ER Modelling + Mapping — appears in every exam's mandatory question
2. SQL Queries — appears in almost every exam
3. Integrity Constraints — consistent 20+ marks across papers
4. Access Control — appears in most papers
5. GDPR — appears in every paper (Q2/Q3/Q4)
6. Transactions/Concurrency — appears regularly
7. Normalisation — tested within mapping questions
8. NoSQL — appears as MCQ or short answer

13. Quick-Reference Summary Tables¶

Normalisation Checklist¶

Given a table T with PK and FDs:

1NF: Are all values atomic?
  NO → Create new table for multi-valued attribute
  YES → Proceed

2NF: Does every non-key attribute depend on the ENTIRE PK?
  NO (partial dep) → Decompose into new tables
  YES → Proceed

3NF: Is any non-key attribute transitively dependent on PK?
  NO → Proceed
  YES → Decompose using transitive dependency

BCNF: Is every determinant a superkey?
  NO → Decompose using non-superkey determinant
  YES → Table is in BCNF

ERD → Relational Mapping Decision Tree¶

Entity type → Create table with simple attributes
Composite attribute → Decompose into component columns
Multi-valued attribute → Create new table {FK, attribute}, composite PK
1:1 relationship → FK in either table (or merge if both total participation)
1:N relationship → FK from 1-side into N-side table
M:N relationship → Create new table {FK1, FK2}, composite PK
Recursive relationship → Self-referencing FK in same table
Relationship attribute → Migrate based on cardinality (see table in Section 4)

Concurrency Protocol Decision Table¶

SQL Constraint Reference¶

-- Table-level constraints
CREATE TABLE Employees (
    emp_id     INT PRIMARY KEY,
    ssn        CHAR(9) UNIQUE NOT NULL,
    salary     DECIMAL CHECK (salary > 0),
    dept_id    INT REFERENCES Departments(dept_id)
                ON DELETE SET NULL
                ON UPDATE CASCADE,
    CHECK (hire_date <= end_date)
);

-- Renamed constraints
ALTER TABLE Employees ADD CONSTRAINT no_negative_salary
    CHECK (salary > 0);

-- Assertions
CREATE ASSERTION max_salary_sum CHECK (
    (SELECT SUM(salary) FROM Employees) < 1000000
);

-- Trigger
CREATE TRIGGER salary_audit
AFTER UPDATE OF salary ON Employees
FOR EACH ROW
WHEN (NEW.salary < OLD.salary)
BEGIN
    INSERT INTO Salary_Log VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END;

This study guide was compiled from CSU 34041 lecture materials and past exam papers (2022, 2023, 2024, 2026 sample). Always cross-reference with the latest lecture slides and consult your lecturer for any discrepancies.