Jamie makes a good point and I'd like to amend my answer to address that as
well.

I approached the answer from a pure academic normalization standpoint
because the point is to get a good grade. The Address-problem makes an
interesting and challenging assignment. As a college instructor myself, I
would be more interested in the logic behind your choices than the actual
answer.

However, in the real world, I have never designed a database with the
Address normalized to that extent. Generally, I keep all the fields in the
same table. I may have a look-up table for States and even Cites,
(especially if I'm going to search or sort on them) but that's all. There
comes a point when the cost of establishing and maintain the relationships
is more than the benefit derived.

For instance, the City -- Zip relationship would take a long time to fill an
intersection table with all the possible combinations. The only reason to
do so would be to make sure the user could never enter an impossible
City/Zip combination. But it's relatively easy for a person to view the
data and correct it manually. Furthermore, addresses are relatively static,
so it's not truly transactional data anyway. The same is true for City --
State.

On the other hand, I still think the Contacts table is a good idea because
it doesn't limit the number of alternate phone numbers or email addresses
you allow a vendor and makes your database much more robust to meet changing
business rules.

All this to say that sometimes fully normalized is not the optimum design,
but you should have good reasons to vary from it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Roger Carlson" wrote in message
...
I see a several problems.

First of all, your design assumes that a vendor can have only one address
and one Rep. If that's in the business rules (which we are not privy to),
that's fine, but there are plenty of business out there that have muliple
addresses and Reps. I suggest you create a separate Address table and
also a separate Rep table.

Now, each Address can be in only one City, but each City can certainly
contain more than one Address, so there is a one-to-many relationship
there as well. So the idea of a separate City table is correct, but it is
related to the Address table rather than the Vendor table.

Each City can be in only one State, but each State can contain more than
one City. So we also have a 1:M relationship there too. Thus you need a
State table related to the City table and not directly to the Vendor (or
Address) table.

Each City can have more than one Zip code AND each Zip code can have more
than one City. This is a Many-to-Many relationship. (frex: New York City
contains muliple zipcodes, however, the zip code that I live in spans
multiple cities but not *all* of each of those cities). Therefore, you
need to create a ZipCode table and create an intersection table between
City and ZipCode which contains the primary keys of the other two tables
as foreign keys.

Lastly, I am concerned about PhoneNumber and AltPhoneNumber and Email.
What if the vendor has more than 2 contact numbers, say a cell number? Or
if the vendor has several email addresses? One way to handle this is to
have a "Contact" table that would store all of these. Something like
this: Contact(ContactID, ContactType, ContactString). In ContactType, you
would store the type of contact (Phone, AltPhone, Cell, Email, etc.) and
in Contact String, you would store the actual value. Of course, you would
want to have a look-up table for each ContactType also (to reduce data
integrity errors) so you'd have another table for ContactType related to
Contact.

Because this is an assignment, I'm not going to give you the actual design
details, but you should be able to piece them from here.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"CoachBarkerOJPW" wrote in
message ...
In our Database Management class, our next project deliverable is to
normalize our database to 3NF. The argument has come up that in our
database,
any entity that has City, Zipcode and State has a transitive dependency
between the three. One of our team mates says to resolve this issue we
should
put all three attributes into one table with CityID (An autonumber) as
the
foreign key to any other tables. His reasoning that if you know the City,
you
then know the Zip code and State does not work because some of the Cities
have multiple Zip codes.

This would be the relationship schema he wants to use.

tblVendor (VendorCode, Season, BusName, RepName, Address, CityID,
PhoneNumber, AltPhoneNumber, Email)

One issue is that in our database, which is used by a local business who
only deals with people and business in about a 50 mile radius, is that
since
there are a limited number of Cities, Zip codes and for the most part the
default value for state is NY, that they are in separate tables for City,
Zip
code and State and these are basically used to fill combo boxes on a form
for
data entry.

This would be the relationship schema I want to use.

tblVendor (VendorCode, Season, BusName, RepName, Address, CityID,
ZipcodeID,
StateID, PhoneNumber, AltPhoneNumber, Email)

The real issue is that we only have to worry if they are transitive
dependencies, the Professor told us to work this out by researching the
idea,
and one way to research this would be to ask the experts for their
advice. So
any input to us would be greatly appreciated.

Thanks
CoachBarkerOJPW