CS 6400 Exam 4 Questions with Correct Answers New Version (Rated 100%)

CS 6400 Exam 4 Questions with Correct Answers New Version (Rated 100%) Functional Dependencies - Answers Let X and Y be sets of attributes in R. Y is functionally dependent on X in R iff for each x in RX, there is precisely one y in RY. _________ determines _______ X -- Y Email -- BirthYear, CurrentCity, Salary Questions to ask about normalization - Answers Is this relation laid out in such a manner that it is easy to enforce the functional dependencies that we want to enforce in the database? How do we normalize the relation (or decompose into smaller relations) without information loss and so that the functional dependencies can be enforced? Rules for normalization - Answers no redundancy no cluttering of facts must preserve information must preserve functional dependencies What is not a relation? - Answers Non-first normal form data structures can contain multivalued attributes values of attributes in relations are pulled from sets of atomic values What to do with multivalued attributes? - Answers Repeat the key for each multivalue attribute i.e., if user1 has 3 interests with 3 since ages, turn the 1 row into 3 Problems to look for with normalization - Answers Redundancy Insertion anomaly Deletion anomaly Update anomaly Redundancy - Answers Leads to inconsistency Database can be useless from the point of view of understanding repetitive fields Insertion anomaly - Answers occurs when certain attributes cannot be inserted into the database without the presence of other attributes, leads to many NULL values or duplicates Deletion anomaly - Answers deleting rows may cause a loss of data that would be needed for other future rows Update anomaly - Answers improper database organization where a non-primary key item is stored multiple times; updating the item in one location and not the others causes data inconsistencies redundant data may not be properly updated Information loss in normalization - Answers getting more or less information from decomposing a relation into smaller tables that doesn't reflect reality Dependency Loss in normalization - Answers If we decompose a table into multiple tables, then we cannot enforce the functional dependencies, or may lose the ability to enforce them, that are split between the two relations dependencies should be together in the tables (Except transitive) A perfect decomposition has - Answers no redundancies no insertion anomalies no deletion anomalies no update anomalies no information loss no dependency loss the idea is to decompose and if we need to see the original view, we join the decomposed tables Full functional dependencies - Answers Let X and Y be sets of attributes in R. Y is fully functional dependent on X in R iff Y is functional dependent on X and Y is not functional dependent on any proper subset of X ex: sinceAge is fully functional dependent on interest because it is not determined on email alone How do we enforce full functional dependencies? - Answers with keys in a relation, the values of the key are unique If we want to enforce that X determines Y, then we make X the key in the relation NF^2 - Answers Non first normal form 1NF - Answers R is in 1NF iff all domain values are atomic All relations are automatically born in first normal form because of the definition of a relation 2NF - Answers R is in 2NF iff R is in 1NF and every nonkey attribute is fully dependent on the key 3NF - Answers R is in 3NF iff R is in 2NF and every nonkey attribute is non-transitively dependent on the key BCNF - Answers R is in BCNF iff every determinant is a candidate key a determinant is a set of attributes on which some other attribute is fully functionally dependent How to compute with functional dependencies (Armstrong) - Answers Reflexivity Augmentation Transitivity Reflexivity - Answers if Y is part of X, then X -- Y (X determines Y as a function) Email, Interest -- Interest If the right hand side is a subset of the left hand side, then the left hand side determines the right hand side Augmentation - Answers If X -- Y, then WX -- WY If Email -- BirthYear, then Email, Interest -- BirthYear, Interest (W is Interest) Transitivity - Answers If X -- Y and Y -- Z, then X -- Z Email -- BirthYear and BirthYear -- Salary, then Email -- Salary How do we guarantee lossless joins? - Answers The join field must be a key in at least one of the relations How do we guarantee preservation of functional dependencies? - Answers The meaning implied by the remaining functional dependencies must be the same In a transitive dependency, even though we don't see Email -- Salary explicitly, we see it implicitly with transitivity 3NF and BCNF - Answers There does exist relations which can only be decomposed into 3NF, but not to BCNF, while being lossless and dependency preserving