YamiFox
Starting Member
1 Post |
Posted - 2013-04-21 : 18:22:14
|
okay so I have the following code:---drop tables to ensure there aren't errors making this oneDrop Table Student;Drop Table Course;Drop Table Faculty;Drop Table Offering;Drop Table Enrollment;----Creation of tables---Create Student TableCREATE Table Student (Stu_ID int constraint Student_Stu_ID_PK Primary Key,Stu_FN varchar2(50),Stu_LN varchar2(50),Stu_City varchar2(25),Stu_State varchar2(2),Stu_Zip int,Stu_Major varchar2(4),Stu_Class varchar2(50),Stu_GPA number(3,1),Stu_Balance number(7,2);---Create Course TableCREATE Table Course (Cou_No varchar2(10) constraint Course_Cou_ID_PK Primary Key,Cou_Desc varchar2(50),Cou_Cred Int);---Create Faculty TableCREATE Table Faculty (Fac_ID Int constraint Faculty_Fac_ID_PK Primary Key,Fac_FN varchar2(50),Fac_LN varchar2(50),Fac_Dept varchar2(4),Fac_Rank varchar2(4),Fac_HireDate Date,Fac_Salary number(6),Fac_Supervisor number(4));---Create Offering TableCREATE Table Offering (Off_No Int constraint Offering_Off_ID_PK Primary Key,Cou_No varchar2(10),Off_Term varchar2(7),Off_Year int,Off_Loca varchar2(5),Off_Time varchar2(10),Off_Day varchar2(5),Fac_ID int);---Create Enrollment TableCREATE Table Enrollment (Stu_ID int,Off_No int,Enr_Grade varchar2(1),Constraint Enrollment_PK Primary Key(Stu_ID, Off_No));----Inserting Records into created tables----Insert Records into StudentINSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (101, 'Joe', 'Smith', 'Eau Clare', 'WI', 18121, 'IS', 'FR', 3.0);INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (102, 'Jenny', 'Sneider', 'Eau Clare', 'WI', 98011, 'IS', 'JR', 3.2);INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (103, 'Dan', 'Robinson', 'Sartell', 'MN', 98042, 'IS', 'JR', 3.5);INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (104, 'Sue', 'Williams', 'St. Cloud', 'MN', 56301, 'ACCT', 'SR', 3.2); ---Insert Records into courseINSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 250', 'Application Program Dev. I', 3);INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 251', 'Application Program Dev. II', 3);INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 445', 'Application Program Dev. III', 3);INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 350', 'Systems Analysis and Design I', 3);INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 351', 'Systems Analysis and Design II', 3);INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('ACCT 291', 'Accounting Principles II', 3);INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 443', 'Database Design', 3);---Insert Records into FacultyINSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9001, 'Leonard', 'Vince', 'IS', 'ASST', To_Date('12041997', 'DD-MM-YYYY'), 67000, 9003);INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9002, 'Victor', 'Fibon', 'IS','ASSO', To_Date('08081999', 'DD-MM-YYYY'), 70000, 9003);INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9003, 'Nicki', 'Colan', 'IS', 'PROF', To_Date('20081981', 'DD-MM-YYYY'), 75000, 9010);INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9004, 'Fred', 'Wells', 'ACCT', 'ASST', To_Date('28081996', 'DD-MM-YYYY'), 60000, 9010); ---Insert Records into OfferingINSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2201, 'IS 250', 'Spring', 2000, 'BB260', '10:30am', 'MWF', 9002);INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2202, 'IS 250', 'Spring', 1999, 'BB118', '8:00am', 'TTH', 9002);INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2203, 'IS 350', 'Fall', 2001, 'BB260', '9:30am', 'TTH', 9001);INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2204, 'IS 351', 'Fall', 2001, 'BB315', '12:30pm', 'TTH', 9003);INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (1101, 'ACCT 291', 'Fall', 2000, 'BB320', '12:30pm', 'MWF', 9010);INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2205, 'IS 443', 'Fall', 2002, 'BB216', '12:30pm', 'MWF', 9003);---Insert Records into EnrollmentINSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (101, 2201, 'A');INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (101, 2203, 'B');INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (102, 2203, 'C');INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (103, 2203, 'B');INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (103, 2201, 'C');INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (103, 1101, 'B');I need to do the following triggers that i am having a brain fart with:#1. The maximum number of students per class allowed is 30#2. Prevent a student from registering for a class with an unpaid balance of more then 500.#3.a student is not allowed to register for IS 443 unless they completed IS 350 & IS 250.The code was the code i am supposed to add a triggers to for these events for an assignment that i am frustrated with because of this damn ambiguous teacher and his ineffective explanations.....PLEASE HELP ME WITH THIS...There one thing that will always be true there will never be a short supply of stupid people in this world |
|