Applied Class 1: Intro to DBMS and Basic ERD

exercises
tutorial
databases
database-design
er-model

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