Lesson 8: ER Notation, Weak Entities and Case Studies¶
Putting it all together — three complete designs, drawn step by step
About this lesson
Course: Database Management Systems
Unit 2: Entity Relationship Modelling · 7 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 3.7, 3.8, 4.1–4.4
Learning Outcomes
- Reproduce the complete Chen notation from memory.
- Draw a full ER diagram for a described mini-world, with all constraints.
- Apply a repeatable seven-step design procedure.
- Recognise and resolve the standard design traps (fan trap, chasm trap).
- Read the EER extensions: specialisation, generalisation, inheritance.
8.1 The Complete Chen Notation¶
Memorise this table. Every examination question on Unit 2 uses it.
| Symbol | Meaning |
|---|---|
| Rectangle | Entity type (strong / regular) |
| Double rectangle | Weak entity type |
| Diamond | Relationship type |
| Double diamond | Identifying relationship (of a weak entity type) |
| Ellipse | Attribute |
| Ellipse with child ellipses | Composite attribute |
| Double ellipse | Multivalued attribute |
| Dashed ellipse | Derived attribute |
| Underlined name | Key attribute |
| Dash-underlined name | Partial key of a weak entity type |
| Single line | Partial participation |
| Double line | Total participation (existence dependency) |
1, N, M on a line |
Cardinality ratio |
(min, max) on a line |
Structural constraint, both bounds at once |
| Label on a line | Role name |
============ THE COMPLETE NOTATION AT A GLANCE ============
+--------+ +==========+ ( Attr ) (( Multi ))
| ENTITY | || WEAK || simple multivalued
+--------+ +==========+
/------\ /========\ , - - - - . ( Comp. )
< RELSHP > << IDENTIF.>> ( Derived ) / \
\------/ \========/ ` - - - - ' (P1) (P2)
---- single line = PARTIAL participation
==== double line = TOTAL participation
1 / N / M = cardinality (min,max) = structural constraint
___________ underline = key - - - - - dash = partial key
8.2 A Seven-Step Design Procedure¶
Procedure
- Identify entity types. Underline nouns; keep those with properties of their own (Lesson 1).
- Identify relationship types. Verbs connecting two entity types.
- Attach attributes to entity types and to relationship types (Lesson 2 test).
- Determine keys. Underline. Any entity type with no key of its own is weak — find its owner, its identifying relationship, and its partial key.
- Determine cardinality ratios, using the two-question method.
- Determine participation constraints, side by side.
- Review: read the diagram back in English, sentence by sentence, and check every sentence against the requirements.
Step 7 is the one students skip and the one that finds the errors. Read "Every student must be admitted to at least one course, and may be admitted to many" aloud, and ask the registrar whether it is true.
8.3 Case Study 1 — The College Database (our running example)¶
Requirements¶
The college keeps records of its students: roll number (unique), name, and date of birth. It offers courses, each with an identifier (unique), name, total seats, duration, a course type that is either Fulltime or Parttime, and a teacher-in-charge. A student may take admission in any number of courses, and the date of admission is recorded; every student must be admitted to at least one course, but a newly created course may have no students. The college also runs societies, each with an identifier (unique), name, mentor and total seats. Students enrol in societies and the date of enrolment is recorded; enrolment is optional on both sides.
Step-by-step¶
Steps 1–2. Entity types: STUDENT, COURSE, SOCIETY. Relationship types: TAKES_ADMISSION (student–course), ENROLS_IN (student–society).
Step 3. Attributes as listed; Dateofadmission belongs to TAKES_ADMISSION and Dateofenrolment to ENROLS_IN, because each is determined only by the pair (Lesson 3).
Step 4. Keys: Rollno, SID, SocID. All three entity types are strong.
Step 5. Cardinalities. One student, how many courses? many. One course, how many students? many → M:N. Similarly ENROLS_IN is M:N.
Step 6. Participation. STUDENT in TAKES_ADMISSION: total (stated). COURSE: partial (stated). Both sides of ENROLS_IN: partial.
Step 7. Read back: "Every student is admitted to at least one course and may be admitted to many. A course may have no students, or many. A student may join any number of societies, or none. A society may have no members, or many." This matches the requirements.
The diagram¶
(Rollno) (Name) (Dateofbirth) (SID) (Cname) (TotalSeats)
underlined | | underlined | |
\ | | \ | /
+----------+----------+ +-----+-------+
| | |
+----+--------+ M /----------------\ N +-------------+---+
| STUDENT |=========< TAKES_ADMISSION >---| COURSE |
+----+--------+ \-------+--------/ +----+--------+---+
| total | | |
| (Dateofadmission) (Duration) (Coursetype)
| M (TeacherInCharge)
|
/----+---------\
< ENROLS_IN >----(Dateofenrolment)
\----+---------/
| N
+----+--------+
| SOCIETY |----(SocID underlined)----(Socname)----(Mentor)
+-------------+----(TotalSeats)
LEGEND: ==== total participation ; ---- partial participation
Figure 4.1 — ER diagram for the college database.
Shorthand form (useful in examinations when space is short)¶
STUDENT ( Rollno, Name, Dateofbirth ) [key: Rollno]
COURSE ( SID, Cname, TotalSeats, Duration,
Coursetype, TeacherInCharge ) [key: SID]
SOCIETY ( SocID, Socname, Mentor, TotalSeats ) [key: SocID]
TAKES_ADMISSION ( STUDENT (1,N) , COURSE (0,N) ) attribute: Dateofadmission
ENROLS_IN ( STUDENT (0,N) , SOCIETY (0,N) ) attribute: Dateofenrolment
This diagram maps directly to the tables used in the practical component; the mapping is done formally in Unit 5, Lesson 1.
8.4 Case Study 2 — The COMPANY Database (the textbook standard)¶
This is Elmasri and Navathe's canonical example, and it appears in examinations year after year because it exercises every notational feature.
Requirements¶
The company is organised into departments. Each department has a unique name, a unique number, and several locations. A department is managed by one employee; the date on which that employee began managing is recorded. Each department controls a number of projects; each project has a unique name, a unique number and a single location. The company stores each employee's social security number (unique), name, address, salary, sex and birth date. Every employee is assigned to exactly one department but may work on several projects, which need not be controlled by the same department; the number of hours per week an employee works on each project is recorded. Each employee has a direct supervisor, who is another employee. Employees have dependants for insurance purposes; for each dependant the first name, sex, birth date and relationship to the employee are recorded. A dependant's first name is unique only within that employee's family.
Analysis¶
| Element | Decision | Reasoning |
|---|---|---|
DEPARTMENT |
Strong entity type | Two keys: Dname, Dnumber — both underlined; choose Dnumber as primary. |
Dlocations |
Multivalued attribute of DEPARTMENT |
"several locations". Double ellipse. |
NumberOfEmployees |
Derived attribute | Countable from WORKS_FOR. Dashed ellipse. |
PROJECT |
Strong entity type | Keys Pname, Pnumber; Plocation is single-valued. |
EMPLOYEE |
Strong entity type | Key Ssn. Name is composite; Age derived from Bdate. |
DEPENDENT |
Weak entity type | "unique only within that employee's family" — no key of its own. Owner EMPLOYEE, partial key DependentName. |
MANAGES |
1:1, attribute StartDate |
One employee manages at most one department; a department has exactly one manager. |
WORKS_FOR |
1:N | Employee assigned to exactly one department; department has many employees. |
CONTROLS |
1:N | Department controls many projects; a project is controlled by one department. |
WORKS_ON |
M:N, attribute Hours |
Employee works on several projects; a project has several employees. Hours is determined only by the pair. |
SUPERVISION |
Recursive 1:N, roles supervisor / supervisee | The supervisor is another employee. |
DEPENDENTS_OF |
Identifying relationship, 1:N, double diamond | Weak entity's identification. |
The diagram¶
((Dlocations)) , -Number- .
(Dname) (Dnumber) | (OfEmployees) <- derived
\ | | ` - - - - '
\ | | |
+--+----+---------------+----------------+--+
| DEPARTMENT |
+--+------------+-------------------+-------+
| | |
1 | | 1 | 1
/-----+----\ /---+----\ /----+-----\
< MANAGES > < WORKS_FOR> < CONTROLS >
\-----+----/ \---+----/ \----+-----/
(StartDate) | N | N
| 1 | |
+--+------------+---+ +----+------+
| EMPLOYEE | | PROJECT |
+--+---+--------+---+ +----+------+
| | | \ | N
total | | | \ M /----+-----\
| | (Ssn) +-------< WORKS_ON >
| | underlined \----+-----/
| | (Name comp.) |
| | (Address) (Hours)
| | (Salary)(Sex)(Bdate)
| |
| +---- SUPERVISION (recursive 1:N)
| supervisor (0,1) / supervisee (0,N)
|
/====+=======\ 1
<< DEPENDENTS_OF >> double diamond = identifying
\=============/
|| N (total participation, double line)
+=====++======+
|| DEPENDENT || double rectangle = weak
+=============+
DependentName (partial key, dashed underline)
Sex, Bdate, Relationship
Figure 4.2 — ER diagram for the COMPANY database.
Structural constraints in (min, max) form¶
| Relationship | Left entity | (min,max) | Right entity | (min,max) |
|---|---|---|---|---|
MANAGES |
EMPLOYEE | (0,1) | DEPARTMENT | (1,1) |
WORKS_FOR |
EMPLOYEE | (1,1) | DEPARTMENT | (1,N) |
CONTROLS |
DEPARTMENT | (0,N) | PROJECT | (1,1) |
WORKS_ON |
EMPLOYEE | (1,N) | PROJECT | (1,N) |
SUPERVISION |
EMPLOYEE (supervisor) | (0,N) | EMPLOYEE (supervisee) | (0,1) |
DEPENDENTS_OF |
EMPLOYEE | (0,N) | DEPENDENT | (1,1) |
8.5 Case Study 3 — A Hospital Database (with a weak entity)¶
Requirements¶
A hospital has wards, each with a unique ward number, a name and a capacity. Each ward contains beds numbered 1 upwards within that ward. Patients have a unique registration number, name and date of birth. A patient occupies at most one bed at a time; a bed may be empty. Doctors have a unique employee code, name and one or more specialisations. A doctor treats many patients and a patient may be treated by several doctors; the date of first consultation is recorded for each doctor-patient pair.
Analysis and diagram¶
BED is weak: bed number 1 exists in every ward, so BedNo alone identifies nothing. Owner WARD, identifying relationship CONTAINS, partial key BedNo, full key (WardNo, BedNo).
Specialisation is multivalued on DOCTOR. TREATS is M:N with relationship attribute FirstConsultDate. OCCUPIES is 1:1 — a patient occupies at most one bed (0,1), and a bed holds at most one patient (0,1).
(WardNo) (Name) (Capacity)
underlined | |
\ | /
+-+----+-----+-+
| WARD |
+-------+-------+
|| 1 double diamond = identifying relationship
/=====++======\
<< CONTAINS >>
\=====++======/
|| N total participation
+=====++=====+
|| BED || weak entity type
+============+
BedNo (partial key, dashed underline)
| (0,1)
/-----+------\
< OCCUPIES > 1:1
\-----+------/
| (0,1)
+-------+--------+ ((Specialisation))
| PATIENT | |
+-------+--------+ +-------+--------+
RegNo (underlined) M | DOCTOR |
Name, Dateofbirth /---------------\ | EmpCode (key) |
| N < TREATS >-+ Name |
+----------->\---------+-----/ +----------------+
(FirstConsultDate)
Figure 4.3 — Hospital ER diagram with a weak entity type.
8.6 Design Traps¶
Two classic errors produce diagrams that look right and are wrong. Both arise from careless use of 1:N relationships.
The fan trap¶
Fan trap
Occurs when two or more 1:N relationships fan out from the same entity type, and the path between the outer entity types is therefore ambiguous.
BAD:
+----------+ 1 /--------\ N +----------+
| DIVISION |------< OPERATES>----| BRANCH |
+----+-----+ \--------/ +----------+
| 1
/----+----\
< HAS >
\----+----/
| N
+----+-----+
| STAFF |
Question: which branch does a member of staff work at?
UNANSWERABLE. Staff hangs off DIVISION, and a division has
many branches, so the path staff -> division -> branch fans
out to every branch of the division.
FIX: re-route the relationship so the path is unambiguous.
+----------+ 1 /--------\ N +--------+ 1 /-----\ N +-------+
| DIVISION |-----<OPERATES >----| BRANCH |---< HAS >---| STAFF |
+----------+ \--------/ +--------+ \-----/ +-------+
Now: staff -> branch -> division is a single unambiguous path.
The chasm trap¶
Chasm trap
Occurs when a path exists between entity types, but some instances cannot be connected because of a partial participation somewhere along the path.
+--------+ 1 /-----\ N +-------+ 1 /--------\ N +----------+
| BRANCH |---< HAS >---| STAFF |---< OVERSEES>---| PROPERTY |
+--------+ \-----/ +-------+ \--------/ +----------+
^
PARTIAL participation of PROPERTY:
a property may not yet be assigned
to any member of staff.
Question: which branch is property PG4 registered at?
UNANSWERABLE for unassigned properties -- the path is broken
at the chasm.
FIX: add the missing direct relationship.
+--------+ 1 /---------\ N +----------+
| BRANCH |----< ADVERTISES>---| PROPERTY |
+--------+ \---------/ +----------+
How to detect both traps: after drawing, take every pair of entity types that users ask questions about, and check that a single unambiguous, unbroken path connects them. This is Step 7 of the procedure.
8.7 A Glimpse of the EER Model¶
The basic ER model cannot express "a postgraduate student is a kind of student". The Enhanced (Extended) ER model adds three concepts.
EER concepts
Specialisation — defining a set of subclasses of an entity type (the superclass) on the basis of some distinguishing characteristic. STUDENT specialises into UG_STUDENT and PG_STUDENT.
Generalisation — the reverse process: noticing that CAR and TRUCK share attributes and creating the superclass VEHICLE.
Attribute inheritance — a subclass entity inherits all attributes of its superclass, and participates in all its relationships. PG_STUDENT inherits Rollno, Name, Dateofbirth and adds ThesisTitle, Supervisor.
Notation: a circle with a subset symbol on each line from the circle to the subclass.
+-------------+
| STUDENT | superclass
+------+------+
|
( d ) d = DISJOINT (o = overlapping)
/ | \
U | U U = subset symbol
/ | \
+--------+ +--+---+ +---------+
| UG | | PG | | DIPLOMA | subclasses
+--------+ +------+ +---------+
ThesisTitle on PG only; all three inherit Rollno,
Name, Dateofbirth from STUDENT.
Two constraints on a specialisation:
| Constraint | Values | Meaning | Notation |
|---|---|---|---|
| Disjointness | disjoint (d) / overlapping (o) | Can an entity belong to more than one subclass? | letter in the circle |
| Completeness | total / partial | Must every superclass entity belong to some subclass? | double / single line from superclass to circle |
A student is either UG or PG but not both, so the specialisation is disjoint; if every student must be one of the three, it is also total.
Scope note
The outline of this course lists the basic ER model. EER is included here so that you recognise the notation if it appears, and because specialisation is the correct answer to the "too many NULLs" design smell of Lesson 2. Examination questions will concentrate on the basic model.
8.8 Solved Questions¶
Solved Question 1
Q. Draw a complete ER diagram for the college database and list every business rule it asserts.
A. (Diagram: reproduce Figure 4.1.)
Business rules asserted:
- Every student is uniquely identified by
Rollno; every course bySID; every society bySocID. - A student may take admission in many courses, and a course may admit many students (M:N).
- Every student must be admitted to at least one course (double line on
STUDENT). - A course may exist with no students admitted (single line on
COURSE) — necessary, since a course must be created before anyone can join it. - For each (student, course) pair a single date of admission is recorded.
- A student may enrol in many societies and a society may have many members (M:N).
- Enrolment in a society is optional on both sides: a student need join no society, and a new society may have no members.
- For each (student, society) pair a single date of enrolment is recorded.
Coursetypeis restricted to Fulltime or Parttime — this is a domain constraint which the ER diagram cannot show; it must be recorded in the accompanying documentation and enforced later by aCHECKconstraint.
Point 9 illustrates an important limitation: the ER diagram captures structure and cardinality, but not arbitrary business rules.
Solved Question 2
Q. Draw the ER diagram for the COMPANY database, marking all constraints.
A. (Diagram: reproduce Figure 4.2; constraints as in the (min, max) table of Section 4.4.)
The features that make this the standard examination example:
- Two candidate keys on
DEPARTMENT(Dname,Dnumber) and onPROJECT— both underlined. - A multivalued attribute
Dlocations(double ellipse). - A derived attribute
NumberOfEmployees(dashed ellipse). - A composite attribute
Name(Fname, Minit, Lname)onEMPLOYEE. - A 1:1 relationship
MANAGEScarrying its own attributeStartDate, withDEPARTMENTtotal(1,1)andEMPLOYEEpartial(0,1). - Two 1:N relationships,
WORKS_FORandCONTROLS, each with total participation on the N side. - An M:N relationship
WORKS_ONwith the relationship attributeHours— which cannot be migrated to either entity type. - A recursive 1:N relationship
SUPERVISIONwith role names supervisor(0,N)and supervisee(0,1). - A weak entity type
DEPENDENT(double rectangle) with partial keyDependentName(dashed underline), identified through the double-diamond relationshipDEPENDENTS_OF, with total participation (double line) on the weak side.
Solved Question 3
Q. What is a fan trap? Give an example and its correction.
A. A fan trap occurs when two or more 1:N relationships radiate ("fan out") from the same entity type, so that the path between two of the outer entity types is ambiguous and a legitimate question cannot be answered.
Example. DIVISION 1:N BRANCH and DIVISION 1:N STAFF. Both BRANCH and STAFF hang off DIVISION. Now ask: "At which branch does staff member S05 work?" We can find S05's division, but that division operates several branches, and nothing in the model says which. The relationships fan out and the answer is indeterminate.
Correction. Restructure so that the path is unique, by re-routing the relationship through the intermediate entity type:
DIVISION --1-- <OPERATES> --N-- BRANCH --1-- <HAS> --N-- STAFF
Now each staff member belongs to exactly one branch, and each branch to exactly one division, so "which branch?" and "which division?" both have unique answers. No information is lost — the division of a staff member is still derivable, by following the path.
Detection. After drawing, list the questions users actually ask, and for each check that a single unambiguous path connects the entity types involved. A fan trap is the case where the path exists but is not unique; the chasm trap is the case where the path exists but is broken by a partial participation.
Solved Question 4
Q. Model this: "A library has books; each book has an ISBN, title and several authors. The library holds several physical copies of each book, numbered 1, 2, 3 within the title. Members borrow copies; the issue date and due date are recorded. A member may borrow many copies over time and a copy may be borrowed by many members over time."
A.
Entity types. BOOK (strong, key ISBN), COPY (weak — copy number restarts at 1 for each title, so owner BOOK, partial key CopyNo, full key (ISBN, CopyNo)), MEMBER (strong, key MemberID).
Attributes. BOOK: ISBN (key), Title, {Author} (multivalued — "several authors"). COPY: CopyNo (partial key), Status. MEMBER: MemberID (key), Name, Address.
Relationships. HAS_COPY — identifying, double diamond, BOOK 1 : COPY N, total on COPY. BORROWS — M:N over time, with relationship attributes IssueDate and DueDate.
(ISBN) (Title) ((Author))
underlined | |
\ | /
+-+-----+--------+-+
| BOOK |
+---------+---------+
|| 1
/======++=======\
<< HAS_COPY >>
\======++=======/
|| N total
+======++======+
|| COPY ||
+==============+
CopyNo (partial key, dashed)
| M
/------+-------\
< BORROWS >----(IssueDate)
\------+-------/ \----(DueDate)
| N
+-------+--------+
| MEMBER |
+----------------+
MemberID (key), Name, Address
Note on the M:N choice. BORROWS is M:N because we keep history: the same copy is borrowed by many members over time. If only the current loan were stored, it would be 1:N (a copy is on loan to at most one member) with (0,1) on the copy side.
Note on the key of BORROWS. Since a member may borrow the same copy more than once over the years, the pair (MemberID, ISBN, CopyNo) is not unique; IssueDate must be added to the key. This is a good illustration of why Step 7 — reading the diagram back — matters.
Solved Question 5
Q. Explain specialisation, generalisation and attribute inheritance. When would you specialise an entity type?
A. Specialisation is the process of defining a set of subclasses of an entity type, called the superclass, on the basis of some distinguishing characteristic. From EMPLOYEE one may specialise SECRETARY, ENGINEER and TECHNICIAN by job type. It is a top-down refinement.
Generalisation is the reverse, bottom-up process: recognising that CAR and TRUCK share VehicleID, Price and LicensePlate, and creating the superclass VEHICLE to hold the common attributes.
Attribute inheritance is the property that a subclass entity possesses all the attributes of its superclass in addition to its own, and participates in all the superclass's relationships. An ENGINEER entity has Ssn, Name and Salary inherited from EMPLOYEE, plus EngType of its own; and being an EMPLOYEE, it also participates in WORKS_FOR.
Two constraints apply: disjointness — disjoint (d) if an entity may belong to at most one subclass, overlapping (o) if it may belong to several — and completeness — total (double line) if every superclass entity must belong to some subclass, partial (single line) otherwise.
When to specialise. Three signals:
- Certain attributes apply to only some entities of the type — the "too many NULLs" smell. If
ThesisTitleis NULL for every undergraduate, specialise. - Some relationships apply to only some entities. If only postgraduates participate in
SUPERVISED_BY, specialise. - The subclasses are meaningful to the users and are named in the requirements.
If none of these holds, do not specialise: an unnecessary hierarchy adds joins and complexity for nothing.
Solved Question 6
Q. State four limitations of the ER model.
A.
-
It cannot express arbitrary business rules. "Coursetype must be Fulltime or Parttime", "a department head must be a professor of that department", "salary must not exceed the manager's salary" — none can be drawn. They must be documented separately and enforced later with
CHECKconstraints, assertions or triggers. -
It has no standard notation. Chen, Crow's Foot, UML and (min,max) all differ, and textbooks disagree on where to write cardinality labels. Diagrams must therefore carry a legend, and answers must state the convention used.
-
The basic model lacks abstraction mechanisms. It has no specialisation, generalisation, inheritance, categories or aggregation — which is precisely why the EER model was introduced.
-
Some constraints are inexpressible even in principle. Higher-degree constraints across several relationships, temporal constraints ("a student may not be admitted to two full-time courses simultaneously"), and cycles in recursive relationships cannot be captured.
Two further limitations worth mentioning: there is no operational component — the ER model describes structure but supplies no query language, so it must be mapped to the relational model before data can be manipulated; and large diagrams become unreadable, so practical designs need modularisation into subject-area views.
8.9 Exercises¶
A. Short answer
- Draw the symbol for each of: weak entity type, identifying relationship, derived attribute, multivalued attribute, total participation, partial key.
- State the seven steps of the design procedure.
- What is a fan trap? What is a chasm trap?
- Define specialisation and generalisation.
- What are the two constraints on a specialisation?
- Give two limitations of the ER model.
B. Long answer
- Explain the complete Chen notation with a diagram illustrating every symbol.
- Draw and explain the ER diagram for the COMPANY database, listing every constraint in (min, max) form.
- Explain fan and chasm traps with examples and corrections, and describe how to detect them systematically.
- Explain specialisation, generalisation, attribute inheritance, and the disjointness and completeness constraints, with a diagram.
C. Design problems — draw the complete ER diagram for each
-
Airline reservation. Flights have a unique number, origin, destination and departure time. A flight has legs numbered 1, 2, 3 within it. Aircraft have registration numbers and models. Passengers have a unique ID and name, and book seats on flights, with a booking date and seat number. One aircraft is assigned to each flight; an aircraft serves many flights.
-
Bank. Branches have a unique code, name and address. Customers have a unique ID, name and several phone numbers. Accounts have a unique number, type and balance, and belong to exactly one branch. A customer may hold several accounts and an account may be jointly held. Transactions on an account are numbered 1 upward within that account, and record a date, type and amount. Every account must have at least one holder.
-
University examination system. Students, courses, examinations, question papers, invigilators and rooms. Decide the entity types yourself, justify each decision, and mark every constraint. Identify at least one weak entity type.
-
Online shopping. Customers, products, orders, order lines, payments and suppliers. A product may be supplied by several suppliers at different prices. An order contains several products with quantities. State clearly which attributes belong to relationships.
D. Analytical
-
For the diagram of Figure 4.1, state what would change if: (a) a student were allowed at most one course; (b) every course had to have at least one student; (c) the college began storing each teacher's phone number and department.
-
A designer draws
DEPARTMENT 1 --- <HAS> --- N EMPLOYEEandDEPARTMENT 1 --- <OWNS> --- N PROJECT, and then asks "which project does employee E works on?". Name the trap, explain why it fails, and give two possible corrections with their different meanings. -
In the COMPANY database, argue whether
DEPENDENTcould be made a strong entity type. What would have to change, and what would be gained and lost? -
Convert the entire COMPANY diagram from Chen cardinality labels to (min, max) notation, and verify each pair by reading it aloud in English.
E. Objective — True or False with reason
- Every ER diagram maps to exactly one relational schema.
- A weak entity type may have a multivalued attribute.
- In a specialisation, subclasses inherit relationships as well as attributes.
- A fan trap is caused by an M:N relationship.
- The ER model can express the rule "salary must be positive".
- A double diamond always connects to a double rectangle.
Answers to Section E
- False. The mapping algorithm allows choices — for a 1:1 relationship, which side receives the foreign key; whether to merge a subclass into its superclass. Several correct relational schemas may result.
- True. Weakness concerns only identification; a weak entity type may have any kind of attribute.
- True. A subclass entity is a superclass entity, so it participates in all the superclass's relationships as well as inheriting its attributes.
- False. A fan trap arises from two or more 1:N relationships fanning out from the same entity type.
- False. Domain and value constraints cannot be drawn; they are documented separately and enforced by
CHECKconstraints after mapping. - True. A double diamond is an identifying relationship, which by definition identifies a weak entity type, drawn as a double rectangle.
Lesson Summary
- The Chen notation table is the vocabulary of Unit 2; reproduce it from memory.
- The seven-step procedure ends with reading the diagram back in English — the step that catches the errors.
- Three worked case studies: college (M:N with relationship attributes), COMPANY (every feature, including a weak entity, a recursive relationship and a 1:1 with an attribute), and hospital (weak entity plus multivalued attribute).
- Watch for the fan trap (ambiguous path from fanned-out 1:N relationships) and the chasm trap (path broken by partial participation).
- EER adds specialisation, generalisation and inheritance, with disjointness and completeness constraints.
- The ER model cannot express arbitrary business rules; document them separately.
End of Unit 2. Next: Unit 3, Lesson 1 — Update Anomalies and the Relational Data Model.