Analysis and Design of Data Systems. Functional Dependencies and Normalization Theory (Lecture 15)

or meaning of the attributes.

The database designers will use their understanding of the semantics (meaning) of the attributes of R—that is, how they relate to one another—to specify the functional dependencies that should hold on all relation states of R.

The main use of functional dependencies is to describe further a relation schema R by specifying constraints on its attributes that must hold at all times.

property of the relation schema R, not of a particular instance of R. Therefore, an FD must be defined explicitly by someone who knows the semantics of the attributes of R

One cannot determine which FDs hold and which do not unless the meaning of and the relationships among the attributes are clearly known and understood

property of the relation schema R, not of a particular instance of R. Therefore, an FD must be defined explicitly by someone who knows the semantics of the attributes of R

One cannot determine which FDs hold and which do not unless the meaning of and the relationships among the attributes are clearly known and understood

analyzing the given relation schemas based on their FDs and primary keys to achieve the desirable properties of :
minimizing redundancy and
minimizing the insertion, deletion, and update anomalies

In other words Normalization is a process to make the design have successively better quality.

If relations doesn’t meet certain conditions (normal form tests) they are decomposed into ‘smaller’ relations schemas that meet the tests hence possess desirable properties.

more than one key, each is called a candidate key

One of the candidate keys is arbitrarily designated to be the
primary key, and the others are called secondary keys

Let’s

An attribute of R is called a prime attribute if it is a member of some candidate key of R. An attribute is called nonprime if it is not prime.

atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.

dependencies whose left-hand side attributes are part of the primary key.
If the primary key contains a single attribute, the test need not be applied at all.

Nonprime attributes Ename, Pname, and Plocation violate 2NF.

it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key.