You are to complete the ERD exercise detailed in the attached document.? Create the diagram using Lucidchart and submit your response as a PDF via the title link.?? Name the file ‘ER

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

ERD Practice BIS 638 – Database Management for Business Systems

Prestige Bakers has hired you to create a database to help mange customer orders at its three stores. Use the following information to create an ERD: For each store, Prestige records the store number, address, and phone number. Each store has multiple employees, while an employee works at only one store. Each employee has an employee number, name, title, date hired, years of service, hourly rate, and skill(s). Each store offers multiple items for sale, while a given item may be available at multiple stores. Prestige records the item number, name, description, and unit price for each item. Prestige records the customer number, name (i.e., a person’s name), address, and phone number for each of its customers. When a customer orders items from a store, the order is processed using the following form:

Order Form

Order Number: __________ Order Date: __________

Store Number: __________

Customer Number: __________

Item Number Quantity

Employee Number:

__________

ERD Practice Solution BIS 638 – Database Management for Business Systems

For each store, Prestige records the store number, address, and phone number. We begin by creating an entity for Store with StoreNbr as the primary key. Address is a composite attribute so it gets broken into its component parts: Street, City, State, ZipCode.

Each store has multiple employees, while an employee works at only one store. Each employee has an employee number, name, title, date hired, years of service, hourly rate, and skill(s). We create an entity for Employee with EmployeeNbr as the primary key. Name is a composite attribute so it gets broken into its component parts: FirstName and LastName. YearsofService (YOS) is a derived attribute so it gets enclosed in brackets. Skills is a multi-valued attribute so it gets enclosed in braces.

We then create a relationship between the Store and Employee entities. The cardinality is one- to-many (I<) on the Employee end of the relationship because a store should have at least one employee (min) and probably has many employees (max). The cardinality is one-and-only-one (II) on the Store end of the relationship because an employee has to work at a store (min) and can only work at one store (max). Make sure to add the primary key from the one-side (Store) to the many-side (Employee) as a foreign key (StoreNbr).

Each store offers multiple items for sale, while a given item may be available at multiple stores. Prestige records the item number, name, description, and unit price for each item. We create an entity for Item with ItemNbr as the primary key.

We then need to relate the Store and Item entities. This relationship is many-to-many (many items at many stores). The minimum cardinality on the Store end of the relationship is zero because an out of season item is available at no stores.

Since this is a binary, many-to-many relationship, it must be replaced with an associative entity (StoreItem). The primary key of StoreItem (StoreNbr, ItemNbr) is comprised of the primary keys of the associated entities (Store and Item). StoreNbr in StoreItem is also a foreign key to Store, while ItemNbr in StoreItem is a foreign key to Item.

The relationship between Store and StoreItem is then one-to-many (a store has many store items). The cardinality on the StoreItem end of the relationship is one-to-many (I<) because a store has to have at least one item (min) and probably has many items (max). The cardinality on the Store end of the relationship is one-and-only-one (II) because each StoreItem record has to be for a single store. The relationship between Item and StoreItem is also one-to-many (an item has many store items). The cardinality on the StoreItem end of the relationship is zero-to-many (0<) because an item may not be available at any stores (min) but it may be available at many stores (max). The cardinality on the Item end of the relationship is one-and-only-one (II) because each StoreItem record has to be for a single item.

Prestige records the customer number, name (i.e., a person’s name), address, and phone number for each of its customers. We create an entity for Customer with CustomerNbr as the primary key. Name is a composite attribute so it gets broken into its component parts: FIrstName and LastName. Address is a composite attribute so it gets broken into its component parts: Street, City, State, and ZipCode.

When a customer orders items from a store, the order is processed using the following form: Based on the form, we create an entity for Order with OrderNbr as the primary key. The entity also needs to include StoreNbr, CustomerNbr, and OrderDate.

An order can include multiple items so we can’t include item number or quantity in the Order entity. Instead, we need to show this as a relationship between the Order and Item entities. This relationship is many-to-many (many items on many orders).

Order Form

Order Number: __________ Order Date: __________

Store Number: __________

Customer Number: __________

Item Number Quantity

Employee Number:

__________

Since this is a binary, many-to-many relationship, it must be replaced with an associative entity (OrderItem). The primary key of OrderItem (OrderNbr, ItemNbr) is comprised of the primary keys of the associated entities (Order and Item). OrderNbr in OrderItem is also a foreign key to Order, while ItemNbr in OrderItem is a foreign key to Item. The relationship between Order and OrderItem is then one-to-many (an order can have many order items). The cardinality on the OrderItem end of the relationship is one-to-many (I<) because an order has to have at least one item (min) and probably has many items (max). The cardinality on the Order end of the relationship is one-and-only-one (II) because each OrderItem record has to be for a single order. The relationship between Item and OrderItem is also one-to-many (an item can be on many order items). The cardinality on the OrderItem end of the relationship is zero-to-many (0<) because an item may not have been ordered yet (min) but it may have been ordered many times (max). The cardinality on the Item end of the relationship is one-and-only-one (II) because each OrderItem record has to be for a single item. The OrderItem associative entity is also where we store the quantity of the item being ordered.

Returning to the Order entity, we note that StoreNbr also appears in the Store entity as a primary key. The fact that StoreNbr appears in both entities indicates that Order and Store are related. We, therefore, need to create a relationship from Order to Store and indicate StoreNbr as a foreign key in the Order entity. The cardinality on the Order end of the relationship is zero- to-many (0<) because a store could have no orders yet (min) but probably has many orders (max). The cardinality on the Store end of the relationship is one-and-only-one (II) because an order has is for a single store.

Since CustomerNbr appears in both the Order and Customer entities, we create a relationship from Order to Customer and indicate CustomerNbr as a foreign key in the Order entity. The cardinality on the Order end of the relationship is zero-to-many (0<) because a customer could have no orders (min) but probably has many orders (max). The cardinality on the Customer end of the relationship is one-and-only-one (II) because an order is for a single customer.

ERD Exercise BIS 638 – Database Management for Business Systems

William’s Furniture has hired you as a consultant to design a database to manage their materials inventory and customer orders. Use the following information to create an ERD: William’s is organized into different departments. Each department has a department number, name, and supervisor (i.e., the supervisor’s name). A department has one or more employees, while an employee works for only one department. Each employee at William’s has an employee number, name, title, date hired, years of service, hourly rate, and skill(s). Employees process customer orders using the following form (each order is processed by a single employee): For each customer, William’s records the customer number, name, address, and phone number. An order can include multiple products, while a product can appear on multiple orders.

Order Form

Order Number: __________ Order Date: __________

Customer Number: __________

Product Number Quantity

Employee Number: __________

William’s uses the following form to record information about their products: A product is composed of many raw materials, while a raw material can be used in many products. For each raw material, William’s records the material ID, unit of measure, and unit cost. William’s has a number of vendors who supply raw materials. For each vendor, William’s records the vendor number, name, address, contact name (i.e., a person’s name) and contact phone number. Vendors supply raw materials to the departments. Each time materials are supplied, William’s records the price that was charged and the supply date.

Product Bill of Material

Product Number: __________

Product Name: ______________________________

Product Description:

____________________________________________________________

____________________________________________________________

____________________________________________________________

Unit Price: __________

Vendor Number: __________

Bill of materials:

Material ID Quantity

Employee Number: __________