EERD and normalization

Production tracking is important in many manufacturing environments (e.g., the pharmaceuticals industry, childrens toys, etc.). The following ER diagram captures important information in the tracking of production. Specifically, the ER diagram captures relationships between production lots (or batches), individual production units, and raw materials.

1. Convert the ER diagram above into an enhanced ERD. Make assumptions where necessary. (15 points) a. Include a set of table definitions supporting attribute inheritance, constraints, and derivation rules. b. Indicate primary key and referential integrity constraints. c. Also indicate strong/weak entity, one to many relationships, many-to-many relationships. 2. Identify attributes that might represent a composite attribute and explain why/how it might represent composite attribute. ( 3 points) 3. Identify an attribute that might represent a derived attribute and explain why/how it might represent a derived attribute. ( 2 points) 4. Make sure the tables are at least in 3NF. If you believe that practical considerations prevent you from using a 3NF structure, explain why your decision to retain 2NF is appropriate. (10 points)