Week 4

Learning Outcomes / Objectives

By the end of this week, students should be able to:

1. Describe the relational data model using formal terminology.

2. Convert an ER diagram into a relational schema.

3. Define and distinguish between different types of keys.

4. Explain and implement entity integrity and referential integrity constraints.

5. Write SQL DDL statements to create tables with constraints.

Key Type	Definition and Example
Superkey	Any set of attributes that uniquely identifies a tuple. E.g., {MatricNo}, {MatricNo, StudentName}, or {MatricNo, StudentName, Email} are all superkeys.
Candidate Key	A minimal superkey, no attribute can be removed. E.g., {MatricNo} and {Email} are both candidate keys for STUDENT (assuming email is unique).
Primary Key (PK)	The chosen candidate key used to identify tuples. Underlined in schema notation. E.g., MatricNo is the PK for STUDENT.
Alternate Key	A candidate key that was not chosen as the primary key. E.g., Email is an alternate key for STUDENT.
Composite Key	A primary key consisting of two or more attributes. E.g., (MatricNo, CourseCode) is the composite PK for ENROLMENT.
Foreign Key (FK)	An attribute (or set) in one table that references the primary key of another table. Creates a link between tables. E.g., DeptCode in STUDENT references DeptCode in DEPARTMENT.
Surrogate Key	An artificial key generated by the system (e.g., auto-increment ID). Used when no natural key exists or when natural keys are complex.

Teaching and Learning Methods to be Employed