DBMS Assign 3

Transforming ERDs into Relations

1

This week’s presentation covered the basic components of the ERD: entities, attributes, relationships, and cardinality. This document builds on the presentation by using the components to create simple ERDs. It then shows how those ERDs are transformed into relations. The document provides examples for the following relationship types:

• unary one-to-many

• unary many-to-many

• binary one-to-one

• binary one-to-many

• binary many-to-many

• ternary

Transforming ERDs into Relations

2

Unary One-to-Many In a unary, or recursive, relationship the entity is related to itself. As an example, let’s model the relationship between managers and employees. A manager manages employees while an employee is managed by a manager. Since a manager is really just a type of employee, the relationship between managers and employees can be modeled recursively using a single employee entity.

If you view the relationship from the right side to the top, it reads: An employee manages zero or many employees. We use zero for the minimum cardinality because an employee may not be a manager and, as such, would manage no employees. We use many for the maximum cardinality because, if an employee is a manager, he/she could manage many employees. Viewing the relationship from the top to the right, it reads: An employee is managed by zero or one employee. We use zero for the minimum cardinality because an employee might not have a manager (e.g., the CEO is an employee who does not have a manager). We use one for the maximum cardinality because an employee would, hopefully, have only one manager. To tranform this ERD, you would begin by creating an Employee relation with columns based on the attributes in the Employee entity. The primary key of the relation would be EmployeeID. To represent the one-to-many recursive relationship, a recursive foreign key (ManagerID) would be added to the relationship. This foreign key would store the ID of the employee’s manager – which is just another employee ID (thus the key is recursive). Employee (EmployeeID, FirstName, LastName, HireDate, ManagerID)

Transforming ERDs into Relations

3

Unary Many-to-Many In the following example, suppose that you wanted to model the parts in invenotry. In this case, however, parts are sometimes used to make other parts (e.g., part 100 is used to make parts 101 and 102). Specifically, a part can be used to make zero, or many, other parts while a part is composed of zero, or many, other parts. Since parts are on both ends of the relationship, it can be modeled recursively using a single part entity.

To tranform this ERD, you would begin by creating a Part relation with columns based on the attributes in the Part entity. The primary key of the relation would be PartNbr. To represent the many-to-many recursive relationship, create an association table whose primary key consists of the primary keys from the recursive relationship (PartNbr and SubPartNbr). In this case, SubPartNbr is simply the number of the part contained in the larger part. Part (PartNbr, Description, UnitCost) SubPart (PartNbr, SubPartNbr)

Transforming ERDs into Relations

4

Binary One-to-One In a binary relationship two entites are directly related. Binary relationships are, by far, the most common type. Consider the following example of a binary one-to-one relationship. Each member of a club may, or may not, rent a locker. A locker can be rented by one member at a time. Of course, every locker is not rented all the time.

To tranform this ERD, you would begin by creating relations for ClubMember and Locker using the attributes from each entity. The primary key of the ClubMember relation would be MemberNbr, while the primary key of the Locker relation would be LockerNbr. To represent the one-to-one binary relationship, add the primary key of one relation to the other relation as a foreign key. You could add the primary key of ClubMember (MemberNbr) to Locker as a foreign key:

ClubMember (MemberNbr, FirstName, LastName, PhoneNbr) Locker (LockerNbr, Room, Size, MemberNbr) Or, you could add the primary key of Locker (LockerNbr) to ClubMember as a foreign key:

ClubMember (MemberNbr, FirstName, LastName, PhoneNbr, LockerNbr) Locker (LockerNbr, Room, Size)

Transforming ERDs into Relations

5

Binary One-to-Many The following example models the relationship between a college department and the courses that it offers. A department offers one, or many courses while a course is offered by one, and only one, department.

To tranform this ERD, you would begin by creating relations for Department and Course using the attributes from each entity. The primary key of the Department relation would be DepartmentID, while the primary key of the Course relation would be CourseID. To represent the one-to-many binary relationship, add the primary key from the one side of the relationship (Department) as a foreign key to the relation on the many side (Course).

Department (DepartmantID, Name, Chair) Course (CourseID, Description, Credits, DepartmentID)

Transforming ERDs into Relations

6

Binary Many-to-Many The relationship between students and course sections is a classic example of a binary many-to- many relationship. A student can enroll in zero, or many sections while a section can have zero, or many students enrolled in it. While this relationship may appear to be straightforward, problems arise when you want to record grades. Where should the grade go - in the Student entity or the Section entity? Since students can enroll in many sections, a single grade attribute in the Student entity won’t work. Likewise, since a section can have many students enrolled in it, a single grade attribute in the Section entity won’t work either. For these reasons, grade can’t go in either entity. It, therefore, becomes an attribute of the relationship between them (we show this by connecting grade to the relationship with a dashed line).

When a many-to-many relationship exists between two entities, you need to replace the relationship with an associative entity. With an associative entity you have two options for the primary key: Option 1: Composite PK The primary key of the associative entity can be created as a composite of the primary keys from each of the associated entities. Using this option, the primary key of the associative entity (Enrollment) would be a composite of StudentNbr from Student and SectionNbr from Section. Please note that StudentNbr in Enrollment serves as a foreign key to Student while SectionNbr in Enrollment serves as a foreign key to Section.

Option 2: Surrogate PK Instead of using a composite primary key, a surrogate primary key could be created for the associative entity. Using this option, EnrollmentNbr would be added to the associate entity to

Transforming ERDs into Relations

7

serve as the primary key. StudentNbr and SectionNbr are included in the associative entity to serve as foreign keys to Student and Section respectively.

With either primary key option, the grade attribute is added to Enrollment to record the grade for a given student in a given section. The relationship between Student and Enrollment then becomes one-to-many (a student could enroll in many sections). The cardinality on the Enrollment end of the relationship is zero-to- many (0<) because a student may not be enrolled in any sections (min) but is probably enrolled in many sections (max). The cardinality on the Student end of the relationship is one-and-only- one (II) because each Enrollment record has to be for a single student. The relationship between Section and Enrollment is also one-to-many (a section could have many students enrolled). The cardinality on the Enrollment end of the relationship is zero-to- many (0<) because a section may have no students enrolled (min) but probably has many students enrolled (max). The cardinality on the Section end of the relationship is one-and-only- one (II) because each Enrollment record has to be for a single section. Option 1: Composite PK

Student (StudentNbr, FirstName, LastName, Major) Section (SectionNbr, CourseTitle, DayTime, RoomNbr) Enrollment (StudentNbr, SectionNbr, Grade)

Transforming ERDs into Relations

8

Option 2: Surrogate PK

Student (StudentNbr, FirstName, LastName, Major) Section (SectionNbr, CourseTitle, DayTime, RoomNbr) Enrollment (EnrollmentNbr, StudentNbr, SectionNbr, Grade)

Transforming ERDs into Relations

9

Ternary Much like a binary many-to-many relationship, a ternary relationship needs to be replaced with an associative entity. The following example models the relationship when a customer purchases a vehicle from a salesperson. When a vehicle is purchased, the date and price are recorded. Just like grade in the previous binary many-to-many example, date and price become attributes of the purchase relationship.

The ternary relationship is replaced with an associative entity called Purchase. The primary key of the associative entity can either be a composite of the primary keys from each of the associated entities (VehicleNbr from Vehicle, CustomerNbr from Customer, and SalespersonNbr from Salesperson), or a surrogate primary key (e.g., PurchaseNbr). PurchaseDate and PurchasePrice are also added to Purchase as attributes.

Transforming ERDs into Relations

10

Option 1: Composite PK

Vehicle (VehicleNbr, Make, Model, Features) Customer (CustomerNbr, FirstName, LastName, PhoneNbr) Salesperson (SalespersonNbr, FirstName, LastName, HireDate) Purchase (VehicleNbr, CustomerNbr, SalespersonNbr, PurchaseDate, PurchasePrice)

Option 2: Surrogate PK

Vehicle (VehicleNbr, Make, Model, Features) Customer (CustomerNbr, FirstName, LastName, PhoneNbr) Salesperson (SalespersonNbr, FirstName, LastName, HireDate) Purchase (PurchaseNbr, VehicleNbr, CustomerNbr, SalespersonNbr, PurchaseDate, PurchasePrice)

Transforming ERDs into Relations

11

Multi-valued Attributes When an entity has a multi-valued attribute, you can create a table, not a relation.

In the example, the Vehicle entity has a multi-valued attribute named Features. If you create the table from the entity it will not be a relation because it won’t meet the requirements of 1NF (no multi-valued attributes). Vehicle (VehicleNbr, Make, Model, Features) – This is a table, not a relation. If you want 3NF, you need to move the multi-valued attribute to a separate entity.

The VehicleFeature entity has a surrogate primary key (FeatureID) with a sequential number to make it unique. VehicleNbr is then added to VehicleFeature as a foreign key to create the link with Vehicle. Vehicle (VehicleNbr, Make, Model) VehicleFeature (FeatureID, Feature, VehicleNbr) If Vehicle 100 had three features (i.e., heated seats, satellite radio, and backup camera) the VehicleFeature table would end up with three records: FeatureID Feature VehicleNbr 1 heated seats 100 2 satellite radio 100 3 backup camera 100