Re: Normalization and DBMS

From: Paul <paul_at_test.com>
Date: Tue, 11 May 2004 23:46:17 +0100
Message-ID: <3Jcoc.2175$wI4.227540@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

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.

Paul. Received on Tue May 11 2004 - 17:46:17 CDT