07DatabaseDesignQuality

Database Design Quality

Outline

Why not ONE BIG TABLE !?
Where is a problem ?
Insertion test
Good design guidelines

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

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 !?

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)

Why not one relational schema ?


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

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 !?

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 |
|    +----------------------+   +---------------+ |         |       |       |       |
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+

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

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 |
|    +----------------------+   +---------------+ |         |       |       |       |
+---------+-------+-------+---------+-------------+---------+-------+-------+-------+

More 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 ?
Insertion test
Good design guidelines

The reason of the redundancies occurring in BIG TABLE is insertion into such table incorrect columns like first and last names of of customers and dates of orders. These columns should be included into the separate relational tables.

An interesting question is how to verify if a relational schema is designed in a correct way ? In the other word how to check if a relational schema does not contain the attributes that trigger the redundancies in the respective relational table. It is possible to formally prove that a relational schema is constructed in correct way through so called "normalization of relational schema". A formal prof is beyond a scope of this subject and here we shall only use an intuitive verification of correctness through so called "insertion test".

We start from inserting of a single row into a relational table, any row we can imagine, it really does not matter what are the values of the attributes in the first inserted row. For example, we insert information about building number, room number, and area of a room:

[3, SCIT, 210,20]

Then, we insert the second row that contains information about another room in the same building. To trigger the redundancies we try to insert a new row such that as many as it is possible values are the same as in the first row. if we insert information about another room in the same building then building number and building name can be the same in the second row as in the first one:

[3, SCIT,211,22]

Now, we can see that a pair o values [3,SCIT] is repeated as many times as many rooms are located in a building 3. It means, we discovered a data dependency of the same sort as before:

If building number is equal to 3 the building name is always equal to SCIT.

In a general case if a building number is equal to x then building name is always equal to y.

To make it completely sure, we insert information about one more broom in building 3:

[3, SCIT,213,20]

Insertion test

How to verify if a relational schema is designed in a correct way ?
Simple ! We try to insert few rows to create the redundancies

For example, we insert few rows into a relational table ROOM

ROOM(bldgnum, name, roomnum, area)
PRIMARY KEY = (bldgnum, roomnum)

+---------+----------+---------+------+
| bldgnum | bldgname | roomnum | area |
+---------+----------+---------+------+
|       3 | SCIT     |     210 |   20 |
+---------+----------+---------+------+

+---------+----------+---------+------+
| bldgnum | bldgname | roomnum | area |
+---------+----------+---------+------+
|       3 | SCIT     |     210 |   20 |
|       3 | SCIT     |     211 |   22 |
+---------+----------+---------+------+

+---------+----------+---------+------+
| bldgnum | bldgname | roomnum | area |
+---------+----------+---------+------+
|       3 | SCIT     |     210 |   20 |
|       3 | SCIT     |     211 |   22 |
|       3 | SCIT     |     213 |   20 |
+---------+----------+---------+------+

A design problem we discovered through the "insertion test"is such that a building name is repeated as many times as many rooms are included in a building. For example, if a building hosts 100 rooms then its name is repeated 100 times.

It means that an attribute building name cannot be included in a relational schema that contains information about the rooms located in a building. To eliminate such problem we have to remove an attribute building name from the schema.

Of course one may say, well, I got such relational schema from a logical design, i.e. from a process of transformation of a class of objects into relational schemas. Does it mean that a class of objects has been constructed in an incorrect way ? Yes, it precisely means so. In a moment when a designer created a class of objects he/she used an incorrect attribute (building name) to describe a class of objects. The attributes used to describe a class of objects are selected in an intuitive way mainly based on our understanding of a modelled database domain. If our understanding is not correct or not complete it is possible to use a wring attribute to describe a class of objects.

Insertion test

+---------+----------+---------+------+
| bldgnum | bldgname | roomnum | area |
+---------+----------+---------+------+
|       3 | SCIT     |     210 |   20 |
|       3 | SCIT     |     211 |   22 |
|       3 | SCIT     |     213 |   20 |
+---------+----------+---------+------+

Problems:
- It is clearly visible that a building name is repeated as many times as many rooms are included in a building

In another example we consider a relational schema:

WAREHOUSE(name, address, part, quantity)

A relational table based on such schema contains information about names of warehouses, addresses warehouses, names and quantities of parts stored in the warehouse.

We ask the same question as before: is the relational schema WAREHOUSE constructed in a correct way ?

To answer the question we insert few rows into a relational table with information about warehouses and parts stored in each warehouse. We start from a row:

[Golden Bolts, Northfields Ave, bolt, 210]

Then like before we try to insert the next row such that as many values as possible are the same as in the first row. For example we insert information about a name and quantity of part located in the same warehouse:

[Golden Bolts, Northfields Ave, lock, 20]

Again we see that addition of information about a new part located in the sam warehouse triggers a repetition of a warehouse address. It means, that if add information about more parts located in the same warehouse then information about warehouse address will be repeated with information about each part.

For example, addition of one more row:

[Golden Bolts, Northfields Ave, screw, 211]

triggers one more repetition of the same address.

Insertion test

In another example, we insert few rows into a relational table WAREHOUSE

WAREHOUSE(name, address, part, quantity)
PRIMARY KEY = (name, part)

+--------------+-----------------+-------+----------+
| name         | address         | part  | quantity |
+--------------+-----------------+-------+----------+
| Golden Bolts | Northfields Ave | bolt  |      210 |
+--------------+-----------------+-------+----------+

+--------------+-----------------+-------+----------+
| name         | address         | part  | quantity |
+--------------+-----------------+-------+----------+
| Golden Bolts | Northfields Ave | bolt  |      210 |
| Golden Bolts | Northfields Ave | lock  |       20 |
+--------------+-----------------+-------+----------+

+--------------+-----------------+-------+----------+
| name         | address         | part  | quantity |
+--------------+-----------------+-------+----------+
| Golden Bolts | Northfields Ave | bolt  |      210 |
| Golden Bolts | Northfields Ave | lock  |       20 |
| Golden Bolts | Northfileds Ave | screw |      211 |
+--------------+-----------------+-------+----------+

Insertion test

+--------------+-----------------+-------+----------+
| name         | address         | part  | quantity |
+--------------+-----------------+-------+----------+
| Golden Bolts | Northfields Ave | bolt  |      210 |
| Golden Bolts | Northfields Ave | lock  |       20 |
| Golden Bolts | Northfileds Ave | screw |      211 |
+--------------+-----------------+-------+----------+

Problems:
- An address of a warehouse is repeated as many times as many different parts are stored in the warehouse
- If at some point in time, there are no parts stored in a warehouse then there may be no rows to keep a warehouse address or the values of certain attributes must be set to NULL

In a yet another example of incorrect design we consider a relational schema EMPLOYEE(enum, skill, hobby). A relational table with a header EMPLOYEE(enum, skill, hobby) suppose to contain information about employee number, kills possessed by an employee and hobbies of an employee.

To verify whether the design is correct we insert a number of rows with information about an employee who possesses two skills and who has two hobbies. Due to the 1NF assumption we can only insert into the columns skill and hobby one value per row. We start from insertion of information about a fact saying, that an employee number 7 possess a skill cooking and a hobby hiking. Such information is represented as a single row:

[7, cooking, hiking]

Next, we add information about a new hobby "swimming" possessed by an employee number 7. Such information is represented by a single row:

[7, cooking, swimming]

Now, we get the first signal that the design is incorrect, Information about a skill "cooking" possessed by an employee number 7 is repeated twice in the first two rows.

Next, we insert information about a new skill "programming" possessed by an employee number 7. So, initially we add a row:

[7, programming, hiking]

The, we have to to add a row:

[7, programming, swimming]

because a skill "swimming" does not depend on the hobbies of the employee. Therefore, to add a new piece of information we have to add two rows.

Such effect is called "tuples explosion". If an employee has 5 skills and 3 hobbies than we need to add 5 *3 = 15 rows. Now, it is clear why it is called "tuples explosion".

The design is incorrect because two unrelated mutivalued attributes are incorrectly transformed into the single valued attribute in a process of logical design. It is an evident mistake performed during a stage of logical design.

Insertion test

In yet another example, we insert few rows into a relational table EMPLOYEE

EMPLOYEE(enum, skill, hobby)
PRIMARY KEY = (enum, skill, hobby)

+------+-------------+----------+
| enum | skill       | hobby    |
+------+-------------+----------+
|    7 | cooking     | hiking   | 
+------+-------------+----------+

+------+-------------+----------+
| enum | skill       | hobby    |
+------+-------------+----------+
|    7 | cooking     | hiking   |
|    7 | cooking     | swimming |
+------+-------------+----------+

+------+-------------+----------+
| enum | skill       | hobby    |
+------+-------------+----------+
|    7 | cooking     | hiking   |
|    7 | cooking     | swimming |
|    7 | programming | hiking   |
|    7 | programming | swimming |
+------+-------------+----------+

We discover the following problems with redundancies in a relational table based on a relational schema EMPLOYEE(enum, skill, hobby):

- a skill name must be repeated with each hobby name

- a hobby name must be repeated with each skill name

- If at some point in time an employee has no hobbies (or skills) then a value of an attribute hobby (or skill) must be set to NULL, however, it is impossible due to PRIMARY KEY = (enum, skill, hobby) constraint.

A solution to the problem of "tuples explosion" is quite simple. We decompose the original relational schema EMPLOYEE(enum, skill, hobby) into two schemas: EMPLOYEE-SKILL(enum, skill) with primary key = (enum, skill) and EMPLOYEE-HOBBY(enum,hobby) with primary key = (enum, hobby). Such decomposition is correct because do not exists any semantic relationships, that must be stored in a database, between skills possessed and hobbies owned by an employee.

Insertion test

+------+-------------+----------+
| enum | skill       | hobby    |
+------+-------------+----------+
|    7 | cooking     | hiking   |
|    7 | cooking     | swimming |
|    7 | programming | hiking   |
|    7 | programming | swimming |
+------+-------------+----------+

Problems:
- A skill name must be repeated with each hobby name
- A hobby name must be repeated with each skill name
- If at some point in time an employee has no hobbies (or skills) then a value of an attribute hobby (or skill) must be set to NULL, however, it is impossible due to PRIMARY KEY = (enum, skill, hobby) constraint

Database Design Quality

Outline

Why not ONE BIG TABLE !?
Where is a problem ?
Insertion test
Good design guidelines

An insertion test is a pretty powerful tool that can be used to informally verify the correctness of a relational design. Apart from verification of an already existing design application of "good design guidelines" significantly reduces a chance for a mistake in a design.

The "good design guidelines" are the following:

- design a relational table such that it is easy to explain its meaning

- do not include the unrelated attributes into the same table

- design a relational table such that the insertion, deletion and update tests do not cause anomalies

- minimize the total number of attributes whose values can be missing (NULL)

- design the relational tables such that they can be joined over an equality conditions on the attributes that are either primary or foreign keys in a way that creates no spurious rows

-and the first of all ...

ALWAYS START YOUR DESIGN FROM THE CONCEPTUAL MODELING !

Good design guidelines

Design a relational table such that it is easy to explain its meaning
Do not include the unrelated attributes into the same table
Design a relational table such that the insertion, deletion and update tests do not cause anomalies
Minimize the total number of attributes whose values can be missing (NULL)
Design the relational tables such that they can be joined over an equality conditions on the attributes that are either primary or foreign keys in a way that creates no spurious rows
And the first of all ...
ALWAYS START YOUR DESIGN FROM THE CONCEPTUAL MODELING !

References

T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation and Management, Chapters 14.1 - 14.3 Introduction to normalization, Pearson Education Ltd, 2015