COMP9120 · Database Management Systems
Relational Model & ER→Relational Mapping
This chapter of the COMP9120 Database Management Systems study guide at the University of Sydney is the bridge from a conceptual ER diagram to a database schema. It defines relations, superkeys, candidate keys, primary keys and foreign keys, then sets out the fixed rulebook for translating every ER construct — strong and weak entities, 1:N and N:M relationships, IsA hierarchies and n-ary relationships — into relations with correct keys. It is one of the most mechanical and bankable topics in the unit, examined both in Assignment 1 and in the ER-mapping question that opens the final exam.
What this chapter covers
- 01What a relation is: a set of tuples over a schema with atomic domains
- 02Schema vs instance, degree (arity) and cardinality
- 03The key hierarchy: superkey ⊇ candidate key (minimal) → primary key
- 04Foreign keys and referential integrity: every FK must point at a real row
- 05The 1:N rule: post the one-side primary key as a foreign key on the many side
- 06The N:M and n-ary rule: build a new relation keyed on the combined foreign keys
- 07Weak entities: primary key = owner's primary key + discriminator
- 08IsA / subclass mapping: subclass primary key is also a foreign key to the superclass
- 09Reading cardinality to decide FK-column versus new-relation
- 10How the mapping question is marked and how to bank the structural marks
Map a clinic ER diagram to relations with keys
- +2Strong entities become relations keyed on their own attribute: Doctor(doctor_id [PK], name, speciality).
- +2The second strong entity likewise: Patient(patient_id [PK], name, dob).
- +3The weak entity borrows the owner's primary key as a foreign key and adds its discriminator: Prescription(patient_id [FK→Patient], script_no, drug), with primary key = the pair (patient_id, script_no). Here patient_id is both a PK part and an FK.
- +3The N:M relationship becomes a new relation with a foreign key to each side and any relationship attribute as a column: Consults(doctor_id [FK→Doctor], patient_id [FK→Patient], since), with primary key = the pair (doctor_id, patient_id).
Key terms
- Relation
- A set of tuples over a fixed schema; each attribute draws atomic values from a domain. In the pure model there are no duplicate tuples and no row order (a real table may relax this).
- Tuple / attribute / domain
- A tuple is one row of a relation; an attribute is a named column; a domain is the set of atomic values an attribute may take. Degree is the number of attributes, cardinality the number of tuples.
- Superkey
- Any set of attributes whose values uniquely identify each tuple. The full attribute list is always a superkey; smaller unique sets are the useful ones.
- Candidate key
- A minimal superkey: no proper subset of it is still unique. A relation may have several candidate keys.
- Primary key
- The one candidate key chosen to identify tuples. It is automatically UNIQUE and NOT NULL, and there is exactly one per relation; conventionally underlined.
- Foreign key
- An attribute set in one relation that references a candidate key (usually the primary key) of another relation, encoding a relationship between the two.
- Referential integrity
- The rule that every foreign-key value must match an existing key value in the referenced relation (or be NULL); a mismatch is a dangling reference. On parent delete/update the options include NO ACTION, CASCADE, SET NULL and SET DEFAULT.
- Weak entity
- An entity with no key of its own; it depends on an identifying owner. Its relation includes the owner's primary key as a foreign key, and its own primary key is that owner key plus a discriminator.
Relational Model & ER→Relational Mapping FAQ
In a 1:N relationship, which table gets the foreign key?
The many (N) side. Each row on the many side relates to exactly one parent, so a single foreign-key column referencing the one-side primary key is enough, and no separate relation is needed. If participation on the many side is total, make that foreign key NOT NULL. Putting the key on the one side would force a single parent row to store many child identifiers, which breaks the atomic-domain rule.
How do I map an N:M relationship that also has its own attribute?
Create a new relation with a foreign key to each participating entity, and add the relationship's attribute as an ordinary column. The primary key is the combination of the two foreign keys, which enforces one row per pair. For example, an employee-project link that records hours becomes WorksOn(empno→Employee, projno→Project, hours) keyed on (empno, projno).
Can AI help me with ER→relational mapping in COMP9120?
Yes, as a study aid. Sia, the AskSia AI tutor, can explain each mapping rule step by step, walk through why a foreign key sits on the many side, and check your reasoning on practice diagrams you build yourself. It is designed to help you understand the method, not to hand over completed answers — it will not do your graded Assignment 1 or exam for you, and no tool can guarantee a mark. Always follow the University of Sydney academic-integrity rules and confirm assessment details on Canvas.
Studying with AI? Sia — free AI data science tutor works through COMP9120 step by step.
Exam move
Drill the mapping rulebook until it is automatic: strong entity → table with its own key; weak entity → owner key + discriminator; 1:N → foreign key on the many side; N:M and n-ary → a new relation keyed on the combined foreign keys; IsA → subclass key that is also a foreign key to the superclass. Practise on your own diagrams, always underlining primary keys and drawing foreign-key arrows, because the marker awards those structural decisions individually. The released practice paper gives the ER-mapping-and-integrity question 30 marks, so in the 2-hour closed-book final work at a little under a minute per mark — around 25 minutes for a 30-mark question — and confirm the exact split on Canvas.
