Re: Normalization and DBMS

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.

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?

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 Wed May 12 2004 - 00:46:17 CEST