INFS1200 — Full Course Notes

Week 1

Conceptual Database Design and the ER Model

Module 1, part 1. See er-diagram-notation and relationship-constraints for the reference symbol legend and cardinality/participation definitions this lecture introduces.

Today’s outline

  • Conceptual database design
  • Entities and relationships
  • Relationship constraints

Conceptual database design

  • Step 1: identify the “Universe of Discourse” (UoD) — the database models some “mini-world”/UoD, not everything in the world.
  • Step 2: convert the UoD into a data model that a database can capture.
  • A model is never perfect — three categories of phenomena around any conceptual schema: common phenomena (captured in the model, e.g. “every employee works for a department”), phenomena not captured (e.g. “some employees go out for dinner on Fridays” — irrelevant, left out), and phenomena not true in the world (e.g. an over-general assumption like “every secretary can type”).

The ER model

The Entity-Relationship (ER) model provides a graphical representation of data entities — it helps define a project’s scope/requirements for clients and businesses. An entity is a physical or conceptual object with data (attributes) associated with it; the same real-world entity can have different attributes recorded depending on the system’s requirements (e.g. an R&D organisation records an employee’s Degree/Field of Study, a retail organisation records Sales Experience instead).

An entity type provides the format (name + attributes) for recording a particular kind of entity — drawn as a rectangle, with attribute ovals attached. The entity set is the collection of all entities of one entity type in the database at a point in time (maps to a table).

Attributes

  • Key attribute: every entity type has at least one — its value is unique for each entity in the entity set (name underlined). Multiple keys are possible, and a key must hold for every possible extension of the entity type.
  • Composite vs simple: a composite attribute (e.g. Name) can be split into simple attributes with independent meaning (FirstName, MiddleName, LastName).
  • Composite key: a combination of simple attributes that together must be unique (e.g. a car’s Registration = State + Number, alongside a separate simple key VehicleID) — an entity type can have several candidate keys like this.
  • Single-valued vs multivalued: a multivalued attribute (double-lined oval) can hold more than one value per entity (e.g. Degree — one person can hold multiple degrees).
  • Stored vs derived: a derived attribute (dashed oval) is computed from another stored attribute (e.g. Age derived from BirthDate).
  • Value sets: the set of legal values for an attribute (e.g. employeeAge: integers 21-65) — never shown on the diagram itself. A null value represents an inapplicable, unknown, or missing value.

In-class exercise — Student entity

Every student has a unique id, name (title/first/middle initial/last), email, address (number/street/suburb/postcode), and one or more phone numbers. Draw an ER diagram.

STUDENT entity type with key attribute ID; composite attribute Name (→ Title, Fname, Initial, Lname); simple attribute Email; composite attribute Address (→ Number, Sname, Suburb, Pcode); and multivalued attribute Phone (double-lined oval, since a student can have more than one phone number).

flowchart TD
    ID(["ID (key)"]) --- STUDENT[STUDENT]
    Name(["Name"]) --- STUDENT
    Title(["Title"]) --- Name
    Fname(["Fname"]) --- Name
    Initial(["Initial"]) --- Name
    Lname(["Lname"]) --- Name
    Email(["Email"]) --- STUDENT
    Address(["Address"]) --- STUDENT
    Number(["Number"]) --- Address
    Sname(["Sname"]) --- Address
    Suburb(["Suburb"]) --- Address
    Pcode(["Pcode"]) --- Address
    Phone(["Phone (multi)"]) --- STUDENT

Relationships

  • A relationship is an association among two or more entities (e.g. “Paris Lane works on the project FileZilla”). A relationship type defines it — drawn as a diamond connected to its entity types, and may have its own descriptive/key attributes.
  • Relationship degree = number of participating entity types: binary (2, e.g. EMPLOYEE WORKSFOR DEPARTMENT), ternary (3, e.g. SUPPLIER SUPPLIES PART via a PROJECT), or n-ary (3+) in general.
  • Roles: each participating entity type plays a named role in the relationship, explaining what the relationship means (e.g. DEPARTMENT is the Employer, EMPLOYEE is the Worker in WORKSON).
  • Recursive relationships: the same entity type can participate more than once in one relationship type, under different roles (e.g. EMPLOYEE MANAGES EMPLOYEE, with Manager/Subordinate roles).
  • The relationship set is the collection of all relationship instances of one relationship type at a point in time.

Question — Entities and Relationships (courses/students)

Store info about students, courses, courses taken, and grades. Courses have a number/department/title (numbers assigned per-department, so different departments may reuse a number); students have a unique student ID and name; students enrol in courses and receive a grade.

COURSES (composite key Key = Dept + Number, plus Title) ↔︎ ENROLMENTS (attribute Grade) ↔︎ STUDENTS (key ID, Name) — an M:N relationship, since a student can enrol in many courses and a course has many enrolled students.

flowchart LR
    ID(["ID (key)"]) --- STUDENTS[STUDENTS]
    NameS(["Name"]) --- STUDENTS
    STUDENTS ---|"N"| ENROLMENTS{ENROLMENTS}
    ENROLMENTS ---|"M"| COURSES[COURSES]
    Grade(["Grade"]) --- ENROLMENTS
    CourseKey(["Key (key)"]) --- COURSES
    Dept(["Dept"]) --- CourseKey
    Number(["Number"]) --- CourseKey
    Title(["Title"]) --- COURSES

Relationship constraints

See relationship-constraints for the cardinality ratio (1:1/1:N/M:N) and participation constraint (total/partial) definitions.

Worked example — EMPLOYEE/DEPARTMENT:

  • WORKSFOR: N:1 — “each department can have any number of employees, but an employee can work for at most one department”, and EMPLOYEE has total participation (“every employee must work for a department”).
  • MANAGES: 1:1 — “each department can have at most one manager and each employee can manage at most one department”; DEPARTMENT has total participation (“every department must have a manager”) while EMPLOYEE’s participation is partial (“every employee can manage 0 or more departments”).
  • WORKSON (EMPLOYEE/PROJECT): M:N — “each employee can work on any number of projects, and each project can have any number of employees working on it”.

flowchart LR
    EMPLOYEE1[EMPLOYEE] ===|"N"| WORKSFOR{WORKSFOR}
    WORKSFOR ---|"1"| DEPARTMENT[DEPARTMENT]
    EMPLOYEE1 ---|"1"| MANAGES{MANAGES}
    MANAGES ===|"1"| DEPARTMENT


flowchart LR
    EMPLOYEE2[EMPLOYEE] ---|"M"| WORKSON{WORKSON}
    WORKSON ---|"N"| PROJECT[PROJECT]

Exercise — ABC Banks

Model a bank with branches (unique name, city, budget, rating), customers (name + phone, address), accounts and loans (unique number, created/ maintained by a single branch), where an account is assigned to ≥1 customers and a loan is assigned to a single customer.

BRANCH (Name, City, Budget, Rating) —1:N OPENS→ ACCOUNT (ID, Balance); BRANCH —1:N GIVES→ LOAN (ID, Rate, Balance); CUSTOMER (ID, Name, Phone, Address) —M:N OWNS→ ACCOUNT; CUSTOMER —1:N TAKES→ LOAN.

flowchart TD
    BRANCH[BRANCH] ---|"1"| OPENS{OPENS}
    OPENS ---|"N"| ACCOUNT[ACCOUNT]
    BRANCH ---|"1"| GIVES{GIVES}
    GIVES ---|"N"| LOAN[LOAN]
    CUSTOMER[CUSTOMER] ---|"M"| OWNS{OWNS}
    OWNS ---|"N"| ACCOUNT
    CUSTOMER ---|"1"| TAKES{TAKES}
    TAKES ---|"N"| LOAN

    NameBr(["Name (key)"]) --- BRANCH
    City(["City"]) --- BRANCH
    Budget(["Budget"]) --- BRANCH
    Rating(["Rating"]) --- BRANCH

    IDa(["ID (key)"]) --- ACCOUNT
    BalanceA(["Balance"]) --- ACCOUNT

    IDl(["ID (key)"]) --- LOAN
    Rate(["Rate"]) --- LOAN
    BalanceL(["Balance"]) --- LOAN

    IDc(["ID (key)"]) --- CUSTOMER
    NameC(["Name"]) --- CUSTOMER
    Phone(["Phone"]) --- CUSTOMER
    AddressC(["Address"]) --- CUSTOMER

Practical 1: Course Tools

Introduces the two tools used throughout the course: diagrams.net (draw.io) for building ER diagrams, and Gradescope for submitting assessment. This practical itself is not assessable — it’s onboarding practice before Assignment 1.

Task 1 — Recreate the “Always a Gamer” diagram in draw.io

UoD: a fictional gaming company’s GAME entity (Name, Year, Language, Description, Console) specialises two separate, disjoint (d) ways:

  • How it’s played: SINGLEPLAYER (Characters) or MULTIPLAYER (MinPlayers, MaxPlayers).
  • Whether it can be won: WINNABLE, or NON-WIN (Purpose).

NON-WIN games also have a recursive SEQUEL relationship to other NON-WIN games (roles PrequelTo/SequelTo, 1:1).

flowchart TD
    GAME[GAME] --- d1(("d"))
    d1 --- SINGLEPLAYER[[SINGLEPLAYER]]
    d1 --- MULTIPLAYER[[MULTIPLAYER]]
    GAME --- d2(("d"))
    d2 --- WINNABLE[[WINNABLE]]
    d2 --- NONWIN[["NON-WIN"]]
    NONWIN ---|"1 PrequelTo"| SEQUEL{SEQUEL}
    SEQUEL ---|"1 SequelTo"| NONWIN

    Name(["Name"]) --- GAME
    Year(["Year"]) --- GAME
    Language(["Language"]) --- GAME
    Description(["Description"]) --- GAME
    Console(["Console"]) --- GAME
    Characters(["Characters"]) --- SINGLEPLAYER
    MinPlayers(["MinPlayers"]) --- MULTIPLAYER
    MaxPlayers(["MaxPlayers"]) --- MULTIPLAYER
    Purpose(["Purpose"]) --- NONWIN

Recreate this EER diagram yourself in draw.io — see the course’s linked draw.io usage guide for the mechanics (shapes, connecting entities to a relationship diamond, exporting).

Task 2 — Using the ER diagram

Question 1 — Identifying entities

Which entity type is the superclass that every other entity type in this diagram specialises from (directly or indirectly)?

GAME is the superclass. Its two disjoint specialisations are SINGLEPLAYER/MULTIPLAYER (one d-circle) and WINNABLE/NON-WIN (a separate d-circle) — four subclasses total, all ultimately specialising GAME.

Task 2.2 — Exporting the diagram

Export the finished diagram from draw.io as an image and insert it into the practical’s Gradescope submission template — no fixed “right answer” here, just confirms the export step works.

Task 3 — Submitting via Gradescope

Submit via the Gradescope link on the course Blackboard site. Not assessable — purely to confirm your Gradescope account is set up correctly ahead of Assignment 1.

Week 2

Weak Entities, the EER Model and Design Choices

Module 1, part 2 — continues on from 2026-02-23-conceptual-database-design-and-the-er-model. See er-diagram-notation and relationship-constraints for the reference symbol legend and weak-entity definition this lecture uses.

Today’s outline

  • Weak entities
  • The Enhanced ER (EER) diagram — superclasses/subclasses
  • Design choices for conceptual modelling

Weak entities

See relationship-constraints#weak-entities for the definition (owner entity, partial key, identifying relationship, total participation). Example: EMPLOYEE —1:N INSURES→ DEPENDANT, where DEPENDANT’s partial key is Pname and its full key is (EMPLOYEE.SSN, DEPENDANT.Pname).

flowchart LR
    EMPLOYEE[EMPLOYEE] ---|"1"| INSURES{INSURES}
    INSURES ===|"N"| DEPENDANT[[DEPENDANT]]
    SSN(["SSN (key)"]) --- EMPLOYEE
    Pname(["Pname (partial key)"]) --- DEPENDANT

Question — Weak Entity (hotel/room)

ROOM (partial key Number, attribute Type) is a weak entity, N:1 IN→ HOTEL (key Address, attribute Service). Which is true: (A) two hotels can share an address, (B) no two rooms share a number, (C) no two hotels have rooms with the same number, (D) no two same-numbered rooms share a type, (E) none of the above?

(E) None of the above. ROOM’s real key is the composite (HOTEL.Address, ROOM.Number) — a room number is only guaranteed unique within its owning hotel, so (B) and (C) are both false; (A) is false since Address is HOTEL’s key attribute (must be unique per hotel); (D) doesn’t follow from any stated constraint.

flowchart LR
    ROOM[[ROOM]] ===|"N"| IN{IN}
    IN ---|"1"| HOTEL[HOTEL]
    Number(["Number (partial key)"]) --- ROOM
    Type(["Type"]) --- ROOM
    Address(["Address (key)"]) --- HOTEL
    Service(["Service"]) --- HOTEL

The EER model — superclasses and subclasses

An entity type is called a class in the EER model. Entities in the same class share the same attributes; a class can be a superclass or subclass — a subclass inherits its superclass’s attributes/ relationships, and can also have its own specific attributes/relationships. Every entity in a subclass is a member of its superclass(es).

Motivating example: a supermarket ITEM (superclass: ProductName, Price) vs a FOOD item (subclass: adds ExpiryDate) — FOOD is just an extension of a regular ITEM.

  • Specialisation: define subclasses of an entity type based on a more specific distinguishing characteristic (top-down).
  • Generalisation: abstract away differences between several existing entity types to identify a common superclass (bottom-up).
  • Subclasses are a specialisation of the superclass; the superclass is a generalisation of the subclasses.

Constraints on specialisation

See er-diagram-notation#subclasses-superclasses-eer for the notation.

  • Total vs partial: whether every superclass instance must belong to some subclass, or not.
  • Disjoint (d) vs overlapping (o): whether an instance can belong to at most one subclass, or more than one at once.

Example: EMPLOYEE splits disjointly (d) into SECRETARY/ENGINEER (each with their own attribute — TypingSpeed/EngineerDetails), and separately (still under EMPLOYEE) an overlapping (o) split into DRIVER/PASSPORT-holder isn’t required to be disjoint or total depending on the UoD.

flowchart TD
    EMPLOYEE[EMPLOYEE] --- d(("d"))
    d --- SECRETARY[[SECRETARY]]
    d --- ENGINEER[[ENGINEER]]
    TypingSpeed(["TypingSpeed"]) --- SECRETARY
    EngineerDetails(["EngineerDetails"]) --- ENGINEER

Exercise — University database (UofU)

Students: unique student id, name, address, phone, registered major; visiting students stay for a year. Courses: identified by department + course#, with title and credits. Course sections: unique section# per course/semester, taught by exactly one instructor (no idle instructors); instructors have a unique name and a higher degree recorded. Students enrol in a section and get a mark. A course may have other courses as prerequisites.

STUDENT (key SID, Name, Address, Phone, Major) —M:N ENROLS_IN→ SECTION (attribute Mark); STUDENT has a subclass VISITING_STUDENT (HomeInst, StartDate). SECTION is a weak entity (partial key Sec#, owner COURSE, composite key Sec# + Semester), —N:1 OFFERING→ COURSE (key Dept + Course#, Title, Credits); SECTION —N:1 TEACHES← INSTRUCTOR (key Name, Degree), with total participation on the INSTRUCTOR side (“no idle instructors”). COURSE has a recursive M:N PREREQUISITE relationship with itself (roles higher/requires).

flowchart TD
    STUDENT[STUDENT] ---|"M"| ENROLS_IN{ENROLS_IN}
    ENROLS_IN ---|"N"| SECTION[[SECTION]]
    SECTION ===|"N"| OFFERING{OFFERING}
    OFFERING ---|"1"| COURSE[COURSE]
    SECTION ---|"N"| TEACHES{TEACHES}
    TEACHES ===|"1"| INSTRUCTOR[INSTRUCTOR]

    SID(["SID (key)"]) --- STUDENT
    SecNum(["Sec# (partial key)"]) --- SECTION
    CourseKey(["Dept+Course# (key)"]) --- COURSE
    NameI(["Name (key)"]) --- INSTRUCTOR

Design choices for ER conceptual design

Modelling the same UoD can involve genuine design choices:

  • Equivalent choices — two ER representations that produce the same resulting database.
  • Inequivalent choices — the UoD is ambiguous, and different mappings meet the spec but enforce different constraints; note your assumption under the diagram when this happens.

Common choice points:

  • Attribute vs. (weak) entity type — e.g. an interview’s Details (Department, Date) can be modelled as attributes of the APPLIES relationship, or pulled out into its own weak INTERVIEW entity.
  • Attribute vs. subclass — e.g. EMPLOYEE.Type as a plain attribute, vs. splitting into SECRETARY/ENGINEER subclasses with their own extra attributes.
  • Binary vs. n-ary relationships — e.g. SUPPLIER/PROJECT/PART as three binary relationships (CANSUPPLY, USES, SUPPLIES) vs. one ternary SUPPLIES relationship (with a quantity attribute) linking all three at once.
  • Subclass relationship vs. superclass relationship — e.g. giving WORKSON (a PROJECT relationship) directly to the EMPLOYEE superclass vs. only to specific subclasses like ENGINEER.

Applied Class 1: Intro to DBMS and Basic ERD

Practice for 2026-02-23-conceptual-database-design-and-the-er-model. See er-diagram-notation and relationship-constraints for reference.

Section A — DBMS architecture concepts

Question 1 — Three-schema architecture matching

Match each concept to its description: (1) describes the physical storage structure, (2) describes the whole database’s structure for a community of users, (3) change the conceptual schema without changing external views/ applications, (4) modify the physical schema without changing the logical schema, (5) provides access to particular parts of the database to users.

External Level → 5, Logical data independence → 3, Conceptual Level → 2, Physical data independence → 4, Internal Level → 1.

Question 2 — Typical DBMS functions

Select all options that are a typical function of a DBMS: (1) providing secure access, (2) enforcing integrity constraints, (3) normalising the relational schema, (4) handling concurrent access, (5) recommending changes to database design.

Enter the correct option numbers in ascending order, comma-separated (e.g. 1,2,3):

1, 2, 4 — providing secure access, enforcing integrity constraints, and handling concurrent access are all typical DBMS functions. Normalising a schema and recommending design changes are design-time human decisions, not something the DBMS itself does.

Section B — Entities and attributes

Question 1 — RESTAURANT: CHEF entity

Each chef’s name (first/middle/last) is unique; DoB and age are recorded, along with any specialisations.

CHEF entity, composite key Name (→ FirstName, MiddleName, LastName, underlined as the key), simple attribute DoB, derived attribute Age (dashed oval, computed from DoB), and multivalued attribute Specialisations (double-lined oval).

flowchart TD
    Name(["Name (key)"]) --- CHEF[CHEF]
    FirstName(["FirstName"]) --- Name
    MiddleName(["MiddleName"]) --- Name
    LastName(["LastName"]) --- Name
    DoB(["DoB"]) --- CHEF
    Age(["Age (derived)"]) --- CHEF
    Specialisations(["Specialisations (multi)"]) --- CHEF

Section C — Basic relationships

Question 1 — Olympics database

Athletes: unique athleteID, Name, Age, Sex, Country. Events: unique eventID, Name, Category. Venues: unique ID, Name, Address. Each athlete participates in ≥1 event with a recorded Placement; each event is held at exactly one venue; some venues are backups and never host an event.

ATHLETE (key ID) —M:N PARTICIPATES (attribute Placement)→ EVENT (key ID), total participation on the ATHLETE side. EVENT —N:1 HOSTS→ VENUE (key ID), with partial participation on the VENUE side (“some venues… never used to host an event”).

flowchart LR
    ATHLETE[ATHLETE] ===|"M"| PARTICIPATES{PARTICIPATES}
    PARTICIPATES ---|"N"| EVENT[EVENT]
    EVENT ---|"N"| HOSTS{HOSTS}
    HOSTS ---|"1"| VENUE[VENUE]
    IDa(["ID (key)"]) --- ATHLETE
    IDe(["ID (key)"]) --- EVENT
    IDv(["ID (key)"]) --- VENUE
    Placement(["Placement"]) --- PARTICIPATES

Section D — Analysis & application

Uses the same Olympics UoD/diagram as Section C.

Question 1 — Correct statements

Select the correct statements: (1) two different venues may share an address, (2) EVENT has a ternary relationship, (3) ATHLETE is an entity while Age is an attribute, (4) an ATHLETE cannot participate at the same EVENT more than once, (5) all VENUEs must host an EVENT.

Enter the correct option numbers in ascending order, comma-separated:

1, 3, 4. (1) Address isn’t a key of VENUE here, so two venues could share one. (2) EVENT’s relationships (PARTICIPATES, HOSTS) are both binary, not ternary. (3) correct by definition. (4) the diagram shows a plain (non-recursive, non-attributed-for-repeats) M:N relationship — an athlete/event pair only appears once. (5) false — some venues are backups and never host an event (partial participation).

Question 2 — Total participation, semantically

What does total participation on both sides of PARTICIPATES mean? Does it align with the UoD?

Total participation from both ATHLETE and EVENT means every athlete participates in at least one event, and every event has at least one participating athlete — consistent with the UoD (“each athlete participates in at least one event”).

Question 3 — Redraw VENUE with two candidate keys

Redraw VENUE so it can be identified by either its ID, or the combination of its Name and Address.

VENUE gets two separate key ovals: ID (underlined), and a composite key Key (also underlined) that itself splits into Name + Address — two independent candidate keys for the one entity type.

flowchart TD
    ID(["ID (key)"]) --- VENUE[VENUE]
    Key(["Key (key)"]) --- VENUE
    Name(["Name"]) --- Key
    Address(["Address"]) --- Key

Section E — Additional resources (not covered in the applied class)

Question 1 — RESTAURANT: DISH entity

The restaurant serves many dishes, each with a unique name on the menu. Each dish also has a unique number and a description (history, calories, price), a preparation time, and ingredients.

DISH entity: key attributes Name and (separately) Number — two candidate keys. Composite attribute Description (→ derived-looking History, Calories, Price — History shown multivalued in the source diagram). Simple attribute PreparationTime, and multivalued attribute Ingredients.

flowchart TD
    Name(["Name (key)"]) --- DISH[DISH]
    Number(["Number (key)"]) --- DISH
    Description(["Description"]) --- DISH
    History(["History (multi)"]) --- Description
    Calories(["Calories"]) --- Description
    Price(["Price"]) --- Description
    PreparationTime(["PreparationTime"]) --- DISH
    Ingredients(["Ingredients (multi)"]) --- DISH

Case Study 1: Dirt Road Driving

Group case study applying the ER/EER model in 2026-02-23-conceptual-database-design-and-the-er-model and 2026-03-02-weak-entities-the-eer-model-and-design-choices to a real-world brief. See er-diagram-notation and relationship-constraints for reference.

Section A — EER diagram

Dirt Road Driving is a rural rideshare company, gathered from correspondence with the company’s Director of Innovation:

  • Users: unique id, full Name, DOB. A user can register any number of emergency contacts (Name unique per user, Email, Phone).
  • Staff: unique staffID, full Name (first/middle/last), DOB, Phone(s). Split into Administration (DeskNumber) and Driver (Licence) — a staff member can be both at once (overlapping).
  • Vehicles: VIN, Make, Model. Disjointly splits into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive).
  • Trip: a user, a vehicle, and a driver (never permanently assigned to one vehicle — recorded per trip), plus a BookingTime and start/end timestamps (used to derive Fare); a trip can record multiple stop Locations. After a trip, the user separately rates the driver and the vehicle (integer out of 10, stored as RATES relationships) — a repeat rating updates the existing one rather than creating a new one.

USER (key ID, Name, DOB) —1:N (total on the weak-entity side) HAS→ EMERGENCYCONTACTS (weak entity, partial key Name, Email, Phone). STAFF (key ID, Name, DOB, multivalued Phone) overlappingly (o) specialises into DRIVER (Licence) and ADMIN (DeskNumber). VEHICLES (key VIN, Make, Model) disjointly (d) specialises into 4WD (RideHeight, WheelType) and 2WD (FrontWheelDrive).

USER —M:N TRIP←DRIVER, and VEHICLES —M:N TRIP (a trip is identified by USER+DRIVER+VEHICLES+BookingTime, with StartTime, EndTime, multivalued StopLocation, and derived Fare). Separately, USER —M:N RATES (Number, out of 10)→ DRIVER, and USER —M:N RATES→ VEHICLES.

flowchart TD
    USER[USER] ---|"1"| HAS{HAS}
    HAS ===|"N"| EMERGENCYCONTACTS[[EMERGENCYCONTACTS]]

    STAFF[STAFF] --- o1(("o"))
    o1 --- DRIVER[[DRIVER]]
    o1 --- ADMIN[[ADMIN]]

    VEHICLES[VEHICLES] --- d1(("d"))
    d1 --- FourWD[["4WD"]]
    d1 --- TwoWD[["2WD"]]

    USER ---|"N"| TRIP{TRIP}
    DRIVER ---|"N"| TRIP
    VEHICLES ---|"N"| TRIP

    USER ---|"M"| RATESD{RATES}
    RATESD ---|"N"| DRIVER
    USER ---|"M"| RATESV{RATES}
    RATESV ---|"N"| VEHICLES

    IDu(["ID (key)"]) --- USER
    NameEC(["Name (partial key)"]) --- EMERGENCYCONTACTS
    IDs(["ID (key)"]) --- STAFF
    Licence(["Licence"]) --- DRIVER
    DeskNumber(["DeskNumber"]) --- ADMIN
    VIN(["VIN (key)"]) --- VEHICLES
    BookingTime(["BookingTime (partial key)"]) --- TRIP
    Fare(["Fare (derived)"]) --- TRIP

Section B — Critical thinking

Propose a change to the EER diagram that would help the company make a new data-informed decision not currently possible.

This is open-ended — no single correct answer. Consider what a rural rideshare operator might want to analyse that today’s schema can’t answer (e.g. tracking incident reports per trip, recording each vehicle’s service/ maintenance history, or logging cancelled vs. completed trips separately) and how you’d extend the entities/relationships above to capture it.

Week 3

The Relational Model and Integrity Constraints

Module 2, part 1. See relational-mapping-notation for the schema notation this lecture (and 2026-03-16-er-to-relational-mapping) uses.

Today’s outline

  • Relational model concepts — relations, attributes, domains, tuples
  • Integrity constraints
  • The transaction concept

Relational model concepts

Introduced by E.F. Codd in 1970. Many DBMS products are based on this model — a sound theoretical foundation with a simple, uniform data structure called a relation. Four basic concepts: relations, attributes, domains, tuples.

Relations

A relation is the main construct for representing data — informally, a set of records, similar to a table with columns and rows. The term table is used interchangeably with relation, but every relation is a table; not every table is a relation — relations have specific properties based on mathematical set theory (e.g. a “pivoted” table with merged header cells, like a quarterly sales report broken down by region/suburb, is a table but not a valid relation).

Domains

A domain D is a set of atomic values — an atomic value is indivisible as far as the relational model is concerned. Each domain has a data type/format: integers, numbers/currency, fixed/variable-length character strings, date/timestamp, a sub-range of a data type (e.g. 1 ≤ grade ≤ 7), or an enumerated type (e.g. Gender ∈ {Male, Female, Other}) — including format-constrained domains like Australian phone numbers (61 + 9 digits) or car registrations (6 alphanumeric characters, no Q).

Attributes

Each attribute A is the name of a role played by some domain D in a relation R. The number of attributes in R is its degree. Same-named attributes across relations don’t necessarily share a domain (Department.id and Employee.id can be different domains despite the shared name id), and differently-named attributes can share a domain (Employee.id and Employee.managerId can both be drawn from the same “employee id” domain, despite the different names).

Tuples

Each tuple t is an ordered list of n values t = <v1, v2, …, vn>, where each value belongs to the corresponding attribute’s domain, or is the special value NULL. t is called an n-tuple.

Relation schema and instance

  • Relation schema: R [A1, A2, …, An] — a relation name R and its list of attributes; n is the degree of the relation. E.g. Employee [id, name, sex, salary, department] is a schema of degree 5.
  • Relation instance: r(R), a set of n-tuples r = {t1, t2, …, tm} — the actual data, at a point in time.

Question — Schema and Instance

Match each characteristic to Schema or Instance: “data in the database”, “specified during database design”, “data describing the data”, “created through data update operations”.

Instance: “data in the database”, “created through data update operations” (inserts/updates/deletes change the instance, not the schema). Schema: “specified during database design”, “data describing the data” (the schema is metadata — it describes the shape data must take, not the data itself).

Ordering of tuples and values

Relations are sets of tuples — mathematically, a set has no implied order. Semantically (e.g. when writing queries), tuple order is irrelevant. Physically, tuples reside on blocks of secondary storage with some ordering, but two instances with the same tuples in a different order are still the same relation. Likewise, an n-tuple is syntactically an ordered list — every tuple in one relation must list values in the same attribute order — but semantically, which order is chosen doesn’t matter, as long as the attribute/value correspondence is maintained consistently.

Integrity constraints

Integrity constraints are rules that enforce the correctness of a database — they must hold on every instance of the schema. Five kinds: domain, key, entity integrity, referential integrity, semantic.

Domain constraint

A domain constraint violation occurs when an attribute’s value doesn’t appear in its corresponding domain — e.g. Employee.id = "LOL" when id is meant to be a 4-digit integer.

Key

A key is a minimal set of attributes that uniquely identifies tuples in a relation — minimal meaning no redundant attributes, not necessarily the smallest possible set. A schema can have more than one key (each a candidate key); the one chosen as the relation’s main key is the primary key (conventionally underlined). A key constraint violation occurs when a tuple is inserted/modified to share a key value with an existing tuple.

Question — Key

Assuming department IDs are unique, which of the following is a key for Department [id, name, manager]: (A) (id), (B) (id, name), (C) (id, manager), (D) all of the above?

(A) (id) — since IDs are already unique on their own, (id) is the minimal key; (id, name) and (id, manager) both contain a redundant extra attribute (adding anything to an already-unique set isn’t minimal).

Question — Key (multiple candidate keys)

Assuming department IDs are unique and the combination of Name and Manager is also unique per department, which of the following is a key(s): (A) (id), (B) (id, name), (C) (name, manager), (D) both A and C?

(D) Both A and C. (id) is still minimal and unique on its own; now (name, manager) is also minimal and unique, making it a second candidate key. (id, name) is still not minimal since (id) alone already suffices.

Entity integrity constraint

An entity integrity constraint violation occurs when a tuple is inserted/modified such that (any part of) its primary key is NULL — for a composite primary key, no part of it can be null.

Foreign keys

A foreign key is a set of attributes in one relation that links it to another relation’s primary key. Formally: let FK be attributes in R1 and PK the primary key of R2. FK in R1 is a foreign key referencing PK in R2 if FK/PK share a domain, and for every tuple t1 in R1, either t1[FK] is NULL, or some tuple t2 in R2 has t1[FK] = t2[PK].

Department [id, name, manager]
Department.manager references Employee.id

Employee [id, name, sex, salary, department]
Employee.department references Department.id

Self-referencing relations are also possible — a table can reference itself, e.g. Employee.managerId references Employee.id. Composite foreign keys are possible too, referencing a multi-attribute primary key:

Student [sid, name]

Course [cid, department]

Enrolment [sid, cid, department, grade]
Enrolment.sid references Student.sid
Enrolment.{cid, department} references Course.{cid, department}

Referential integrity constraint

A referential integrity constraint violation occurs when a foreign key value doesn’t match any existing primary key value in the referenced relation (and isn’t NULL) — e.g. inserting an Employee tuple with department = 5 when no Department with id = 5 exists.

Semantic (business-rule) constraint

Semantic constraints are generally defined by the business/organisation (not derivable from the schema’s structure alone) — e.g. “an employee’s salary must not exceed their supervisor’s”, or “an employee can work at most 56 hours across all projects”. Often implemented via a constraint specification language (SQL triggers/assertions).

Question — Integrity Constraints (password)

Opening an online bank account, you enter your usual password “password123”, but get: “your password must contain at least one capital letter and a number”. What kind of constraint is this?

Domain constraint — the domain of “password” is defined as strings matching a particular format (≥1 capital, ≥1 digit); “password123” simply isn’t a member of that domain.

Question — Integrity Constraints (which is violated?)

Given Department [id, name, manager] and Employee [id, name, salary, department], with Department rows (1, Marketing, 4671), (2, Development, 1751), and Employee rows (1751, Paris Lane, 60000, 2), (4671, Anna Lee, 70000, 1), (2670, Grace Mills, 50000, 2), (2034, Jack Smith, 40000, 1) — which constraint is violated by each of the following?

  1. Inserting (2670, James Smith, 40000, 1) into Employee.
  2. Inserting (2644, James, Smith, 1) into Employee (note: only 4 values given for a 4-attribute relation, but shifted).
  3. Inserting (2644, James Smith, 40000, 3) into Employee.
  4. Deleting (2, Development, 1751) from Department.
  5. Updating (2, Development, 1751) to (2, Development, 2034) in Department.
  1. Key constraint — id = 2670 already exists in Employee.
  2. Domain constraint — the shift means salary receives a non-numeric value ("Smith"), which isn’t a member of salary’s domain.
  3. Referential integrity constraint — no Department with id = 3 exists.
  4. Referential integrity constraint — Employee rows with department = 2 still exist (Grace Mills), so deleting Department 2 would leave a dangling foreign key.
  5. None of the above (by the stated constraints) — Employee with id = 2034 doesn’t actually work in Department 1, but there’s no declared constraint stopping a department’s manager from being an employee of a different department; this would only be a semantic constraint if the business rule “a manager must work in the department they manage” were explicitly specified.

Constraints and operations

Enforcing integrity constraints keeps the database consistent — insert, modify, and delete operations must not leave the database in an inconsistent state; a DBMS should reject any update that would violate integrity.

  • Insertion/modification can violate any of the five constraint types (domain, key, entity integrity, referential integrity, semantic).
  • Deletion can only violate referential or semantic constraints. A referential integrity violation on delete can be handled by rejecting the delete, cascading it (deleting dependent rows too), or setting the referencing value to a default/NULL.

The transaction concept

A transaction is an executing program that includes database operations (reads, inserts, deletes, updates). At the end of a transaction, the database must be left in a valid/consistent state satisfying all constraints — but constraint violations are allowed at intermediate steps within the transaction.

Example: given Department [id, name, manager] and Employee [id, name, sex, salary, department], with the business rules “every department must have ≥1 employee” and “every employee must work for a department” — neither a new department (no employees yet) nor a new employee (no department yet) can be inserted alone without violating one of these rules. A transaction that inserts both the new department and its first employee together resolves this: the intermediate state (after just one insert) is inconsistent, but the final state (after both inserts) is valid.

Applied Class 2: Entity Relationship Diagrams

Practice for 2026-03-02-weak-entities-the-eer-model-and-design-choices. See er-diagram-notation and relationship-constraints for reference.

Section A — ERD components

Given a COMPANY ER diagram (EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT, plus the relationships MANAGES/WORKS_FOR/CONTROLS/ WORKS_ON/SUPERVISION/DEPENDENTSOF), find an example of each ERD component.

Component Example
Strong entity EMPLOYEE, DEPARTMENT, PROJECT
Weak entity DEPENDENT
(Candidate) key EMPLOYEE.Ssn; DEPARTMENT.Name/Number; PROJECT.Name/Number; DEPENDENT.{Ssn, Name}
Partial key DEPENDENT.Name
Composite attribute EMPLOYEE.Name (→ FirstName, MiddleInitial, LastName)
Derived attribute DEPARTMENT.Number_of_employees
Multivalued attribute DEPARTMENT.Locations
1-1 relationship MANAGES
1-N relationship CONTROLS, WORKS_FOR, SUPERVISION
M-N relationship WORKS_ON
Identifying relationship DEPENDENTSOF 

flowchart TD
    EMPLOYEE[EMPLOYEE] ---|"1 supervisor"| SUPERVISION{SUPERVISION}
    SUPERVISION ---|"N supervisee"| EMPLOYEE
    EMPLOYEE ===|"N"| WORKS_FOR{WORKS_FOR}
    WORKS_FOR ---|"1"| DEPARTMENT[DEPARTMENT]
    EMPLOYEE ---|"1"| MANAGES{MANAGES}
    MANAGES ===|"1"| DEPARTMENT
    DEPARTMENT ---|"1"| CONTROLS{CONTROLS}
    CONTROLS ---|"N"| PROJECT[PROJECT]
    EMPLOYEE ---|"M"| WORKS_ON{WORKS_ON}
    WORKS_ON ---|"N"| PROJECT
    EMPLOYEE ---|"1"| DEPENDENTSOF{DEPENDENTSOF}
    DEPENDENTSOF ===|"N"| DEPENDENT[[DEPENDENT]]

    Ssn(["Ssn (key)"]) --- EMPLOYEE
    NameE(["Name"]) --- EMPLOYEE
    Salary(["Salary"]) --- EMPLOYEE

    DeptKey(["Number/Name (key)"]) --- DEPARTMENT
    NumEmp(["Number_of_employees (derived)"]) --- DEPARTMENT
    Locations(["Locations (multi)"]) --- DEPARTMENT

    ProjKey(["Number/Name (key)"]) --- PROJECT

    DepName(["Name (partial key)"]) --- DEPENDENT

Section B — ERD modelling assumptions

Uses a health/vaccination EER diagram: HEALTHWORKER —1:N OVERSEEN→ HEALTH (subclass of PARTICIPANT, alongside an overlapping AGE subclass); VACCINE (ID, composite Key = Brand+Name) has a recursive WITH relationship (roles First/Second, both partial) and —1:N HAS→ BATCH (weak entity, partial key Number).

Evaluate each statement as Correct or Incorrect.

B1 — Each vaccine must have a second dose or a preceding dose.

B2 — A participant can be overseen by several healthcare workers.

B3 — Every participant in the HEALTH subclass is overseen by a health worker during vaccination.

B4 — A vaccine can only be uniquely identified by the combination of its Brand and Name.

B5 — A participant can be in both the HEALTH subclass and the AGE subclass.

B6 — A BATCH is uniquely identified by only its number.

  • B1 Incorrect — the recursive WITH relationship lets a vaccine be recorded as a first or second dose, but participation from both sides is partial: a vaccine doesn’t have to be part of a two-shot program.
  • B2 Incorrect — OVERSEEN is 1:N from HEALTHWORKER to HEALTH: one healthcare worker can oversee many participants, but each participant has at most one overseeing healthcare worker.
  • B3 Correct — OVERSEEN has total participation from the HEALTH side.
  • B4 Incorrect — VACCINE has two candidate keys: the composite Key (Brand+Name), or its ID.
  • B5 Correct — HEALTH and AGE are overlapping subclasses of PARTICIPANT — an instance can be a member of both at once.
  • B6 Incorrect — BATCH is a weak entity; its real key is Batch Number combined with its owner VACCINE’s key (either ID or Key).

flowchart TD
    PARTICIPANT[PARTICIPANT] --- o1(("o"))
    o1 --- HEALTH[[HEALTH]]
    o1 --- AGE[[AGE]]
    HEALTHWORKER[HEALTHWORKER] ---|"1"| OVERSEEN{OVERSEEN}
    OVERSEEN ===|"N"| HEALTH

    VACCINE[VACCINE] ---|"First"| WITH{WITH}
    WITH ---|"Second"| VACCINE
    VACCINE ---|"1"| HAS0{HAS}
    HAS0 ===|"N"| BATCH[[BATCH]]

    IDv(["ID (key)"]) --- VACCINE
    VKey(["Key (key)"]) --- VACCINE
    Brand(["Brand"]) --- VKey
    VName(["Name"]) --- VKey
    Number(["Number (partial key)"]) --- BATCH

Section C — Conceptual modelling

Question 1 — Cinema

Movies (unique Name, Description, RunningTime); attendants (unique StaffID, Name, Phone); customers (unique ID, Name, Email); viewings record a Timestamp (unique per customer+movie) and any Snacks purchased; exactly one attendant oversees each viewing.

MOVIE —1:N HAS→ VIEWING (Timestamp, multivalued Snack) —1:N OVERSEES←ATTENDANT; CUSTOMER —1:N HAS→ VIEWING. VIEWING’s key is the composite of its owning CUSTOMER+MOVIE+Timestamp (it’s a weak entity in the fuller reading of the UoD, or an M:N relationship with a Timestamp key attribute, depending on the design choice made).

flowchart TD
    MOVIE[MOVIE] ---|"1"| HAS1{HAS}
    HAS1 ---|"N"| VIEWING[[VIEWING]]
    CUSTOMER1[CUSTOMER] ---|"1"| HAS2{HAS}
    HAS2 ---|"N"| VIEWING
    VIEWING ---|"N"| OVERSEES{OVERSEES}
    OVERSEES ---|"1"| ATTENDANT[ATTENDANT]

    NameM(["Name (key)"]) --- MOVIE
    Description(["Description"]) --- MOVIE
    RunningTime(["RunningTime"]) --- MOVIE
    Timestamp(["Timestamp (partial key)"]) --- VIEWING
    Snack(["Snack (multi)"]) --- VIEWING
    StaffID(["StaffID (key)"]) --- ATTENDANT
    IDc(["ID (key)"]) --- CUSTOMER1

Question 2 — Medical clinic

Patients (PatientID) and doctors (LicenceNo); each doctor is either a GP or a specialist (never both), specialists also record their SpecialisationArea; doctors work in clinics (RegistrationNo), one clinic can have several doctors; some clinics are specialist clinics, each run by exactly one specialist doctor; patients make appointments (Fee) to consult doctors.

PATIENT (PatientID) —M:N CONSULTS (Fee)→ DOCTOR (LicenceNo), which disjointly (d) specialises into GP/SPECIALIST (SpecialisationArea). DOCTOR —M:N WORKSIN→ CLINIC (RegistrationNo), which has a subclass SPECIALIST CLINIC —1:N RUNS←SPECIALIST.

Assumptions: every patient in the DB has made an appointment; every clinic has at least one doctor.

flowchart TD
    PATIENT[PATIENT] ---|"M"| CONSULTS{CONSULTS}
    CONSULTS ---|"N"| DOCTOR[DOCTOR]
    DOCTOR --- d(("d"))
    d --- GP[[GP]]
    d --- SPECIALIST[[SPECIALIST]]
    DOCTOR ---|"M"| WORKSIN{WORKSIN}
    WORKSIN ---|"N"| CLINIC[CLINIC]
    CLINIC --- SPECIALISTCLINIC[["SPECIALIST CLINIC"]]
    SPECIALIST ---|"1"| RUNS{RUNS}
    RUNS ---|"N"| SPECIALISTCLINIC

    PatientID(["PatientID (key)"]) --- PATIENT
    LicenceNo(["LicenceNo (key)"]) --- DOCTOR
    Fee(["Fee"]) --- CONSULTS
    SpecArea(["SpecialisationArea"]) --- SPECIALIST
    RegNo(["RegistrationNo (key)"]) --- CLINIC

Section D — Additional resources (not covered in the applied class)

Question 1 — Bubble tea shop

BUBBLETEA (unique Name, Description, Ingredients, Price); customers sign up with a BubbleID, Name, Phone, Email; each order (BUYS) is timestamped with a PrepTime; customers can friend other customers.

BUBBLETEA —N:M BUYS (PrepTime, key Timestamp)→ CUSTOMER (BubbleID); CUSTOMER has a recursive M:N FRIENDS relationship with itself (roles Requestor/Requestee).

flowchart LR
    BUBBLETEA[BUBBLETEA] ---|"N"| BUYS{BUYS}
    BUYS ---|"M"| CUSTOMER2[CUSTOMER]
    CUSTOMER2 ---|"Requestor"| FRIENDS{FRIENDS}
    FRIENDS ---|"Requestee"| CUSTOMER2

    NameB(["Name (key)"]) --- BUBBLETEA
    Description2(["Description"]) --- BUBBLETEA
    Ingredients(["Ingredients"]) --- BUBBLETEA
    Price(["Price"]) --- BUBBLETEA
    Timestamp2(["Timestamp (partial key)"]) --- BUYS
    PrepTime(["PrepTime"]) --- BUYS
    BubbleID(["BubbleID (key)"]) --- CUSTOMER2

Question 2 — Bank

Bank (composite key Code+Name, Address) has branches (Number unique per-bank, Address); a branch manages accounts and loans; every account has Type, Balance, unique Number, controlled by ≥1 customers; every loan has unique Number, Type, Amount, connected to ≥1 customers; every loan/account belongs to exactly one branch; customers have Name, Address, Phone, unique ID.

BANK (key Key = Code+Name) —1:N OPERATES→ BRANCH (weak entity, partial key Number) —1:N OFFERS→ LOAN (Number, Type, Amount); BRANCH —1:N OFFERS→ ACCOUNT (Number, Balance, Type). LOAN —M:N HAS→ CUSTOMER (ID, Name, Address, Phone); ACCOUNT —M:N HAS→ CUSTOMER, both with total participation on the LOAN/ACCOUNT side (“must be associated with… at least one customer”).

flowchart TD
    BANK[BANK] ---|"1"| OPERATES{OPERATES}
    OPERATES ===|"N"| BRANCH[[BRANCH]]
    BRANCH ---|"1"| OFFERS1{OFFERS}
    OFFERS1 ---|"N"| LOAN[LOAN]
    BRANCH ---|"1"| OFFERS2{OFFERS}
    OFFERS2 ---|"N"| ACCOUNT[ACCOUNT]
    LOAN ===|"M"| HAS3{HAS}
    HAS3 ---|"N"| CUSTOMER3[CUSTOMER]
    ACCOUNT ===|"M"| HAS4{HAS}
    HAS4 ---|"N"| CUSTOMER3

    BankKey(["Key (key)"]) --- BANK
    Code(["Code"]) --- BankKey
    BName(["Name"]) --- BankKey
    BranchNum(["Number (partial key)"]) --- BRANCH
    LoanNum(["Number (key)"]) --- LOAN
    AcctNum(["Number (key)"]) --- ACCOUNT
    IDc2(["ID (key)"]) --- CUSTOMER3

Case Study 2: Dirt Road Driving

Group case study applying integrity constraints from 2026-03-09-the-relational-model-and-integrity-constraints to Dirt Road Driving’s internal payroll system. See relational-mapping-notation for reference.

Section A — The schema

Dirt Road Driving’s payroll backend, built by an external developer:

Employee [id, firstName, lastName, role]

Project [name, description, funding, projectLeader]
Project.projectLeader references Employee.id

TimeLog [employeeID, projectName, date, hoursWorked, approved]
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name

Company policy (a semantic/business-rule constraint, not enforceable by the schema’s structure alone): administration staff cannot be project leaders.

Question 1 — Identify a key and a foreign key

Any candidate key works, e.g. Employee.id, Project.name, or TimeLog.{employeeID, projectName, date} (composite). Any foreign key works, e.g. Project.projectLeader, TimeLog.employeeID, or TimeLog.projectName.

Section B — Integrity constraint violations

Sample data: Employee has (1919, Diluen, Smith, Developer), (2014, Daniel, Johnson, Administration), (2019, Annie, Fang, Developer), (2020, Russell, Turner, Manager). Project has ("Website Setup", ..., 12000, 2019), ("2020 Marketing", ..., 40000, 2020). TimeLog has 5 rows, including (1919, "Website Setup", 2/1/2020, 5, true) and (2020, "2020 Marketing", 2/1/2020, 5, true), among others — no row with employeeID = 1919 and projectName = "2020 Marketing". For each operation, does it violate an integrity constraint?

Operation 1 — Update ("Website Setup", ..., 12000, 2019) to ("Website Setup", ..., 20000, 1919) in Project (changes funding and projectLeader; 1919 is a Developer).

Operation 2 — Insert (2014, "Rebecca", "Zhang", "Administration") into Employee.

Operation 3 — Update (2020, "2020 Marketing", 2/1/2020, 5, true) to (1919, "Overall Marketing", 2/1/2020, 5, true) in TimeLog.

Operation 4 — Insert (NULL, "Test", "Test", "Test") into Employee.

Operation 5 — Delete (2014, "Daniel", "Johnson", "Administration") from Employee.

Operation 6 — Insert ("Talent Recruitment Initiative", ..., 10000, 2014) into Project (2014 is Administration).

  • Op 1 No — a well-formed update; funding/projectLeader values both change to valid new values (1919 is a real, existing employee), and nothing here conflicts with a key/entity/referential/semantic rule.
  • Op 2 Yes — Key constraint. id = 2014 already exists in Employee (id is its primary key), so this insert isn’t unique.
  • Op 3 Yes — Referential integrity. TimeLog.projectName references Project.name, but no Project named "Overall Marketing" exists — the foreign key value doesn’t resolve.
  • Op 4 Yes — Entity integrity. Employee.id is the primary key, and it’s NULL here — no part of a primary key may ever be NULL.
  • Op 5 Yes — Referential integrity. TimeLog.employeeID (foreign key to Employee.id) has a row referencing employeeID = 2014 — deleting that Employee row would leave it dangling.
  • Op 6 Yes — Semantic (user-defined) constraint. projectLeader = 2014 is an Administration staff member, violating the business rule that administration staff cannot be project leaders — this isn’t something the schema’s structure alone captures (no foreign key/domain/key rule is broken), it’s an organisation-specific policy.

Question 2 — Original examples

Give an original example (not copied from above) of an operation that would cause (a) a domain constraint violation, (b) a referential integrity constraint violation.

  1. Domain constraint: inserting ("A string is not a number", "Joe", "Blog", "Administration") into Employee — id is meant to be numeric, not a string. (b) Referential integrity: deleting (2020, "Russell", "Turner", "Manager") from Employee while a Project row still has projectLeader = 2020 — the deletion would leave that foreign key dangling.

Week 4

ER to Relational Mapping

Module 2, part 2 — continues on from 2026-03-09-the-relational-model-and-integrity-constraints. See relational-mapping-notation for the schema notation used throughout, and er-diagram-notation for the ER/EER diagram notation being mapped from.

Today’s outline

  • The (7+1) steps for mapping an ER/EER diagram to a relational schema
  • Worked example: the Company database
  • Worked example: a Bank database

Why map at all?

The ER model is commonly used for conceptual design (user’s perspective); the relational model is the basis for most commercial DBMSs (storage perspective). Mapping converts a conceptual ER schema into a logical relational schema — input: an ER model; output: relations with primary/foreign key constraints.

The (7+1) steps

  1. Entity mapping
  2. Weak entity mapping
  3. Binary 1:1 relationship mapping
  4. Binary 1:N relationship mapping
  5. Binary M:N relationship mapping
  6. Multivalued attribute mapping
  7. N-ary relationship mapping
  8. (+1, for EER only) Super/subclass mapping

Super/subclass mapping (step “+1”) doesn’t happen at one fixed point — it’s generally done after step 1 or 2, whenever the subclass needs to exist for a later relationship-mapping step to reference it.

Running example — the Company database: EMPLOYEE (key Ssn, composite Name → Fname/Mit/Lname, Sex, Salary, Address, DOB), DEPARTMENT (keys Dnumber/Dname, derived NumberOfEmployees, multivalued Locations), PROJECT (keys Pno/Pname, Plocation), DEPENDENT (weak entity, partial key DepName, Sex, DOB, Relationship) — relationships WORKSFOR (EMPLOYEE N:1 DEPARTMENT), MANAGES (EMPLOYEE 1:1 DEPARTMENT, attribute StartDate), CONTROLS (DEPARTMENT 1:N PROJECT), WORKSON (EMPLOYEE M:N PROJECT, attribute Hours), SUPERVISION (EMPLOYEE recursive 1:N, roles supervisor/supervisee), DEPENDENTSOF (EMPLOYEE 1:N DEPENDENT, identifying), and EMPLOYEE disjointly (d) specialises into SECRETARY (TypingSpeed)/ENGINEER (EngineerType).

Step 1 — Entity mapping

For each (strong) entity type: create a relation, choose a key as the primary key, and include all simple attributes (not composite, derived, or multivalued).

Employee [ssn, fName, mIt, lName, dob, address, sex, salary]

Department [dNumber, dName]

Project [pNo, pName, pLocation]

Name isn’t added directly (it’s composite — its simple components fName/mIt/lName are added instead); Locations and NumberOfEmployees aren’t added to Department yet (multivalued and derived respectively — derived attributes are never stored as columns, since they’re computed on demand).

Step 2 — Weak entity mapping

For each weak entity type: create a relation; its primary key is the combination of its owner’s primary key(s) and its own partial key; include a foreign key back to the owner’s primary key; include all simple attributes.

Dependent [ssn, depName, sex, dob, relationship]
Dependent.ssn references Employee.ssn

Ssn (Employee’s key) is included as part of Dependent’s own primary key, and depName is its partial key — so Dependent’s full key is (ssn, depName).

If a weak entity has more than one owner entity type, its relation includes a foreign key to each owner’s primary key, and its own primary key is the combination of all owner keys plus its partial key.

Question — Weak Entities (Cities/Provinces)

PROVINCE (key name, premier) —1:N IN→ CITY (partial key name, mayor). Resolving the dual use of “name” reasonably, which schema is the most reasonable translation?

A. Cities [name, mayor], Provinces [name, premier]
B. Cities [cName, pName, mayor], Provinces [pName, premier], Cities.cName references Provinces.pName, Cities.pName references Provinces.pName
C. Cities [cName, pName, mayor], Provinces [pName, premier], Cities.pName references Provinces.pName
D. Cities [cName, pName, mayor], In [cName, pName], Provinces [name, premier], Cities.pName references Provinces.name

(C). CITY is a weak entity, so its real key is (pName, cName) — (A) is wrong since it drops pName from Cities’ key entirely. (B) is wrong because cName (the partial key) is not a foreign key — only pName (inherited from the owner) is. (D) is wrong because IN is the identifying relationship for a weak entity — it does not get mapped to its own separate relation (unlike a regular 1:N relationship, which doesn’t need one either, but D invents an unnecessary one here on top of misnaming Provinces’ key column).

Step 3 — Binary 1:1 relationship mapping

For each binary 1:1 relationship: choose one participating entity type (prefer the one with total participation); include a foreign key from it back to the other entity’s primary key; include the relationship’s own simple attributes on the chosen side.

MANAGES (EMPLOYEE 1:1 DEPARTMENT, DEPARTMENT has total participation — every department must have a manager) — extend Department, since it’s the side with total participation:

Department [dNumber, dName, mgrSSN, mgrStartDate]
Department.mgrSSN references Employee.ssn

Question — Binary Relationship

S —1:1 R→ T (T has attribute b1). Which schema is a reasonable translation?

A. S [a1, b1], T [b1], S.b1 references T.b1
B. S [a1], T [b1]
C. ST [a1, b1]
D. S [a1], T [b1, a1], T.a1 references S.a1

(A). S should be the side extended with the foreign key, since S has full/total participation in R (shown by the diagram’s 1 cardinality directly against S, no partial-participation gap) — preferred over T for that reason. (B) loses all information about the relationship itself. (C) merges two intentionally-separate entity types into one relation, discarding a conceptual modelling decision. (D) puts the foreign key on the wrong side.

Step 4 — Binary 1:N relationship mapping

For each (non-weak) binary 1:N relationship: identify the entity type on the N side; add a foreign key there, referencing the primary key of the entity type on the 1 side; include the relationship’s simple attributes on the N side.

WORKSFOR (EMPLOYEE N:1 DEPARTMENT), CONTROLS (DEPARTMENT 1:N PROJECT), SUPERVISION (EMPLOYEE recursive, N side = supervisee):

Employee [ssn, fName, mIt, lName, dob, address, sex, salary, dNumber, superSSN]
Employee.dNumber references Department.dNumber
Employee.superSSN references Employee.ssn

Project [pNo, pName, pLocation, dNumber]
Project.dNumber references Department.dNumber

Question — Relationship Mapping

A —N R 1→ B —N S 1→ C (with attributes a/d on A, b/e on B, c/f on C). Which of the following appears in your relational schema: (A) A [a, b, d], A.b references B.b; (B) B [b, c, e], B.c references C.c; (C) S [b, c]; (D) all of the above; (E) none of the above?

(B). Each 1:N relationship puts the foreign key on the N side: for R that’s A (referencing B), and for S that’s B (referencing C) — so B [b, c, e], B.c references C.c is exactly right. (A) is wrong: b is the right foreign key column for A, but it shouldn’t be part of A’s primary key — 1:N foreign keys are plain (non-key) attributes on the N side, not treated as part of a composite key like a weak entity’s would be (the answer as written implies b joining A’s key, which the mapping rule doesn’t call for). (C) is wrong — S is a relationship, not an entity type, so 1:N mapping doesn’t create a relation for it at all. The full correct schema: A [a, b, d], A.b references B.b; B [b, c, e], B.c references C.c; C [c, f].

Step 5 — Binary M:N relationship mapping

For each binary M:N relationship: create a new relation; include foreign keys to both participating entity types’ primary keys (their combination becomes the new relation’s primary key); include the relationship’s own simple attributes.

WORKSON (EMPLOYEE M:N PROJECT, attribute Hours):

WorksOn [ssn, pNo, hours]
WorksOn.ssn references Employee.ssn
WorksOn.pNo references Project.pNo

Note: 1:1 and 1:N relationships can also be mapped this same “new relation” way — useful when the relationship is sparse, since it avoids NULL foreign key values that a direct-embedding mapping would otherwise produce for non-participating rows.

Step 6 — Multivalued attribute mapping

For each multivalued attribute: create a new relation; include a foreign key to the owning entity’s primary key; the new relation’s primary key is the combination of that foreign key and the multivalued attribute itself (if the multivalued attribute is composite, include its simple components instead).

DEPARTMENT.Locations:

DeptLocs [dNumber, location]
DeptLocs.dNumber references Department.dNumber

Step 7 — N-ary relationship mapping

For each N-ary relationship (excluding all-1-side cases): create a new relation; include foreign keys to all participating entity types; the foreign keys coming from the many-side entity types form the primary key; include the relationship’s own simple attributes.

(No N-ary relationships in the Company database — WORKSON is binary.)

Step “+1” — Super/subclass mapping (EER)

Works for any combination of total/partial and disjoint/overlapping subclasses. For each subclass: create a relation; its primary key is the superclass’s primary key; include a foreign key back to the superclass’s relation; include the subclass’s own simple attributes.

EMPLOYEE disjointly specialises into SECRETARY/ENGINEER:

Secretary [ssn, typingSpeed]
Secretary.ssn references Employee.ssn

Engineer [ssn, engineerType]
Engineer.ssn references Employee.ssn

This step doesn’t happen at one fixed position in the 7-step sequence — it’s usually done right after step 1 or 2 (whichever applies to the superclass), specifically so the subclass relations already exist by the time any later relationship-mapping step needs to reference them.

Final schema — Company database

Employee [ssn, fName, mIt, lName, dob, address, sex, salary, dNumber, superSSN]
Employee.dNumber references Department.dNumber
Employee.superSSN references Employee.ssn

Department [dNumber, dName, mgrSSN, mgrStartDate]
Department.mgrSSN references Employee.ssn

Project [pNo, pName, pLocation, dNumber]
Project.dNumber references Department.dNumber

Dependent [ssn, depName, sex, dob, relationship]
Dependent.ssn references Employee.ssn

Secretary [ssn, typingSpeed]
Secretary.ssn references Employee.ssn

Engineer [ssn, engineerType]
Engineer.ssn references Employee.ssn

WorksOn [ssn, pNo, hours]
WorksOn.ssn references Employee.ssn
WorksOn.pNo references Project.pNo

DeptLocs [dNumber, location]
DeptLocs.dNumber references Department.dNumber

Worked example — Bank database

A bank (unique code, name, hoAddr) has branches (branchNo unique per-bank, addr). A branch has accounts (acNo, type, balance, ≥1 account holders) and loans (loanNo, type, amount, ≥1 loan holders). Customers (ssn, name, address) can hold accounts and/or loans.

Working through the same 7 steps:

After step 1 (entity mapping):

Bank [code, name, hoAddr]

Account [acNo, type, balance]

Loan [loanNo, type, amount]

Customer [ssn, name, address]

After step 2 (BRANCH is a weak entity, owned by BANK):

Branch [bankCode, branchNo, addr]
Branch.bankCode references Bank.code

After step 4 (BRANCH 1:N ACCOUNT/LOAN — foreign keys added to the N side; note the FK is composite here, since Branch’s own key is composite):

Account [acNo, type, balance, bankCode, branchNo]
Account.{bankCode, branchNo} references Branch.{bankCode, branchNo}

Loan [loanNo, type, amount, bankCode, branchNo]
Loan.{bankCode, branchNo} references Branch.{bankCode, branchNo}

After step 5 (ACCOUNT/LOAN M:N CUSTOMER — new relations):

AccountHolder [acNo, ssn]
AccountHolder.acNo references Account.acNo
AccountHolder.ssn references Customer.ssn

LoanHolder [loanNo, ssn]
LoanHolder.loanNo references Loan.loanNo
LoanHolder.ssn references Customer.ssn

Final schema:

Bank [code, name, hoAddr]

Branch [bankCode, branchNo, addr]
Branch.bankCode references Bank.code

Account [acNo, type, balance, bankCode, branchNo]
Account.{bankCode, branchNo} references Branch.{bankCode, branchNo}

Loan [loanNo, type, amount, bankCode, branchNo]
Loan.{bankCode, branchNo} references Branch.{bankCode, branchNo}

Customer [ssn, name, address]

AccountHolder [acNo, ssn]
AccountHolder.acNo references Account.acNo
AccountHolder.ssn references Customer.ssn

LoanHolder [loanNo, ssn]
LoanHolder.loanNo references Loan.loanNo
LoanHolder.ssn references Customer.ssn

Applied Class 3: Relational Integrity

Practice for 2026-03-09-the-relational-model-and-integrity-constraints. See relational-mapping-notation for reference.

Section A — Recap and preview

Question 1 — Matching relational concepts

Match each concept to its description: (1) any minimal set of attributes that can uniquely identify tuples in a relation, (2) defines the structure of a relation, specified during database design, (3) a candidate key chosen to identify tuples in a relation, (4) data in a relation, modified via create/update/delete operations.

Candidate key → 1, Relational Schema → 2, Primary Key → 3, Relational Instance → 4.

Question 2 — Electoral roll schema

Given:

Region [prefix, suffix]

Member [enrolmentId, passportNum, regionPrefix, regionSuffix]
Member.{regionPrefix, regionSuffix} references Region.{prefix, suffix}

Candidate [enrolmentId, name, regionPrefix, regionSuffix]
Candidate.enrolmentId references Member.enrolmentId
Candidate.{regionPrefix, regionSuffix} references Region.{prefix, suffix}

List all examples of each component: candidate key, primary key, composite primary key, foreign key.

  • Candidate key: Member.enrolmentId, Member.passportNum, Candidate.enrolmentId, Region.{prefix, suffix}.
  • Primary key: Member.enrolmentId, Candidate.enrolmentId, Region.{prefix, suffix}.
  • Composite primary key: Region.{prefix, suffix}.
  • Foreign key: Member.{regionPrefix, regionSuffix}, Candidate.{regionPrefix, regionSuffix}, Candidate.enrolmentId.

Question 3 — Schema statements

Select all true statements: (1) a schema is the metadata, or data describing the data, (2) a schema is specified during database design, (3) a schema is created during data updates and changes frequently, (4) a schema is the data in the database at a particular time.

Enter the correct option numbers in ascending order, comma-separated:

1, 2. A schema is metadata specified at design time — it doesn’t change with every data update (that’s the instance), and it isn’t itself “the data” (statements 3 and 4 both describe the instance, not the schema).

Question 4 — Key statements

Select all true statements: (1) a key is a unique identifier of a tuple in the relation, (2) the key constraint implies no two tuples can share the same values for all attributes, (3) a key cannot have multiple attributes, (4) an entity may have more than one primary key, (5) a primary key is a candidate key chosen as the main key for a relation.

Enter the correct option numbers in ascending order, comma-separated:

1, 5. (2) is a weaker, different notion (that’s just basic set semantics — no two tuples are ever fully identical in a relation regardless of keys — not what makes something a key specifically). (3) is false — composite keys exist (e.g. Region.{prefix, suffix} above). (4) is false — an entity can have several candidate keys, but only one of them is chosen as the primary key.

Section B — Integrity constraints in practice

A company database: Employee [ssn, fName, mInit, lName, bDate, address, sex, salary, superSsn, dNo], Department [dName, dNumber, mgrSsn, mgrStartDate], Project [pName, pNumber, pLocation, dNum], WorksOn [essn, pNo, hours], Dependent [essn, depName, sex, bDate, relationship]. Two extra business rules: an employee’s salary cannot exceed their supervisor’s, and a department cannot have more than three active projects at a time. For each operation below, does it violate an integrity constraint (assume any earlier operations in this list have not been applied)?

B1 — Insert ('Robert', 'F', 'Scott', 943775543, '1942-06-21', '2365 Newcastle Rd, Bellaire, TX', 'M', 50000, 888665555, 1) into Employee (943775543 and 888665555 are both not currently Employee SSNs other than as noted; 888665555 belongs to the company president, whose salary far exceeds 50000).

B2 — Insert ('ProductA', 'A', 'Bellaire', 2) into Project (no Department with dNumber = 2 exists).

B3 — Insert ('Production', 4, 943775543, '1988-10-01') into Department (a Department with dNumber = 4 already exists; no Employee with ssn = 943775543 exists).

B4 — Insert (677678989, null, 40.0) into WorksOn (no Employee with ssn = 677678989 exists).

B5 — Insert ('Grace', 'G', 'Chan', 31203126, '1962-06-21', '2312 Anchor Rd, Bellaire, TX', 'F', 58000, 888665555, 1) into Employee (supervisor 888665555 earns less than 58000).

B6 — Delete the WorksOn tuples with essn = 333445555.

B7 — Delete the Employee tuple with ssn = 987654321.

B8 — Modify mgrSsn/mgrStartDate of the Department tuple with dNumber = 5 to 123456789/'1988-10-01' (123456789 is an existing Employee SSN).

B9 — Modify superSsn of the Employee tuple with ssn = 999887777 to 943775543 (no Employee with ssn = 943775543 exists).

B10 — Insert ('ProductSuperDuperSecret', 86, 'Washington', 5) into Project (department 5 already has three active projects).

  • B1 No — this is a well-formed, non-conflicting insert: the new ssn doesn’t already exist (no key violation), the supervisor ssn does exist (no referential violation), and the salary doesn’t exceed the supervisor’s (no semantic violation).
  • B2 Yes — Referential integrity (dNum = 2 doesn’t exist in Department) and domain constraint (pNumber should be an integer, not the string 'A').
  • B3 Yes — Key constraint (dNumber = 4 already exists) and referential integrity (mgrSsn = 943775543 doesn’t exist in Employee).
  • B4 Yes — Entity integrity (pNo, part of WorksOn’s primary key, is NULL) and referential integrity (essn = 677678989 doesn’t exist in Employee).
  • B5 Yes — Semantic constraint (this employee’s salary exceeds their supervisor’s).
  • B6 No — nothing else references WorksOn rows as a foreign key target, and no business rule requires an employee to have work assignments, so deleting them is unconstrained.
  • B7 Yes — Referential integrity (WorksOn, Dependent, Department, and Employee itself all have tuples referencing this ssn, which would be left dangling).
  • B8 No — 123456789 is a valid existing employee, dNumber = 5 already exists (this is an update, not a duplicate insert), and no stated business rule constrains who can be a department’s manager.
  • B9 Yes — Referential integrity (the new superSsn value doesn’t match any existing Employee.ssn).
  • B10 Yes — Semantic constraint (exceeds the “max 3 active projects per department” business rule).

Case Study 3: Dirt Road Driving

Group case study applying ER-to-relational mapping from 2026-03-16-er-to-relational-mapping to a revised Dirt Road Driving EER diagram (the EER diagram itself is week2-tutorial-case-study-1-dirt-road-driving’s, refined per that case study’s open-ended Section B). See relational-mapping-notation for reference.

Section A — Full relational mapping

Elaine’s team maps the revised EER diagram (User, Staff disjointly specialising into Driver/Admin, Vehicles disjointly specialising into 4WD/2WD, weak entities EmergencyContact and TripStop, the ternary-ish Trip, and the UserRatesDriver/ UserRatesVehicle relationships) to a full relational schema.

User [id, dob, firstName, middleName, lastName]

Staff [id, dob, firstName, middleName, lastName]

Vehicles [vin, make, model]

Driver [id, licence]
Driver.id references Staff.id

Admin [id, deskNumber]
Admin.id references Staff.id

4WD [vin, rideHeight, wheelType]
4WD.vin references Vehicles.vin

2WD [vin, frontWheelDrive]
2WD.vin references Vehicles.vin

EmergencyContact [userID, name, email, phone]
EmergencyContact.userID references User.id

StaffPhone [id, phone]
StaffPhone.id references Staff.id

Trip [userID, driverID, vin, bookingTime, startTime, endTime]
Trip.userID references User.id
Trip.driverID references Driver.id
Trip.vin references Vehicles.vin

TripStop [userID, driverID, vin, bookingTime, location]
TripStop.{userID, driverID, vin, bookingTime} references Trip.{userID, driverID, vin, bookingTime}

UserRatesDriver [userID, driverID, rating]
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id

UserRatesVehicle [userID, vin, rating]
UserRatesVehicle.userID references User.id
UserRatesVehicle.vin references Vehicles.vin

Key points:

  • Driver/Admin and 4WD/2WD are standard disjoint-subclass mappings — each subclass’s primary key is its superclass’s key, plus a foreign key back to the superclass.
  • EmergencyContact is a weak entity owned by User — its real key is (userID, name) (owner’s key + its own partial key name), not just name alone (a name is only guaranteed unique per user).
  • StaffPhone maps Staff’s multivalued Phone attribute — its primary key is (id, phone) (the multivalued attribute joins the owner’s key to form the key, per the multivalued-attribute mapping rule), which makes sense of why phone isn’t shown as a single extra column on Staff itself.
  • Trip is identified by the combination of userID, driverID, vin, and bookingTime — a trip is only unique per that combination (the same user/driver/vehicle triple could recur across different bookings).
  • TripStop is a weak entity too, owned by Trip — it needs Trip’s entire composite key as a foreign key, plus its own partial key location.
  • UserRatesDriver/UserRatesVehicle are both M:N relationships (each gets its own new relation, combining both sides’ foreign keys as its primary key, plus the relationship’s own rating attribute).

Section B — Spot the mistakes

A junior admin partially mapped the same diagram:

User [id, dob, firstName, middleName, lastName]

Staff [id, dob, firstName, middleName, lastName]

Vehicles [vin, make, model]

EmergencyContact [name, userID, email, phone]
EmergencyContact.userID references User.id

Trip [userID, driverID, vin, bookingTime, startTime, endTime]
Trip.userID references User.id
Trip.driverID references Staff.id
Trip.vin references Vehicles.vin

UserRatesDriver [userID, driverID, rating]
UserRatesDriver.userID references User.id
UserRatesDriver.driverID references Driver.id

Driver [id, licence]
Driver.id references Staff.id

4WD [vin, make, model, rideHeight, wheelType]
4WD.vin references Vehicles.vin

StaffPhone [id, phone]
StaffPhone.id references Staff.id

Find 5 mistakes (ignore “missing tables” — some relations from Section A are simply left out here, and that’s not one of the 5).

  1. EmergencyContact’s primary key is missing userID. As a weak entity, its key must be (userID, name), not name alone — otherwise two different users couldn’t each have an emergency contact with the same name (e.g. two users both listing a contact named “Mum”).
  2. Trip’s primary key is missing bookingTime. Without it, a user couldn’t take two separate trips with the same driver and vehicle — bookingTime is exactly what distinguishes repeat trips between the same pair.
  3. Trip.driverID incorrectly references Staff.id instead of Driver.id. This would let an Admin-only staff ID (someone who isn’t a Driver at all) be entered as a trip’s driver, corrupting the data and undermining any audit that assumes driverID always names an actual driver.
  4. 4WD redundantly repeats make/model. These are already stored on Vehicles (the superclass) — duplicating them wastes space and risks the two copies going out of sync.
  5. StaffPhone’s primary key is missing phone. As the mapping of a multivalued attribute, the key must be (id, phone) — without phone in the key, each staff member could only ever have one phone number on file, defeating the purpose of the multivalued attribute allowing several.

Week 5

Applied Class 4: Entity Relationship Mapping

Practice for 2026-03-16-er-to-relational-mapping. See relational-mapping-notation and er-diagram-notation for reference.

Section A — Basic mapping

Question 1 — Select the correct mapping

A diagram combining several mapping rules at once: H (strong entity, key i, attribute j) and A (strong entity, key b, attributes c, d) participate in a ternary relationship K (roles: N for H, M for A, 1 for P, own attribute l) together with P (strong entity, key q, attribute r). A —1:N R1→ E (weak entity, partial key f, attribute g). A —1:1 R2— P. P —1:N S→ M (weak entity, partial key o).

H [i, j]

A [b, c, d]

E [b, f, g]
E.b references A.b

P [q, b, r]
P.b references A.b

K [b, i, q, l]
K.b references A.b
K.i references H.i
K.q references P.q

M [q, o]
M.q references P.q
  • E is a weak entity owned by A — its key is (b, f) (A’s key
    • its own partial key f).
  • R2 is a plain 1:1 relationship between A and P — merge it into one side rather than creating a separate relation for it; P is extended with A’s key as a foreign key here.
  • K is a ternary relationship — its own relation gets a foreign key to all three participants, but only the foreign keys from the many-cardinality sides (H’s N and A’s M) form the primary key; P’s foreign key (q, the 1 side) is a plain attribute, not part of the key — same pattern as the lecture’s N-ary “1:1:1 & N:1:1” worked cases.
  • M is a second weak entity, this time owned by P — key (q, o).

Question 2 — Subclasses and weak entities

F (key a, composite attribute b → c/d) has a recursive 1:N relationship R (attribute r), and disjointly (d) specialises into G (attributes i, j) and H (attribute k). H —1:N S→ X (weak entity, partial key x, attributes y, z).

(a) What’s the relationship between F, G, H, and how are G/H mapped? (b) Interpret S/X with respect to H.

(a) G and H are disjoint subclasses of F (an instance is at most one of G/H, never both). Standard subclass mapping — each subclass’s primary key is the superclass’s primary key, plus a foreign key back to it:

F [a, c, d, superF, r]
F.superF references F.a

G [a, i, j]
G.a references F.a

H [a, k]
H.a references F.a

(F.superF/r come from R, F’s own recursive 1:N relationship — mapped the same way as any binary 1:N relationship, just back onto F itself.)

(b) X is a weak entity owned by H via the identifying relationship S — each X belongs to exactly one H, but one H can own many Xs. X’s real key is (a, x) (H’s key, inherited from F, plus X’s own partial key x):

X [a, x, y, z]
X.a references H.a

Question 3 — Olympics database

Map the ER diagram: Athlete (id, sex, name, age, country) —M:N Participates (placement)→ Event (id, name, category) —N:1 Venue (id, name, address).

Athlete [id, sex, name, age, country]

Venue [id, name, address]

Event [id, name, category, venue]
Event.venue references Venue.id

Participates [athleteID, eventID, placement]
Participates.athleteID references Athlete.id
Participates.eventID references Event.id

Event—Venue is 1:N, so Event (the N side) gets a plain foreign key. Athlete—Event is M:N, so Participates is its own new relation, carrying both foreign keys plus the relationship’s own attribute placement.

Section B — Advanced mapping

Question 1 — Cinema

Map: Movie (name, description, runningTime) —1:N Viewing (date, time, multivalued Snack)←1:N Customer (id, name, email), each Viewing overseen by exactly one Attendant (staffId, name, phone).

Movie [name, description, runningTime]

Customer [id, name, email]

Attendant [staffID, name, phone]

Viewing [name, id, date, time, attendant]
Viewing.name references Movie.name
Viewing.id references Customer.id
Viewing.attendant references Attendant.staffID

ViewingSnack [name, id, date, time, snack]
ViewingSnack.{name, id, date, time} references Viewing.{name, id, date, time}

Viewing’s own key is the composite (name, id, date, time) — the movie + customer + timestamp jointly identify one viewing. Snack is multivalued, so it gets its own relation (ViewingSnack) with a composite foreign key back to the full Viewing key.

Question 2 — Student peer evaluation

Map: Student (sid, firstName, secondName) submits Assessments (sid+number); students peer-evaluate each other’s assessment submissions, recording a mark.

Student [sid, firstName, secondName]

Assessment [sid, number]
Assessment.sid references Student.sid

PeerEvaluation [studentSid, assessmentSid, number, evaluationId, sidGrades, mark]
PeerEvaluation.studentSid references Student.sid
PeerEvaluation.sidGrades references Student.sid
PeerEvaluation.{assessmentSid, number} references Assessment.{sid, number}

PeerEvaluation has two separate foreign keys to Student playing different roles — studentSid (the evaluator submitting the mark) and sidGrades (whose assessment is being marked) — plus a composite foreign key to the specific Assessment being evaluated.

Question 3 — Spot the mistakes

A junior developer mapped question 1’s Cinema diagram as:

Movie [name, description, runningTime]

Customer [id, name, email]

Attendant [staffID, name, phone]

Viewing [name, id, date, time, attendant]
Viewing.name references Movie.name
Viewing.id references Customer.id
Viewing.attendant references Attendant.staffID

ViewingSnack [name, id, date, time, snack]
ViewingSnack.name references Movie.name
ViewingSnack.id references Viewing.id
ViewingSnack.date references Viewing.date
ViewingSnack.time references Viewing.time

Find the mistakes.

ViewingSnack should reference Viewing’s composite key as one foreign key constraint (ViewingSnack.{name, id, date, time} references Viewing.{name, id, date, time}), not four separate, independently-named foreign key lines pointing at different tables (Movie for name, Viewing for id, and Viewing again for date/time, inconsistently) — splitting a composite foreign key up like this breaks the link between a ViewingSnack and the specific Viewing it belongs to (nothing actually forces all four columns to jointly match one real Viewing row), and pointing name back at Movie instead of Viewing is simply the wrong target relation for a foreign key that’s supposed to identify which viewing the snack belongs to.

Section C — EER diagram mapping

Question 1 — Medical clinic

Map: Doctor (licenceNo) splits into GP/Specialist (specialisationArea); Doctor —M:N WorksIn→ Clinic (regNo); Clinic has a subclass SpecialistClinic, run by exactly one Specialist (Runs, 1:N); Patient (patientId) —M:N Appointment (dateTime, fee)→ Doctor.

Doctor [licenceNo]

Patient [patientId]

Clinic [regNo]

GP [licenceNo]
GP.licenceNo references Doctor.licenceNo

Specialist [licenceNo, specialisationArea]
Specialist.licenceNo references Doctor.licenceNo

SpecialistClinic [regNo]
SpecialistClinic.regNo references Clinic.regNo

Appointment [licenceNo, dateTime, patientId, fee]
Appointment.licenceNo references Doctor.licenceNo
Appointment.patientId references Patient.patientId

WorksIn [licenceNo, regNo]
WorksIn.licenceNo references Doctor.licenceNo
WorksIn.regNo references Clinic.regNo

Runs [licenceNo, regNo]
Runs.licenceNo references Specialist.licenceNo
Runs.regNo references SpecialistClinic.regNo

GP/Specialist are standard disjoint-subclass mappings of Doctor. SpecialistClinic is a subclass of Clinic with no extra attributes of its own — just a foreign key back to Clinic. Runs is a 1:N relationship between Specialist and SpecialistClinic specifically (not the general Doctor/Clinic pair), so its foreign keys target the subclass relations, not the superclasses.

Week 6

Basic SQL Syntax: Data Definition and Data Manipulation Language

Module 3, part 1. Worked examples use two running schemas — a Company database and a Movie database — introduced below.

Where we are

Module 1 covered conceptual modelling (the ER diagram); Module 2 covered the relational model and ER-to-relational mapping. Module 3 is about expressing queries against a relational schema using SQL. Relational algebra (Codd’s formal query language) is the “logic engine” behind databases — precise, composable, and optimisable — but SQL is the declarative language we actually use to talk to a DBMS: you say what you want, not how to compute it. SQL is (almost) universal across Postgres, MySQL, Oracle, SQL Server, etc., and is the query interface behind tools like Power BI, Tableau, and most data-science workflows.

Example schemas used this module

Department [dNumber, dName, mgrSSN, mgrStartDate]
Employee   [ssn, name, dob, address, sex, salary, mgrSSN, dNum]
Sale       [itemID, custID, timestamp, price, salesPerson]
Item       [itemID, category, colour]
Customer   [custID, cname, gender, dob]
Movie     [movieID, title, year]
StarsIn   [movieID, starID, role]
MovieStar [starID, name, gender]

Foreign keys: Employee.mgrSSN → Employee.ssn (self-referencing — recursive relationship), Employee.dNum → Department.dNumber, Department.mgrSSN → Employee.ssn, Sale.itemID → Item.itemID, Sale.custID → Customer.custID, Sale.salesPerson → Employee.ssn, StarsIn.movieID → Movie.movieID, StarsIn.starID → MovieStar.starID.

The three (main) types of SQL statement

  • DDL (Data Definition Language) — statements that define/change the database schema (CREATE, ALTER, DROP).
  • DML (Data Manipulation Language) — statements that manipulate data (INSERT, UPDATE, DELETE, SELECT).
  • DCL (Data Control Language) — transaction control, semantic integrity (triggers/assertions), authorisation/privilege management, physical storage parameters (file structures, indexes), role-based security controls.

A relational query language should support INSERT (new tuples), DELETE (remove tuples), UPDATE (change attribute values), and SELECT (retrieve attributes/tuples/relations).

Data Definition Language (DDL)

DROP TABLE

DROP TABLE <table name> [CASCADE];

Drops all constraints defined on the table (including constraints in other tables that reference it), deletes all tuples, and removes the table definition from the system catalog.

CREATE TABLE

CREATE TABLE <table name>
   (<column name> <column type> [<attribute constraint>]
   {, <column name> <column type> [<attribute constraint>]}
   [<table constraint> {, <table constraint>}])

Notation used throughout this module: KEYWORD, <argument>, [optional], {repeatable}, …|choice|…. Key/entity/referential integrity constraints are specified after the attributes are declared; domain constraints are specified per-attribute (directly, or via a CREATE DOMAIN).

Entity table example — Item [itemID, category, colour]:

CREATE TABLE Item (
    itemID   INTEGER,
    category ENUM('food', 'clothing', 'furniture'),
    colour   CHAR(3),
    PRIMARY KEY (itemID));

Relationship table example — Sale [itemID, custID, timestamp, price, salesPerson], a ternary-degree relation between Item, Customer and Employee:

CREATE TABLE Sale (
    itemID      INTEGER,
    custID      INTEGER,
    timestamp   TIMESTAMP,
    price       DOUBLE(8, 2),
    salesPerson CHAR(9),
    PRIMARY KEY (itemID, custID, timestamp),
    FOREIGN KEY (itemID) REFERENCES Item(itemID),
    FOREIGN KEY (custID) REFERENCES Customer(custID),
    FOREIGN KEY (salesPerson) REFERENCES Employee(ssn));

Constraints

Constraints are rules that limit what data can go into a table:

  • PRIMARY KEY — attribute value is unique and not null.
  • FOREIGN KEY — attribute value must exist in the referenced (parent) table.
  • CHECK — attribute value(s) must satisfy a predefined condition.
  • UNIQUE — attribute value is unique or null (unlike a primary key).

CHECK constraints are semantic constraints over a single table, evaluated whenever tuples are inserted or modified:

CREATE TABLE Sale (
    itemID      INTEGER,
    custID      INTEGER,
    timestamp   TIMESTAMP,
    price       DOUBLE(8, 2),
    salesPerson CHAR(9),
    PRIMARY KEY (itemID, custID, timestamp),
    FOREIGN KEY (itemID) REFERENCES Item(itemID),
    FOREIGN KEY (custID) REFERENCES Customer(custID),
    FOREIGN KEY (salesPerson) REFERENCES Employee(ssn),
    CHECK (price >= 8.50 AND price < 150000));

Naming constraints

CREATE TABLE Item (
    itemID   INTEGER,
    category ENUM('food', 'clothing', 'furniture'),
    colour   CHAR(3),
    CONSTRAINT item_pk PRIMARY KEY (itemID));

Giving a constraint a name has two benefits: (1) clearer error messages — a violation names the constraint; (2) the constraint can be modified/removed later, e.g. ALTER TABLE Item DROP CONSTRAINT item_pk;.

Question 1 — DDL constraints

Which SQL statement correctly implements Student [id, firstName, lastName] where {firstName, lastName} is unique?

-- A
CREATE TABLE Student (
    id INT NOT NULL, firstName VARCHAR(50), lastName VARCHAR(50),
    PRIMARY KEY (id));

-- B
CREATE TABLE Student (
    id INT NOT NULL, firstName VARCHAR(50), lastName VARCHAR(50),
    PRIMARY KEY (firstName, lastName));

-- C
CREATE TABLE Student (
    id INT NOT NULL, firstName VARCHAR(50), lastName VARCHAR(50),
    PRIMARY KEY (id), PRIMARY KEY (firstName, lastName));

-- D
CREATE TABLE Student (
    id INT NOT NULL, firstName VARCHAR(50), lastName VARCHAR(50),
    PRIMARY KEY (id), UNIQUE(firstName, lastName));

D. A has no uniqueness constraint on {firstName, lastName} at all. B makes {firstName, lastName} the primary key instead of id (wrong key). C is invalid SQL — a table cannot declare two PRIMARY KEY clauses. D correctly keeps id as the primary key and adds a UNIQUE constraint over {firstName, lastName}.

Question 2 — Designing with referential integrity

Given Student [studentID, name, advisorID], Professor [professorID, name], Student.advisorID references Professor.professorID — which statement best explains the design rationale for using ON DELETE SET NULL on advisorID?

A. It automatically reassigns students to a new advisor. B. It prevents the professor’s deletion unless all advisees are manually updated. C. It allows deletion of a professor without losing student records, marking that they no longer have an advisor. D. It deletes all students advised by the professor.

C. ON DELETE SET NULL preserves student records and referential integrity by clearing the advisor reference — it does not reassign (A), does not block deletion (B, that’s RESTRICT), and does not delete students (D, that’s CASCADE).

Question 3 — Implementing referential integrity

CREATE TABLE ParkingPermit (
    pID     INTEGER,
    staffID INTEGER,
    PRIMARY KEY (pID),
    FOREIGN KEY (staffID) REFERENCES Staff(staffID) ON DELETE CASCADE);

Given a row pID = 1000, staffID = 5678 — which is correct? A. Deleting pID = 1000 cascades to delete staffID = 5678 in Staff. B. Deleting staffID = 5678 in Staff cascades to delete matching rows in ParkingPermit. C. Both A and B. D. None of the above.

B. ON DELETE CASCADE only propagates from the referenced (parent) table to the referencing (child) table — deleting the Staff row cascades to ParkingPermit. It never works in reverse (deleting a ParkingPermit row has no defined effect on Staff).

ALTER TABLE

ALTER TABLE <table name>
    ADD <column name> <column type> [<attribute constraint>]
        {, <column name> <column type> [<attribute constraint>]}
  | DROP <column name> [CASCADE]
  | MODIFY <column name> <column-options>
  | ADD <constraint name> <constraint-options>
  | DROP <constraint name> [CASCADE];

Used for schema evolution — adding/dropping columns, changing a column definition, adding/dropping constraints. To alter a constraint, it must be dropped and re-added (commercial products vary in exact syntax).

-- Add an attribute (existing rows get NULL, so NOT NULL can't be used)
ALTER TABLE Employee ADD job VARCHAR(12);

-- Drop an attribute (CASCADE if other tables' FKs reference it)
ALTER TABLE Employee DROP address;

-- Add a constraint
ALTER TABLE Sale ADD CONSTRAINT ChkAge CHECK (price BETWEEN 10 AND 10000);

-- Drop a constraint (must have been named)
ALTER TABLE Sale DROP CONSTRAINT itemID_fk;

Cyclical foreign key dependencies

Department.mgrSSN → Employee.ssn and Employee.dNum → Department.dNumber form a cycle — you can’t create either table first with its foreign keys in place, since the other table doesn’t exist yet. Solution: create both tables without the foreign keys, insert the data, then ALTER TABLE to add the foreign keys afterwards.

CREATE Department (without FKs)
CREATE Employee (without FKs)
INSERT data into Department
INSERT data into Employee
ALTER TABLE Department ADD FOREIGN KEY (mgrSSN) REFERENCES Employee(ssn)
ALTER TABLE Employee ADD FOREIGN KEY (dNum) REFERENCES Department(dNumber)

Data Manipulation Language (DML)

Four statements: INSERT (add tuples), UPDATE (modify existing data), DELETE (remove tuples), SELECT (retrieve data).

INSERT

INSERT INTO <table name>
    [(<column name> {, <column name>})]
    (VALUES (<constant value> {, <constant value>}) | <select statement>);

A single-tuple insert lists values in the same order as the columns were declared (or the explicit column list, if given). A multi-tuple insert either comma-separates several value-lists, or loads the result of a query.

-- Insert from values
INSERT INTO Customer VALUES
    ('653298653', 'Ronald West', 'Male', '1995-12-30');

-- Insert from a query: create a customer account for every
-- employee in department 1
INSERT INTO Customer (custID, cname, gender, dob)
    SELECT ssn, name, sex, dob
    FROM   Employee
    WHERE  dNum = 1;

DELETE

DELETE FROM <table name>
    [WHERE <select condition>];

A single DELETE can remove zero, one, several, or all tuples from one table. Deletion may propagate to other tables if ON DELETE referential-triggered actions were declared.

DELETE FROM Employee
    WHERE name = 'Ramesh';

UPDATE

UPDATE <table name>
    SET <column name> = <value expression> {, <column name> = <value expression>}
    [WHERE <select condition>];

Tuples are selected for update from a single table; updating a primary key value may propagate to other tables (referencing foreign keys).

UPDATE Employee
    SET salary = salary * 1.1
    WHERE name = 'Joyce';

Question 4 — Correct use of DELETE

Given a Student [id, fName, lName, degree] table, which query deletes exactly the CompSci students except id = 4?

A. DELETE FROM Student WHERE id = 3 AND id = 5 AND id = 12 B. DELETE FROM Student WHERE fName = 'Diluen' OR fName = 'Peter' OR fName = 'Jason' C. DELETE FROM Student WHERE degree = 'CompSci' D. DELETE FROM Student WHERE degree = 'CompSci' AND NOT id = 4

D. A is a contradiction (id can never equal three different values at once — nothing is deleted). B also incidentally deletes a non-CompSci student (Peter Park, id=10, is not CompSci). C deletes id = 4 too, which we want to keep. D correctly restricts to degree = 'CompSci' AND NOT id = 4.

Basic SELECT

SELECT <attribute list>
FROM   <table list>
[WHERE <condition>];

SELECT is declarative — you specify what the result should look like, and the DBMS decides the execution plan. The result of any SQL query is itself a table (relation).

Projection

SELECT [DISTINCT] (<attribute list> | *)
FROM   <table list>
[WHERE <condition>];

SQL relations are bags/multisets, not sets — duplicates are not eliminated by default. DISTINCT eliminates duplicates and enforces set semantics; * is a wildcard for “all columns”.

-- Find the titles of movies
SELECT title
FROM   Movie;

-- Find all the years a movie was produced (with vs without duplicates)
SELECT year FROM Movie;
SELECT DISTINCT year FROM Movie;

Question 5 — SQL projection

Given Scores [team1, team2, score1, score2] with rows (Dragons, Tigers, 5, 3), (Carp, Swallows, 4, 6), (Bay Stars, Giants, 2, 1), (Marines, Hawks, 5, 3), (Ham Fighters, Buffaloes, 1, 6), (Lions, Golden Eagles, 8, 12) — for SELECT score1, score2 FROM Scores, which tuple is in the result?

A. (1,2) B. (5,3) C. (8,6) D. All are in the answer E. None are in the answer

B. (5, 3) appears twice in the source table (Dragons vs Tigers, Marines vs Hawks) — projection just doesn’t eliminate the duplicate by default.

Question 6 — SQL projection with DISTINCT

Same table as Question 5. For SELECT DISTINCT score1, score2 FROM Scores, how many tuples are in the output?

A. 6 B. 5 C. 4 D. 3

B. Six rows exist, but (5, 3) is duplicated — DISTINCT removes one copy, leaving 5 unique tuples.

Projection with expressions

Expressions can use standard arithmetic operators (+ - * /) on numeric attributes, and can be given an alias with AS.

-- Names, salaries, and salaries with a 17% loading, for dept 6
SELECT name, salary, 1.17 * salary AS 'includingSuper'
FROM   Employee
WHERE  dNum = 6;

Selection (WHERE clause)

SELECT <attribute list>
FROM   <table list>
[WHERE search condition];
-- Find all the male stars
SELECT *
FROM   MovieStar
WHERE  gender = 'Male';

-- Names of employees in dept 4 earning > 25000, or dept 5 earning > 30000
SELECT name
FROM   Employee
WHERE  (dNum = 4 AND salary > 25000) OR (dNum = 5 AND salary > 30000);

Question 7 — Selection

Given a Scores [team, opponent, runsFor, runsAgainst] table, for

SELECT *
FROM   Scores
WHERE  (runsFor >= 6 AND runsAgainst <= 4) OR (runsFor < 3 AND opponent = 'Giants');

which rows are in the result? A. Swallows vs Carp, 6-4 B. Buffaloes vs Ham Fighters, 6-1 C. Lions vs Golden Eagles, 8-12 D. A and B

D. Swallows vs Carp (runsFor=6, runsAgainst=4) satisfies the first clause (6>=6 AND 4<=4). Buffaloes vs Ham Fighters (runsFor=6, runsAgainst=1) also satisfies the first clause (6>=6 AND 1<=4). Lions vs Golden Eagles (8, 12) fails both clauses (12<=4 is false, and 8<3 is false) — it’s the archetypal “bigger numbers, so it must match” trap.

Complex WHERE conditions

  • LIKE — string matching: % = zero-or-more arbitrary characters, _ = any one character. WHERE title LIKE '%sin%' finds titles containing “sin” anywhere.
  • IN — membership in a list: WHERE lastName IN ('Jones', 'Wong', 'Harrison').
  • IS — null-checking (= doesn’t work with NULL): WHERE dNum IS NULL.
  • Arithmetic/date functions, BETWEEN: WHERE salary BETWEEN 10000 AND 30000.
-- Names of employees in a "Research" department earning 40-60K
SELECT name
FROM   Employee
JOIN   Department ON dNum = dNumber
WHERE  dName LIKE '%Research%' AND salary BETWEEN 40000 AND 60000;

(Multi-relation JOIN queries like this are covered properly next lecture.)

Sorting (ORDER BY)

SELECT [DISTINCT] <target list>
FROM   <table list>
[WHERE search condition]
[ORDER BY column [ASC|DESC] {, column [ASC|DESC]}];

The target list can be a column name, expression, or *; column can be a name or a position in the target list; sorting can use multiple columns.

-- Employee names/salaries, ordered by salary descending
SELECT name, salary
FROM   Employee
ORDER BY salary DESC;

-- Employee names/depts/salaries, by dept ascending then salary descending
SELECT dNum, name, salary
FROM   Employee
ORDER BY dNum ASC, salary DESC;

Question 8 — Sorting

For SELECT a, b, c FROM R ORDER BY c DESC, b ASC, which tuple t necessarily precedes (5, 5, 5)?

A. (3, 6, 3) B. (1, 5, 5) C. (5, 5, 6) D. All of the above

C. Sorting is primarily by c DESC. (5,5,6) has c=6 > 5, so it sorts strictly before (5,5,5) regardless of a/b. (3,6,3) has c=3 < 5, so it comes after. (1,5,5) ties on c=5 with (5,5,5), so the tie-break (b ASC) applies — both have b=5, so it’s still a tie, and the ordering between them is unspecified (not “necessarily precedes”).

Summary

You should now be able to create, alter and drop relations, enforce integrity constraints, and perform basic insert/update/delete/select operations in SQL. Next lecture: aggregation, grouping, and querying across multiple relations. See week6-tutorial-applied-class-5-basic-sql-ddl-and-dml and week6-tutorial-case-study-4-easydrive-insurance for practice.

Applied Class 5: Basic SQL syntax, DDL and DML

Practice for 2026-03-30-basic-sql-ddl-and-dml.

Schema — BestTechLtd

BestTechLtd have built a simplistic authorisation management system for secure access control within their organisational network. When a new employee joins, their personal details are stored in Employee. Administrative employees (a specific type of employee, recorded additionally in AdministrativeEmployee) can grant roles to employees through a role-granting process. Each Role comes with specific Permissions that determine which company websites/resources an employee holding that role can access.

Employee             [EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt]
AdministrativeEmployee [EmployeeID, Level, Type]
Role                  [RoleID, Name, Description]
RoleGranting          [EmployeeID, RoleID, AdministrationID, Timestamp]
Permission            [WebsiteURI, RoleID, GrantType, Description]

AdministrativeEmployee.EmployeeID references Employee.EmployeeID
RoleGranting.EmployeeID references Employee.EmployeeID
RoleGranting.RoleID references Role.RoleID
RoleGranting.AdministrationID references AdministrativeEmployee.EmployeeID
Permission.RoleID references Role.RoleID

Section 1 — Basic SQL

Question 1

Return all information (all columns) relating to the Roles recorded in the database.

SELECT * FROM Role;

Question 2

Return the distinct first name and last name of all employees, in descending alphabetical order of their last name.

SELECT DISTINCT FirstName, LastName
FROM   Employee
ORDER BY LastName DESC;

Question 3

Return all the Permission websiteURIs which grant edit permissions to commercial websites (websites ending in .com).

SELECT WebsiteURI
FROM   Permission
WHERE  GrantType = 'Edit'
  AND  WebsiteURI LIKE '%.com';

Question 4 (Challenge)

Create an Employee code, which is the combination of the employee’s first name, last name, and DOB year with syntax [FirstName]-[Lastname]-[yyyy]. Create the code only if a first name, last name, and Date of Birth is present.

SELECT CONCAT(FirstName, '-', LastName, '-', YEAR(DOB))
FROM   Employee
WHERE  1 = 1
  AND  FirstName IS NOT NULL
  AND  LastName IS NOT NULL
  AND  DOB IS NOT NULL;

Section 2 — Data Definition Language (DDL)

Question 5

Before BestTechLtd created their authorisation system, employees were assigned only a single role, plus a unique access token granting access for that role. Create a new relation LegacyEmployee, a specialised type of employee (stored in a separate relation), with:

  • LegacyEmployeeID: a unique reference to the EmployeeID stored in the authorisation database.
  • GrantAccessToken: a string of exactly 64 characters granting access to old websites.
  • RoleID: a reference to the legacy employee’s original role. This reference cannot be null.
CREATE TABLE LegacyEmployee (
    LegacyEmployeeID VARCHAR(8) PRIMARY KEY,
    GrantAccessToken CHAR(64) NOT NULL,
    RoleID           INT NOT NULL,
    FOREIGN KEY (LegacyEmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (RoleID) REFERENCES Role(RoleID)
);

LegacyEmployee is a specialisation of Employee (recall relational-mapping-notation’s Rule 8b: a 1:1 relation with the subclass’s primary key also being a foreign key referencing the superclass).

Question 6 (Challenge)

BestTechLtd want to migrate password credentials from Employee into a separate Credential relation. Create the new table (fields in any order), assuming there is no existing data, and remove the migrated fields from Employee.

CREATE TABLE Credential (
    CredentialID VARCHAR(9) PRIMARY KEY,
    EmployeeID   VARCHAR(8) NOT NULL,
    PasswordHash VARCHAR(128) NOT NULL,
    PasswordSalt VARCHAR(64) NOT NULL,
    Timestamp    DATETIME,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

ALTER TABLE Employee
    DROP COLUMN PasswordHash,
    DROP COLUMN PasswordSalt;

Section 3 — Data Manipulation Language (DML)

Question 7

Revoke all roles granted to EmployeeID E0008.

DELETE FROM RoleGranting
WHERE  EmployeeID = 'E0008';

Question 8

Revoke the role(s) granted to John Stevens at any time during 22 July 2024. Assume such a role exists and only one John Stevens exists.

DELETE FROM RoleGranting
WHERE  EmployeeID = (
    SELECT EmployeeID FROM Employee
    WHERE  FirstName = 'John' AND LastName = 'Stevens'
)
AND    Timestamp BETWEEN '2024-07-22 00:00:00' AND '2024-07-22 23:59:59';

Question 9 (Challenge)

A new employee E0024, James Moran, born 21 November 2001, with a given password hash and salt, is granted all the roles that employee Sofia Gonzalez has, granted by admin E0001 today. Insert the new records (assume only one Sofia Gonzalez exists).

INSERT INTO Employee (EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt)
VALUES ('E0024', 'James', 'Moran', '2001-11-21',
        'e7cf3ef8d8aac2c1c93963e7a58b7b62ade24d0d0ba2c8ae0f7fb6c8b0aa0332',
        'gmAlpLW8cUj');

INSERT INTO RoleGranting
SELECT 'E0024', RoleID, 'E0001', Timestamp
FROM   RoleGranting
WHERE  EmployeeID = (
    SELECT EmployeeID
    FROM   Employee
    WHERE  FirstName = 'Sofia' AND LastName = 'Gonzalez'
);

Case Study 4: EasyDrive Insurance (DDL & DML)

Practice for 2026-03-30-basic-sql-ddl-and-dml. This case study builds practical experience implementing a database in phpMyAdmin (a GUI for MySQL) — running real DDL/DML, and learning to break-test a schema and check edge cases.

Correspondence 1

Sarah Chen, Head of Technology Operations at EasyDrive Insurance (a direct-to-consumer car insurer), emails asking for help migrating their schema to MySQL/phpMyAdmin. When a customer signs up they create a profile (Customer, Address), then may purchase a Policy for a Vehicle, insuring it over consecutive years.

Relational schema:

Customer    [CustomerID, Name, DateOfBirth, Email, Occupation, AddressID]
Address     [AddressID, StreetName, Number, Suburb, Postcode, State, Country]
Vehicle     [VehicleID, VehicleCode, VehiclePurpose, EstYearlyKm]
VehicleType [VehicleCode, Make, Model, Year, MarketValue]
Policy      [PolicyID, CustomerID, VehicleID, PolicyStartYear, PolicyPurchaseDate, Excess, Premium]

Customer.AddressID references Address.AddressID
Policy.VehicleID references Vehicle.VehicleID
Policy.CustomerID references Customer.CustomerID
Vehicle.VehicleCode references VehicleType.VehicleCode

Data types (as specified by Sarah):

  • Address: AddressID (≤4 chars), StreetName (≤255), Number (≤10, to allow e.g. "4/12"), Suburb (≤255), Postcode (≤10), State (≤10), Country (≤50).
  • Customer: CustomerID (integer), Name (≤255), DateOfBirth (date), Email (≤255), Occupation (≤255), AddressID (≤4).
  • VehicleType: a reference table mapping a VehicleCode (≤50) to Make/Model/Year — the combination of Make, Model, Year must be unique, since each distinct vehicle configuration maps to exactly one code (used to determine MarketValue, the accident payout).
  • Vehicle: VehicleID (≤6), VehicleCode (references VehicleType), VehiclePurpose (only 'Private' or 'Business'), EstYearlyKm (integer).
  • Policy: PolicyID (≤6), CustomerID (integer), VehicleID (≤6), PolicyStartYear (integer), PolicyPurchaseDate (date), Excess (integer), Premium (decimal, 2 dp, ≤10 digits total).

Section A — Data Definition Language

Using phpMyAdmin, create a new database EasyDrive and implement the five tables above, with all data types/keys/constraints as described.

CREATE DATABASE EasyDriveInsurance;
USE EasyDriveInsurance;

CREATE TABLE Address (
    AddressID  VARCHAR(4),
    StreetName VARCHAR(255),
    Number     VARCHAR(10),
    Suburb     VARCHAR(255),
    Postcode   VARCHAR(10),
    State      VARCHAR(10),
    Country    VARCHAR(50),
    PRIMARY KEY (AddressID)
);

CREATE TABLE Customer (
    CustomerID  INT,
    Name        VARCHAR(255),
    DateOfBirth DATE,
    Email       VARCHAR(255),
    Occupation  VARCHAR(255),
    AddressID   VARCHAR(4),
    PRIMARY KEY (CustomerID),
    FOREIGN KEY (AddressID) REFERENCES Address(AddressID)
);

CREATE TABLE VehicleType (
    VehicleCode VARCHAR(50),
    Make        VARCHAR(50),
    Model       VARCHAR(50),
    Year        INT,
    PRIMARY KEY (VehicleCode),
    UNIQUE (Make, Model, Year)
);

CREATE TABLE Vehicle (
    VehicleID      VARCHAR(6),
    VehicleCode    VARCHAR(50),
    VehiclePurpose ENUM('Private', 'Business'),
    EstYearlyKm    INT,
    PRIMARY KEY (VehicleID),
    FOREIGN KEY (VehicleCode) REFERENCES VehicleType(VehicleCode)
);

CREATE TABLE Policy (
    PolicyID           VARCHAR(6),
    CustomerID         INT,
    VehicleID          VARCHAR(6),
    PolicyStartYear    INT,
    PolicyPurchaseDate DATE,
    Excess             INT,
    Premium            DECIMAL(10, 2),
    PRIMARY KEY (PolicyID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
);

Note: modern AI tools can draft CREATE TABLE statements from a schema description, but the draft must be checked carefully against the description before running it — AI-generated SQL can contain errors or omissions (see 2026-04-13-nested-queries-views-and-generative-ai’s “Generative AI & SQL” section).

Section B — Analysis of Data Manipulation Language

Sarah asks the students to run five INSERT operations, in order, and record for each: whether it succeeded/failed, the error message (if any), and which integrity constraint was violated and why.

-- Operation 1
INSERT INTO Address (AddressID, StreetName, Number, Suburb, Postcode, State, Country)
VALUES ('A001', 'Main Street', '12', 'Brisbane', '4000', 'QLD', 'Australia');

-- Operation 2
INSERT INTO Customer (CustomerID, Name, DateOfBirth, Email, Occupation, AddressID)
VALUES (1001, 'Allan Smith', '2000-01-01', 'allan@uq.edu.au', 'Student', 'A999');

-- Operation 3
INSERT INTO Customer (CustomerID, Name, DateOfBirth, Email, Occupation, AddressID)
VALUES (1001, 'Rebecca Johnson', '1995-06-15', 'rebecca@gmail.com', 'Engineer', 'A001');

-- Operation 4
INSERT INTO VehicleType (VehicleCode, Make, Model, Year)
VALUES ('VT001', 'Toyota', 'Corolla', 2020);
INSERT INTO Vehicle (VehicleID, VehicleCode, VehiclePurpose, EstYearlyKm)
VALUES ('V00001', 'VT001', 'Personal', 15000);

-- Operation 5
INSERT INTO Policy (PolicyID, CustomerID, VehicleID, PolicyStartYear, PolicyPurchaseDate, Excess, Premium)
VALUES ('P00001', 1001, 'V00001', 2024, '2024-03-01', 500, 1200.00);
Op Result Error / constraint
1 Succeeded —
2 Failed #1452 - Cannot add or update a child row: a foreign key constraint fails. Referential integrity — AddressID = 'A999' doesn’t exist in Address.
3 Succeeded — (a different row, correctly referencing A001; note it reuses CustomerID = 1001 from the failed Op 2, which is fine since Op 2 never committed)
4 Failed #1265 - Data truncated for column 'VehiclePurpose' at row 1. Domain constraint — 'Personal' is not a valid VehiclePurpose value (only 'Private'/'Business').
5 Failed #1452 - ... FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID). Referential integrity — since Op 4 failed, VehicleID = 'V00001' was never inserted into Vehicle.

This is a good illustration of cascading failure: a single domain violation in Op 4 also causes Op 5 to fail, even though Op 5’s own values look superficially fine.

Correspondence 2 (Challenge)

Sarah’s team requests further changes now the core schema is in place:

  1. A new InsuranceClaim table: an auto-incrementing claim ID; a reference to the Policy the claim is against (claims should be automatically deleted if their policy is deleted); the incident date/time; the claimed amount (2 dp decimal); a status restricted to 'Pending', 'Approved', or 'Rejected'; and a description (≤255 chars).
  2. Remove the Occupation column from Customer (no longer collected).
  3. Add two constraints to Customer: a named UniqueEmail uniqueness constraint on Email, and a check constraint requiring customers to be at least 18 years old (based on DateOfBirth).

Task 4 — Creating InsuranceClaim

CREATE TABLE InsuranceClaim (
    ClaimID          INT AUTO_INCREMENT,
    PolicyID         VARCHAR(6),
    ClaimDate        DATE,
    ClaimAmount      DECIMAL(10, 2),
    ClaimStatus      ENUM('Pending', 'Approved', 'Rejected'),
    ClaimDescription VARCHAR(255),
    PRIMARY KEY (ClaimID),
    FOREIGN KEY (PolicyID) REFERENCES Policy(PolicyID) ON DELETE CASCADE
);

Task 5 — Removing Occupation

ALTER TABLE Customer DROP COLUMN Occupation;

Task 6 — Fraud prevention constraints

ALTER TABLE Customer
    ADD CONSTRAINT UniqueEmail UNIQUE (Email);

ALTER TABLE Customer
    ADD CONSTRAINT AgeOver CHECK (
        TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) >= 18
    );

Week 7

Aggregation, Grouping and Multiple Relation Queries

Module 3, part 2. Continues 2026-03-30-basic-sql-ddl-and-dml, using the same Company (Department/Employee/Sale/Item/Customer) and Movie (Movie/StarsIn/MovieStar) schemas.

Today’s outline

  • Aggregation and grouping (COUNT/SUM/AVG/MAX/MIN, GROUP BY, HAVING)
  • Multiple relation queries — renaming, joins, set operations

Aggregation

Aggregates are functions that produce a summary value from a set of tuples:

  • COUNT — counts the number of tuples returned.
  • SUM/AVG — sum/average of a set of numeric values.
  • MAX/MIN — maximum/minimum value from a set with a total ordering (the domain doesn’t have to be numeric — e.g. dates, strings).

Aggregation functions can be used in the SELECT clause and the HAVING clause (below), with or without DISTINCT.

-- Total and average salary of all employees
SELECT AVG(salary), SUM(salary)
FROM   Employee;

-- Total number of employees in department 5
SELECT COUNT(*)
FROM   Employee
WHERE  dNum = 5;

-- Distinct salary count of employees in department 5
SELECT COUNT(DISTINCT salary)
FROM   Employee
WHERE  dNum = 5;

Grouping (GROUP BY)

An aggregate can apply to the whole table, but is often needed per group of rows — e.g. “total employees per department”, “average salary per department”. GROUP BY provides this.

SELECT [DISTINCT] <target list>
FROM   <table list>
[WHERE search condition]
[GROUP BY <grouping attributes>]
[HAVING <group conditions>]
[ORDER BY column [ASC|DESC] {, column [ASC|DESC]}];

Rule: any attribute in the SELECT clause must either appear in GROUP BY, or be wrapped in an aggregation function.

-- Total employees (no grouping)
SELECT COUNT(*) FROM Employee;                    -- 8

-- Total employees per department
SELECT dNum, COUNT(*)
FROM   Employee
GROUP BY dNum;
--  dNum | COUNT(*)
--     1 |    1
--     4 |    3
--     5 |    4

-- Total employees earning > 40000, per department
SELECT dNum, COUNT(*)
FROM   Employee
WHERE  salary > 40000
GROUP BY dNum;

-- Average salary per department, per gender
SELECT dNum, sex, AVG(salary)
FROM   Employee
GROUP BY dNum, sex;

WHERE filters rows before grouping; GROUP BY then buckets the remaining rows.

Question 9 — INSERT and GROUP BY

Given Students [id, fName, lName, degree], what does INSERT INTO DegreeStatistics (degree, num) SELECT degree, COUNT(*) FROM Students GROUP BY degree insert?

A. (Arts, 2), (CompSci, 3) B. (Math, 1), (Arts, 2), (CompSci, 3) C. (Math, 1), (Arts, 8), (CompSci, 12) D. None of the above

B. Every distinct degree value gets its own group and count — missing Math from option A is the trap (there’s no WHERE clause excluding it). C looks like SUM(id) was used instead of COUNT(*).

HAVING — conditions on groups

HAVING (following GROUP BY) filters groups, the way WHERE filters rows — and unlike WHERE, HAVING can include aggregates. Any attribute in HAVING must appear in GROUP BY or be aggregated.

-- Total employees, for departments with more than 2 employees
SELECT   dNum, COUNT(*)
FROM     Employee
GROUP BY dNum
HAVING   COUNT(*) > 2;

-- Departments with more than 2 employees earning > 20000
SELECT   dNum, COUNT(*)
FROM     Employee
WHERE    salary > 20000
GROUP BY dNum
HAVING   COUNT(*) > 2;

In the second example, WHERE and HAVING both operate on “employees earning > 20000” — contrast this with the correlated-subquery version of the same idea, in 2026-04-13-nested-queries-views-and-generative-ai, where HAVING checks a different condition across all employees via a subquery.

Question 10 — HAVING clause

For SELECT team, COUNT(*) AS games_played, SUM(runsFor) AS total_runs FROM Scores GROUP BY team HAVING SUM(runsFor) > 10, what is the HAVING clause doing?

A. Removes rows where runsFor <= 10 before grouping. B. Filters rows before aggregation. C. Limits which teams are included, based on their total runs scored. D. Replaces WHERE for simple conditions. E. Counts opponents that allowed more than 10 runs.

C. HAVING filters groups (here, teams) by an aggregate condition computed after grouping — it doesn’t touch individual rows before grouping (that’s what WHERE would do).

Question 11 — HAVING clause application

Given Employee [id, dNum, sex, salary] with rows (1,5,M,30000), (2,5,M,40000), (3,5,F,25000), (4,5,M,38000), (5,1,M,55000), for SELECT dNum, sex, COUNT(*) FROM Employee GROUP BY dNum, sex HAVING …, which HAVING clause produces a different result from the others?

A. HAVING COUNT(*) < 2 B. HAVING AVG(salary) = 25000 C. HAVING MAX(salary) <= MIN(salary) D. HAVING SUM(salary) < 60000

B. Groups are (5,M) [3 rows: 30000/40000/38000], (5,F) [1 row: 25000], (1,M) [1 row: 55000]. A, C and D all select the same two singleton groups (5,F,1) and (1,M,1) (both have COUNT(*)=1<2; both trivially satisfy MAX<=MIN since there’s one value; both have SUM<60000). B selects only (5,F,1), since its average is exactly 25000 but (1,M)’s average is 55000 — a different result set.

Multiple relation queries

Three tools for combining relations: renaming, joins, and set operations (union/intersect/difference).

Renaming

Two ways to rename: qualifying attribute names (table.attribute), or declaring an alias (AS). Renaming removes ambiguity and enables self-joins.

-- Names/salaries/salaries+17% for dept 4
SELECT E.name, salary, 1.17 * salary AS 'includingSuper'
FROM   Employee E
WHERE  dNum = 4;

Cartesian product

R1 × R2: every row of R1 combined with every row of R2; the result schema is the concatenation of both schemas. If |R1| = m and |R2| = n, then |R1 × R2| = m * n.

SELECT *
FROM   MovieStar, StarsIn;

Every MovieStar row is paired with every StarsIn row — almost always not what you want (compare with a join, below).

Equi-join and joins generally

An equi-join combines tuples from two relations that agree on some pair of attributes (a join condition using only =). A join in general combines related tuples into a single result relation:

SELECT <attribute list>
FROM   <table> {<type of join> JOIN <table to join to> ON <join attributes>}
[WHERE search condition]

-- equivalently, using WHERE (Cartesian product + join condition):
SELECT <attribute list>
FROM   <table list of more than one table>
[WHERE join condition AND search condition]
-- Names of the managers of each department
SELECT E.name, D.dName
FROM   Department AS D, Employee AS E
WHERE  D.mgrSSN = E.ssn;

-- equivalent, using JOIN
SELECT E.name, D.dName
FROM   Department AS D
JOIN   Employee AS E ON D.mgrSSN = E.ssn;

JOIN/ON is generally preferred over the WHERE-based Cartesian product form because it can be more efficiently optimised by the DBMS — but for this course, they’re treated as equivalent.

-- IDs and names of all stars who've been in a movie
SELECT DISTINCT S.starID, name
FROM   StarsIn S
JOIN   MovieStar MS ON S.starID = MS.starID;

-- IDs, names and characters of stars in the movie with movieID 1
SELECT S.starID, name, role
FROM   StarsIn S
JOIN   MovieStar MS ON S.starID = MS.starID
WHERE  S.movieID = 1;

-- Multi-join: stars in "Gone with the Wind"
SELECT S.starID, MS.name, S.role, M.title
FROM   StarsIn S
JOIN   MovieStar MS ON S.starID = MS.starID
JOIN   Movie M ON S.movieID = M.movieID
WHERE  M.title LIKE 'Gone with the Wind';

Question 12 — SQL joins

Which query correctly returns employee names and the name of the department they work in?

A. SELECT e.name, d.dName FROM Employee e, Department d WHERE e.mgrSSN = d.mgrSSN; B. SELECT e.name, d.dName FROM Employee e JOIN Department d ON e.ssn = d.mgrSSN; C. SELECT e.name, d.dName FROM Department d JOIN Employee e ON d.mgrSSN = e.mgrSSN; D. SELECT e.name, d.dName FROM Employee e JOIN Department d ON e.dNum = d.dNumber;

D. A joins on shared mgrSSN values (matching managers to managers, not employees to their department). B matches an employee’s ssn to a department’s mgrSSN — that’s “departments this employee manages”, not “the department they work in”. C is the same mismatch as B, reversed. D correctly joins each employee’s dNum to the department’s dNumber — “the department they work in”.

Self-joins (recursive relationships)

-- Employees who earn less than their manager
SELECT   A.name AS employee, A.salary AS employeeSalary,
         B.name AS manager, B.salary AS managerSalary
FROM     Employee A
JOIN     Employee B ON A.mgrSSN = B.ssn
WHERE    A.salary < B.salary;

Question 13 — Grouping with join

Given Flight [FlightNo, Origin, Destination], for

SELECT F1.Origin, F2.Destination, COUNT(*)
FROM   Flight F1, Flight F2
WHERE  F1.Destination = F2.Origin
GROUP BY F1.Origin, F2.Destination

(read as: “how many ways can you connect from F1.Origin to F2.Destination via one stopover?”) which is in the result?

A. (Brisbane, Melbourne, 2) B. (Perth, Sydney, 6) C. (Perth, Melbourne, 1) D. All of the above E. None of the above

D — all of the above. The self-join pairs every F1 flight with every F2 flight departing from where F1 lands, then groups by (F1.Origin, F2.Destination) and counts the pairs. Working through all nine flights (2× Brisbane→Sydney, 1× Sydney→Melbourne, 2× Melbourne→Perth, 3× Perth→Brisbane, 1× Perth→Sydney) gives:

F1.Origin F2.Destination COUNT(*) Why
Brisbane Melbourne 2 2 Brisbane→Sydney flights × 1 Sydney→Melbourne flight
Sydney Perth 2 1 Sydney→Melbourne flight × 2 Melbourne→Perth flights
Melbourne Brisbane 6 2 Melbourne→Perth flights × 3 Perth→Brisbane flights
Melbourne Sydney 2 2 Melbourne→Perth flights × 1 Perth→Sydney flight
Perth Sydney 6 3 Perth→Brisbane flights × 2 Brisbane→Sydney flights
Perth Melbourne 1 1 Perth→Sydney flight × 1 Sydney→Melbourne flight

(Brisbane, Melbourne, 2), (Perth, Sydney, 6) and (Perth, Melbourne, 1) are all genuinely in the result — so A, B and C are all correct.

Theta-join

The most general join type — the join condition can use any of {=, ≠, <, ≤, >, ≥}, not just =.

Question 14 — Theta join

Given Student [id, sName, age], for SELECT DISTINCT S1.sName, S1.age FROM Student S1 JOIN Student S2 ON S1.age > S2.age, what does this return?

A. Name/age of one of the oldest student(s). B. Name/age of all of the oldest student(s). C. Name/age of all of the youngest student(s). D. Name/age of all students older than the youngest student(s). E. None of the above.

D. S1.age > S2.age only has a match for S1 if some student (S2) is younger — i.e. S1 is not the/a youngest student. So the result is every student who is older than at least one other student: everyone except the youngest.

Inner and outer joins

  • Inner join (default, just JOIN): a tuple appears in the result only if matching tuples exist in both relations.
  • Outer join: includes the inner join result, plus unmatched rows from one or both tables:
    • Left join — all rows from the first table.
    • Right join — all rows from the second table.
    • Full outer join — all rows from both. Not implemented in MySQL.
-- Misses departments without a manager:
SELECT   D.dName, E.name
FROM     Department AS D
JOIN     Employee AS E ON D.mgrSSN = E.ssn;

-- Includes them (NULL for the manager's name):
SELECT   D.dName, E.name
FROM     Department AS D
LEFT JOIN Employee AS E ON D.mgrSSN = E.ssn;

Set operations

A relation is a set of tuples (no duplicates, no order) — set operators apply directly:

  • UNION — tuples in either or both relations.
  • INTERSECT — tuples in both.
  • EXCEPT (a.k.a. MINUS) — tuples in the first but not the second.

Union compatibility is required: the same number of columns, with pair-wise compatible domains.

By default each of these eliminates duplicates; append ALL (UNION ALL, INTERSECT ALL, EXCEPT ALL) to keep multiset semantics. If a tuple occurs m times in r and n times in s: it occurs m + n times in r UNION ALL s, min(m, n) times in r INTERSECT ALL s, and max(0, m - n) times in r EXCEPT ALL s.

SELECT ...
UNION [ALL] SELECT ...
[UNION [ALL] SELECT ...]
-- Stars in a movie from 1944 or 1974 — via OR:
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID
WHERE  year = 1944 OR year = 1974;

-- ...or equivalently via UNION:
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID WHERE year = 1944
UNION
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID WHERE year = 1974;

INTERSECT is part of the SQL standard but not implemented in MySQL — it can be rewritten with a self-join:

-- Stars in a movie from BOTH 1944 and 1974:
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID WHERE year = 1944
INTERSECT
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID WHERE year = 1974;

-- Rewritten without INTERSECT, using a self-join on starID:
SELECT DISTINCT S1.starID
FROM   Movie M1
JOIN   StarsIn S1 ON M1.movieID = S1.movieID
JOIN   StarsIn S2 ON S1.starID = S2.starID
JOIN   Movie M2 ON M2.movieID = S2.movieID
WHERE  M1.year = 1944 AND M2.year = 1974;
-- Stars in a movie from 1944 but NOT 1974 (EXCEPT):
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID WHERE year = 1944
EXCEPT
SELECT starID FROM Movie M JOIN StarsIn S ON M.movieID = S.movieID WHERE year = 1974;

EXCEPT queries can also always be rewritten as nested queries (next lecture).

Properties of set operators

\[A \cup B = B \cup A \qquad (A \cup B) \cup C = A \cup (B \cup C)\] \[A \cap B = B \cap A \qquad (A \cap B) \cap C = A \cap (B \cap C)\] \[A - B \neq B - A \qquad (A - B) - C \neq A - (B - C)\]

Union and intersection are commutative and associative; difference is neither.

JOIN vs set operations

Join Set operation
Combines Rows from many tables into new columns Result-sets of SELECTs into new rows
Column count May differ between tables Must match
Column types Can differ Must be compatible
Duplicates Kept by default Removed by default

Question 15 — UNION query

Given Table [A, B, C] with rows (1,X,11), (2,Y,12), (3,Y,13), what does SELECT * FROM Table UNION SELECT a, b FROM Table WHERE b = 'X' OR c = '13' produce?

D — none of the above; the query raises an error. SELECT * returns 3 columns (A, B, C) but SELECT a, b returns only 2 — the two halves of the UNION are not union-compatible (mismatched column counts), so none of the listed result tables can occur.

Question 16 — UNION query (two tables)

Given Table1 [A,B,C] = (1,X,11), (2,Y,12), (3,Y,13) and Table2 [D,E,C] = (3,X,11), (4,Y,12), (3,Y,13), what does SELECT * FROM Table1 UNION SELECT d, e, c FROM Table2 WHERE E = 'X' OR C = 13 produce?

C. Table2 rows matching E='X' OR C=13 are (3,X,11) and (3,Y,13); renamed to (A,B,C) these are (3,X,11) and (3,Y,13). Unioned with all of Table1 — (1,X,11), (2,Y,12), (3,Y,13) — and removing the duplicate (3,Y,13), the result is (1,X,11), (2,Y,12), (3,Y,13), (3,X,11): four rows, all of Table1 plus the one genuinely new row (3,X,11). (A is missing a row from Table1; B fails to remove the duplicate (3,Y,13).)

Summary

You can now aggregate and group data, and query across multiple relations using renaming, joins (equi/theta/inner/outer), and set operations. Next lecture: nested queries, views, and Generative AI & SQL. See week7-tutorial-applied-class-6-aggregation-and-grouping, week7-tutorial-case-study-5-easydrive-insurance and week8-tutorial-applied-class-7-multiple-relation-queries for practice.

Applied Class 6: DQL Aggregation and Grouping

Practice for 2026-04-06-aggregation-grouping-and-multiple-relation-queries. Same BestTechLtd schema as week6-tutorial-applied-class-5-basic-sql-ddl-and-dml.

Employee              [EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt]
AdministrativeEmployee [EmployeeID, Level, Type]
Role                   [RoleID, Name, Description]
RoleGranting           [EmployeeID, RoleID, AdministrationID, Timestamp]
Permission             [WebsiteURI, RoleID, GrantType, Description]

AdministrativeEmployee.EmployeeID references Employee.EmployeeID
RoleGranting.EmployeeID references Employee.EmployeeID
RoleGranting.RoleID references Role.RoleID
RoleGranting.AdministrationID references AdministrativeEmployee.EmployeeID
Permission.RoleID references Role.RoleID

Question 1

Return the youngest Employee’s Date of Birth.

SELECT MAX(DOB)
FROM   Employee;

The youngest employee has the largest (most recent) DOB — a common trap is reaching for MIN by reflex.

Question 2

For each website URI, find the number of roles that can access it. Two columns: the website URI, and the number of roles.

SELECT   WebsiteURI, COUNT(DISTINCT RoleID)
FROM     Permission
GROUP BY WebsiteURI;

Question 3

Return the EmployeeId of all employees who have been granted more than one role.

SELECT   EmployeeID
FROM     RoleGranting
GROUP BY EmployeeID
HAVING   COUNT(RoleID) > 1;

Question 4

Return the highest AdministrativeEmployee level, the lowest level, and the difference between both, aliased DifferenceInAdministrativeEmployeeLevels.

SELECT MAX(Level), MIN(Level),
       (MAX(Level) - MIN(Level)) AS DifferenceInAdministrativeEmployeeLevels
FROM   AdministrativeEmployee;

Question 5 — Discuss

(1) What do you observe using an aggregation function without GROUP BY? Why? (2) What do you observe aggregating (GROUP BY) over a primary key or unique field? Why?

  1. Observation: the aggregate operates over the entire table and returns a single row/value. Reason: with no GROUP BY, the whole result set is treated as one big group.
  2. Observation: the number of rows returned equals the number of rows in the table, and most aggregate functions return values identical to the ungrouped row. Reason: a primary key/unique field is unique and non-null per row, so every “group” contains exactly one tuple.

Question 6

Return the average salary of Employees born after 1 January 1990.

SELECT AVG(Salary)
FROM   Employee
WHERE  DOB > '1990-01-01';

Question 7

Return the roleID of all roles which have at least two associated website URIs with a grantType of 'Edit'.

SELECT   RoleId
FROM     Permission
WHERE    GrantType = 'Edit'
GROUP BY RoleId
HAVING   COUNT(WebsiteURI) >= 2;

Question 8 (Challenge)

Return all last names that are shared by multiple employees.

SELECT   DISTINCT LastName
FROM     Employee
WHERE    LastName IS NOT NULL
GROUP BY LastName
HAVING   COUNT(FirstName) > 1;

Case Study 5: EasyDrive Insurance (Queries)

Practice for 2026-04-06-aggregation-grouping-and-multiple-relation-queries. An analyst at EasyDrive Insurance needs several queries answered against their production data (loaded from a synthetic-data dump), but struggles to write them — this case study writes them on the analyst’s behalf.

Schema

Note this schema has evolved slightly since week6-tutorial-case-study-4-easydrive-insurance — VehicleType has been split into VehicleCodeMapping, VehicleValue and VehicleExcessRange (as transcribed directly from the source materials — the two case studies’ schemas are not perfectly identical).

Customer           [CustomerID, Name, DateOfBirth, Email, Occupation, AddressID]
Address            [AddressID, StreetName, Number, Suburb, Postcode, State, Country]
Vehicle            [VehicleID, VehicleCode, VehiclePurpose, EstYearlyKm]
VehicleCodeMapping [VehicleCode, Make, Model, Year]
VehicleValue       [VehicleCode, MarketValue]
VehicleExcessRange [VehicleCode, MinimumExcess, MaximumExcess]
Policy             [PolicyID, CustomerID, VehicleID, PolicyStartYear, PolicyPurchaseDate, Excess, Premium]

Customer.AddressID references Address.AddressID
Policy.VehicleID references Vehicle.VehicleID
Policy.CustomerID references Customer.CustomerID
Vehicle.VehicleCode references VehicleCodeMapping.VehicleCode
VehicleExcessRange.VehicleCode references VehicleCodeMapping.VehicleCode
VehicleValue.VehicleCode references VehicleCodeMapping.VehicleCode

Task 1a

Return the number of vehicles used for every VehiclePurpose, ordered greatest to least.

SELECT   VehiclePurpose, COUNT(*)
FROM     Vehicle
GROUP BY VehiclePurpose
ORDER BY COUNT(*) DESC;

Task 2

Which CustomerID(s) have at least 2 Policies?

SELECT   CustomerID
FROM     Policy
GROUP BY CustomerID
HAVING   COUNT(*) >= 2;

Task 3

The marketing team wants to find the most common suburb of our customers (return the suburb name and street count, two columns). If there are ties, return all of them, in ascending alphabetical order of suburb name. Assume different suburbs never share a name.

SELECT   Suburb AS 'Suburb Name', COUNT(*) AS 'Street Count'
FROM     Address
GROUP BY Suburb
HAVING   COUNT(*) >= ALL (
    SELECT   COUNT(*)
    FROM     Address
    GROUP BY Suburb
)
ORDER BY Suburb ASC;

>= ALL (...) here means “greater than or equal to every group’s count” — i.e. the maximum. See 2026-04-13-nested-queries-views-and-generative-ai for more on ALL.

Task 4a

A possible fraud was detected — identify all street names containing "et" with at least two customers living on that street.

SELECT   StreetName
FROM     Address
WHERE    StreetName LIKE '%et%'
GROUP BY Postcode, StreetName
HAVING   COUNT(*) >= 2;

This query is incorrect as written: grouping by (Postcode, StreetName) doesn’t account for whether two customers actually live at the same address (e.g. a shared house sharing one AddressID) versus merely the same street. Correctly answering this requires joining Address to Customer and reasoning about which customers share an address — which needs a multi-relation query, covered next week. This is a useful edge case to notice: grouping by the wrong combination of columns can silently produce a plausible-looking but wrong count.

Task 4b

Duplicate counting can happen for many different reasons — check whether the query above counts each street name correctly.

Same underlying issue as 4a — grouping by StreetName alone (dropping Postcode from the GROUP BY) doesn’t fix the shared-address double-counting problem either; both versions can overcount when multiple customers share one AddressID.

Week 8

Nested Queries, Views and Generative AI & SQL

Module 3, part 3 — the last SQL lecture. Continues 2026-03-30-basic-sql-ddl-and-dml and 2026-04-06-aggregation-grouping-and-multiple-relation-queries, using the same Company and Movie schemas.

Today’s outline

  • Nested queries (subqueries) — output types, correlated vs non-correlated, relational division
  • Views
  • Generative AI & SQL

Nested queries

A nested query (subquery) is a query that appears inside another query — nesting can occur at multiple levels. The query containing the nested query is the outer query. Subqueries let you compute an intermediate result and feed it into a larger query, without creating a temporary table.

-- INVALID: can't use an aggregate directly in WHERE
SELECT name, salary
FROM   Employee
WHERE  salary > AVG(salary);

-- Valid: wrap the aggregate in a subquery
SELECT name, salary
FROM   Employee
WHERE  salary > (SELECT AVG(salary) FROM Employee);

Subquery output types

Type Expected result Operators Example
Scalar A single value =, <, >, != salary > (SELECT AVG(salary) ...)
Set A list of values (1 column) IN, NOT IN, ANY, ALL WHERE dept IN (SELECT dNumber ...)
Table Rows & columns (a relation) used in FROM FROM (SELECT ...) AS sub
Boolean True/false for a row EXISTS, NOT EXISTS WHERE EXISTS (SELECT * FROM ...)

Always ask: what do I expect this subquery to return — a value, a list, a table, or a yes/no? That determines which clause it can go in and which operators are legal.

Scalar — a single value, for direct comparison. Common pitfall: a scalar subquery that unexpectedly returns more than one row raises an error (“subquery returned more than one row”).

SELECT name, salary
FROM   Employee
WHERE  salary > (SELECT AVG(salary) FROM Employee);

Set — a list of values, for IN/NOT IN/ANY/ALL. Common pitfall: never use = where the subquery can return multiple rows.

-- Departments with (not) a manager named "Jennifer"
SELECT dName FROM Department
WHERE  mgrSSN IN (SELECT ssn FROM Employee WHERE name LIKE 'Jennifer');

SELECT dName FROM Department
WHERE  mgrSSN NOT IN (SELECT ssn FROM Employee WHERE name LIKE 'Jennifer');

IN is equivalent to = ANY:

SELECT DISTINCT dName FROM Department
WHERE  mgrSSN = ANY (SELECT ssn FROM Employee WHERE name LIKE 'Jennifer');

ALL requires the comparison to hold against every value returned:

-- Employees earning more than everyone in department 5
SELECT *
FROM   Employee
WHERE  salary > ALL (SELECT salary FROM Employee WHERE dNum = 5);

Table — used in FROM, must be given an alias.

-- Average salary per department, alongside the department name
SELECT d.dName, avgSal.avg_salary
FROM   Department d
JOIN   (SELECT dNum, AVG(salary) AS avg_salary
        FROM   Employee
        GROUP BY dNum) AS avgSal ON d.dNumber = avgSal.dNum;

Boolean — EXISTS/NOT EXISTS check for the presence of any row; they don’t return data, just yes/no.

-- Employees in a department that currently has a manager assigned
SELECT name, dNum
FROM   Employee e
WHERE  EXISTS (
    SELECT * FROM Department d
    WHERE  d.dNumber = e.dNum AND d.mgrSSN IS NOT NULL
);

Joins vs subqueries

Many nested queries are equivalent to a plain JOIN — but not always.

  • Joins can display columns from every table in the FROM clause; a subquery-based query can only display columns from the outer query’s table(s).
  • Subqueries can compute an aggregate on the fly and feed it back to the outer query for comparison — an advantage over joins.

Rule of thumb: use a join when displaying results from multiple tables; use a subquery when comparing against an aggregate.

Non-correlated vs correlated subqueries

  • Non-correlated: the inner query is evaluated once, independently of the outer query (“inside out”) — like calling a parameterless function and reusing its result.
  • Correlated: the subquery’s WHERE clause references an attribute from the outer query’s relation — the outer query supplies values the inner query needs, so the subquery is (conceptually) re-evaluated once per outer row.
-- Non-correlated: the AVG is computed once, then reused for every row
SELECT name, dNum, salary
FROM   Employee
WHERE  salary > (SELECT AVG(salary) FROM Employee);

-- Correlated: E1.dNum ties the inner query to the outer row
SELECT E1.name, E1.dNum
FROM   Employee E1
WHERE  salary > (
    SELECT AVG(salary) FROM Employee E2 WHERE E1.dNum = E2.dNum
);
Non-correlated Correlated
Semantics Executed once Executed once per outer row
Dependency Independent — can run in isolation Depends on the outer query
Runtime n + m rows examined (inner + outer) n * m rows examined

Subqueries with GROUP BY / HAVING

-- WHERE and HAVING both apply to "earning > 20000" (same filtered set)
SELECT   dNum, COUNT(*)
FROM     Employee E1
WHERE    salary > 20000
GROUP BY dNum
HAVING   2 < COUNT(*);

-- HAVING instead checks a DIFFERENT condition, over ALL employees,
-- via a correlated subquery
SELECT   dNum, COUNT(*)
FROM     Employee E1
WHERE    salary > 20000
GROUP BY dNum
HAVING   2 < (SELECT COUNT(*) FROM Employee E2 WHERE E1.dNum = E2.dNum);

The first finds departments with >2 employees earning over 20000; the second finds departments (among those with employees earning over 20000) that have >2 employees in total.

EXISTS / NOT EXISTS

Subqueries using EXISTS/NOT EXISTS are always correlated. EXISTS (subquery) is true if the subquery’s result is non-empty; NOT EXISTS (subquery) is true if it’s empty.

-- Movies that were the ONLY movie produced that year
SELECT *
FROM   Movie M1
WHERE  NOT EXISTS (
    SELECT * FROM Movie M2
    WHERE  M1.movieID <> M2.movieID AND M1.year = M2.year
);

-- Movies that were NOT the only movie produced that year
SELECT *
FROM   Movie M1
WHERE  EXISTS (
    SELECT * FROM Movie M2
    WHERE  M1.movieID <> M2.movieID AND M1.year = M2.year
);

Relational division

Division answers “for all”/“for every” queries — e.g. find movie stars who were in all movies [produced in a given year].

Dividing R1 / R2: the result has R1’s columns except R2’s, where R1 and R2 must be division compatible (R1’s last n columns identically named to R2’s n columns, n = R2’s degree). The result contains a value t if t appears in R1 in combination with every tuple of R2.

Student Degree   Subject          Subject   Student Degree
Anna    BIT      CS114     ÷      CS114  =  Anna    BIT
Anna    BIT      CS115            CS115
Anna    BIT      CS180
Fred    BSc      CS114
Fred    BSc      CS180

(Fred is excluded — Fred hasn’t taken CS115, so Fred doesn’t divide evenly by {CS114, CS115}.)

MySQL has no / division operator for relations — division is expressed via counting or double negation.

Division via counting

“Find the movie star(s) who acted in at least all the movies produced in 1934.” For a candidate star X: count how many 1934 movies X acted in, and compare that count to the total number of 1934 movies — if they’re equal, X acted in all of them.

SELECT   X.starID, X.name
FROM     MovieStar X
JOIN     StarsIn S ON X.starID = S.starID
JOIN     Movie M ON S.movieID = M.movieID
WHERE    M.year = 1934
GROUP BY X.starID
HAVING   COUNT(*) = (SELECT COUNT(*) FROM Movie M2 WHERE M2.year = 1934);

Division via double negation

“Find X such that there is no 1934 movie which X did not act in.” This reformulates “for all” as “there does not exist an exception”:

SELECT starID, name
FROM   MovieStar X
WHERE  NOT EXISTS (
    SELECT *
    FROM   StarsIn S
    JOIN   Movie M ON S.movieID = M.movieID
    WHERE  M.year = 1934
      AND  M.movieID NOT IN (
          SELECT movieID FROM StarsIn M2 WHERE M2.starID = X.starID
      )
);

Reading from the inside out: the innermost subquery is “all movies X acted in”; the middle subquery is “1934 movies X did not act in”; the outer NOT EXISTS is “there is no such movie” — i.e. X acted in every 1934 movie.

Question 17 — Subquery

Given Employee [ID, Name, DepartmentID] and Department [ID, Department, ManagerID], how do you find all employees managed by Michael Scott?

A. WHERE DepartmentID = (SELECT ID FROM Employee WHERE name="Michael Scott") B. WHERE DepartmentID IN (SELECT ID FROM Department WHERE ManagerID = (SELECT ID FROM Employee WHERE name="Michael Scott")) C. WHERE DepartmentID = (SELECT DepartmentID FROM Employee WHERE name="Michael Scott") D. FROM Employee, Department WHERE name="Michael Scott" AND departmentID=ID

B. We need Michael Scott’s ID (from Employee), then the Department(s) he manages (ManagerID = his ID), then employees whose DepartmentID matches one of those departments. A incorrectly compares an Employee.DepartmentID to an Employee.ID (mismatched domains). C and D make the same mistake — they never consult Department at all, so they can’t find who Michael manages, only who shares his own DepartmentID.

Question 18 — Correlated subquery

Given Scores [team, day, opponent, runs], for

SELECT team, day
FROM   Scores S1
WHERE  runs <= ALL (SELECT runs FROM Scores S2 WHERE S1.day = S2.day);

which result(s) are correct? A. (Carp, Sun) B. (Bay Stars, Sun) C. (Swallows, Mon) D. All of the above E. None of the above

D. The correlated subquery restricts comparison to games played on the same day as S1 — so this returns the team(s) that scored the fewest runs on their day. On Sunday, Carp (2 runs) and Bay Stars (2 runs) tie for fewest; both qualify. On Monday, Swallows (0 runs) has the fewest. All three listed answers are genuinely in the result.

Question 19 — Division

Given R1 [Category, SecondaryCategory, Budget] and R2 [SecondaryCategory, Budget] (values Romance/10 and Horror/10), what is R1 / R2?

Category = Drama, Comedy (a single-column result). Only Drama and Comedy appear in R1 paired with both (Romance, 10) and (Horror, 10) — Action only pairs with (Horror, 10), so it’s excluded. The result keeps only the columns of R1 not in R2 (just Category), not the full (Category, SecondaryCategory, Budget) tuples.

Question 20 — Division

Given R1 as above, R2 = {(Romance,10), (Horror,10)}, and R3 = {(Horror, 11)} — what tuple is in both R1/R2 and R1/R3?

A. (Drama, Romance, 10) B. (Drama) C. (Action) D. (Comedy, Horror, 10) E. None of the above

B. From Question 19, R1/R2 = {Drama, Comedy}. R3 has a single tuple (Horror, 11), so R1/R3 contains every category paired with (Horror, 11) in R1 — both Drama (Drama, Horror, 11) and Comedy (Comedy, Horror, 11) qualify, so R1/R3 = {Drama, Comedy} too. The intersection of R1/R2 and R1/R3 is therefore {Drama, Comedy} — Drama is in both, which is what option B asserts (Comedy isn’t offered as a choice here). A and D are the wrong shape (division results keep only the Category column, degree 1 — not the full 3-column tuple); C (Action) never appears in either division, since Action only pairs with (Horror, 10), not (Romance, 10) or (Horror, 11).

Views

A view is a single table derived from other tables (base tables or other views).

  • Virtual — does not physically exist on disk; recomputed each time it’s queried.
  • Materialized — physically stored, and must be refreshed when base tables change.
CREATE VIEW <view name> (<column name> {, <column name>}) AS <select statement>;
-- Count, gender and average salary of employees, per department
CREATE VIEW DepEmpStatus AS
SELECT   dNumber, dName, sex, COUNT(*) AS employeeNumber, AVG(salary) AS avgSalary
FROM     Department AS D
JOIN     Employee AS E ON D.dNumber = E.dNum
GROUP BY dNum, sex;

SELECT * FROM DepEmpStatus;

Given DepEmpStatus but not Department/Employee directly, a user can see aggregate departmental statistics without access to confidential per-employee data (address, salary).

Benefits of views

  • Simplification — hide the complexity of underlying tables.
  • Security — hide sensitive columns from some users.
  • Computed columns — computed on the fly.
  • Logical data independence — users/programs querying the view are insulated from changes to the underlying logical schema.

View updates and dropping

Updates to a view must ultimately happen on the base table(s) — this can be ambiguous or difficult in general, so DBMSs restrict updates to simple, single-table updatable views (e.g. a view of “employees from department X” that a department manager can edit directly).

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE];

DROP TABLE ... RESTRICT refuses to drop a table with views defined on it; DROP TABLE ... CASCADE drops the table and recursively drops any dependent views.

Question 21 — Views

Given R [a, b, c],

CREATE VIEW V AS SELECT a+b AS d, c FROM R;
SELECT d, SUM(c) FROM V GROUP BY d HAVING COUNT(*) <> 1;

which tuple is returned? A. (2,3) B. (3,12) C. (5,9) D. All are correct E. None are correct

C. Computing d = a+b for every row of R [a,b,c] = (1,1,3), (1,2,3), (2,1,4), (2,3,5), (2,4,1), (3,2,4), (3,3,6) gives view rows (d=2,c=3), (d=3,c=3), (d=3,c=4), (d=5,c=5), (d=6,c=1), (d=5,c=4), (d=6,c=6). Grouping by d:

d rows (c values) COUNT(*) SUM(c)
2 3 1 3
3 3, 4 2 7
5 5, 4 2 9
6 1, 6 2 7

HAVING COUNT(*) <> 1 excludes d=2 (only one row), leaving (3,7), (5,9), (6,7). (5, 9) is genuinely in the result; (2,3) was excluded by the HAVING, and (3,12) doesn’t match d=3’s actual sum of 7.

Views as a cleaner alternative to nested subqueries

-- Ugly: nested subquery re-derives the per-department total twice
SELECT   d.dName
FROM     Department d,
         (SELECT dNum, SUM(salary) AS departmentWage
          FROM   Employee GROUP BY dNum) AS Temp
WHERE    d.dNumber = Temp.dNum
  AND    Temp.departmentWage = (
      SELECT MAX(departmentWage)
      FROM   (SELECT SUM(salary) AS departmentWage
              FROM   Employee GROUP BY dNum) AS Temp2
  );

-- Cleaner: define the intermediate result as a view, once
CREATE VIEW Temp AS
SELECT   dNum, SUM(salary) AS departmentWage
FROM     Employee
GROUP BY dNum;

SELECT *
FROM   Temp
WHERE  departmentWage IN (SELECT MAX(departmentWage) FROM Temp);

Generative AI & SQL

Generative AI uses machine learning to produce new content (text, images, code, music) from a prompt. It can draft code/queries, explain concepts, summarise data, and much more — but it is not infallible.

Risks to keep in mind

  • Hallucination — confident-sounding but false/misleading output: fake sources, incorrect facts, invented code/data/people.
  • Deepfakes — digitally altered video/image/audio depicting someone saying or doing something they didn’t; hard to detect, can spread misinformation or damage reputations.
  • Bias — AI trained on human-generated data can repeat or amplify existing biases (gender/racial bias in language or image models, cultural bias from uneven data representation, confirmation bias in recommendations).

Five tips for responsible use: remember AI is a tool, not a person; critically evaluate its output; investigate anything that “feels off”; keep private information private; use AI to elevate your skills, not replace them.

Three GenAI-assisted SQL workflows

  1. Full query workflow (schema + data) — share both the schema and sample data; the AI can generate a full query and the expected result. Good for rapid prototyping and teaching SQL end-to-end. Caution: verify the output manually, since results can still hallucinate.
  2. Schema-only query generation — share only the schema (useful when the data itself is privacy-sensitive). Good for planning queries before you have data access. Caution: without data, the AI may make incorrect assumptions — review generated queries for correctness.
  3. Query explanation & debugging — paste an existing query and ask the AI to explain it, or help debug syntax errors, logic problems, or unexpected results. Caution: explanations can still contain inaccuracies — cross-check against the schema and expected logic.

Learning SQL yourself, while building AI confidence

This course’s priority is that you learn to write SQL independently — you’ll be tested on this without AI tools (the final exam, and the Assignment 2 oral interview). At the same time, you’re encouraged to use AI in Assignment 2 to test/check queries and deepen your understanding — the goal is to use AI as a supportive partner, not a substitute for your own skills. The RiPPLE weekly activities are scaffolded to build AI literacy progressively: designing your own questions grounded in course concepts, practising prompt engineering, critical/metacognitive reflection, and structured peer review — moving from passive AI use to critical, ethical, effective collaboration with AI tools.

Summary

You should now be able to write nested (correlated and non-correlated) subqueries, use relational division (via counting or double negation), create/drop views, and use generative AI tools for SQL responsibly. This completes the Module 3 lecture content — see week8-tutorial-applied-class-7-multiple-relation-queries and week9-tutorial-applied-class-8-nested-queries-and-views for practice.

Applied Class 7: DQL Multiple Relation SQL Queries

Practice for 2026-04-06-aggregation-grouping-and-multiple-relation-queries. Same BestTechLtd schema as week6-tutorial-applied-class-5-basic-sql-ddl-and-dml and week7-tutorial-applied-class-6-aggregation-and-grouping:

Employee              [EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt]
AdministrativeEmployee [EmployeeID, Level, Type]
Role                   [RoleID, Name, Description]
RoleGranting           [EmployeeID, RoleID, AdministrationID, Timestamp]
Permission             [WebsiteURI, RoleID, GrantType, Description]

AdministrativeEmployee.EmployeeID references Employee.EmployeeID
RoleGranting.EmployeeID references Employee.EmployeeID
RoleGranting.RoleID references Role.RoleID
RoleGranting.AdministrationID references AdministrativeEmployee.EmployeeID
Permission.RoleID references Role.RoleID

Question 1

Return the distinct role ID, name and descriptions of all roles which permit access to commercial websites (URI ends with .com).

SELECT DISTINCT R.RoleID, R.Name, R.Description
FROM   Role R
JOIN   Permission P ON P.RoleID = R.RoleID
WHERE  P.WebsiteURI LIKE '%.com';

Question 2

Return the First Name, Last Name, Level, Type and Salary of all Administrative Employees.

SELECT FirstName, LastName, Level, Type, Salary
FROM   AdministrativeEmployee AE
JOIN   Employee E ON E.EmployeeID = AE.EmployeeID;

Question 3

For every role, find the number of websites it gives access to. Two columns: RoleId, number of website URIs.

SELECT     R.RoleID, COUNT(DISTINCT WebsiteURI)
FROM       Role R
LEFT JOIN  Permission P ON P.RoleId = R.RoleID
GROUP BY   R.RoleID;

A LEFT JOIN (not an inner join) is needed here so that roles with zero permissions still appear in the result, with a count of 0.

Question 4

For Employee Sofia Gonzalez, return the distinct name and description of the role(s) she was granted, along with the description of the associated permissions.

SELECT DISTINCT R.Name, R.Description, P.Description
FROM   Employee E
JOIN   RoleGranting RG ON E.EmployeeID = RG.EmployeeID
JOIN   Role R ON RG.RoleID = R.RoleID
LEFT JOIN Permission P ON P.RoleID = RG.RoleID
WHERE  E.FirstName = 'Sofia' AND E.LastName = 'Gonzalez';

Question 5

The co-founders of BestTechLtd can be identified as either: (A) Administrative Employees with a 'LegacyEngineer' type; (B) Employees granted the FinancialPerformanceOverview role; or (C) Employees with permission to view* (but not edit) https://grafana.besttechltd.com. Return the EmployeeID, first and last name of all co-founders.*

Restriction: use at least one set operation.

SELECT E.EmployeeID, E.FirstName, E.LastName
FROM   Employee E
JOIN   AdministrativeEmployee AE ON AE.EmployeeID = E.EmployeeID
WHERE  AE.Type = 'LegacyEngineer'
UNION
SELECT E.EmployeeID, E.FirstName, E.LastName
FROM   Employee E
JOIN   RoleGranting RG ON RG.EmployeeID = E.EmployeeID
JOIN   Role R ON R.RoleID = RG.RoleID
WHERE  R.Name = 'FinancialPerformanceOverview'
UNION
SELECT E.EmployeeID, E.FirstName, E.LastName
FROM   Employee E
JOIN   RoleGranting RG ON RG.EmployeeID = E.EmployeeID
JOIN   Permission P ON P.RoleID = RG.RoleID
WHERE  P.WebsiteURI = 'https://grafana.besttechltd.com' AND P.GrantType = 'View';

Three independent conditions, each a join producing the same (EmployeeID, FirstName, LastName) shape, combined with UNION (which also conveniently de-duplicates employees matching more than one condition).

Question 6

Return all employees who have been granted at least two roles, and can edit at least four distinct website URIs.

SELECT   EmployeeID
FROM     RoleGranting RG
GROUP BY EmployeeID
HAVING   COUNT(RoleID) >= 2
INTERSECT
SELECT   EmployeeID
FROM     RoleGranting RG
JOIN     Permission P ON P.RoleID = RG.RoleID
WHERE    P.GrantType = 'Edit'
GROUP BY EmployeeID
HAVING   COUNT(DISTINCT WebsiteURI) >= 4;

INTERSECT combines two independently-grouped-and-filtered employee lists — “granted ≥2 roles” and “can edit ≥4 distinct websites” — rather than trying to express both conditions in one GROUP BY/HAVING (which would conflate the two different counts). Note INTERSECT isn’t implemented in MySQL — see 2026-04-06-aggregation-grouping-and-multiple-relation-queries for how to rewrite it as a self-join.

Week 9

Applied Class 8: DQL Nested Queries and Views

Practice for 2026-04-13-nested-queries-views-and-generative-ai. Same BestTechLtd schema as week8-tutorial-applied-class-7-multiple-relation-queries:

Employee              [EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt]
AdministrativeEmployee [EmployeeID, Level, Type]
Role                   [RoleID, Name, Description]
RoleGranting           [EmployeeID, RoleID, AdministrationID, Timestamp]
Permission             [WebsiteURI, RoleID, GrantType, Description]

AdministrativeEmployee.EmployeeID references Employee.EmployeeID
RoleGranting.EmployeeID references Employee.EmployeeID
RoleGranting.RoleID references Role.RoleID
RoleGranting.AdministrationID references AdministrativeEmployee.EmployeeID
Permission.RoleID references Role.RoleID

Nested queries

Question 1

Return the Date of Birth, first and last name of the youngest employee(s).

SELECT E.DOB, E.FirstName, E.LastName
FROM   Employee E
WHERE  DOB >= (SELECT MAX(DOB) FROM Employee E2);

>= (not =) against the max, so ties for youngest are all returned, not just one arbitrary row.

Question 2

Return all information about all employees that have not been granted a role from an Administrator with type 'ProductEngineer'. Restriction: must use a subquery.

SELECT E.*
FROM   Employee E
WHERE  EmployeeID NOT IN (
    SELECT DISTINCT E2.EmployeeID
    FROM   Employee E2
    JOIN   RoleGranting RG ON RG.EmployeeID = E2.EmployeeID
    JOIN   AdministrativeEmployee AE ON AE.EmployeeID = RG.AdministrationID
    WHERE  AE.Type = 'ProductEngineer'
);

Question 3

Find the number of employees who share a surname with another employee.

SELECT COUNT(*) AS employees_with_shared_surname
FROM   Employee E1
WHERE  LastName IS NOT NULL
  AND  EXISTS (
      SELECT * FROM Employee E2
      WHERE  E1.LastName = E2.LastName AND E1.EmployeeID != E2.EmployeeID
  );

A correlated EXISTS check — for each E1, is there some other employee E2 with the same last name?

Question 4

Return the EmployeeId of the Administrative Employee(s) that have granted at least as many roles to employees as Mehdi Rahman. Assume only one Mehdi Rahman exists, and include Mehdi Rahman in the result.

SELECT     RG.AdministrationID
FROM       AdministrativeEmployee AE
JOIN       RoleGranting RG ON AE.EmployeeID = RG.AdministrationID
GROUP BY   RG.AdministrationID
HAVING     COUNT(DISTINCT RG.EmployeeID, RG.RoleID) >= (
    SELECT   COUNT(DISTINCT RG.EmployeeID, RG.RoleID)
    FROM     RoleGranting RG
    JOIN     Employee E ON RG.AdministrationID = E.EmployeeID
    WHERE    E.FirstName = 'Mehdi' AND E.LastName = 'Rahman'
    GROUP BY RG.AdministrationID
);

Question 5

Find the first name and last name of all employees that have access to at least all the website URIs that Employee E0007 has access to.

SELECT E.FirstName, E.LastName
FROM   Employee E
WHERE  NOT EXISTS (
    SELECT P.WebsiteURI
    FROM   Employee E2
    JOIN   RoleGranting RG1 ON RG1.EmployeeID = E2.EmployeeID
    JOIN   Permission P ON P.RoleID = RG1.RoleID
    WHERE  E2.EmployeeID = 'E0007'
      AND  P.WebsiteURI NOT IN (
          SELECT P2.WebsiteURI
          FROM   RoleGranting RG2
          JOIN   Permission P2 ON P2.RoleID = RG2.RoleID
          WHERE  RG2.EmployeeID = E.EmployeeID
      )
);

This is a relational division via double negation — “there is no website E0007 can access that E cannot” — see 2026-04-13-nested-queries-views-and-generative-ai for the general pattern.

Views

Question 6

Return the EmployeeID, FirstName, LastName and RoleId of all employees who were the first to receive a given RoleID.

DROP VIEW IF EXISTS EarliestRoleGranting;

CREATE VIEW EarliestRoleGranting AS
SELECT   RoleID, MIN(TimeStamp) AS EarliestTimeStamp
FROM     RoleGranting
GROUP BY RoleID;

SELECT E.EmployeeID, E.FirstName, E.LastName, RG.RoleID
FROM   RoleGranting RG
JOIN   EarliestRoleGranting ERG
       ON ERG.RoleID = RG.RoleID AND ERG.EarliestTimeStamp = RG.TimeStamp
JOIN   Employee E ON E.EmployeeID = RG.EmployeeID;

Question 7

Return the EmployeeID of the Administrator(s) that have granted the most permissions of any other Administrator. Restriction: must use one or more views.

DROP VIEW IF EXISTS AdminPermissionsCount;

CREATE VIEW AdminPermissionsCount AS
SELECT      AE.EmployeeID,
            COUNT(WebsiteURI) AS NumOfPermissionsGranted
FROM        AdministrativeEmployee AE
LEFT JOIN   RoleGranting RG ON RG.AdministrationID = AE.EmployeeID
LEFT JOIN   Permission P ON P.RoleID = RG.RoleID
GROUP BY    AE.EmployeeID;

SELECT *
FROM   AdminPermissionsCount
WHERE  NumOfPermissionsGranted >= (
    SELECT MAX(APC2.NumOfPermissionsGranted)
    FROM   AdminPermissionsCount APC2
);

Week 10

Database Design Guidelines and Functional Dependencies

Module 4, part 1. This module concerns database design theory — how to measure the quality of a relational schema, and how to fix a poor one.

Today’s outline

  • Informal design guidelines
  • Functional dependencies (FDs) — definition, keys, closure

Informal design guidelines

Four informal measures of relational schema quality:

  1. Make sure the semantics of the attributes are clear in the schema.
  2. Reduce redundant values in tuples.
  3. Reduce null values in tuples.
  4. Disallow spurious tuples (don’t allow lossy joins).

Guideline 1 — one relation, one meaning

Design each relation so its meaning is easy to explain. Don’t combine attributes from multiple entity/relationship types into one relation — this confuses the entity’s meaning and causes redundancy.

EMPLOYEE [Ename, Ssn, Bdate, Address, Dnumber]     -- FK: Dnumber
DEPARTMENT [Ename, Dnumber, Dmgr_ssn]              -- FK: Dmgr_ssn

-- vs. combining them into one relation:
EMP_DEPT [Ename, Ssn, Bdate, Address, Dnumber, Dname, Dmgr_ssn]

Guideline 2 — avoid update anomalies

Design base relations so that no insertion, deletion, or modification anomalies occur. If anomalies can’t be avoided, applications must update relations carefully enough to preserve database integrity.

Motivating example — an Employee [ID, Name, Level, Salary] table where salary is fixed per level (Developer=60,000, Manager=700,000, Driver=50,000, Administration=50,000):

ID Name Level Salary
1 Paris Developer 60,000
2 Anna Manager 700,000
3 Ben Manager 700,000
4 Rose Driver 50,000
5 Jack Developer 60,000
6 Charlie Administration 50,000
  • Modification anomaly: updating one developer’s salary makes the “Developer” salary inconsistent with the others.
  • Deletion anomaly: deleting Charlie loses the fact that Administration pays 50,000 (Charlie was the only Administration row).
  • Insertion anomaly: can’t record a Cook’s salary until an employee actually holds that position; inserting a new Developer row with a different salary makes the Developer salary inconsistent.

These aren’t just textbook abstractions — a widely reported 2021 issue with Australia’s vaccine certificate system arose from essentially this class of problem: state vaccination staff recorded details in a way that didn’t precisely match federal records, so contradicting datasets failed to reconcile automatically.

Decomposition

A decomposition of relation R replaces it with two or more relations such that (1) each new relation’s attributes are a subset of R’s (no foreign attributes), and (2) every attribute of R appears in at least one new relation.

Decomposing the Employee example correctly:

Employee [ID, Name, Level]
Level_Salary [Level, Salary]

removes all three anomaly types — modifying one developer’s salary in Level_Salary doesn’t touch Employee; deleting an employee’s row doesn’t touch Level_Salary; a Cook’s salary can be stored in Level_Salary before anyone holds that role. (An incorrect decomposition — e.g. splitting into [ID, Name, Salary] and [Salary, Level] — does not fix the anomalies, since Salary isn’t a valid link back to Level on its own.)

The join operation and lossless joins

R1 ⋈ R2 (natural join): concatenate each tuple of R1 with every tuple of R2 agreeing on their common attributes.

A decomposition of R into R1 and R2 is a lossless join decomposition if, for every legal instance, R = R1 ⋈ R2 — i.e. breaking R apart and rejoining it gives back exactly R, no more, no less.

Lossy join example — decomposing R [A, B, C] into R1 [A, B] and R2 [B, C] when B does not uniquely determine C:

R                    R1        R2         R1 ⋈ R2 (rejoined)
A  B  C              A  B      B  C       A  B  C
1  2  3              1  2      2  3       1  2  3
4  5  6      →        4  5   +  5  6   →  1  2  9   <- spurious!
7  2  9              7  2      2  9       4  5  6
                                          7  2  3   <- spurious!
                                          7  2  9

Here “loss” means loss of information, not loss of tuples — two extra (“spurious”) rows appear because B = 2 maps to two different C values (3 and 9), so the join can’t tell which A goes with which C.

Guideline 4 — join on keys

Design relation schemas so they can be joined using equality conditions on primary/foreign keys, in a way that guarantees no spurious tuples are generated.

Functional dependencies

Motivating question: how can we be sure that all employees at the same level have the same salary, rather than it just happening to be true of the current data? Databases let you declare this formally via a functional dependency (FD): level → salary (“level determines salary” — if we know an employee’s level, we know their salary).

Formal definition

An FD X → Y holds on relation R if, for every legal instance r of R and all tuple pairs t1, t2 ∈ r:

\[t_1[X] = t_2[X] \implies t_1[Y] = t_2[Y]\]

i.e. if two tuples agree on X, they must agree on Y. X → Y is a constraint between two attribute sets X and Y — it restricts which tuples can legally appear together in an instance of R.

Crucially: an FD is a statement about all allowable instances. You can check whether a given instance violates an FD, but you can never prove an FD holds just by looking at one instance — FDs must be identified from the application’s real-world semantics (business rules), not reverse-engineered from a data sample.

Question 1 — Functional dependencies

Given R [A, B, C, D] with rows (1,2,3,4), (2,3,4,6), (6,7,8,9), (1,3,4,5) — which FDs cannot be true?

A. B → C B. B → D C. D → B D. All of the above can be true E. None of the above can be true

B. Rows 2 and 4 both have B = 3, but row 2 has D = 6 while row 4 has D = 5 — two tuples agreeing on B disagree on D, so B → D is impossible. B → C and D → B are both still possible given this instance (no counterexample rows exist for either) — remember, “possible” here just means “not yet contradicted”, not “proven”.

Fixing anomalies via FDs

Given level → salary, decomposing Employee into [ID, Name, Level] and [Level, Salary] fixes all three anomaly types from before — updating a developer’s salary in [Level, Salary] no longer creates inconsistency; deleting an employee no longer loses level/salary mappings; a new level’s salary can be recorded independent of whether any employee holds it yet.

Question 2 — Anomalies

Given R [A, B, C, D] with D → {A, C} and rows (1,4,2,5), (2,3,4,3), (1,1,2,5) — which is not an example of an update anomaly?

A. Deleting <2,3,4,3> B. Inserting <3,5,3,3> C. Modifying <1,1,2,5> to <1,2,2,5> D. Inserting <1,null,2,4> E. Modifying <1,1,2,5> to <1,2,3,5>

C. Modifying B from 1 to 2 (row <1,1,2,5> → <1,2,2,5>) doesn’t touch A, C, or D at all — B isn’t constrained by D → {A, C}, so no anomaly results. A is an anomaly (deletes the only row with D=3, losing the fact that D=3 → {A=2, C=4}). B is an anomaly (row 2, <2,3,4,3>, already establishes D=3 → {A=2, C=4}; inserting <3,5,3,3> gives D=3 a different A/C pair, {A=3, C=3}, contradicting it). D is an anomaly (a null in the primary key violates entity integrity). E is an anomaly (both existing D=5 rows have C=2; changing one to C=3 breaks D → C consistency).

Keys

A key is a minimal set of attributes that uniquely identifies a relation’s tuples — equivalently, a minimal set of attributes that functionally determines all attributes in the relation. A superkey is any set of attributes (not necessarily minimal) that uniquely identifies the relation.

Question 3 — Possible keys

Given R [A, B, C, D] with B → C, C → B, D → {A, B, C} — which is a key?

A. B B. C C. {B, D} D. All of the above E. None of the above

E. B doesn’t determine D or A ({B}⁺ = {B, C}, missing A, D) — not a key. C is symmetric to B ({C}⁺ = {B, C}) — also not a key. {B, D} does determine everything (D alone already does, via D → {A,B,C}), but it’s not minimal — D alone is already a key, so {B, D} is a superkey, not a (candidate) key.

Question 4 — Possible superkeys

Same FDs as Question 3 — which is a superkey?

A. D B. {B, D} C. {B, C, D} D. All are superkeys E. None are superkeys

D. D → {A, B, C} means D alone determines every attribute — D is a key, and therefore every superset of D ({B,D}, {B,C,D}) is automatically a superkey too.

Explicit and implicit FDs; closure of F

Given a set of explicit FDs, further implicit (inferred) FDs can be derived — e.g. from ID → level and level → salary, we can infer ID → salary. The notation F ⊨ X → Y means X → Y can be inferred from F (X = left-hand side/LHS, Y = right-hand side/RHS).

  • Trivial FDs hold regardless of F (the LHS already contains the RHS) — e.g. A → A, {A,B,C} → {A,B}.
  • Non-trivial FDs depend on the specific F — e.g. A → B.

The closure of F, written F⁺, is the set of all FDs (trivial and non-trivial) implied by F. F⁺ can be computed via Armstrong’s Axioms, but that’s outside this course’s scope — instead we focus on attribute closure.

Attribute closure (X⁺)

X⁺ is the set of all attributes determined by X under F:

X+ := X;
repeat
    old X+ := X+;
    for each FD Y → Z in F:
        if Y ⊆ X+ then X+ := X+ ∪ Z;
until (old X+ = X+);

Example — Employee (ID, level, salary, name), F = {ID → level, level → salary, ID → name}:

  1. ID⁺ = {ID}
  2. ID⁺ = {ID, level} (using ID → level)
  3. ID⁺ = {ID, level, salary} (using level → salary)
  4. ID⁺ = {ID, level, salary, name} (using ID → name)

Larger example — R [pNumber, pName, pLocation, dNum, dName, mgrSSN, mgrStartDate], F = {pNumber → {pName, pLocation, dNum}, dNum → {dName, mgrSSN, mgrStartDate}}:

  1. {pNumber}⁺ = {pNumber}
  2. {pNumber}⁺ = {pNumber, pName, pLocation, dNum} (FD1)
  3. {pNumber}⁺ = {pNumber, pName, pLocation, dNum, dName, mgrSSN, mgrStartDate} (FD2) — the full relation, so pNumber is a key.

Finding a superkey — show {sName, pNum} is a superkey for SupplierPart (sName, city, status, pNum, pName, qty) with F = {sName → city, city → status, pNum → pName, {sName, pNum} → qty}:

{sName, pNum}+ = {sName, pNum}
{sName, pNum}+ = {sName, pNum, city}          using sName → city
{sName, pNum}+ = {sName, pNum, city, status}  using city → status
{sName, pNum}+ = {sName, pNum, city, status, pName}   using pNum → pName
{sName, pNum}+ = {sName, pNum, city, status, pName, qty}  using {sName,pNum}→qty

Since the closure covers every attribute of SupplierPart, {sName, pNum} is a superkey.

Tips for finding keys

Given a relation R and FD set F: S ⊆ R is a key iff (1) S⁺ = R, and (2) no proper subset S' ⊂ S also has S'⁺ = R. For n attributes there are 2ⁿ subsets to consider in the worst case, but two shortcuts help:

  1. If an attribute never appears on the RHS of any FD, it must be part of every key (nothing else can ever produce it).
  2. If S is already a key, don’t test any superset of S — it’ll be a superkey, not a (minimal) key.
  3. A relation can have multiple keys of different sizes.

To fully show {sName, pNum} is a key (not just a superkey) for SupplierPart, also confirm minimality: {sName}⁺ = {sName, city, status} and {pNum}⁺ = {pNum, pName} — neither proper subset covers all attributes, so {sName, pNum} is minimal.

Question 5 — Finding keys

Given R [A, B, C, D, E, F] with B → {C, F}, C → E, {E, F} → D — which is a key?

A. B B. {B, E} C. {E, F} D. {A, B} E. None of the above

D. {B}⁺ = {B, C, D, E, F} — misses A. {B, E}⁺ = same, still misses A. {E, F}⁺ = {D, E, F} — misses far more. {A, B}⁺ = {A, B, C, D, E, F} — everything. A never appears on any RHS, so (per the tip above) it must be in every key — confirming why options A–C, none of which include A, can never be keys.

Question 6 — Finding keys

Given R [A, B, C, D, E] with D → C, {C, E} → A, D → A, {A, E} → D — which is a key?

A. {A, B, D, E} B. {B, C, E} C. {C, D, E} D. All of these are keys E. None of these are keys

B. {A, B, D, E} is a superkey but not minimal: since D → A already holds, A is redundant once D is present, so this isn’t the smallest determining set. {C, D, E}⁺: D → C (redundant, already have C), D → A adds A, {C,E} → A (redundant), {A,E} → D (redundant, already have D) — final closure {A, C, D, E}, missing B, so it’s not even a superkey. {B, C, E}⁺: {C,E} → A adds A (→ {A,B,C,E}), then {A,E} → D adds D (→ {A,B,C,D,E}) — every attribute, and no proper subset of {B,C,E} achieves this, so it’s a minimal key.

Question 7 — Finding keys

Given R [A, B, C, D, E, F] with {A, B} → E, C → {B, E}, {E, D} → F — which is a key?

A. {A, B} B. {A, B, C} C. {A, C, D} D. {A, D} E. None of the above

C. {A, B}⁺ = {A, B, E} (via {A,B}→E) — misses C, D, F. {A, B, C}⁺ = {A, B, C, E} (C→{B,E} is redundant here) — still misses D, F. {A, D}⁺ = {A, D} — none of the three FDs’ left-hand sides ({A,B}, C, {E,D}) are subsets of {A, D} alone, so nothing can be added at all. {A, C, D}⁺: C → {B, E} adds B, E (→ {A,B,C,D,E}), then {E,D} → F adds F — every attribute. {A, C, D} is minimal and complete — a key.

Summary

You should now be familiar with informal design guidelines, functional dependencies (their formal definition and how to identify them), keys/superkeys, and how to compute attribute closure. These are the foundation for normalisation, covered next lecture. See week10-tutorial-applied-class-9-functional-dependencies and week10-tutorial-case-study-8-dirt-road-driving for practice.

Applied Class 9: Functional Dependencies

Practice for 2026-04-27-database-design-guidelines-and-functional-dependencies.

Section A — Anomalies and functional dependencies

Question A.1

Based on the following data, provide an example and explanation of an insertion, deletion and modification anomaly.

A  B  C  D  E
2  2  1  5  6
2  3  1  5  4
3  4  5  3  2
3  5  5  1  3

Functional dependencies: {A} → {C}, {B} → {D, E}.

  • Insertion anomaly: insert anything into B, D and E (with B, D, E values that don’t already exist in the data) without also inserting a value into A — the row is incomplete but there’s no A to attach it to.
  • Deletion anomaly: deleting the tuple with B = 4 loses the information for the FD B → {D, E}, specifically 4 → {3, 2} — no other row records that mapping.
  • Modification anomaly: updating {2, 2, 1, 5, 6} to {2, 4, 1, 5, 6} creates an inconsistency in B → {D, E} — it now implies both 4 → {3, 2} (from the existing row with B=4) and 4 → {5, 6} (from the just-modified row).

Question A.2

Based on A → B, B → C, {C, D} → E, fill in the blanks (?) below so that no FD is violated.

A  B  C  D  E
1  2  1  6  2
1  ?  1  4  ?
2  4  2  7  4
3  ?  ?  4  ?
A  B  C  D  E
1  2  1  6  2
1  2  1  4  3
2  4  2  7  4
3  2  1  4  3

Row 2’s B is forced: A = 1 also appears in row 1 (B = 2), and A → B requires matching A values to share the same B. Row 2’s E is then constrained by {C, D} → E: row 2 has {C=1, D=4}, a combination that will recur in row 4 — both rows sharing that {C, D} pair must agree on E (here, 3).

Row 4’s B and C are not strictly forced by the given FDs (A = 3 doesn’t recur elsewhere, so A → B places no constraint on it) — but choosing B = 2, C = 1 is a valid, self-consistent choice: it matches row 1/2’s B → C mapping (B=2 → C=1), and then {C=1, D=4} → E correctly forces row 4’s E to match row 2’s (E = 3), since both rows now share {C=1, D=4}.

Question A.3

Based on the following data, identify which options are potential functional dependencies.

A  B  C  D  E
1  X  1  M  1
2  Y  1  M  1
3  Y  4  N  3
4  W  2  L  5
5  W  2  M  1
6  T  5  O  2
  • A → B — B → A — A → C — B → C — C → D — C → E — D → E
  • {A, B} → C — {B, C} → E — {B, C, D} → E

Potential FDs: A → B, A → C (both trivially hold — every row has a distinct A, so there’s never a repeated-A pair to violate anything). D → E (every repeated D value agrees on E: D=M in rows 1, 2, 5 always has E=1). {A, B} → C (trivially holds, since A alone is already unique per row). {B, C, D} → E (every (B,C,D) triple in the table is distinct, so it trivially holds too).

Not potential (contradicted by the data):

  • B → A: B=Y appears in rows 2 and 3 with different A (2 vs 3).
  • B → C: B=Y appears in rows 2 and 3 with different C (1 vs 4).
  • C → D: C=2 appears in rows 4 and 5 with different D (L vs M).
  • C → E: C=2 appears in rows 4 and 5 with different E (5 vs 1).
  • {B, C} → E: {B=W, C=2} appears in rows 4 and 5 with different E (5 vs 1).

Section B — Closures

Question B.1

Given R [A, B, C, D, E, F, G] with {A} → {D}, {B, C} → {A}, {C} → {F}, {F} → {E} — find the following closures.

  • {C}⁺ = {C, F, E} (via C→F, then F→E).
  • {B, C, A}⁺ = {A, B, C, D, E, F} (via A→D, C→F, F→E) — this is a superkey.
  • {B, C}⁺ = {A, B, C, D, E, F} (via {B,C}→A, then A→D, C→F, F→E) — this is a composite (candidate) key: {B, C, A} is a superkey but not minimal (since {B, C} alone already suffices), and {B, C} is the minimal set achieving the same closure.

Question B.2

Given R [A, B, C, D, E, F] with {A} → {B, C}, {C, D} → {E}, {A, C} → {E}, {B} → {D}, {E} → {A, B} — find the following closures.

  • {A, F}⁺ = {A, B, C, D, E, F} (via A→{B,C}, B→D, {C,D}→E or {A,C}→E, E→{A,B}).
  • {C, D, F}⁺ = {A, B, C, D, E, F} (via {C,D}→E, E→{A,B}).
  • Both {A, F} and {C, D, F} are minimal — removing any single attribute from either set makes the reduced set no longer a superkey.

Section C — Candidate keys

Question C.1

R [A, B, C, D, E, F] with {A, E} → {D}, {B, C} → {A}, {B} → {F}, {F} → {E}. Find all candidate keys.

Candidate key(s): {B, C}.

{B, C}⁺: B→F adds F; F→E adds E; {B,C}→A adds A; {A,E}→D adds D — covers everything, and no proper subset of {B,C} does (neither B nor C alone determines the other).

Question C.2

R [A, B, C, D, E, F] with {A} → {B, C}, {C, D} → {E}, {A, C} → {E}, {B} → {D}, {E} → {A, B}. Find all candidate keys.

Candidate keys: {B, C, F}, {C, D, F}, {A, F}, {E, F}.

F never appears on any FD’s right-hand side, so it must be part of every key. Among {A, B, C, D, E}, the FDs form a tightly-coupled cluster (A, B/D, C/E combinations all inter-derive each other) — A, E, {B,C}, and {C,D} are each independently sufficient to derive the rest of that cluster, giving four minimal keys once F is added to each.

Question C.3

R [A, B, C, D] with {A, B} → {C, D}, {C} → {A, B, D}, {D} → {C}. Find all candidate keys.

Candidate keys: {C}, {A, B}, {D}.

{C}⁺ = {A,B,C,D} directly. {D}⁺: D→C, then C→{A,B,D} — covers everything. {A,B}⁺ = {A,B,C,D} directly. All three are minimal.

Question C.4

R [A, B, C, D, E, F, G, H, I, J] with {A, B} → {C}, {A} → {D, E}, {B} → {F}, {F} → {G, H}, {D} → {I, J}. Find all candidate keys.

Candidate key: {A, B}.

{A, B}⁺: A→{D,E} adds D,E; B→F adds F; {A,B}→C adds C; F→{G,H} adds G,H; D→{I,J} adds I,J — every attribute, and neither A nor B alone reaches the other’s attributes.

Section D — Highest normal form

Question D.1

R [A, B, C, D, E, F] with {A, E} → {D}, {B, C} → {A}, {B} → {F}, {F} → {E}. Identify the highest normal form and justify.

Only candidate key (CK) is {B, C} (from Question C.1). Highest normal form is 1NF, because of the partial dependency {B} → {F} — B is a proper subset of the candidate key {B, C}, and F is a non-prime attribute, violating 2NF.

Question D.2

R [A, B, C, D, E] with {A} → {B, C, D, E}, {B} → {A, C, D, E}. Identify the highest normal form and justify.

CKs are {A} and {B}. Highest normal form is BCNF, because the LHS of every FD (A and B) is itself a superkey.

Question D.3

R [A, B, C, D, E, F, G] with {A} → {B, C, D}, {D} → {A}, {C} → {F, G}. Identify the highest normal form and justify.

CKs are {A, E} and {D, E}. Highest normal form is 1NF. Decomposing {A} → {B, C, D} into {A}→{B}, {A}→{C}, {A}→{D}: {A} → {B} and {A} → {C} are both partial dependencies, since A is a proper subset of the candidate key {A, E} and B/C are non-prime attributes. ({D} → {A} is not a partial dependency, since A is a prime attribute — appearing in candidate key {A, E}.)

Question D.4

R [A, B, C, D, E] with {A, B} → {C, E}, {D} → {A}, {A} → {D}. Identify the highest normal form and justify.

CKs are {A, B} and {D, B}. Highest normal form is 3NF — there are no partial or transitive dependencies. It’s not BCNF, though: the LHS of {A} → {D} (just A) is not a superkey, violating BCNF — but this is not a partial dependency, since D is a prime attribute (appears in candidate key {D, B}). The same reasoning applies symmetrically to {D} → {A}.

Case Study 8: Dirt Road Driving (Payroll System — Anomalies and FDs)

Practice for 2026-04-27-database-design-guidelines-and-functional-dependencies. Dirt Road Driving’s Director of Innovation, Peter Thompson, has asked for student help auditing their payroll/finance system’s database schema — some staff feel the current design is inefficient.

The payroll schema

Note: this is Peter’s first schema email — it’s missing FDs for EmployeeHistory, TripExpenseAllocations and TravelInsuranceHistory (he sends a corrected, more complete version later, used in week11-tutorial-case-study-9-payroll-system). For Section 2 below, those FDs must be identified from the sample data instead.

Employee [id, firstName, lastName, role]

Project [name, description, funding, projectLeader]
Project.projectLeader references Employee.id

TimeLog [employeeID, projectName, date, hoursWorked, approved]
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name

AssetUse [employeeID, assetID, timestamp, useDuration, assetType, purchaseDate, insuranceValue]
assetID → assetType, purchaseDate
assetType → insuranceValue
AssetUse.employeeID references Employee.id

Department [code, name, manager, buildingID, buildingName, buildingLocation, floor]
buildingID → buildingLocation, buildingName
buildingName → buildingLocation, buildingID
Department.manager references Employee.id

EmployeeHistory [employeeID, departmentCode, dateStarted, seniorityLevel, baseSalary, securityLevel]
EmployeeHistory.employeeID references Employee.id
EmployeeHistory.departmentCode references Department.code

TripExpenseAllocations [tripName, expenseType, quantity, organiser, startDate, endDate, location, allowance, restrictions, description]
TripExpenseAllocations.organiser references Employee.id

TravelInsuranceHistory [tripName, approved, insuranceLevel, description, maxCoverage, advisedPrecautions]
TravelInsuranceHistory.tripName references TripExpenseAllocations.tripName

Sample data (relevant excerpts):

AssetUse
employeeID  assetID  timestamp             useDuration  assetType     purchaseDate  insuranceValue
2014        1200     20-12-2019 11:04:14   00:15:02     Vehicle       16-02-2019    20,000
2014        7900     24-12-2019 18:54:01   01:12:52     Vehicle       23-10-2019    20,000
2020        7901     01-01-2020 13:07:59   05:00:09     Power Tools   05-05-2019    1,000
2014        1200     01-01-2020 14:47:08   02:45:36     Vehicle       16-02-2019    20,000

Department
code  name       manager  buildingID  buildingName      buildingLocation              floor
MAK   Marketing  2020     0302        Dumpling Building (-27.4907639, 152.9955379)    8
FIN   Finance    2021     0302        Dumpling Building (-27.4907639, 152.9955379)    7
IT    Computering 2023    2023        Hotpot Building   (-27.4856679, 152.9898608)    2

EmployeeHistory
employeeID  departmentCode  dateStarted  seniorityLevel  baseSalary  securityLevel
2020        FIN             13-09-2019   Junior          70,000      Limited
2023        IT              23-09-2020   Junior          70,000      Limited
1919        IT              05-11-2019   Junior          70,000      Limited
2014        FIN             07-11-2019   Junior          70,000      Limited
2019        IT              07-11-2019   Junior          70,000      Limited
2020        MAK             09-11-2019   Executive       80,000      Full Access
2021        FIN             18-11-2019   Senior          70,000      Full Access
2022        IT              19-11-2019   Junior          70,000      Limited
2023        IT              07-01-2020   Executive       80,000      Full Access

TripExpenseAllocations
tripName                       expenseType  organiser  startDate    endDate      location   allowance  restrictions
UQ Partnership                 Food         2023       02-02-2020   03-02-2020   Brisbane   200        No alcohol over $70
UQ Partnership                 Hotel        2023       02-02-2020   03-02-2020   Brisbane   500        Food service not included
UQ Partnership                 Transport    2023       02-02-2020   03-02-2020   Brisbane   100        Cannot use Uber or DiDi
Ride-share Marketing Conf.     Hotel        2020       21-03-2020   28-03-2020   Sydney     500        Food service not included
Ride-share Marketing Conf.     Transport    2020       21-03-2020   28-03-2020   Sydney     100        Cannot use Uber or DiDi
Investor Meeting               Food         2021       27-03-2020   28-03-2020   Cairns     200        No alcohol over $70

TravelInsuranceHistory
tripName                    insuranceLevel  description                                    maxCoverage
UQ Partnership              4               Local travel without unsafe circumstances       1,000,000
Ride-share Marketing Conf.  1               Any travel with unsafe/govt-conflicting circs.   10,000
Investor Meeting            1               Any travel with unsafe/govt-conflicting circs.   10,000

Section A — Anomalies

Provide and explain one example each of a modification, deletion, and insertion anomaly for the AssetUse and Department tables. (≤100 words each — the exercise must show practically* how each anomaly applies, not just restate the textbook definition.)*

AssetUse

Modification anomaly: updating the tuple <2014, 1200, 20-12-2019 11:04:14, 00:15:02, "Vehicle", 16-02-2019, 20,000> to <2014, 1200, 20-12-2019 11:04:14, 00:15:02, "Vehicle", 18-03-2019, 20,000> — since assetID → purchaseDate, and assetID = 1200 also appears in another row (with timestamp = 01-01-2020 14:47:08), that other row’s purchaseDate would also need updating to 18-03-2019, otherwise the table becomes inconsistent about when asset 1200 was purchased.

Deletion anomaly: deleting <2020, 7901, 01-01-2020 13:07:59, 05:00:09, "Power Tools", 05-05-2019, 1,000> — this is the only row recording asset 7901, so deleting it loses that asset’s assetType and purchaseDate entirely, even though those facts are logically about the asset, not about this particular use-event.

Insertion anomaly: to record a new asset that hasn’t been used yet, we’d need to insert <null, 2200, null, null, "Power Tools", 29-11-2019, 1,000> — but employeeID and timestamp form the primary key, so a null primary key violates entity integrity. A new asset can’t be entered until someone has used it.

Department

Modification anomaly: updating <"FIN", "Finance", 2021, 0302, "Dumpling Building", (-27.4907639, 152.9955379), 7>’s buildingLocation alone — since buildingID → buildingLocation, the other row with buildingID = 0302 (“MAK”) would also need updating, otherwise the two rows disagree about where building 0302 is.

Deletion anomaly: deleting <"IT", "Computering", 2023, 2023, "Hotpot Building", (-27.4856679, 152.9898608), 2> — this is the only row referencing buildingID = 2023, so deleting it loses the buildingName and buildingLocation facts for that building.

Insertion anomaly: recording a new building (buildingID = 2013, “Peking Duck Building”) that isn’t yet occupied by any department requires inserting <null, null, null, 2013, "Peking Duck Building", (-27.566544, 152.917845), null> — a null primary key (code), which violates entity integrity.

Section 2 — Functional dependencies and highest normal form

Based on the sample data above, identify all non-trivial functional dependencies (beyond the given primary keys) for the tables below, and their highest normal form. As the primary keys are given in the schema, ignore FDs for the primary key itself.

EmployeeHistory

FD: seniorityLevel → baseSalary, securityLevel

Every row sharing a seniorityLevel agrees on baseSalary and securityLevel — all six Junior rows have {70,000, Limited}; both Executive rows have {80,000, Full Access}.

Highest normal form: 2NF. This FD is a transitive dependency — its left-hand side (seniorityLevel) is not a superkey, and its right-hand side contains non-prime attributes, violating 3NF. (The LHS is also not a subset of the candidate key {employeeID, departmentCode}, so it doesn’t violate 2NF.)

TripExpenseAllocations

FDs: tripName → startDate, endDate, location, organiser; expenseType → allowance, restrictions

All three “UQ Partnership” rows agree on organiser/dates/location; expenseType = 'Food' (rows for “UQ Partnership” and “Investor Meeting”) always has allowance = 200 and the same restriction text.

Highest normal form: 1NF. The first FD is a partial dependency — its left-hand side (tripName) is a prime attribute (part of the candidate key {tripName, expenseType}) but not itself a superkey, and its right-hand side contains non-prime attributes, violating 2NF.

TravelInsuranceHistory

FD: insuranceLevel → description, maxCoverage

Both rows with insuranceLevel = 1 (“Ride-share Marketing Conference” and “Investor Meeting”) share the same description and maxCoverage = 10,000.

Highest normal form: 1NF. Same shape of violation as above — the left-hand side (insuranceLevel) is a prime attribute but not a superkey, and the right-hand side contains non-prime attributes, violating 2NF.

Week 11

Normalisation and Relational Database Schema Design

Module 4, part 2. Continues 2026-04-27-database-design-guidelines-and-functional-dependencies — now that we can identify FDs and keys, we use them to formally measure and improve schema quality via normalisation.

Today’s outline

  • Normalisation: 1NF, 2NF, 3NF, BCNF
  • Relational database schema design: BCNF decomposition, minimal cover, 3NF synthesis

Key concepts recap

  • Superkey — a set of attributes such that no two tuples share the same values for them (its closure covers every attribute in R).
  • (Candidate) key — a minimal superkey (removing any attribute breaks the superkey property). Minimal ≠ shortest; a relation can have several candidate keys, and every superkey contains at least one candidate key.
  • Primary key — one candidate key, chosen as the key (only one per relation).
  • Prime attribute — an attribute that belongs to any candidate key (not necessarily the primary key).
  • Non-prime attribute — belongs to no candidate key.

Approaching normality

Functional dependencies reveal redundancy: in R [A, B, C] with no FDs, there’s no redundancy; but given A → B, several tuples can share the same A value, and whenever they do, they’re forced to repeat the same B value too — that repetition is the redundancy normalisation identifies and removes.

Normalisation is the process of taking a relational schema through a series of tests, using FDs and (candidate/primary) keys, to certify which normal form it satisfies. Schemas that fail a test are decomposed into smaller relations with better properties.

Normal forms overview

NF Outcome Test (for every non-trivial X → A in F⁺)
1NF Identifies non-atomic values Relation has no multivalued attributes or nested relations
2NF Identifies partial dependencies (removes some anomalies) X is not a proper subset of a candidate key, or A is a prime attribute
3NF Identifies partial + transitive dependencies (removes most anomalies) X is a superkey, or A is a prime attribute
BCNF Identifies all anomalies (at the cost of not preserving all FDs) X is a superkey

BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF — each normal form is strictly more restrictive than the last.

First Normal Form (1NF)

A relation schema is in 1NF if every attribute’s domain contains only atomic (simple, indivisible) values — no set of values, no tuple of values (nested attributes), and no combination of both.

Non-1NF example — an items column holding a comma-separated list, or a nested name = {firstName, familyName} sub-structure, both violate 1NF. Normalizing to 1NF either:

  • repeats the non-nested key columns per value (e.g. one row per (customerName, orderNum, item) combination) — introduces redundancy; or
  • flattens into fixed columns (item1, item2, …) — introduces a lack of flexibility (a hard cap on how many items one order can have, and nulls for orders with fewer items).

Neither is fully satisfactory — this motivates 2NF/3NF/BCNF, which address how to further decompose an already-1NF relation.

Full and partial functional dependencies

  • X → Y is a full functional dependency if removing any attribute from X breaks the dependency (no proper subset of X determines Y).
  • X → Y is a partial dependency if some attribute can be removed from X and the FD still holds.

Example — Address [houseNum, street, postcode, state, value], F = {{houseNum, street, postcode} → {state, value}, postcode → state}:

  • {houseNum, street, postcode} → value is a full dependency (no subset of the LHS determines value).
  • {houseNum, street, postcode} → state is a partial dependency, since postcode → state alone already suffices.

Second Normal Form (2NF)

A relation schema R is in 2NF if every non-prime attribute is fully functionally dependent on the primary key — informally, no partial dependency.

In the Address example: the (only) key is {houseNum, street, postcode}; state is non-prime and only partially dependent on the key (via postcode → state) — this violates 2NF, so Address is not in 2NF.

Fixing it: decompose into Address [houseNum, street, postcode, value] and Postcodes [postcode, state] (Address.postcode → Postcodes.postcode). 2NF identifies anomalies caused by partial dependencies — but not anomalies caused by transitive dependencies, covered next.

2NF doesn’t fix everything: Employee [ID, name, level, salary] with level → salary doesn’t violate 2NF at all (level isn’t even a proper subset of the key {ID} — it’s a whole non-key attribute), so Employee is in 2NF — yet it still has all the same anomalies from before (updating one developer’s salary is still inconsistent, deleting the only “Administration” employee still loses that level’s salary, etc.). 2NF alone isn’t enough.

Transitive dependency

X → Y in R is a transitive dependency if some attribute set Z exists such that Z is neither a candidate key nor a subset of one, and both X → Z and Z → Y hold.

Example — Employee [ID, name, level, salary], F = {level → salary}: consider ID → salary. level is neither a candidate key nor a subset of one; ID → level and level → salary both hold; therefore ID → salary is a transitive dependency (salary depends on ID only through the intermediate level).

Third Normal Form (3NF)

A relation schema R with FD set F is in 3NF iff, for every non-trivial FD X → A in F⁺: X is a superkey, or A is a prime attribute. Equivalently — for any non-trivial X → A where A is non-prime, X must be a superkey.

In the Employee example: level → salary — level is not a superkey, and salary is not a prime attribute — violates 3NF. Fixing it: decompose into StaffAppointment [ID, name, level] and StaffIncome [level, salary]. Most 3NF relations are anomaly-free — but not all of them, as the next example shows.

3NF still isn’t always enough — Teach [studentID, courseID, lecturer], F = {{studentID, courseID} → lecturer, lecturer → courseID} (keys: {studentID, courseID}, {studentID, lecturer}). Teach is in 3NF (lecturer → courseID’s RHS, courseID, is a prime attribute — so it doesn’t violate 3NF even though lecturer isn’t a superkey). Yet it still has anomalies:

  • Deletion anomaly: deleting the only row for a given (studentID, courseID) pair can delete the only record of which lecturer teaches that course.
  • Insertion anomaly: can’t record which lecturer teaches a course that currently has no enrolled students.

Boyce-Codd Normal Form (BCNF)

A relation schema R with FD set F is in BCNF iff, for every non-trivial FD X → A in F⁺: X is a superkey. Informally: whenever a set of attributes determines another attribute, it must determine all attributes of R.

In Teach: lecturer → courseID — lecturer is not a superkey — violates BCNF, so Teach is not in BCNF (even though it is in 3NF).

The catch: BCNF can lose dependencies

Decomposing Teach to fix the BCNF violation: split into [lecturer, courseID] (from lecturer → courseID) and [studentID, lecturer]. Each piece individually satisfies its own local FDs and is in BCNF. But now rejoin them:

lecturer  courseID          studentID  lecturer
John      INFS1200          1234       John
Jane      INFS1200          1234       Jane
                ↓ join ↓
studentID  courseID  lecturer
1234       INFS1200  John
1234       INFS1200  Jane

The rejoined table violates the original FD {studentID, courseID} → lecturer (both rows share {1234, INFS1200} but disagree on lecturer) — even though neither decomposed piece violated any FD on its own. Decomposing into BCNF can fail to preserve all the original FDs. A dependency-preserving decomposition is one where, if R is split into R1, ..., Rn with FD sets F1, ..., Fn (each Fi containing the FDs of F whose attributes fall entirely within Ri), then (F1 ∪ ... ∪ Fn)⁺ = F⁺ — i.e. no FD information is lost by the split.

BCNF vs 3NF trade-off:

BCNF 3NF
Removes all anomalies ✅ ❌
Preserves all FDs ❌ ✅

Most organisations aim for one or the other depending on which guarantee matters more for their use case.

Question 8 — Partial vs. transitive dependency

What’s the difference between partial and transitive dependency?

Partial dependency: an attribute depends on only a subpart of the primary key. Normalizing to 2NF solves this. Transitive dependency: a non-prime attribute depends on other non-prime attributes (rather than directly on the key). Normalizing to 3NF solves this.

Question 9 — Validating normal form

Given R [A, B, C, D, E, F] with {A, B} → {C, D, E}, C → F, E → {A, B} — what is the highest normal form?

2NF. Keys: {A, B}, {E}. No partial dependencies exist (both {A,B} → {C,D,E} and E → {A,B} have a whole key as LHS), so 2NF holds. But C → F: C is not a superkey, and F is non-prime — violates both 3NF and BCNF.

Question 10 — Validating normal form

Given R [A, B, C, D] with {A, B} → {C, D}, {C, D} → A, D → B — what is the highest normal form?

3NF. Keys: {A, B}, {C, D}, {A, D} — every attribute is prime. Since every attribute is prime, 2NF and 3NF automatically hold (every FD’s RHS is a prime attribute). But D → B: D is not a superkey — violates BCNF.

Question 11 — Validating normal form

Given R [A, B, C, D, E] with B → {C, D}, A → E — what is the highest normal form?

1NF. Key: {A, B}. Both A and B are proper subsets of the key — A → E and B → {C, D} are both partial dependencies, violating 2NF.

Question 12 — Validating normal form

Given R [A, B, C, D, E] with A → {B, C, D, E}, E → A — what is the highest normal form?

BCNF. Keys: {A}, {E}. The LHS of every FD (A and E) is itself a superkey — satisfies BCNF, the strictest test.

Question 13 — BCNF and 3NF

Given R [A, B, C, D] with {A, C, D} → B, {A, C} → D, D → C, {A, C} → B — which is true?

A. Neither BCNF nor 3NF B. BCNF but not 3NF C. 3NF but not BCNF D. Both BCNF and 3NF

C. Keys: {A, C}, {A, D}. D → C: D is not a superkey, so BCNF is violated. But C is a prime attribute (appears in key {A, C}), so this same FD does not violate 3NF — R is in 3NF but not BCNF.

Relational database schema design

Two complementary algorithms turn a universal relation (all attributes lumped together, plus a set of FDs) into a well-designed multi-relation schema:

  1. Decomposition (top-down): break the universal relation apart to reach lossless-join, anomaly-free BCNF schemas.
  2. Synthesis (bottom-up): build up from individual attributes to reach lossless-join, dependency-preserving 3NF schemas.
flowchart LR
    U["Universal Relation + FDs"] -->|decomposition| RDB[("Relational DB schema")]
    E["EER Diagram (from UoD)"] -->|mapping| RDB
    A["All attributes + FDs"] -->|synthesis| RDB

Determining which FDs apply to a decomposed relation

For a decomposed relation S and an FD X → Y from the original F: X → Y holds in S if X ∪ Y ⊆ S’s attributes and Y ⊆ X⁺ (computed using all of F, even attributes not in S).

Question 14 — Determining which FDs apply

Given R [A, B, C, D, E] with {A,B}→C, {B,C}→D, {C,D}→E, {D,E}→A, {A,E}→B — which FDs hold in S [A, B, C, D]?

A. A → B B. {A, B} → E C. {A, E} → B D. {B, C, D} → A E. None of the above

D. {B, C, D}⁺ (computed against the full F, using E as an intermediate even though it’s not in S): {B,C}→D gives nothing new; {C,D}→E adds E → {B,C,D,E}; {D,E}→A adds A → {A,B,C,D,E} — covers everything, and A ∈ S, so {B,C,D} → A holds in S. A fails (A⁺ = {A} alone — doesn’t even reach B). B and C both fail because their RHS, E, isn’t even an attribute of S — an FD “holding in S” requires both sides to be within S’s attributes.

Minimal cover

To decompose into 3NF (synthesis) — or just to simplify reasoning about BCNF — we first reduce F to a minimal cover G: an equivalent, “as small as possible” set of FDs. G is a minimal cover for F iff:

  • F⁺ = G⁺ (equivalent implied FDs);
  • every FD in G has a single attribute on its RHS;
  • deleting any FD in G, or any attribute from any FD’s LHS, changes G⁺ (i.e. every FD, and every LHS attribute, is necessary).

Finding a minimal cover — 3 steps

  1. RHS simplification — split every FD so its RHS has exactly one attribute (X → {Y, Z} becomes X → Y and X → Z).
  2. LHS simplification — for each FD X → A where X has multiple attributes including some B: if X⁺ = (X - {B})⁺ (i.e. B is redundant in the LHS), replace it with (X - {B}) → A.
  3. FD set simplification — delete any FD X → A if X⁺ (computed without that FD) still includes A — i.e. the FD is implied by the rest of F and can be dropped.

Worked example — R [A,B,C,D,E,F,G,H], F = {A → B, {A,B,C,D} → E, {E,F} → G, {E,F} → H, {A,C,D,F} → {E,G}}:

Step 1: RHS split Step 2: LHS simplify Step 3: delete redundant
A→B A→B A→B
{A,B,C,D}→E {A,C,D}→E (B redundant, since A→B) {A,C,D}→E
{E,F}→G {E,F}→G {E,F}→G
{E,F}→H {E,F}→H {E,F}→H
{A,C,D,F}→E {A,C,D,F}→E (dropped — {A,C,D,F}⁺ already includes E without this FD)
{A,C,D,F}→G {A,C,D,F}→G (dropped — same reason)

Minimal cover: {A → B, {A,C,D} → E, {E,F} → G, {E,F} → H} (often written with the last two combined as {E,F} → {G,H}).

Question 15 — Minimal cover

Given R [A, B, C, D, E, F] with {D, E, F} → C, {A, B} → {D, C}, D → F — which is a minimal cover?

A. {{D,E,F}→C, {A,B}→{D,C}, D→F} B. {{D,E,F}→C, {A,B}→D, D→F} C. {{D,E}→C, {A,B}→D, {A,B}→C, D→F} D. {{D,E}→C, {A,B}→{C,D}}

C. Step 1 (RHS split): {D,E,F}→C, {A,B}→D, {A,B}→C, D→F. Step 2 (LHS simplify): {D,E,F}→C simplifies to {D,E}→C, since D → F already makes F redundant in that LHS ({D,E}⁺ already includes F via D→F, so {D,E,F}⁺ = {D,E}⁺). Step 3: nothing more to remove — all four remaining FDs are necessary. A keeps the redundant F in the first FD’s LHS. B drops the necessary {A,B}→C FD entirely. D drops the necessary D→F FD.

3NF synthesis algorithm

S := ∅;
Compute a minimal cover G of F;
Combine all FDs in G with the same LHS into one;
For each X → Y in G:
    if no relation in S contains X ∪ Y:
        add a relation with schema X ∪ Y to S;
if any candidate key is missing from the relations:
    add a relation containing all prime attributes;
Eliminate redundant relations (one whose attributes are a
subset of another relation already in S).

Example — R [A, B, C, D, E], F = {{A,B}→C, C→D} (already minimal); key: {A, B, E}. Synthesis: R1 [A,B,C] (from {A,B}→C), R2 [C,D] (from C→D); the key {A,B,E} isn’t contained in either, so add R3 [A,B,E]. None of R1/R2/R3 is a subset of another — final answer: R1 [A,B,C], R2 [C,D], R3 [A,B,E].

Question 16 — 3NF synthesis

Given the minimal cover F = {{A,C}→E, {B,D}→A, A→B, E→{C,F}} for R [A, B, C, D, E, F] — which is a correct 3NF synthesis?

A. R1[A,C,E], R2[D,B,A], R3[A,B], R4[E,C,F] B. R1[A,C,E], R2[A,B,D], R3[A,B], R4[E,C,F], R5[A,C,D] C. R1[A,C,E], R2[B,D,A], R3[E,C,F], R4[A,C,D] D. R1[E,C,F], R5[A,B,C,D,E]

D. Candidate keys are {A,C,D}, {A,D,E}, {B,C,D}, {B,D,E} — note F never appears in any key. Synthesising one relation per FD gives [A,C,E] ({A,C}→E), [A,B,D] ({B,D}→A), [A,B] (A→B), [E,C,F] (E→{C,F}). [A,B] is a subset of [A,B,D] — redundant, drop it. None of the remaining three relations contains a full candidate key (all four keys include D, but neither [A,C,E] nor [E,C,F] has D, and [A,B,D] is missing C/E), so add R5 with all prime attributes: {A, B, C, D, E} (the union of all candidate keys). Now [A,C,E] and [A,B,D] are both subsets of R5 [A,B,C,D,E] — redundant, drop both. [E,C,F] is not a subset of R5 (F isn’t in it) — keep. Final: [E,C,F] and R5 [A,B,C,D,E].

BCNF decomposition algorithm

D := {R};
while (some relation Q in D is not in BCNF):
    find an FD X → Y in Q that violates BCNF;
    replace Q in D with Q1 [Q - Y] and Q2 [X ∪ Y];

(The split point: Q1 keeps everything except the “extra” attributes Y; Q2 becomes the offending FD’s own little BCNF-satisfying table, since X is now trivially a key for it.) The algorithm terminates because every 2-attribute relation is automatically in BCNF (with 0 or 1 non-trivial FDs, the LHS is always a key). Results can vary depending on which order violating FDs are processed in — that’s fine, multiple correct decompositions can exist.

Worked example — R [A, B, C, D], F = {B → C, D → A}: keys — {B,D}⁺ = {A,B,C,D}, the only key. B → C violates BCNF (B isn’t a key) → split into R1 [A,B,D], R2 [B,C]. D → A now violates BCNF in R1 (D isn’t a key for R1) → split into R3 [B,D], R4 [A,D]. Final: R2 [B,C], R3 [B,D], R4 [A,D].

Implicit FDs matter: when checking whether a relation is in BCNF during decomposition, you must also check implied FDs, not just the explicitly given ones — e.g. given A → B and B → C, the implicit A → C might be the one that actually violates BCNF in a sub-relation, even though it was never written down explicitly.

Question 17 — BCNF decomposition

Given R [A, B, C, D, E] with {A,D}→B, C→{D,E}, A→E — which is a correct BCNF decomposition tree?

The correct order is: apply {A,D} → B first (splitting off R1 [A,B,D], leaving R2 [A,C,D,E]), then apply C → {D,E} to R2 (splitting it into R3 [C,D,E] and R4 [A,C]). Final: R1 [A,B,D], R3 [C,D,E], R4 [A,C].

Common mistakes to watch for: writing R3 as [C,E] instead of the full [C,D,E] (dropping an attribute of the violating FD’s RHS); writing the intermediate relation as [A,B,C,E] instead of the correct [A,C,D,E] (forgetting A → E only removes B, not D, from the first split); or decomposing R2 further using A → E when A is already a key for R2 at that point (no violation left to fix).

Question 18 — BCNF decomposition (with a lossy-join check)

Given R [A, B, C, D] with A → B, C → D, {A,D} → C, {B,C} → A — which is a lossless-join BCNF decomposition?

A. {{A,B}, {A,C}, {B,D}} B. {{A,B}, {A,C}, {C,D}} C. {{A,B}, {A,C}, {B,C,D}} D. All of the above E. None of the above

B. Keys: {A,D}, {A,C}, {B,C} (all three give closure {A,B,C,D}). A → B violates BCNF → split into R1 [A,C,D], R2 [A,B]. C → D violates BCNF in R1 → split into R3 [A,C], R4 [C,D]. Final: {{A,B}, {A,C}, {C,D}}.

Why not A ({{A,B}, {A,C}, {B,D}})? Concretely: take R rows (1,2,5,6), (1,2,3,7), (8,2,9,4). Decomposing per option A and rejoining produces two extra rows not in the original R (e.g. (1,2,3,4) appears in the rejoined result despite never being an actual tuple of R) — this is a lossy join, since {B,D} isn’t actually a valid join key here (B=2 recurs across all three original rows with different D values, so joining on B alone reintroduces spurious combinations).

Denormalisation

Since anomalies come from redundancy, it’s tempting to decompose as aggressively as possible — but heavily decomposed schemas need more JOINs to answer queries. Denormalisation is the deliberate, controlled process of relaxing a normal form to improve query performance: fewer joins (faster queries), fewer foreign keys (less storage/maintenance overhead) — useful for analytical workloads or when specific frequent queries need pre-joined results. It must be a controlled, deliberate trade-off, not an accident of poor initial design.

Summary

You should now be able to test a relation schema against 1NF/2NF/3NF/ BCNF given a set of FDs, decompose a universal relation into lossless-join anomaly-free BCNF relations (top-down), and compute a minimal cover to synthesise a lossless-join dependency-preserving 3NF schema (bottom-up). This completes Module 4. Next module: Database Security. See week11-tutorial-applied-class-10-normalisation-bcnf-3nf-synthesis and week11-tutorial-case-study-9-payroll-system for practice.

Reading: Elmasri & Navathe Chapters 14 (up to 14.6) and 15 (up to 15.5).

Applied Class 10: Normalisation, BCNF Decomposition and 3NF Synthesis

Practice for 2026-05-04-normalisation-and-relational-database-schema-design. Note: “consider the FDs in the order provided” is a default guideline — only FDs that actually violate BCNF get split on; other correct decompositions can exist depending on split order.

Section A — BCNF decomposition (top-down)

Question A.1

R [A, B, C, D, E] with {A} → {C}, {A, B} → {D, E}. Decompose into BCNF.

{A,B}⁺ = {A,B,C,D,E} — {A, B} is the only candidate key.

{A} → {C} violates BCNF (A isn’t a superkey) → split into R1 [A, C] and R2 [A, B, D, E]. Within R2, {A,B} is a key (it already was for the whole relation), so {A,B} → {D,E} doesn’t violate BCNF there — no further splitting needed.

Final answer: R1 [A, C] ({A}→{C}), R2 [A, B, D, E] ({A,B}→{D,E}).

Question A.2

R [A, B, C, D, E] with {A} → {B}, {C} → {D, E}. Decompose into BCNF.

{A,C}⁺ = {A,B,C,D,E} — {A, C} is the only candidate key.

{A} → {B} violates BCNF (A alone isn’t a superkey) → split into R1 [A, B] and R2 [A, C, D, E]. In R2, {C} → {D, E} violates BCNF (C alone isn’t a superkey for R2) → split into R3 [C, D, E] and R4 [A, C].

Final answer: R1 [A, B], R3 [C, D, E], R4 [A, C] (no non-trivial FD).

Question A.3

R [A, B, C, D, E, F] with {A} → {B, C}, {C} → {D, E}, {E} → {F}. Decompose into BCNF.

{A}⁺ = {A,B,C,D,E,F} — A is the only candidate key. A → {B, C} does not violate BCNF here, since A is the key — so we skip it and look for an FD whose LHS genuinely isn’t a superkey.

{C} → {D, E} violates BCNF (C isn’t a superkey) → split into R1 [C, D, E] and R2 [A, B, C, F]. Within R2, the explicit FDs don’t obviously violate BCNF — but the implicit FD C → F (derivable transitively via C → E and E → F) does (C isn’t a superkey for R2 either) → split R2 into R3 [C, F] and R4 [A, B, C].

Final answer: R1 [C, D, E] ({C}→{D,E}), R3 [C, F] ({C}→{F}, implicit), R4 [A, B, C] ({A}→{B,C}).

Note: {E} → {F} is lost by this decomposition — E and F never end up together in any final relation, so that FD can no longer be checked directly against the decomposed schema.

Question A.4

R [A, B, C, D, E] with {A} → {B}, {C} → {D, E}, {A, D, E} → {C}, {B, C} → {A}. Decompose into BCNF.

{A, C}, {A, D, E} and {B, C} are all candidate keys.

{A} → {B} violates BCNF → split into R1 [A, B] and R2 [A, C, D, E]. Within R2, {C} → {D, E} violates BCNF → split into R3 [C, D, E] and R4 [A, C].

Final answer: R1 [A, B], R3 [C, D, E], R4 [A, C] — the exact same result as Question A.2, even though A.4 started with twice as many FDs. {A, D, E} → {C} and {B, C} → {A} are both lost in this decomposition (their attributes never all end up together in one final relation) — this is worth noticing: extra FDs don’t necessarily survive decomposition, and a differently-ordered decomposition might have preserved different (but not necessarily more) FDs.

Section B — 3NF synthesis (bottom-up)

Question B.1

R [A, B, C, D, E, F, G, H] with {A, B} → {C}, {C, D} → {E}, {D} → {F, G}. Synthesise into 3NF.

{A,B,D,H}⁺ = {A,B,C,D,E,F,G,H} — the only candidate key. Prime attributes: A, B, D, H.

This FD set is already a minimal cover (RHS already single-attribute per FD after splitting {D}→{F,G} into {D}→F and {D}→G; no LHS or whole-FD redundancy).

Synthesis: R1 [A, B, C] ({A,B}→C), R2 [C, D, E] ({C,D}→E), R3 [D, F, G] ({D}→{F,G}), and — since the candidate key {A,B,D,H} isn’t contained in any of these — R4 [A, B, D, H] (no non-trivial FD). All four are in 3NF.

Question B.2

R [A, B, C, D, E, F] with {A, B} → {C}, {C} → {D, E}. Synthesise into 3NF.

{A,B,F}⁺ = {A,B,C,D,E,F} — the only candidate key. Prime attributes: A, B, F. Already a minimal cover.

Synthesis: R1 [A, B, C] ({A,B}→C), R2 [C, D, E] ({C}→{D,E}), R3 [A, B, F] (no non-trivial FD, added since the key isn’t otherwise present). All three are in 3NF.

Question B.3

R [A, B, C, D, E] with {A} → {D, E}, {D} → {A}, {B} → {C}, {B, C} → {A, D}, {E, A} → {D}. Synthesise into 3NF.

{B}⁺ = {A,B,C,D,E} — {B} is the only candidate key. Prime attributes: B.

Minimal cover: RHS-splitting and removing redundant LHS attributes (e.g. {E,A}→{D} simplifies to {A}→{D}, since E is redundant once A is present — A alone already reaches D) and redundant whole FDs ({B}→{D} becomes derivable via {B}→{A}→{D} so it’s dropped; the duplicate simplified {A}→{D} collapses into one) leaves: {A} → {D, E}, {B} → {A, C}, {D} → {A}.

Synthesis: R1 [A, D, E] ({A}→{D,E}, {D}→{A}), R2 [A, B, C] ({B}→{A,C}) — the candidate key {B} is already present in R2, so no extra all-prime-attributes relation is needed. Both are in 3NF.

Question B.4

R [A, B, C, D, E, F] with {A} → {B, C, D, E, F}, {B, C} → {A}, {D, E} → {B}, {C} → {D}. Synthesise into 3NF.

{A}, {B, C} and {C, E} are all candidate keys. Prime attributes: A, B, C, E.

Minimal cover: RHS-splitting {A} → {B,C,D,E,F} and simplifying — {A}→{B} is redundant (derivable via {A}→{D}, {A}→{E}, {D,E}→{B}); {A}→{D} is redundant (derivable via {A}→{C}, {C}→{D}) — leaves: {A} → {C, E, F}, {B, C} → {A}, {D, E} → {B}, {C} → {D}.

Synthesis: R1 [A, C, E, F] ({A}→{C,E,F}), R2 [B, C, A] ({B,C}→{A}), R3 [D, E, B] ({D,E}→{B}), R4 [C, D] ({C}→{D}). The candidate key {A} is already present in R1, so no extra relation is needed. All four are in 3NF.

Case Study 9: Payroll System (BCNF Decomposition and 3NF Synthesis)

Practice for 2026-05-04-normalisation-and-relational-database-schema-design. Continues week10-tutorial-case-study-8-dirt-road-driving — Peter has now resent Dirt Road Driving’s payroll schema with the full set of functional dependencies included (his earlier email accidentally omitted them).

The corrected payroll schema

Employee [id, firstName, lastName, role]

Project [name, description, funding, projectLeader]
Project.projectLeader references Employee.id

TimeLog [employeeID, projectName, date, hoursWorked, approved]
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name

AssetUse [employeeID, assetID, timestamp, useDuration, assetType, purchaseDate, insuranceValue]
assetID → assetType, purchaseDate
assetType → insuranceValue
AssetUse.employeeID references Employee.id

Department [code, name, manager, buildingID, buildingName, buildingLocation, floor]
buildingID → buildingLocation, buildingName
buildingName → buildingLocation, buildingID
Department.manager references Employee.id

EmployeeHistory [employeeID, departmentCode, seniorityLevel, baseSalary, securityLevel]
seniorityLevel, securityLevel → baseSalary
EmployeeHistory.employeeID references Employee.id
EmployeeHistory.departmentCode references Department.code

TripExpenseAllocations [tripName, expenseType, quantity, organiser, startDate, endDate, location, allowance, restrictions, description]
tripName, expenseType → quantity, description
tripName → startDate, endDate, location, organiser
expenseType → allowance, restrictions
TripExpenseAllocations.organiser references Employee.id

TravelInsuranceHistory [tripName, approved, insuranceLevel, description, maxCoverage, advisedPrecautions]
tripName → approved, insuranceLevel, description
insuranceLevel → description, maxCoverage
TravelInsuranceHistory.tripName references TripExpenseAllocations.tripName

Section A — BCNF decomposition

Decompose (if needed) AssetUse, Department, EmployeeHistory, TripExpenseAllocations, and TravelInsuranceHistory into BCNF, clearly stating any new tables and all foreign keys.

AssetUse

CK = {employeeID, assetID, timestamp}. FDs: {employeeID, assetID, timestamp} → {useDuration, assetType, purchaseDate, insuranceValue}, {assetID} → {assetType, purchaseDate}, {assetType} → {insuranceValue}, and the implicit {assetID} → {insuranceValue}. Highest NF: 1NF.

Final answer:

  • Asset [assetID, assetType, purchaseDate] — {assetID} → {assetType, purchaseDate}
  • Insurance [assetID, insuranceValue] — {assetID} → {insuranceValue}, FK assetID references Asset.assetID
  • AssetUse [employeeID, assetID, timestamp, useDuration] — no non-trivial FD, FK assetID references Asset.assetID

Lost: {assetType} → {insuranceValue} (once decomposed, assetType and insuranceValue never appear together in one relation, so this FD can no longer be directly enforced/checked).

Department

CK = {code}. FDs: {code} → {name, manager, buildingID, buildingName, location, floor}, {buildingID} → {buildingLocation, buildingName}, {buildingName} → {buildingLocation, buildingID}. Highest NF: 2NF.

Final answer:

  • Building [buildingID, buildingLocation, buildingName] — {buildingID} → {buildingLocation, buildingName}, {buildingName} → {buildingID, buildingLocation}
  • Department [code, name, manager, buildingID, floor] — {code} → {name, manager, buildingID, floor}, FK buildingID references Building.buildingID

EmployeeHistory

CK = {employeeID, departmentCode}. FDs: {employeeID, departmentCode} → {seniorityLevel, baseSalary, securityLevel}, {seniorityLevel, securityLevel} → {baseSalary}. Highest NF: 2NF.

Final answer:

  • Salary [seniorityLevel, securityLevel, baseSalary] — {seniorityLevel, securityLevel} → {baseSalary}
  • EmployeeHistory [employeeID, departmentCode, seniorityLevel, securityLevel] — {employeeID, departmentCode} → {seniorityLevel, securityLevel}, FKs to Employee.id and Department.code

TripExpenseAllocations

CK = {tripName, expenseType}. FDs: {tripName, expenseType, quantity} → {organiser, startDate, endDate, location, allowance, restrictions, description}, {tripName, expenseType} → {quantity, description}, {tripName} → {startDate, endDate, location, organiser}, {expenseType} → {allowance, restrictions}. Highest NF: 1NF.

Final answer:

Relation FD
Trip [tripName, startDate, endDate, location, organiser] {tripName} → {startDate, endDate, location, organiser}
Expense [expenseType, allowance, restrictions] {expenseType} → {allowance, restrictions}
TripExpenseAllocations [tripName, expenseType, quantity, description] {tripName, expenseType} → {quantity, description}, FKs to Trip.tripName and Expense.expenseType

TravelInsuranceHistory

CK = {tripName} (the schema’s stated primary key {tripName, approved, insuranceLevel} is not minimal — tripName alone is already a key). FDs: {tripName, approved, insuranceLevel} → {description, maxCoverage, advisedPrecautions}, {tripName} → {approved, insuranceLevel, description}, {insuranceLevel} → {description, maxCoverage}. Highest NF: 2NF.

Final answer:

  • InsuranceCoverage [insuranceLevel, description, maxCoverage] — {insuranceLevel} → {description, maxCoverage}
  • TravelInsuranceHistory [tripName, approved, insuranceLevel, advisedPrecautions] — {tripName} → {approved, insuranceLevel}, {tripName, approved, insuranceLevel} → {advisedPrecautions}, FK insuranceLevel references InsuranceCoverage.insuranceLevel

Section B — 3NF synthesis

Decompose (if needed) Department and TripExpenseAllocations into 3NF via minimal-cover synthesis.

Department

CK = {code}. Highest NF (from Section A): 2NF.

Minimal cover: RHS-splitting {code} → {name, manager, buildingID, buildingName, buildingLocation, floor} and removing redundant attributes — code → buildingName and code → buildingLocation are both redundant (derivable transitively via code → buildingID then buildingID → buildingName/buildingLocation); buildingID → buildingLocation is redundant (derivable via buildingID → buildingName then buildingName → buildingLocation). Simplified cover: {code} → {name, manager, buildingID, floor}, {buildingID} → {buildingName}, {buildingName} → {buildingLocation, buildingID}.

Synthesis:

  • Department [code, name, manager, buildingID, floor] — {code} → {name, manager, buildingID, floor}
  • Building [buildingName, buildingLocation, buildingID] — {buildingID} → {buildingName}, {buildingName} → {buildingLocation, buildingID} (a candidate relation [buildingID, buildingName] from {buildingID}→{buildingName} alone is redundant — it’s a subset of this Building relation, so it’s dropped.)

TripExpenseAllocations

CK = {tripName, expenseType} (the stated primary key is not minimal). Highest NF (from Section A): 1NF.

Minimal cover: after RHS-splitting and removing every attribute already implied by {tripName} or {expenseType} alone from the {tripName, expenseType, quantity} → ... FD (all seven of its RHS attributes turn out to be redundant there, since each is already determined by tripName or expenseType individually), the simplified cover is exactly: {tripName, expenseType} → {quantity, description}, {tripName} → {startDate, endDate, location, organiser}, {expenseType} → {allowance, restrictions}.

Synthesis:

  • TripExpenseAllocations [tripName, expenseType, quantity, description] — {tripName, expenseType} → {quantity, description}
  • Trip [tripName, startDate, endDate, location, organiser] — {tripName} → {startDate, endDate, location, organiser}
  • Expense [expenseType, allowance, restrictions] — {expenseType} → {allowance, restrictions}

(Same final relations as the BCNF decomposition in Section A — in this case, decomposition and synthesis happen to converge on the same schema.)

Week 12

Database Security

Module 5 — the final module. Covers threats to database security, the control measures used to defend against them, and SQL injection.

Today’s outline

  • Threats to database security
  • Access control mechanisms — discretionary, mandatory, role-based
  • Other control measures — inference control, flow control, encryption
  • SQL injection

Why database security matters

Some of the largest data breaches on record (Yahoo, First American, Facebook, Marriott, MongoDB, Equifax, and others) collectively exposed billions of records — via hacking, poor security practice, or simple misconfiguration. Database security matters for:

  • Legal/policy reasons — e.g. government policy protecting access to individuals’ data (medical records, financial ratings).
  • Ethical reasons — e.g. controlling who can see employee salary packages or student grades.
  • Technical reasons — e.g. protection from malware, performance overloading.

Threats — the CIA principle

  • Loss of confidentiality — unauthorized disclosure of confidential information.
  • Loss of integrity — improper modification of information.
  • Loss of availability — a legitimate user cannot access data objects.

Privacy vs. security

  • Security concerns how access to data is controlled — availability for use (with integrity) and permitted access.
  • Privacy concerns how data can be used — an individual’s ability to control the terms under which their sensitive data is acquired and used (preventing storage of sensitive data; ensuring appropriate/authorized use of it).
  • Security is a required building block for privacy, but they’re distinct: privacy concerns both directly accessible information and information that can be inferred from accessible data.

Sensitive data is often double-edged — patient data is private, but also valuable for biomedical/public-health research; audio from CCTV can capture private conversations, but also evidence of criminal activity. This creates a utility/privacy tradeoff: protect all sensitive data, while making as much nonsensitive data available as possible.

Database control measures

Four broad categories:

  1. Access control — provide access only to users with the right access authority.
  2. Inference control — ensure information about individuals cannot be derived even indirectly (applies to statistical databases).
  3. Flow control — prevent information from flowing to unauthorized users.
  4. Data encryption — protect sensitive data in transit.

Access control mechanisms

Database access control has two components:

  • Authentication — verifying the identity of whoever is accessing the database.
  • Authorization — determining whether an authenticated user should be allowed to execute the transaction they’re attempting.

Basic authentication security functions

  • User accounts — users log in with an assigned username/password.
  • Login session — a sequence of database operations by one user, recorded in the system log (also used for recovery, not just security).
  • Database audit — reviewing the log to examine all accesses and operations applied during a period, to identify potential privacy breaches or unauthorised modifications. A log typically records who ran what operation, when, and (for modifications) the before/after values.

Three authorization mechanisms

  1. Discretionary Access Control (DAC) — grant/revoke privileges to users.
  2. Mandatory Access Control (MAC) — classify data and users into security classes, and implement a security policy across them.
  3. Role-Based Access Control (RBAC) — assign users to roles, then apply DAC or MAC at the role level.

Discretionary Access Control (DAC)

Based on granting and revoking privileges.

CREATE USER <username> IDENTIFIED BY "<password>";

GRANT privilegeName
ON     objectName
TO     {userName | PUBLIC | roleName}
[WITH GRANT OPTION];

REVOKE privilegeName
ON     objectName
FROM   {userName | PUBLIC | roleName};

Two levels of privileges

  • Account level — privileges specified per account, independent of any particular relation: CREATE SCHEMA/TABLE/VIEW, ALTER/DROP TABLE, modification (INSERT/DELETE/UPDATE) and SELECT privileges.
  • Relation level — privileges for a specific relation or view (can also be specified at the attribute level): select (read), modification (insert/delete/update), and references privileges. The REFERENCES privilege grants permission to create a foreign key reference to the specified table.
CREATE SCHEMA AUTHORIZATION oe
    CREATE TABLE Product (color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
    CREATE VIEW RedProduct AS
        SELECT color, quantity FROM Product WHERE color = 'RED'
    GRANT select ON RedProduct TO hr;

This single statement creates a schema oe, creates Product, creates the view RedProduct, and grants hr the SELECT privilege on RedProduct.

Access matrix model

Each relation R is assigned an owner account. Owners can grant select/modification/references privileges to other users on any owned relation. An access matrix M(i, j) captures the privilege(s) user i has on object j:

Jake Aiden Nelly Sham
Customer READ READ MODIFY —
CustOrder — — READ —
CustOrder.amt — READ READ —
Supp.status — READ MODIFY REFERENCE
Supp.address — READ MODIFY MODIFY

Revoking and propagating privileges

REVOKE cancels a privilege — useful for granting something temporarily. If account A grants a privilege to B WITH GRANT OPTION, B can then grant it onward to other accounts, without A’s direct knowledge — the DBMS must track this chain of dependency to support revocation correctly.

REVOKE privilegeName ON objectName FROM userName [RESTRICT | CASCADE];
  • CASCADE — revokes the privilege and any dependent privileges that were granted as a result of it (following the chain onward).
  • RESTRICT — refuses the revoke (returns an error) if the privilege has already been passed on to someone else.

A user’s authorization is valid iff there is a path from the root of the authorization graph down to that user’s node. So if u1 grants to u2, who grants to u3: revoking u1 → u2 also cuts off u3 (no path remains); but revoking a different branch, or u2’s authorization being revoked by someone else entirely, doesn’t necessarily touch u1 (the grantor’s own authorization is independent of what they’ve granted downstream).

Specifying privileges through views

If owner A wants to give account B a limited capability to SELECT from a relation — e.g. only some columns, or only rows meeting a condition — A can create a view and grant access to the view instead of the base table:

-- Employee [ssn, name, dob, address, sex, salary, mgrSSN, dNum]
CREATE VIEW A3EMPLOYEE AS
    SELECT name, dob, address
    FROM   EMPLOYEE
    WHERE  dNum = 5;

GRANT SELECT ON A3EMPLOYEE TO A3;

A3 can now only ever see name/dob/address for department 5 employees — never salary, ssn, or other departments’ staff.

Question 1 — Discretionary access control

Which statement removes a privilege from a user?

A. Remove update on department from Amir B. Revoke update on employee from Amir C. Delete select on department from Raj D. Grant update on employee from Amir

B. REVOKE is the actual SQL keyword for removing a privilege. REMOVE and DELETE aren’t valid DCL keywords for this purpose, and GRANT adds a privilege rather than removing one.

Question 2 — Discretionary access control (propagation)

u1 grants authorization to u2, who in turn grants it to u3. Which is correct?

A. If u1 revokes from u3, u2’s authorization is revoked. B. If u1 revokes from u2, u3’s authorization is also revoked. C. If u2’s authorization is revoked, u1’s authorization is also automatically revoked.

B. A user has an authorization iff there’s a path from the root of the authorization graph down to their node. u1 → u2 → u3 — revoking u1’s grant to u2 removes the only path down to u3 as well, so u3 loses authorization too. Revoking from u3 directly (A) has no effect on u2’s own (separately-granted) authorization. u2 losing authorization (C) doesn’t propagate upward to u1 — u1 is the root/grantor, not a recipient.

Worked exercise — GRANT/REVOKE chains

Four users A1, A2, A3, A4. DBA: GRANT CREATETAB TO A1. A1 creates Employee and Department, then runs:

GRANT INSERT, DELETE ON Employee, Department TO A2;
GRANT SELECT ON Employee, Department TO A3 WITH GRANT OPTION;

Q1: Can A3 execute GRANT SELECT ON Employee TO A4?

Yes — A3 was granted SELECT WITH GRANT OPTION, so A3 can pass that privilege on to A4.

Q2: If A1 then runs REVOKE SELECT ON Employee FROM A3, does A4 still have SELECT on Employee?

No — A4’s privilege is automatically revoked too, by propagation (there’s no longer a path from the root down to A4).

Q3: What type of access control is this?

Discretionary access control — privileges are granted/revoked at the discretion of individual account owners, rather than being fixed by a system-wide security classification (MAC) or role hierarchy (RBAC).

Mandatory Access Control (MAC)

An additional policy that classifies both data and users into security classes, for multilevel security. Typical classes (low to high): Unclassified (U) < Confidential (C) < Secret (S) < Top Secret (TS). Users are called subjects; data (table, tuple, or attribute) are objects.

The Bell-LaPadula model

  • Simple security property (“no read up”, NRU) — a subject can’t read an object with a higher sensitivity label than the subject’s own. E.g. a user with clearance U cannot view a salary value classified C.
  • Star property (“no write down”, NWD) — a subject can’t write to an object with a lower sensitivity label than the subject’s own — this prevents information flowing from a higher to a lower classification. E.g. a user with clearance S cannot insert a new tuple containing only C-classified information (that would let a lower-clearance user infer something about a higher-clearance operation via the write).

Each tuple gets a Tuple Classification (TC) — the highest classification of any of its attribute values.

(a) EMPLOYEE (original)
Name       Salary      JobPerformance  TC
Smith (U)  40000 (C)   Fair (S)        S
Brown (C)  80000 (S)   Good (C)        S

(b) EMPLOYEE, as seen by a Confidential-clearance user
Name       Salary      JobPerformance  TC
Smith (U)  40000 (C)   NULL (C)        C
Brown (C)  NULL (C)    Good (C)        C

(c) EMPLOYEE, as seen by an Unclassified-clearance user
Name       Salary   JobPerformance  TC
Smith (U)  NULL(U)  NULL (U)        U

A C-clearance user sees Smith’s JobPerformance as NULL (it’s actually S-classified — above their clearance) and Brown’s Salary as NULL (S-classified).

Inference attacks and polyinstantiation

Suppose a C-clearance user runs UPDATE EMPLOYEE SET JobPerformance = 'Excellent' WHERE Name = 'Smith' (working from view (b), where they only ever saw NULL for that field). The system must not reject this — rejecting it would let the user infer that a real, higher- classified value already exists (a covert channel leak). The solution is polyinstantiation — maintaining multiple tuples with the same key but different classifications:

(d) EMPLOYEE, polyinstantiated
Name       Salary      JobPerformance    TC
Smith (U)  40000 (C)   Fair (S)          S
Smith (U)  40000 (C)   Excellent (C)     C
Brown (C)  80000 (S)   Good (C)          S

Both the original S-classified fact and the new C-classified “fact” now coexist — a C-clearance user sees “Excellent”; an S-or-higher user still sees the original “Fair” (plus, depending on implementation, awareness that a lower-classified duplicate also exists).

DAC vs. MAC

Discretionary Mandatory
Flexibility High — owners choose who gets what Low — rigid, centrally imposed
Propagation control None — DAC doesn’t restrict how information, once accessible, is further shared Strong — enforces classification rules on every read/write
Protection Weaker Stronger — prevents illegal information flow

Role-Based Access Control (RBAC)

Permissions are associated with organisational roles, and users are assigned to the appropriate role(s) — roles can then have DAC or MAC methods applied to them as a unit.

CREATE ROLE manager;
DROP ROLE manager;

GRANT ROLE full-time TO emp_typ1;
GRANT ROLE intern TO emp_typ2;

GRANT privilegeName ON objectName TO {userName | PUBLIC | roleName} [WITH GRANT OPTION];
REVOKE privilegeName ON objectName FROM {userName | PUBLIC | roleName};

A typical hierarchy: users → groups → roles → privileges (e.g. alice is in group admin, which maps to admin_role, which grants ALL ON SERVER server1). RBAC’s flexibility and easier administration make it a popular choice for web-based applications.

Other control measures

Inference control

Statistical databases provide aggregate statistics about a population (e.g. for government statisticians or market researchers) without exposing individual-level confidential data — only statistical queries using aggregates (COUNT, SUM, MIN, MAX, AVG, standard deviation) are permitted:

SELECT COUNT(*) FROM PERSON WHERE <condition>;
SELECT AVG(Income) FROM PERSON WHERE <condition>;

The risk: a narrow enough WHERE condition can isolate a group small enough (even a single individual) that an “aggregate” query effectively discloses that person’s data. Mitigations:

  • k-anonymity — enforce a minimum threshold on the number of tuples any query’s result can be based on.
  • Prohibit sequences of queries that all refer to the same (or overlapping) population of tuples, which together could be combined to isolate an individual.
  • Differential privacy — introduce carefully calibrated noise/ inaccuracy into results.

Flow control

Regulates the distribution of information among accessible objects, verifying information doesn’t flow — explicitly or implicitly — into less protected objects (this is exactly the Bell-LaPadula “no write down” idea, generalised). A flow policy specifies which channels information may move along — e.g. in a simple confidential (C) / nonconfidential (N) scheme, flow from C to N is prohibited. Example: an income tax computing service might be allowed to retain a customer’s address, but not their income/deductions data.

Data encryption

Encryption converts data into ciphertext, using an encryption algorithm and a key; recovering the original data requires the corresponding decryption key.

  • DES (Data Encryption Standard) — developed by the US government for general public use.
  • AES (Advanced Encryption Standard) — a newer, more difficult to crack standard.

SQL injection

SQL injection is one of the most common threats to a database system: an attacker injects string input through an (often web-facing) application in a way that changes or manipulates the resulting SQL statement to their advantage.

Methods

  • SQL manipulation — changes an existing SQL command, e.g. adding conditions to a WHERE clause. Classic target: the login form.
  • Code injection — adds entirely new SQL statements/commands by exploiting improper handling of untrusted input.
  • Function call injection — inserts a database or OS function call into a vulnerable SQL statement, to manipulate data or make a privileged system call.

SQL manipulation example

A naive login check builds its query by directly concatenating user input:

SELECT * FROM Users WHERE Username = '<input>' AND Password = '<input>';

Normal login (Jack / Pass123) produces WHERE Username = 'Jack' AND Password = 'Pass123' — matches the stored row, access granted. But an attacker who knows the username Jack can enter the password field as:

' OR 'x'='x

giving the executed query:

SELECT * FROM Users WHERE username = 'Jack' AND (password = '' OR 'x'='x');

'x'='x' is always true, so the AND collapses to just username = 'Jack' — the row is returned regardless of the actual password, and access is granted.

Code injection example

Going further, appending a second statement via a semicolon:

wrongpass' OR 'x'='x'; drop table Users;

If the application naively executes whatever SQL string results (and the driver/DBMS allows statement-stacking), this doesn’t just bypass login — it can destroy the entire Users table.

Risks associated with SQL injection

  • Database fingerprinting — determining the type of database in use (to target version-specific exploits).
  • Denial of service — denying service to valid users.
  • Bypassing authentication — gaining access without valid credentials (as shown above).
  • Identifying injectable parameters — learning about the backend structure through error messages/behaviour.
  • Executing remote commands — running harmful commands remotely (e.g. via function call injection).
  • Performing privilege escalation — upgrading the attacker’s own access level.

Protection techniques

  • Bind variables (parameterized statements) — pass user input as bound parameters, never concatenated directly into the SQL string. This is the primary defence: the database driver treats parameter values purely as data, never as executable SQL syntax — and as a bonus, it also improves performance (query plans can be cached and reused).
  • Filtering input (input validation) — strip/escape characters (like unescaped quotes) that could otherwise be used to break out of a string literal and inject manipulation.
  • Function security — restrict which standard and custom database functions are callable, to limit the blast radius of function call injection.

Summary

You should now be able to explain the threats to database security and the CIA principle; describe DAC, MAC, and RBAC and apply them (given grant/revoke commands, or a Bell-LaPadula classification scenario) to determine resulting access; and explain SQL injection, its risks, and how to defend against it. This completes the course content. See week12-tutorial-5-1-database-security for practice.

Reading: Elmasri & Navathe Chapter 30.

Tutorial 5.1: Database Security

Practice for 2026-05-11-database-security.

Section A — Discretionary Access Control

Universe of discourse: Rob’s Convenience Store has a database system to manage business activities. System admin Paris has created accounts for herself and four staff members, with these requirements:

  • Paris (sysadmin) — full access to the whole database, and can grant permissions to others.
  • Rachel (HR manager) — full access to Employee; can view (but not modify) Restock.
  • Aaron (supply chain lead) — full access to Item, Restock, and Sale.
  • Chris (customer service) — full access to Customer; read access to Sale.
  • Emily (security manager, investigates missing stock) — read access to Sale and Restock; can view only the fName/lName attributes of both Employee and Customer.

Schema: Employee [id, fName, lName, role, email], Restock [employee, item, time, quantity] (FKs to Employee.id, Item.id), Item [id, name, description, price], Sale [customer, item, time, quantity] (FKs to Customer.id, Item.id), Customer [id, fName, lName, dob].

Question A.1 — Identify and fix incorrect access

The permission table below (as currently configured) contains several errors. Fix it, using the UoD above (- = not directly granted).

Paris Rachel Aaron Chris Emily
Employee ALL ALL UPDATE — SELECT
Restock ALL ALL ALL — SELECT
Item ALL — ALL — —
Sale — — ALL UPDATE, GRANT —
Customer SELECT, UPDATE — SELECT ALL —
Customer.fName/lName — — — — SELECT
Employee.fName/lName — — — — —

Corrected table:

Paris Rachel Aaron Chris Emily
Employee ALL ALL — — —
Restock ALL SELECT ALL — SELECT
Item ALL — ALL — —
Sale ALL — ALL SELECT SELECT
Customer ALL — — ALL —
Customer.fName/lName — — — — SELECT
Employee.fName/lName — — — — SELECT

Errors fixed (bold): Rachel had ALL on Restock (should only be SELECT — she’s not allowed to make changes there). Aaron had UPDATE on Employee and SELECT on Customer (should have none — he’s supply chain, not HR/customer-facing). Chris had UPDATE, GRANT on Sale (should be SELECT only, and definitely no grant option — he only needs read access, and never had a stated need to further delegate access). Emily had no access to Sale at all (should have SELECT, per her investigative role) and no access to Employee.fName/lName (should have SELECT, matching her Customer.fName/lName access) — and Paris, as sysadmin, needed ALL on Sale and Customer (both were missing/blank), consistent with her full-database access.

Question A.2 — SQL grant/revoke queries

Write the SQL queries to fix the incorrectly-configured accounts from A.1.

-- Rachel
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Restock` FROM 'Rachel';
GRANT SELECT ON `Rob's Convenience Store`.`Restock` TO 'Rachel';

-- Aaron
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Employee` FROM 'Aaron';
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Customer` FROM 'Aaron';

-- Chris
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Sale` FROM 'Chris';
REVOKE GRANT OPTION ON `Rob's Convenience Store`.`Sale` FROM 'Chris';
GRANT SELECT ON `Rob's Convenience Store`.`Sale` TO 'Chris';

-- Emily
REVOKE ALL PRIVILEGES ON `Rob's Convenience Store`.`Employee` FROM 'Emily';
GRANT SELECT ON `Rob's Convenience Store`.`Sale` TO 'Emily';
GRANT SELECT (`fName`, `lName`) ON `Rob's Convenience Store`.`Employee` TO 'Emily';

Section B — Mandatory Access Control

Universe of discourse: ASIO tracks undercover agents’ identities in a MAC database, to keep classified information from riskier assignments protected.

Schema: Spy [ID, fName, lName, dob, email], CodeName [spyID, name, dateEffective, dateExpired, operation] (FK spyID → Spy.id, operation → Operation.name), Operation [name, description, budget].

DBMS accounts (low to high): Cody = U, Jane = C, Tanya = S, Jack = TS.

Sample data, with each attribute’s classification noted, and the overall Tuple Classification (TC = the highest classification of any attribute present):

Spy — id(U), fName(U), lName(TS), dob(C), email(S)
id  fName  lName  dob         email                 TC
1   Daniel Teal   18/06/1995  bmw@hotmail.com        TS
2   May    Lee    03/02/1998  may@stevefam.com       TS
3   John   Smith  12/05/1990  john@smith.com         TS
4   April  Fuller 25/06/1980  ilikefood@korea.com    TS

CodeName — spyID always (C); name/operation classification varies per row;
dateEffective/dateExpired always (U)
spyID  name              dateEffective  dateExpired  operation      TC
1      Handbrake (C)     01/02/2019     03/06/2020   Le-Ferrari (S) S
2      BunnyBunny (S)    12/03/2019     12/04/2019   Hotpot (C)     S
4      BubbleTea (C)     19/04/2019     24/04/2019   Hotpot (C)     C
2      BBQ (TS)          27/09/2019     04/10/2019   KimChi (TS)    TS

Operation
name         description                                    budget       TC
Hotpot (S)   "Infiltrate McDonalds..." (S)                  1000000 (S)  S
Le-Ferrari (C) "Identify the Ferrari-stealing syndicate leader" (U) 10000000 (U) C
KimChi (S)   "Someone stole ASIO's KimChi supply..." (S)     500000000 (S) S

Question B.1 — Identifying security denials

For each query, determine whether it succeeds (S) or fails (F), and why.

User Query Result Reason
Cody SELECT id FROM Spy; S Cody’s clearance (U) ≥ the classification of every id value (U).
Cody SELECT name FROM Operation WHERE budget > 10000000 F The one matching tuple (KimChi) has TC = S, above Cody’s U clearance — the query returns nothing even though the WHERE condition is logically true for it.
Cody INSERT INTO CodeName (spyID, name, dateEffective, dateExpired, operation) VALUES (2, "BBQ", "16/05/2019", "26/05/2019", "Hotpot") S Looks like it should violate the key constraint (another spyID=2, name="BBQ" combination already exists at TS) — but since the two “identical” tuples have different security classifications, the DBMS applies polyinstantiation to let them coexist.
Jack INSERT INTO Operation (name, description, budget) VALUES ("Ace", "Find an unidentified hacker...", 350000) F Operation’s highest classification is S. Jack’s clearance is TS — higher than S — so the star property (no write down) blocks this insert, to prevent Jack from writing lower-classified information into a table that could then leak higher-clearance context.
Jane UPDATE CodeName SET name = "Iced Coffee" WHERE spyID = 4 S Jane’s clearance (C) ≥ the classification of spyID (C, so she can read/select the row) and ≤ the classification of name for that row (C, so she can write it) — both read and write permission hold, so the update succeeds.

Question B.2 — Returning data in a MAC database

What data is returned for each query?

Jane: SELECT spyID, name, operation FROM CodeName →

spyID name operation
1 Handbrake NULL
2 NULL Hotpot
4 BubbleTea Hotpot

(Row spyID=2, name="BBQ" is entirely TS-classified — above Jane’s C clearance — so it doesn’t appear at all. Within the visible rows, any individual cell above Jane’s clearance is masked to NULL rather than hiding the whole row — e.g. row 1’s Handbrake is C, visible, but row 2’s BunnyBunny is S, masked.)

Jane: SELECT name, dob FROM CodeName, Spy WHERE id = spyID AND dateEffective < "01/06/2020" →

name dob
Handbrake 18/06/1995
NULL 03/02/1998
BubbleTea 25/06/1980

Cody: SELECT name, budget FROM Operation WHERE description LIKE "%a%" →

name budget
NULL 10,000,000

(Only Le-Ferrari — TC=C — is within reach of Cody’s U clearance enough to appear at all, and even then its name attribute specifically, being C-classified, is masked to NULL for a U user; budget for that row is U-classified so it’s shown.)

Jane: SELECT spyID, Operation.name, Operation.budget FROM Operation, CodeName WHERE Operation.name = CodeName.operation → empty set. Every possible join row pairs a CodeName.operation value with an Operation.name value where at least one side is classified S or TS — above Jane’s C clearance — so no valid (joinable, visible-to- Jane) pairing exists, and the join returns nothing.

Bonus — Completing a database audit

Universe of discourse: SummerStyles (a clothing retailer) audits its DBMS log monthly for unauthorised changes or potential privacy violations.

Using the log below, flag any potentially suspicious records (there’s no exact formula — use judgement).

event_time            user_host                thread_id  argument
17-05-2020 8:27:18am   SystemAdmin@localhost    2097       SHOW COLUMNS FROM SummerStyles.customer
17-05-2020 8:28:00am   SystemAdmin@localhost    2097       SHOW INDEXES FROM SummerStyles.customer
17-05-2020 8:30:07am   HRManager@localhost      2103       UPDATE SummerStyles.staff SET Wage = Wage * 1.1
17-05-2020 8:31:07am   CustomerService@localhost 2129      SELECT * FROM SummerStyles.customer WHERE fName="Elaine" AND lName="Wang"
17-05-2020 8:33:30am   SystemAdmin@localhost    2129       SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA='SummerStyles' AND TABLE_NAME='Customer'
17-05-2020 8:35:05am   CustomerService@localhost 2129      SELECT fName, lName, max(wage) FROM SummerStyles.staff
17-05-2020 8:35:43am   StockManager@localhost   2129       DELETE FROM SummerStyles.deliveries WHERE id = 204472
17-05-2020 8:38:32am   StockManager@localhost   2128       UPDATE SummerStyles.currentStock SET quantity = quantity + 8 WHERE stockID = 1263
17-05-2020 8:41:14am   SystemAdmin@localhost    2129       ALTER TABLE SummerStyles.staff ADD email varchar(255);
17-05-2020 8:45:41am   HRManager@localhost      2129       INSERT INTO SummerStyles.staff (id, fName, lName) VALUES (142, "Leya", "Rebecca")
17-05-2020 8:57:37am   HRManager@localhost      2131       UPDATE SummerStyles.stock SET price = 11.00 WHERE name LIKE "%Calvin Klein%"

Two entries stand out as worth flagging:

  1. 8:35:05am — CustomerService: SELECT fName, lName, max(wage) FROM SummerStyles.staff. A customer service rep querying staff wage data is well outside their normal remit — salary information is HR’s domain (per the DAC exercise above, this kind of cross-role access is exactly what discretionary permissions should normally prevent) — a potential unauthorised-access/privacy concern worth following up.
  2. 8:57:37am — HRManager: UPDATE SummerStyles.stock SET price = 11.00 WHERE name LIKE "%Calvin Klein%". An HR manager modifying product pricing is outside HR’s normal duties (that’s StockManager’s job, as seen elsewhere in the log) — an unauthorised-modification concern, and potentially indicative of privilege misuse.

Reference material

ER Diagram Notation

Reference legend for the symbols used across every ER/EER diagram in this course — introduced across 2026-02-23-conceptual-database-design-and-the-er-model and 2026-03-02-weak-entities-the-eer-model-and-design-choices.

Core symbols

Symbol Meaning
Rectangle Entity type
Double-line rectangle Weak entity type (no key attribute of its own)
Oval Attribute
Oval, name underlined Key attribute
Oval, name underlined with a dotted line Partial key attribute (weak entity’s own distinguishing attribute)
Double-line oval Multivalued attribute (can hold more than one value)
Dashed-line oval Derived attribute (computed from another stored attribute, e.g. Age from BirthDate)
Diamond Relationship type
Double-line diamond Identifying relationship type (connects a weak entity to its owner entity)
  • Composite attribute: an attribute with its own sub-attributes attached (e.g. Name splitting into FirstName/MiddleName/LastName) — the parent attribute oval isn’t underlined/dashed/doubled itself unless it’s also a key/derived/multivalued attribute.
  • Value sets (the domain of legal values for an attribute, e.g. “integers 21-65”) are never shown on the diagram itself.

Relationship constraints

See relationship-constraints for cardinality ratio (1:1, 1:N, M:N) and participation (total/partial) notation.

Subclasses / superclasses (EER)

Symbol Meaning
⊂ between two entity types The entity type on the narrow side is a subclass of the one on the wide side
A circle joining several subclasses to one superclass Several entity types are all subclasses of the same superclass
d inside that circle Disjoint specialization — an entity instance can belong to at most one of the subclasses
o inside that circle Overlapping specialization — an entity instance can belong to more than one subclass at once
Single line from superclass to the subclass circle Partial specialization — not every superclass instance needs to belong to a subclass
Double line from superclass to the subclass circle Total specialization — every superclass instance must belong to at least one subclass

Naming conventions (INFS1200/7900 style guide)

Course-specific naming standard for entities/relationships/attributes on ER and EER diagrams (distinct from — and in addition to — the mandatory notation above):

  • Entity names: capitalised, no spaces, ideally one word (e.g. STUDENT, POLICEOFFICER — not PoliceOfficer).
  • Relationship names: capitalised, no spaces, ideally one word, preferably a verb (e.g. CREATES, ACTSIN — not LeadActor).
  • Attribute names: UpperCamelCase — first letter of each word capitalised, no spaces, acronym letters stay capitalised (e.g. ComputerIP, DateOfBirth — not Date of Birth).

How diagrams are drawn in these notes

Diagrams in this course’s notes are drawn as Mermaid flowchart graphs (renders in both PDF and HTML), using shapes chosen to match the Chen notation above as closely as Mermaid’s flowchart shapes allow:

Mermaid shape Used for
[Entity] (rectangle) Entity type
[[WeakEntity]] (subroutine, double vertical bars) Weak entity type
{Relationship} (diamond/rhombus) Relationship type
([Attribute]) (stadium) Attribute
((d)) / ((o)) (small circle) Disjoint/overlapping specialisation marker

Since Mermaid can’t reliably underline/dash node text across renderers, attribute qualifiers are written as a plain-text suffix instead: (key), (partial key), (derived), (multi). Composite attributes are drawn as a parent attribute node connected to its component attribute nodes. Cardinality is a text label on the edge ("1", "N", "M"); total participation/identifying relationships use a thick edge (===/==>), partial participation uses a thin edge (---/-->).

Any diagram with more than ~6-8 nodes needs an explicit %%| fig-width: cell option (in inches) or it runs off the right edge of the PDF page - mermaid sizes itself from the diagram’s own SVG bounding box, and there’s no project-wide default that reaches it (a document/project fig-width only applies to executed-code-cell figures, e.g. matplotlib). 5.5 fits this vault’s PDF page width (scrartcl, DIV=11, letter) comfortably:

```{mermaid}
%%| fig-width: 5.5
flowchart TD
    ...
```

Relational Mapping Notation

Reference legend + course style guide for writing relational schemas — introduced across 2026-03-09-the-relational-model-and-integrity-constraints and 2026-03-16-er-to-relational-mapping.

Schema notation

  • Relation [attr1, attr2, ...] — a relation schema; the (primary) key attribute(s) are underlined.
  • A composite primary key gets a single continuous underline spanning all of its attributes, e.g. Enrolment [studentId, courseCode, sem, year].
  • Relation.fk references OtherRelation.pk — a foreign key constraint, listed directly under the relation it belongs to.
  • Relation.{fk1, fk2} references OtherRelation.{pk1, pk2} — a composite foreign key referencing a composite primary key.

Naming convention (INFS1200/7900 style guide)

  • Table names: UpperCamelCase (first letter of each word capitalised, no spaces) — e.g. Flight, not FLIGHT.
  • Attribute names: lowerCamelCase (first letter of each word from the second word onwards capitalised) — e.g. departureTime, not Departure Time. Acronym attribute names stay entirely lowercase (e.g. eta, not ETA).
  • A space separates the table name from the opening bracket: Flight [planeNumber, ...], not Flight[planeNumber, ...].

Layout convention

A table’s foreign key constraint lines go directly underneath that table’s own definition, with a blank line before the next table starts — not all grouped together under a separate “Foreign Keys:” heading at the end:

Employee [ssn, firstName, lastName, dob, manager]
Employee.manager references Employee.ssn

Dependant [ssn, name, dob]
Dependant.ssn references Employee.ssn

DependantPhoneNumber [ssn, name, phoneNumber]
DependantPhoneNumber.{ssn, name} references Dependant.{ssn, name}

Relationship Constraints

Constraints on a relationship type limit the possible combinations of entities that may participate in its relationship set — determined by the Universe of Discourse (UoD), not chosen arbitrarily. Introduced in 2026-02-23-conceptual-database-design-and-the-er-model; see er-diagram-notation for the full symbol legend.

Cardinality ratio

Specifies the number of relationship instances an entity can participate in.

Ratio Meaning
1:1 Both entities can participate in only one relationship instance
1:N One entity can participate in many relationship instances (the “1” side, only one of the “N” side per instance)
N:1 Same relationship, viewed from the other side
M:N Both entities can participate in many different relationship instances

Example: EMPLOYEE WORKSFOR DEPARTMENT — “each department can have any number of employees, but an employee can work for at most one department” is N:1 (N on the EMPLOYEE side, 1 on the DEPARTMENT side).

Participation constraint (existence dependency)

Indicates whether an entity’s existence depends on its relationship to another entity — i.e. whether every instance of the entity type must participate in at least one relationship instance.

  • Total participation (double line in the diagram) — every entity instance must participate. E.g. “every employee must work for a department” — EMPLOYEE has total participation in WORKSFOR.
  • Partial participation (single line) — an entity instance is not required to participate. E.g. “every employee can manage 0 or more departments” — EMPLOYEE has partial participation in MANAGES.

Weak entities

An entity type with no key attribute of its own is a weak entity — it can only be identified uniquely by combining the primary key of its owner entity with its own partial key (underlined with a dotted line). The relationship linking a weak entity to its owner is the identifying relationship, and the weak entity always has total participation in it (it can’t exist without its owner). Both the weak entity box and the identifying relationship diamond are drawn with double lines. A weak entity can have two (or more) owner entity types, in which case the identifying relationship is n-ary rather than binary.