IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 1st Normal Form
Starting from the basics, a Table is said to be in First Normal Form if the attributes are non-repeating. Taking the example of Critters, let's design a table that's not normalized. Let's say for each entry in the table, we have a list of up to three countries in which the critter is found:
\nCREATE TABLE critter(\nname         VARCHAR(80)   NOT NULL,\ndescription  VARCHAR(255)  NOT NULL,\ncountry1     CHAR(3)       NULL,\ncountry2     CHAR(3)       NULL,\ncountry3     CHAR(3)       NULL)\nINSERT INTO critter VALUES('Panda', 'Bear', 'PRC', 'China', NULL, NULL)\nINSERT INTO critter VALUES('Housefly', 'Insect', 'USA', 'MEX', 'CAN')\n

The first problem you encounter is that some animals only can be found in one place. Since you are reserving space for 3 countries, that means that any animal that can only be found in one country has two unused cells. It also means that if you want to find the animals for any particular country, you have to look in 3 seperate columns.

The second problem you have is that you've put a maximum limit on the number of countries that an animal can be found. The knee jerk reaction is to then add country4, country5,....countryn, until you've safely reserved enuf columns to cover what you think will be the maximum ever required (which only compounds the first problem). The rule of thumb is that eventually you will stumble upon some critter that can be found in every country on the planet (the common housefly would be a good example).

To solve the problem, you normalize the table(s) so that any particular attribute is not repeated. So you might be more creative and have:
\nCREATE TABLE critter(\nname         VARCHAR(80)   NOT NULL,\ndescription  VARCHAR(255)  NOT NULL,\ncountry      CHAR(3)       NULL)\nINSERT INTO critter VALUES('Panda', 'Bear', 'PRC')\nINSERT INTO critter VALUES('Housefly', 'Insect', 'USA')\nINSERT INTO critter VALUES('Housefly', 'Insect', 'MEX')\nINSERT INTO critter VALUES('Housefly', 'Insect', 'CAN')\n

Not a particularly elegant solution, but you have not repeated the attributes along the columnar lines. Of course, now you have a different problem in that you have repeating data along the rows (the description of 'Insect' for the 'Housefly' is repeated 3 times). So you've really only changed the repeating of attributes from one dimesion (columnar) to another (row based). But that is the subject for 2nd Normal Form. The First Normal Form has to do with eliminating redundant columns (attributes).
Expand Edited by ChrisR Sept. 30, 2003, 07:54:34 PM EDT
Expand Edited by ChrisR Sept. 30, 2003, 08:05:35 PM EDT
New Re: 1st Normal Form
Thanks, that helped some Chris.

John explained that the reason my Critter database works as one huge table is because all the information depends on the critter name (name not type) directly. So each time I name a critter (which is the primary key), the information in the surrounding columns depends on the name. And there are no repeated column headings.

He explained that if we were say, to add address as a heading, then it would be dependent upon the location we bought the stuffed critter, (i.e. Hallmark, Nagels, etc). and that would be an example of a separate table with repeating information.

Location is already a heading, but not EXACT location, just name of store. But since there are many Hallmarks, for example, he said I could name each one with a number also, Hallmark, Vans 1, and Hallmark Vans 2, and etc.

I'm starting to get the concept... but since I worked with only this one, and it's defined as a flat file database, I hadn't done much else. John said it is an example of a flat file database however, that doesn't need to be changed, which is why it works like it does.

Nightowl >8#
"I learned to be the door, instead of the mat!" "illegitimi nil carborundum"

Comment by Nightowl
     Is there some sort of tutorial disk for Access? - (Nightowl) - (31)
         Okay, first question... :) - (Nightowl) - (17)
             Table is multiple columns together as a unit - (FuManChu) - (5)
                 Re: Table is multiple columns together as a unit - (Nightowl) - (4)
                     Third normal form - (ChrisR) - (3)
                         Re: Third normal form - (Nightowl) - (2)
                             1st Normal Form - (ChrisR) - (1)
                                 Re: 1st Normal Form - (Nightowl)
             Tuples - (ChrisR) - (10)
                 Re: Tuples - (Nightowl) - (1)
                     Could be - (ChrisR)
                 How come critterid isn't IDENTITY? -NT - (mmoffitt) - (7)
                     I'm of two minds on identity - (ChrisR) - (6)
                         Re: I'm of two minds on identity - (Nightowl) - (5)
                             In Access, Identity is an AutoNumber - (ChrisR) - (3)
                                 Re: In Access, Identity is an AutoNumber - (Nightowl) - (2)
                                     AutoNumbers not always continuous - (ChrisR) - (1)
                                         ICLRPD (new thread) - (ben_tilly)
                             Identity being the key - (orion)
         Don't recall if there's a tutorial - (ChrisR) - (4)
             Re: Don't recall if there's a tutorial - (Nightowl) - (3)
                 Part of Office Pro - (ChrisR) - (2)
                     Re: Part of Office Pro - (Nightowl) - (1)
                         Word to Access - (orion)
         Access tutorials - (orion) - (4)
             Re: Access tutorials - (Nightowl) - (1)
                 Re: Access tutorials - (orion)
             It worked, Norman!! ( was Re: Access tutorials) - (Nightowl) - (1)
                 Glad to hear it - (orion)
         the first thing to do - (cforde) - (2)
             Better yet - (orion) - (1)
                 Not that I recommend it... - (ChrisR)

Here he comes, here comes Speed Racer.
67 ms