ER model cont. #

Structure constraint cont. #

The reason we have both \( M \) and \( N \) is because they are are both in the same association. It is okay to reuse \( M \) in other places in the diagram.

Note: The double line from employee to works_for indicates the total participation.

Weak entity #

Entity has

• key
• instance

In this diagram, we don’t necessarily know what key we should use for the dependent entity. We need to treat dependent as a weak entity, indicated by a double box, also note the total participation association.

We can use a partial key of the employee’s key plus the dependent’s name, indicated by a dashed line.

Attributes of relationship types #

Note: We could optionally have mgr_start_date belong to either entity, because it is a 1:1 relationship.

If one entity, say department, has total participation, it is better to have mgm_start_date belong to that entity. (If we had mgr_start_date belong to employee, then all except 1 employees would have a null attribute, thus it is better to have the department hold the attribute.)

We have 2 choices for the emp_start_date attribute, it can either belong to the employee or the works_for association.

Recursive relationships #

Non-binary relationship #

Non-binary relationships will not be on exams/assignments.

A bigger ER diagram example #

In class exercise #

Note: This exercise is not included in the week 1 assignment.

1. The university keeps track of each student’s name (first name, middle name, last name), student number, social security number, permanent address and phone, birthdate, sex, class (e.g. freshman, sophomore, …, graduate), and degree program (e.g. B.A., B.S., …, Ph.D.). Some user applications need to refer to the street, city, state, and zip of the student’s permanent address, and to the student’s last name. Both social security number and student number have unique values for each student.