Concepts of Database Design and Management

It’s not difficult to find an online tutorial on creating a database. It’s not hard to find a tutorial on how to create queries. In fact, it’s not hard to find tutorials on syntax for SQL, the differences of data types, and even which database is best for what purposes. It is, however, difficult to find good tutorials on the theories of designing, developing, and maintaining a quality database.

Database design and management isn’t very difficult. People much wiser than we have designed some very orderly and sound rules to follow and developed these rules into what is called the Normalization Process.

Using this process, you can create brand new, fully functional, finely tuned databases or take current database tables, run them through these steps, and come out with well-oiled tables ready to fly. However you use these steps, they are the fundamentals of quality database design.

Functional Dependence

Before we jump into the Normalization Process, I should take a step back and clear a few things up. First, this is not specific to any one type of database. These are rules that should be followed when using any database system, whether it is Oracle, MySQL, PostgreSQL, SQL Server, etc.

Let us first discuss Functional Dependence, which is crucial in understanding the Normalization Process. This is merely a big term for a relatively simple idea. To illustrate it, lets take a look at a small sample table.

Sales Rep Number	Pay Class	Rate
001	1	.05
002	1	.05
003	1	.05
004	2	.07
005	1	.05
006	3	.09

This relatively simple table is a good example of functional dependence, it can also be used to illustrate a point.

Definition: A column is functionally dependent on another column if a value ‘A’ determines a single value for ‘B’ at any one time.

Sound confusing? Let me explain. The field ‘Rate’ is functionally dependent on the field ‘Pay Class’. In other words, Pay Class determines Rate.

To determine functional dependency, you can think of it like this: Given a value for Field A, can you determine the single value for B? If B relies on A, then A is said to functionally determine B.

Taking the same table as above, lets add to it.

Name	Sales Rep Number	Pay Class	Rate
Ward	001	1	.05
Maxim	002	1	.05
Cane	003	1	.05
Beechum	004	2	.07
Collins	005	1	.05
Cannery	006	3	.09

Now, lets look at this table and find some more Functional Dependencies. We already know that Pay Class determines Rate. We can also say that Sales Rep Number determines Last Name. Only one Sales Rep Number for each Last Name. This fits the definition of a Functional Dependency.

But does Last Name functionally determine anything? At first glance, some people might say yes, however, this is not true. Currently, you can say that Ward will only give you one Sales Rep Number, however, what if we hired another person with the name Ward? Then you would have two values for your Sales Rep Number, and then Last Name would no longer functionally determine anything.

On Keys

Now that we know what functional dependence is, we can clarify keys. Now, if you are working in databases, you probably already know what Primary Keys are. But, can you define them?

Definition: Column A is the primary key for table T if:
Property 1. All columns in T are functionally dependent on A
Property 2. No sub collections of the columns in table T also have Property 1.

This makes perfect sense. If all your fields in a database are dependent on one and only one field, then that field is the key. Now, occasionally Property 2 is broken, and two fields are candidates for the Primary Key. These keys are then called candidate keys. From these candidate keys, one key is chosen and the others are called alternate keys.

For example, in the same table as before:

Name	Sales Rep Number	Pay Class	Rate
Ward	001	1	.05
Maxim	002	1	.05
Cane	003	1	.05
Beechum	004	2	.07
Collins	005	1	.05
Cannery	006	3	.09

Our primary key is the Sales Rep Number, as it fits the definition of a Primary Key. Everything in the table is dependent on the Sales Rep Number, and nothing else can claim the same thing. Now, let us take this one step further, and assume that we also have the Social Security number of the employee in the table as well.

Name	Sales Rep Number	Pay Class	Rate	Soc.Sec. no.
Ward	001	1	.05	133-45-6789
Maxim	002	1	.05	122-46-6889
Cane	003	1	.05	123-45-6999
Beechum	004	2	.07	113-75-6889
Collins	005	1	.05	121-44-6789
Cannery	006	3	.09	111-45-9339

Now, we have two Candidate Keys, Sales Rep Number and Social Security Number. So, what we have to decide is which field to use, as both will be unique. In the end, it would be best to have the Sales Rep Number as the Primary Key for various reasons.

First Normal Form

Now that we have clarified these concepts, lets move into the Normalization Process. The First Normal Form is defined as a table that does not contain repeating groups.

For example:

Order Number	Order Date	Part Number	Number Ordered
5245	3/02/99	35436	23
5246	3/02/99	32116	11
5247	4/02/99	32133	52
–	–	12311	10
5248	4/02/99	46563	1

Now, from that example, you should easily see the problem. First off, in this setup, Order Number is considered the Primary Key, but this is not entirely true. The true Primary Key is Order Number and Part Number. In every order, a part will only ever be ordered once (though the amount ordered can be greater than 1). However, multiple parts can be ordered in on Order Number. So, when we retool this table, we end up getting this:

Order Number	Order Date	Part Number	Number Ordered
5245	3/02/99	35436	23
5246	3/02/99	32116	11
5247	4/02/99	32133	52
5247	4/02/99	12311	10
5248	4/02/99	46563	1

This is much easier to understand, and is a much better design then the previous table. And this time, the Primary Key is both the Order Number and Part Number, and everything else in the table depends on that key.

This was the First Norm Form, or 1NF. Most people can do this right, however, I have seen some tables designed without this in mind, and for a while, it looks like things are all right, but when you start filling the table up with lots of information, it can become quite cumbersome. Definitely, not a shining point in someone’s career, but a most valuable lesson indeed.

Second Normal Form

First, lets just say this. The table is automatically 2NF if its Primary Key contains only one column. That was easy, wasn’t it? But then, if your Primary Key has more than one column, read on.

Lets jump right into the table here. Primary Keys are Order Number (Order #) and Part Number (Part #).

Order #	Order Date	Part #	Part Desc.	Number Ordered	Price
5245	3/02/99	35465	Gas Key	1	$10
5246	3/02/99	65466	Lawn Chair	4	$25
5246	3/02/99	65473	Picnic Table	1	$30
5247	3/02/99	44654	Lawn Darts	1	$45
5248	4/02/99	44665	Volleyball	3	$20

At first glance, everything is okay with this table. However, there are some problems that need to be resolved. Lets first map out the functional dependencies.

Order Number -> Order Date

Part Number -> Part Description

Order Number, Part Number -> Number Ordered, Price, Order Date, Part Description

As you can see, this is a big mess, and needs to be taken care of. In fact, there are other problems with this table as well.

First, a change to the Part Description in this table would also mean a change in other tables, like the Products table. This is obviously cumbersome to code, and cumbersome to run.

Second, you could very well have different descriptions for the same part. This is simply not acceptable in a business environment.

This is because Part Description is dependent on only part of the primary key, and that is Part Number. Part Description is therefore a nonkey attribute. A nonkey attribute is simply a column that is not a part of the primary key.

This relates specifically to the 2NF definition, which is a table that is in the 1NF and no nonkey attribute is dependent on only a portion of the primary key. This of course reaffirms the fact that if the table only contains one column in its primary key, it is in second normal form.

So, how do we solve this problem? Well, we already solved this problem above. Here are the three new tables for the one above, with the first part being the primary keys and the second part being the other fields in the table.

Order Number -> Order Date

Part Number -> Part Description

Order Number, Part Number -> Number Ordered, Price

Those are our new tables. Notice that I took out the Order Date and the Part Description, as there is no need to use data that is already stored in other tables. Also note that you only have one description for each part. Quality database design needs no redundant data.

Third Normal Form

So, you think your table is clear now. You think it is ready for the big leagues? No…not yet. The 3NF is ready to take on new challenges. Lets go right to our example table:

Customer #	Name	Address	Credit	Sales Rep #	Sales Rep Name
2343	Bob	2343 Wee St.	$1000	03	Dave
2344	Daniel	1230 Wow St.	$3000	04	Pam
2345	Kate	33 Soho	$5000	03	Dave
2346	Less	99 More Is	$1000	07	Garrett
2347	Dan	1 Less St.	$500	09	Peter
2348	Bruno	45 Candy Ln.	$2000	03	Dave

Now, you probably recognize a few problems, but be assured, this is 2NF. Every field depends on the Customer Number. For example, Customer Number 2345 will only have one name, address, credit, sales rep number, and sales rep name associated with it. However, this doesn’t mean the table is ready for the prime time.

First, we need to define a determinant as any column or collection of columns that determine another column. By this definition, and primary key, or any candidate key will be a determinant. Also, that would make Sales Rep Number a determinant, but it’s not the candidate key or a primary key.

So, how does this relate to the 3NF? Well, the definition of the 3NF is a table that complies with the 2NF (and of course, the 1NF) and if the only determinants it contains are candidate keys. This does, of course, include the primary key.

Now, you have just discovered the problem with the table: the determinant of Sales Rep Number. So how do you go about getting rid of it?

First, track down all the determinants that are NOT a candidate key. Then, remove all the fields that rely on this determinant key, but keep the determinant key in the table. Then, with all the fields you removed, put them into a table, with the primary key being the determinant key you left in the main table. So, that would mean our original table of:

Customer #, Name, Address, Credit, Sales Rep #, Sales Rep Name

Would be turned into these two tables:

Customer #, Name, Address, Credit, Sales Rep #
Sales Rep #, Sales Rep Name

Quality.

Remember, these two new tables need to comply with 1NF, 2NF, and 3NF as well, so double check them, though if you are up to this point, you should be doing okay.

Fourth Normal Form

Finally, we are up to the big one. 4NF is the father of the forms, as it is the be all and end all. This takes care of any problem that may occur. Lets start this time by defining a very important term, multivalued dependence (MD). MD is when field B is multidependent on A if each value of A is associated with a specific list of values for B, and this collection is independent of any values of C.

Lets take a deep breath, and illustrate this with a simple table. Lets assume that Faculty represents the guidance counselor for any number of students represented by Student Number. Also, we can assume that the faculty member will be a part of any number of committees, and we want to store this as well. So, this is what we come up with.

Faculty	Student Number	Committee Code
1243	2343	ADV
Â	2345	PER
Â	Â	HSG
1553	3243	ADV
4003	3408	HSG
Â	4095	Â
Â	4403	Â

Now, passing this to 1NF would give you this result:

Faculty	Student Number	Committee Code
1243	2343	ADV
1243	2345	PER
1243	2345	HSG
1553	3243	ADV
4003	3408	HSG
4003	4095	HSG
4003	4403	HSG

But, you can clearly see the problem developing there as well. This is what MD is. If Faculty number 1243 was no longer counseling Student Number 2345, and we deleted the data, then the Committee information would be deleted as well. This is not quality.

A 4NF is 3NF compliant, and there are no multivalued dependencies.

You would handle this problem like in 2NF and 3NF, by splitting the table up into smaller tables with each containing the field that multidetermined them, and in this case, that would be the Faculty field. So, the new tables would be:

Faculty, Student Number
Faculty, Committee Code


Conclusion
Truly, that was a lot of work, but it is well worth it. Making sure your tables are optimized is key to quality design. Taking care of the problem before it becomes a problem is vital to managing a database.

Before you sit down to design the database, gather all the information you want to include in the database. I mean everything. Go around to each department of the company (or just write it out yourself if this is just for you) and find out what everyone wants in the database. Once you have everything, bring it back, and create one huge table.

From there, break that table down to 1NF, then 2NF, and so on. Go back over each table, and make sure they all work together, and are all 4NF tables. If they aren’t, then it can be assured the tables will suffer problems in the future.

Quality is in the design. And for those people who know, this helps comply with Codd’s first 2 rules for a truly relational database system.

Happy coding.

Frequently Asked Questions (FAQs) on Database Design and Management