help with normalization
openleren
a normalization?
Let me be positive: some of you may answer.
The case: a school timetable
Student (StudNo, Surname, Forename, address, Class)
Teacher(StaffNo, Surname, Forename, address)
Room(RoomNo, description)
Course(CourseNo, description)
Class(ClassNo, description, total students in)
Period(Period)
These are the one-key tables which are obvious.
The Period is an integer that increments over one week (eg. period 8 is the
first period of tuesday)
We need to be able to constuct a timetable in which
- a teacher can teach several courses
- a single class has a single course in a certain room
- a room can not be occupied by two classes in the same period
- a class can not follow more than one course in one period
I have problems following Codd's rules in normalizing this situation. I
would like to see it happening step by step and discuss a bit about it.
Anyone?
thank you,
Jan Vandorpe, open...@skynet.be
Belgium
x
"openleren" <open...@skynet.be> wrote in message
news:43183569$0$8598$ba62...@news.skynet.be...
Of what rule have you stumbled ?
mAsterdam
> Hello, is there anyone who can help me with the 'logic'
> I have to apply with a normalization?
>
> Let me be positive: some of you may answer.
>
> The case: a school timetable
/Before/ starting to normalize, (try to)
distinguish the types of fact you need data on.
These can serve as the 'external predicates' for
the logical model.
> Student (StudNo, Surname, Forename, address, Class)
What does this mean? Let's try:
A student, identified by <StudNo> has name <Forename> <Surname>,
lives at <adress> and attends <class>.
Example:
A student, identified by 123 has name Jan VanDorpe,
lives at Vlaanderenstraat 456a Gent and
attends 'introductory normalization'.
This has a mixture of distinct types of facts
(e.g. 'lives at' and 'attends'). Before designing
the logical model it a good thing to have the right
concepts.
Tonkuma
before consider normalization.
For Example:
> - a teacher can teach several courses
Add reference to StaffNo(Primary key of Teacher table) to Cource table.
> Course(CourseNo, description, TeacherNo) (TeacherNo reference StaffNo of Teacher table)
openleren
STUDENT(studentNr{PK}, Name,..., classNr{FK})
TEACHER(teachernr{PK}, name,...)
COURSE(courseNr{PK}, name,...)
CLASS(ClassNr{PK}, description
ROOM(roomNr{PK},descript,...)
ROSTER(classcode{PK}, courseNr{PK}, teacherNr{PK}, RoomNr{PK}, period{PK})
In the last table I end up with the entire thing being a composite key. I
suspect it should be split,
because...how do I control:
- a class can only be in one room in one given period
- a room can only be occupied by one class in a given periuod (the same
thing)
- a class can have only one course in a given period
Do I need to split this last table, and how?
thanks again, Jan
"Tonkuma" <ton...@jp.ibm.com> wrote in message
news:1125667802....@f14g2000cwb.googlegroups.com...
mAsterdam
> I think that you shoud consider to add forein keys and constraints,
> before consider normalization.
Some constraints, yes, but not foreign keys.
Foreign keys are a result of the normalization steps.
See e.g. http://databases.about.com/od/specificproducts/a/normalization.htm
paul c
> Let me explain a little further: this is as far as I get:
>
> STUDENT(studentNr{PK}, Name,..., classNr{FK})
does this mean that a student can take only one class?
p
-CELKO-
appeared. This sonds like someone is uising my book for homework
problems.
Jon Heggland
mAst...@vrijdag.org says...
> Tonkuma wrote:
> > I think that you shoud consider to add forein keys and constraints,
> > before consider normalization.
>
> Some constraints, yes, but not foreign keys.
> Foreign keys are a result of the normalization steps.
Foreign keys have very little to do with normalisation. What you first
and foremost need to do is to determine the functional dependencies.
Without those, I don't see how it is possible to normalise in any
systematic and formal manner.
--
Jon
mAsterdam
> mAsterdam says...
>>Tonkuma wrote:
>>
>>>I think that you shoud consider to add forein keys and constraints,
>>>before consider normalization.
>>
>>Some constraints, yes, but not foreign keys.
>>Foreign keys are a result of the normalization steps.
>
> Foreign keys have very little to do with normalisation. What you first
> and foremost need to do is to determine the functional dependencies.
> Without those, I don't see how it is possible to normalise in any
> systematic and formal manner.
Except (obviously) for the first sentence I agree.
First you should get the meaning (including the FDs) right.
A quote from Date's intro (8th ed, p 356):
"FDs are a semantic notion ... (in fact, they are part of the relvar
predicate). Recognizing the FDs is part of the process of understanding
what the data /means/ ... "
David Cressey
sound a lot like yours. Even the examples seem oddly familiar. Some of us
tend to think that a student is asking for help with the homework.
If you ARE taking a course in data modeling and database design,
congratulations! I suggest you tell us exactly that. Some of us will be
happy to help you learn the material. This will be more useful to you, in
the long run, that doing well on your assignemnts.
If you are NOT taking a formal course in data modeling and database design,
here is a website that can give you an overview of this material. It's not
as good as a class with a real, live instructor. But at least it's
something.
http://www.utexas.edu/its/windows/database/datamodeling/index.html
>
edigiam
reason to think it must be split.
As far as classes, rooms and periods and no overlap; this is clearly a
constraint. This is one of the things that always posed a problem in
the classes that I have taught. The tables/relationships/keys do not
change, what does change is the criteria for membership in one/all of
the involved tables. It's the program doing the INSERT/UPDATE that has
to implement this rule. The rule itself is not referential, it's
semantic.