hi,
can anyone pla advice whether the contraits i have define for my oracle create tables statements is alright? or have i miss out any contraits?
contraits are based on this requirement:
- Only the courses on offer for a particular year/semester can be selected for that year/semester.
- No course could be selected more than once, no matter in the same semester or not.
- The number of units selected in any semester by a student cannot exceed 8 (including those year long courses, whose total number of units is split into the two semesters equally).
- A course has the same code, title, number of units and duration no matter when it is offered.
- Each semester-long course carries 2 units, and each year-long course carries 4 units.
- Only the existing courses in Courses dataset can be offered.
here is my create table statements:
CREATE TABLE Student(
StudentID VARCHAR2(8),
FamilyName VARCHAR2 (50) NOT NULL,
GivenName VARCHAR2 (50) NOT NULL,
StudentPwd VARCHAR2(8) NOT NULL,
CONSTRAINT pk_Student PRIMARY KEY (StudentID));
CREATE TABLE Course(
CourseCode VARCHAR2(8),
CourseTitle VARCHAR2 (100) NOT NULL,
CONSTRAINT pk_Course PRIMARY KEY (CourseCode));
CREATE TABLE StudentEnrolment(
CourseCode VARCHAR2(8),
StudentID VARCHAR2(8),
EnrolledYear INTEGER NOT NULL,
EnrolledSem INTEGER NOT NULL,
CONSTRAINT pk_StudentEnrolment PRIMARY KEY (CourseCode, StudentID, EnrolledYear, EnrolledSem),
CONSTRAINT fk_StudentEnrolment1 FOREIGN KEY (CourseCode)
REFERENCES Course (CourseCode),
CONSTRAINT fk_Student_Enrolment2 FOREIGN KEY (StudentNo)
REFERENCES Student (StudentNo));
CREATE TABLE CourseOffering(
CourseCode VARCHAR2(8),
Year INTEGER NOT NULL,
Sem VARCHAR2(6) NOT NULL,
Title VARCHAR2(100) NOT NULL,
Duration VARCHAR2(4),
UnitNum VARCHAR2(1) NOT NULL,
CONSTRAINT pk_CourseOffering PRIMARY KEY (CourseCode, year, sem),
CONSTRAINT fk_CourseOffering FOREIGN KEY (CourseCode)
REFERENCES Course (CourseCode));
thank you.
