7.1 Case Study: Diagram Conventions
Clockwise Convention
All relationship names appear horizontally, written from left to right. The full relationship name Entity-RelatesTo-Entity is read clockwise, around the center of the relationship line, as in the figure below.
Relationship cardinality
Relationship cardinality is depicted with crow's foot notation. Zero, one, and many are depicted as symbols at the ends of relationship lines:
Zero is depicted as a circle. One is depicted as a vertical bar. Many is depicted as three short lines. Ex: In the figure below:
Each owner owns at least zero and at most many vehicles. Each vehicle is owned by at least one and at most many owners. Each person has at least zero and at most one license. Each license belongs to exactly one person. Table diagrams
Tables are depicted with square corners. Columns and keys appear as follows:
R and U indicate NOT NULL and UNIQUE columns. Bullets indicate primary key columns. Multiple bullets in one table indicate a composite primary key. Since primary keys are always NOT NULL and UNIQUE, the bullet replaces the R and U symbols. Arrows depict foreign keys. The arrow starts at the foreign key column and points to the referenced table. RESTRICT, CASCADE, or SET NULL appears on the foreign key arrow. The rule applies to both update and delete of the referenced primary key.
Attribute cardinality
Attributes are presumed singular, optional, and not unique. When an attribute is plural, required, or unique, one or more letters follow the attribute name:
When a composite attribute is unique, the component attributes are grouped with a bracket prior to the U.
Ex: In the figure below:
OfficerID is singular, required, and unique. AwardCode is plural, optional, and not unique. BadgeNumber and DepartmentCode are singular, required, and not unique. The composite (BadgeNumber, DepartmentCode) is unique.
7.2 Case Study: Discovery
7.3 Case Study: Cardinality
Relationship cardinality
Following discovery, minimum and maximum cardinality is determined for all relationships in the DMV model.
Cardinality is determined from requirements captured during discovery. Cardinality is sometimes inferred from common business practices. When requirements are uncertain, cardinality must be confirmed with staff in follow-up interviews.
Cardinality appears below in crow's foot notation. The following relationships are noteworthy:
Owner-ResidesAt-Address and Operator-ResidesAt-Address. Follow-up interviews confirm that each owner has exactly one address, and each address has exactly one owner. When several owners reside at the same address, The DMV duplicates the address information. The same is true of operators. Owner-Owns-Vehicle. DMV policy allows each vehicle to have many owners. When a vehicle is junked, the vehicle record is retained with no owner. Accident-Involves-Vehicle and Accident-Involves-License. Follow-up interviews confirm that an accident may involve a vehicle with no plate or an operator with no license. Hence, the minimum of vehicle and license is zero. Citation-Cites-Vehicle and Citation-Cites-License. As with accidents, a citation may cite a vehicle with no plate or an operator with no license, so the minimum of vehicle and license is zero. Citation-Causes-Accident. Follow-up interviews confirm that, per DMV policy, a citation may cause several accidents, and an accident is caused by at most one citation. If cardinality cannot be confirmed, maximum many and minimum zero allow the greatest flexibility.
Attribute cardinality
Like relationship cardinality, attribute cardinality is determined from requirements captured during discovery. Cardinality is sometimes inferred from common business practices. When requirements are uncertain, cardinality must be confirmed with staff in follow-up interviews.
Attribute cardinality appears in the figure below.
Most attributes are followed by an R only, which indicates required, singular, and not unique.
Unique attributes, either simple or composite, may become primary keys in the logical design phase. Plural attributes become new tables.
Required attributes become NOT NULL columns.
7.4 Case study: Supertype and weak entities
An identifying attribute is unique, singular, and required. Strong entities have identifying attributes. Vehicle, Person, Event, License, Officer, and TrafficSection are strong.
Weak entities have identifying relationships rather than identifying attributes. Address and Exam are weak:
Address is identified via the Person-ResidesAt-Address relationship. Exam is identified via the Operator-Scores-Exam relationship. Subtype entities have an identifying relationship to the supertype entity.
The identifying relationship is implicit and not shown on the diagram. Owner and Operator are identified by PersonNumber. Accident and Citation are identified by EventNumber.
7.5 Case Study: Implementing Entities
Strong Entities
The first step of logical design is specification of primary keys.
Primary keys must be singular, required, and unique. Primary keys should also be stable, simple, and meaningless, however these characteristics are not necessary. If no suitable primary key is available, the database designer may introduce an artificial key. Strong entities become strong tables. Primary keys of strong tables are:
VehicleID, or VIN, has 17 characters and encodes information such as manufacturer and model year. Since the VIN is meaningful and complex, a database designer may introduce an artificial primary key. However, a VIN never changes and is commonly used to identify vehicles, so VehicleID is selected as the primary key of Vehicle. (LicenseID, StateCode) is meaningful and complex, but also stable and commonly used to identify drivers. So (LicenseID, StateCode) is selected as the primary key of License. (BadgeNumber, DepartmentCode) is meaningful, complex, and unstable, since an officer's department may change. So an artificial primary key OfficerNumber is created for Officer. SectionNumber is stable and simple, but meaningful. Since section numbers rarely change, SectionNumber is selected as the primary key of TrafficSection. Person and Event are new supertype entities. No unique attributes were discovered for these entities, so identifying attributes PersonNumber and EventNumber were created in the analysis phase. These identifying attributes become artificial primary keys. The table below summarizes the characteristics of these primary keys.
Subtype Entities
Subtype entities become subtype tables. The subtype primary key is the same as the supertype primary key:
PersonNumber is the primary key of Owner and Operator. EventNumber is the primary key of Accident and Citation. PersonNumber and EventNumber are also foreign keys, referencing Person and Event, with cascade on primary key update and delete.
Weak Entities
Weak Entities become weak tables. The primary key of a weak table includes the primary key of the identifying table:
A person can take the exam many times, but only once on each day, so the Exam primary key is (PersonNumber, ExamDate). A person has at most one address in the DMV system, so the Address primary key is PersonNumber. PersonNumber is also a foreign key in both tables.
The foreign keys reference Person and Operator with cascade on primary key update and delete.
Table Design
When a foreign key is part of a primary key, which foreign key rule cannot be specified?
Foreign keys that are part of a primary key cannot be NULL, since all primary key columns must be non-NULL. So SET NULL cannot be specified for foreign keys that are part of a primary key. For a given foreign key, the update and delete rules must
The identifying relationship of a weak entity may be one-one maximum.
7.6 Case Study: Implementing Relationships
Many-One Relationships
The first step of logical design was to identify any identifying relationships as foreign keys. The next step implements many-one-relationships:
Foreign key cardinality and rules depend on the minimum cardinality of the opposite side of the relationship:
If the minimum is one, the foreign key column is required and rules are either restrict or cascade. If the minimum is zero, the foreign key column is optional and foreign key rules are set null. Ex: Each event is reported by an officer, so ReportOfficerNumber is required and rules are restrict. Some events are not caused by another event, so CauseEventNumber is optional and rules are set null.
One-One Relationships
One-to-One relationships are foreign keys in a table with fewer rows.
Key Points
Each Operator has exactly one License, and each License belongs to one Operator. This is enforced by using a foreign key. Implemented using a foreign key with a unique constraint. The foreign key OperatorLicenseID is placed in the Operator table. This choice simplifies queries that involve an operator's details, as the license number is directly available in the Operator table. The diagram indicates a restrict rule for deletions. This ensures that if an operator record or a license record is referenced, it cannot be deleted without first removing the associated record. Why Use One-to-One Relationships?
This design keeps related data separate, avoiding redundancy while maintaining integrity. Personal details of an operator and their license details are stored separately but linked via the foreign key.
Many-Many Relationships
A many-to-many (M) relationship occurs when multiple records in one table are associated with multiple records in another table. This type of relationship cannot be directly implemented in relational databases without the use of an intermediary table.
Each record in Table A can relate to multiple records in Table B. Each record in Table B can relate to multiple records in Table A. Example Scenario
A student can enroll in multiple courses. A course can have multiple students. Implementation of Many-to-Many Relationship
To implement this relationship, a junction table (also known as a bridge table or associative entity) is used.
Tables and Junction Table
Enrollment Table (Junction Table): StudentID (Foreign Key referencing Student table) CourseID (Foreign Key referencing Course table) Other attributes like EnrollmentDate, Grade (optional) ER Diagram
Student ←→ Enrollment ←→ Course Junction Table Purpose
The Enrollment table connects Student and Course tables. Ensures the many-to-many relationship is stored and allows for additional attributes specific to the relationship, like EnrollmentDate or Grade.
Reflexive Relationships
A reflexive relationship occurs when an entity is related to itself. In other words, records in a single table are associated with other records in the same table.
This is common when an entity has a hierarchical or self-referential structure. Same table references itself. Often used to model hierarchies or recursive relationships. Employee-Manager relationship: An employee can have a manager who is also an employee. A folder can contain subfolders, which in turn can contain more subfolders. Employee(EmployeeID, Name, ManagerID) ManagerID: Foreign Key referencing EmployeeID in the same table. Non-Reflexive Relationships
A non-reflexive relationship occurs when an entity in one table is related to an entity in a different table. This is the standard type of relationship in most database designs.
Key Characteristics
Different entities are involved. No self-referencing — each table represents a distinct entity type. A customer places orders. A student enrolls in courses. When to Use Which?
Unit 7: Case Studies
