Part I. ERD Diagram (10 points) Draw an ERD diagram based on the Chen model for a database which supports the basic operations of a company. Specify: ? Entity sets, their attributes, and primary keys; ? Relationship sets and their descriptive attributes (if such exist); ? Connectivities and cardinalities; ? Decompose all M-M relationships, if any; ? If found, replace any multivalued attributes and composite attributes appropriately. Consider the following requirements: ? A customer has a unique customer identifier. Other important attributes of each customer include name and address. They can request any number of work orders from the company. ? The company maintains a list of materials. The data about materials include a unique material identifier, a name and cost. ? A work order has a unique work order number, a creation date, a completion date, a work address and a set of (one or more) tasks. In addition, each work order has one optional supervising employee. Each employee has a unique number assigned by the company. Other important attributes of each employee include name and skill. An employee can have multiple skills. ? Each work order also has a collection of materials. The same material can be used by any number of work orders. Material requirement includes material quantity.

2 ? Each task has a unique task identifier, a task name, an hourly rate and estimated hours. Tasks are standardized across work orders so that the same task may be performed on many work orders. We have to keep record of actual hours of each task on a work order.

Part II. Normalization (10 points) Using the conceptual model (ERD) presented below, build a relational schema for the database of a manufacturing company. For each of the relations, indicate the normal form for that relation. If the relation is not in third normal form, decompose it into 3NF.

Transcribed Image Text:

Customer C_ID M Date C_Name Due_date date_charged amount Owns M M M 1 Account No 1 Credit Card Charge Merchant Account B No B_address Balance M Saving managed by has rate Branch M Employee manage E ID E_Name Salary Ac Ga Customer C_ID M Date C_Name Due_date date_charged amount Owns M M M 1 Account No 1 Credit Card Charge Merchant Account B No B_address Balance M Saving managed by has rate Branch M Employee manage E ID E_Name Salary Ac Ga