Please help with the attached assignment.
CMPT 354 Module 4 Assignment Due: April 16, 2021 @ 11:59 PM Weighting: 8% 1. Overview The purpose of this assignment is to test your ability to use and apply functional dependencies and normalisation in the database design process. You will gain experience in isolating problems with relational schema by determining the appropriate normal form and finding examples of potential anomalies in relation instances. Finally, you will generate an efficient database schema using both the BCNF and 3NF approach to normalisation. This assignment must be completed individually. 2. Submission All submissions must be made through an electronic marking tool called Gradescope, which will also be used for providing feedback (enroll with the entry code M68KZM). You must record all your answers in the spaces provided in this document. Altering the format or layout of this document in anyway will attract penalties. You may however add landscape images in the submission boxes without changing the orientation of the page. 3. Marking The Module 4 assignment counts for 8% of course mark. 4. Task This assignment has been split into five sections which each examine a key topic covered in Module 4 of this course. Each section will have several questions with appropriate instructions to assist you in completing that question. You must complete all sections of this assignment and submit your answers using the boxes provided. http://www.gradescope.ca/ http://www.gradescope.ca/ 2 Section A – Anomalies & Functional Dependencies Question 1 A local grocery shop Kimchi N’ BreadTM uses a database to store transactions related to customer purchases. A sample of a table in their database has been provided below. For simplicity, the attribute names have been simplified to single letters. A B C D E F 1 Bread Grace 12/10/2020 4 1000 2 Kimchi Jack 12/10/2020 1 2000 3 Milk Grace 14/10/2020 4 2000 4 Bread Andrew 18/10/2020 8 1000 5 Kimchi Charlie 18/10/2020 7 2000 6 Milk Jack 19/10/2020 1 2000 Based on the data above, provide a list of all possible non-trivial functional dependences for this table. You do not need to justify your answers. Note: For this question, non-trivial FD’s with more than one attribute on the left-hand side should also not be included. For example, the following two FD’s should not be included: {A} → {A} {A, B} → {A} Question 2 SFU has decided to create an efficient database to help tutors manage their teaching schedule. The schema for their Tutor table and a sample of the instance data can be seen below. ID BuildingNumber BuildingName DayTime TutorNumber TutorName 10 7 ASB Thursday 12:00 PM 1345 Jelena 12 7 ASB Thursday 1:00 PM 1345 Jelena 13 47 Physics Thursday 1:00 PM 4123 Jason 14 68 Chemistry Friday 08:00 AM 2289 Tahlia 15 68 Chemistry Friday 10:00 AM 4123 Jason 16 7 ASB Friday 08:00 PM 2289 Tahlia This table contains the following non-trivial functional dependencies: {ID} → {BuildingNumber, BuildingName, DayTime, TutorNumber, TutorName} {BuildingNumber} → {BuildingName} {TutorNumber} → {TutorName} Using the table above, you must provide a brief example and explanation of database operations which would cause an insertion, modification and deletion anomaly. Your explanation should be brief and not exceed 50 words. A dummy example of the format your answers should be written in can be seen on the next page. 3 Example format: Insertion Anomaly: Modification Anomaly: Deletion Anomaly: Operation: Explanation: Operation: Explanation: Operation: Explanation: Operation: Insert <11, 7,="" ‘asb’,="" ‘monday="" 8:00="" am’,="" 0007,="" ‘jack’=""> into Tutor Explanation: This operation would cause an insert anomaly to occur because … 4 Section B – Keys For each question in this section, you are required to list all possible candidate keys for the given schema based on the functional dependencies provided. You may wish to compute the closure of your key(s) to confirms they are valid. Question 1 R [A, B, C, D, E, F, G, H, I, J] {A} → {B} {C} → {B} {B} → {D, E, F, G, H} {D, F} → {I, J, A} Question 2 R [A, B, C, D, E, F, G, H] {A, B} → {C, D, E} {C} → {A, B, D, E} {D} → {G} {E} → {F} Question 3 R [A, B, C, D, E, F, G, H, I, J, K] {A, C, D} → {K, J, E} {C} → {A, B, K} {D, H, I} → {E, C, G} {B} → {A} {K, G} → {B, C, H, I} Candidate Key(s): Candidate Key(s): Candidate Key(s): 5 Section C – Highest Normal Form For each question in this section, you are required state and justify the highest normal form of the relation given a schema and functional dependencies. Your explanations should be brief and concise. Hint: It may be useful to identify the candidate keys for each relation. Question 1 R [A, B, C, D, E, F] {B, C} → {D, E, F} {A} → {D, E, F} \ Question 2 R [A, B, C, D, E, F, G, H] {A, B, C} → {D, E, F, G, H} {G, H} → {A, B, C} Question 3 R [A, B, C, D, E, F, G, H, I, J, K, L] {A, C, L} → {E} {C, E, L} → {A} {H, B, C, F, L, A} → {I, J, K} {K} → {D, G} Highest Normal Form: Explanation: Highest Normal Form: Explanation: Highest Normal Form: Explanation: 6 Section D – BCNF Decomposition For each question in this section, you are required to decompose the given relation into BCNF form and state any new relations created in the process with their functional dependencies and identify any functional dependencies which are lost during the decomposition. You must show your working using the tree method presented in the lecture (Module 4 - slides of 89 and 90). Consider the functional dependencies in the order presented in the question. Question 1 R [A, B, C, D, E, F, G, H, I, J] {A} → {B, C} {B} → {D, E, F} {C} → {G, H, I} {H, I} → {F, J} 7 Question 2 R [A, B, C, D, E, F, G, H] {A, B, C} → {D, E, F, G} {G, H} → {A, B, C} {C} → {H} 8 Section E – 3NF Decomposition Question 1 Based on the following relational schema and functional dependencies, find minimal cover for relation R. R [A, B, C, D, E, F, G, H, I, J, K, L, M, N] {A} → {C, D, F, G} {B} → {E} {A, G} → {J, C} {D, E, B} → {H, I, J} {J} → {K, L, M} {M} → {N} 9 Question 2 The minimal cover has been provided below for a given relation with a set of functional dependencies. Using the minimal cover, normalise the relation to 3NF such that all functional dependencies are preserved. R [A, B, C, D, E, F, G, H] {A} → {D, F} {B} → {G, E} {F, G} → {H} Minimal Cover: { {A} → {D}, {B} → {G}, {B} → {E}, {A} → {F}, {F, G} → {H} }