Chapter 7 Flashcards
What is an insertion anomaly?
Occurs when extra data beyond the desired data must be added to the database.
What is an update anomaly?
Occurs when it is necessary to change multiple rows to modify only a single face
What is a deletion anomaly?
Occurs whenever deleting a row inadvertently causes other data to be deleted.
What is the cause of modification anomalies?
Poor DB design causes the modification anomaly, A good DB design avoids modification anomalies by eliminating excessive redundancies
What is normalization?
Is the (conceptual and theoretical) process of removing redundancy in a table so that the table does not have modification anomalies It is not practical - using normalization you can judge, but cannot build a database
What is a functional dependency?
A constraint about two or more columns of a table. X determines Y (X –> Y) if there exists at most one value of Y for every value of X
How is it like a candidate key? (a functional dependency)
You can think about functional dependency as identifying potential candidate keys. By stating that X–> Y, if X and Y are placed together in a table without other columns, X is a candidate key. Every determinant (LHS) is a candidate key if placed in a table with the other columns that it determines
What is the meaning of tan FD with multiple columns on the right-hand side?
Multiple columns on the RHS abbreviate separate FDs with the LHS determine each of the RHS columns
Why be careful when writing FDs with multiple columns on the left-hand side?
Multiple columns on the LHS indicate that the combination of columns determines the RHS columns. You should be careful that you have written the LHS with the minimal columns for the dependency. Otherwise, the normalization rules and sw procedures may not work as intended. Writing an FD with extra columns in the LHS is the same as writing a candidate key with extra columns. Both will lead to incorrect DB constraints.
What is a normal form?
rule about allowable dependencies. Each normal form removes certain kinds of redundancies
What does 1NF prohibit?
1NF prohibits nesting or repeating groups in tables. A table not in 1NF is unnormalized or non-normalized
What FDs are not allowed in 2FN? 3FN?
FDs in which part of a key determines a nonkey column
FDs in which a nonkey column determine another nonkey column
What are partial FDs?
Non-key attributes are dependent on not only the whole but also a part of the composite key
Combined definition of 2NF and 3NF?
A table is in 3NF if every nonkey column is dependent on a candidate key, the whole candidate key, and nothing but candidate keys
Splitting a 2NF table?
Allnon-key FDssharing a common determinant must become a new table
Remove dependents of all non-key FDsfrom the original table.