NetNews Usenet Archive 1992 #27/NN_1992_27.iso/spool/comp/database/8003

Internet Message Format | 1992-11-21 | 3.8 KB

Path: sparky!uunet!zaphod.mps.ohio-state.edu!cs.utexas.edu!natinst.com!hrd769.brooks.af.mil!hrd769.brooks.af.mil!not-for-mail
From: [e-mail] (Dave Burgess)
Newsgroups: comp.databases
Subject: Re: Normalizing 2NF -> 3NF
Date: 20 Nov 1992 17:44:58 -0600
Organization: Armstrong Lab MIS, Brooks AFB TX
Lines: 71
Distribution: world
Message-ID: <[e-mail]>
References: <[e-mail]> <[e-mail]> <[e-mail]>
NNTP-Posting-Host: hrd769.brooks.af.mil

In article <[e-mail]> [e-mail] (David D Vazquez) writes:
|[e-mail] (Robert Marti) writes:
|.  :
|.  FDs:  A -> B and B -> C
|.  Relation: R(A*,B,C)
|.  Problem: How to decompose?
|.  Suggestion: R1(A*,B) and R2(B*,C) or R1(A*,B) and R2(A*,c)
|.              ^^^^^^^^^^^^^^^^^^^^^
|.Yes, but this might not be efficient. Fx, if there are two groups of users
|.that wants to see an (A,B) and an (A,C) view resp., it requires a lot of
|.unnecessary joins. Eg, (A,B,C) == (Name,Address,Phone#). You typically look up
|.addresses from names and phone-numbers from names---not phone# from addresses.
|.

  While the statement you make in the example is true, you are not adhering to
the original statement.  The One to One relationship described in Name, Address
and Phone is not valid for this question.  Phone Number is Fully Functionally
Dependent on Address, not Name.  Suppose four students share a house, or (a
military example) 50 airmen share a single barracks.  The phone is only
transitively dependent on the name.  It is functionally dependent on the
address.  It is transitively dependent on the name (if it is dependent at all).

  One of the references I use when I teach this course (Mary E.S. Loomis) says:
"A relation is in 'third normal form' if, and only if, it is in 2NF and no
non-key attribute is ''transitively dependent'' on the primary key."

  A Student/Dormitory relationships might be a good example of the original
question.  R1(A,B,C) == (Student Name, Dorm, Dorm Address), where every
student has a room in a dormitory.  The Address of the dorm is Functionally
Dependent on the Dormitory (yes, or vice versa, it's only an example), not
the student name.  In addition, every student is in a dorm.  This relation
is NOT in 3NF.  The dependency between R1(A) and R1(C) is transitive (at best)
based on the value of R1(B).

|
|It may make that specific query faster, but it can wind up costing you more
|disk space.  While R1(A*,B) and R2(A*,C) are in 3NF given the above stated FDs,
|you will have alot of redundant (repeated) data (3NF is supposed to solve this
|correct?).
|

  Where is the redundancy?  In the foreign key element?  That is where your
cardinality will help determine the actual implementation, but we are talking
Form Normalization, not implementation.  In normalization, the concepts of
query speed and disk space have no place.  Save them for the physical
implementation phase.

|.A DBMS should be able to check other integrity constraints than primary keys.
|
|Correct, but a 'database design' should enforce all of the given functional
|dependencies (R1(A*,B) + R2(A*,C) will not).
|

  I agree.  The only problem with the statement is that what a DBMS 'should'
do and what a DBMS 'will' do are neither germaine nor controllable by the
database designer.  Every DBMS implements different levels of integrity
constraint, even with a particular model.

|.--
|.         Niels Elgaard Larsen
|.         Institute of Computer Science,
|.         University of Copenhagen
|.         E-mail: [e-mail]
|
|  David Vazquez
|  Computer Science major @ University of New Orleans
|  E-mail: [e-mail] and [e-mail]

TSgt Dave Burgess
NCOIC, AL/MIS
Brooks AFB, TX