05BeyondBCNF

Beyond BCNF

Outline

Data explosion problem
Multivalued dependency
4NF
Join dependency
5NF

We start from the analysis of a relational schema EMP(employee-number, programming-language, operating-system). The relational schema EMP has no valid functional dependencies. In the reality an employee knows many programming languages and is able to use many operation systems. The same applies to programming language and operating systems. A programming language is known by many employees and operating system can be used by many employees a swe

Now, assume that a relational table EMPLOYEE over a relational schema EMP contains information about the programming languages known by the employees and operating systems that can be used by the employees.

Next, assume that an employee number 200 is. able to program in Java, Python and Scala and the employee can use Linux operating system. To save such information in a relational table EMPLOYEE we have to add the following 3 rows:

[200 Java Linux]

[200 Python Linux]

[200 Scala Linux]

We must add 3 rows because a relational table EMPLOYEE must be in 1NF and we cannot insert sequences or groups of values into single slots in a relational table.

Next, assume that an employee 200 passed a certification exam in Windows operating system. It means that employee number 200 can use Windows operating system together with 3 programming languages. To add such information to a relational table EMPLOYEE we have to add additional 3 rows:

[200 Java Windows]

[200 Python Windows]

[200 Scala Windows]

In general if each time an employee 200 gets a certification in any other operations system we have to add 3 rows to a relational table EMPLOYEE.

Data explosion problem

A relational schema
EMP(employee-number, programming-language, operating-system)
has no valid functional dependencies

A relational table EMPLOYEE over a relational schema EMP contains information about the programming langauges and operating systems know by employees

employee-number     programming-language     operating-system

   200                     Python                  Linux
   200                     Java                    Linux
   200                     Scala                   Linux

   200                     Python                  Windows 10
   200                     Java                    Windows 10
   200                     Scala                   Windows 10

Now, assume that an employee 200 learned how to use a new programming language FORTRAN. Then because FORTRAN can be used both under linux and Windows we must add 2 rows to a relational table EMPLOYEE:

[200 FORTRAN Linux]

[200 FORTRAN Windows]

Now, the employee knows 4 programming languages and the employee can use 2 operation system. Assume that, the employee got a new certification in Unix operating systems. To add such information to a relational table EMPLOYEE we must add 4 new rows:

200 Java Unix]

[200 Python Unix]

[200 Scala Unix]

[200 FORTRAN Unix]

In a general case if an employee is able to use n programming languages and if an employee has certifications in m operating systems then we such information need insertion n*m rows into a relational table. It is called the "data explosion problem".

Data explosion problem

employee-number     programming-language     operating-system
   200                     Python                  Linux
   200                     Java                    Linux
   200                     Scala                   Linux
   200                     Python                  Windows
   200                     Java                    Windows
   200                     Scala                   Windows

   200                     Fortran                 Linux
   200                     Fortran                 Windows

   200                     Python                  Unix
   200                     Java                    Unix
   200                     Scala                   Unix
   200                     Fortran                 Unix

What normal form is valid for a relational schema EMP ?

If a relational schema has no valid nontrivial functional dependencies then such schema is in BCNF. It is because does not exist a nontrivial functional dependency that violates BCNF. Yet, as we can see above a relational table EMPLOYEE built on a relational relational schema EMP may have a lot of redundancies due to the "data explosion problem".

An interesting question is why a relational schema EMP is constructed in an incorrect way. A relational schema EMP is a "product" of an incorrect logical design, i.e. an incorrect transformation of a class of objects into a relational schema. Assume that a class of objects E is described by an attribute employee-number and two multivalued attributes: programming-language and operating-system. A direct transformation of a class E into a relational schema EMP(employee-number, programming-language, operating-system) where the attributes programming-language and operating-system become the single valued attributes is the source of "data explosion" problem.

Data explosion problem

employee-number     programming-language     operating-system
   200                     Python                  Linux
   200                     Java                    Linux
   200                     Scala                   Linux
   200                     Python                  Windows
   200                     Java                    Windows
   200                     Scala                   Windows
   200                     Fortran                 Linux
   200                     Fortran                 Windows
   200                     Python                  Unix
   200                     Java                    Unix
   200                     Scala                   Unix
   200                     Fortran                 Unix

Normal form ?
No valid functional dependencies means that does not exists a functional dependence such that its left hand side is not a superkey
It means that no functional dependencies violate BCNF
BCNF but ... still a lot of redundancies !

Beyond BCNF

Is there any data dependency, like for example functional dependency that is "responsible" for "data explosion" problem ?

Yes, such data dependency is called "multivalued dependency" and in some sense it is opposite to functional dependency. If functional dependency say that one value of an attribute can be associated with only one value of another attribute then multivalued dependency says that one value of an attribute can be associated with many values of another attribute.

Let R = ( A1 , ..., An ) be a relational schema and let X, Y, Z be nonempty subsets of R

We say that a multivalued dependency X →→ Y|Z is valid in relational schema R if for any relational table r created over a relational schema R, if for any two rows v and w in r such that v[X] = w[X] there exist a row t in r such that v[XY] = t[XY] and w[XZ] = t[XZ]

Assume that we have the following two rows in a relational table EMPLOYEE:

v = [200 Java ...]

w = [200 ... Windows]

Then because the rows have the common X-value (200) then there must exist a new row

t = [200 Java Windows]

that is a combination of v= [200 Java ...] and w = [200 Windows]

The following tabular notation expresses a multivalued dependency X →→ Y|Z in a better way

X Y

X Z

--------

X Y Z

It means that whenever we have two rows that have the same X-value then we must have a row with the same X-value from both rows

and Y-value from the first row and Z-value from the second row.

In the other words:

if a row X Y ▢ is in a relational table and a row X ▢ Z is in the same table then a row X Y Z must be in the same relational table

Multivalued dependency

Let R = ( A₁ , ..., A_n ) be a relational schema and let X, Y, Z be nonempty subsets of R
We say that a multivalued dependency X →→ Y|Z is valid in relational schema R if ...
... for any relational table r created over a relational schema R, if for any two rows v and w in r such that v[X] = w[X] there exist a row t in r such that ...
... v[XY] = t[XY] and w[XZ] = t[XZ]
Other notation
It means that if a row X Y ▢ is in a relational table and a row X ▢ Z is in the same table then a row X Y Z must be in the same relational table

Are the redundancies in a relational table EMPLOYEE caused by a multivalued dependency ?

Yes, it is a simple multivalued dependency saying that en employee knows many programming languages and can use many operating systems:

employee-number →→ programming-language | operating-system

The same multivalued dependency can be expressed in a tabular notation in the following way:

employee-number programming-language

employee-number operating-system

---------------------------------------------------------------------------------------

employee-number programming-language operating-system

The other examples of multivalued dependencies:

A person owns many cars and has many skills: first-name, last-name →→ registration-number|skill

A students has many friends and many hobbies student-number →→ first-name, last-name|hobby

If a multivalued dependency X →→ Y|Z is valid in a relational table R and the following relational view are created on R:

CREATE VIEW XY AS (SELECT X,Y FROM R);

CREATE VIEW XZ AS (SELECT X,Z FROM R);

then the results of the following SELECT statement:

SELECT XY.X, XY.Y, XZ.Z

FROM XY JOIN XZ

ON XY.X = XZ.X

are always equal to R.

In the other words if we project a relational table R on XY and the same table on XZ then a join of both projections over an equality condition on a column X must always return the original relational table R.

Multivalued dependency

Examples
employee-number →→ programming-language|operating-system

employee-number     programming-language   
employee-number                              operating-system
-------------------------------------------------------------
employee-number     programming-language     operating-system

A person owns many cars and has many skills
first-name, last-name →→ registration-number|skill
A students has many friends and many hobbies
student-number →→ first-name, last-name|hobby

CREATE VIEW XY AS (SELECT X,Y FROM R);
CREATE VIEW XZ AS (SELECT X,Z FROM R);
SELECT XY.X, XY.Y, XZ.Z
FROM XY JOIN XZ ON XY.X = XZ.X

The result of SELECT is always equal to R

Beyond BCNF

Outline

Data explosion problem
Multivalued dependency
4NF
Join dependency
5NF

The existence of a multivalued dependency in a relational schema is the source of data redundancies earlier called as "data explosion".

To avoid such problem all multivalued dependencies in the relational schema must be equivalent to functional dependencies, i.e. no more than one value can be associated with an attribute. Such condition contributes to a definition of 4th Normal form (4NF)

A relational schema R is in the Fourth Normal Form (4NF) if for every nontrivial multivalued dependency X →→ Y|Z a set of attributes X is a superkey in a relational schema R.

If a set of attributes is X is a superkey then any of its values can be associated with only one value of any other attribute.

An alternative definition of 4NF says:

A relational schema R is in 4NF if no nontrivial multivalued dependencies are valid in a relational schema R

A multivalued dependency

employee-number →→ programming-language|operating-system

is valid in a relational schema

EMP(employee-number, programming-language, operating-system)

A relational schema EMP is NOT in 4NF because a nontrivial multivalued dependency is valid in EMP:

employee-number →→ programming-language|operating-system

4NF

A relational schema R is in the Fourth Normal Form (4NF) if for every nontrivial multivalued dependency X →→ Y|Z a set of attributes X is a superkey in a relational schema R
Alternative definition:
A relational schema R is in 4NF if no nontrivial multivalued dependencies are valid in a relational schema R
A multivalued dependency
employee-number →→ programming-language|operating-system
is valid in a relational schema
EMP(employee-number, programming-language, operating-system)
A relational schema EMP is NOT in 4NF becasuse a nontrivial multivalued dependency is valid in EMP

4NF

Decomposition into 4NF ?
EPGM(employee-number, programming-language),
EOPS(employee-number, operating-system)

Beyond BCNF

Outline

Data explosion problem
Multivalued dependency
4NF
Join dependency
5NF

It is possible to generalise multivalued dependency into "join dependency" by adding more multivalued attributes.

Let R = ( A1 , ..., An ) be a relational schema and let X, Y1 , ... ,Yn be nonempty subsets of R

We say that join dependency ⋈(X, Y1 , ... ,Yn) is valid in a relational schema R if ...

... for any relational table r with relational schema R, if for any n rows v1 , ... , vn in r such that v1[X] = ... = vn[X] there exist a row t in r such that ...

... v1[XY1] = t[XY1 ] and ... and vn[XYn] = t[XYn]

A tabular view of the same definition is much more expressive:

X Y1

X Y2

X Y3

... ... ... ...

X ... ... ... YN

------------------

X Y1 Y2 Y3 ... YN

It means that if in a relational table r we have n rows with the same X-values and different Y1, Y2, Y3, ... Yn-values than we must have one more row

with X Y1 Y2 Y3 ... YN-values.

p>If N = 2 then join dependency reduces to multivalued dependency.

The source of join dependency is the same as the source of multivalued dependency, i.e. incorrect transformation of N multivalued attributes at a stage of logical database design.

Join dependency

Let R = ( A₁ , ..., A_n ) be a relational schema and let X, Y₁ , ... ,Y_n be nonempty subsets of R
We say that join dependency ⋈(X, Y₁ , ... ,Y_n) is valid in a relational schema R if ...
... for any relational table r with relational schema R, if for any n rows v₁ , ... , v_n in r such that v₁[X] = ... = v_n[X] there exist a row t in r such that ...
... v₁[XY₁] = t[XY₁ ] and ... and v_n[XY_n] = t[XY_n]

Other notation

X  Y1  
X     Y2
X        Y3
... ... ... ...
X   ... ... ... YN
------------------
X  Y1 Y2 Y3 ... YN

We extend the previous example of multivalued dependency employee-number →→ programming-language | operating-system by adding a multivalued attribute "hobby". Assuming that an employee has many hobbies multivalued dependency is "upgraded" to join dependency:

⋈(employee-number, programming-language, operating-system, hobby)

A definition of the same join dependency is more expressive in the following tabular format:

employee-number programming-language

employee-number operating-system

employee-number

employee-number hobby

-------------------------------------------------------------------------------------------------

employee-number programming-language operating-system hobby

In another example, a person owns many cars and has many skills and has many employers. Such a domain contributes to the following join dependency:

⋈((first-name, last-name),registration-number, skill, employer)

If a relational table R is crated oveer a schema that satisfies a join dependency:

X Y1

X Y2

X Y3

... ... ... ...

X ... ... ... YN

------------------

X Y1 Y2 Y3 ... YN

then a join of projections of the relational table on XY1, XY2, ... XYN is always equal to the relational table R, please SQL below:

CREATE VIEW XY1 AS (SELECT X,Y1 FROM R);

CREATE VIEW XY2 AS (SELECT X,Y2 FROM R);

... ... ... ... ... ... ... ... ... ...

CREATE VIEW XYN AS (SELECT X,YN FROM R);

SELECT XY1.X, XY1.Y1, XY2.Y2, ... XYN.YN

FROM XY1 JOIN XY2 ON XY1.X = XY2.X JOIN ... JOIN XYN ON XY1.X = XYN.X

The result of SELECT statement above is always equal to R

Join dependency

Examples
⋈(employee-number, programming-language, operating-system, hobby)

employee-number     programming-language   
employee-number                              operating-system
employee-number                                                  hobby
----------------------------------------------------------------------
employee-number     programming-language     operating-system    hobby

A person owns many cars and has many skills and has many employers
⋈((first-name, last-name),registration-number, skill, employer)

CREATE VIEW XY1 AS (SELECT X,Y1 FROM R);
CREATE VIEW XY2 AS (SELECT X,Y2 FROM R);
... ... ... ... ... ... ... ... ... ...
CREATE VIEW XYN AS (SELECT X,YN FROM R);
SELECT XY1.X, XY1.Y1, XY2.Y2, ... XYN.YN
FROM XY1 JOIN XY2 ON XY1.X = XY2.X JOIN ... JOIN XYN ON XY1.X = XYN.X

The result of SELECT is always equal to R

Beyond BCNF

Outline

Data explosion problem
Multivalued dependency
4NF
Join dependency
5NF

A nontrivial join dependency like a nontrivial multivalued dependency contribute to "data explosion" problem. To eliminate such problem we must eliminate nontrivial join dependencies from a relational schema. Such process contributes to a definition of the Fifth Normal Form.

A relational schema R is in the Fifth Normal Form (5NF) if for every nontrivial join dependency⋈(X, Y1 , ... ,Yn) a set of attributes X is a superkey in R. It means that when a val;ue of attribute X can be associated with many values of attributes Y1, Y2, ... Yn than "many" is at most one.

An alternative definition of 5NF is the following:

A relational schema R is in 5NF if no nontrivial join dependencies are valid in schema R

A join dependency ⋈(employee-number, programming-language, operating-system, hobby) is valid in a relational schema EMP(employee-number, programming-language, operating-system, hobby).

It is why a relational schema EMP is NOT in 5NF because a nontrivial join dependency is valid in EMP.

5NF

A relational schema R is in the Fifth Normal Form (5NF) if for every nontrivial join dependency⋈(X, Y₁ , ... ,Y_n) a set of attributes X is a superkey in R
Alternative definition:
A relational schema R is in 5NF if no nontrivial join dependencies are valid in schema R
A join dependency ⋈(employee-number, programming-language, operating-system, hobby) is valid in a relational schema
EMP(employee-number, programming-language, operating-system, hobby)
A relational schema EMP is NOT in 5NF because a nontrivial join dependency is valid in EMP

5NF

Decomposition into 5NF ?
EPGM(employee-number, programming-language),
EOPS(employee-number, operating-system),
EHOB(employee-number, hobby)

References

T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 15.4 Fourth Normal Form (4NF), Chapter 15.5 Fifth Normal Form (5NF), Pearson Education Ltd, 2015