Oracle RSS Oracle RSS

Beginner Oracle Help

Reply

•
•
Join Date: Oct 2009
Posts: 1
Reputation: Kramer.Cosmo33 is an unknown quantity at this point 
Solved Threads: 0
Kramer.Cosmo33 Kramer.Cosmo33 is offline Offline
Newbie Poster

Beginner Oracle Help

 
0
  #1
Oct 5th, 2009
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.


Oracle Syntax (Toggle Plain Text) 
  1.  DROP TABLE Teaches;
  2. DROP TABLE Enrolls;
  3. DROP TABLE Instructor;
  4. DROP TABLE Class;
  5. DROP TABLE Student;
  6.  
  7.  
  8. CREATE TABLE Student
  9.  
  10.     (student_id INTEGER PRIMARY KEY,
  11.  
  12.      student_name VARCHAR(255),
  13.  
  14.      major  VARCHAR(255),
  15.  
  16.      gpa  VARCHAR(5));
  17.  
  18.  
  19.  
  20.  CREATE TABLE Class
  21.  
  22.     (schedule_num INTEGER,
  23.  
  24.      semester VARCHAR(255),
  25.  
  26.      course_name  VARCHAR(255),
  27.      course_number VARCHAR(255),
  28.  
  29.      credit  VARCHAR(2),
  30.  
  31.      department VARCHAR(255),
  32.  
  33.      meeting_time VARCHAR(30),
  34.      meeting_place VARCHAR(255),
  35.  
  36.      major  VARCHAR(255),
  37.      enrollment INTEGER,
  38.      CONSTRAINT uq_Class UNIQUE (schedule_num),
  39.      PRIMARY KEY (semester));
  40.  
  41.  
  42.  
  43.  
  44.  
  45.  CREATE TABLE Instructor
  46.  
  47.     (name VARCHAR(255) PRIMARY KEY,
  48.  
  49.      department VARCHAR(255),
  50.  
  51.      office VARCHAR(255),
  52.  
  53.      title  VARCHAR(255));
  54.  
  55.  
  56.  
  57.  CREATE TABLE Enrolls
  58.  
  59.    (student_id INTEGER,
  60.  
  61.     schedule_num INTEGER, 
  62.  
  63.     semester VARCHAR(255), 
  64.  
  65.     grade  INTEGER,
  66.     PRIMARY KEY (student_id, schedule_num, semester), 
  67.     FOREIGN KEY (student_id) references Student(student_id),
  68.     FOREIGN KEY (schedule_num) references Class(schedule_num),
  69.     FOREIGN KEY (semester) references Class(semester));   
  70.  
  71.  
  72.  
  73.  CREATE TABLE Teaches
  74.  
  75.    (name VARCHAR(255),
  76.  
  77.     schedule_num INTEGER,
  78.  
  79.     semester VARCHAR(255),
  80.     PRIMARY KEY(name, schedule_num, semester),
  81.     FOREIGN KEY (name) references Instructor(name),
  82.     FOREIGN KEY (schedule_num) references Class(schedule_num),
  83.     FOREIGN KEY (semester) references Class(semester));
  84.  
  85.  
  86.  
  87.  
  88.  
  89.  INSERT INTO Student(student_id, student_name, major, gpa) VALUES
  90.  
  91. ( '0024', 'John Smith', 'Computer Science', '3.0');
  92.  
  93.  INSERT INTO Student(student_id, student_name, major, gpa) VALUES
  94.  
  95. ( '0027', 'Barney Stinson', 'Business', '2.6');
  96.  
  97.  INSERT INTO Student(student_id, student_name, major, gpa) VALUES
  98.  
  99. ( '0038', 'Ted Mosby', 'Building Management', '3.2');
  100.  
  101.  INSERT INTO Student(student_id, student_name, major, gpa) VALUES
  102.  
  103. ( '0005', 'Robin Scherbatsky', 'Communications', '3.5');
  104.  
  105.  
  106.  INSERT INTO Class(schedule_num, semester, course_name, course_number, credit, 
  107.  
  108. department, meeting_time, meeting_place, enrollment ) VALUES ( '1102', 'Spring 09', 
  109.  
  110. 'CSCI3700','Database Management', '3', 'Computer Science', '11:00-11:50', 'Austin 306', '21'); 
  111.  
  112.  INSERT INTO Class(schedule_num, semester, course_name, course_number, credit, 
  113.  
  114. department, meeting_time, meeting_place, enrollment ) VALUES ( '3403', 'Spring 07', 'BUS2350',
  115.  
  116. 'Philosphy of Business Management', '3', 'Business Managment', '5:00-6:15', 
  117.  
  118. 'Bate 2005', '68'); 
  119.  
  120.  INSERT INTO Class(schedule_num, semester, course_name, course_number, credit, 
  121.  
  122. department, meeting_time, meeting_place, enrollment ) VALUES ( '2022', 'Spring 09', 'ARC1001',
  123.  
  124. 'Intro into Aritechture', '3', 'Contruction Management', '2:00-2:50', 'Flanagan 2001', '57'); 
  125.  
  126.  
  127.  
  128.  INSERT INTO Instructor(name, department, office, title) VALUES ('Ding', 
  129.  
  130. 'Computer Science', 'ST107', 'Professor');
  131.  
  132.  INSERT INTO Instructor(name, department, office, title) VALUES ('Mathers', 
  133.  
  134. 'Business Management', 'B323', 'Professor');
  135.  
  136.  INSERT INTO Instructor(name, department, office, title) VALUES ('Mooney', 
  137.  
  138. 'Contruction Management', 'TS302', 'Professor');
  139.  
  140.  
  141.  
  142.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0024',
  143.  
  144. '1102', 'Spring 09', '78');
  145.  
  146.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0024',
  147.  
  148. '3403', 'Spring 07', '82');
  149.  
  150.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0024',
  151.  
  152. '2022', 'Spring 09', '66');
  153.  
  154.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0027',
  155.  
  156. '1102', 'Spring 09', '51');
  157.  
  158.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0027',
  159.  
  160. '3403', 'Spring 07', '91');
  161.  
  162.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0038',
  163.  
  164. '1102', 'Spring 09', '34');
  165.  
  166.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0038',
  167.  
  168. '2022', 'Spring 09', '99');
  169.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('0005',
  170.  
  171. '2022', 'Spring 09', '85');
  172.  INSERT INTO Enrolls( student_id, schedule_num, semester, grade) VALUES ('B005',
  173.  
  174. '1102', 'Spring 09', '77');
  175.  
  176.  
  177.  
  178.  INSERT INTO Teacher(name, schedule_num, semster) VALUES ('Doug', '1102', 'Spring 09');
  179.  
  180.  INSERT INTO Teacher(name, schedule_num, semster) VALUES ('Mathers', '3403', 'Spring 07');
  181.  
  182.  INSERT INTO Teacher(name, schedule_num, semster) VALUES ('Mooney', '2022', 'Spring 09');
  183.  INSERT INTO Teacher(name, schedule_num, semster) VALUES ('Doug', '2022', 'Spring 09');
  184.  INSERT INTO Teacher(name, schedule_num, semster) VALUES ('Mooney', '1102', 'Spring 09');
  185.  
  186. COMMIT;
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2007
Posts: 2,104
Reputation: debasisdas will become famous soon enough debasisdas will become famous soon enough 
Solved Threads: 128
debasisdas's Avatar
debasisdas debasisdas is offline Offline
Postaholic
 
0
  #2
Oct 6th, 2009
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.
Share your Knowledge.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Oracle Forum
Thread Tools Search this Thread



2009predictions acquisition amazon.com bartz bernanke cia citrix cloudcomputing crm database dell economy editor enterprise enterprise2.0 enterprisesoftware federalreserve forbes hp ibm intellipedia internet larryellison layoffs linux loughridge mediawiki michaeljackson microsoft neverland nortel notebooks oil operatingsystem oracle palm rimm saas salesforce sap seagate socialcomputing sql sun sybase technologystocks virtualiron virtualization vmware wiki wikipedia xen yahoo zoho
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC