Drawing and Reading ER Diagrams

Concept

An ER diagram is the picture of a design, drawn before any table exists so that the people who asked for the system can check it. Everything in this phase so far now appears in one notation.

The complete notation

An eight step method

1. Read the requirement and underline every noun. Nouns are candidate entities and attributes.
2. Underline every verb. Verbs are candidate relationships.
3. Split the nouns into entities and attributes using the four tests from the entities note.
4. Choose a key for each entity type, and mark weak entities where no key exists.
5. Add relationships between entity types, named with verbs.
6. Decide cardinality for each relationship by asking the question in both directions.
7. Decide participation by asking whether every entity must take part.
8. Attach attributes, placing any that depend on the combination on the relationship itself.

Step 6 has a fixed script. Ask: can one A relate to many B? and then can one B relate to many A? Two yes answers means many to many. One yes means one to many, and the many side is the one that answered yes.

Worked example

Requirement. A college has departments, each with a code, a name and exactly one head of department. Every student belongs to exactly one department and has a roll number, a name, a date of birth and possibly several phone numbers. Courses have a code, a title and credits, and are offered by a department. Students enrol in courses and receive marks for each. Some students take a course more than once, in different semesters.

STEP 1-2  nouns: college, department, code, name, head,
                 student, roll number, date of birth, phone,
                 course, title, credits, marks, semester
          verbs: belongs to, offers, enrols, heads

STEP 3    entities:   DEPARTMENT, STUDENT, COURSE
          attributes: everything else
          college is the whole mini world, not an entity

STEP 4    keys: dept_code, roll_no, course_code

STEP 5    relationships:
            DEPARTMENT ---- HAS      ---- STUDENT
            DEPARTMENT ---- OFFERS   ---- COURSE
            STUDENT    ---- ENROLS   ---- COURSE

STEP 6    can one department have many students? yes
          can one student belong to many departments? no
             -> 1:N, many side is STUDENT

          can one student enrol in many courses? yes
          can one course have many students? yes
             -> M:N

STEP 7    every student must belong to a department -> TOTAL
          a department may have no students yet    -> partial
          a student may have enrolled in nothing   -> partial

STEP 8    marks and semester depend on the PAIR
             -> attributes of ENROLS
          phone is multivalued -> double ellipse

THE DIAGRAM

   (dept_code)  (dept_name)          (roll_no) (dob)
                  /                      |     /
      +--------------+              +-----------+   ((phone))
      |  DEPARTMENT  |====< HAS >---|  STUDENT  |---/
      +--------------+  1        N  +-----------+
             |                            |
             | 1                          | M
        < OFFERS >                    < ENROLS >--( marks )
             |                            |      \_( semester )
             | N                          | N
      +--------------+                    |
      |    COURSE    |--------------------+
      +--------------+
       (course_code) (title) (credits)

The last requirement sentence

Some students take a course more than once, in different semesters. That means the pair roll_no and course_code is not unique on its own, so semester must be part of the key of the enrolment. This is the signal described earlier: the relationship is behaving like an entity, and its key is ( roll_no, course_code, semester ). Missing this sentence produces a design that silently loses the second attempt.

Common mistakes

• Modelling the whole system as an entity. The college is the mini world, not an entity type.
• Attaching relationship attributes to an entity. Marks on STUDENT is wrong.
• Guessing cardinality. Ask the question in both directions, every time.
• Leaving participation unmarked. The diagram is then incomplete, and the NOT NULL decisions cannot be made.
• Overcrowding one diagram. Split a large model into subject areas rather than producing something unreadable.
• Naming relationships with nouns. Use verbs so the diagram reads as sentences.

Exam and interview questions

1. List every ER symbol and what it represents.
2. Describe a method for producing an ER diagram from a written requirement.
3. How do you determine the cardinality of a relationship? State the exact questions.
4. Where do you attach an attribute that depends on two entities?
5. What does it tell you when a many to many relationship needs a third attribute in its key?

Practice

1. Draw an ER diagram for a bus service with routes, buses, drivers and trips.
2. Take the college example and add a rule that a course may have prerequisite courses. Show the relationship and its role names.
3. Write the eight steps from memory and apply them to a gym membership system.

Conclusion

Nouns become entities and attributes, verbs become relationships, cardinality is settled by asking the question both ways, and participation decides what is compulsory. Follow the eight steps in order and the diagram will be complete rather than merely plausible.