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

Internet Message Format | 1992-11-22 | 3.1 KB

Path: sparky!uunet!portal!cup.portal.com!DVaz
From: [e-mail] (David D Vazquez)
Newsgroups: comp.databases
Subject: Re: Normalizing 2NF -> 3NF
Message-ID: <[e-mail]>
Date: Sun, 22 Nov 92 16:56:09 PST
Organization: The Portal System (TM)
Distribution: world
References: <[e-mail]>
  <[e-mail]> <[e-mail]>
  <[e-mail]>
Lines: 61

Dave Burges writes:

>  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."
>

Exact definition I learned for 3NF.

>  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 FD
s
>,
>|you will have alot of redundant (repeated) data (3NF is supposed to solve thi
s
>|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.
>

I agree, but physically implemented tables generally tend to look alot like
their abstractly designed  relatives.   You can also save alot of time and
headaches if you implement your tables just like they've been designed.  Just
think, you've spent 7 hours designing a database only to have your programmer/
implementor spend another 7 hours trying to figure out the better implementa-
tion (and for something silly like R1(A*,B)/R2(A*,C) vs. R1(A*,B)/R2(B*,C).
The database implementor shouldn't have to make these kinds of decisions!
Supertype/subtype and recursive relationship implementation should be the exten
t
of implementation issues.

'Redundancy?', you ask.  Given R(Student, Dorm, Dorm Address); if you implement
as R1(Student, Dorm) and R2(Student, Dorm Address), you are repeating values
of Dorm Address for each student as well as not enforcing Dorm -> Dorm Address
(or vice versa); you'd have to do some extra coding..  Also, you'd need two
copies of Student's names (one to find the dorm and one to find the dorm's
address); this is a waste.

The problem I see is that you never have one correct answer when it comes to
getting a database into 3NF.  Should there be one, maybe?

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

  -Dave. "[e-mail] and [e-mail]"