4. Design Principles

Question 1

Reasons for nomalization

Answer 1

Eliminates redundancy

Makes database more robust to changes

Makes database easier to understand

Answer 2

𝑅{𝐴, 𝐵, 𝐶}

Question 3

Normalization terminology

Answer 3

𝑅{𝐴, 𝐵, 𝐶} - Denotes a relation R with attributes A, B, C

Functional dependency

Left-irreducability

Heath’s Theorem

𝑋 ⇒ 𝑌 ∶ X Implies Y

Question 4

Functional Dependencies

Answer 4

For two given subsets of the attributes of a relation x and y, 𝑋 ⇒ 𝑌 ∶ X Implies Y means that Y is functionally dependent on X

Means that: given a specific value of X, we can find its corresponding Y value

A dependency is trivial if Y is a subset of X

Question 5

Closure

Answer 5

The closure of F is the set of all functional dependencies logically implied by F

Question 6

Left Irreducibility

Answer 6

When 𝑋 ⇒ 𝑌 ∶ Y is irreducibly dependent upon X if X is as small a subset as possible

i.e. make your primary key a single column if possible

Question 7

Heath’s Theorem

Answer 7

Let 𝑅{𝐴, 𝐵, 𝐶} be a relational variable where 𝐴, 𝐵 and 𝐶 are
sets of attributes.

If I satisfies the functional dependency 𝐴 ⇒ 𝐵, R is equal to the join of its projections {A,B}, {A,C}

Meaning: we can split R into 2 relations –> one containing {A,B}, and one containing {A,C} –> then use join to recover the full table

Question 8

Normalization

Answer 8

The decomposition of 𝑅 into 𝑅1, 𝑅2, 𝑅n, … , 𝑅􀯡 is non-loss if 𝑅
is equal to the join of 𝑅1, … , 𝑅n

Normal Forms: Categories of normalization –> based on treatment of non-key attributes

Objective of normalization is to reduce risks of potential errors
–> so degrees of normal form are essentially degrees of risk you are open to

If a table meets a higher normal form, then it automatically meets the conditions of all of the lower ones

Question 9

1st Normal Form

Answer 9

Relational variable is in 1st normal form iff every legal value of that relational variable contains exactly one value

No composite attributes

No multi-value attributes

Each row must be unique –> something must qualify as key

Each column must be unique –> no repeating groups of attributes

Question 10

2nd Normal Form

Answer 10

Iff:

1) Requirements for 1st NF are met

2) Every non-key attribute is irreducibly dependent upon the primary key

Question 11

Non-key attribute

Answer 11

Attribute that does not participate in the primary key

Question 12

3rd Normal Form

Answer 12

Iff:

1) Requirements of 2NF are met

2) Every non-key attribute is non-transitively dependent upon the primary key

3) Every non-key attribute is Mutually Independent

4) There is irreducible dependency

Question 13

Transitive dependency

Answer 13

𝐴 ⇒ 𝐵 and 𝐵 ⇒ 𝐶 implies that 𝐴 ⇒ 𝐶

Question to verify this:

If I change a non-key attribute, will I need to change any other non-key attribute?

RULE: non-key attributes should depend upon the key, the whole key, and nothing but the key

Question 14

Mutually Independent

Answer 14

Two or more attributes are mutually independent if none of them is functionally dependent upon any combination of the others

Question 15

Irreducibly Dependent

Answer 15

When 𝑋 ⇒ 𝑌, 𝑌 is irreducibly dependent upon 𝑋 if 𝑋 is as small a
subset as possible.

Question 16

4th Normal Form

Answer 16

Only multi-valued dependencies allowed are multi-valued dependencies on the key

Must be in Boyce Codd normal form

Question 17

5th Normal Form

Answer 17

Iff:

Meets requirements for 4NF

Does not contain any join dependencies

Joining should be lossless