Normalisation

Hi freinds,

pls contribute any idia on;

what is the difference between two normal forms (1NF,2NF)

There are many equivalent definitions for normal forms. Here is a version:

1NF
Each table must have a primary key, i.e., a minimal set of attributes that can uniquely identify a record.There must be atomicity i.e each attribute must contain a single value, not a set of values.There should be no repeating groups (categories of data that would seem to be required a different number of times on different records) by defining keyed and non-keyed attributes appropriately.

2NF
The database must meet all the requirements of the 1NF. In addition, if a table has a composite key, all attributes must be related to the whole key. And, data that is redundantly duplicated across multiple rows of a table should be moved out to a separate table.

Hi freinds,

pls contribute any idia on;

what is the difference between two normal forms (1NF,2NF)

Alem

Check out the following

Mary

First Normal Form

• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data
• Identify each set of related data with a primary key

Do not use multiple fields in a single table to store similar data.




Second Normal Form

• Create separate tables for sets of values that apply to multiple records
• Relate these tables with a foreign key

Records should not depend on anything other than a table's primary key (a compound key, if necessary). For example, consider a customer's address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Alem, on a point of board etiquette, I notice that you title all of the threads you start with your user name (Alem). It would be better for all concerned if you could make your thread titles an short description of the problem you are having.

It will also make the experts on the board more inclined to have a look at your threads.

mmccarthy/r035198x

Are there any other normal forms. This has direct relevence to my work (where I have been designing a database for the past 6 months).

However I kind of new NF1 and NF2, seem like common sense to me (they come directly from the programming equivilent 'no magic numbers' which basically states that you should only store data in your program 1 place then you are less likely to make a mistake if you need to change it).

Got any good links to references about this subject?

mmccarthy/r035198x

Are there any other normal forms. This has direct relevence to my work (where I have been designing a database for the past 6 months).

However I kind of new NF1 and NF2, seem like common sense to me (they come directly from the programming equivilent 'no magic numbers' which basically states that you should only store data in your program 1 place then you are less likely to make a mistake if you need to change it).

Got any good links to references about this subject?

The next one is 3NF and then you have BCNF (Boyce-Codd Normal Form)

3NF

A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key.

The process of transforming a table into 3NF is:

1. Identify any determinants, other the primary key, and the columns they determine.
2. Create and name a new table for each determinant and the unique columns it determines.
3. Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.
4. Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.
5. The original table may be renamed to maintain semantic meaning.

The advantage of having relational tables in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies.

BCNF

A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. BCNF is very similar to 3NF but deals with dependencies within the primary keys.

Most relations that are in 3NF are also in BCNF. It only happens that a relation which is in 3NF is not in BCNF when:
(a) the candidate keys in the relation are composite keys (that is, they are not single attributes),
(b) there is more than one candidate key in the relation, and
(c) the keys are not disjoint, that is, some attributes in the keys are common.

The BCNF differs from the 3NF only when there are more than one candidate keys and the keys are composite and overlapping.

Although 4NF, 5NF and even 6NF do exist they are not normally referred to.

A database is said to be normalised if it is in 3NF and/or BCNF.

MAry

The next one is 3NF and then you have BCNF (Boyce-Codd Normal Form)

3NF

The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:

A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key.

The process of transforming a table into 3NF is:

1. Identify any determinants, other the primary key, and the columns they determine.
2. Create and name a new table for each determinant and the unique columns it determines.
3. Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.
4. Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.
5. The original table may be renamed to maintain semantic meaning.

The advantage of having relational tables in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies.

BCNF

A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. BCNF is very similar to 3NF but deals with dependencies within the primary keys.

Most relations that are in 3NF are also in BCNF. It only happens that a relation which is in 3NF is not in BCNF when:
(a) the candidate keys in the relation are composite keys (that is, they are not single attributes),
(b) there is more than one candidate key in the relation, and
(c) the keys are not disjoint, that is, some attributes in the keys are common.

The BCNF differs from the 3NF only when there are more than one candidate keys and the keys are composite and overlapping.

Although 4NF, 5NF and even 6NF do exist they are not normally referred to.

A database is said to be normalised if it is in 3NF and/or BCNF.

MAry

In practice, of course, people rarely go as far as BCNF.

In practice, of course, people rarely go as far as BCNF.

We're lucky sometimes if they go as far as 3NF.

Mary

We're lucky sometimes if they go as far as 3NF.

Mary

I thought you were the database expert?

I thought you were the database expert?

I'm the one who spends half my time fixing databases that aren't fully normalised.

I'm the one who spends half my time fixing databases that aren't fully normalised.

When I first learnt databases there was this thing that DBMSs were being judged by their degree of normalisation. Then it seemed to just dissappear. I don't blame DBMS producers that much though (naturally being a programmer). Most of the time the functionality required is usually achieved and many companies have requirements that make it impossible to fully normalize their data.

When I first learnt databases there was this thing that DBMSs were being judged by their degree of normalisation. Then it seemed to just dissappear. I don't blame DBMS producers that much though (naturally being a programmer). Most of the time the functionality required is usually achieved and many companies have requirements that make it impossible to fully normalize their data.

The problem is if you don't fully normalise the data then future development is very difficult even if you can achieve the required functionality for the moment.

Although Normalisation can be difficult due to certain circumstances it's always achieveable. You just have to understand how to make some leaps in logic. Although I've come accross business requirements that make normalisation difficult I've never come accross one that made it impossible.

Mary

The problem is if you don't fully normalise the data then future development is very difficult even if you can achieve the required functionality for the moment.

Although Normalisation can be difficult due to certain circumstances it's always achieveable. You just have to understand how to make some leaps in logic. Although I've come accross business requirements that make normalisation difficult I've never come accross one that made it impossible.

Mary

True. Most of the lack of normalisation is due to laziness on the developer's part rather than prohibitive requirements. But the time pressure sometimes ...

True. Most of the lack of normalisation is due to laziness on the developer's part rather than prohibitive requirements. But the time pressure sometimes ...

I usually find spending extra time on table structure makes life so much easier when developing queries and code. It usually balances out.

I usually find spending extra time on table structure makes life so much easier when developing queries and code. It usually balances out.

The more time one takes designing the easier it is to write the code.

So simple and elementary, yet so seldom practiced.

The more time one takes designing the easier it is to write the code.

So simple and elementary, yet so seldom practiced.

I learned my lesson the hard way. Many years ago due to time constraints I didn't spend much time on the table design and ended up wasting weeks of effort on code that should have been so simple.

Now days I can do the Normalisation almost roboticaly (no pun intended) as I've done it so many times that I often don't have to think about what I'm doing.

Mary

I learned my lesson the hard way. Many years ago due to time constraints I didn't spend much time on the table design and ended up wasting weeks of effort on code that should have been so simple.

Now days I can do the Normalisation almost roboticaly (no pun intended) as I've done it so many times that I often don't have to think about what I'm doing.

Mary

Perhaps there was no pun.

I simply stay as far away from databases as possible.

Perhaps there was no pun.

I simply stay as far away from databases as possible.

Each to their own. I quite like them most of the time.

Mary

Each to their own. I quite like them most of the time.

Mary

Unfortunately it's not always possible to stay away. Right now I'm designing tables required for the next bit of the project I'm working on.

Unfortunately it's not always possible to stay away. Right now I'm designing tables required for the next bit of the project I'm working on.

Let me know if I can help.

Let me know if I can help.

Kind of you. Almost through though. Not many foreign keys too...

3NF

The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:

A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key.

The process of transforming a table into 3NF is:

1. Identify any determinants, other the primary key, and the columns they determine.
2. Create and name a new table for each determinant and the unique columns it determines.
3. Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.
4. Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.
5. The original table may be renamed to maintain semantic meaning.

The advantage of having relational tables in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies.

Any chance you can give a simple before and after example of the 3NF process?

Any chance you can give a simple before and after example of the 3NF process?

http://www2.yk.psu.edu/~lxn/IST_210/...finitions.html

One of the big advantages of databases in the system I am working on is that I am and programming and communications specialist, I can write the code to perform the communications and put the data into the database (and read configuration changes from the database).

Once the data is in the database some else can (and is) write a user interface using a webserver to allow data retrieval and configuration changes from anywhere in the world.

One of the big advantages of databases in the system I am working on is that I am and programming and communications specialist, I can write the code to perform the communications and put the data into the database (and read configuration changes from the database).

Once the data is in the database some else can (and is) write a user interface using a webserver to allow data retrieval and configuration changes from anywhere in the world.

Did you get your head around 3NF Ben?

Did you get your head around 3NF Ben?

Taking the example r035198x post of decomposing

SUPPLIER_PART (supplier_no, part_no, quantity)

SECOND (supplier_no, status, city)

into

SUPPLIER_PART (supplier_no, part_no, quantity)

SUPPLIER_CITY (supplier_no, city)

CITY_STATUS (city, status)


In this eaxmple it appears that all suppliers from the same city must have the same status, this seems an unlikely real situation and so it is not really clear what the advantage of doing this is or in what way this differed from the decomposition from 1NF to 2NF which was

FIRST (supplier_no, status, city, part_no, quantity)

to

SUPPLIER_PART (supplier_no, part_no, quantity)

SECOND (supplier_no, status, city)


This I see the point of, clearly it makes sense to keep you supplier data (status, location etc) separate from what they can/are supplying.

The BCNF is even worst as it just seems to duplicate data for no advantage and several disadvantages in the example given.


Part of the problem is that the language used in defining the NF is not clear to me so the their meaning is not properly clear.

For instance in

1NF A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only

what does "underlying domains contain atomic values only" mean, can you give an example where this is not the case?

Part of the problem is that the language used in defining the NF is not clear to me so the their meaning is not properly clear.

For instance in

1NF A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only

what does "underlying domains contain atomic values only" mean, can you give an example where this is not the case?

Atomic in this case just means single values. No repeated values or groups should be in the table.

For example if you have a customer/Invoice system then customers have more than one invoice. The Invoices should not be put in multiple fields like Invoice1, Invoice2, etc. and should not be a group like Invoices (101, 102, 103, etc.). In this case Invoices would be moved into a table of their own with a foreign key reference to the customer table usually customerID which would be stored for each invoice.

In this eaxmple it appears that all suppliers from the same city must have the same status, this seems an unlikely real situation and so it is not really clear what the advantage of doing this is or in what way this differed from the decomposition from 1NF to 2NF which was

FIRST (supplier_no, status, city, part_no, quantity)

to

SUPPLIER_PART (supplier_no, part_no, quantity)

SECOND (supplier_no, status, city)

supplier_no would have city and status attached to it as the city and status (I assume) are attributes of the supplier. If there is a separate supplier table then they belong there. However, if city and status are attributes that belong to the supplier_part then assuming there is more than one supplier per part you would need three tables here. One for supplier, one for part and one for supplier_part (As I don't know the rest of your structure you may already have the first two of these).

The supplier table would be a list of suppliers with all attributes belonging to suppliers. e.g. supplier_no, Address, ContactName, ContactNum, etc. The parts table would be a list of parts with e.g. part_no, cost, etc.

The supplier_part table would be supplier_no, part_no (which together would make up the primary key).

Assuming quantity is an order reference this would belong in a separate orders table.

Unfortunately, some of these example sites are not just confusing they can also be misleading with the examples being used.

All 3NF really means is that all fields (attributes) should be dependent on the tables primary key. If they are not they should be put in their own table.

BCNF in it's simplist terms just says don't have a primary key made up of more than one field unless it is a join table to disperse a many to many relationship and only contains the two primary keys of the tables it is joining.

if you need a guide, or even assistance to normalise, email me at <email address snipped>