Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Normalization

Re: Normalization

From: Jan Hidders <hidders_at_wsinis12.win.tue.nl>
Date: 2000/08/03
Message-ID: <8mbpq5$4nb$1@news.tue.nl>#1/1

On 02 Aug 2000 00:57:47 GMT, GrySpr <gryspr_at_aol.com> wrote:
>I have tried to pick up on the idea of normalization, but still do not
>understand. Can anyone give me some imput that would make understanding it
>easier?

The basic idea of normalization is to store every fact in only one row of one table. Let me give you a small example. Assume we have a database with the following two tables:

Follows([Student, Course]) Given-by([Course], Teacher)

The Follows-table stores which students take which course and the Given-by-table stores which teachers teach which course. (The square brackets indicate a key of the table.) As you can see the two tables record independent facts and every row in them represents one fact. So this is probably a good design. But lets see what happens if we decide, for some reason, to represent these two facts in one table:

School([Student, Course], Teacher)

In this table every row will represent two different facts. This causes two problems. First, we see that the fact that a certain teacher teaches a certain course is repeatedly stored for every student that follows the course. Second, there is the problem that it may not be possible to assign a teacher to a certain course if there is not yet a student that follows this course (unless we use some trick such as NULL-values). Obviously the same problem occurs if we would like to add a student to a course that does not yet have any teachers assigned to it.

So is it always a problem if a row represents multiple facts? No, it isn't. Consider for example the following table:

Person([Name], Address, Date-of-Birth)

This table also records two facts in one row: the address of a certain person, and the date of birth of a certain person. But it is clear that every fact is represented in only one record.

So when is it a problem? Well, if you want to know what facts are represented in a table you can look at the functional dependencies in the table. In the School-table we see, for instance, the dependency Course --> Teacher. This corresponds with the fact that for every course there is (or is not) a teacher assigned. Normalization theory then tells us that this fact will cause problems if its left-hand side is not equal to some key of the table. As you can see this is the case for the School-table and the solution is simply to give this fact its own table and remove it form the original table. This will then result in the two tables that I presented at the beginning.

And that is really all there is to the first four normal forms (1NF, 2NF, 3NF and BCNF); if you continue by giving every functional dependency that causes problems its own table then you will bring your tables in BCNF. If you want to go further then you must also check the facts that correspond with multi-valued dependencies and join dependencies.

I think this should give you some idea of what normalization is about. If you have any questions, just let me know.

Kind regards,

Received on Thu Aug 03 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US