Object relational database primary keys

Question

I am trying to make a object relational database of a school enrolment system in oracle 11g, my sql code is below. I am trying to write a query that gives back the course name and students enrolled in that course. but when i try to add another chemistry class, with the id 101. i get an error. this is because i have made courseID a primary key, so i cant insert multiple courses with that course id? So can anyone show me how i can create multiple chemistry insert statements with the same id, but different student id?

CREATE TYPE Student_objtyp AS OBJECT (
  FName         VARCHAR2(20),
  LName         VARCHAR2(20),
  StudentID     NUMBER
  ); 
/

CREATE TABLE Student_objtab OF Student_objtyp (StudentID PRIMARY KEY)
  OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TYPE Course_objtyp AS OBJECT (
  CourseName VARCHAR(20),
  CourseID NUMBER,
  StudentID REF Student_objtyp
  );
/

CREATE TABLE Course_objtab OF Course_objtyp (
  PRIMARY KEY (CourseID),
  FOREIGN KEY (StudentID) REFERENCES Student_objtab)
  OBJECT IDENTIFIER IS PRIMARY KEY;


INSERT INTO Student_objtab VALUES('bill','smitts',1);
INSERT INTO Student_objtab VALUES('bob','jo',2);


INSERT INTO Course_objtab
SELECT 'Chemistry',101,
REF(S)
FROM Student_objtab S
WHERE S.StudentID = 1;

INSERT INTO Course_objtab
SELECT 'Chemistry',101,
REF(S)
FROM Student_objtab S
WHERE S.StudentID = 2;

INSERT INTO Course_objtab
SELECT 'Physics',201,
REF(S)
FROM Student_objtab S
WHERE S.StudentID = 1;

select c.CourseName, c.StudentID
from Course_objtab c;

Answer 1

This design violates database normalization rules. COURSE should only hold information on the course, NOT on the students enrolled in the course. IMO there should at least be an ENROLLMENT table, with references to COURSE and STUDENT. This is known as a "junction table".

I'd model it as follows:

STAFF - information about staff members (teachers/professors, etc) STUDENT - information about students COURSE - information about courses which may be taught CLASS - information about a specific instance of a course, including the days/times the class meets, the location, and the staff member who is teaching it. CLASS_ENROLLMENT - relates STUDENTs to a specific CLASS.

There should also probably be something here about ROOM, and ROOM_SCHEDULE, and perhaps an ACADEMIC_CALENDAR.

Best of luck.