Re: Normalization and DBMS

From: Dawn M. Wolthuis <dwolt_at_tincat-group.com>
Date: Tue, 11 May 2004 19:11:15 -0500
Message-ID: <c7rq3j$bna$1_at_news.netins.net>


"Paul" <paul_at_test.com> wrote in message news:3Jcoc.2175$wI4.227540_at_wards.force9.net...
> Dawn M. Wolthuis wrote:
> >> Suppose there is some extra information about each "Major", maybe
> >> the name of the person in charge of it or something.
> >
> > A nested table can have any number of columns as well. A "nested
> > table" can be thought of as a table that is a child table of the
> > parent.
>
> So in this case suppose Prof.Smith was in charge if MATH, Prof.Jones was
> in charge of ENGLISH, Prof.Davis was in charge of PHYSICS. Would your
> records look like this?:
>
> NAME MAJOR PROF
> ========= ====== =========
> A.Student MATH Prof.Smith
> ENGLISH Prof.Jones
>
> B.Student MATH Prof.Smith
> PHYSICS Prof.Davis
>
> C.Student PHYSICS Prof.Davis

No. If by "in charge" you mean the chair of the department, then that information would be "normalized" into the departments file, keyed by codes like "MATH" above (yes, an "intelligent" yet coded key and "yes" a key as in an element of the domain of the function DEPARTMENTS).

If by "in charge" you mean the instructor of a course, then the student might have a sub-table of multipart keys to course sections, such as

Student MATH|315|01

            PHIL|151|03 And then the COURSE-SECTIONS file/function would map MATH|315|01 to a specific course section and have the instructor there.

That is why I say that it is in 2nd and 3rd normal form, but not irst. --dawn

>
> i.e. you repeat the entries of the "nested table" every time they occur?
> What then if MATH is taken over by Prof.Plum? Do you have to go through
> every record in the "nested table" and update it?
> I assume from what you say later that this isn't "best practice" to have
> multiple columns for a nested table unless they are all required to
> uniquely identify the nested table row?
>
> >> Are the entries in multi-valued fields always the full text, or are
> >> they ever id numbers that correspond to a different file? e.g
> >> suppose you had 1=MATH, 2=ENGLISH, 3=PHYSICS. Would you just store
> >> the numbers 1,2,3 in the multivalued "major" column and have a
> >> separate file for the lookups? How would the query be written then
> >> in Pick?
> >
> > "Return links" are often in MultiValue fields -- they are lists of
> > foreign keys. Yes, you still work to remove redundancy -- it is not
> > the same as an OLAP cube. It can be used like an OLAP cube with all
> > such repetitions being virtual fields, however. --dawn
>
> OK so in this case we would have two files like this?:
>
> Students:
> NAME MAJORID
> ======== =======
> A.Student 1
> 2
> B.Student 1
> 3
> C.Student 3
>
> Majors:
> MAJORID MAJOR PROF
> ======= ===== ====
> 1 MATH Prof.Smith
> 2 ENGLISH Prof.Jones
> 3 PHYSICS Prof.Davis
>
> How would we now write the query in Pick to list all students who don't
> have MATH as one of their majors? We'll assume that at the time the
> query is written we don't actually know what the MAJORID values will be
> for each major, as they will be assigned during the use of the
application.

LIST STUDENTS WITH EVERY MAJOR <> "MATH"

Where the MAJORS or DEPARTMENTS file might look like MAJORID MAJOR
MATH MATH
ENGL ENGLISH
PHYS PHYSICS or

12421 MATH
83728 ENGLISH
90238 PHYSICS The STUDENTS file would have the vocabularly item "MAJOR" added so that the user sees MAJORID as one possible field name (and it is stored on STUDENTS) and MAJOR as another. The user need not know where MAJOR is stored -- just that it is in the vocabularly for the STUDENTS entity/file/function.

Make sense? --dawn Received on Wed May 12 2004 - 02:11:15 CEST

Original text of this message