I only need # 3 (The ER Diagram after completing #1 and #2)
solution, show the Initial Grouping, NF, Dependency Diagram, 2NF, and 3NF. When appropriate, be sure to identify all functional dependencies (both partial and transitive). WAREHOUSE The warehouse database stores inventory information. A warehouse has one manager who has an employee number (unique) and a name. A warehouse is identified by its warehouse name (unique) and its address. Every part in the warehouse has a part number (unique), inventory date, quantity-on- hand, supplier name (one), and the delivery number, data, and quantity. One delivery could deliver several parts. The data is shown below: WAREHOUSE (manager-id, manager-name, warehouse-name, warehouse-address, part-no, inventory-date, qty-on-hand, supplier-name, delivery-no, delivery-date, delivery-qty) manager- manager-warehouse warehouse- part- inventory- onsupplier- delivery- delivery- delivery- id address hand nameno name name no 1001 St Paul 1001 St Paul 1001 St Paul Duster Aoo1 7/28/201 Aoo2723/201 Aoo38/15/2016 Aoo27/26/201 VollzoDo01715/2016 0001 Duster South 6 Vollzo Doo1 2 Randste Doo2 Volo Do 15/2016 o8-09- 2016 0001 Duster South Marion Jones Marion Jones Marion Jones Marion Jones 0001 1004 St 1004 St 1004 St 1004 St 1008 St 0004 or 1201201 09-01- 201 0004 or 0 12-201 Doo4 04-01- 0004 or Aoo6 201 12 Panaso Doo 25/201 0004 Aoo7 o3-12-201623 Whirlpo Doo4 or 201 o8-o1- 2016 0 o8-o8- 0008 DawsonEast Aoo8 2016 4 SMC Doo6 1008 St 0008 Dawson East Aoo7 2016 2o Whirlpo Doo62016 1008 St o6-06 0008 Dawson East Ao1o o6-12-201 8 Paul nc Doo8 201 1. List the primary key(s) and list all transitive and/or partial dependencies for the WAREHOUSE relation. (10 points) WAREHOUSE (manager-id, manager-name, warehouse-name, warehouse-address, part-no, inventory-date, qty-on-hand, supplier-name, delivery-no, delivery-date, delivery-qty) Example Relational Schema EMPLOYEE(EMP CODE, EMP_LNAME, EMP_EDUCATION, JOB_CLASS, EMP_BASE SALARY, EMP COMMISSION RATE) The dependencies shown as: EMP-CODE-) -name, emp-education, job-class Partial Dependencies . No Partial Dependencies Transitive Dependencies JOB_CLASSemp_base_salary Convert the relation to 3NF. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes and that the naming conventions are met.) Write the corresponding relational schemas. Identify each primary key and each foreign key. (20 points) 2. 3. Draw the ER Diagram. (20 points) solution, show the Initial Grouping, NF, Dependency Diagram, 2NF, and 3NF. When appropriate, be sure to identify all functional dependencies (both partial and transitive). WAREHOUSE The warehouse database stores inventory information. A warehouse has one manager who has an employee number (unique) and a name. A warehouse is identified by its warehouse name (unique) and its address. Every part in the warehouse has a part number (unique), inventory date, quantity-on- hand, supplier name (one), and the delivery number, data, and quantity. One delivery could deliver several parts. The data is shown below: WAREHOUSE (manager-id, manager-name, warehouse-name, warehouse-address, part-no, inventory-date, qty-on-hand, supplier-name, delivery-no, delivery-date, delivery-qty) manager- manager-warehouse warehouse- part- inventory- onsupplier- delivery- delivery- delivery- id address hand nameno name name no 1001 St Paul 1001 St Paul 1001 St Paul Duster Aoo1 7/28/201 Aoo2723/201 Aoo38/15/2016 Aoo27/26/201 VollzoDo01715/2016 0001 Duster South 6 Vollzo Doo1 2 Randste Doo2 Volo Do 15/2016 o8-09- 2016 0001 Duster South Marion Jones Marion Jones Marion Jones Marion Jones 0001 1004 St 1004 St 1004 St 1004 St 1008 St 0004 or 1201201 09-01- 201 0004 or 0 12-201 Doo4 04-01- 0004 or Aoo6 201 12 Panaso Doo 25/201 0004 Aoo7 o3-12-201623 Whirlpo Doo4 or 201 o8-o1- 2016 0 o8-o8- 0008 DawsonEast Aoo8 2016 4 SMC Doo6 1008 St 0008 Dawson East Aoo7 2016 2o Whirlpo Doo62016 1008 St o6-06 0008 Dawson East Ao1o o6-12-201 8 Paul nc Doo8 201 1. List the primary key(s) and list all transitive and/or partial dependencies for the WAREHOUSE relation. (10 points) WAREHOUSE (manager-id, manager-name, warehouse-name, warehouse-address, part-no, inventory-date, qty-on-hand, supplier-name, delivery-no, delivery-date, delivery-qty) Example Relational Schema EMPLOYEE(EMP CODE, EMP_LNAME, EMP_EDUCATION, JOB_CLASS, EMP_BASE SALARY, EMP COMMISSION RATE) The dependencies shown as: EMP-CODE-) -name, emp-education, job-class Partial Dependencies . No Partial Dependencies Transitive Dependencies JOB_CLASSemp_base_salary Convert the relation to 3NF. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes and that the naming conventions are met.) Write the corresponding relational schemas. Identify each primary key and each foreign key. (20 points) 2. 3. Draw the ER Diagram. (20 points)
