7. ENTITY RELATIONSHIP MODELLING
7.3.7: Partial Key
Sometimes we have attributes that distinguish entities of an entity type from other entities of the same type, but only relative to some other related entity. This situation arises naturally when we model things like invoices and invoice lines. If invoice lines are assigned line numbers (1, 2, 3, etc.), these line numbers distinguish lines on a single invoice from other lines of the same invoice. However, for any given line number value, there could be many invoice lines (from separate invoices) with that same line number.
A partial key (also called a discriminator) is an attribute that distinguishes instances of a weak entity type relative to a strong entity. Invoice line number is a partial key for invoice lines; each line on one invoice will have different line numbers. Using the Peter Chen notation, the discriminator attribute is underlined with a dashed line:
Later when relationships are covered it will be clearer that attributes for relationships can be discriminators too. Consider that a library has books that members will borrow. Any book could be borrowed many times and even by the same member. However, when a member borrows the same book more than once the date/time will distinguish those events. Consider the following ERD for this case:
Exercises
1) Consider an educational institution that has departments and where each department offers courses. Suppose departments are assigned unique identifiers and so deptCode is a key for the department entity type. Courses are identified within a department by a course number; course numbers are unique within a department but not across departments. So, History may have a course numbered 215, and English could have a course numbered 215 too. In order to identify a particular course, we need to know the department and we need to know the course number. Illustrate an ERD including department and course entity types. Include attributes for Department (department code and department name), and for Course (course number, title, and description).
2) Consider a company that owns and operates parking lots. Develop an ERD with two entity types Parking Lot and Space and where:
- The address of a parking lot serves to identify the lot.
- Each space within a lot is rented at the same monthly rental charge.
- Each parking space is known by its number within the lot (within a lot these always start at 1).
- Each parking space is rented out to at most one vehicle. The vehicle’s identifier must be recorded. The identifier comprises a province code and license plate number.
