Chapter 7

Question 1

What is an insertion anomaly?

Answer 1

Occurs when extra data beyond the desired data must be added to the database.

Question 2

What is an update anomaly?

Answer 2

Occurs when it is necessary to change multiple rows to modify only a single face

Question 3

What is a deletion anomaly?

Answer 3

Occurs whenever deleting a row inadvertently causes other data to be deleted.

Question 4

What is the cause of modification anomalies?

Answer 4

Poor DB design causes the modification anomaly, A good DB design avoids modification anomalies by eliminating excessive redundancies

Question 5

What is normalization?

Answer 5

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

Question 6

What is a functional dependency?

Answer 6

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

Question 7

How is it like a candidate key? (a functional dependency)

Answer 7

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

Question 8

What is the meaning of tan FD with multiple columns on the right-hand side?

Answer 8

Multiple columns on the RHS abbreviate separate FDs with the LHS determine each of the RHS columns

Question 9

Why be careful when writing FDs with multiple columns on the left-hand side?

Answer 9

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.

Question 10

What is a normal form?

Answer 10

rule about allowable dependencies. Each normal form removes certain kinds of redundancies

Question 11

What does 1NF prohibit?

Answer 11

1NF prohibits nesting or repeating groups in tables. A table not in 1NF is unnormalized or non-normalized

Question 12

What FDs are not allowed in 2FN? 3FN?

Answer 12

FDs in which part of a key determines a nonkey column

FDs in which a nonkey column determine another nonkey column

Question 13

What are partial FDs?

Answer 13

Non-key attributes are dependent on not only the whole but also a part of the composite key

Question 14

Combined definition of 2NF and 3NF?

Answer 14

A table is in 3NF if every nonkey column is dependent on a candidate key, the whole candidate key, and nothing but candidate keys

Question 15

Splitting a 2NF table?

Answer 15

Allnon-key FDssharing a common determinant must become a new table
Remove dependents of all non-key FDsfrom the original table.

Question 16

Splitting a 3NF table?

Answer 16

All non-key FDs with key dependents sharing a common determinant must become a new table

Question 17

FDs not allowed in BCNF?

Answer 17

FDs in which the determinant is not a candidate key

Question 18

Two concepts in BCNF definition?

Answer 18

A table is said to be BCNF when it satisfies the below two concepts-
It is in 3NF.
For any functional dependency A->B, A should be a super key or a candidate key. In other words, if B is a prime attribute, A cannot be a non-prime attribute.

Question 19

Why is the BCNF definition preferred to the original 3NF definition?

Answer 19

because it’s a simpler definition and provides the basis for the simple synthesis algorithm