Please help with a homework ! ! !

[student]

Hi group !

I need to normalize a small database for a homework. but
I'm not sure if I have it in BCNF.

The database is to be created in MS Access 2000. For now,
I just need to normalize the tables.

As of now, all I've been given is a table with all the
information in it with the entities such as:

Last and First Name (even worse, sometimes this field has
two people such as husband and wife -one is to be the
primary account holder);
Phone Number;
Phone Line Features;
Address;
City;
State;
Zip

Would anybody be kind enough to give it a shot and see
they come up with the same info as I have ?

Thanks a bunch !

 

[tom]

I think it would be better if you posted what you have first!

 

[student]

Thank you so much for the reply. Sounds good about
posting what I have, thought about it but didn't think it
would help.

Here it goes....

tbl_customer
custID (PK)
cust_LName
cust_FName
cust_MI
cust_Phone (FK from tbl_Phone)
cust_Addr
cust_ZIP (FK)

tbl_Phone
phone_No (PK in this table and FK on tbl_customer)
phone_Active
phone_Business
phone_Private
Phone_Listed
phone_callID
phone_callBack
phone_callNote
phone_callBlock

tbl_ZIP
zip_ID (PK and FK on tbl_customer)
zip_state (FK from tbl_State)
zip_city (FK from tbl_City)

tbl_State
stateID (PK in this table and FK on tbl_ZIP)
state_Name

tbl_City
cityID (PK in this table and FK on tbl_ZIP)
city_Name

And that's that !
Is my normalization somewhat accurate... or way off ?
Please advice.

Thank you very much ! ! !

-----Original Message-----
I think it would be better if you posted what you have first!

 

[tom]

I assume a 1 to many relationship from Customers to Phone tables. Therefore,
I have trouble with the field cust_Phone in the Customer table. I don't know
what the database is for, but looking at it are you using tbl_Phone as a
reference 'pick' table? i.e. a subform detailing the type of telephone the
customer has? If so, this is better done through a combo Value list, or
alternatively, build it through the column in tbl_Customer… you will have a
lot of wasted space doing it your way. Maybe I need more information before
trying to reorganize your data…

I'm not sure why you have split your States, Cities and ZIP codes - aren't
they all the same? Isn't a full ZIP code unique to a City, which is unique
to a State? Therefore, they should all be in 1 table, e.g. HA7 9BC in
England can narrow a search down to just 1 property. It's always a good idea
to list the Data Types as well.

If you are using tables to store data that doesn't alter, e.g. States,
Cities and ZIPs, then it could be better accomplished through Value Lists,
although this is a debatable point! Another point is, depending on what your
database is for, is there any point in recording information you might not
use? e.g. every Zip code, City and State in the USA when you only have 200
customers from California?

Please tell me what the database is for and what info you hope to extract
from it.

Tom.

 

[tom]

Wow! Telephone directory companies have some sophisticated software to
filter millions of records... let's not try and replicate that just yet! At
least you know what you want! But you have to start somewhere and gradually
build it up dependant on your own skills, being able to write code to
control access/data entry, etc. Additional 'interfaces' to control data
access is unnecessary because you can invoke security on tables/forms... a
better way of proceeding.

Basically, you could have 1 table for everything - Name, telephone number
and full address... then filter the records by using your combo boxes,
ending up with just the number/s you want. However, it is probable that your
tutors want to see you being able to create more than 1 table and specify
relationships between them... Why have you chosen this application? Yes you
could split your data as you have indicated before, but this may not be
ideal.

I think if it was my project, I would think up another application...
something like 3 tables - Customers, Products and Purchases... An easy
database to create and maintain... However, yours may be fine if you were to
save your searches into a new table... or maybe not! You see, you are
splitting data unnecessarily to create additional tables... If it's several
tables you want then use a real world situation. Your solution is top heavy,
your work is in the filtering part, not the table/relationship creation
process... Please let me know what you think and we'll proceed from there.

Tom.

 

[tom]

The reason I asked him/her to post what had been done to date was that some
students want the work to be done by others... Read this thread... I think
he/she has done a lot of work to date, if only by thinking about the
problem... This seems to me to be a good time to ask advice!

Tom.
(We all have to start somewhere...)

 

[student]

Hi Mr. Spok.

I disagree with your statement of "making a mistake and
then have to redo it. That is what 95% of software
projects do anyways" I believe that THAT 95% is due to
improper requirements gathering, data modeling. In other
words, analysis. And that is the reason why so many
system design and implementations are carried out over
budget. If I understand correctly, you're advising me to
adopt a trial and error approach, which is negligible and
unacceptable. I have been thought better. I have been
thought to divide and conquer. And to research all
feasible posibilities at their appropriate phase and in a
timely fasion. And that's what I am doing.

Thank you very much for your reply.

 

[student]

Well, if your way is a very "efficient, effective, and
acceptable way" then I'll go for it. But I still need
more than one table to have it somewhat normalized.

Being the case, would you say that I need three tables ?

tbl_customer
cust_ID (pk)
cust_Lname
cust_Fname
cust_phone (fk from tbl_phone)
cust_address

tbl_phone
phone_ID (pk)
phone_features (call ID, call block, etc.)
phone_business
phone_residential
phone_etc.

tbl_location
state (pk)
cust_id (fk from tbl_customer)
zip
city

What do you think about this ?

Thank you so much for your help.

 

[tom]

Okay, if you going to proceed with this type of project, and based on your
table layout, I would assume a 1-many relationship between Customers and
Phones, i.e. one customer could have many phones… I also assume a 1-1
relationship between Customers and Location, i.e. 1 phone has 1 location… I
suppose you could have a multinational listed that has 1000 phones located
all over the world. Therefore, when you enter a new Customer, you would list
each phone number in a subform, which also contains the phone's location.
You also need to give careful consideration to how you will extract the
relevant information...