Assignment 1. You recognize that the existing table structure is very poor and need to improve it. Given what you see in the table above, a) Draw a dependency diagram. Identify the possible primary key(s). b) Identify and list all partial functional dependencies (if any) c) Identify and list all transitive dependencies (if any) d) Determine and report the normal form of this original table (25 points) 2. Review the dependency diagram and use the normalization approach to improve the data structure. Normalize to 3NF (Third normal form) and list all relations (tables) that result. As you work through the diagram you may have to create a few new attributes based on the scenario description. You also should make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, each relation has a unique primary key, and so on.) (25 points) 3. You know that normalization is a very useful tool to help eliminate data redundancies, however, it is incapable of serving as the sole source of good database design. Every good database design must start with a data model. Thus, you also need to create an ER diagram to properly document and design the database that the spa needs. Based on the tables that you identified through normalization, create a Crow's Foot ERD in Visio (or alternative). Label all entities, attributes, keys (primary and foreign) and relationships. Make sure all attributes are atomic and properly named. Resolve all many-to-many relationships by creating associative entities and identify their primary and foreign keys. Review and follow the guidelines for formatting your Visio diagrams posted with this assignment (entity borders, background color and fonts). If I cannot read your diagram, I cannot grade it. (25 points) Instructions: Create a dependency diagram, relations normalized to 3 normal form, and an ERD in Visio that captures the information requirements described below. Organize your ERD so that it fits on one page. Combine your dependency diagrams, 3NF relations and ERD into one file and save in PDF format. Submit your file on Blackboard by the beginning of class on Tuesday, September 29. Make sure your name is on your document. File name must follow this convention: LastName_Normalization_Fall2020.pdf (for example, Veltri_Normalization_Fal12020.pdf) Any questions regarding the assignment should be directed to your professor and not your classmates. This is an individual assignment and should be completed individually by you without outside assistance. UT's Academic integrity policy applies to this assignment and I reserve the right to ask you to explain your normalization process and your model to me before assigning a grade. Scenario: Palm Harbor Spa offers spa services and needs your help to evaluate their data records which contain the table structure shown below. They use this table to keep track of the customer bookings, booked spa services and technicians assigned to perform them. Sample BOOKED SERVICE Records Customer Gender Booking Service Booking Booking Customer Customer Number Code Service Name Date Time Name Phone 1 MANI1 Basic Manicure 9/18/2020 10:00 AM Lady Smiley 122-335-2332 1 PEDI2 Delux Pedicure 19/18/2020 10:00 AM Lady Smiley 122-335-2332 2 RMSGE Relaxation Massage 9/19/2020 11:00 AM Super Man 788-335-2312 3 PEDI2 Delux Pedicure 9/19/2020 11:00 AM Donald Duck 999-999-9999 4 MANI1 Basic Manicure 9/19/2020 2:30 PM Minnie Mouse 444-444-4444 4 PEDI2 Delux Pedicure 9/19/2020 2:30 PM Minnie Mouse 444-444-4444 4 RMSGE Relaxation Massage 9/19/2020 2:30 PM Minnie Mouse 444-444-4444 5 DMSGE Deep Tissue Massag 9/20/2020 10:00 AM Lady Smiley 122-335-2332 Female Female Male Male Female Female Female Female Service Spa Technician Spa Technician Cost Assigned Certifications $ 25.00 Tinker Bell Nails $ 30.00 Tinker Bell Nails $ 60.00 Wendy Darling Massage, Facials $ 30.00 Tinker Bell Nails $ 25.00 Mickey Mouse Nails, Massage $ 30.00 Mickey Mouse Nails, Massage $ 60.00 Mickey Mouse Nails, Massage $ 80.00 Peter Pan Massage Assignment 1. You recognize that the existing table structure is very poor and need to improve it. Given what you see in the table above, a) Draw a dependency diagram. Identify the possible primary key(s). b) Identify and list all partial functional dependencies (if any) c) Identify and list all transitive dependencies (if any) d) Determine and report the normal form of this original table (25 points) 2. Review the dependency diagram and use the normalization approach to improve the data structure. Normalize to 3NF (Third normal form) and list all relations (tables) that result. As you work through the diagram you may have to create a few new attributes based on the scenario description. You also should make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, each relation has a unique primary key, and so on.) (25 points) 3. You know that normalization is a very useful tool to help eliminate data redundancies, however, it is incapable of serving as the sole source of good database design. Every good database design must start with a data model. Thus, you also need to create an ER diagram to properly document and design the database that the spa needs. Based on the tables that you identified through normalization, create a Crow's Foot ERD in Visio (or alternative). Label all entities, attributes, keys (primary and foreign) and relationships. Make sure all attributes are atomic and properly named. Resolve all many-to-many relationships by creating associative entities and identify their primary and foreign keys. Review and follow the guidelines for formatting your Visio diagrams posted with this assignment (entity borders, background color and fonts). If I cannot read your diagram, I cannot grade it. (25 points) Instructions: Create a dependency diagram, relations normalized to 3 normal form, and an ERD in Visio that captures the information requirements described below. Organize your ERD so that it fits on one page. Combine your dependency diagrams, 3NF relations and ERD into one file and save in PDF format. Submit your file on Blackboard by the beginning of class on Tuesday, September 29. Make sure your name is on your document. File name must follow this convention: LastName_Normalization_Fall2020.pdf (for example, Veltri_Normalization_Fal12020.pdf) Any questions regarding the assignment should be directed to your professor and not your classmates. This is an individual assignment and should be completed individually by you without outside assistance. UT's Academic integrity policy applies to this assignment and I reserve the right to ask you to explain your normalization process and your model to me before assigning a grade. Scenario: Palm Harbor Spa offers spa services and needs your help to evaluate their data records which contain the table structure shown below. They use this table to keep track of the customer bookings, booked spa services and technicians assigned to perform them. Sample BOOKED SERVICE Records Customer Gender Booking Service Booking Booking Customer Customer Number Code Service Name Date Time Name Phone 1 MANI1 Basic Manicure 9/18/2020 10:00 AM Lady Smiley 122-335-2332 1 PEDI2 Delux Pedicure 19/18/2020 10:00 AM Lady Smiley 122-335-2332 2 RMSGE Relaxation Massage 9/19/2020 11:00 AM Super Man 788-335-2312 3 PEDI2 Delux Pedicure 9/19/2020 11:00 AM Donald Duck 999-999-9999 4 MANI1 Basic Manicure 9/19/2020 2:30 PM Minnie Mouse 444-444-4444 4 PEDI2 Delux Pedicure 9/19/2020 2:30 PM Minnie Mouse 444-444-4444 4 RMSGE Relaxation Massage 9/19/2020 2:30 PM Minnie Mouse 444-444-4444 5 DMSGE Deep Tissue Massag 9/20/2020 10:00 AM Lady Smiley 122-335-2332 Female Female Male Male Female Female Female Female Service Spa Technician Spa Technician Cost Assigned Certifications $ 25.00 Tinker Bell Nails $ 30.00 Tinker Bell Nails $ 60.00 Wendy Darling Massage, Facials $ 30.00 Tinker Bell Nails $ 25.00 Mickey Mouse Nails, Massage $ 30.00 Mickey Mouse Nails, Massage $ 60.00 Mickey Mouse Nails, Massage $ 80.00 Peter Pan Massage