third normal form

[john]

Hi all,

If i understand correctly a third normal form
should not contain field that are mutually dependant

Does that mean if i have 3 fields

name, adress, postal code
where address is nb,street,city

this is not a 3rd normal form since postal code depend on adress?

regards
john

 

[Rick Brandt]

Hi all,

If i understand correctly a third normal form
should not contain field that are mutually dependant

Does that mean if i have 3 fields

name, adress, postal code
where address is nb,street,city

this is not a 3rd normal form since postal code depend on adress?

regards
john

You don't think that "123 Main street" could occur in more than one zip
code?

 

[mnature]

Quoting verbatim from O'Reilly's book, Access Database Design & Programming
(3rd Edition, pages 42 and 43):

[Begin quote]

It is possible to find table schemes that are in third normal form, but
still have redundancy. Here is an example.

Consider the table scheme {City,StreetName,ZipCode}, with dependencies:
{City,StreetName} -> {ZipCode}
and:
{ZipCode} -> {City}

(Althought in real life, a zip code may be shared by two different cities,
we will assume otherwise for the purposes of illustration.) This table
scheme is in third normal form. To see this, observe that the keys are
{City,StreetName} and {ZipCode,StreetName}. Hence, no attribute is strictly
informational, and there is nothing to violate third normal form.

On the other hand, consider the following table. We can fill in the blank
city name because {ZipCode} -> {City}.

City, StreetName, ZipCode
Los Angeles, Hollywood Blvd., 95000
?, Vine St., 95000

The problem here is with the dependency:
{ZipCode} -> {City}
which does not violate third normal form because, as we have mentioned,
{City} is not strictly informational.

The previous example gives us the idea to strengthen the condition in the
definition of third normal form by dropping the requirement that B be
strictly informational. Thus, we can define our last, and strongest, normal
form. A table scheme is in Boyce-Codd normal form if it is not possible to
have a dependency of the form:
{A1, . . . , Ak} -> {B}
where {A1, . . . , Ak} is not a superkey. In other words, BCNF form does
not permit any attribute to depend upon anything other than a superkey.

As mentioned earlier, all attributes must depend on any superkey by the very
definition of superkey. Thus, BCNF is the strongest possible restriction of
this type - it says that an attribute is not allowed to depend on anything
other than a superkey.

[End quote]

Now, aren't you sorry you asked?

 

[Allen Browne]

We'll get to the address part in a moment, but firstly the Name field.

Almost everything in Access (forms, reports, controls, tabledefs, ...) has a
Name property. You *really* don't want to call a field "Name". If you do
then Access is likely to think that you are referring to the Name of the
form when you try to refer to the Name field/control on your form. Searches,
matches, filters, sorting, and so on will all go awry.

In any case, one of the normalization rules is that the data must be atomic,
i.e. don't store 2 or more pieces of information in one field. That means
you need separate fields for Surname, FirstName, etc.

For the same reason, an address field that contains both the street and city
is not normalized, i.e. the data is not atomic--you have multiple pieces of
info in a single field. In practice, this severely limits:
- the reliability of the data (cannot match against a list of cities),
- data entry speed (cannot autocomplete the name),
- the efficiency of the data (cannot use the index when the city is embedded
in the middle of the field), and
- the searchability of the data (cannot distinguish between a street name
and a city name.)

To be fully normalized, you might even consider that 123 Main Drive should
be 3 fields: the number, the street name, and the street type. If addresses
were consistent, that would be a very good idea. In practice, though, that
structure is impractical, because heaps of addresses do not follow that
format. There are unit numbers (7 / 222 Main St), street numbers with
suffixes (12B Main St), building names, floor levels, lot numbers instead
of street numbers, rural addresses with no street number and perhaps an RMD
number (Road Mail Delivery), addresses that indicate the corner of 2
streets, post boxes, address Care Of somone else, locked bag numbers, ...
And that's before we start considering all the possibilities in other
countries. It becomes impractical to normalize addresses to that level.

Hope that's useful.

 

[Allen Browne]

Sorry: didn't get to your question.

If you live in a country where one zip code always relates to one city, and
you are certain you will never need addresses in any other country, then by
all means leave the City out of the table completely.

In my country, one postal code can cover multiple suburbs, and one suburb
can have multiple postal codes, so there is no direct dependency.

 

[John Nurick]

Now consider this approach: a postal contact address is a single
attribute, being the text (four lines of 32 characters or whatever) you
must put on an address label for the local jurisdiction's postal
service to be able to deliver a letter to the addressee.

I often do consider it. But there always seems to be a requirement to
select or sort records by country, province, city or postcode, and I'm
not yet sure it's practical to write code that will parse these out of
an all-in-one address field without unacceptably limiting the number of
countries one can do business with.

In fact I feel there's a stronger case for treating names this way.
Index him as
Blow, Lord (Joseph)
Put him on the address label as
The Right Honourable the Lord Blow of Hardley, PC, KCMG
And start the letter with
Dear Joe
..