Oracle pl\sql question for my homework in oracle 11G class [migrated]
- by Bjolds
I am new to oracle 11G programming and i have run into a tough situation with pl\sql funtions and automation. I ame unsure how to create the function for the automation of Registration system for a College registration system. Here is what i want to do. I want to automate the registrations system so that it automaticly registers students. Then I want a procedure to automate the grading system. I have included the code that i am written to make most of this assignment work which it does but unsure how to incorporate Pl\SQL automated fuctions for the registrations system, and the grading system. So Any help or Ideas I would greatly appreciate please.
set Linesize 250
set pagesize 150
drop table student;
drop table faculty;
drop table Course;
drop table Section;
drop table location;
DROP TABLE courseInstructor;
DROP TABLE Registration;
DROP TABLE grade;
create table student(
studentid number(10),
Lastname varchar2(20),
Firstname Varchar2(20),
MI Char(1),
address Varchar2(20),
city Varchar2(20),
state Char(2),
zip Varchar2(10),
HomePhone Varchar2(10),
Workphone Varchar2(10),
DOB Date,
Pin VARCHAR2(10),
Status Char(1));
ALTER TABLE Student
Add Constraint Student_StudentID_pk Primary Key (studentID);
Insert into student values (1,'xxxxxxxx','xxxxxxxxxx','x','xxxxxxxxxxxxxxx','Columbus','oh','44159','xxx-xxx-xxxx','xxx-xxx-xxxx','06-Mar-1957','1211','c');
create table faculty(
FacultyID Number(10),
FirstName Varchar2(20),
Lastname Varchar2(20),
MI Char(1),
workphone Varchar2(10),
CellPhone Varchar2(10),
Rank Varchar2(20),
Experience Varchar2(10),
Status Char(1));
ALTER TABLE Faculty
ADD Constraint Faculty_facultyId_PK PRIMARY KEY (FacultyID);
insert into faculty values (1,'xxx','xxxxxxxxxxxx',xxx-xxx-xxxx','xxx-xxx-xxxx','professor','20','f');
create table Course(
CourseId number(10),
CourseNumber Varchar2(20),
CourseName Varchar(20),
Description Varchar(20),
CreditHours Number(4),
Status Char(1));
ALTER TABLE Course
ADD Constraint Course_CourseID_pk PRIMARY KEY(CourseID);
insert into course values (1,'cit 100','computer concepts','introduction to PCs','3.0','o');
insert into course values (2,'cit 101','Database Program','Database Programming','4.0','o');
insert into course values (3,'Math 101','Algebra I','Algebra I Concepts','5.0','o');
insert into course values (4,'cit 102a','Pc applications','Aplications 1','3.0','o');
insert into course values (5,'cit 102b','pc applications','applications 2','3.0','o');
insert into course values (6,'cit 102c','pc applications','applications 3','3.0','o');
insert into course values (7,'cit 103','computer concepts','introduction systems','3.0','c');
insert into course values (8,'cit 110','Unified language','UML design','3.0','o');
insert into course values (9,'cit 165','cobol','cobol programming','3.0','o');
insert into course values (10,'cit 167','C++ Programming 1','c++ programming','4.0','o');
insert into course values (11,'cit 231','Expert Excel','spreadsheet apps','3.0','o');
insert into course values (12,'cit 233','expert Access','database devel.','3.0','o');
insert into course values (13,'cit 169','Java Programming I','Java Programming I','3.0','o');
insert into course values (14,'cit 263','Visual Basic','Visual Basic Prog','3.0','o');
insert into course values (15,'cit 275','system analysis 2','System Analysis 2','3.0','o');
create table Section(
SectionID Number(10),
CourseId Number(10),
SectionNumber VarChar2(10),
Days Varchar2(10),
StartTime Date,
EndTime Date,
LocationID Number(10),
SeatAvailable Number(3),
Status Char(1));
ALTER TABLE Section
ADD Constraint Section_SectionID_PK PRIMARY KEY(SectionID);
insert into section values (1,1,'18977','r','21-Sep-2011','10-Dec-2011','1','89','o');
create table Location(
LocationId Number(10),
Building Varchar2(20),
Room Varchar2(5),
Capacity Number(5),
Satus Char(1));
ALTER TABLE Location
ADD Constraint Location_LocationID_pk PRIMARY KEY (LocationID);
insert into Location values (1,'Clevleand Hall','cl209','35','o');
insert into Location values (2,'Toledo Circle','tc211','45','o');
insert into Location values (3,'Akron Square','as154','65','o');
insert into Location values (4,'Cincy Hall','ch100','45','o');
insert into Location values (5,'Springfield Dome','SD','35','o');
insert into Location values (6,'Dayton Dorm','dd225','25','o');
insert into Location values (7,'Columbus Hall','CB354','15','o');
insert into Location values (8,'Cleveland Hall','cl204','85','o');
insert into Location values (9,'Toledo Circle','tc103','75','o');
insert into Location values (10,'Akron Square','as201','46','o');
insert into Location values (11,'Cincy Hall','ch301','73','o');
insert into Location values (12,'Dayton Dorm','dd245','57','o');
insert into Location values (13,'Springfield Dome','SD','65','o');
insert into Location values (14,'Cleveland Hall','cl241','10','o');
insert into Location values (15,'Toledo Circle','tc211','27','o');
insert into Location values (16,'Akron Square','as311','28','o');
insert into Location values (17,'Cincy Hall','ch415','73','o');
insert into Location values (18,'Toledo Circle','tc111','67','o');
insert into Location values (19,'Springfield Dome','SD','69','o');
insert into Location values (20,'Dayton Dorm','dd211','45','o');
Alter Table Student
Add Constraint student_Zip_CK Check(Rtrim (Zip,'1234567890-') is null);
Alter Table Student
ADD Constraint Student_Status_CK Check(Status In('c','t'));
Alter Table Student
ADD Constraint Student_MI_CK2 Check(RTRIM(MI,'abcdefghijklmnopqrstuvwxyz')is Null);
Alter Table Student
Modify pin not Null;
Alter table Faculty
Add Constraint Faculty_Status_CK Check(Status In('f','a','i'));
Alter table Faculty
ADD Constraint Faculty_Rank_CK Check(Rank In ('professor','doctor','instructor','assistant','tenure'));
Alter table Faculty
ADD Constraint Faculty_MI_CK2 Check(RTRIM(MI,'abcdefghijklmnopqrstuvwxyz')is Null);
Update Section Set Starttime = To_date('09-21-2011 6:00 PM', 'mm-dd-yyyy hh:mi pm');
Update Section Set Endtime = To_date('12-10-2011 9:50 PM', 'mm-dd-yyyy hh:mi pm');
alter table Section
Add Constraint StartTime_Status_CK Check (starttime < Endtime);
Alter Table Section
Add Constraint Section_StartTime_ck check (StartTime < EndTime);
Alter Table Section
ADD Constraint Section_CourseId_FK FOREIGN KEY (CourseID) References Course(CourseId);
Alter Table Section
ADD Constraint Section_LocationID_FK FOREIGN KEY (LocationID) References Location (LocationId);
Alter Table Section
ADD Constraint Section_Days_CK Check(RTRIM(Days,'mtwrfsu')IS Null);
update section set seatavailable = '99';
Alter Table Section
ADD Constraint Section_SeatsAvailable_CK Check (SeatAvailable < 100);
Alter Table Course
Add Constraint Course_CreditHours_ck check(CreditHours < = 6.0);
update location set capacity = '99';
Alter Table Location
Add Constraint Location_Capacity_CK Check(Capacity < 100);
Create Table Registration (
StudentID Number(10),
SectionID Number(10),
Constraint Registration_pk Primary key (studentId, Sectionid));
Insert into registration values (1, 2);
Insert into Registration values (2, 3);
Insert into registration values (3, 4);
Insert into registration values (4, 5);
Insert into registration values (5, 6);
Insert into registration values (6, 7);
Insert into registration values (7, 8);
Insert into registration values (8, 9);
insert into registration values (9, 10);
insert into registration values (10, 11);
insert into registration values (9, 12);
insert into registration values (8, 13);
insert into registration values (7, 14);
insert into registration values (6, 15);
insert into registration values (5, 17);
insert into registration values (4, 18);
insert into registration values (3, 19);
insert into registration values (2, 20);
insert into registration values (1, 21);
insert into registration values (2, 22);
insert into registration values (3, 23);
insert into registration values (4, 24);
insert into registration values (5, 25);
Insert into registration values (6, 24);
insert into registration values (7, 23);
insert into registration values (8, 22);
insert into registration values (9, 21);
insert into registration values (10, 20);
insert into registration values (9, 19);
insert into registration values (8, 17);
Create Table courseInstructor(
FacultyID Number(10),
SectionID Number(10),
Constraint CourseInstructor_pk Primary key (FacultyId, SectionID));
insert into courseInstructor values (1, 1);
insert into courseInstructor values (2, 2);
insert into courseInstructor values (3, 3);
insert into courseInstructor values (4, 4);
insert into courseInstructor values (5, 5);
insert into courseInstructor values (5, 6);
insert into courseInstructor values (4, 7);
insert into courseInstructor values (3, 8);
insert into courseInstructor values (2, 9);
insert into courseInstructor values (1, 10);
insert into courseInstructor values (5, 11);
insert into courseInstructor values (4, 12);
insert into courseInstructor values (3, 13);
insert into courseInstructor values (2, 14);
insert into courseInstructor values (1, 15);
Create table grade(
StudentID Number(10),
SectionID Number(10),
Grade Varchar2(1),
Constraint grade_pk Primary key (StudentID, SectionID));
CREATE OR REPLACE TRIGGER TR_CreateGrade
AFTER INSERT ON Registration
FOR EACH ROW
BEGIN
INSERT INTO grade (SectionID,StudentID,Grade)
VALUES(:New.SectionID,:New.StudentID,NULL);
END TR_createGrade;
/
CREATE OR REPLACE FORCE VIEW V_reg_student_course AS
SELECT
Registration.StudentID,
student.LastName,
student.FirstName,
course.CourseName,
Registration.SectionID,
course.CreditHours,
section.Days,
TO_CHAR(StartTime, 'MM/DD/YYYY') AS StartDate,
TO_CHAR(StartTime, 'HH:MI PM') AS StartTime,
TO_CHAR(EndTime, 'MM/DD/YYYY') AS EndDate,
TO_CHAR(EndTime, 'HH:MI PM') AS EndTime,
location.Building,
location.Room
FROM registration, student, section, course, location
WHERE registration.StudentID = student.StudentID
AND registration.SectionID = section.SectionID
AND section.LocationID = location.LocationID
AND section.CourseID = course.CourseID;
CREATE OR REPLACE FORCE VIEW V_teacher_to_course AS
SELECT
courseInstructor.FacultyID,
faculty.FirstName,
faculty.LastName,
courseInstructor.SectionID,
section.Days,
TO_CHAR(StartTime, 'MM/DD/YYYY') AS StartDate,
TO_CHAR(StartTime, 'HH:MI PM') AS StartTime,
TO_CHAR(EndTime, 'MM/DD/YYYY') AS EndDate,
TO_CHAR(EndTime, 'HH:MI PM') AS EndTime,
location.Building,
location.Room
FROM courseInstructor, faculty, section, course, location
WHERE courseInstructor.FacultyID = faculty.FacultyID
AND courseInstructor.SectionID = section.SectionID
AND section.LocationID = location.LocationID
AND section.CourseID = course.CourseID;
SELECT * FROM V_reg_student_course;
SELECT * FROM V_teacher_to_course;
