Re: Normalization problem
Date: Mon, 15 Apr 2002 10:24:23 GMT
Message-ID: <3cba9cbc.7803390_at_news.ch.kpnqwest.net>
On Sun, 14 Apr 2002 14:19:43 +0200, "De Stercke Erwin" <Erwin.DES_at_village.uunet.be> wrote:
>I have a problem with following excercice :
>
>For a school I would like to design a normalized database model for the
>surveillance during different times a day on different places by different
>teachers.
>
>- There are different places
> Tables => Places (Placen°, placename)
> Placen° is the primary key
>
> => Teachers (Teachn°, teachersname)
> Teachn° is the primary key
>
> => Surveillancemoments(Survn°, Date, Begintime, endtime)
> Survn° is the primary key
>
>Note : it must be possible to have different surveillancemoments for each
>place.
>
> => SurveillanceTeachersLink(Teachn°, Survn°)
>
>Note : for each surveillancemoment, different numbers of different teachers
>must be possible too.
>
>I'm trying to make a prototype-construction in MS Access, but, I don't get
>out of it.
>
First of all, you should post DDL instead of wordy descriptions of your schema. I know that MSAccess makes it difficult for you to do this, but if you want to learn to do serious work in databases, you should learn DDL, anyway. (Just try not using the table design GUI once in a while :-)
Second, I would try to keep the table names as short as possible ... why not just "Surveillance" instead of "Surveillancemoments"?
Third, it's not a good idea to use ANSI extended characters in identifiers (e.g. use "TeacherNo" instead of "Teachern°". In Access ver. 2, this was a serious bug and could cause a GPF. Why play with fire? It's supposed to be OK as of Access 97, but if you want your schema to be portable to other RDBMS, you need to use only the non-accented Latin characters, the underscore (no spaces!) or digits, anyway ...
As to the design itself:
You could conceivably have surveillance moments which spanned more than 24 hours, or crossed midnight; with your above design, you'd have to add an extra record to the Surveillancemoment table whenever that happened. Therefore, I would use a date/time column (or "TIMESTAMP" on ANSI-compliant databases) for SurvBegin and SurvEnd. This would cover both possibilities, and you would not need the "Date" field (BTW don't use "Date" or "Name" as a field name; these are reserved words in SQL). You write "different surveillances for each place"; however, I assume that each surveillance can only be performed in one place, and that the entire surveillance is completed at that place? In that case, Place is an attribute of a Surveillance, and you should have a Place field in the Surveillance table.
Therefore, I suggest the following schema (this DDL will work in MSAccess if you run each statement from the SQL view of the query design window one at a time. Also, you'll have to delete the comments from the SQL, which will NOT work in M$Access, but should work in real SQL databases, and remove the CHECK constraint from the last table DLL): CREATE TABLE Places (
PlaceNo COUNTER NOT NULL,
PlaceName VARCHAR(255) NOT NULL,
CONSTRAINT pk_Places PRIMARY KEY (PlaceNo),
CONSTRAINT uk1_Places UNIQUE (PlaceName));
CREATE TABLE Teachers (
TeacherNo COUNTER NOT NULL,
TeacherName VARCHAR(255) NOT NULL,
CONSTRAINT pk_Teachers PRIMARY KEY (TeacherNo),
CONSTRAINT uk1_Teachers UNIQUE (TeacherName));
CREATE TABLE Surveillance (
SurvNo COUNTER NOT NULL,
PlaceNo LONG NOT NULL,
LeadTeacher LONG NOT NULL, -- this is to ensure that each
-- Surveillance has at least one
-- teacher assigned to it
SurvBegin DATETIME NOT NULL,
SurvEnd DATETIME NOT NULL,
CONSTRAINT pk_Surv PRIMARY KEY (SurvNo), CONSTRAINT uk1_Surv UNIQUE (PlaceNo, LeadTeacher, SurvBegin), CONSTRAINT fk1_Surv FOREIGN KEY (PlaceNo)
REFERENCES Places (PlaceNo),
CONSTRAINT fk2_Surv FOREIGN KEY (LeadTeacher)
REFERENCES Teachers (TeacherNo),
-- replace the following with a table validation rule: -- "[SurvBegin] < [SurvEnd]" -- Access doesn't support CHECK constraints:
CONSTRAINT chk_Surv CHECK (SurvBegin < SurvEnd) );
For the additional teachers involved in each surveillance, you can use the linking table you metioned (perhaps naming it "SurvAdditionalTeachers"?). Be sure to create a unique index on the combination of SurvNo/TeacherNo (or better yet, make it the primary key of that table).
There are still some unresolved issues here; however, I'll leave this to you as homework <g>. These are:
(1) To enforce the requirement that each surveillance has at least one teacher, we added a field "LeadTeacher" to the Surveillance table. Perhaps there are better ways of doing this ... however, you must now keep users from adding that same teacher to the "SurvAdditionalTeachers" table. (HINT: you can do this with a CHECK constraint or a TRIGGER in non-brain-damaged databases which support them; in Access, you will have to enforce this through the application logic, i.e. in a form's BeforeUpdate event).
(2) If you wanted to do a report counting the number of teachers involved in each surveillance, with the LeadTeacher field in Surveillance it's a little less straightforward than "SELECT COUNT(*) FROM Surveillance GROUP BY <etc.>" (but it's still fairly easy ... how would you do this?);
(3) The PK's in Teachers and Places do not necessarily have to be AutoNumber (i.e. COUNTER) types. Actually, I would prefer to use some other code which would have some meaning for the places and teachers involved. For example, you could use SSN for teachers, or a unique ID number assigned to them by the school; for places, you have things like zip codes (although zip codes are not unique, but you could include them in some kind of unique key, making lookups in other databases easier) or perhaps GPS coordinates ... these might prove much more useful than some meaningless number. However, for the purpose of learning to normalize, AutoNumber is OK (IMHO).
Good luck! Received on Mon Apr 15 2002 - 12:24:23 CEST