ER Design Project: College Database

Requirement

A college is organised into departments. Each department has a code, a name, a building and exactly one head of department, who must be one of the faculty members. A department may exist before any student joins it.

Every student belongs to exactly one department and has a roll number, a name, a date of birth, one email address and possibly several phone numbers. The office also needs the student age, but it must always be correct.

Faculty members have a staff number, a name and a designation, and each belongs to exactly one department. A faculty member may teach several courses and a course may be taught by several faculty members in different semesters.

Courses have a code, a title and credits, and each is offered by exactly one department. A course may require other courses as prerequisites.

Students enrol in courses. For each enrolment the college records the semester, the attendance percentage and the marks. A student may repeat a course in a later semester.

Step 1 — Entities and attributes

Two attributes need care. age is derived, because the requirement says it must always be correct — so it is computed from dob and never stored. phone is multivalued, so it will become its own table.

Step 2 — Relationships, cardinality and participation

Step 3 — The ER diagram

  (dept_code) (dept_name) (building)      (staff_no) (designation)
                |        /                     |     /
       +-----------------+   N            1  +---------+
       |   DEPARTMENT    |----< WORKS_IN >----| FACULTY |
       +--------+--------+                    +----+----+
          1  |     |  1                            | M
             |     +====< HEADS >=========1========+
             |          (department TOTAL)         |
        < OFFERS >                            < TEACHES >--( semester )
             | N                                   | N    \_( section )
       +-----v-----+                               |
       |  COURSE   |<------------------------------+
       +-----+-----+
        |    |  (course_code)(title)(credits)
        |    +----< PREREQUISITE >----+
        |        roles: requires /    |
        |               required_by ---+
        | N
   < ENROLS >--( semester )( attendance )( marks )
        | M
  +-----v------+
  |  STUDENT   |==== BELONGS_TO ====> DEPARTMENT  (student TOTAL)
  +------------+
   (roll_no)(dob)(email)((phone))(name{first,last})(age dashed)

Step 4 — The relational schema

  departments ( dept_code      PK
              , dept_name      NOT NULL
              , building
              , head_staff_no  NOT NULL UNIQUE -> faculty )

  faculty     ( staff_no       PK
              , faculty_name   NOT NULL
              , designation
              , dept_code      NOT NULL -> departments )

  students    ( roll_no        PK
              , first_name     NOT NULL
              , last_name
              , dob            NOT NULL
              , email          UNIQUE
              , dept_code      NOT NULL -> departments )
                -- age is NOT a column; it is computed

  student_phones ( roll_no  -> students ON DELETE CASCADE
                 , phone
                 , PK ( roll_no, phone ) )

  courses     ( course_code    PK
              , title          NOT NULL
              , credits        NOT NULL
              , dept_code      NOT NULL -> departments )

  teaches     ( staff_no       -> faculty
              , course_code    -> courses
              , semester
              , section
              , PK ( staff_no, course_code, semester, section ) )

  enrolments  ( roll_no        -> students
              , course_code    -> courses
              , semester
              , attendance
              , marks
              , PK ( roll_no, course_code, semester ) )

  prerequisites ( course_code      -> courses
                , requires_code    -> courses
                , PK ( course_code, requires_code ) )

  8 tables.
  Created by: 4 entities, 1 multivalued attribute,
              3 many to many relationships.
  The three 1:N and 1:1 relationships created NO tables.

Step 5 — Why each decision was made

Verification questions

A design is only finished when it can answer the questions the requirement implies. Check each one can be answered from the schema above.

1. Which students are in the Computing department?
2. What are the phone numbers of one student?
3. Which courses does one faculty member teach this semester?
4. Which students failed a course, and did they repeat it later?
5. What must a student have passed before enrolling in a given course?
6. Who heads each department, and which department do they work in?

A deliberate trap

A faculty member heads a department and also works in one. Nothing above forces them to be the same department, and the requirement does not say they must be. If the college does require it, that is a rule the schema alone cannot express — it needs an explicit check or a trigger. Noticing that a requirement is silent, and saying so, is exactly what design review is for.

Common mistakes

• Storing dept_name on students instead of a foreign key, which repeats the name on every row.
• Making enrolment key just roll_no and course_code, silently losing the repeat attempt.
• Modelling phone as three columns.
• Placing the head foreign key on faculty and leaving it null everywhere.
• Forgetting the prerequisite relationship is recursive and needs role names.

Practice

1. Add a rule that each course runs in a fixed set of rooms at fixed times. Which entity and which relationships change?
2. Add hostel allocation, where a student may hold at most one room and a room holds up to three students. Give the cardinality and the resulting table.
3. Write the constraint needed to force a head of department to work in the department they head.

Conclusion

Eight tables, produced by applying the seven conversion rules to a design built from the eight step method. The valuable habits here are checking participation before writing NOT NULL, and testing the finished schema against the questions the requirement implies.