Cs 474 Normalization

Front 1

What is Normalization?

Back 1

a step-by-step reversible process in which a set of given relations are replaced by successive collections of relations which have a progressively simpler and more regular structure.

Front 2

Is information lost during transformations between normal forms?

Back 2

No, because the normalization process is reversible.

Front 3

What are the benefits of the Normalization Process?

Back 3

- To make feasible to represent any relation in the database.
- To free relations from undesirable insertion, update, and deletion anomalies.
- To reduce the need for restructuring relations as new types of data are introduced.

Front 4

What does BCNF stand for?

Back 4

Boyce-Codd Normal Form

Front 5

What level of normal form do databases behave reasonably?

Back 5

3NF and BCNF

Front 6

1 Normal Form Conditions are:

Back 6

The intersection of a row and a column cannot have another relation or a set of values (repeating groups).

Front 7

When do you transform a unnormalized table into 1NF?

Back 7

Case No. 1- One of the attributes is also a table.

Case No. 2- There are one or more repeating groups in the unormalized table.

Front 8

When is a attribute functionally dependent on another attribute?

Back 8

if and only if each value of X is associated with exactly one value of Y in r.

X -> Y
X "functionally determines" Y

Front 9

Algorithm to determine Functional Dependency:

Back 9

Sort the relation on its X columns to bring tuples with equal X-values together.

If each set of tuples with equal X-values has equal Y-values, return true. Otherwise, return false.

Front 10

What are Functional dependencies useful for?

Back 10

determine partial and transitive dependencies

identify all possible candidate keys.

Front 11

Given any key (primary or candidate) all other attributes must be functionally dependent on the key. (T/F)

Back 11

True

Front 12

What do Inference Axioms allow us to derive?

Back 12

all functional dependencies that are satisfied by a relation r(R).

Front 13

Reflexivity

Back 13

If Y is a subset of X => X -> Y

Front 14

Augmentation

Back 14

If X --> Y
=> XZ --> Y

Front 15

Additivity

Back 15

If X --> Y
& X --> Z
=> X --> YZ

Front 16

Projectivity

Back 16

If X --> YZ
=> X --> Y
and
X --> Z

Front 17

Transitivity

Back 17

If X --> Y
& Y --> Z
=> X --> Z

Front 18

Pseudotransitivity

Back 18

If X --> Y
& YZ --> W
=> XZ --> W

Front 19

What are the basis of Armstrong’s axioms?

Back 19

Complete:
That is, ALL valid FDs that can be derived from a given set of FDs can de derived by means of the axioms.

Sound:
The axioms cannot derive FDs that are not valid.

Front 20

prime attribute

Back 20

Any attribute that belongs to any key (candidate or primary)

Front 21

nonprime attribute

Back 21

Any attribute that does not belongs to any key (candidate and primary)

Front 22

determinant

Back 22

left hand side of any functional dependency

Front 23

an attribute is partially dependant on key if...

Back 23

X if there is a proper subset of attributes W of X such that W --> Y

in addition of being dependent on the entire key a partially dependent attribute is functionally dependent on some other attribute of the key.

Front 24

What does it mean to be fully dependent on the key

Back 24

If an attribute Y is functionally dependent on the key X but not in any attribute W -that is part of the key- the attribute Y is said to be fully dependent on the key X

Front 25

Do anomalies occur in 1NF relations?

Back 25

yes, anomalies occur because there are nonprime attributes that are partially dependent on the key.

Front 26

Conditions for Second Normal Form (2NF):

Back 26

The relation r is already in 1NF.

No nonprime attribute is partially dependent upon any composite key of the relation. That is, all nonprime attributes must be totally dependent upon every key.

Front 27

Implications of the definition of 2NF:

Back 27

A relation with one-attribute primary key is automatically in 2NF.

A relation in which all its attributes are prime is automatically in 2NF.

Front 28

How to determine if a relation is in 2NF

Back 28

1.- Check that the relation is already in 1NF.

2.- Find ALL possible keys of the relation to determine all prime attributes.

3 Find ALL prime and nonprime attributes

4.- Show that ALL nonprime attributes are fully dependent upon any key.

Front 29

Heath's Theorem

Back 29

"Let R(A,B,C) be a relation where A, B, and C are set of attributes. If the relation R satisfies A→ B, then R is equal to the join of its projections on (A,B) and (A,C)."

Front 30

What anomalies are solved by moving from 1NF to 2NF?

Back 30

Insertion, deletion, and update

Front 31

What are the anomalies in 2NF?

Back 31

to transitive dependencies of nonprime attributes upon any key.

Front 32

When does a transitive dependency occur?

Back 32

A transitive dependency occurs when a non prime attribute determines another non prime attribute.

Front 33

Conditions for 3NF:

Back 33

The relation r is already in 2NF.

No nonprime attribute is functionally dependent upon any other nonprime attribute of the relation.

Front 34

How to determine if a relation is in 3NF

Back 34

Check that the relation is already in 2NF.

Show that all nonprime attributes are independent.

Front 35

Conditions for Boyce-Codd Normal Form (BCNF)

Back 35

The relation r(R) is already in 1NF.

For every set of attributes C of R, if any attribute not in C is functionally dependent on C, then each and every attribute in r(R) is functionally dependent on C.

Front 36

It may not be convenient to transform a relation to BCNF if:

Back 36

The functional dependencies are not preserved

The candidate keys have overlapping attributes

it may slow down the performance of the database due to its high number of tables and the join operations