Entity-relationship (ER) model

Database design starts on paper, before code. The entity-relationship model (ER) is the standard way to picture the real world as “what things exist and how they are connected”. The midterm has two kinds of questions: drawing an ER diagram from a scenario, or reading cardinality, participation and keys from a given diagram. In this page we build the university database we use in class (student, instructor, course, department, takes, teaches) as an ER model.

Golden rules:

1. Entity = noun, relationship = verb. “A student takes a course”: student and course are entities, takes is the relationship.
2. Ask the attribute type: can it be divided (composite), can it have several values (multivalued), can it be computed (derived)?
3. Cardinality and participation are different things. Cardinality is “at most how many”, participation is “is at least one required”.
4. An entity with no key is weak and is connected to its owner with total participation.
5. Sample data does not prove a rule. The business rule decides the cardinality.

Entity and entity set

An entity is a single object that can be told apart from others: the student Ada, the course CS305, the CS department. The collection of entities that share the same properties is an entity set: all students form the student set. In programming terms, an entity set is a class and an entity is an object.

Each entity is described by its attributes. The set of values an attribute may take is called its domain: for example whole numbers from 0 to 240 for tot_cred. The attribute(s) that distinguish entities from each other form the key; for student it is stu_id.

Attribute types

A derived attribute is not stored, it is computed when needed. A student born on 2005-11-02 is 20 years old on 2026-10-01: the year difference is 21, but the birthday has not come yet. If you stored age, you would have to update it every year; date_of_birth never changes. That is why the base data is stored and the derived value is computed.

The text notation we use in this course (close to Silberschatz’s boxed notation) looks like this:

+------------------------+
| student                |
+------------------------+
| _stu_id_               |     _x_   : primary key (underlined)
| name                   |     indented : parts of a composite attribute
|    first_name          |
|    last_name           |
| date_of_birth          |
| { phone }              |     { }   : multivalued
| age ( )                |     ( )   : derived
+------------------------+

PITFALL · multivalued and composite get mixed up. name consists of two parts, but every student has one name: composite. phone has one part, but it may have several values: multivalued. If the question says “more than one”, it is multivalued; if it says “made of parts”, it is composite.

Relationships, degree and roles

A relationship is a link between entities: “Ada takes CS305”. A set of relationships of the same kind is a relationship set: takes. The number of entity sets that take part in the relationship is the degree. In the university model most relationships are binary:

student ---- < takes > ---- course          (binary, attribute: grade)
instructor ---- < teaches > ---- course     (binary)
instructor ---- < advisor > ---- student    (binary)
instructor ---- < inst_dept > ---- department

An entity set can take part in the same relationship twice. For example, the prereq relationship connects course to course: the prerequisite of CS201 is CS101. Here the role names say which side is which: the course_id role and the prereq_id role. Such relationships are called recursive relationships.

A relationship can also have attributes. The grade on takes belongs neither to the student nor to the course, but to the pair of the two. That is why the attribute of an M:N relationship stays on the relationship.

Ternary relationships

If an instructor guides a student on a specific project, you need the relationship proj_guide(instructor, student, project) that connects three entities. Splitting it into three binary relationships looks tempting, but it loses information:

proj_guide triples:   (I1,S1,P1) (I1,S2,P2) (I2,S1,P2)
binary parts:         IS={I1S1, I1S2, I2S1}  SP={S1P1, S2P2, S1P2}  IP={I1P1, I1P2, I2P2}
join back:            (I1,S1,P1) (I1,S1,P2) (I1,S2,P2) (I2,S1,P2)
spurious triple:      (I1,S1,P2)   I1 and S1 were not together on P2

Mapping cardinality

Cardinality says with at most how many entities an entity can be matched through the relationship.

In the sample data S1 takes four courses and four students take CS101: takes is “many” in both directions, so M:N. For teaches, the sample has at most two courses per instructor and at most one instructor per course. This data is consistent with 1:N. But if a course having two instructors is possible in a real university, the design must be M:N.

PITFALL · reading cardinality off the data. A sample table cannot prove a constraint, it can only refute it. You cannot say “it is 1:N” just because every course in the table has a single instructor. If the exam asks “is it consistent with this data”, look at the data; if it asks “what is the cardinality”, look at the rule in the scenario.

In Silberschatz notation the arrow (-->) shows the “at most one” side and a plain line shows “many”. Other books use the letters 1 and N or crow’s foot. In the exam, check which notation the diagram uses.

Participation and min..max notation

Participation is the “at least one” question.

• Total participation: every entity in the set takes part in at least one relationship. Every instructor must have a department: instructor participates totally in inst_dept. It is drawn with a double line.
• Partial participation: some entities may be in no relationship. In the sample data S6 (Firat) has taken no course, nobody teaches BI100, and I6 (Yildiz) teaches no course. So the participation of student in takes, of course in teaches, and of instructor in teaches is partial.

The way to combine cardinality and participation in one notation is min..max labels. An l..h written on the line says the entity can appear in the relationship at least l and at most h times.

instructor --1..1-- < inst_dept > --0..*-- department
   every instructor in exactly one department   a department has zero or many instructors

student --0..*-- < takes > --0..*-- course

min = 1 means total participation, min = 0 means partial participation. max = 1 means “at most one”, * means unbounded.

Weak entity sets

Think of the sections of a course: sections 1 and 2 of CS101 in Fall 2026, and section 1 of MA101 in Fall 2026. sec_id = 1 alone does not identify a section, because many courses have a section number 1. A section has no key that is enough on its own: it is a weak entity set.

• The strong entity that identifies the weak entity is the owner (identifying entity set): course.
• The connecting relationship is the identifying relationship: sec_course. It is drawn with a double diamond.
• The attributes that tell weak entities apart within one owner are the discriminator (partial key): {sec_id, semester, year}. It is shown with a dashed underline.
• The primary key of the weak entity = the PK of the owner + the discriminator = {course_id, sec_id, semester, year}.

+===============+                   +-----------+
|| section     ||====<< sec_course >>----| course    |
|| - sec_id -  ||                   | _course_id_|
|| - semester -||                   +-----------+
|| - year -    ||
+===============+
  double box: weak entity   double diamond: identifying relationship   double line: total participation

A weak entity always participates totally in its relationship with the owner, and the relationship is “one” on the owner side: every section belongs to exactly one course. In the verification run we saw that when a course is deleted, its sections are deleted too (ON DELETE CASCADE). A section cannot live without its owner.

TIP · when should it be a weak entity? If the meaning of the entity depends entirely on another entity (an order line, a room inside a building, a section of a course) and it has no natural key of its own, make it weak. If you give it its own identification number (like section_id), it becomes a strong entity.

Specialization, generalization, aggregation

Subgroups of some entity sets carry their own attributes. person is the general set, and student (tot_cred) and instructor (salary) are subsets. Thinking from the top down is specialization; going up from the subsets to the common superset is generalization. In the diagram it is shown with an ISA triangle; the subset inherits all attributes of the superset (attribute inheritance).

There are two independent constraints:

Aggregation is treating a relationship itself as if it were an entity and connecting it to another relationship. For example, you put the proj_guide relationship inside a box and connect it to the evaluation entity with the relationship eval_for: “the evaluation of this instructor-student-project triple”. Without the box you would need a quaternary relationship, and there would be needless repetition.

Design decisions

• Entity or attribute? If you keep the phone only as a number, it is an attribute. If the phone will have its own information such as type and location, make it an entity.
• Entity or relationship? If an enrollment carries its own information (date, fee) and will be connected to other things, it can be an entity. If it is only “who took what”, a relationship is enough.
• Redundancy: Do not keep both a dept_name attribute on instructor and an inst_dept relationship. The information is already in the relationship, and the two could contradict each other.
• Ternary or binary? If the three pieces of information really belong together (which instructor guides which student on which project), use a ternary. The spurious triple example above is the reason.

Common mistakes