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, and dbms-fundamentals for the Section A material.

Purpose: practical experience in using conceptual modelling, such as ER diagrams, to capture important aspects of a system (Universe of Discourse). By the end of the class you should be able to identify key DBMS architecture concepts, identify key components of an ER diagram, create semantically correct ER diagrams, develop a basic ER diagram from a UoD, and evaluate and justify basic design choices.

TipAbout the diagrams below

The solution diagrams here are drawn in TikZ rather than in the Mermaid approximation described at the bottom of er-diagram-notation, so that the mandatory Chen symbols render literally: a solid underline for a key attribute, a dashed oval for a derived attribute, a double-outlined oval for a multivalued attribute, and a double line into a diamond for total participation. The shapes are unfilled (black on white) rather than in the purple-entity / magenta-diamond / grey-oval house fills.

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.

Enter the numbers matching External Level, Logical data independence, Conceptual Level, Physical data independence, Internal Level, in that order, comma-separated:

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

The two easy-to-swap pairs are the ones to check. Levels describe structure; independence properties describe what you are allowed to change without disturbing something else. And logical independence is about the conceptual↔︎external boundary, physical independence about the internal↔︎conceptual one — the three-schema figure in dbms-fundamentals brackets each property onto exactly the mapping it rides.

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.

The rule that separates them: a DBMS enforces the design it is handed, it does not choose it. Every option phrased as a design decision is a distractor.

Section B — Entities and attributes

You draw an ER diagram for one entity from a RESTAURANT example. The RESTAURANT database keeps track of a restaurant’s chefs, dishes and other information.

Question 1 — RESTAURANT: CHEF entity

The system stores each chef’s name (which consists of their first name, middle name and last name), which is unique. Their date of birth and age are also recorded, along with any specialisations that they have.

Four different attribute kinds appear in one small diagram, which is the point of the question:

  • Name is both composite (it splits into FirstName, MiddleName, LastName) and the key — “which is unique” attaches to the whole name, so the underline goes on the parent Name oval, not on the three components.
  • DoB is a plain simple attribute.
  • Age is derived — it is computable from DoB, so it is not stored independently.
  • Specialisations is multivalued — “any specialisations”, plural, with no fixed number.

Figure 1: The CHEF entity. Underline = key, dashed oval = derived, double outline = multivalued; the three name components hang off the composite Name oval rather than off CHEF directly. The derived Age has a dashed oval but a solid connecting line here; the course notation dashes the connecting line too.

Section C — Basic relationships

Create an ER (or EER) diagram for each Universe of Discourse below. When choosing a cardinality or participation constraint, justify the choice from the UoD. It is worth doing the same in the assignments. Where the UoD is silent on some aspect, make a reasonable decision and write it down as an assumption beneath the diagram rather than leaving it implicit.

The justification tables in the workings below match each constraint in the diagram back to the sentence in the UoD that motivates it.

Question 1 — Olympics database

An athlete is identified by a unique athleteID and their name, age, sex and representative country are also stored. Each event is given a unique eventID and the name of the event and category are also recorded. For venues, a unique ID is stored, along with the venue’s name and its address.

Each athlete participates in at least one event and their final placement for each event is also recorded. Each event is held at one venue. Some venues are backups and hence not every venue stored in the system will be used to host an event.

Three entity types, each with its own key; one M:N relationship carrying an attribute; one N:1 relationship.

Figure 2: The Olympics ER diagram. Double lines mark total participation, and there are three of them: ATHLETE and EVENT are both total in PARTICIPATES, and EVENT is total in HOSTS. VENUE’s single line into HOSTS is the one partial participation, and it is what encodes the backup venues.

Reading the constraints back off the diagram, each against its sentence in the UoD:

Constraint Justification from the UoD
PARTICIPATES is M:N An athlete may enter several events, and an event has many athletes
Placement sits on the relationship, not on either entity A placement belongs to an athlete in an event, not to the athlete alone
ATHLETE total in PARTICIPATES “Each athlete participates in at least one event”
EVENT total in PARTICIPATES Implied — an event with no athletes is not an event being run
HOSTS is N:1 from EVENT to VENUE “Each event is held at one venue”; a venue may host several
EVENT total in HOSTS Every event is held somewhere
VENUE partial in HOSTS “Some venues are backups… not every venue will be used to host an event”

Question 2 — University transcripts database

The university keeps track of each student’s name, student number and social security number. The department each student majors in is also recorded. If the student has a minor, then that department is also recorded. Both social security number and student number have unique values for each student. Students can also take courses and for each course they take a grade is recorded.

Each department is described by a name, department code and office number. Both name and code have unique values for each department. A department may manage some courses.

Each course has a course name, course number, description and offering department. The value of course number is unique for each course.

The interesting parts here are the two entity types that each have two candidate keys and the two relationships between the same pair of entity types.

Figure 3: The university-transcripts ER diagram. STUDENT and DEPARTMENT each carry two underlined (candidate-key) attributes. MAJORS and MINORS are two separate N:1 relationships between the same pair of entity types, and only MAJORS gets the double line — every student majors somewhere, but a minor is optional. COURSE is likewise total in OFFERS, since every course has an offering department.

What each decision rests on:

Decision Justification from the UoD
StudentNo and SSN both underlined “Both social security number and student number have unique values for each student” — two candidate keys, drawn as two separate underlined ovals
Name and Code both underlined on DEPARTMENT “Both name and code have unique values for each department”
TAKES is M:N with attribute Grade A student takes many courses and a course has many students; the grade belongs to the pair
Separate MAJORS and MINORS relationships They are two different facts about a student, both N:1 to DEPARTMENT — one relationship with a “kind” attribute would lose the constraint that a student has at most one major
STUDENT total in MAJORS, partial in MINORS “The department each student majors in is also recorded” (unconditional) versus “if the student has a minor” (conditional)
COURSE total in OFFERS Every course has an “offering department” listed among its attributes in the UoD
DEPARTMENT partial in OFFERS “A department may manage some courses”

Section D — Analysis & application

Uses the same Olympics UoD and diagram as Section C question 1 (Figure 2).

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. True. Address isn’t underlined on VENUE — only ID is — so nothing in the diagram stops two venues sharing an address.
  2. False. EVENT takes part in PARTICIPATES and HOSTS, and both are binary. A ternary relationship would be one diamond with three entity types on it.
  3. True by definition: ATHLETE is drawn as a rectangle, Age as an oval hanging off it.
  4. True. A relationship instance in the ER model is a pair, so the same (athlete, event) pair can occur at most once in PARTICIPATES — which is also why Placement can sit on the relationship at all. Recording repeat entries would need a different design.
  5. False — VENUE’s participation in HOSTS is drawn with a single line, i.e. partial, matching “some venues are backups”.

Question 2 — Total participation, semantically

What does the total participation on both sides of the PARTICIPATES relationship mean semantically? Does this align with the specification in the UoD?

Total participation from ATHLETE and EVENT to the PARTICIPATES relation signifies each athlete in the system participates in at least one event and each event has at least one athlete participating in it. This is in line with the specification presented in the UoD.

The ATHLETE half is stated outright (“each athlete participates in at least one event”). The EVENT half is the one to be able to defend: an Olympic event with no athletes entered is not an event that is being run, so requiring at least one participant per event is the reasonable reading — and under the Section C instruction, exactly the sort of decision you would note as an assumption if challenged on it.

Question 3 — Redraw VENUE with two candidate keys

Redraw the VENUE entity so that a venue can be identified by either its ID or the combination of its name and address.

VENUE gets two underlined ovals, one per candidate key. The second is a composite key — a parent oval named Key with Name and Address hanging off it, so the underline lands on the composite as a whole rather than on either component separately.

Figure 4: VENUE with two candidate keys. Two underlined ovals, not one: ID alone identifies a venue, and so does the composite Name+Address. Underlining Name and Address individually would say something different and wrong — that each is a key on its own.

This redraw contradicts Section D question 1 statement (1): once Name+Address is a key, two venues can no longer share both — though they could still share an address alone.

Section E — Additional resources

Recommended as revision practice for the assessment.

Question 1 — RESTAURANT: DISH entity

The restaurant serves many dishes, each of which has a unique name on the menu. Each dish also has a unique number and associated description, which consists of a brief history of the dish, the number of calories in the dish, and the price. The preparation time for each dish is also recorded, along with its ingredients.

Two candidate keys again — Name (“a unique name on the menu”) and Number (“a unique number”) — each drawn as its own underlined oval. Description is a composite attribute splitting into History, Calories and Price. PreparationTime is simple, and Ingredients is multivalued.

Figure 5: The DISH entity. Only Ingredients carries the double outline of a multivalued attribute; History is an ordinary component of the composite Description.

Compare with the CHEF answer in Section B: same restaurant UoD, same four attribute kinds to distinguish, but here the two keys are two separate simple attributes rather than one composite one, and there is no derived attribute to spot.