DCIT24 - Lesson 4 Normalization

functional dependency

A relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table. X → Y left side: determinant right side: dependent.

Non-Trivial Functional Dependency

A → B has a non-trivial functional dependency if B is not a subset of A.

Trivial Functional Dependency

A → B has trivial functional dependency if B is a subset of A

Boyce-Codd Normal Form

BCNF

4NF

Boyce Codd normal form and has no multi-valued dependency. For a dependency A → B, if for a single value of A, multiple values of B exists, then the relation will be a multi-valued dependency.

Lossless Decomposition

If the information is not lost from the relation that is decomposed, then the decomposition will be lossless. guarantees that the join of relations will result in the same relation as it was decomposed if natural joins of all the decomposition give the original relation

Dependency Preserving

It is an important constraint of the database. In the dependency preservation, at least one decomposed table must satisfy every dependency. If a relation R is decomposed into relation R1 and R2, then the dependencies of R either must be a part of R1 or R2 or must be derivable from the combination of functional dependencies of R1 and R2.

Multivalued Dependency

Occurs when two attributes in a table are independent of each other but, both depend on a third attribute. Consists of at least two attributes that are dependent on a third attribute that's why it always requires at least three attributes.

DBMS Normalization

Process of organizing the data in the database. To minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies. Divides the larger table into the smaller table and links them using relationship. The normal form is used to reduce redundancy from the database table.

1. Reflexive Rule (IR1) 2. Augmentation Rule (IR2) 3. Transitive Rule (IR3) 4. Union Rule (IR4) 5. Decomposition Rule (IR5) 6. Pseudo Rule (IR6)

The Functional dependency has 6 types of inference rule:

Inclusion Dependency

They typically show little influence on designing of the database. a statement in which some columns of a relation are contained in other columns.

Armstrong's axioms

basic inference rule

Lossless Decomposition and Dependency Preserving

2 Types of Decomposition

Trivial and non-trivial functional dependency

2 Types of Functional Dependency

1NF, 2NF, 3NF, 4NF, BCNF, 5NF

6 Types of Normal Forms

complete non-trivial

When A intersection B is NULL, then A → B is called as _________.

Relational Decomposition

When a relation in the relational model is not in appropriate normal form then the decomposition of a relation is required. It breaks the table into multiple tables. If the relation has no proper decomposition, then it may lead to problems like loss of information. Used to eliminate some of the problems of bad design like anomalies, inconsistencies, and redundancy.

Inference Rule

a type of assertion, can apply to a set of FD (functional dependency) to derive other FD can derive additional functional dependency from the initial set

Boyce-Codd Normal Form

advance version of 3NF. It is stricter than 3NF. if every functional dependency X → Y, X is the super key of the table. the table should be in 3NF, and for every FD, LHS is super key.

Augmentation Rule (IR2)

also called as a partial dependency if X determines Y, then XZ determines YZ for any Z. If X → Y then XZ → YZ Example: For R(ABCD), if A → B then AC → BC

Decomposition Rule (IR5)

also known as project rule reverse of union rule if X determines Y and Z, then X determines Y and X determines Z separately. If X → YZ then X → Y and X → Z Proof: 1. X → YZ (given) 2. YZ → Y (using IR1 Rule) 3. X → Y (using IR3 on 1 and 2)

1NF

contains an atomic value an attribute of a table cannot hold multiple values must hold only single-valued attribute disallows the multi-valued attribute, composite attribute, and their combinations

Join Dependency

generalization of Multivalued dependencies If the join of R1 and R2 over C is equal to relation R, then we can say that a join dependency (JD) exists. Where R1 and R2 are the decompositions R1(A, B, C) and R2(C, D) of a given relations R (A, B, C, D).

Union Rule (IR4)

if X determines Y and X determines Z, then X must also determine Y and Z. If X → Y and X → Z then X → YZ Proof: 1. X → Y (given) 2. X → Z (given) 3. X → XY (using IR2 on 1 by augmentation with X. Where XX = X) 4. XY → YZ (using IR2 on 2 by augmentation with Y) 5. X → YZ (using IR3 on 3 and 4)

Transitive Rule (IR3)

if X determines Y and Y determine Z, then X must also determine Z. If X → Y and Y → Z then X → Z

Pseudo Rule (IR6)

if X determines Y and YZ determines W, then XZ determines W. If X → Y and YZ → W then XZ → W Proof: 1. X → Y (given) 2. WY → Z (given) 3. WX → WY (using IR2 on 1 by augmenting with W) 4. WX → Z (using IR3 on 3 and 2)

Reflexive Rule (IR1)

if Y is a subset of X, then X determines Y. If X ⊇ Y then X → Y Example: X = {a, b, c, d, e} Y = {a, b, c}

3NF

if it is in 2NF and not contain any transitive partial dependency used to reduce the data duplication. It is also used to achieve the data integrity. If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form. holds atleast one of the following conditions for every non-trivial function dependency X → Y. 1. X is a super key. 2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

5NF

if it is in 4NF and does not contain any join dependency and joining should be lossless 5NF is satisfied when all the tables are broken into as many tables as possible in order to avoid redundancy. 5NF is also known as Project-join normal form (PJ/NF).

2NF

relational must be in 1NF all non-key attributes are fully functional dependent on the primary key

Armstrong's axioms

used to conclude functional dependencies on a relational database