CS 448 HW 4

.docx

School

Purdue University *

*We aren’t endorsed by this school

Course

448

Subject

Computer Science

Date

Apr 3, 2024

Type

docx

Pages

4

Uploaded by JusticeClover34803

CS 448 HW 4 Shatakshi Singh Question 1. (0.5 points) Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F. F = {{A, B}→{C, D}, {D}→{E, F, G}, {F, G}→{H}, {A}→{I}, {A, B}→{E, G}, {A, I} → {I, J}} (a) What is the closure of the set {A, D}? (b) What is the key for R? (c) What is the minimum cover for the set of functional dependencies F? (d) Decompose R into 2NF. (e) Decompose R into 3NF. Answer: a) {A, D} + = {A, D, E, F, G, H, I, J} b) The key for R is {A, B} c) The minimal cover is { {A, B} {C} {A, B} {D} {D} {E} {D} {F} {D} {G} {F, G} {H} {A} {I} {A} {J}} d) {A} {I} and {A, I} {I, J} are considered partial dependencies. After decomposition we have R1 = (A, I, J) and R2 = (A, B, C, D, E, F, G, H) which are 2NF. R1 = (A, I, J), e) R2 = (A, B, C, D, E, F, G, H). R1 is already 3NF. R2 is containing transitive dependency {D} {E, F, G}. We decompose it into R3 = (A, B, C, D) and R4 = (D, E, F, G, H). R4 is still in 2NF and contains transitive dependency {F, G} {H}. We decompose R4 into R5 = (D, E, F, G) and R6 = (F, G, H). Thus, we have R1 = (A, I, J), R3 = (A, B, C, D), R5 = (D, E, F, G), and R6 = (F, G, H).
Question 2. (0.75 points) (a) What are the possible sources of the information that defines the functional dependencies that hold among the attributes of a relation schema? Discuss two possible sources. Explain your answer. (b) What is meant by the closure of a set of functional dependencies? Illustrate with an example. (c) What is the lossless (or nonadditive) join property of a decomposition? Why is it important? Answer: a) Two possible sources are: a. Functional dependencies can be defined based on the business rules and constraints of an organization. For example, in a university database, a student's grade can be uniquely determined by the combination of the course and the student's ID. b. Functional dependencies can also be derived by analyzing the data in a database. By looking at the values of attributes, one can identify patterns and relationships that hold among them. For instance, in a customer database, the age of a customer can be determined by their date of birth. b) The closure of a set of functional dependencies is the set of all functional dependencies that can be inferred or derived from the original set of dependencies. For example, consider the following set of functional dependencies for a relation R: {A} → {B}, {B} → {C}. The closure set of R i.e. R + = {{A} → {B}, {B} → {C}, {A} → {C}}. c) The lossless (or nonadditive) join property ensures that if we decompose a relation schema into two or more smaller schemas and then join these smaller schemas together, we get back the original relation schema, without losing any information. The lossless join property is important because it ensures that a decomposition can be used to perform operations on the original relation schema without any loss of information. Queries performed on the decomposed relations would have the same results as queries performed on the original relation.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help