0

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;
2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by debasisdas
0

You need to pass unique values for the scheduled_num field.

schedule_num should be the PRIMARY KEY and semester should be having an UNIQUE constraint.

Try to use the insert statement in a procedure and use a SEQUENCE to generate the schedule_num.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.