DBMS Assign 3
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: __________