Normalization Theory

by K. Yue

1. Functional Dependencies

• Normal forms: a set of rules to avoid redundancy and inconsistency.
• Require the concepts of data dependencies. Examples:
  1. functional dependency (FD, most important: up to BCNF)
  2. multivalued dependency (MVD for 4NF)
  3. join dependency (5NF)
• Common Normal Forms in ascending order: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, 6NF.
• Higher normal forms are more restrictive.
• A relation is in a higher normal form implies that it is in a lower normal form, but not vice versa.

Example:

If a relation R is in BCNF, then R is also in 3NF, 2NF and 1NF.

If a relation is in 2NF, then

1. It is in 1NF,
2. it may or may not be in 3NF, and
3. it may or may not be in BCNF.

If a relations is not in 3NF, then

1. It is not in BCNF.
2. It may or may not be in 1NF or 2NF.

• In general, the higher the normal forms a relation is in, the better the design of the relation in terms of avoiding redundancy and inconsistency is.
• However, it may be necessary to consider other issues, especially performance.
  • Higher normal forms may be achieved by decomposition, resulting in more relations. More joins may then be needed to provide the data for a query, decreasing performance.
• 1NF is usually assumed. However, there are relations not in 1NF in both theory and practice.
  • For an example, a composite data type may be supported by a specific DBMS vendor.
  • Standard SQL supports many non-1NF features.
• 2NF are more interesting for historical reasons.
• 4NF and 5NF involves the concept of multivalued and join dependencies (MVD and JD). They are hard to understand and even harder to apply in most situations.
• Domain Key Normal Form (DKNF) involves the concept of constraints.
• Based on the concept of functional dependencies (FD), the most important normal forms are
  • 3NF and
  • BCNF (Boyce-Codd Normal Form).

Functional Dependencies (FD):

• Each attribute in a database represents certain data information in the application.
• There can be dependency between data.
• For example, types of dependency and relationship between two sets of attributes:
  • Many to one (0..* to 0..1)
  • Many to many (0..* to 0..*)
• These relationships are the results of assumptions we made about the application requirements.

Example

Many to many relationships.

Consider an instance of the relation Enroll:

Under reasonable assumptions, there are many to many relationships between these sets of attributes:

1. Course and Student: A course may enroll many students. A student may take many courses.
2. Course and Grade
3. Student and Grade
4. {Course, Grade} and Student: both S2 and S3 may have a grade of B in Course C1.

However, the relationship between {Course, Student} and Grade may not be a many-to-many relationship if we assume that a student can only has one grade for a given course.

• A many to many relationship between two sets of attributes means that there is no functional dependency between the values of these two sets of attributes.

Example

Many to one relationships.

For many applications, the relationship between the two sets of attributes, SSNUM and NAME, are many to one.

SSNUM        ->     NAME
(many)                (one)

Assumptions:

1. A SSN uniquely identifies a person.
2. Given a SSNum, there can only be one Name associated with it (not allowing/storing alias, etc.)
3. Many different SSNum's (persons) may have the same Name.
4. There should not be two tuples with the same SSNum, but different NAME in all instances of R.

Terms:

1. SSNum uniquely determines Name.
2. Name is functionally determined by SSNum.
3. There is a functional dependency SSNum -> NAME.
4. Hence, a functional dependency specifies a many to one relationship between two sets of attributes.

For example, the relation instance:

is not allowed if we assume SSNum -> NAME.

Example

In a university, there may be a many-to-one relationship between {CourseId, StudentId} and Grade.

In a university, there may be a many-to-one relationship between {CourseId, StudentId} and Grade.

Interpretations:

1. A student may have only one grade for a course.
2. We say that there is a functional dependency:
   • CourseId, StudentId -> GRADE, or
   • {CourseId, StudentId} determines Grade.
3. Note that under different assumptions, the functional dependency may not be true.
4. For example, if a student is allowed to retake a course, then he may have two grades for the same course (in different semesters), then CourseId, StudentId -> Grade  is false.
5. We may actually have CourseId, StudentId, Semester -> Grade

• Hence, functional dependency is a result of the requirements and business logics of the applications. 
• There is no universally true non-trivial functional dependency.
• In other words, functional dependencies depend on the semantic of the problems.

Note that AB->CD is a shorthand notation for {A,B} -> {C,D}

Example

In most applications, we have

SSNum -> Name             (i.e.  a person has only one SSNum.)

However, in a criminal database, several bad guys may use the same fake SSNum, and thus

SSNum -> Name  is not true.

Or, if you are dealing with an international data base with many countries.  Each country may has its own SSNum.  Two countries may issue the same SSNum.  Hence,

SSNum -> Name   is not true.

We may instead have  SSNum, Country -> Name.

Definition of FD (from EN):

A relation scheme R is said to satisfy the functional dependency X -> Y if for any relation r that uses R, if there are two tuples s and t in r such that s[X] = t[X], then s[Y] = t[Y].

Example: This instance of R does not violate X->Y.

This instance of R violates X->Z.

In order to have X-> Y, all instances must not violate the conditions.

Example

SSNUM -> SNAME:

There are no two tuples with the same SSNUM but different names.

DEPT_NO -> MANAGER_NO:

There are no two tuples with the same DEPT_NO but different MANAGER_NO.  A department has only one manager.

SNUM, PNUM, DATE -> QUANTITY

There are no two tuples with the same SNUM, PNUM and DATE but different QUANTITY.  That is, any supplier has only one shipment of a part on a given date.

In the example of poorly designed database:

If we assume that a department has only one manager, we have:

DEPT_NO -> MANAGER_NO

Note that we also have:

NAME, DEPT_NO -> MANAGER_NO
EMP_NO, DEPT_NO -> MANAGER_NO

and so on.

Armstrong's axioms

• A set of axioms for inference with FD: http://en.wikipedia.org/wiki/Armstrong%27s_axioms.
• Axioms: 'self-evidence', 'assumed', or 'established'.
• Three basic axioms:
  1. Reflexivity: If X and Y are sets of attributes and Y is a subset of X, then X -> Y.
  2. Augmentation: If X -> Y then X Z -> Y Z.
  3. Transitivity: If X -> Y and Y -> Z then X -> Z
• Three additional rules that can be proven by the basic axioms.
1. Pseudo-transivitiy Rule: If X-> Y, YZ -> A then XZ -> A
2. Decomposition Rule: If X -> Y Z, then X -> Y and X -> Z.
3. Union Rule:  If X -> Y and X -> Z then X -> Y Z.
• Armstrong's axioms are sound and complete.
  1. Sound: implies only FD that are correct.
  2. Complete: can be used to imply all correct FD.
• CS students need to know how to infer using a formal mathematical method.

Example

Let X be CITY STREET, Y be STREET, then Y is a subset of X, and X -> Y or CITY STREET -> STREET. (Reflexivity).

• If two tuples have the same value of CITY and STREET, then they surely have the same value of STREET.
• This is so trivial that we call a functional dependency likes CITY STREET -> STREET a trivial functional dependency. They do not actually specify any business requirement.

A -> A and BC -> B are trivial.

• Since trivial functional dependencies do not actually give you any information, we are only interested in non-trivial functional dependency.

If EMP_NO  ->  DEPT_NO, and DEPT_NO  ->  MANAGER_NO
then EMP_NO  ->  MANAGER_NO

Interpretation: If

• every employee works for only one department, and
• every department has only one manager,

then every employee has only one manager.

Proof with Armstrong axioms.

1. Show new facts (FDs) and provide the reasons. Each new fact should be numbered for easy reference.
2. Stop when the new fact is the FD to be proved.

Example

Prove the union Rule.

Proof.

(1) X -> Z (given)
(2) X X -> X Z (augmentation of (1) with X)
(3) X -> XZ (simplification of (2))
(4) X -> Y (given)
(5) XZ -> YZ (augmentation of (4) with Z)
(6) X -> YZ (transitivity on (3) and (5))

Exercise

Prove the pseudo-transitivity rule.

Examples

(1) Prove that F = AC->B, B->D, AE->C}

implies AE->D

Proof. For example:

[1] AE-> C (given)
[2] AC -> B (given)
[3] AAE -> B (pseudo-transitivity on (1) and (2)) 
[4] AE -> B (simplification of (3))
[5] B -> D (given)

(2) Prove that F = {AB->C, AC->D, BD->E}

implies AB->E

Proof. For example:

[1] AB-> C (given)
[2] AC -> D (given)
[3] AAB -> D (pseudo-transitivity on (1) and (2)) 
[4] AB -> D (simplification of (3))
[5] BD -> E (given)
[6] ABB-> E (pseudo-transitivity on (4) and (5))
[7] AB-> E (simplification of (6)) 

Keys and Superkeys

• We can use functional dependencies to define keys and superkeys.
• For a relation scheme R, K is a candidate key (CK) if
  1. Uniqueness:  K -> R.
  2. Minimality:  there is no proper subset of K that determines R. (There is no extraneous attribute.)
• K is a superkey if K -> R. Superkeys (SK) do not need to satisfy the minimality requirement.
• Some properties:
  1. If K is a CK, any superset of K is a SK.
  2. If K is a CK, any proper subset of K is not a CK.
  3. If K is a CK, any proper superset of K is not a CK.
• Note that the primary key of a table is just a selected candidate key used to structure the physical storage. It is just like other candidate keys (alternate keys) in the context of the normalization theory.
• A CK with only one attribute is known as a simple key. A CK with more than one attributes is known as a composite key.

Example

In EMPLOYEE(EMP_NO, DEPT_NO, MANAGER_NO) with

EMP_NO -> DEPT_NO and
DEPT_NO -> MANAGER_NO.

By the transitivity axiom, EMP_NO -> MANAGER_NO.
By the union rule, EMP_NO -> EMP_NO DEPT_NO, MANAGER_NO.
                       
Hence, EMP_NO is a candidate key of EMPLOYEE(EMP_NO, DEPT_NO, MANAGER_NO).

On the other hand, DEPT_NO is not a candidate key since we do not have DEPT_NO -> EMP_NO.

Furthermore, there are four superkeys:

1. EMP_NO
2. EMP_NO, DEPT_NO
3. EMP_NO, MANAGER_NO
4. EMP_NO, DEPT_NO, MANAGER_NO

Closure of Attributes

• Given a set of FD F, the closure of a set of attributes X, denoted as X+, is the set of all attributes functionally determined by X using Armstrong's axioms on F.

Example

Consider R(A,B,C,D) with

F = {B->A, A->C, AB->D, D->AC}

A+ = AC
B+ = ABCD
C+ = C
D+ = ACD

Thus, B is a candidate key (CK).

No proper superset of B is a candidate key (since it will not be minimal).

Remaining non-empty subset of ABCD to check:

AC+ = AC
AD+ = ACD
CD+ = ACD
ACD+ = ACD

Thus, B is the only CK.

Example (A more degenerate case)

Consider:

F = {A-> B, BC -> DA, BD -> C, E-> A, AC -> DE}

We have

A+ = AB
B+ = B
C+ = C
D+ = D
E+ = EAB
(AB)+ = AB
(AC)+ = ABCDE
(AD+ = ABCDE
(AE)+ = ABE
(BC)+ = ABCDE
(BD)+ = ABCDE
(BE)+ = ABE
(CD)+ = CD
(CE)+ = ABCDE
(DE)+ = ABCDE
(ABC)+ = ABCDE
...
(ABE)+ = ABE
...

• There are thus six candidate keys: AC, AD, BC, BD, CE and DE. Also, all attributes are prime.
• This is a theoretical example not likely to appear in the real world, especially if you have performed a good data modeling job.
• The closure of attributes can be used for other purposes, such as checking validity of FD, computing closure of a set of functional dependencies, checking equivalence of two set of FDs, etc.

Algorithm for finding X+ for a set of FDs F.

X+ <- X
while (there exists a FD P -> Q such that P is a subset of X+ and there are attributes K in Q that is not in X+) {
   X+ <- X+ U Q
}

Examples:

(1) Relation R(A,B,C,D,E) has exactly four superkeys. Can you deduce from this statement the number of candidate keys? If yes, how many CKs are there? Justify your answer.

Solution:

No. If ABC is the only CK of R, then there are four superkeys: ABC, ABCD, ABCE and ABCDE. On the other hand, if there are three CKs: ABCD, ABCE and ABDE, there are also four superkeys: ABCD, ABCE, ABDE and ABCDE.

(2) A relation R of four attributes has two candidate keys, what are the maximum and minimum numbers of superkeys R may have?

Solution:

Minimum: 3; e.g. when the candidate keys are ABC and ABD.
Maximum: 12; e.g. when the candidate keys are A and B.

(3) Consider the following valid instance of a relation R(A,B,C). Can you deduct from it all candidate keys of R? If yes, what are the candidate keys? If not, why?

Solution:

Yes, there is only one candidate key: ABC. This is because for all proper subsets of ABC, there are two or more tuples with the same values and thus no proper subsets of ABC can be a candidate key. This leaves only ABC.

Closure of a set of functional dependencies

• The closure of a set of FD, F, is denoted by F+ and is the set of all FDs that are logically implied by F.

Consider F = {A->B, B->C}

F+ = {
A->{}, A->A, A->B, A->C, A-> AB, A-> AC, A-> BC, A->ABC,
B->{}, B->B, B->C, B->BC,
C->{}, C->C,
AB->{}, AB->A, AB->B, AB->C, AB->AB, AB->AC, AB->BC, AB->ABC,
AC->{}, AC->A, AC->B, AC->C, AC->AB, AC->AB, AC->BC, AC->ABC,
BC->{}, BC->B, BC->C, BC->BC,
ABC->{}, ABC->A, ABC->B, ABC->C, ABC-> AB, ABC-> AC, ABC-> BC, ABC->ABC }

Note that

• Many FDs in F+ are trivial. Examples: A->{}, ABC->AC, etc.
• FD+ itself is not very interesting.

Equivalence and cover

• Two sets of FD, F and G are equivalent, if F+ = G+. They are covers of each other.
• Thus, covers can be used to support the concepts of equivalence. If F and G are covers of each other, they represent the same set of application requirements and assumptions.

Canonical and Minimal Covers

• The attribute A in the FD P-> Q is extraneous for a set of FDs F if F - {P-> Q} U {P-A -> Q} is equivalent to F.
• Thus, the attribute A is not actually needed in P to determine Q. It is extraneous.

Example

Consider the F = {A->B, AB->C}.

B is extraneous since for G = {A->B, A->C}, and F+ = G+.

• A FD f in F is redundant if (F - f)+ = F+.

Example

In F = {A->B, AB->C, B->C},

AB->C is redundant since for

G = {A->B, B->C}, AB+ = ABC.

Alternatively,

G |- AB-> C.

• A canonical cover, G, of F satisfies the following conditions:
  1. G is a cover of F; G+ = F+.
  2. There is no redundant FD in G.
  3. There is no extraneous attribute in G.
  4. The left hand side of every FD in G is unique.
• A minimal cover, G, of F satisfies the following conditions:
  1. G is a cover of F; G+ = F+.
  2. There is no redundant FD in G.
  3. There is no extraneous attribute in G.
  4. The right hand side of every FD in G contains only a single attribute

In F = {A->B, AB->C, B->C, A->D},

G1 = {A->B, B->C, A->D} is a minimal cover.

G2 = {A->BD, B->C} is a canonical cover.

• The minimal covers and canonical covers are simplified equivalent versions of a set of FDs,
• They are useful in understanding the FD and for proper decompositions to remove unnecessary redundancy.

Exercise:

Consider F: {A->C, BCD->A, C->E, CD-> A, AB->C}

1. Does F imply BD-> A (i.e. F |- BD -> A)?
2. F |- AE -> B ?
3. Give a canonical cover for F.
4. Show all candidate keys.

Example:

Find a canonical cover for F = {BC->AE, AD->BCE, A->E, AE->D, BCD->F, AB->C}

Solution:

Basically, we iteratively remove all extraneous attributes and redundant function dependencies.

We use decomposition rule to ensure the RHS to contain only a single attribute so we can work on them one by one. F becomes:

(1) BC -> A
(2) BC -> E
(3) AD -> B
(4) AD -> C
(5) AD -> E
(6) A -> E
(7) AE -> D
(8) BCD -> F
(9) AB -> C

To investigate whether B or C is extraneous in BC -> A, we note that in F:

B+ = B
C+ = C

This means B alone and C alone cannot determine A and neither of them is extraneous.

On the other hand, in F:

A+ = ABCDEF

That means A alone can determine all other attributes. Any other attributes in the LHS with A in a FD are thus extraneous, we thus have the following by removing D in [2], [3] and [4], and B in [9].

(1) BC -> A
(2) BC -> E
(3) A -> B
(4) A -> C
(5) A -> E
(6) A -> E
(7) A -> D
(8) BCD -> F
(9) A -> C

Removing identical FD, we have F:

(1) BC -> A
(2) BC -> E
(3) A -> B
(4) A -> C
(5) A -> E
(6) A -> D
(7) BCD -> F

For (7), since B+ = B, C+ = C and D+ = D. However, BC+ = ABCDEF, and thus D is extraneous. Thus, we now have:

(1) BC -> A
(2) BC -> E
(3) A -> B
(4) A -> C
(5) A -> E
(6) A -> D
(7) BC -> F

To remove redundant FD, we consider whether we can deduce a FD when it is removed.

For (1) BC -> A, removing it result in F':

(1) BC -> E
(2) A -> B
(3) A -> C
(4) A -> E
(5) A -> D
(6) BC -> F

In F': we have

BC+ = BCE, which does not include A. Thus, F' does not imply BC -> A and it is not redundant.

For (2) BC -> E, removing it and we have F':

(1) BC -> A
(2) A -> B
(3) A -> C
(4) A -> E
(5) A -> D
(6) BC -> F

In F', we have BC+ = ABCDEF. Thus, F' |= BC -> E and BC -> E is redundant. Remove it and we have:

(1) BC -> A
(2) A -> B
(3) A -> C
(4) A -> E
(5) A -> D
(6) BC -> F

Using this method, we can find that there are no more redundant FD.

Finally, we use the union rule to merge FD with the same LHS and get the canonical cover:

{BC -> AF, A-> BCDE}

Note that the canonical cover is not unique. Another canonical cover is:

{BC -> A, A-> BCDEF}

Exercise:

Consider F: {AB->CE, BC->D, D->BC, C->E, A->C, A->E}

Find:

• all candidate keys.
• a canonical cover of F.

Exercise:

Can there be more than one canonical covers for a set of FDs?

Finding Candidate keys

• It is necessary to find all candidate keys to conduct normalization analysis.
• In general, if R has n attributes, there are 2^n-1 non-empty subsets of R which are potential candidate keys.

Example

For R(A,B,C), need to check A, B, C, AB, AC, BC and ABC for candidate keys.

Thus, the problem is O(e^N).

To find all candidate keys of R with a set of FD, F:

1. Find the canonical cover, FC, first. This simplifies F.
2. Use heuristics to cut down the number of sets of attributes to check.
3. Use classification of attributes into three groups
   1. L (NR): If X does not appear in the RHS of any f in FC, every candidate key must include X.
   2. R: If X appears in the RHS of a fd in FC but does not appear in the LHS of any f in FC, then x is not a part of any candidate key.
   3. M: If X appears in LHS in some FD and in RHS in some other FD, then X can potentially be in a CK.
4. If X is found to be a CK, then any proper superset of X is not a CK and needs not be checked.

Example:

For R(A,B,C,D,E,F) with F = {BC->AE, AD->BC, A->E, AE->D, BCD->F, AB->C}, find all candidate keys.

Solution:

There are 31 candidates for the CK. These are the non-empty subsets of ABCDEF.

In F, we have:

A+ = ABCDEF
B+ = B
C+ = C
D+ = D
E+ = E
F+ = F

Thus, A is a CK. As a result, it is not necessary to check all proper supersets of A as they will not be minimal.

Continue to check sets of two attributes:

BC+ = ABCDEF
BD+ = BD
BE+ = BE
BF+ = BF
CD+ = CD
CE+ = CE
CF+ = CF
DE+ = DE
DF+ = DF
EF+ = EF

Thus, BC is another CK.

Further checking on sets of three attributes:

BDE+ = BDE
BDF+ = BDF
BEF+ = BEF
CDE+ = CDE
CDF+ = CDF
CEF+ = CEF
DEF+ = DEF

Note that it is not necessary to check proper subsets of A or BC.

We check sets of four attributes:

BDEF+ = BDEF
CDEF+ = CDEF

We have exhausted all candidates and there are only two CKs: A and BC.

Alternatively, we can use the canonical cover:

{BC -> A, A-> BCDEF}

It can be seen that A and BC are CK. Furthermore, DEF appears in RHS of some FD but not the LHS of any FD, thus any of them can not appear in any CK.

2. Normal Forms using Functional Dependencies

First Normal Form

• A relation is in 1NF if all attribute values are atomic: no repeating group, no composite attributes.
• Formally, a relation may only has atomic attributes.  Thus, all relations satisfy 1NF.
• In practice, DBMS may allow data types with composite values, e.g. set, list, etc.

Consider the following table with 3 records.  It is not in 1 NF.

The corresponding relation with 6 tuples is in 1 NF:

• Why atomic? relational theory and operations treat attributes as atomic.
• Relations satisfying only 1NF has unnecessary redundancy and anomalies.

Example

Consider the tuple (Empid: 12345, OSSkills: "Windows, Linux, Solaris").

• It will be difficult to identify all employees with Linux skills.
• OSSkills will not be effective as the common attribute of joins.
• Data entry problems and issues, e.g. Linux linux, linx, etc., may further degrade data quality and introduce inconsistency.

Second Normal Form

• A relation R is in 2NF if
  1. R is in 1NF, and
  2. all non-prime attributes are fully dependent on the candidate keys. (Any non-prime attribute is not determined by a part of any candidate key.)
• A prime attribute appears in one or more candidate key. Otherwise, it is a non-prime attribute.
• Note that a relation may have many candidate keys.
• A non-prime attribute does not appear in any candidate key.
• There is no partial dependency in 2NF.
• 2NF: If X -> A, A is a non-prime attribute, and X is a subset of a candidate key K, then X = K.

Example

The following relation is not in 2NF.  (Assume the number of credits of a given course does not change).  Note the redundancy and anomalies.

Enroll(Course, Credit, Student, Grade)

That is, we assume the following FDs.

1. Course -> Credit
2. Course, Student -> Grade

Thus,

1. Course, Student is the only candidate key.
2. Prime attributes: Course, Student
3. Non-prime attribute: Credit, Grade.
4. FD (1) is a violation of 2NF.

To convert to 2NF, decompose Enroll into

• Enroll(Course, Student, Grade)
• Class(Course, Credit)

Third Normal Form

• (Old definition) A relation R is in 3NF if
  1. R is in 2NF, and
  2. There is no transitive dependency of nonprime attributes on the keys.
• (New definition) : a relation R is said to be in the third normal form if for every non-trivial functional dependency X -> A,
  1. X is a superkey, or
  2. A is a prime (key) attribute.

Example

• The following relation may be in 2NF, but is not in 3NF.

• If we assume the following canonical set of FDs:
  1. EMP_NO -> NAME, DEPT_NO
  2. DEPT_ NO -> MANAGER_NO
• then
  • There is only one candidate key: EMP_NO
  • Prime attributes: EMP_NO
  • Non-prime attributes: NAME, DEPT_NO, MANAGER_NO.
  • The relation is in 2NF.
  • The relation is not in 3NF because of the transitive FD: EMP_NO -> MANAGER_NO via the non-prime attribute DEPT_NO.

Using the new definition.

The functional dependency  DEPT_NO -> MANAGER_NO is

(1) non-trivial,
(2) DEPT_NO is not a superkey, and
(3) MANAGER_NO is not a prime attribute.

Thus, it violates the 3NF.

Example 

For the relation R(CITY, ZIP, STREET)
       
Using the code for the postal office, we may have:

CITY STREET -> ZIP, and ZIP -> CITY.

Hence, there are two candidate keys:

1. CITY STREET, and
2. ZIP STREET

Hence,

Prime attributes: STREET, CITY, ZIP

R is in the 3NF because

• For the non-trivial FD: CITY STREET -> ZIP, CITY STREET is a superkey.
• For the non-trivial FD: ZIP -> CITY, CITY is a prime attribute.

Note that a relation such as EMPLOYEE(EMP_ID, EMP_NAME, Street, City, Zip, State) is not in 3NF.

This is a classical example you can find in many database textbooks. The FDs are actually not valid in the United States. See, for example: Why all 5-digit ZIP Code™ lists are obsolete.

Note:

• 3NF cannot eliminate all redundancy due to functional dependencies.

Example

Consider the relation

S(SNUM, PNUM, SNAME, QUANTITY) with the following assumptions:

1. SNUM is unique for every supplier.
2. SNAME is unique for every supplier.
3. QUANTITY is the accumulated quantities of a part supplied by a supplier.
4. A supplier can supply more than one part.
5. A part can be supplied by more than one supplier.

We have the following non-trivial functional dependencies:

1. SNUM -> SNAME
2. SNAME -> SNUM
3. SNUM PNUM -> QUANTITY
4. SNAME PNUM -> QUANTITY

Note that SNUM and SNAME are equivalent.

The candidate keys are:

1. SNUM PNUM
2. SNAME PNUM

Prime attributes: SNUM, PNUM, SNAME

Non-prime attribute: QUANTITY.

S is in 3NF because

• For the non-trivial FDs (1) and (2), the right hand sides are prime attributes (SNAME and SNUM).
• For the functional dependencies (3) and (4), the left hand sides are superkeys.

The relation is in 3NF. However, there are unnecessary redundancy.

• 3NF does not eliminate all redundancy due to functional dependencies.

BCNF (Boyce-Codd Normal Form)

• A relation R is said to be in BCNF if for every non-trivial functional dependency X -> A in R, X is a superkey.

Example                  

EMPLOYEE(EMP_NO, NAME, DEPT_NO, MANAGER_NO) with

EMP_NO -> NAME
EMP_NO -> DEPT_NO
DEPT_NO -> MANAGER_NO

is not in BCNF.

The functional dependency DEPT_NO -> MANAGER_NO is

(1)  non-trivial, and
(2)  DEPT_NO is not a superkey.

• Recall that this is the example we used for illustrating bad design.
• This is also not in 3NF.

We can decompose

EMPLOYEE(EMP_NO, NAME, DEPT_NO, MANAGER_NO) into

EMP(EMP_NO, NAME, DEPT_NO) with

EMP_NO -> NAME, DEPT_NO

and

DEPARTMENT(DEPT_NO, MANAGER_NO) with

DEPT_NO -> MANAGER_NO

Both relations are in BCNF since

• EMP_NO is a superkey of the relation EMP.
• DEPT_NO is a superkey of the relation DEPARTMENT.

Recall that these are the good relations without the anomalies in the previous example.

Example    

Consider again the relation

S(SNUM, PNUM, SNAME, QUANTITY) with the following non-trivial functional dependencies:

1. SNUM -> SNAME
2. SNAME -> SNUM
3. SNUM PNUM -> QUANTITY
4. SNAME PNUM -> QUANTITY

Note that SNUM and SNAME are equivalent.

The candidate keys are:

1. SNUM PNUM
2. SNAME PNUM

Prime attributes: SNUM, PNUM, SNAME

Non-prime attribute: QUANTITY.

S is not in BCNF because, for example, the functional dependency 

SNUM -> SNAME is

• non-trivial, and
• SNUM is not a superkey.

To deal with it, we can decompose S(SNUM, PNUM, SNAME, QUANTITY) into

(1) SUPPLIER(SNUM, SNAME) with  

SNUM -> SNAME
SNAME -> SNUM

with two candidate keys:

1. SNUM
2. SNAME

(2) SUPPLY(SNUM, PNUM, QUANTITY)  with 

SNUM, PNUM -> QUANTITY.

Both are in BCNF.

Example: 

Consider the relation R(A, B, C, D) with

A -> B,  B -> C, C -> A and C -> D.

There are three candidate keys:

1. A
2. B 
3. C

Since every left hand side of any non-trivial functional dependency is a superkey,  R is in BCNF.

Motivation of BCNF

• The purpose of BCNF is to eliminate any unnecessary redundancy that functional dependencies can create in a relation.
  • In a BCNF relation, no value can be predicted from any other attributes besides the keys, using only functional dependencies.
  • This is because in a BCNF relation, using functional dependencies only,
    • any value can only be determined by a superkey,
    • but the superkey is unique.
  • However, there are other type of dependencies.
  • Therefore, there are higher normal forms.

Consider the relation R(CITY, ZIP, STREET) again
       
Using the code for the postal office, we have

CITY STREET -> ZIP, and ZIP -> CITY.

Hence, there are two candidate keys:

1. CITY STREET, and
2. ZIP STREET

Therefore, R is not in BCNF since in ZIP -> CITY, ZIP is not a superkey.

However, if we decompose R into two relations, each with two attributes, then the functional dependency

CITY STREET -> ZIP is lost (i.e. cannot be enforced within a single relation)

Therefore, we better leave the relation alone.

• Sometimes it is not desirable to achieve BCNF ==> need a less strict normal form (3NF).

Normalization Theory Using Functional Dependencies

• To use the theory on functional dependency:
  1. For a relation of a set of attributes, we analyze the assumptions of the applications.
  2. From the assumptions, we obtain the functional dependencies.
  3. Simplify the FD by finding a canonical cover.
  4. We determine the candidate keys and prime attributes.
  5. If the relation is not in BCNF, we perform decomposition.
  6. If BCNF cannot be satisfied, we aim for 3NF.

Example:

[1] Consider R(A, B, C, D, E) with

F = {BC->D, A->C, C->BD, AD->E}

(a) What are A+, B+, C+, D+ and E+?

(b) What are the candidate keys? Why?

(c) Show all prime attributes and non-prime attributes?

(d) Give a canonical cover of F?

(e) What is the highest normal form (up to BCNF) of R? Why?

(f) If R is not in BCNF, can you provide a lossless FD preserving decompositions of R into BCNF relations?

Solution:

(a) A+=ABCDE, B+=B, C+= BCD, D+=D, E+=E

(b) The candidate key is A

(c) Prime: A, non-prime: BCDE

(d) {A->CE, C->BD}

(e) 2NF since C->D violates 3NF: D is non-prime and C is not a Sk.

(f) Yes, the decomposition to R(A,C,E) {A->CE} and R2(B,C,D) {C->BD} satisfy the requirement.

[2] Consider R(A, B, C, D, E, F) with

F = {CD->E, A->BD, AC->EF. C->BD, F->E, EF->D}

(a) What are A+, B+, C+, D+, E+, F+?

(b) What are the candidate keys? Why?

(c) Show all prime attributes and non-prime attributes?

(d) Give a canonical cover of F?

(e) What is the highest normal form (up to BCNF) of R? Why?

(f) If R is not in BCNF, can you provide a lossless FD preserving decompositions of R into BCNF relations?

Solution:

(a) A+=ABD, B+=B, C+=BCDE, D+=D, E+=E, F+=DEF

(b) The candidate key is AC

(c) Prime: AC; non-prime: BDEF

(d) {A->BD, AC->F, C->BDE, F->DE}

(e) 1NF. A->B violates 2NF as A is a part of the a CK and B is non-prime.

(f) Yes, the following decomposition satisfies the requirement:

R1(B,C,D,E) {C->BDE}
R2(D,E,F) {F->DE}
R3(A,B,D) {A->BD}
R4(A,C,F) {AC->F}

3. Decomposition

• Decomposition is a major tool for constructing relations satisfying high enough normal forms.
• Decomposition should be disciplined:
  • More relations may be less efficient in storage.
  • More relations may be less efficient in executing queries.
  • Some decompositions are harmful:
    1. Lossy decompositions.
    2. Decompositions that do not preserve dependencies.
• Hence, it is important to have lossless dependency-preserving decomposition.

Lossy Decomposition

Example:

Consider the relation EMP(EMP_NO, DEPT_NO, MANAGER_NO) with 

EMP_NO ->  DEPT_NO
DEPT_NO ->  MANAGER_NO

Note that we do not have MANAGER_NO -> DEPT_NO in this example, since a manager can manage more than one departments under the assumptions made for this example.

          
The relation is not in BCNF because of the FD

DEPT_NO -> MANAGER_NO

Suppose we decompose the relation into

EMP1(EMP_NO, MANAGER_NO)
DEPT(DEPT_NO, MANAGER_NO)

The common attribute is MANAGER_NO. They are obtained by projections from EMP:

EMP1:                     

DEPT:

If we do not lose any information by the decomposition, we should get the original relation from the natural join.

However,  EMP1 |x| DEPT is     

         
This is not the same as the original relation EMP. Spurious tuples were incorrectly created.

Hence, the decomposition of EMP(EMP_NO, DEPT_NO, MANAGER_NO) into
 
EMP1(EMP_NO, MANAGER_NO) and
DEPT(DEPT_NO, MANAGER_NO)

is lossy.  It is not a good decomposition.

Example: A lossy decomposition using the supply database:

supply(snum, pnum, quantity) {snum, pnum} -> quantity

decomposed into:

s1(snum, quantity)
s2(pnum, quantity)

select *
from (select snum, quantity from supply) as s1
    natural join
    (select snum, pnum from supply) as s2;
 

Lossless Decomposition

Example:

Consider now the following decomposition of EMP(EMP_NO, DEPT_NO, MANAGER_NO):

EMP2(EMP_NO, DEPT_NO)  and
EMP3(EMP_NO, MANAGER_NO)

The common attribute is EMP_NO. We have EMP2 and EMP3:

EMP2:                     

EMP3:

Hence, EMP2 |x| EMP3:

This is exactly the same as the original relation EMP.  Therefore, the decomposition does not lose any information.  It is a lossless decomposition.

Theory of Lossless Decomposition

Example:

Why is the decomposition of EMP(EMP_NO, DEPT_NO, MANAGER_NO) into

(1) EMP1(EMP_NO, MANAGER_NO) and DEPT(DEPT_NO, MANAGER_NO) lossy, and

(2) EMP2(EMP_NO, DEPT_NO) and EMP3(EMP_NO, MANAGER_NO) lossless?

Theorem: Suppose R(X, Y, Z) is decomposed into R1(X, Y) and R2(X, Z).  X is the set of common attributes in R1 and R2.  The decomposition is lossless if and only if

(a) X -> Y, or
(b) X -> Z.

Example:

In case (1), X is MANAGER_NO, Y is EMP_NO, Z is DEPT_NO.

Neither condition (a) not (b) is satisfied.  Hence, (1) is lossy.

In case (2), X is EMP_NO, Y is DEPT_NO, Z is MANAGER_NO.

Both conditions (a) and (b) are satisfied.  Hence, (2) is lossless.

• For decompositions into more than two relations, use the chase matrix algorithm (EN Algorithm 16.3).

Example:

Consider R(A,B,C,D,E) with {A->BC, CD -> E, BA -> C, D->B}.

It is decomposed into R1(A,B), R2(A,C), R3(C,D,E) and R4(B,E).

Step 1. Create a table of 5 columns (number of columns) and 4 rows (number of relations). Populate it with b(i,j).

Step 2. For each relation Ri, set all attribute Aj that appears in Ri from b(i,j) to a(j).

Step 3. While changes can be made with a FD X-> Y, with two rows in the table having the common X values in the following manner:

for every attribute W in Y:

• If one cell is an a and the other cell is an b, change the b to the a.
• If both cells are b's, change them to the same b.

Note that a specific FD can be applied more than once.

Applying A-> BC:

Applying CD -> E: no change since no two rows has the same values in CD.

Applying BA -> C: no change since R1 and R2 already have the same a's value: a(3).

Applying D->B: no change. No D's have the same value.

In fact, no FD can be applied again to change the matrix.

Step 4. If there is a row with only a's, the decomposition is lossless. Otherwise, there is no row with only a's and the decomposition is lossy.

Since there is no row with only a's, the decomposition is lossy.

Example:

Now suppose that C->DE is also in the FDs. That is, we have:

R(A,B,C,D,E) with {A->BC, CD -> E, BA -> C, D->B, C->DE}.

We will now have one more step.

Applying C->DE:

Now we have two rows with only a's and thus the decomposition is lossless.

Dependency-Preserving Decomposition

Example:                        

For the relation EMP(EMP_NO,DEPT_NO,MANAGER_NO) with 

EMP_NO ->  DEPT_NO
DEPT_NO ->  MANAGER_NO,

The decomposition of EMP into

EMP2(EMP_NO, DEPT_NO)  and
EMP3(EMP_NO, MANAGER_NO)

is lossless but does not preserve dependencies:

the FD  DEPT_NO -> MANAGER_NO

cannot be enforced by any relation after the decomposition. No relation contains both attributes.

For example, if we add the information EMP 23000 work in the ACCT department under manager 97000 and are not careful, we may have:

 EMP2:                     

EMP3:

The FD  DEPT_NO ->  MANAGER_NO is violated.

Thus, for the relation EMP(EMP_NO,DEPT_NO,MANAGER_NO) with 

EMP_NO ->  DEPT_NO
DEPT_NO ->  MANAGER_NO,

the best decomposition is into

EMP1(EMP_NO, DEPT_NO)  and
DEPT(DEPT_NO, MANAGER_NO)

It is easy to show that, the decomposition is lossless, preserves dependencies, and that EMP1 and DEPT are both in BCNF.

• It is possible to decompose a relation such that
  1. all member relations are in 3NF,
  2. the decomposition is lossless, and
  3. all FDs are preserved.
• It is also possible to decompose a relation such that
  1. all member relations are in BCNF, and
  2. the decomposition is lossless, but
  3. not all FDs may be preserved.

Algorithm for decomposition in 3NF relations

• See Algorithm 16.6 of EN: lossless FD preserving decomposition into relations in 3NF.

Example:

Consider R(A,B,C,D,E) with F = {A->BC, CD -> E, BA -> C, D->B}.

Step 1. Find a canonical cover (as opposed to a minimal cover in EN) G for F.

The FD BA->C is redundant.

G = {A->BC, CD -> E, D->B} is a canonical cover.

Step 2. For every FD X->Y in G, create a relation with the schema XY and add it to the result D.

Relations created:

R1(A,B,C) with A->BC
R2(C,D,E) with CD->E
R3(B,D) with D->B

This step ensures that all FDs are preserved.

Step 3. If no relation in D contains a candidate key of R, create a new relation with a candidate key of R being the schema and add it to the result D.

There is only one candidate key of R: AD. Since none of R1, R2 and R3 contains both A and D, create the relation

R4(A,D) with no FD

Step 4. Simplify D by removing relations that are redundant (i.e. that its schema is a subset of the schema of another relation).

No action as there is no redundant relation.

The result relations are all in BCNF.

Example:

Consider R(A,B,C,D,E) with {A->BCD, BC->D, D->C}

Using the algorithm,

(1) Canonical cover: {A->BC, BC->D, D->C}

(2) The following relations are created:

R1(A,B,C) with {A-> BC},
R2(B,C,D) with {BC->D, D->C},
R3(C,D) with {D->C}

(3) There is only one candidate key AE. Since it is not in any of R1, R2 or R3, R4 is created.

R4(A,E)

(4) R3(C,D) is removed as redundant since it is a subset of R2.

As the result, we have:

R1(A,B,C) with {A-> BC}, in BCNF
R2(B,C,D) with {BC->D, D->C}, in 3NF but not in BCNF
R4(A,E) with {}, in BCNF

• Algorithm 16.5 of EN is an algorithm for lossless decomposition into BCNF but FD may not be preserved.
• Sometimes, it is not possible to decompose a relation into two relations losslessly and preserve all FD, just to achieve BCNF.

Example:

Consider the relation R(A, B, C) with A -> B and C -> B.

R is not in 2NF.  It is not possible to decompose R into two relations losslessly while preserving all functional dependencies.

However, it is possible to decompose into three relations losslessly and with all functional dependencies preserved:

R1(A, B),
R2(B, C) and
R3(A, C).

4. Higher Normal Forms (Additional Materials only; will not be in the exam.)

Multivalued Dependencies

• BCNF guarantees that there is no anomaly related to functional dependencies.  However, there are other forms of redundancy.

Example 1:

Consider the following instance of the relation R(Emp_No, Dept_NO, Skill):

There are no non-trivial functional dependencies.  R is in BCNF.

If the department an employee is working on is independent of the skill that he has, there is redundancy. For example, the fact that employee 100 has the skill PHP is stored twice.

• Let A, B, C be the three distinct sets of attributes in R(A,B,C).  There is a multivalued dependency (MVD) of B on A, A ->-> B, if the set of B values associated with a given A value is independent of the value of C.  A is said to multidetermine B.

Example 2:

Under the assumption of the previous example, we have

Emp_No ->-> Dept_NO and
Emp_No ->-> Skill.

Suppose we have the following relation instance under a different set of assumptions:

Without knowing the underlying assumptions, this instance of the relation implies that the multivalued dependency Emp_No ->-> Dept_NO does not hold true since

• for a given Emp_No (e.g. 100), skill is not independent of Dept_NO. 
  • When Dept_NO is D101, skills are {PHP, MySQL} and
  • when Dept_NO is D102, skills are {PHP}.

Note:

• A valid instance of a relation cannot be used to prove that a FD is valid for a relation schema (since a FD is the result of application require mens and must be held for every instance, not just one.)
• A valid instance of relation may be used to prove that a FD is not valid for a relation schema: by providing two tuples that violate the FD.

A more precise definition of MVD: 

• Let A, B, C be the three distinct sets of attributes in  R(A,B,C).  A ->-> B is true iff the following condition is true.  For every two tuples t1 and t2 in R such that t1(A) = t2(A), then there exist t3 and t4 in R such that t3(A) = t4(A) = t1(A), t3(B) = t1(B), t4(B) = t2(B), t3(C) = t2(C) and t4(C) = t1(C).

Example 3:

Consider Example 1 again. In the data modeling, there may be two classes with a many-to-many association.

• Employee: with key EMP_NO and a multi-valued attribute SKILL.
• Department: with key DEPT_NO

If one follows the data modeling and the mapping to relation guideline, two relations will be created (instead of one).

This shows the importance of good data modeling.

• Intuitive explanation: If in R(X,Y,Z), X->->Y and X->->Z, then there are independent many-to-many relationships between X and Y, and X and Z.

Some Properties Of Multivalued Dependencies

• For R(A,B,C), A ->-> B => A ->-> C.
• If A -> B, then A ->-> B.
• A ->-> B and V is a subset of W => AW ->-> VB
• Note that, in general, X ->-> Y and Y ->-> Z do not imply that X ->-> Z. There is no equivalent transitivity rule in MVD.

Fourth Normal Form

• A relation R is in the fourth normal form (4NF) if
  • (1) R is in BCNF and
  • (2) all MVD are either trivial or can be derived from a FD.
• 4NF => BCNF.
• A relation in 4NF does not have redundancy due to FD or MVD.

Example 4:

The relation R(Emp_No, Dept_NO, Skill) in Example 1 is in BCNF but not in 4NF.  It should be decomposed into:

R1(Emp_No, Dept_NO) and
R2(Emp_No, Skill).

Note that these two relations are created with good data modeling and mapping to relations.

• Decomposition to overcome 4NF: if R(A,B,C) is in BCNF and A ->-> B, then decompose the relation into
  • R1(A,B) and
  • R2(A,C).

Embedded Multivalued Dependencies

• FD and MVD are not the only form of data dependency.

Example 5:

Consider the following relation R(Proj_No, Emp_No, Skill):

For this application, each project (Proj_No) has a number of employees (Emp_No) and each project requires a list of skills (Skill).  We have:

Proj_No ->-> Emp_No
Proj_No ->-> Skill.

An employee provides some skills that a project needs.  If an employee has a skill that is not needed by the project (e.g. employee E1 may have the skill of 'Internet'), the skill is not stored in the tuples of project P1, which does not require the skill 'Internet'.

Note that Emp_No ->-> Skill does not hold in R.

R is not in 4NF.

We can decompose the relation into two relations:

R1(Proj_No, Emp_No) and
R2(Proj_No, Skill)

R1:                        

R2:

Both R1 and R2 are now in 4NF.

However, if skill is a multi-valued attribute of an employee, then we should have

Emp_No ->-> Skill

It does not show up in R because it is embedded. If we project R to remove PROJ_NO, this relationship appears.

These embedded MVD's are not enforced by the relations R1 and R2.

These MVD's only display themselves after projection.

We may have the following three classes with three many-to-many associations between each pair of them:

• Project: with key PROJ_NO
• Employee: with key EMP_NO
• Skill: with key SKILL

Decomposition of R into R1 and R2 will lose this embedded multivalued dependency. 

• Let X, Y and Z be subsets of attributes of the relation scheme r.  A relation R over the scheme r satisfies the embedded multivalued dependency X ->-> Y | Z if X ->-> Y in the relation π(X U Y U Z)(R).  X, Y and Z need not be disjoint.

Example 6:

There are (trivial) embedded MVD's Emp_No ->-> Skill | φ and Emp_No ->-> Proj_No | φ in R of the previous example.

To remedy the problem, decompose the relation R into:

R1(Proj_No, Emp_No)
R2(Proj_No, Skill) and
R3(Emp_No, Skill).

All relations are in 4NF (5NF too) and the embedded MVD are not lost.

• The previous examples show that it may not be possible to decompose a relation R into two relations and preserve all dependencies but it may be possible to decompose R into more than two relations and preserve all dependencies.
• Note that there is no embedded FD, only embedded MVD.

Example 7:

Consider an application with four classes A, B, C and D with primary keys A_ID, B_ID, C_ID and D_ID.  There are many to many binary associations between A and C as well as B and C.  Furthermore, there is a ternary association between A, B and D.

If somebody has not performed a good data modeling, it is possible to come up with a relation R(A_ID, B_ID, C_ID, D_ID).

However, the MVD C_ID ->-> A_ID (or C_ID ->-> B_ID) is not true in R because of the additional attribute D_ID.  If the attribute D_ID is removed by projection, then the independence between A_ID and B_ID for a given value of C_ID will show up.  Hence, there is a embedded MVD C_ID ->-> A_ID | B_ID in R.

Join Dependencies

• Given a relation scheme r and a projection {R1, R2, ..., Rn} of r.  A relation R on r satisfies the join dependency (JD) *[R1, R2, ..., Rn] iff πR1(R) |x| πR2(R) ... |x| πRn(R) = R.
• In other word, there is a lossless decomposition of R into R1, R2, ..., Rn.
• A JD is trivial if one of Ri is R.

Example 8:

In Example 5 with embedded MVD, there is a non-trivial JD of R(Proj_No, Emp_No, Skill):

{{Proj_No, Emp_No}, {Proj_No, Skill}, {Emp_No, Skill}}

Fifth (Project-Join) Normal Form

• A relation R satisfies the fifth normal form (5NF) or Project-Join Normal Form (PJNF) if for every non-trivial join dependency *[R1, R2, ..., Rn] of R, Ri is a superkey of the original relation.
  

Example 9:

The relation R(Proj_No, Emp_No, Skill) of Example 3 does not satisfy 5NF.

• 5NF => 4NF but not the other way around.

Domain-Key Normal Form

• Domain Constraint (DC), In(Ai, Di): if the value of the attribute Ai of the relation R must be in the domain Di.
• Key Constraint (KC):  KEY(K): for the key K of a relation R, no two tuples of the relation R has the same K value.
• General Constraint (GC): A general constraint is a predicate statement such that every tuple in the relation must satisfy the GC in order for the tuple to be valid.
• A relation is in domain key normal form (DKNF) if all its GC's are natural consequence of its DC and KC.

Example 10:

Consider the relation scheme Enrollment(Course_No, Student_No, Grade)

The key is {Course_No, Student_No}.

KC: Course_No, Student_No -> Grade.

DC:

Domain(Course_No): 001..999 (i.e. In(Course_No, {001..999}
Domain(Grade): {A,B,C,D,F,I,P}
Domain(Student_No): string(1..10)

GC:

One of the GC may be:

if Course_No mod 10 >= 8 then
   (Grade ε {'P', 'F', 'I'}
else
   (Grade ε {'A', 'B', 'C', 'D', 'F', 'I'};
end if;

The relation Enrollment is in PJNF.

However, the relation Enrollment is not in DKNF since the GC is not a natural consequence of the KC and DC.

To solve the problem, we may make the following decomposition:

Pass_Fail_Course_Enrollment(Course_No, Student_No, Grade) with

DC: 

Domain(Course_No): {I | I ε 1..999 and I mod 10 >= 8}
Domain(Grade): {'F', 'I', 'P'}

Regular_Course_Enrollment(Course_No, Student_No, Grade) with

DC:              

Domain(Course_No): {I | I ε 1..999 and I mod 10 < 8}
Domain(Grade): {'A', 'B', 'C', 'D', 'F', 'I'}

Both relations are now in DKNF. However, this is usually not done. Instead, stored procedures may be used to enforce the constraint.

Example 11:

A non-trivial FD is a GC.
A FD with the determinant being a key is a KC.

Hence, if a relation satisfies BCNF, then all its FD can be deducted from KC.

If a relation does not satisfy BCNF, then there is a FD with a non-key determinant.  Thus, it will not satisfy DKNF too.

Example 12:                    

A non-trivial MVD is a GC.
There is no way to express a MVD using KC or DC.

Thus, if a relation is not in 4NF, it is also not in DKNF.

• In fact, DKNF => PJNF but not vice versa.
• There exists no simple algorithm that helps in the design of DKNF.
• It is unlikely that applications with complex constraints can be converted to DKNF.
• It is also difficult to infer using MVD, embedded MVD or JD.
• Thus, BCNF and 3NF are usually the highest normal forms for many practical applications.