Skip to content

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

  1. Reproduce the complete Chen notation from memory.
  2. Draw a full ER diagram for a described mini-world, with all constraints.
  3. Apply a repeatable seven-step design procedure.
  4. Recognise and resolve the standard design traps (fan trap, chasm trap).
  5. 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

  1. Identify entity types. Underline nouns; keep those with properties of their own (Lesson 1).
  2. Identify relationship types. Verbs connecting two entity types.
  3. Attach attributes to entity types and to relationship types (Lesson 2 test).
  4. Determine keys. Underline. Any entity type with no key of its own is weak — find its owner, its identifying relationship, and its partial key.
  5. Determine cardinality ratios, using the two-question method.
  6. Determine participation constraints, side by side.
  7. 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:

  1. Every student is uniquely identified by Rollno; every course by SID; every society by SocID.
  2. A student may take admission in many courses, and a course may admit many students (M:N).
  3. Every student must be admitted to at least one course (double line on STUDENT).
  4. A course may exist with no students admitted (single line on COURSE) — necessary, since a course must be created before anyone can join it.
  5. For each (student, course) pair a single date of admission is recorded.
  6. A student may enrol in many societies and a society may have many members (M:N).
  7. Enrolment in a society is optional on both sides: a student need join no society, and a new society may have no members.
  8. For each (student, society) pair a single date of enrolment is recorded.
  9. Coursetype is 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 a CHECK constraint.

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 on PROJECT — both underlined.
  • A multivalued attribute Dlocations (double ellipse).
  • A derived attribute NumberOfEmployees (dashed ellipse).
  • A composite attribute Name(Fname, Minit, Lname) on EMPLOYEE.
  • A 1:1 relationship MANAGES carrying its own attribute StartDate, with DEPARTMENT total (1,1) and EMPLOYEE partial (0,1).
  • Two 1:N relationships, WORKS_FOR and CONTROLS, each with total participation on the N side.
  • An M:N relationship WORKS_ON with the relationship attribute Hours — which cannot be migrated to either entity type.
  • A recursive 1:N relationship SUPERVISION with role names supervisor (0,N) and supervisee (0,1).
  • A weak entity type DEPENDENT (double rectangle) with partial key DependentName (dashed underline), identified through the double-diamond relationship DEPENDENTS_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:

  1. Certain attributes apply to only some entities of the type — the "too many NULLs" smell. If ThesisTitle is NULL for every undergraduate, specialise.
  2. Some relationships apply to only some entities. If only postgraduates participate in SUPERVISED_BY, specialise.
  3. 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.

  1. 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 CHECK constraints, assertions or triggers.

  2. 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.

  3. The basic model lacks abstraction mechanisms. It has no specialisation, generalisation, inheritance, categories or aggregation — which is precisely why the EER model was introduced.

  4. 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

  1. Draw the symbol for each of: weak entity type, identifying relationship, derived attribute, multivalued attribute, total participation, partial key.
  2. State the seven steps of the design procedure.
  3. What is a fan trap? What is a chasm trap?
  4. Define specialisation and generalisation.
  5. What are the two constraints on a specialisation?
  6. Give two limitations of the ER model.

B. Long answer

  1. Explain the complete Chen notation with a diagram illustrating every symbol.
  2. Draw and explain the ER diagram for the COMPANY database, listing every constraint in (min, max) form.
  3. Explain fan and chasm traps with examples and corrections, and describe how to detect them systematically.
  4. Explain specialisation, generalisation, attribute inheritance, and the disjointness and completeness constraints, with a diagram.

C. Design problems — draw the complete ER diagram for each

  1. 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.

  2. 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.

  3. 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.

  4. 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

  1. 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.

  2. A designer draws DEPARTMENT 1 --- <HAS> --- N EMPLOYEE and DEPARTMENT 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.

  3. In the COMPANY database, argue whether DEPENDENT could be made a strong entity type. What would have to change, and what would be gained and lost?

  4. 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

  1. Every ER diagram maps to exactly one relational schema.
  2. A weak entity type may have a multivalued attribute.
  3. In a specialisation, subclasses inherit relationships as well as attributes.
  4. A fan trap is caused by an M:N relationship.
  5. The ER model can express the rule "salary must be positive".
  6. A double diamond always connects to a double rectangle.

Answers to Section E

  1. 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.
  2. True. Weakness concerns only identification; a weak entity type may have any kind of attribute.
  3. True. A subclass entity is a superclass entity, so it participates in all the superclass's relationships as well as inheriting its attributes.
  4. False. A fan trap arises from two or more 1:N relationships fanning out from the same entity type.
  5. False. Domain and value constraints cannot be drawn; they are documented separately and enforced by CHECK constraints after mapping.
  6. 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.