03DatabaseNormalization

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

The first presentation on the relational database mode in a subject CSIT115 introduced a concept of the first normal form (1NF).

We say that a relational schema is in the First Normal Form (1NF) if all occurrences of rows in the respective relational table contain the same number of fields and include the atomic values only, i.e. there are no repeating fields and groups.

A relational table given on the present slide is in the first normal form despite that the values in a column odate (order date) consist of three components: day, month and year. The values of type date can exceptionally have a complex structure because it allows for an easy implementation of date arithmetic (tomorrow = today +1) and date comparison (tomorrow > today). Distribution of date, month and year over separate columns will require additional operations for assembling date components into a complete date.

The other columns in a relational table given on the present slide contain only atomic values.

We also say that a relational schema which is not in the First Normal Form (1NF) is in the Zeroth Normal Form (0NF) or it is called as a nested relational schema. A relational table built over a nested relational schema is called as a nested relational table or 0NF relational table. Do exist database systems that provide a logical view of data as a nested relational table ? In the past such systems have been implemented and later on failed to capture a location at a commercial market. Additionally, later on, object-oriented, object-relational, XML, and JSON data models provide more sophisticated ways of representation of nested data structures than simple nested relational tables.

First Normal Form (1NF)

A relational schema is in the First Normal Form (1NF) if all occurrences of rows in the respective relational table contain the same number of fields and include the atomic values only, i.e. there are no repeating fields and groups

	      
+--------+-------+-------+---------+------------+---------+-------+-------+-------+
|cnumber | fname | lname | onumber |   odate    | lnumber | item  | price | total |
+--------+-------+-------+---------+------------+---------+-------+-------+-------+
|   7    | James | Bond  |    7    | 2017-01-01 |    1    | bolt  | 23.04     5   |
|   7    | James | Bond  |    7    | 2017-01-01 |    2    | nut   | 29.01 |   3   |
|   7    | James | Bond  |    8    | 2017-01-02 |    1    | nut   |  4.55 |   2   |
|   7    | James | Bond  |    8    | 2017-01-02 |    2    | pin   | 14.25 |   2   |
+--------+-------+-------+---------+------------+---------+-------+-------+-------+

A relational schema which is not in the First Normal Form (1NF) is in Zeroth Normal Form (0NF) or it is called as a nested relational schema
A relational table built over a nested relational schema is called as a nested relational table or 0NF relational table.

The present slide shows a nested relational table where information about the orders submitted by a customer is a value in a column orders. A value is a relational table where the values in the columns order number and order date are atomic and the ordered parts is a relational table in a column parts.

In the present example a customer 7, James Bond submitted two orders on 2017-01-01 and on 2017-01-02. The first order included two items: bolt and nut. The second order also included two parts: nut an pin,

A relational table given in the present slide is in 0NF because the values in the columns orders and parts are not atomic.The nested relational tables are the values in the columns orders and parts. It is why a relational table in 0NF is also called as nested relational table.

First Normal Form (1NF)

	      
+---------+-------+-------+---------------------------------------------------------+
| cnumber | fname | lname |                      orders                             |
+---------+-------+-------+---------------------------------------------------------+
|         |       |       | +-----------------------------------------------------+ |
|    7    | James | Bond  | |  onumber | odate       |         parts              | |
|         |       |       | +-----------------------------------------------------+ |
|         |       |       | |          |             | +------------------------+ | |  
|         |       |       | |    7     | 2017-01-01  | |lnumber|item|price|total| | |
|         |       |       | |          |             | +------------------------+ | |
|         |       |       | |          |             | |  1    |bolt|23.04|  5  | | |
|         |       |       | |          |             | |  2    |nut |29.01|  3  | | |
|         |       |       | |          |             | +------------------------+ | |
|         |       |       | |          |             | +------------------------+ | |  
|         |       |       | |    8     | 2017-01-02  | |lnumber|item|price|total| | |
|         |       |       | |          |             | +------------------------+ | |
|         |       |       | |          |             | |  1    |nut | 4.55|  2  | | |
|         |       |       | |          |             | |  2    |pin |14.25|  2  | | |
|         |       |       | |          |             | +------------------------+ | |
|         |       |       | +-----------------------------------------------------+ |
+---------+-------+-------+---------------------------------------------------------+

Database normalization

A concept of a key has been already introduced when the relational database model has been presented in the first year database subject. The keys where defined as the smallest sets of attributes obtained from the transformations of identifiers of classes of objects into primary and candidate keys in a process of logical design. An assumption was such that the identifiers of the classes of objects were created in a correct way.

At a level of conceptual modelling the identifiers are created in an informal way on the base of our knowledge of modelled database domain. When proving the correctness of a relational design with functional dependencies all what we have are the functional dependencies describing the semantics of data. And it means that in such a case the keys must be derived from functional dependencies.

We start from a definition of a superkey. A superkey is a nonempty subset X of relational schema R = (A1, ... , An) such that for any two rows ti, tj, ti≠ tj in a relational table created over a relational schema R ti[X] ≠ tj[X]. It also means that if X is a superkey then no two rows can have identical X-values in a relational table.

A superkey is called as "superkey" because it is a subset of minimal key, i.e. either primary key or one of candidate keys.

An important observation from the previous slide is that if X is a superkey in R then X → A1, ... , An. It means that a superkey functionally determines all other attributes in a relational schema. it also means that if from a given set of functional dependencies we can derive X → A1, ... , An then X is a superkey. If for none of proper subsets X' of X it is true that X' → A1, ... , An then left-hand side of functional dependence is minimal and a superkey X' is minimal. It is later on called as "minimal key".

A minimal key is a superkey K with an additional property such that removal of any attribute from K causes K not to be a superkey.

For example, a relational schema TRIP(rego#, licence#, tdate) has one minimal key (rego#, licence#, tdate). In the systems where a precision of date is a day, a driver can make at most one trip per day. If in the reality such constraint is not true then it is always possible to make granulation of time finer and consider date and time.

A set of attributes is (rego#, licence#, tdate) is a minimal key because none of its subsets has a property to be a key. For example a pair of attributes (rego#, licence#) is not a key because a drive can use the same vehicle for two trips on different days.

It is possible that a relational schema has more than one minimal key. For example, a relational schema DRIVER(licence#, employee#, first-name, last-name) has two minimal keys (licence#) and (employee#).

Keys

A superkey is a nonempty subset X of relational schema R = (A₁, ... , A_n) such that for any two rows t_i, t_j, t_i≠ t_j in a relational table created over a relational schema R t_i[X] ≠ t_j[X]
If X is a superkey in R then X → A₁, ... , A_n
A minimal key is a superkey K with an additional property such that removal of any attribute from K causes K not to be a superkey
For example, a relational schema TRIP(rego#, licence#, tdate) has one minimal key (rego#, licence#, tdate)
For example, a relational schema DRIVER(licence#, employee#, first-name, last-name) has two minimal keys (licence#) and (employee#)

If we know what is a minimal key in a schema, for example a set of attributes X, then any X and any proper superset of X is a superkey.

For example, relational schema

DRIVER(licence#, employee#, first-name, last-name)

has many superkeys: (licence#) , (employee#) , (licence#, employee#) , (licence#, first-name) , (licence#, last-name) , (licence#, first-name, last-name), and so on ...

How does a concept of minimal key is related to the already known concepts of primary and candidate keys ? A minimal key is a smallest set of attribute whose values uniquely identify each row in a relational table.

It means that a primary key is an arbitrarily selected minimal key.

A candidate key is any other minimal key which is not a primary key.

For example a minimal key (rego#, licence#, tdate) in a relational schema

TRIP(rego#, licence#, tdate)

can be nominated as a primary key (rego#, licence#, tdate).

For example, a relational schema

DRIVER(licence#, employee#, first-name, last-name)

has a primary key (licence#) and a candidate key (employee#) or the opposite.

Keys

For example, relational schema DRIVER(licence#, employee#, first-name, last-name) has many superkeys: (licence#) , (employee#) , (licence#, employee#) , (licence#, first-name) , (licence#, last-name) , (licence#, first-name, last-name), and so on ...
A primary key is an arbitrarily selected minimal key
A candidate key is any other minimal key which is not a primary key
For example, a relational schema
TRIP(rego#, licence#, tdate)
has a primary key (rego#, licence#, tdate)
For example, a relational schema
DRIVER(licence#, employee#, first-name, last-name)
has a primary key (licence#) and a candidate key (employee#) or the opposite

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

What are the relationships between superkeys, minimal keys and functional dependencies ?

Assume that we have a relational schema R = (A1, ... , An) that consists of n attributes (columns in a relational table with a header (A1, ... , An) ). We consider two nonempty subsets X, Y of a set of attributes (A1, ... , An) such that X ∪ Y = R. We say that X ∪ Y "covers" a relational schema R.

Then, if a functional dependency X → Y is valid in R then X is a superkey. The opposite is also true. If X is a superkey then a functional dependency X → Y is valid in R.

If a functional dependency X → Y is valid in R and if X is minimal, i.e. it is not true that X' → Y for any X' properly included in X then X is a minimal key. The opposite is also true. If X is a minimal key than X → Y is valid in R and it is not true that X' → Y for any X' properly included in X. It means that left hand side of functional dependency X → Y is minimal.

For example, if a functional dependency licence#, employee# → first-name, last-name is valid in a relational schema

DRIVER(licence#, employee#, first-name, last-name) then (licence#, employee#) is a superkey.

If a functional dependency licence# → first-name, last-name is valid in a relational schema

DRIVER(licence#, employee#, first-name, last-name) then (licence#) is a minimal key.

For example, if a functional dependency employee# → first-name, last-name is valid in a relational schema

DRIVER(licence#, employee#, first-name, last-name) then (employee#) is a minimal key.

Opposite, if (licence#) is a superkey in a relational schema

DRIVER(licence#, employee#, first-name, last-name)

then a functional dependency licence# → employee#, first-name, last-name is valid in a schema DRIVER.

Opposite, if (employee#) is a superkey in a relational schema

DRIVER(licence#, employee#, first-name, last-name)

then a functional dependency employee# → licence#, first-name, last-name is valid in a schema DRIVER.

Functional dependencies and keys

Let R = (A₁, ... , A_n) be a relational schema and let X, Y be nonempty subsets of R such that X ∪ Y = R
If a functional dependency X → Y is valid in R then X is a superkey
If X is a superkey then a functional dependency X → Y is valid in R
For example, if a functional dependency licence#, employee# → first-name, last-name is valid in a relational schema
DRIVER(licence#, employee#, first-name, last-name)
then (licence#, employee#) is a superkey
For example, if (licence#) is a superkey in a relational schema
DRIVER(licence#, employee#, first-name, last-name)
then a functional dependency licence# → employee#, first-name, last-name is valid in DRIVER

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

Assume that X is a minimal key valid in a relational schema R. Any attribute A in X is called as "prime attribute". In the other words, a prime attribute is an attribute from relational schema R which is a member of at least one minimal key in R.

An attribute that is not prime is called as "nonprime attribute". A nonprime attribute is an attribute that is not a member of any minimal key in a schema R.

For example, the attributes licence# and employee# are prime attributes in a relational schema

DRIVER(licence#, employee#, first-name, last-name).

It is because licence# is a member of a minimal key (licence#) and employee# is a member of a minimal key (employee#).

The attributes first-name and last-name are nonprime attributes in a relational schema

DRIVER(licence#, employee#, first-name, last-name)

because both attributes do not belong to the minimal keys (licence#) and (employee#).

In a relational schema

TRIP(rego#, licence#, tdate)

all attributes are prime because all of them belong to a minimal key (rego#, licence#, tdate).

Attributes

A prime attribute is an attribute from relational schema R which is a member of at least one minimal key in R
A nonprime attribute is an attribute which is not prime
For example, the attributes licence# and employee# are prime attributes in a relational schema
DRIVER(licence#, employee#, first-name, last-name)
For example, the attributes first-name and last-name are nonprime attributes in a relational schema
DRIVER(licence#, employee#, first-name, last-name)

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

In the previous slide we found that there exist functional dependencies such that it is impossible to remove any attribute from the left-hand side and still get a valid functional dependency. For example, licence# → employee#, first-name, last-name and employee# → licence#, first-name, last-name belong to such group of functional dependencies. Functional dependency that has such property is call full functional dependency.

A full functional dependency is a functional dependency X → Y such that removal of any attribute A from X causes that (X-A) not → Y.

A partial functional dependency is a functional dependency which is not full functional dependency.

For example, a functional dependency

student#, subject#, edate → grade

valid in a relational schema

ENROLMENT(student#, subject#, edate, grade)

is a full functional dependency because none of the attributes student#, subject#, edate can be removed from the left hand side of the functional dependency such that it is still valid.

In another example, a functional dependency

licence#, employee# → first-name, last-name

valid in a relational schema

DRIVER(licence#, employee#, first-name, last-name)

is a partial functional dependency because if either licence# or employee# attributes are removed from the left hand side of the functional dependency then it is still valid.

If a functional dependency X → Y is full and if a union of X and Y cover entire relational schema R then X is a minimal key in R.

For example, a functional dependency

student#, subject#, edate → grade

valid in a relational schema

ENROLMENT(student#, subject#, edate, grade)

is a full functional dependency because none of the attributes student#, subject#, edate can be removed from the left hand side of the functional dependency such that it is still valid.

For example, a functional dependency

licence#, employee# → first-name, last-name valid in a relational schema

DRIVER(licence#, employee#, first-name, last-name)

is a partial functional dependency because if either licence# or employee# attributes are removed from the left hand side of the functional dependency then it is still valid.

Full and partial functional dependencies

A full functional dependency is a functional dependency X → Y such that removal of any attribute A from X causes that (X-A) ↛ Y
A partial functional dependency is a functional dependency which is not full functional dependency
For example, a functional dependency
student#, subject#, edate → grade
valid in a relational schema
ENROLMENT(student#, subject#, edate, grade) is a full functional dependency because none of the attributes student#, subject#, edate can be removed from the left hand side of the functional dependency such that it is still valid
For example, a functional dependency
licence#, employee# → first-name, last-name valid in a relational schema DRIVER(licence#, employee#, first-name, last-name)
is a partial functional dependency because if either licence# or employee# attributes are removed from the left hand side of the functional dependency then it is still valid

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

Now, we have all concepts required to define the Second Normal Form (2NF) for a relational schema.

We say that a relational schema R is in the Second Normal Form (2NF) if every nonprime attribute A in R is fully functionally dependent on all minimal keys of schema R.

So, to verifiy if a relational schema is in 2NF we need to know all its minimal keys and all functional dependencies valid in the schema. The minimal keys provide us with information about prime and nonprime attributes. From a set of functional dependencies valid in a relational schema we can pick full functional dependencies.

Usually it is more convenient to negate the definition of 2NF and to say that a schema R is not in 2NF if there exists a nonprime attribute that is partially dependent on one of minimal keys, i.e. it is functionally dependent on a proper subset of one of minimal keys.

As a very simple example consider a relational schema R(A,B,C) and a set of functional dependencies {AB → C and A → C}. Then, from a functional dependence AB → C we can conclude that (AB) is a minimal key. Note that a functional dependence A → C does not contribute to a minimal key because it does not cover a schema (A,B,C). Then, the prime attributes are A and B. An attribute C is nonprime. In our case a non prime attribute C functionally dependent on a subset (A) of minimal key (A,B) and because of that it violates 2NF. It is not true that every nonprime attribute is fully functionally dependent on all minimal keys. Attribute C depends on a subset of a minimal key.

Typically, 2NF is violated when a minimal key is composite, i.e. it consists of more than one attribute, and one or more of the prime attributes in a minimal key functionally determines a nonprime attribute.

As a more realistic example consider a relational schema

INVENTORY(part, quantity, warehouse, warehouse address)

that contains information about the parts stored in warehouses, quantities of parts, and addresses of warehouse.

The following functional dependencies are valid in a relational schema WAREHOUSE:

warehouse → warehouse-address; it means that each warehouse is located at one address, however more than one warehouse can be located at the same address

part, warehouse → quantity; it means that a part located in a warehouse has one quantity information associated with it. In the other words there is only one quantity recorded for each part located in a warehouse.

First we try to derive a minimal key. To do so we need to find a functional dependency that covers entire schema and such that its left-hand side cannot be reduced. To get two functional dependencies with the same left-hand side we extend left-hand side of warehouse → warehouse-address in the following way: if warehouse → warehouse-address then part, warehouse → warehouse-address.

Next, if part, warehouse → warehouse-address and part, warehouse → quantity then we can apply a union rule to get part, warehouse → quantity, warehouse-address. Now, we got a functional dependence that covers entire schema and such that its left-hand side cannot be reduced. Hence, its left-hand size is a minimal key (part, warehouse).

Second Normal Form (2NF)

A relational schema R is in the Second Normal Form (2NF) if every nonprime attribute A in R is fully functionally dependent on all minimal keys of schema R
A relational table based on a relational schema
INVENTORY(part, quantity, warehouse,warehouse-address)
contains information about parts stored in warehouses, quantities of parts, and addresses of warehouse
The following functional dependencies are valid in a relational schema INVENTORY(part, quantity, warehouse,warehouse-address)
warehouse → warehouse-address
part, warehouse → quantity
If warehouse → warehouse-address then
part, warehouse → warehouse-address
If part, warehouse → warehouse-address and part, warehouse → quantity then part, warehouse → warehouse-address, quantity

If part, warehouse → warehouse-address, quantity then a minimal key is (part, warehouse).

Now, to fail 2NF we try to find a nonprime attribute (one of: quantity or warehouse-address) that functionally depends on a subset of a minimal key. Indeed an attribute warehouse-address functionally depends on warehouse because each warehouse has one address: warehouse → warehouse-address.

It means that a relational schema

INVENTORY(part, quantity, warehouse, warehouse-address)

is not in 2NF because nonprime attribute warehouse-address depends on a part (warehouse) of a key (part, warehouse). A functional dependency that violates 2NF is warehouse → warehouse-address.

An interesting observation is that if all minimal keys in a relational schema consist of only one attribute (single attribute keys) then such schema is always in 2NF. It is impossible to find a nonprime attribute that functionally depends on a subset of a minimal key. It is because a minimal key cannot be empty. Then any nonprime attribute in the schema does not depend on a part of a key because each key consists of one attribute only.

An interesting question is why a relational schema

INVENTORY(part, quantity, warehouse, warehouse-address)

is constructed in an incorrect way. A simple insertion test provides an answer. Try to insert into a relational table with INVENTORY header the following rows:

[bolt, 20, XYZ, Station St 20]

[screw, 30, XYZ, Station St 20]

[nut, 20, XYZ, Station St 20]

It is quite easy to discover that information about an address Station St 20 of a warehouse XYZ must be repeated as many time as many different types of items are kept in the warehouse. It is because warehouse name functionally determines warehouse address.

Another reason why a relational schema INVENTORY is constructed in an incorrect way is such that a relational table with INVENTORY header contains two different types of information: the first, the total number of items of each type in each warehouse and the second, an address of each warehouse.

Second Normal Form (2NF)

If part, warehouse → warehouse-address, quantity then a minimal key is (part, warehouse)
A relational schema
INVENTORY(part, quantity, warehouse,warehouse-address)
is not in 2NF because nonprime attribute warehouse-address depends on a part (warehouse) of a key (part, warehouse)
A functional dependency that violates 2NF is warehouse → warehouse-address
If all minimal keys in a relational schema consist of only one attribute (single attribute keys) then such schema is always in 2NF
This is because any nonprime attribute in the schema does not depend on a part of a key because each key consists of one attribute only

So, what do we do if a relational schema is not in 2NF. eE must decompose it into two or more schemas. In our case a relational schema

INVENTORY(part, quantity, warehouse, warehouse address)

must be decomposed into the relational schemas:

INVENTORY(part, quantity, warehouse)

WAREHOUSE(warehouse, warehouse-address)

We simply separate information about the quantities of different types of parts in each warehouse from the addresses of warehouses.

The decomposition must be performed such that it is possible to join the tables based on the relational schemas obtained after decomposition. In our case an attribute warehouse present in both schemas can be used to formulate a join condition.

Additionally we try to decompose the schema such that all functional dependencies valid in the original schema are also valid in the decomposed schemas. Note, that it is not always possible to enforce all functional dependencies in the decomposed schemas. If it is possible to enforce a functional dependency in a relational schema then it can be usually done through a primary key constraint that is automatically enforced by a database systems. If a functional dependency cannot be enforced in a decomposed schema that the respective consistency constraint can be still enforce with stored procedures or database triggers. We shall return to this problem later on.

The following functional dependencies are valid in a relational schema INVENTORY(part, quantity, warehouse)

part, warehouse → quantity

Hence (part, warehouse) is a minimal key in a new INVENTORY schemas. Because no nonprime attributes are partially dependent on a minimal key the new INVENTORY schema is in 2NF.

A functional dependency part, warehouse → quantity is automatically enforced through a primary key constraint.

Second Normal Form (2NF)

A relational schema
INVENTORY(part, quantity, warehouse,warehouse address)
must be decomposed into the relational schemas
INVENTORY(part, quantity, warehouse)
WAREHOUSE(warehouse,warehouse-address)
The following functional dependencies are valid in a relational schema INVENTORY(part, quantity, warehouse)
part, warehouse → quantity
Hence (part, warehouse) is a minimal key
A relational schema INVENTORY(part, quantity, warehouse)
is in 2NF because a nonprime attribute quantity does not depend on a part of key (part, warehouse)

The following functional dependencies are valid in the second relational schema

WAREHOUSE(warehouse, warehouse-address):

warehouse → warehouse-address

It means that warehouse is a minimal key. It also means that it is impossible to find a nonprime attribute that functionally depends on a part of a key and because of that WAREHOUSE relational schema is in 2NF.

A functional dependency warehouse → warehouse-address is automatically enforced through a primary key constraint.

Unfortunately, a relational schema can be in 2NF and still a relational table built over such schema may have a lot of redundancies because a functional dependence enforces the repetition of identical pair of values in the relational table.

As a simple example consider a relational schema R(A,B,C) with the following functional dependencies valid in the schema:

A → B and B → C.

Then application of a transitivity axiom allows for a derivation: if A → B and B → C then A → C. Application of union rule to

A → B and A → C provides A → BC. It means that (A) is a minimal key. If (A) is a minimal key then all nonprime attributes B and C are fully functionally dependent on a minimal key and a schema R is in 2NF.

However, insertion of the following rows to a relational table built over a schema R(A, B, C) reveals a redundancy problem:

[1, 2, 3]

[2, 2, 3]

[3, 2, 3]

The pairs (2,3) are repeated as many times as any value of A is a associated with a value of B equal to 2. Then a functional dependency B → C forces a value of C to be equal to 3 and it forces the repetitions of a pair (2,3). It means that the redundancies are still possible in a relatioanal table whose schema is in 2NF.

Second Normal Form (2NF)

The following functional dependencies are valid in a relational schema WAREHOUSE(warehouse, warehouse-address)
warehouse → warehouse-address
Hence (warehouse) is a minimal key
A relational schema WAREHOUSE(warehouse, warehouse-address)
is in 2NF because a nonprime attribute warehouse-address does not depend on a part of key (warehouse)

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

In order to find a new correctness criterion for relational schemas we introduce a concept of "transitive functional dependency"

A functional dependency X → Y valid in a relational schema R is a transitive functional dependency if there exists a nonempty subset Z of R, that is not a subset of any key in R and such that the functional dependencies X → Z and Z → Y are valid in R.

As a simple example consider a relational schema

DEPARTMENT(department-name, project-title, employee#)

and assume that each employee works on one project and each project is implemented by one department. It means that the following functional dependencies are valid in a relational schema DEPARTMENT:

employee# → project-title; each employee works on one project and

project-title → department-name; each project is implemented by one department.

Then a functional dependency employee# → department-name is a transitive functional dependency because there exists a set of attributes (project-title) such that employee# → project-title and project-title → department-name. It means that it is possible to derive a functional dependency dependency employee# → department-name using a transitivity rule over a set of attributes (project-title) not included in any minimal key.

As another example consider a relational schema

DRIVER(licence#, employee#, first-name, last-name)

and assume that a driving licence is granted to only one employee and each employee has one first name. It means that the following functional dependencies are valid in a relational schema DRIVER:

licence# → employee#; a driving licence is granted to one employee and

employee# → first-name; each employee has one first name

Then a functional dependency licence# → first-name can be derived through application of transitivity axiom to licence# → employee# and employee# → first-name. However, in this particular case an attribute employee# is minimal key in a relational schema DRIVER. An attribute employee# violates a definition of a transitive functional dependency and because of that a functional dependency

licence# → first-name is not transitive.

Transitive functional dependencies

A functional dependency X → Y valid in a relational schema R is a transitive functional dependency if there exists a nonempty subset Z of R, that is not a subset of any key in R and such that the functional dependencies X → Z and Z → Y are valid in R
For example, if the functional dependencies
employee# → project-title and project-title → department-name
are valid in a relational schema
DEPARTMENT(department-name, project-title, employee#)
then a functional dependency employee# → department-name is a transitive functional dependency
For example, if the functional dependencies
licence# → employee# and employee# → first-name
are valid in a relational schema
DRIVER(licence#, employee#, first-name, last-name)
then a functional dependency licence# → first-name is not a transitive functional dependency
It is because (employee#) is a key in a relational schema DRIVER

We say that "Y is transitively dependent on X" in a schema R if X → Y is valid in R and X → Y is a transitive functional dependency.

As an example consider a relational schema

TRIP(trip#, licence#, employee#)

where the following functional dependencies are valid in the schema:

trip# → licence#; a trip is performed by one driver

licence# → employee#; a driving licence is granted to one employee and

employee# → licence#; and employee has one driving licence.

Then it is possible to apply a transitivity axiom in the following way: if trip# → licence# and licence# → employee# then we can derive trip# → employee#. An attribute licence# is not included in a minimal key (trip#) and because of that a functional dependence

trip# → employee# is transitive. It means that an attribute employee# is transitively dependent on an attribute trip#.

In the same way it is possible to show that an attribute licence# is transitively dependent on an attribute trip#. If trip# → employee# and employee# → licence# then trip# → licence#. An attribute employee# is not included in a minimal key (trop#) and because of that a functional dependence trip# → licence## is transitive. It means that an attribute licence# is transitively dependent on an attribute trip#.

A relational schema

TRIP(trip#, licence#, employee#)

is constructed in an incorrect way because information about a licence# of a driver with a given employee# is listed as many times as many times a trip is performed by the driver. For example, if a driver performed 100 trips then his/her licence# is listed together with his/her employee# 100 times.

Transitive functional dependencies

We say that Y is transitively dependent on X in a schema R if X → Y is valid in R and X → Y is a transitive functional dependency
For example, if the functional dependencies
trip# → licence#, licence# → employee#, and employee# → licence# are valid in a relational schema TRIP(trip#, licence#, employee#)
then an attribute employee# is transitively dependent on an attribute trip# and ...
... an attribute licence# is transitively dependent on an attribute trip#
Then, information about a licence# of a driver with a given employee# is listed as many times as the total number of trips performed by the driver
For example, if a driver performed 100 trips then his/her licence# is listed together with his/her employee# 100 times.

Consider a relational schema:

OUTLINE(lecturer, subject, textbook)

such that a textbook is used for only one subject and a subject is taught by one lecturer. It means that the following functional dependencies are valid in a schema OUTLINE:

textbook → subject; a textbook is used for one subject

subject → lecturer; a subject is taught by one lecturer.

Then, it is possible to derive a functional dependency textbook → lecturer using transitivity axiom in the following way:

if textbook → subject and subject → lecturer then textbook → lecturer.

A minimal key in a schema OUTLINE is (textbook). Because an attribute subject is not included in a minimal key then an attribute lecturer is transitively dependent on an attribute textbook.

Then, information about a lecturer assigned to a subject is repeated as many times as many textbooks are listed for the subject.

For example, if a subject has 2 textbooks then information about a lecturer assigned to a subject is listed twice.

Transitive functional dependencies

For example, if the functional dependencies
textbook → subject and subject → lecturer are valid in a relational schema OUTLINE(lecturer, subject, textbook)
then an attribute lecturer is transitively dependent on an attribute textbook
Then, information about a lecturer assigned to a subject is repeated as many times as many textbooks are listed for the subject
For example, if a subject has 2 textbooks then information about a lecturer assigned to a subject is listed twice

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

The definitions of transitive functional dependencies and the attributes being functionally dependent on other attributes allow us to look the first definition of the Third Normal Form (3NF).

Definition of 3NF

A relational schema R is in the Third Normal Form (3NF) if it is in 2NF and no nonprime attribute of R is transitively dependent on the primary key.

We start from an application of the 3NF definition to a simple example studied at the end of 2NF section where the functional dependencies A → B and B → C are valid in a relational schema R(A,B,C). Then a minimal key can be derived through application of transitivity axiom A → B and B → C provide A → C and later on union rule A → B and A → C provide A → BC. Hence (A) is a minimal key.

Now when testing 3NF definition we find that a nonprime attribute C is transitively dependent on the primary/minimal key (A). It means that 3NF does not hold in the schema R(A,B,C).

Now, we look at more realistic example. Assume that relational table based on a relational schema (header)

SUPPLIER(s#, sname, company-name, city)

contains information about suppliers working for a company located in a given city.

Then the following functional dependencies are valid in a relational schema SUPPLIER(s#, sname, company-name, city)

s# → sname ; each supplier has one name

s# → company-name ; each supplier belongs to one company

s# → city ; each supplier lives in one city

company-name → city ; each company is located in one city

If we apply a union rule to the first the functional dependencies with the left-hand side s# then we can derive a functional dependency

s# → sname, company-name, city. The functional dependency cover entire schema SUPPLIER and it is left-hand side is minimal. It means that the left hand size (s#) is a minimal key.

Third Normal Form (3NF)

A relational schema R is in the Third Normal Form (3NF) if it is in 2NF and no nonprime attribute of R is transitively dependent on the primary key
A relational table based on a relational schema
SUPPLIER(s#, sname, company-name, city)
contains information about suppliers working for a company located in a given city
The following functional dependencies are valid in a relational schema SUPPLIER(s#, sname, company-name, city)
s# → sname
s# → company-name
s# → city
company-name → city
A primary key in a relational schema SUPPLIER is (s#) because
s# → sname, company-name, city

A set of functional dependencies give above includes two functional dependencies consistent with a transitivity pattern explained earlier:

s# → company-name

company-name → city

Application of the transitivity axiom provides a functional dependency s# → city. It is a transitive functional dependency because an attribute company-name is not included. If it is so then and attribute city is transitively dependent on a primary/minimal key. At the end it leads to a conclusion that a relational schema SUPPLIER is not in 3NF.

If a relational a schema SUPPLIER is not in 3NF then it contributes to the redundancies in a relational table built on a schema (header) INVENTORY. We can apply an insertion test to show the redundancies. We insert information about 3 suppliers working for the same company:

7 James, MI6, London

6 Harry, MI6, London

5 Robin, MI6, London

A functional dependency company-name → city forces the repetitions of a pair [MI6, London] as many times as many suppliers work for a company MI6.

It is interesting that a relational schema SUPPLIER is in the Second Normal Form (2NF) because each nonprime attribute sname, company-name, and city is fully functionally dependent on a primary key (s#)

s# → sname

s# → company-name

s# → city

It is yet another evidence that 2NF is not sufficient to completely eliminate the redundancies.

Third Normal Form (3NF)

A relational schema SUPPLIER is not in the Third Normal Form (3NF) because an attribute city is transitively dependent on a primary key (s#)
An attribute city is transitively dependent on a primary key (s#) because
s# → company-name and company-name → city
A relational schema SUPPLIER is in the Second Normal Form (2NF) because each noprime attribute sname,company-name, and city is fully functionally dependent on a primary key (s#)
s# → sname
s# → company-name
s# → city

To avoid the redundancies we must separate information about the suppliers from information about the locations of the companies.

to do so, a relational schema

SUPPLIER(s#, sname, company-name, city)

should be decomposed into the relational schemas

SUPPLIER(s#, sname, company-name)

COMPANY(company-name, city)

Does such decomposition solves the problem ?

A new relational schema

SUPPLIER(s#, sname, company-name)

is in 3NF because no attribute is transitively dependent on a primary key (s#)

s# → sname

s# → company-name

This time the decomposition "eliminated" a functional dependency company-name → city that forced the repetitions of a company-name and a city where a company is located.

A new relational schema

COMPANY(company-name, city)

is in 3NF because no nonprime attribute is transitively dependent on a primary key (company-name)

company-name → city

Please note again, that the decomposition must be done such that it is later on possible to join the relational tables with the headers SUPPLIER and COMPANY. A column company-name is common to both relational tables. The functional dependencies are enforced through primary keys in the relational tables, primary key (s#) in a new SUPPLIER and company-name in COMPANY. Additionally and attribute company-name in a relational table with a header SUPPLIER is a foreign key referencing primary key in a relational table with a header COMPANY.

Third Normal Form (3NF)

A relational schema SUPPLIER(s#, sname, company-name, city)
should be decomposed into the relational schemas
SUPPLIER(s#, sname, company-name)
COMPANY(company-name, city)
A relational schema SUPPLIER(s#, sname, company-name)
is in 3NF because no attribute is transitively dependent on a primary key (s#)
s# → sname
s# → company-name
A relational schema COMPANY(company-name, city)
is in 3NF because no nonprime attribute is transitively dependent on a primary key (company-name)
company-name → city

Like for 2NF a relational schema that consists of only 2 attributes (a relational table with only 2 columns) is always in 3NF no matter what attributes are included in the schema.

We shall prove it by enumerating all possible configurations of nontrivial functional dependencies: no functional dependencies, one functional dependency, two functional dependencies.

We start from a case where a relational schema R(a,b) has no nontrivial functional dependencies. Then, a minimal key can be derived from a trivial functional dependency ab → ab. Hence a minimal key is (ab) and no nonprime attribute is transitively dependent on a minimal key (a, b). It is because there are no nonprime attributes. It means that a relational schema R(a,b) is in 3NF.

In the second case we assume that only one nontrivial functional dependency is valid in the relational schema: a → b (a case where b → a is the same). Then, a minimal key is (a) and no nonprime attribute is transitively dependent on a minimal key key (a). It means that a relational schema R(a,b) is in 3NF.

Third Normal Form (3NF)

Any relational schema that consists of at most 2 attributes is always in 3NF
Let R(a,b) be a relational schema such that no notrivial functional dependencies are valid in R
Then (a, b) is a primary key in R and ...
... no nonprime attribute is transitively dependent on a primary key
(a, b)
Let R(a,b) be a relational schema such that a functional dependency
a → b is valid in R
Then (a) is a primary key in R and ...
... no nonprime attribute is transitively dependent on a primary key (a)

Third Normal Form (3NF)

Let R(a,b) be a relational schema such that the functional dependencies a → b and b → a are valid in R
Then either (a) is a primary key in R or (b) is a primary key in R and ...
... no nonprime attribute is transitively dependent on either (a) or (b)

Now, we consider an alternative definition of 3NF. Such definition is useful to explain the differences between 3NF and BCNF. An alternative definition of 3NF is equivalent to the original one base on a concept of transitively dependent attributes.

Accordingly to an alternative definition a relational schema R is in the Third Normal Form (3NF) if whenever a functional dependency X → A is valid in R then either

(i) X is a superkey in R or

(ii) A is a prime attribute in R

It is important to note that a relational schema is in 3NF if ONE of TWO conditions is satisfied. There is no need for both of them to be satisfied. For example, if the first condition fails, i.e. X is not a superkey in R then we still have the "second chance" where A is a prime attribute in R.

For example we use an alternative definition to show that a relational schema

SUPPLIER(s#, sname, company-name, city) with the following valid functional dependecies:

s# → sname

s# → company-name

s# → city

company-name → city

is not in 3NF.

We have already found that a minimal key is equal to (s#). Then, a condition(i) is satisfied for the first 3 functional dependencies. However, a functional dependency company-name → city fails a condition (i) because its left-hand side is not superkey in SUPPLIER. We still have the second chance; condition (ii). Unfortunately, condition (ii) fails as well, because right-hand side city of

company-name → city is not a prime attribute in SUPPLIER.

Third Normal Form (3NF)

Alternative definition of the Third Normal Form
A relational schema R is in the Third Normal Form (3NF) if whenever a functional dependency X → A is valid in R then either ...
- X is a superkey in R or
- A is a prime attribute in R
For example, a relational schema
SUPPLIER(s#, sname, company-name, city)
s# → sname
s# → company-name
s# → city
company-name → city
is not in 3NF because ...
... if we consider a functional dependency company-name → city then ...
- an atribute company-name is not a superkey and
- an attribute city is not a prime attribute

In the next example we consider a relational schema

LOCATION(city, street, zipcode)

and the following functional dependencies valid in the schema:

city, street → zipcode; a street in a city cannot be split over two or more zipcodes

zipcode → city ; each zip code is associated with only one city

are valid in a relational schema LOCATION(city, street, zipcode)

The schema has two minimal keys:

(city, street) sthat comes directly from a functional dependecy: city, street → zipcode

and

(zipcode, street) that comes from application of augmentation axiom to zipcode → city ==> zipcode, street → city, street.

A relational schema LOCATION(city, street, zipcode) is in 3NF because:

(i) the left hand side of city, street → zipcode is a superkey and

(ii)the left side of zipcode → city is not a superkey but ... the right hand side (city) of zipcode → city is a prime attribute.

Third Normal Form (3NF)

For example, if the functional dependencies
city, street → zipcode
zipcode → city
are valid in a relational schema LOCATION(city, street, zipcode)
then the schema has two minimal keys
(city, street)
Directly implied by a functional dependency city, street → zipcode
(zipcode, street)
If zipcode → city then zipcode, street → city, street
A relational schema LOCATION(city, street, zipcode) is in 3NF because
- the left hand side of city, street → zipcode is a superkey and
- the left side of zipcode → city is not a superkey but ... the right hand side (city) of zipcode → city is a prime attribute

Unfortunately, it is possible to find a relational table with a header LOCATION(city, street, zipcode) and such that its contents is redundant:

+------+--------+---------+	      
| city | street | zipcode |
+------+--------+---------+
|  NY  |   55   |   484   |
|  NY  |   56   |   484   |
|  LA  |   55   |   473   |
|  LA  |   56   |   473   |
|  LA  |   57   |   474   |    
+------+--------+---------+

In the table above a pair [city, zipcode] is repeated as many time as many streets in the city has the same zipcode. For example, a pair [NY, 484] is repeated twice because the table contains information about two streets 55, and 56 with the same zipcode 484. A pair [LA,473] is repeated twice because of two streets 55, 56 having the same zip code 473.

A discovery of a redundant relational table that is in 3NF means that 3NF is not the highest form required.

Third Normal Form (3NF)

The following relational table created over a relational schema LOCATION(city, street, zipcode) is redundant

+------+--------+---------+	      
| city | street | zipcode |
+------+--------+---------+
|  NY  |   55   |   484   |
|  NY  |   56   |   484   |
|  LA  |   55   |   473   |
|  LA  |   56   |   473   |
|  LA  |   57   |   474   |    
+------+--------+---------+

because the repetitions of | LA ... 473 | and | NY ... 484 | are forced by a functional dependency zip-code → city
It means that 3NF is not the highest normal form required !

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

A definition of correctness stronger than 3NF is a definition of Boyce-Codd Normal Form(BCNC). A definition of BCNF says that a relational schema R is in the Boyce-Codd Normal Form (BCNF) if whenever a functional dependency X → A is valid in R then X is a superkey in R.

The definition of BCNF is stronger than a definition of 3NF because it does not allow for the second option when the right-hand side of a functional dependency is a prime.

For example, if the functional dependencies

city, street → zipcode

zipcode → city

are valid in a relational schema LOCATION(city, street, zipcode) then as we found earlier, the schema has two minimal keys:

(city, street) and (zipcode, street)

Then, a relational schema LOCATION is not in BCNF because the left side of zipcode → city is not a superkey.

Note, that a functional dependency forced the repetitions of the pairs [zip-code, city] in a relational table that has a header LOCATION(city, street, zipcode). Lack of the second option in a definition of BCNF eliminates the chances for the redundant pairs [zip-code, city].

Boyce-Codd Normal Form (BCNF)

A relational schema R is in the Boyce-Codd Normal Form (BCNF) if whenever a functional dependency X → A is valid in R then
- X is a superkey in R
Boyce-Codd Normal Form is more restrictive because its definition does not give the "second chance"
- A is a prime attribute in R
For example, if the functional dependencies
city, street → zipcode
zipcode → city
are valid in a relational schema LOCATION(city, street, zipcode) then the schema has two minimal keys
(city, street) and (zipcode, street)
Then, a relational schema LOCATION is not in BCNF because
- the left side of zipcode → city is not a superkey

Boyce-Codd Normal Form (BCNF)

A relational schema LOCATION(city, street, zipcode)
should be decomposed into the relational schemas
SZ(street, zipcode)
CZ(city, zipcode)
A relational schema SZ(street, zipcode) has no valid nontrivial functional dependencies
A minimal key in a relational schema SZ(street, zipcode) is
(street, zipcode)
A relational schema SZ(street, zipcode) is in BCNF because does not exist a functional dependency whose left hand side is not a superkey

A functional dependency zipcode → city is valid in a relational schema

CZ(city, zipcode)

It is why a minimal key in a relational schema CZ(city, zipcode) is (zipcode). A relational schema CZ(city, zipcode) is in BCNF because the left hand of functional dependency zipcode → city is a superkey (zipcode).

Like in 2NF and 3NF every relational schema, which consists of at most 2 attributes (so called "binary" relational schema) is always in BCNF. It is possible to prove it in the same way as we proved it before for 3NF.

It is interesting that in this case normalization to BCNF "costs" a functional dependency city, street → zipcode that can not be enforced through primary key or candidate key constraints on the decomposed relational schemas. It is because the attributes city and street are in two different relational schemas. It is a "price" we pay for elimination of redundancies in this case. It is why sometimes we may wish to stay at 3NF level and trade the enforcement of functional dependencies for redundancies.

Additional 3NF reduces fine granulation of the relational schemas eliminating the needs for join operation in SELECT statement when a query asks about association of the values from city and street columns. Elimination of join operation supports better performance of query processing.

Boyce-Codd Normal Form (BCNF)

A functional dependency zipcode → city is valid in a relational schema
CZ(city, zipcode)
A minimal key in a relational schema CZ(city, zipcode) is
(zipcode)
A relational schema CZ(city, zipcode) is in BCNF because the left hand of functional dependency zipcode → city is a superkey (zipcode)
Every relational schema, which consists of at most 2 attributes is always in BCNF
Normalization to BCNF "costs" a functional dependency city, street→ zipcode
It means that it is impossible to enforce the functional dependency
city, street→ zipcode
with a primary key or candidate key constraints of CREATE TABLE statement

The following relational tables are created through decomposition of a relational schema LOCATION(city, street, zipcode) into the relational schemas SZ(street, zipcode) and CZ(city, zipcode):

         LOCATION                      SZ                    CZ
    +------+--------+---------+   +--------+---------+  +------+---------+	      
    | city | street | zipcode |   | street | zipcode |  | city | zipcode |
    +------+--------+---------+   +--------+---------+  +------+---------+
    |  NY  |   55   |   484   |   |   55   |   484   |  |  NY  |   484   |
    |  NY  |   56   |   484   |   |   56   |   484   |  |  LA  |   473   |
    |  LA  |   55   |   473   |   |   55   |   473   |  |  LA  |   474   |
    |  LA  |   56   |   473   |   |   56   |   473   |  +------+---------+
    |  LA  |   57   |   474   |   |   57   |   474   | 
    +------+--------+---------+   +--------+---------+

A functional dependency city, street → zipcode cannot be enforced in the relational tables SZ and CZ.

Boyce-Codd Normal Form (BCNF)

The following relational tables are created through decomposition of a relational schema LOCATION(city, street, zipcode) into the relational schemas SZ(street, zipcode) and CZ(city, zipcode)


LOCATION                      SZ                    CZ
+------+--------+---------+   +--------+---------+  +------+---------+	      
| city | street | zipcode |   | street | zipcode |  | city | zipcode |
+------+--------+---------+   +--------+---------+  +------+---------+
|  NY  |   55   |   484   |   |   55   |   484   |  |  NY  |   484   |
|  NY  |   56   |   484   |   |   56   |   484   |  |  LA  |   473   |
|  LA  |   55   |   473   |   |   55   |   473   |  |  LA  |   474   |
|  LA  |   56   |   473   |   |   56   |   473   |  +------+---------+
|  LA  |   57   |   474   |   |   57   |   474   | 
+------+--------+---------+   +--------+---------+

A functional dependency city, street → zipcode cannot be enforced in the relational tables SZ and CZ

Database normalization

Outline

First Normal Form (1NF)
Keys
Functional dependencies and keys
Attributes
Full and partial functional dependencies
Second Normal Form (2NF)
Transitive functional dependencies
FThird Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Normalization of relational schemas

Assume that we would like to normalize a relational schema R. Normalization of relational schemas, i.e. bringing the schemas to BCNF consists of the following steps:

(1) First, we identify all functional dependencies valid in a relational schema R to be normalized. It is a difficult step that requires good understanding of a database domain represented by the contents of a relational table with a header R. A mistake at this stage may propagate to the next steps and it may completely destroy the correctness of entires process. For example, a missing functional dependency may by mistake validate a relational schema in BCNF while the schema would not be even in 2NF. Correct identification of the functional dependencies valid in a relational schema requires very good knowledge of the database domain.

(2) Next, we use the functional dependencies to derive all minimal keys. It is a step where the Armstrong axioms and other derivation rules are used to derive functional dependencies that cover entire relational schema and such that their left-hand sides are minimal. Then, the left hand-sides of such functional dependencies become the minimal keys.

(3) Next, we use the functional dependencies and minimal keys to identify the highest normal form satisfied by a relational schema R. Such verification can be done either from 2NF towards BCNF or from BCNF towards 2NF.

(4) Finally, if a relational schema R is not in BCNF we decompose it into the relational schemas in BCNF (optionally 3NF). Decomposition should preserve as many nontrivial functional dependencies as it is possible. It also must be possible to restore the original relational table from the components obtained after the decomposition.

Normalization of relational schemas

Let R = ( A₁, ..., A_n) be a relational schema (a header of relational table)
Normalization of a relational schema R is performed in the following way
- Identify all functional dependencies valid in a relational schema R
- Use the functional dependencies to derive all minimal keys
- Use the functional dependencies and minimal keys to identify the highest normal form satisfied by a relational schema R
- Decompose a relational schema R into the relational schemas in BCNF (3NF)

We apply a procedure explained in the previous slide to normalize a relational schema:

SHIPMENT(s#, city, status, p#, quantity)

A relational table with a header SHIPMENT(s#, city, status, p#, quantity) contains information about suppliers (s#), locations of suppliers (city), status of each city (status, for example big, small, capital, etc) parts shipped by each supplier (p#) and total quantity of parts shipped so far (quantity)

Analysis of a database domain leads to identification of the following functional dependencies valid in a relational schema SHIPMENT:

s# → city; each supplier is located in one city

s# → status; each supplier is associated with on status of a city

city → status; each city has one status

s#,p# → quantity; if a supplier (s#) supplies part with a given part number (p#) then only one cumulative quantity is recorded for all shipments of suhc part.

s#,p# → city; this functional dependency can be derived from s# → city.

s#,p# → status this functional dependency can be derived from s# → status.

The last three functional dependencies can be simplified into on functional dependency: s#,p# → quantity, city, status. Such functional dependecy covers entire schema and with left-hand side minimal, it contributes to a minimal key (s#,p#).

A functional dependency breaks 2NF because a nonprime attribute city functionally depends on a subset (s#) of a minimal key (s#,p#). So, relational schema SHIPMENT(s#, city, status, p#, quantity) is not in 2NF because a nonprime attribute city depends on a subset of a minimal key (s#, p#), s# → city.

Normalization of relational schemas

For example, consider a relational schema
SHIPMENT(s#, city, status, p#, quantity)
The following functional dependencies are valid in the schema
s# → city
s# → status
city → status
s#, p# → quantity
s#, p# → city
s#, p# → status
A minimal key is (s#, p#)
A relational schema SHIPMENT(s#, city, status, p#, quantity) is not in 2NF because a nonprime attribute city depends on a subset of a minimal key (s#, p#), s# → city

A decomposition of a relational schema SHIPMENT that preserves the functional dependencies is the following:

SP(s#, p#, quantity)

s#,p# → quantity. It immediately provides a minimal key (s#,p#).

SUPPLIER(s#, city, status)

s# → city

s# → status

city → status

The first two functional dependencies can be simplified to s# → city, status

A relational schema SP(s#, p#, quantity) is in BCNF because s#, p# → quantity, i.e. left hand side of the functional dependency is a superkey.

A relational schema SUPPLIER(s#, city, status) is not in 3NF because an attribute status is transitively dependent on an attribute s#, i.e. s# → city and

s# → status.

It is also possible to use an alternative definition of 3NF in the following way: a relational schema SUPPLIER(s#, city, status) is not in 3NF because left hand side of functional dependency city → status is not a superkey and right hand side is not prime attribute.

A relational schema SUPPLIER is in 2NF because no nonprime attribute functionally depends on a subset of a minimal key. It means that a relational schema SUPPLIER must be further decomposed into the schemas in BCNF.

Normalization of relational schemas

A relational schema SHIPMENT(s#, city, status, p#, quantity) should be decomposed into the relational schemas
SP(s#, p#, quantity) with a minimal key (s#, p#)
SUPPLIER(s#, city, status) with a minimal key (s#)
A relational schema SP(s#, p#, quantity) is in BCNF because
s#, p# → quantity, i.e. left hand side of the functional dependency is a superkey
A relational schema SUPPLIER(s#, city, status) is not in 3NF because an attribute status is transitively dependent on an attribute s#, i.e. s# → city and
s# → status
A relational schema SUPPLIER(s#, city, status) is not in 3NF because left hand side of functional dependency city → status is not a superkey and right hand side is not prime attribute

Normalization of relational schemas

A relational schema SUPPLIER(s#, city, status) should be decomposed into the relational schemas
SUPPLIERCITY(s#, city) with a minimal key (s#)
LOCATION(city, status) with a minimal key (city)
A relational schema SUPPLIERCITY(s#, city) is in BCNF because
s# → city, i.e. left hand side of the functional dependency is a superkey
A relational schema LOCATION(city, status) is in BCNF because
city → status, i.e. left hand side of the functional dependency is a superkey

Note that a relational schema SUPPLIER(s#, city, status)can be decomposed in a different way into the schemas in BCNF. The other way enforces the functional dependencies:

s# → city and

s# → status

However, it does not enforce a functional dependency city → status because such functional dependency cannot be derived from s# → city, s# → status. In the previous decomposition we enforced:

s# → city and

city → status.

Then, a functional dependency s# → status can be always derived from s# → city, city → status and when both functional dependencies are enforced then s# → status is automatically enforced.

A relational schema SUPPLIER(s#, city, status) can be alternatively decomposed into the relational schemas:

SUPPLIERCITY(s#, city) with a minimal key (s#)

SUPPLIERSTAT(s#, status) with a minimal key (s#)

A relational schema SUPPLIERCITY(s#, city) is in BCNF because

s# → city, i.e. left hand side of the functional dependency is a superkey

A relational schema SUPPLIERSTAT(s#, status) is in BCNF because

s# → status, i.e. left hand side of the functional dependency is a superkey

In order to automatically enforce a functional dependency city → status we have to implement either implement a stored procedure/function that verifies the functional dependency or implement a trigger that fires after each update or insertion on the relational tables. So, it is an additional complication in the design.

An interesting question is in what circumstances the second decomposition would be preferable over the first one ? Consider a query that finds associations between the values of attributes s# and status, for example, what is a status of a city where a given supplier lives in ? In the first design, to implement such query, we must join the tables build over the relational schemas SUPPLIER and SUPPLIERCITY. In the second design it is enough to access a relational table built over a relational schema SUPPLIERSTAT. It means that processing of such query would be much faster than in the first. If we would like to improve performance of some database applications it is possible to re-structure a database such that processing of selected applications is faster.

Normalization of relational schemas

A relational schema SUPPLIER(s#, city, status) can be alternatively decomposed into the relational schemas
SUPPLIERCITY(s#, city) with a minimal key (s#)
SUPPLIERSTAT(s#, status) with a minimal key (s#)
A relational schema SUPPLIERCITY(s#, city) is in BCNF because
s# → city, i.e. left hand side of the functional dependency is a superkey
A relational schema SUPPLIERSTAT(s#, status) is in BCNF because
s# → status, i.e. left hand side of the functional dependency is a superkey

References

T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 14.5 The Process of Normalization, Chapter 14.6 First Normal Form (1NF), Chapter 14.7 Second Normal Form (2NF), Chapter 14.8 Third Normal Form (3NF), Chapter 14.9 General definitions of 2NF and 3NF, Chapter 15.2 Boyce-Codd Normal Form (BCNF) Pearson Education Ltd, 2015