CS2855 Databases Revision Notes

(Based on Lecture Slides & Past Papers: CS2855-22/23/24)

---

1. Entity-Relationship (E-R) Model

Key Concepts:

• Entities: Objects with independent existence (e.g., Customer, Account). Represented as rectangles.
• Relationships: Associations between entities (e.g., depositor links Customer and Account). Diamonds in diagrams.
• Attributes: Properties of entities/relationships (e.g., customer_id, balance). Ellipses connected to entities.
• Weak Entities: Depend on a parent entity (e.g., Payment depends on Loan). Use double rectangles and partial keys.
• Cardinality Constraints:
  • One-to-One (e.g., employee manages one branch).
  • One-to-Many (e.g., Customer opens multiple Accounts).
  • Many-to-Many (e.g., Student enrolls in multiple Courses).

Exam Tasks:

• Designing an ER Diagram: Convert a textual description into entities, relationships, and constraints (e.g., “Each customer can have multiple accounts; each account is owned by at least one customer”).
• Convert ER to Tables:
  • Strong Entities: Create a table with attributes (e.g., Customer(customer_id, name, city)).
  • Weak Entities: Include the parent’s primary key (e.g., Payment(loan_number, payment_number, amount) with loan_number referencing Loan).
  • Relationships: Use foreign keys (e.g., Depositor(customer_id, account_number)).

---

2. Relational Model

Key Components:

• Relation Schema: Structure (e.g., Account(account_number, balance)).
• Keys:
  • Super Key: Attribute set uniquely identifying tuples.
  • Candidate Key: Minimal super key (e.g., account_number for Account).
  • Primary Key: Chosen candidate key.
  • Foreign Key: Links to another table (e.g., customer_id in Depositor references Customer).

Exam Tasks:

• Translate ER Diagram: Identify primary/foreign keys and convert relationships (e.g., ISA hierarchies become separate tables or merged with parent).
• Validate Schemas: Check redundancy and key constraints.

---

3. Relational Algebra

Operations (for Querying):

• Select (σ): Filters rows (e.g., σ_{balance > 1000}(Account)).
• Project (π): Selects columns (e.g., π_{customer_name}(Customer)).
• Join (⨝): Combines tables (e.g., Customer ⨝ Depositor).
  • Natural Join: Eliminates duplicate columns.
• Union (∪)/Intersection (∩)/Difference (−): Set operations (e.g., Π_{name}(Borrower) ∪ Π_{name}(Depositor)).
• Division (÷): Finds tuples in one relation associated with all tuples in another.

Exam Application:

• Write expressions for queries (e.g., “Names of customers with both loans and accounts” → Π_{name}(Borrower) ∩ Π_{name}(Depositor)).

---

4. SQL

Key Topics:

• DDL:

  CREATE TABLE Account (
    account_number CHAR(10) PRIMARY KEY,
    balance NUMERIC(12,2),
    branch_name VARCHAR(20) FOREIGN KEY REFERENCES Branch
  );  

• DML:

  SELECT customer_name, SUM(balance) 
  FROM Depositor NATURAL JOIN Account 
  GROUP BY customer_id 
  HAVING COUNT(*) > 1;  

• Aggregations: SUM, AVG, COUNT, with GROUP BY and HAVING.
• JOINs: INNER JOIN, LEFT JOIN, etc.
• Constraints: PRIMARY KEY, UNIQUE, CHECK, ON DELETE CASCADE.

Exam Focus:

• Write queries for complex scenarios (e.g., “Find customers with total deposits over £10,000”).
• Security: Prevent SQL injection via PreparedStatement in JDBC.

---

5. Normalization

Steps to BCNF/3NF:

1. Identify Functional Dependencies (FDs):
   Example: account_number → balance, branch_name.
2. Check for Redundant FDs: Use Armstrong’s axioms (reflexivity, augmentation, transitivity).
3. Decompose Tables:
   • BCNF: Every non-trivial FD’s left side is a superkey.
     Algorithm: For FD X → Y, split into R1(X, Y) and R2(R - Y).
   • 3NF: Relaxes BCNF to preserve dependencies. A FD X → Y is allowed if Y is part of a candidate key.

Example (from normalisation-stuff.pdf):
Given R(A,B,C,D) with FDs A → B, B → D: Decompose into R1(A,B), R2(B,D), and R3(A,C) (BCNF).

Exam Tasks:

• Normalize a schema to BCNF/3NF, check for lossless join, and identify redundant attributes.

---

6. Transactions & ACID

ACID Properties:

• Atomicity: All-or-nothing execution (e.g., rollback on failure).
• Consistency: Validates integrity constraints post-transaction.
• Isolation: Concurrent transactions act serially. Use schedules (e.g., conflict serializability).
• Durability: Committed changes survive crashes.

Schedules:

• Conflict Serializability: Swap non-conflicting operations (e.g., two READ operations on the same data).
• Testing Serializability: Precedence graphs (no cycles → serializable).

Exam Focus:

• Explain “Durability” (e.g., committed data is stored permanently).
• Analyze a schedule for serializability (e.g., check cycles in precedence graph).

---

Exam Tips

• ER Diagrams: Label cardinalities clearly (e.g., (1,N) for one-to-many).
• SQL Queries: Use aliases for readability (e.g., FROM Account A).
• Normalization: Always check if decomposed tables preserve FDs and allow lossless joins.
• Transactions: Mention trade-offs (e.g., isolation levels vs. performance).

Past Paper Alerts:

• BCNF definition questions often require stating “all FDs’ left side must be a superkey”.
• ACID questions expect definitions and real-world examples (e.g., banking transactions).

By mastering these areas, you can tackle all exam question types effectively. Good luck!