NORMALIZING A DATABASE

[Mani]

Example of Data:


MembID Title Firstname Surname Add1 Add2 Add3
Add4

1 Professor SUZANNE VANDERSTRAETEN 14 Abba St Dyce Aberdeen


HomeTel Deptno Dept Uni
D house

0141604389721 28 Accountancy Dublin, Trinity College 24



DeptAdd1 DeptAdd2 D-pc Work-Tel Work-Fax
Use-Add

Chapman Terrace Dublin D63 47B 0136991278474 0136991278480 Dept



e-mail T-Spec 1 T-Spec 2 T-Spec3 R-Spec1
RSpec2

(e-mail address removed) 3 15 0 3
15




That is one record from the first table. There are 2 tables and they have 4
common fields MembID
First name
Lastname
Uni

The second table has less records 100 records.

The first table has 200 records

Duplication summary


Duplicated choices duplicates
Title 6 200
lastname 3 7
Add1 58 146
Add2 20 41
Add3 2 4
Add4 49 71


this summary was just for the home section, the department section has not
been done yet



I have some specific questions as well

What is the best way of normalizing the table?

should i move the highly duplicated field TITLE into a table on its own?

Should i move the address fields in to seperate tables for each field?
What about the fields about specs?

The name field is all in capital letters, how can i make this more pleasing
to the eye by making it capital for the first letter and small letters for
the rest?

Note some of the names in the first name field are actual 2 names like JEAN
- PAUL and like JONATHAN MICHAEL?

How can i keep the format of small letters when editing the data? for phone
numbers, names and email address

Maybe you want to look at the database, let me know and we can arrange file
transfer.

 

[mscertified]

I would put the Title in a separate table.
Do not do this with name and address. Peoples addresses change A LOT (also
names when women get married or divorced) and it would be tricky to cater for
this if you normalize those.
Possibly you could normalize the city name.
I would normalize the 'specs' fields - assuming there are a finite set of
values.

Remember any time you break out a separate table you need a way of
maintaining that table and you need to think about things like cascading
updates or deletes when setting up the relationships.

Your tables are very small to be overly worried about normalization.
Its more of an issue when dealing with thousands or millions of records.

Dorian

 

[Mani]

would you answer change if i say that this is not from the real world but is
an assignment?

So if something is likely to change i should not split up it into another
table?

 

[mscertified]

Not necessarily.
Say you have the title split out, it has a finite number of choices which
will seldom if ever change e.g. Student, Professor, Dean or whatever
However, if you have the address split out, it has an almost unlimited set
of choices, so if someones address changes you may not just have to change
the link to the address table but add a new link to the table as well.
If this is an assignemnt I would just explain why you did what you did so
whoever reviews it does not think you did not normalize because you did not
know how.
Looking at your data again, I would also split out 'dept no and dept' and
'university'.

 

[Albert D.Kallal]

should i move the highly duplicated field TITLE into a table on its own?

Well, since for each record, you only got ONE title, then using the term
"move" the title to another table don't make sense. You should, and can use
a table to hold the common titles, but this is really more of a lookup list,
or a list of titles for convenience sake. You will still have to relate the
title field to the "list of titles" table, and your primary key can be the
actual title name (Mr.., Mrs. doctor etc etc.). This type of relation is a
bit different then a normal type of relation that is enforced. Remember,
when you delete a name, you will still leave the "title table" intact, and
not deletion will occur in our table of titles. This type of relationship is
often referred to a lookup table (this term NOT to be confused with
ms-access table lookup feature). So, the answer for the title field is that
you can, and should make a table of "titles", but the title field can
continue to hold the actual string data of the title. (so, in a sense, you
will build a related table, but the actual title data can continue to stay
in the actual title field). Thus, the title table is really on there to
"present" a list of choices during data entry (and, also prevent people from
making errors during data entry). This would be a one to one relationship.
So, yes, build a table of titles...but you will not be "moving" out the
title data from the title field, as it can remain where it is.

Should i move the address fields in to seperate tables for each field?

The key answer in the above is "table", not tables. You have one name, and
"many" address. So, you got a classic "one to many" relationship. So, you
only need to add ONE table that can contain "many" address of the one name.
So, you don't need, nor want to add several address tables here, you only
need to add ONE TABLE of addresses. You likely should add a field tot he
table address that defines the address (eg: home, work, lake, summer
residence etc). And, you might want to add a field called current mailing
address. This way, you can easily generate a query that pulls the current
mailing address for the person. You want to complete ignore the suggestion
in the other thread that is setup is problem. in fact, it allows you to
build query that pulls in the name + active mailing address, and one you
build this query, then you can freely build reports, or mailing labels that
pulls the name + correct address together as if it was simple one file
mailing list. Nothing could be more easy to solve this problem, and allow
you to freely build reports, or mailing labels of the name + correct
(current) mailing address. You can even build this query in the query
builder it great ease.

DO NOT heed the advice of the other poster that moving the address fields
out to another table is a problem. In fact, moving the address fields out
SOLVES a ZILLION problem. Moving the address fields out to another table
allows you to add a simple check box field in this address table as to which
address you want to use for regular mailings etc. if you keep address1,
address2 etc. in the main table, then trying to build a query, or report
that shows the correct address (out of the 4 possible ones) is a ABSOLUTE
NIGHTMARE. This is probity the best, and most useful example of data
normalizing that you can be given, and without the slightest doubt, you want
to move out the address stuff to another table. I can't stress how wrong it
is to not move these fields out to anther table. I repeat, this normalizing
question is likely the best possible example of data normalizing you could
ever come across that solve problems, makes less work, make less code, and
makes your reports and labels a breeze. This approach turns a night mare
application into something that is a easy hot knife through butter.

What about the fields about specs?

Same thing, any time you see phone1, phone2, phone 3, or address1, address2,
address...etc, then you got a "many" side to the one name,a nd thus you make
ONE table to hold that many side. So, the key concept again is simply to
take any data that is "repeated", and move that into another table. So,
again, the spec1, spec2 etc should move out to another table (and, if there
is any corresponding data, that goes with spec1, 2, then that should also go
to the spec table).

The name field is all in capital letters, how can i make this more
pleasing

to the eye by making it capital for the first letter and small letters for
the rest?

You can use the function strConv([yourfieldname],3) in a update query to fix
the data. If you don't understand how to use a update query, then make a new
post on this subject.

note that with strconv

"how are you" would become "How Are You"

However, the above function can't correctly fix name like MCDONALD to be
McDonald, it will become Mcdonald