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.