Problems defining a primary key !!

[Nagib Abi Fadel]

Hi ,
i'm doing the planification of the courses in a university.
I have definned the following tables:

1- Table DAY representing a day (day_id,year,mo nth ...)
2- Table PERIODE representing an hour of a day (periode_id,sta rting,ending,pe riode_number)
3- Table TEACHER representing a teacher (teacher_id,nam e,phone,address ...)
4- Table ROOM representing a ROOM in the university (room_id,descri ption,name ...)
5- Table COURSE representing a course given by a teacher (course_id,name ,...)

Now i have to planify the courses:
So i created the
following table PLANIFY(day_id, periode_id,teac her_id,room_id, course_id)

THE PROBLEM IS THAT I DON'T HAVE A WAY TO DEFINE A PRIMARY KEY for the table PLANIFY.
We must note that:
1- a teacher can not give 2 courses in the same peiode on the same day, and can not exist in 2 different rooms in the same periode on the same day.
2- a room can not be occupied by 2 courses or 2 teachers in the same periode on the same day.
3- a course can not be given by 2 teachers or in 2 different rooms

ANY IDEAS ??

SHOULD I CHANGE THE STRUCTURE OF THE TABLE OR DEFINE A TRIGGER (which i don't know how to by the way).

thx for any help.


---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

[Karsten Hilbert]

> 1- Table DAY representing a day (day_id,year,mo nth ...)

2- Table PERIODE representing an hour of a day (periode_id,sta rting,ending,pe riode_number)
3- Table TEACHER representing a teacher (teacher_id,nam e,phone,address ...)
4- Table ROOM representing a ROOM in the university (room_id,descri ption,name ...)
5- Table COURSE representing a course given by a teacher (course_id,name ,...)

following table PLANIFY(day_id, periode_id,teac her_id,room_id, course_id)

THE PROBLEM IS THAT I DON'T HAVE A WAY TO DEFINE A PRIMARY KEY for the table PLANIFY. You can always define your own primary key with the SERIAL
datatype.
We must note that:
1- a teacher can not give 2 courses in the same peiode on the same day, and can not exist in 2 different rooms in the same periode on the same day. unique (periode_id, day_id, teacher_id, course_id)
unique (periode_id, day_id, teacher_id, room_id)
2- a room can not be occupied by 2 courses or 2 teachers in the same periode on the same day. unique (periode_id, day_id, room_id, course_id)
unique (periode_id, day_id, room_id, teacher_id) (redundant, see above)
3- a course can not be given by 2 teachers or in 2 different rooms

unique (course_id, teacher_id)
unique (course_id, room_id)

Does that help ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Richard Huxton]

On Tuesday 07 October 2003 11:38, Nagib Abi Fadel wrote:

Hi ,
i'm doing the planification of the courses in a university.
I have definned the following tables:

1- Table DAY representing a day (day_id,year,mo nth ...)
2- Table PERIODE representing an hour of a day
(periode_id,sta rting,ending,pe riode_number) 3- Table TEACHER representing a
teacher (teacher_id,nam e,phone,address ...) 4- Table ROOM representing a
ROOM in the university (room_id,descri ption,name ...) 5- Table COURSE
representing a course given by a teacher (course_id,name ,...)

Now i have to planify the courses:
So i created the
following table PLANIFY(day_id, periode_id,teac her_id,room_id, course_id)

THE PROBLEM IS THAT I DON'T HAVE A WAY TO DEFINE A PRIMARY KEY for the
table PLANIFY. We must note that:

You do (day,period,tea cher), but you'll need UNIQUE constraints on
day,period,room and day,period,cour se too.

Alternatively, two tables (*=primary key)

t1 (day*, period*, teacher*, course)
t2 (day*, period*, course*, room)

You could pick different combinations of p,t,c depending on your model.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html