DBMS Assign 3

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.