On Mar 1, 1:19 pm, CoachBarkerOJPW
wrote:
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.

Have you done the "Is an address an entity?" debate yet? It's a goodie
g.

Consider this approach: an address is an attribute of an entity not an
entity in itself; the meaning of 'address' in this context being, "The
text I have to put on the address label of an item of mail for the
post office to deliver it to the 'mailable' entity". Then you start to
think about what the post office expects e.g. USPS expects five lines
of CHAR(35).

If your business has a need to identify entities sharing an address it
may be better to model addresses as entities. If your business has a
need to identify entities with an address in the same administrative
area/city/county/electoral ward/etc it may be better to have an
attribute for administrative area/city/county/electoral ward/etc but
because 'no man is an island', rather than roll your own CityID,
instead look for a industry standard key e.g. here's one I did the
other day relating to the UK:

ISO 3166-2:GB administrative divisions of the UK (e.g. Staffordshire
=
'GB-STS'):
http://en.wikipedia.org/wiki/ISO_3166-2:GB

UK Internal [Country] Code (e.g. England = 1)
http://www.govtalk.gov.uk/gdsc/html/...ternalCode.htm

ISO 3166-1 alpha-3 country codes (e.g. United Kingdom of Great
Britain
and Northern Ireland = 'GBR'):
http://en.wikipedia.org/wiki/ISO_3166-1_alpha-3

Another thing to be aware of is attribute splitting (google it) where
you have to create joins between 100 tables just to be able to send
Auntie Mabel a birthday card, so don't let lofty ideals of data
modellers and relational theorist make you lose track of the business
problem in hand. And speaking of taking things to the nth degree, this
is an interesting article:

Design Challenge: Global Address
http://www.dmreview.com/portals/port...Id=1062041&top

Are you getting the idea that the meaning of 'address' isn't clear
cut...?

Jamie.

--