Normalize this data

How would you normalize this data into a useable database?
Customer Name City Contact Address
Contact First Name Contact Phone Number Contact Fax Number
Billing Address Country Contact Email
Customer Number Contact Title User Name
Contact Last Name Shipping Address Password
Postal Code Contact Cell Phone User Role PrivilegePut it in a table?
<billsahiker@.yahoo.com> wrote in message
news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
> How would you normalize this data into a useable database?
> Customer Name City Contact Address
> Contact First Name Contact Phone Number Contact Fax Number
> Billing Address Country Contact Email
> Customer Number Contact Title User Name
> Contact Last Name Shipping Address Password
> Postal Code Contact Cell Phone User Role Privilege
>|||On Oct 20, 1:18 pm, "Jay" <s...@.nospam.org> wrote:
> Put it in a table?
> <billsahi...@.yahoo.com> wrote in message
> news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
>
> > How would you normalize this data into a useable database?
> > Customer Name City Contact Address
> > Contact First Name Contact Phone Number Contact Fax Number
> > Billing Address Country Contact Email
> > Customer Number Contact Title User Name
> > Contact Last Name Shipping Address Password
> > Postal Code Contact Cell Phone User Role Privilege- Hide quoted text -
> - Show quoted text -
Two or more tables, and indicate primary and foreign keys. This was a
test item and that is all the information given.|||<billsahiker@.yahoo.com> wrote in message
news:1192908678.033282.164490@.k35g2000prh.googlegroups.com...
> On Oct 20, 1:18 pm, "Jay" <s...@.nospam.org> wrote:
>> Put it in a table?
>> <billsahi...@.yahoo.com> wrote in message
>> news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
>>
>> > How would you normalize this data into a useable database?
>> > Customer Name City Contact Address
>> > Contact First Name Contact Phone Number Contact Fax Number
>> > Billing Address Country Contact Email
>> > Customer Number Contact Title User Name
>> > Contact Last Name Shipping Address Password
>> > Postal Code Contact Cell Phone User Role Privilege- Hide quoted text -
>> - Show quoted text -
> Two or more tables, and indicate primary and foreign keys. This was a
> test item and that is all the information given.
>
How about doing your own homework...
--
David Portas|||Why do you assume it is school work? It was a job application test I
took yesterday.
> How about doing your own homework...
> --
> David Portas- Hide quoted text -
> - Show quoted text -|||Ah, because you told the truth, I will help. And there is little difference
between an employment test and homework. You should have said so in the
first place.
Basically, it looks like they got together and looked for ways to screw
applicants up. They probably thought they were being cute mixing up and
eleminating necessary attributes too.
The first thing that jumps out is that you're storing people and addresses,
so that's two tables:
People is 1-to-many to Addresses (more on addresses later).
Second (and it's glaring): "Customer Name" vs. "Contact First Name" &
"Contact Last Name"
These columns should be: "Prefix", "First Name", "Middle Name",
"Last Name" & "Suffix".
(Mr. John Adam Smith, Sr)
Third Customers have "User Role Privilege", "User Name" and Password,
Contacts do not. Therefore, some people have attributes that others do not.
This suggests storing Customers and Contacts seperatly.
--
If you take the list of attributes they provided and arrange it, more things
become apparent:
Customer Number
User Name
Password
User Role Privilege
Customer Name
Billing Address
City
Postal Code
Country
Shipping Address
Contact Title
Contact First Name
Contact Last Name
Contact Address
Contact Phone Number
Contact Cell Phone
Contact Fax Number
Contact Email
Comments:
"Billing Address" and "Shipping Address" are clearly attributes of the
Customer. However, "City", "Postal Code" (Zip Code) & "Country" are vague.
Are they for Billing, Shipping, or Contact. In fact, you really need those
for each address. Same for attributes that appear in Contact, you should
have those for Customers too. In general, they really messed with the
attribute list.
Also, what "State" is the address in?
People (not including addresses):
There are two approaches here: store the "Contact" as attributes in
Customer, or create three tables: Customer, Contact & People, where "People"
is the parent table. You'll probably have to define your own PK for people
too and store it as a FK in Customer alone with the Customer Only info.
Addresses:
Create a seperate address table with all approporiate attributes. Then
you need a way to link people to addresses. You can do this by adding a
column to one of the people tables, or creating a many-to-many relationship
between people/customer/contact tables and addresses.
Since you have three distinct address types, you might want an AddressType
column in addresses constrained either by a lookup table, or a check
constraint. There are pluses and minuses for each. I prefer lookup tables,
but I'm a 3NF+ bigot.
Well, that covers the basics. Can you normalize the tables now?
<billsahiker@.yahoo.com> wrote in message
news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
> How would you normalize this data into a useable database?
> Customer Name City Contact Address
> Contact First Name Contact Phone Number Contact Fax Number
> Billing Address Country Contact Email
> Customer Number Contact Title User Name
> Contact Last Name Shipping Address Password
> Postal Code Contact Cell Phone User Role Privilege
>