01DatabaseDesignQuality

Database Design Quality

Outline

Why not ONE BIG TABLE !?
Where is a problem ?
Functional dependency

An interesting question related to a process of logical design is why do we need to create many relational table instead of ONE BIG TABLE where we could store all data ? Transformation of the classes of objects into relational schemas later on implemented as relational table and filled with rows seems to be a very natural and very straightforward decision. But, in a case when we transform one-to-one association we merge the attributes from two classes into one relational schema. Can we do the same thing with the classes linked with one-to-many association and many-to-many association ?

Or when we create a conceptual schema can we create ON BIG CLASS and later on transform such class in ONE BIG TABLE ?

In order to find why we cannot have ONE BIG TABLE let us look at the following example. Let us consider the following database domain:

A customer is described by a unique customer number, first and last name. Customers submit orders. An order is described by a unique order number and order date. Orders consist of lines. A line contains information about a name of ordered item, price per single item and total number of ordered items.

A conceptual schema obtained from a process of object modeling is given on the present slide. We created three classes of objects: CUSTOMER, ORDER and LINE. Then, we linked the classes with the associations: CUSTOMER Submits ORDER and ORDER Consists-of LINE.

There is no doubts, that such conceptual schema represents a given database domain in a consistent and comprehensive way.

Why not ONE BIG TABLE !?

Let us consider the following database domain:
- A customer is described by a unique customer number, first, and last name
- Customers submit orders. An order is described by a unique order number and order date
- Orders consist of lines. A line contains information about a name of ordered item, price per single item, and total number of ordered items

Association class

Logical design provides the following relational schemas:

CUSTOMER(cnumber, first-name, last-name) 
PRIMARY KEY = (cnumber)

ORDERS(onumber, odate, cnumber) PRIMARY KEY = (onumber)
FOREIGN KEY = (cnumber) REFERENCES CUSTOMER(cnumber)

LINE(onumber, lnumber, item, price total)
PRIMARY KEY = (onumber, lnumber)
FOREIGN KEY = (onumber) REFERENCES ORDERS(onumber)

Now, we apply to the conceptual schema created on the previous slide a process of logical design and we get three relational schemas:

CUSTOMER(cnumber, first-name, last-name)

PRIMARY KEY = (cnumber)

ORDERS(onumber, odate, cnumber) PRIMARY KEY = (onumber)

FOREIGN KEY = (cnumber) REFERENCES CUSTOMER(cnumber)

and

LINE(onumber, lnumber, item, price total)

PRIMARY KEY = (onumber, lnumber)

FOREIGN KEY = (onumber) REFERENCES ORDERS(onumber)

The foreign keys in the relational schemas ORDERS and LINE reference the respective primary keys in the relational tables CUSTOMER and ORDERS. These are the outcomes from a typical process of conceptual modeling followed by a process of logical design.

Now, we return to the first question: is it possible to have ONE BIG TABLE with a header (relational schema), that consists of all attributes used in the design ?

For example:

CUSTOMER(cnumber, first-name, last-name, onumber, odate, cnumber, onumber, lnumber, item, price total)

PRIMARY KEY = (cnumber, onumber, lnumber)

Primary key in such table would be a composite key, that uniquely identifies each line in each order. It is why primary key consists of the attributes (onumber, lnumber).

Why not ONE BIG TABLE !?

Why not one relational schema ?


CUSTOMER(cnumber, first-name, last-name, onumber, odate, cnumber, onumber, lnumber, item, price total)
PRIMARY KEY = (cnumber, onumber, lnumber)

Insertion of information about one customer who submitted 2 orders such that each order consists several lines reveals a problem !


+---------+-------+-------+---------+-------------+---------+-------+-------+-------+
| 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 | screw | 29.01 |     3 |
|    |  7 | James | Bond  | |   | 7 | 2017-01-01| |       3 | nut   |  4.55 |     2 |
|    |    |       |       | |   +---------------+ |         |       |       |       |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       1 | bolt  | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       2 | screw | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       3 | nut   | 23.04 |     2 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       4 | lock  | 23.04 |     1 |
|    +----------------------+   +---------------+ |         |       |       |       |
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+

At the first glance, ONE BIG TABLE design looks all right. However, insertion of few rows into such table reveals the problems.

Two rows that contain information about two lines from the same order have order date information repeated twice, for example a date 2017-01-01 of an order number 7 is repeated twice because such order consists of 2 lines. If an would consist of 100 lines then order date would be repeated 100 time. Next, information about a customer submitted order like first and last name is repeated as many time as many lines all orders consists of. In our example it is repeated 5 times.

In conclusion, A number, first name and last name of a customer is repeated as many times as the total number of different items purchased in all orders and order number is repeated together with order date as many times as the total number of different items purchased in an order.

It means that ONE BIG TABLE may have a lot of redundancies and it is the main reason why such design is not good.

Why not ONE BIG TABLE !?

	      
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+
| 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 | screw | 29.01 |     3 |
|    |  7 | James | Bond  | |   | 7 | 2017-01-01| |       3 | nut   |  4.55 |     2 |
|    |    |       |       | |   +---------------+ |         |       |       |       |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       1 | bolt  | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       2 | screw | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       3 | nut   | 23.04 |     2 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       4 | lock  | 23.04 |     1 |
|    +----------------------+   +---------------+ |         |       |       |       |
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+

A number, first name, and last name of a customer is repeated as many times as the total number of different items purchased in all orders and ...
... and order number is repeated together with order date as many times as the total number of different items purchased in an order

Why not ONE BIG TABLE !?

A multitable design does not have such a problem:

CUSTOMER(cnumber, first-name, last-name) 
PRIMARY KEY = (cnumber)

+---------+-------+-------+
| cnumber | fname | lname |
+---------+-------+-------+
|       7 | James | Bond  |
+---------+-------+-------+

ORDERS(onumber, odate, cnumber) PRIMARY KEY = (onumber)
FOREIGN KEY = (cnaumber) REFERENCES CUSTOMER(cnumber)

+---------+------------+---------+
| onumber | odate      | cnumber |
+---------+------------+---------+
|       7 | 2017-01-01 |       7 |
|       8 | 2018-01-01 |       7 |
+---------+------------+---------+

A relational table LINE contains the repetition of the order numbers in a column onumber. It is because a column onumber is a foreign key in a relational table LINE. The repetitions of the values in foreign key columns are inevitable in the relational data model. However, such repetitions can be eliminated at the physical stage of a design through clustering of the relational table(s).

In the past (when the hard drives had the capacities ~40Mbs, hmmm, hard to believe in something like that, these days ...) at the ed of logical design the relational tables with the long primary keys, i.e. composite primary keys that consisted of 3 or more attributes obtained an additional attribute called "surrogate key". Then surrogate key could be used as the foreign keys in the other relational tables to implement in the relational model an association between the classes of objects. A goal of using surrogate keys was to avoid large storage consumption for storing the values of foreign keys.

Nowadays, we do not use surrogate keys. Now, "surrogate keys" is a "blast from the past" because of large amounts of cheap and faster persistent storage available fro implementation of relational tables. It is even better to have long composite primary keys because it is away how more attributes can be copied to the other tables as foreign keys. Such strategy reduces the probabilities of joins and makes query processing faster.

Why not ONE BIG TABLE !?

A multitable design does not have such a problem:

LINE(onumber, lnumber, item, price total)
PRIMARY KEY = (onumber, lnumber)
FOREIGN KEY = (onumber) REFERENCES ORDERS(onumber)

+---------+---------+-------+-------+-------+
| onumber | lnumber | item  | price | total |
+---------+---------+-------+-------+-------+
|       7 |       1 | bolt  | 23.04 |     5 |
|       7 |       2 | screw | 29.01 |     3 |
|       7 |       3 | nut   |  4.55 |     2 |
|       8 |       1 | bolt  | 23.04 |     1 |
|       8 |       2 | screw | 23.04 |     1 |
|       8 |       3 | nut   | 23.04 |     2 |
|       8 |       4 | lock  | 23.04 |     1 |
+---------+---------+-------+-------+-------+

Database Design Quality

So, ONE BIG TABLE is not a good solution if we would like to minimize the redundancies from the relational tables. An interesting question is why do we get the redundancies in incorrectly designed relational tables ?

To answer such question assume, that we have a relational table with colours and assume, that the following data dependencies are valid in the tables:

- whenever a value in COLUMN_1 is green then in the same row a value in COLUMN_2 is red

- whenever a value in COLUMN_1 is blue then in the same row a value in COLUMN_2 is yellow

- whenever a value in COLUMN_1 is orange then in the same row a value in COLUMN_2 is red

It is possible to generalize the data dependencies listed above into a single template:

- whenever a value in COLUMN_1 is x then in the same row a value in COLUMN_2 is year

If we know that such data dependency holds in every state of the relational table then practically there is no need to include COLUMN_2 in the relational table because each value in COLUMN_2 can be logically derived from a value in COLUMN_1.

Hence, there is no need to repeat the pairs (green,red), (blue,yellow) and (orange, red). It is why due to the different values in the related columns the redundancies are inevitable.

Where is a problem ?

Why do we get redundancies in an incorrectly designed relational table ?

Decomposition

Data dependencies:
- If COLUMN_1 is green then COLUMN_2 is red
- If COLUMN_1 is blue then COLUMN_2 is yellow
- If COLUMN_1 is orange then COLUMN_2 is red
For any colour x if COLUMN_1 is x then COLUMN_2 is y

To solve the problem we create a new table that consists of the COLUMN_1 and COLUMN_2 only and we remove COLUMN_2 from the original table.

Then the repetitions of pairs (green,red), (blue,yellow), and (orange, red) are no longer needed because the values in COLUMN_1 do not need to be associated with many other values in the remain columns.

The repetitions disappeared from the original table because COLUMN_2 has been removed from the original table.

It is still possible to restore the original table through join of the tables over an equality condition on COLUMN_1.

The repetitions of values in COLUMN_1 like green repeated 3 times, blue and orange repeated 2 times each are inevtable because of a tabular representation of data. As it has been mentioned earlier such redundancies can be removed through clustering of a relational table over COLUMN_1.

Where is a problem ?

Data dependencies can be represented as a separate relational table ...

Decomposition

... and COLUMN_2 can be removed from the original table

Decomposition

Where is a problem ?

Do data dependencies exist in BIG TABLE ?

	
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+
| 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 | screw | 29.01 |     3 |
|    |  7 | James | Bond  | |   | 7 | 2017-01-01| |       3 | nut   |  4.55 |     2 |
|    |    |       |       | |   +---------------+ |         |       |       |       |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       1 | bolt  | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       2 | screw | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       3 | nut   | 23.04 |     2 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       4 | lock  | 23.04 |     1 |
|    +----------------------+   +---------------+ |         |       |       |       |
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+

Data dependencies:
- If cnumber = 7 then fname = James
- If cnumber = 7 then lname = Bond
For any customer number x if cnumber = x then fname = y and lname = z

Where is a problem ?

Do data dependencies exist in BIG TABLE ?

	
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+
| 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 | screw | 29.01 |     3 |
|    |  7 | James | Bond  | |   | 7 | 2017-01-01| |       3 | nut   |  4.55 |     2 |
|    |    |       |       | |   +---------------+ |         |       |       |       |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       1 | bolt  | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       2 | screw | 23.04 |     1 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       3 | nut   | 23.04 |     2 |
|    |  7 | James | Bond  | |   | 8 | 2018-01-01| |       4 | lock  | 23.04 |     1 |
|    +----------------------+   +---------------+ |         |       |       |       |
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+

Data dependencies:
- If onumber = 7 then odate = 2017-01-01
- If onumber = 8 then odate = 2018-01-01

For any order number x if onumber = x then odate = y

Database Design Quality

Outline

Why not ONE BIG TABLE !?
Where is a problem ?
Functional dependency

Data dependencies like if customer number = 7 then first name of customer = James or if order number = 7 then order date = 2017-01-01 or if a value in COLUMN_1 is green then in the same row a value in COLUMN_2 is red are consistent with a common pattern if if a value in column A is x then a value in column B is always y. What sort of pattern it is ? It is the same pattern as when we say that if a value of x is 2 then a value of y is 4, If a value of x is 3 then a value of y is 9, if a value of x is 4 then value of y is 16 ... and if a value of x is a then a value fo y is a*a. It simply a function that maps the values in one set into the values in the other set.

If such data dependency is a function then it means that every value x in a column A is associated with only one value y in a column B. For example, every customer number in a column cnumber is associated with only one first name in a column fname, i.e. a customer has only one first name. Every value in a column student number is associated with one values in a column first name and with one value in a column last name. it means that each student has only one first name and one last name. Well, it is really a true reflection of the reality.

In the same way, every customer has only one last name, every order has only one date, every car has on registration number.

It is true that every part located in a given warehouse has only one quantity. It is because it would not be reasonable to say that in a warehouse XYZ we have 50 bolts in one corner and 40m bolts in another corner. we simply say that on a warehouse XYZ we have 90 bolts. It leads to a a function that maps the pairs of values (warehouse-name, part-name) onto integer numbers.

Functional dependency

What does it mean: if a value in column A is x then a value in column B is always y ?
It means that every value x in a column A is associated with only one value y in a column B
For example, every customer number in a column cnumber is associated with only one first name in a column fname, i.e. a customer has only one first name
For example, every customer number in a column cnumber is associated with only one last name in a column lname i.e. a customer has only one last name
For example, every order number in a column onumber is associated with only one order date in a column odate i.e. an order has only one date

Functional dependency

Such data dependency does not hold for item name and order number because an item name in a column item can be associated with many order numbers in a column onumber and the opposite ...
... an order number in a column onumber can be associated with many item names in a column item

If in a general case every value in a column A is associated with only one value in a column B then it means that the columns A and B represent a function f that maps the values in a column A into the values in a column B. Such such fact is denoted as:

f : domain(A) → domain(B)

where domain(A) and domain(B) are the sets of all attributes in the columns A and B respectively.

If every value in a column cnumber is associated with only one value in a column fname then the columns cnumber and fname represent a function that maps the values in a column cnumber into the values in a column fname. Such fast is denoted as:

f : domain(cnumber) → domain(fname)

where domain(cnumber) and domain(fname) are the sets of all attributes in the columns cnumber and fname.

If every value in a column cnumber is associated with only one value in a column lname then the columns cnumber and lname represent a function that maps the values in a column cnumber into the values in a column lname. Such fact is denoted as:

f : domain(cnumber) → domain(lname)

where domain(cnumber) and domain(lname) are the sets of all attributes in the columns cnumber and lname.

Functional dependency

If every value in a column A is associated with only one value in a column B then it means that the columns A and B represent a function f that maps the values in a column A into the values in a column B

f : domain(A) → domain(B)

If every value in a column cnumber is associated with only one value in a column fname then the columns cnumber and fname represent a function

f : domain(cnumber) → domain(fname)

If every value in a column cnumber is associated with only one value in a column lname then the columns cnumber and lname represent a function

f : domain(cnumber) → domain(lname)

If every value in a column onumber is associated with only one value in a column odate then the columns onumber and odate represent a function that maps the values in a column onumber into the values in a column odate. Such fact is denoted as:

f : domain(onumber) → domain(odate)

where domain(onumber) and domain(odate) are the sets of all attributes in the columns onumber and odate.

In a general case, if the columns A and B in a relational table R represent a function f : domain(A) → domain(B)

then in the future it will be denoted by A → B and we shall say that a functional dependency A → B is valid in a relational table R or that A functionally determines B.

In the other words if A → B then it means that every value in a column A is associated with exactly one value in a column B. For example in a relational table:

A | B | C
--------------
1 | 2 | 3
1 | 2 | 4
2 | 1 | 4
it is true that:
A → B
B → A
B,C → A
A,C → B

it is NOT true that A → C, it is NOT true that B → C, it is NOT true that C → B, it is NOT true that C → A.

Functional dependency

If every value in a column onumber is associated with only one value in a column odate then the columns onumber and odate represent a function

f : domain(onumber) → domain(odate)

If the columns A and B in a relational table R represent a function

f : domain(A) → domain(B)

then in the future it will be denoted by

A → B

and we shall say that a functional dependency A → B is valid in a relational table R or that A functionally determines B

Functional dependency

Therefore, the following functional dependencies are valid in a big table CUSTOMER:

cnumber → fname

cnumber → lname

onumber → odate

onumber → cnumber

onumber → fname, lname

onumber, lnumber → item

onumber, lnumber → price, total

... and the others

"Functional dependency" is a special kind of so called "data dependency" which is a reflection of the real world consistency constraint. if a database is a description of a selected fragment of the reality then like many other data dependencies, functional dependency is valid in the contents of the correctly recorded data.

Functional dependencies can be used to represent the semantics (meaning) of data. For example, is it possible to discover any functional dependencies in a relational table R(a, b, c) ? It is not because we do not know the semantics (meanings) of a relational table name R and the column A, B and C. If we replace the names R, A, B and C with the meaningful names like COURSE, code, title, credits then it is possible use the semantics of the names COURSE, code, title credits to discover functional dependencies:

code → title

code → credits

title → credits

title → code

Such configuration of functional dependencies enforce the semantics where each corse has one code, one title and one total number of credist and it never happens that two or more courses have the same title and different code.

If functional dependencies describe data semantics then functional dependencies can be used to determine whether a relational schema (header of relational table) is constructed in a correct way.

It is also possible to use functional dependencies can be used to design a database, however such approach is used very rarely. In a database design methodology based on analysis of functional dependencies, we first collect all attributes. Then we discover the functional dependencies among the attributes and than we synthesize the relational schemas that validate certain selected sets of functional dependences. it is so called "synthesis approach" to database design based on analysis of functional dependencies. The other methodology we assume the existence of one relational schema that consists of all attributes and we decompose such schema into smaller schemas that validate certain select sets of functional dependencies. It is so called decomposition approach to database design based on analysis of functional dependencies.

In this lecture we shall use the decomposition approach to prove the correctness of the relational schemas and to decompose incorrectly design relational schemas into the correct ones.

Functional dependency

Functional dependency is a special kind of so called data dependency which is a reflection of the real world consistency constraint
Functional dependencies can be used to describe the semantics (meaning) of data
Functional dependencies can be used to determine whether a relational schema (header of relational table) is constructed in a correct way
Functional dependencies can be used to design a database, however such approach is used very rarely

References

T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 14.1 The Purpose of Normalization, Chapter 14.2 How Normalization Supports Database Design, 14.3 Data Redundancies and Update Anomalies, Pearson Education Ltd, 2015