assign5

Fall 2023 CSDS 341 Introduction to Database Systems Prof. Dianne Foreback Assignment 5 – No Points Earned Key Distributed with this Assignment 1 of 2 This assignment will not be graded and no points will be earned. The solution key is posted along with this assignment. 0. Do the practice exercises from the book 7.1, 7.2 7.6, 7.9 the first part of the question, 7.10, 7.13 and make certain that you understand 7.18 transitive dependency and 3 rd Normal Form and in 7.19 partial dependencies. These questions and answers are available from the book site here . Recall, all solutions to practice exercises are here https://www.db-book.com/Practice- Exercises/index-solu.html 1. Given two relations A and B, consider that the primary key of A can functionally determine the primary key of B. Write this as pk(A) → pk(B). Also consider that pk(B) → pk(A). Is this a one-to- one, one-to-many, many-to-one, or many-to-many relationship. Explain. 2. Consider the relation R(A,B,C,D,E) with the set of functional dependencies F={A → B, B → C, E → B, CD → A} and its decomposition to the relations 𝑅 1 (?, ?, ?), 𝑅 2 (?, ?, ?)𝑎𝑛𝑑 𝑅 3 (?, ?). Is this a lossy or lossless decomposition? Explain. To receive credit for this, you MUST show your work and EXPLAIN why it is lossy/lossless. Recall: determining if a decomposition is lossless/lossy requires the binary operation of natural join and using the closure of the intersection of two relations. We will do a similar problem in class on Wed. Nov 29 th , 2023. Show the relations used for each natural join and closure. 3. Consider the relation R(A, B, C, D, E, F, G, H) and the set of functional dependencies F = { AD → BCF, A → G, BC → E, D → F, E → H, E → D} . a. What are the candidate keys per this relation based on the set of functional dependencies? Your work must be shown by explaining how you logically reduced the closure ???????? + to a smaller proper subset using Armstrong Axioms and Inference rules. Then, you must show the closure of the candidate keys and show no proper subset can be a candidate key. b. From your answer in part a, what are the prime attributes? 4. Consider the relation R(A, B, C, D, E, F, G, H) and the set of functional dependencies F = { AD → BCF, A → G, BC → E, D → F, E → H}. Notice, this is the same relation from above but the set of functional dependencies in this example is missing the functional dependency E → D from above. Prof Foreback will give another example in class on Wed., Nov 29 th . a. What are the candidate keys? Again, show your work as done for part 3a. b. This relation is not in 2 nd Normal Form (2NF). Why? Explain by giving the rule(s) that are broken and how the rule(s) with example(s) are broken via this relation and F. c. Decompose this relation to 2NF (but not 3NF). Show your work and include the necessary closures and the new set of functional dependencies that are derived for each decomposition. You will be using the new set of functional dependencies later in this problem for a couple more questions. d. After applying the rule(s) to normalize from 1NF to 2NF, why is the decomposition not in 3NF? Explain by giving the rule(s) that are not satisfied with the example(s) from your decomposition.