Following the requirements below create an ERD Entity Attributes Relationships E
ID: 3636830 • Letter: F
Question
Following the requirements below create an ERD
Entity
Attributes
Relationships
Employee
ID Number, Last Name, First Name, Phone Number, Employee Type ID
An Employee can belong to any one of the three job categories, but can belong to one and only one of the three. Employees have names and other contact information.
Employee Type
Type ID, Description, Hourly Pay Rate
An employee can be either a clerk, delivery person, or a supervisor.
Register Log
Register Log Number, Register Number, Log In Time, Log Out Time, Employee ID
Sales are made using a register and can be tracked by the register log number. An employee must log into the register before he/she can use it and must log out when finished with a shift at that register. An employee may use more than one register during a period of work.
Register
Register Number, Register Location Description, Register Serial Number
The store has 3 registers: One located at the back of the store and two located at the front of the store; one on the left and one on the right of the entrance.
Deliveries
Delivery Number, Employee ID, Sales Receipt Number, Time the Employee Left and Returned
Relates to both the employee and the sale entities. This entity will help track deliveries. An employee can make many deliveries but a sale is delivered by one and only one employee.
Location
Location ID, Aisle, Side, Shelf
Relates to product and identifies one or more areas of the store where products are displayed.
Product
Product ID, Name, Package Size, Cost, Quantity on Hand (QOH), Last Purchased Date, Reorder Minimum
Identifies the products the store sells. One or more products can be sold per sale. The quantity on hand allows John to determine his inventory levels. The reorder minimum can be used to determine when the inventory level has reached a reorder point.
Sale
Receipt Number, Sales Amount, Sale Date- Time
A sale is identified by a single receipt. Sales are:
Sold Items
Receipt Number, Product ID, Item Price, Quantity Sold
Sale Item is part of sale and records product sold per sale. Sale item must be able to associate multiple products sold on a single sales receipt.
Customer
Customer Number, First Name, Last Name, Address, Phone Number
A customer can be associated with multiple sales, but any one sale is to one and only one customer. A sale can occur without a customer registering in the system.
Using MS Visio, develop an ERD that meets the following guidelines:
Entity
Attributes
Relationships
Employee
ID Number, Last Name, First Name, Phone Number, Employee Type ID
An Employee can belong to any one of the three job categories, but can belong to one and only one of the three. Employees have names and other contact information.
Employee Type
Type ID, Description, Hourly Pay Rate
An employee can be either a clerk, delivery person, or a supervisor.
Register Log
Register Log Number, Register Number, Log In Time, Log Out Time, Employee ID
Sales are made using a register and can be tracked by the register log number. An employee must log into the register before he/she can use it and must log out when finished with a shift at that register. An employee may use more than one register during a period of work.
Register
Register Number, Register Location Description, Register Serial Number
The store has 3 registers: One located at the back of the store and two located at the front of the store; one on the left and one on the right of the entrance.
Deliveries
Delivery Number, Employee ID, Sales Receipt Number, Time the Employee Left and Returned
Relates to both the employee and the sale entities. This entity will help track deliveries. An employee can make many deliveries but a sale is delivered by one and only one employee.
Location
Location ID, Aisle, Side, Shelf
Relates to product and identifies one or more areas of the store where products are displayed.
Product
Product ID, Name, Package Size, Cost, Quantity on Hand (QOH), Last Purchased Date, Reorder Minimum
Identifies the products the store sells. One or more products can be sold per sale. The quantity on hand allows John to determine his inventory levels. The reorder minimum can be used to determine when the inventory level has reached a reorder point.
Sale
Receipt Number, Sales Amount, Sale Date- Time
A sale is identified by a single receipt. Sales are:
- made to one or more Customers but only one customer at a time.
- made by one or more Register Clerks but only one clerk per sale.
- recorded on one or more Registers but only one register per sale.
- delivered by one or more delivery persons but no one sale can be delivered by more than one delivery person.
Sold Items
Receipt Number, Product ID, Item Price, Quantity Sold
Sale Item is part of sale and records product sold per sale. Sale item must be able to associate multiple products sold on a single sales receipt.
Customer
Customer Number, First Name, Last Name, Address, Phone Number
A customer can be associated with multiple sales, but any one sale is to one and only one customer. A sale can occur without a customer registering in the system.
Explanation / Answer
It Identifies the products the store sells. One or more products can be sold per sale. The quantity on hand allows John to determine his inventory levels. The reorder minimum can be used to determine when the inventory level has reached a reorder point.