Part 2: Normalization: Write your answers in a Word document and Visio file. (2 points) Individual or Group of 2 Students: If you do Part 2 and Part 3 as a group, BOTH of the group members must email the instructor the names by 11:59pm on Feb 6 (Th) Quality of your answer in the Word document: 0.8 point. The quality of your Visio diagram: 0.8 point Consistency between your answer in the Word and Visio diagram: 0.4 point. You are asked by the director of IT department to review a database design done by a student intern (see the table and sample records below) and normalize the table to a set of tables, all of which are in 3NF. 1. Analyze the Purchase Order table and sample records on the next page, and identify all the partial and/or transitive functional dependencies in it (if there is any). a. Show which attribute is a determinant and which attribute is determined by the determinant using the aaaaa (determinant) bbbbb format. Write this in the Word document. Very Important Note: To understand the aaaaa (determinant) bbbbb format, see pages 177-185 of Ch4. 2. Determine which normal form the Purchase Order table developed by the student intern is currently in, and briefly but sufficiently explain why. Write this in the Word document. 3. Normalize the table into a set of tables, all of which are in 3NF by removing all partial and/or transitive functional dependencies (if there is any). Assure you have all necessary foreign keys. Create the diagram in Visio. Assure there are no redundant attributes other than foreign keys and every table has the primary key. a. Use Microsoft Visio Crow's Foot Notion Database model diagram. Do NOT use UML OO-Class diagram. (To use Visio in LH2212, access FGCU Virtual Lab 5: CIS Lab at https://vlab.fgcu.edu. Read Description of the Table: The table below is created to store purchase order information (order form a company uses to buy office supplies from its suppliers). Purchase Order ID (Part of the Composite Primary key) should be a sequential number assigned by the DBMS as a unique number for each purchase order. Purchase Order Date is a date when the purchase order was created by an employee of the purchase department. The value of this attribute is populated by a computer using its system clock. Supplier ID is a unique data value assigned by the purchase department for each supplier. Supplier name is the registered name of the supplier. Item Code (Part of the Composite Primary key) is unique to each item this company buys. Item Description is the name of the item shown on the supplier's catalog. Unit Price is a price the employee of the purchase department and the supplier agree on a particular item on a urchase order. This price may be different from the standard unit price of the item on the supplier's catalog due to price change, special offer, or discount applied for a large ordered quantity. In short, this Unit Price already includes any special offer or discount for a particular purchase order (Notice the different prices of the 1st record and 5th record for the same item and the different prices of the 3rd record and 7th record). Quantity Ordered is the quantity of the item ordered by the employee of the purchase department. Special Instructions is an attribute where the employee of the purchase department can type any special instructions, notes or comments. This must be stored in a database. ID Purchase Order Table Structure and Sample Records. The combination of PO ID and Item Code are the Composite PK. PO PO Date Supplier Supplier Item Item Description Unit Quantity Special Instructions Name Code Price Ordered 1 2020-02-08 0101 Office Lowes OL298 Monitor Stand 5A 24.95 2 Requested by CFO. 1 2020-02-08 0101 Office Lowes OL564 Tablet case AiPad-M 35.98 1 For Jimmy Bond. 2 2020-02-10 2002 Best Food BF209 Coffee Moka Blend 25.50 1 Ship to Naples office by Feb 15. 2 2020-02-10 | 2002 Best Food BF309 Salt & Pepper Set 29.98 Send to the kitchen. 3 2020-02-12 0101 Office Lowes OL298 Monitor Stand 5A 21.00 20 10 for IT and 10 for ACT. 3 2020-02-12 0101 Office Lowes OL564 Tablet case AiPad-M 35.98 12 These are for IT. 4 2020-02-13 2002 Best Food BF209 Coffee Moka Blend 22.00 10 NULL 4 2020-02-13 2002 Best Food BF309 Salt & Pepper Set 29.98 3 Send to the coffee shop on 2F. Part 2: Normalization: Write your answers in a Word document and Visio file. (2 points) Individual or Group of 2 Students: If you do Part 2 and Part 3 as a group, BOTH of the group members must email the instructor the names by 11:59pm on Feb 6 (Th) Quality of your answer in the Word document: 0.8 point. The quality of your Visio diagram: 0.8 point Consistency between your answer in the Word and Visio diagram: 0.4 point. You are asked by the director of IT department to review a database design done by a student intern (see the table and sample records below) and normalize the table to a set of tables, all of which are in 3NF. 1. Analyze the Purchase Order table and sample records on the next page, and identify all the partial and/or transitive functional dependencies in it (if there is any). a. Show which attribute is a determinant and which attribute is determined by the determinant using the aaaaa (determinant) bbbbb format. Write this in the Word document. Very Important Note: To understand the aaaaa (determinant) bbbbb format, see pages 177-185 of Ch4. 2. Determine which normal form the Purchase Order table developed by the student intern is currently in, and briefly but sufficiently explain why. Write this in the Word document. 3. Normalize the table into a set of tables, all of which are in 3NF by removing all partial and/or transitive functional dependencies (if there is any). Assure you have all necessary foreign keys. Create the diagram in Visio. Assure there are no redundant attributes other than foreign keys and every table has the primary key. a. Use Microsoft Visio Crow's Foot Notion Database model diagram. Do NOT use UML OO-Class diagram. (To use Visio in LH2212, access FGCU Virtual Lab 5: CIS Lab at https://vlab.fgcu.edu. Read Description of the Table: The table below is created to store purchase order information (order form a company uses to buy office supplies from its suppliers). Purchase Order ID (Part of the Composite Primary key) should be a sequential number assigned by the DBMS as a unique number for each purchase order. Purchase Order Date is a date when the purchase order was created by an employee of the purchase department. The value of this attribute is populated by a computer using its system clock. Supplier ID is a unique data value assigned by the purchase department for each supplier. Supplier name is the registered name of the supplier. Item Code (Part of the Composite Primary key) is unique to each item this company buys. Item Description is the name of the item shown on the supplier's catalog. Unit Price is a price the employee of the purchase department and the supplier agree on a particular item on a urchase order. This price may be different from the standard unit price of the item on the supplier's catalog due to price change, special offer, or discount applied for a large ordered quantity. In short, this Unit Price already includes any special offer or discount for a particular purchase order (Notice the different prices of the 1st record and 5th record for the same item and the different prices of the 3rd record and 7th record). Quantity Ordered is the quantity of the item ordered by the employee of the purchase department. Special Instructions is an attribute where the employee of the purchase department can type any special instructions, notes or comments. This must be stored in a database. ID Purchase Order Table Structure and Sample Records. The combination of PO ID and Item Code are the Composite PK. PO PO Date Supplier Supplier Item Item Description Unit Quantity Special Instructions Name Code Price Ordered 1 2020-02-08 0101 Office Lowes OL298 Monitor Stand 5A 24.95 2 Requested by CFO. 1 2020-02-08 0101 Office Lowes OL564 Tablet case AiPad-M 35.98 1 For Jimmy Bond. 2 2020-02-10 2002 Best Food BF209 Coffee Moka Blend 25.50 1 Ship to Naples office by Feb 15. 2 2020-02-10 | 2002 Best Food BF309 Salt & Pepper Set 29.98 Send to the kitchen. 3 2020-02-12 0101 Office Lowes OL298 Monitor Stand 5A 21.00 20 10 for IT and 10 for ACT. 3 2020-02-12 0101 Office Lowes OL564 Tablet case AiPad-M 35.98 12 These are for IT. 4 2020-02-13 2002 Best Food BF209 Coffee Moka Blend 22.00 10 NULL 4 2020-02-13 2002 Best Food BF309 Salt & Pepper Set 29.98 3 Send to the coffee shop on 2F
