Hi, I just started learning how to use oracle I am suppose to create a table for a school that includes class, instructor, enrolls and teaches.
I've basically understand everything, but my only problem is I keep getting a with my schedule_num in my class table. It is suppose to be a primary key and in the instructions it does say that schedule_number of a class is a unique number. I've tried making it a primary key and it just gives me errors for my enrolls and teaches because they each use schedule_num. Making it a unique number gives me a
unique constraint violated error. If anyone has any idea how to correct this error I would greatly appreciate it.
drop table Teaches;
drop table Enrolls;
drop table Instructor;
drop table Class;
drop table Student;
create table Student
(student_id integer PRIMARY KEY,
student_name varchar(255),
major varchar(255),
gpa varchar(5));
create table Class
(schedule_num integer,
semester varchar(255),
course_name varchar(255),
course_number varchar(255),
credit varchar(2),
department varchar(255),
meeting_time varchar(30),
meeting_place varchar(255),
major varchar(255),
enrollment integer,
constraint uq_Class unique (schedule_num),
PRIMARY KEY (semester));
create table Instructor
(name varchar(255) PRIMARY KEY,
department varchar(255),
office varchar(255),
title varchar(255));
create table Enrolls
(student_id integer,
schedule_num integer,
semester varchar(255),
grade integer,
PRIMARY KEY (student_id, schedule_num, semester),
FOREIGN KEY (student_id) references Student(student_id),
FOREIGN KEY (schedule_num) references Class(schedule_num),
FOREIGN KEY (semester) references Class(semester));
create table Teaches
(name varchar(255),
schedule_num integer,
semester varchar(255),
PRIMARY KEY(name, schedule_num, semester),
FOREIGN KEY (name) references Instructor(name),
FOREIGN KEY (schedule_num) references Class(schedule_num),
FOREIGN KEY (semester) references Class(semester));
insert into Student(student_id, student_name, major, gpa) values
( '0024', 'John Smith', 'Computer Science', '3.0');
insert into Student(student_id, student_name, major, gpa) values
( '0027', 'Barney Stinson', 'Business', '2.6');
insert into Student(student_id, student_name, major, gpa) values
( '0038', 'Ted Mosby', 'Building Management', '3.2');
insert into Student(student_id, student_name, major, gpa) values
( '0005', 'Robin Scherbatsky', 'Communications', '3.5');
insert into Class(schedule_num, semester, course_name, course_number, credit,
department, meeting_time, meeting_place, enrollment ) values ( '1102', 'Spring 09',
'CSCI3700','Database Management', '3', 'Computer Science', '11:00-11:50', 'Austin 306', '21');
insert into Class(schedule_num, semester, course_name, course_number, credit,
department, meeting_time, meeting_place, enrollment ) values ( '3403', 'Spring 07', 'BUS2350',
'Philosphy of Business Management', '3', 'Business Managment', '5:00-6:15',
'Bate 2005', '68');
insert into Class(schedule_num, semester, course_name, course_number, credit,
department, meeting_time, meeting_place, enrollment ) values ( '2022', 'Spring 09', 'ARC1001',
'Intro into Aritechture', '3', 'Contruction Management', '2:00-2:50', 'Flanagan 2001', '57');
insert into Instructor(name, department, office, title) values ('Ding',
'Computer Science', 'ST107', 'Professor');
insert into Instructor(name, department, office, title) values ('Mathers',
'Business Management', 'B323', 'Professor');
insert into Instructor(name, department, office, title) values ('Mooney',
'Contruction Management', 'TS302', 'Professor');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0024',
'1102', 'Spring 09', '78');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0024',
'3403', 'Spring 07', '82');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0024',
'2022', 'Spring 09', '66');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0027',
'1102', 'Spring 09', '51');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0027',
'3403', 'Spring 07', '91');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0038',
'1102', 'Spring 09', '34');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0038',
'2022', 'Spring 09', '99');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('0005',
'2022', 'Spring 09', '85');
insert into Enrolls( student_id, schedule_num, semester, grade) values ('B005',
'1102', 'Spring 09', '77');
insert into Teacher(name, schedule_num, semster) values ('Doug', '1102', 'Spring 09');
insert into Teacher(name, schedule_num, semster) values ('Mathers', '3403', 'Spring 07');
insert into Teacher(name, schedule_num, semster) values ('Mooney', '2022', 'Spring 09');
insert into Teacher(name, schedule_num, semster) values ('Doug', '2022', 'Spring 09');
insert into Teacher(name, schedule_num, semster) values ('Mooney', '1102', 'Spring 09');
commit;