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: __________