Zybook Week 5

Question 1

Normal Forms

Answer 1

Rules for designing tables with less redundancy.

Question 2

Redundancy

Answer 2

Repetition of related values in a table. Causes database management problems.

Question 3

Boyce-Codd Normal Form

Answer 3

Most important, improved version of third normal form

Question 4

Functional Dependence

Answer 4

Dependence of one column on another

Question 5

Mutlivalued dependence and join dependence

Answer 5

Entail dependencies between three or more columns. Complex, uncommon and primarily of theoretical interest.

Question 6

First Normal Form

Answer 6

When all non-key columns depend on the primary key.

Question 7

Second Normal Form

Answer 7

When all non-key columns depend on the whole primary key aka a non-key column cannot depend on part of a composite primary key.

Question 8

Third Normal Form

Answer 8

Redundancy can occur in a second normal form table when a non-key column depends on another non-key column. Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key.

Whenever a non-key column A depends on column B, then B is unique. Columns A and B may be simple or composite. Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key.

Question 9

Candidate Key

Answer 9

A candidate key is a simple or composite column that is unique and minimal. Minimal means all columns are necessary for uniqueness. A table may have several candidate keys. The database designer designates one candidate key as the primary key.

Question 10

Non Key

Answer 10

A non-key column is a column that is not contained in a candidate key.

Question 11

Boyce-Codd Nornmal Form

Answer 11

Boyce-Codd normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite. This definition is identical to the definition of third normal form with the term ‘non-key’ removed.

Question 12

Trivial Dependencies

Answer 12

When the columns of A are a subset of the columns of B, A always depends on B. Ex: FareClass depends on (FlightCode, FareClass). These dependencies are called trivial.

Technically, trivial dependencies must be excluded in definitions of normal form: A table is in Boyce-Codd normal form if, for all non-trivial dependencies B → A, B is unique.

Question 13

Normalization

Answer 13

eliminates redundancy by decomposing a table into two or more tables in higher normal form

Question 14

What are the steps to normalizing to Boyce-Codd normal form?

Answer 14

List all unique columns. Unique columns may be simple or composite. In composite columns, remove any columns that are not necessary for uniqueness. The primary key is unique and therefore always on this list.

Identify dependencies on non-unique columns. Non-unique columns are either external to all unique columns or contained within a composite unique column.

Eliminate dependencies on non-unique columns. If column A depends on a non-unique column B, A is removed from the original table. A new table is created containing A and B. B is a primary key in the new table and a foreign key in the original table.

Question 15

Denormalization

Answer 15

means intentionally introducing redundancy by merging tables. Denormalization eliminates join queries and therefore improves query performance. Denormalization results in first and second normal form tables and should be applied selectively and cautiously.