473,224 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Problems defining a primary key !!

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,month ...)
2- Table PERIODE representing an hour of a day (periode_id,starting,ending,periode_number)
3- Table TEACHER representing a teacher (teacher_id,name,phone,address ...)
4- Table ROOM representing a ROOM in the university (room_id,description,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,teacher_id,room_id,cours e_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
Nov 12 '05 #1
2 1297
> 1- Table DAY representing a day (day_id,year,month ...)
2- Table PERIODE representing an hour of a day (periode_id,starting,ending,periode_number)
3- Table TEACHER representing a teacher (teacher_id,name,phone,address ...)
4- Table ROOM representing a ROOM in the university (room_id,description,name ...)
5- Table COURSE representing a course given by a teacher (course_id,name,...)

following table PLANIFY(day_id,periode_id,teacher_id,room_id,cours e_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*******@postgresql.org

Nov 12 '05 #2
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,month ...)
2- Table PERIODE representing an hour of a day
(periode_id,starting,ending,periode_number) 3- Table TEACHER representing a
teacher (teacher_id,name,phone,address ...) 4- Table ROOM representing a
ROOM in the university (room_id,description,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,teacher_id,room_id,cours e_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,teacher), but you'll need UNIQUE constraints on
day,period,room and day,period,course 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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jack A | last post by:
Guys, I'm stumped. While its not pertinent to the matter, we are running a Vignette content management system on Win2k with Sql 2000 Enterprise on a cluster. The server has 2 Gig of RAM , 2 CPU's...
9
by: JMCN | last post by:
hi- i have inherited an access 97 database that keeps track of the loans. i have been running into referential intergrity problems when i try to append new loans to table. first of all is a...
4
by: Jonathan [sbrodolo] | last post by:
Hi there, I am using a LOTUS/DOMINO Web Service in my ASP.Net application and I am having quite a lot of problems. I have managed to solve most of them but this one it really is giving me terrible...
1
by: ckirby | last post by:
I have a situation where I'm trying to add a text field to allow large Notes to be linked to record in an existing table (rather than simply adding the new field into the table since relatively...
0
by: shadowsoulja | last post by:
okay, ive been going okay on my assignment, but ive just run into a problem. i need to read a file into my program, the data from the file is to be read, and then randomised to produce "match...
1
by: aviansh | last post by:
HI Experts, I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare Tables in both tha...
3
by: EllieT | last post by:
Hi all, I've been fiddling around with creating a new database (Access 2003, Windows XP) for work the past two weeks and am having trouble defining my own customized (yet still automatic) primary...
6
by: msb_6 | last post by:
Currently I have a PHP extension thats all written and compiles under windows, but the PC I'm going to end up putting it on is running Ubuntu 8.04 (g++ 4.2.3). I've delved into PHP documentation...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.