Hamish Burke | 2025-05-15

Related to: #databases

Exam

Lecture Schedule

Database Basics

What is a database
What is a DBMS and what are its tasks
Program - Data Independence
Three Schema Architecture

Relational Data Model

Tuple
Relation
Relational Schema
Relational Database
Relational Database Schema
Relational Schema Key
Attribute Constraint
Referential Integrity Constraint

Entity-Relationship (ER) Data Model

Regular entity type
Relationship type
Cluster
Mapping EER to the Relational Data Model
Mapping an entity type
Mapping a relationship type
Mapping with clusters

Structured Query Language (SQL)

DDL, DML
VDL

Relational Algebra

Basic Operations (select, project, join)
Set Theoretic Operations (union, intersect, difference)

Query optimisation

Heuristic Optimisation
- Reordering of algebraic operations
- First execute unary than binary operations
Cost based query optimisation
- Start from heuristic optimisation tree
- Calculate cost of each successive operation

Update Anomalies

Insertion, Deletion, and Modification Anomaly

Lossless Join

Two Relations – A Key is in the Intersection
More Than Two Relations – A Relation Schema Contains A Key of the Universal Relation Schema

Functional Dependencies

Armstrong Inference Rules
Attribute Closure
Finding a Minimal Cover of a Set of Functional Dependencies
A Relation Schema Set of Keys Is A Consequence of Functional Dependencies
- A Key Finding Algorithm
- Additional key Finding Algorithm

Normal Form Based On Functional Dependencies

Definitions: 2NF, 3NF, BCNF

Normalisation Algorithms

Synthesis Algorithm: 3NF
BCNF Decomposition Algorithm: BCNF
Dependency preservation

Transaction Processing

Dirty Read,
Unrepeatable Read,
Lost update
Phantom Record
ACID

Concurrency Control Mechanism

Shareable and Exclusive Locks
Basic 2PL (acquire all locks before releasing any)
Strict 2-phase locking and Conservative 2-phase lock
Transaction isolation Levels
Deadlock
Deadlock Prevention Protocols

Exam Prep

Exam Prep