ER Example


To illustrate the use of the iterative process that ultimately yields a workable ERD, we'll start with an initial interview with the Tiny College administrators.

The interview process yields the following business rules:



  1. Tiny College (TC) is divided into several schools: a school of business, a school of arts and sciences, a school of education, and a school of applied sciences.
    • Each school is administered by a dean who is a professor.
    • Each professor can be the dean of only one school, and a professor is not required to be the dean of any school.
      • Therefore, a 1:1 relationship exists between PROFESSOR and SCHOOL.
      • Note that the cardinality can be expressed by writing (1,1) next to the entity PROFESSOR and (0,1) next to the entity SCHOOL.


  1. Each school comprises several departments.
    • For example, the school of business has an accounting department, a management/marketing department, an economics/finance department, and an informatics department.
    • Note again the cardinality rules:
      • The smallest number of departments operated by a school is one, and the largest number of departments is indeterminate (N).
      • On the other hand, each department belongs to only a single school; thus, the cardinality is expressed by (1,1).
        • That is, the minimum number of schools that a department belongs to is one, as is the maximum number.
    • The figure below illustrates these first two business rules.

      Example figure 1.
    • It is again appropriate to evaluate the reason for maintaining the 1:1 relationship between PROFESSOR and SCHOOL in the "PROFESSOR is dean of SCHOOL" relationship.
      • It is worth repeating that the existence of 1:1 relationships often indicates a misidentification of attributes as entities.
      • In this case, the 1:1 relationship could easily be eliminated by storing the dean's attributes in the SCHOOL entity.
        • This solution would also make it easier to answer the queries, "Who is the dean?" and "What are that dean's credentials?"
        • The downside of this solution is that it requires the duplication of data that are already stored in the PROFESSOR table, thus setting the stage for anomalies.
        • However, because each school is run by a single dean, the problem of data duplication is rather minor.
      • The selection of one approach over another often depends on information requirements, transaction speed, and the database designer's professional judgment.
      • In short, do not use 1:1 relationships lightly, and make sure that each 1:1 relationship within the database design is defensible.


  1. Each department may offer courses.
    • For example, the Informatics department offers courses such as Introduction to Informatics and Analytics, Data Mining and Predictive Analytics, and Data Visualization.
    • The ERD segment for this condition is shown in the following figure.

      Example figure 2.
    • Note that this relationship is based on the way Tiny College operates. If, for example, Tiny College had some departments that were classified as "research only," those departments would not offer courses; therefore, the COURSE entity would be optional to the DEPARTMENT entity.


  1. The relationship between COURSE and CLASS was illustrated in the preceding figure.
    • Nevertheless, it is worth repeating that a CLASS is a section of a COURSE.
      • That is, a department may offer several sections (classes) of the same database course.
    • Each of those classes is taught by a professor at a given time in a given place.
    • In short, a 1:M relationship exists between COURSE and CLASS.
    • However, because a course may exist in Tiny College's course catalog even when it is not offered as a class in a current class schedule, CLASS is optional to COURSE.
    • Therefore, the relationship between COURSE and CLASS looks like the figure below.

      Example figure 3.


  1. Each department should have one or more professors assigned to it.
    • One and only one of those professors chairs the department, and no professor is required to accept the chair position.
      • Therefore, DEPARTMENT is optional to PROFESSOR in the "chairs" relationship.
    • Those relationships are summarized in the ER segment below.

      Example figure 4.


  1. Each professor may teach up to four classes; each class is a section of a course.
    • A professor may also be on a research contract and teach no classes at all.
    • The ERD segment in the next figure depicts those conditions.

      Example figure 5.


  1. A student may enroll in several classes but can take each class only once during any given enrollment period.
    • For example, during the current enrollment period, a student may decide to take five classes – Statistics, Calculus, English, Database, and History – but that student would not be enrolled in the same Statistics class five times during the enrollment period!
    • Each student may enroll in up to six classes, and each class may have up to 35 students, thus creating an M:N relationship between STUDENT and CLASS.
    • Because a CLASS can initially exist (at the start of the enrollment period) even though no students have enrolled in it, STUDENT is optional to CLASS in the M:N relationship.
    • This M:N relationship must be divided into two 1:M relationships through the use of the ENROLL entity.
      • If a class exists but has no students enrolled in it, that class doesn't occur in the ENROLL table.
        • Hence, the optional symbol is shown next to ENROLL.
      • Note that the ENROLL entity is weak: it is existence-dependent, and its (composite) PK is composed of the PKs of the STUDENT and CLASS entities.
      • The cardinalities (0,6) and (0,35) can be added next to the ENROLL entity to reflect the business rule constraints.
      • The following ERD segment depicts these conditions.

        Example figure 6.


  1. Each department has several (or many) students whose major is offered by that department.
    • Each student has only a single major and is, therefore, associated with a single department.
    • However, in the Tiny College environment, it is possible for an undecided student not to declare a major field of study.
      • Such a student would not be associated with a department; therefore, DEPARTMENT is optional to STUDENT.
    • The relationship is summarized in the ER segment below.

      Example figure 7.
    • It is worth repeating that the relationships between entities and the entities themselves reflect the organization's operating environment. That is, the business rules define the ERD components.


  1. Each student has an advisor in his or her department; each advisor counsels several students.
    • An advisor is also a professor, but not all professors advise students.
      • Therefore, STUDENT is optional to PROFESSOR in the "PROFESSOR advises STUDENT" relationship.
    • The relationship is summarized in the ER segment below.

      Example figure 8.


  1. The CLASS entity contains a ROOM_CODE attribute.
    • Given the naming conventions, it is clear that ROOM_CODE is an FK to another entity.
    • Because a class is taught in a room, it is reasonable to assume that the ROOM_CODE in CLASS is the FK to an entity named ROOM.
    • In turn, each room is located in a building.
    • A BUILDING can contain many ROOMs, but each ROOM is found in a single BUILDING.
    • In this ERD segment, it is clear that some buildings do not contain (class) rooms. For example, a storage building might not contain any named rooms at all.

      Example figure 9.


Using the preceding summary, you can identify the following entities:

Once you have discovered the relevant entities, you can define the initial set of relationships among them.

Next, you describe the entity attributes. Identifying the attributes of the entities helps you to better understand the relationships among entities. The following table summarizes the ERM's components, and names the entities and their relations.

Example figure 10.


You must also define the connectivity and cardinality for the just-discovered relations based on the business rules.

The following figure shows the Crow's Foot ERD for Tiny College.

Example figure 11.


The next figure shows the conceptual UML class diagram for Tiny College. Note that this class diagram depicts the M:N relationship between STUDENT and CLASS.

Example figure 12.


The final figure shows the implementation-ready UML class diagram for Tiny College (note that the ENROLL composite entity is shown in this class diagram.

Example figure 13.