COMP3311 Week 8 Monday Lecture Notes

Key Topics Covered

• Assignment 2 Database and Scripts
• Normalisation
• Relation Decomposition
• Schema (Re)Design
• Boyce-Codd Normal Form (BCNF)
• BCNF Decomposition
• Third Normal Form (3NF)
• Database Design Methodology
• Relational Algebra

Assignment 2 Details

Database

• A simplified version of the UNSW student database.
• Small enrolments, some data inconsistencies.
• Focus on Engineering degrees.
• Some NOT NULL constraints are omitted.
• Database design assumes no changes from 2019 to 2023.

Scripts

• Python/Psycopg2 scripts for various tasks:
  • q1.py: Track international student proportions over time.
  • q2.py: Track course satisfaction over time.
  • q3.py: Print program/stream requirements.
  • q4.py: Produce transcript including WAM.
  • q5.py: Progression check for current program.
  • q6.py: Progression check for proposed program.
• Q5 and Q6 will be merged.

Normalisation

• Aims to reduce redundancy in relational schemas using normal forms.
• Heavily reliant on functional dependencies.

Relation Decomposition

• Technique to remove redundancy by splitting relations.
• Requires several decompositions to achieve desired normal form.

Schema (Re)Design

• An example with BankLoans schema is discussed.
• Attempts to improve design by decomposition.
• Highlights issues with lossy decompositions.

Boyce-Codd Normal Form (BCNF)

• A schema is BCNF if every functional dependency X → Y meets certain conditions.
• Ensures no update anomalies and lossless join decomposition.
• BCNF does not guarantee dependency preservation.

BCNF Decomposition

• Algorithm for converting schema to BCNF is non-deterministic.

Exercises on Normalisation

• Practical decompositions and schema transformations using BCNF and 3NF.

Third Normal Form (3NF)

• Similar to BCNF but allows Y to be a single attribute from a key.
• Guarantees lossless join decomposition and dependency preservation.

Database Design Methodology

• Involves identifying attributes, mapping ER design, applying BCNF/3NF.

Relational Algebra

• Describes the basic operations and notations used.
• Fundamental to DBMS implementation, akin to machine code.
• Includes core operations (select, project, join) and set operations.

Notation and Operations

• Custom notation is used for ease of reproduction.
• Operations like selection, projection, join, rename are covered.

Exercises in Relational Algebra

• Examples provided for rename, projection, selection, union, intersection, and difference operations in relational algebra and SQL.