ER model: cardinality, participation and mapping
Learning goals and notation
Turn a verbal requirement into entities, relationship constraints and relational tables. Distinguish maximum cardinality from minimum participation. No SQL background is needed. We use min–max notation: (0,1) means optional, at most one; (1,N) means at least one, possibly many. Read each pair as the number of relationship occurrences for one entity on that side. Diagram conventions differ, so translate every arrow into a sentence before calculating.
1. Entities, attributes and relationships
An entity is a distinguishable object; an entity set groups similar objects. Student has attributes sid, name and dateOfBirth. sid identifies the student; two students may share a name. A relationship associates entities: Enrolment links Student to Course and may have grade as its own attribute, because grade belongs to a particular student–course pair.
A simple attribute is treated as indivisible in the chosen model. A composite address can have city and postcode components. A multivalued phone attribute permits several phones per student. Age can be derived from dateOfBirth and the current date, so storing age independently creates an update obligation. These classifications depend on the application's modeling requirements.
2. Cardinality is not participation
Suppose each employee works for exactly one department, and a department may have no employees. For WorksFor, Employee has (1,1), Department has (0,N). The relationship is many-to-one from Employee to Department. Employee participation is total; Department participation is partial. A many-to-one constraint alone does not imply that every employee participates.
Worked bounds: there are 12 employees and 4 departments, with the preceding rules. Exactly 12 WorksFor pairs exist, because each employee contributes one pair. All 12 may belong to one department. If every department must instead have at least one employee, the pair count remains 12, but the distribution must use all four departments. With a one-to-one relationship, total participation of both entity sets requires equal entity counts in a finite instance.
For an unconstrained many-to-many relationship between sets with m and n entities, there are at most mn distinct binary pairs. If both sides participate totally and m,n are positive, at least max(m,n) pairs are needed and that bound can be attained. Do not multiply the minimum participations mechanically: one pair serves an entity on each side.
3. Weak entities and identifying owners
An OrderLine is identified only within its Order. Its lineNo is a partial key: line 1 can occur in many orders. The identifying relationship ties each line to exactly one owner, and every line must have an owner. The full relational key becomes (orderId,lineNo). A weak entity is not merely an entity whose chosen identifier happens to be a foreign key; the modeling issue is owner-dependent identification. An owner can have zero lines unless a separate rule requires at least one.
4. Mapping to relations
Strong entities become tables with their identifying key. For many-to-one WorksFor, place deptId in Employee as a foreign key to Department. Make deptId NOT NULL when employee participation is total. Department total participation, if required, is not enforced merely by that foreign key: it requires a cross-row existence rule.
For many-to-many Enrolment, create Enrolment(sid,cid,grade) with primary key (sid,cid) and foreign keys to Student and Course. This assumes a student can enroll in a course at most once. If repeat attempts are allowed, include an attempt identifier in the model and key. For one-to-one relationships, a foreign key plus a uniqueness constraint can enforce the maximum; put it on a totally participating side when convenient to avoid nulls. NOT NULL addresses that side's minimum, not the other side's minimum.
Map multivalued phones to StudentPhone(sid,phone), key (sid,phone), instead of phone1, phone2, phone3 columns. Map OrderLine to OrderLine(orderId,lineNo,quantity), key (orderId,lineNo), with orderId referencing Order. Relationship attributes remain with the relationship representation.
Worked design: Department(did,dname); Employee(eid,ename,did) with eid primary key and non-null did referencing Department(did). This implements exactly one existing department per employee. It permits two employees in the same department and a department with no employee, as required.
Pitfalls and self-check
- A sample database with unique names does not prove name is a key for every legal database.
- Total participation means at least one, not exactly one; maximum cardinality supplies the upper bound.
- A foreign key verifies referenced existence, not that every referenced entity is used.
- A ternary relationship is generally not replaceable by three binary relationships: the original triple association can be lost.
- State whether duplicate relationships or repeated attempts are allowed before choosing a relationship key.
Original practice: six single-correct MCQs
These original teaching questions are not previous-year questions. Choose exactly one answer.
P01. Every employee belongs to exactly one department; departments may be empty. Employee participation is:
A. Total with maximum one B. Partial with maximum one C. Total with unlimited maximum D. Partial with unlimited maximum
Answer: A
A: Exactly one gives minimum one and maximum one.
B: Partial would permit an employee with no department.
C: The stated maximum is one, not many.
D: Both minimum and maximum conflict with the rule.
P02. A binary many-to-many relationship has 3 and 5 entities, all participating. Minimum distinct pairs?
A. 3 B. 5 C. 8 D. 15
Answer: B
A: Three pairs cannot cover five entities on the larger side.
B: Five pairs can cover all five and distribute across the three.
C: Adding entity counts double-counts pair coverage.
D: Fifteen is the maximum, not minimum.
P03. OrderLine has partial key lineNo and owner key orderId. Its natural full key is:
A. lineNo B. orderId C. (orderId,lineNo) D. quantity
Answer: C
A: lineNo may repeat across orders.
B: One order may contain several lines.
C: Owner key plus partial key identifies a line.
D: Quantity need not be unique.
P04. Which mapping best represents multiple phone numbers per student?
A. One phone column only B. StudentPhone(sid,phone), key (sid,phone) C. Make phone the Student primary key D. One global phone row for all students
Answer: B
A: A single scalar phone cannot represent all values.
B: A separate relation records each student–phone association.
C: A student may have multiple phones, so this changes entity identity.
D: A global row loses ownership information.
P05. A non-null Employee.did foreign key to Department guarantees which statement?
A. Every department has an employee B. Every employee references an existing department C. Every department has exactly one employee D. Employee names are unique
Answer: B
A: A foreign key does not require every parent to be referenced.
B: Non-null plus reference existence enforces this.
C: Many employees may reference the same department.
D: No name constraint was given.
P06. Where should grade be stored when it depends on a student and a course together?
A. Student only B. Course only C. Enrolment relationship D. Department name
Answer: C
A: One student can have different grades in different courses.
B: Different students can earn different grades in one course.
C: The student–course association determines the grade in this model.
D: Department name does not identify the enrollment.
Sources and scope
Aligned to the 2026 course. This lesson covers the stated slice, not the entire Databases syllabus. All numerical examples and questions are original. No claim of 2027 completeness.
Sign in to keep your progress. Sign in