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;
Unsolved 
