Laboratory Assignment #3 Now that you have good data in all your fileslet’s star

ID: 3861200 • Letter: L

Question

Laboratory Assignment #3

Now that you have good data in all your fileslet’s start registering students for classes.

In this lab, you will create junction tables that will allow students to register and you will assign instructors to classes. You will also create a trigger that will automatically create the data for the

Grade table. In this lab, you will create 3 new tables:

Assignment:

Registration table

The Registration table should include section information, course information, student informationat minimum (be sure to pick the most relevant fields - keep in mind data redundancy) and shouldhave a composite key to prevent duplicate registration records.

Grade table

The grade table should include,section information, course information, student information (again be sure to pick the proper fields), and a grade field (which should be

NULL initially) will beinserted by a trigger.

CSCI 2370 - Database

Systems Programming

Course Instructor table

The Course Instructor table should tell you what instructor(s) teach

what courses. It should include course/section information and Instructor information at a minimum (again be sure to pick the correct fields, eliminating redundancy).

All tables listed above will have a composite key,and foreign key references. I’m expecting to see 3 composite keys and 6 foreign key references.

Rules:

at least 3 courses.

Create 5 classes that have multiple instructors

Create 2 views

Key:

You DO NOT need to change your existing database structure of your course, instructor, or student table.

TURN IN:

Create a report showing what courses students are taking. This will be a VIEW. (NOTE:Make the data “make sense”, like a CSCC course listing – fields include Student ID, Name,

Course Name/Title, Section ID, Credit Hours, Days, start/end (times and dates),

building/room.

Create a report showing what courses are taught

by what instructors. This will be a VIEW (NOTE: Make the data “make sense” include the faculty name, course name/title, section id,days, start/end (times/dates), and building/room)

3.Turn in copies of the data (insert statements),trigger, joins, views used for the reports and reports used for this lab.

Below is the table I created earlier.

DROP TABLE sTUDENT;
CREATE TABLE STUDENT (
StudentID NUMBER(10),
FirstName VARCHAR2(20),
LastName VARCHAR2(20),
MI CHAR(1),
Address VARCHAR2(20),
City VARCHAR2(20),
State CHAR(2),
Zip VARCHAR2(10),
HomePhone VARCHAR2(10),
WorkPhone VARCHAR2(10),
Email VARCHAR2(20) ,
DOB DATE,
PIN VARCHAR2(10),
Status CHAR(1)
);
DROP TABLE FACULTY;
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(20),
Status CHAR(10)
);
DROP TABLE COURSE;
cREATE TABLE COURSE(
CourseID Number(10),
CourseNumber Varchar2(20),
CourseName Varchar(20),
Description Varchar(20),
CreditHours Number(4),
Status Char(1)
);
DROP TABLE SECTION;
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)
);
DROP TABLE LOCATION;
CREATE TABLE LOCATION (
LocationID Number(10),
Building Varchar2(20),
Room Varchar2(5),
capacity Number(5),
Status char(1)
);
DROP SEQUENCE S_id;
CREATE SEQUENCE S_ID
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1
CACHE 20;
DROP INDEX i_sTUDENT_first_Name;
CREATE INDEX i_Student_first_Last_Name on
Student(lastname);

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (1, 'Booky', 'Phillips', 'L', 'PO BOX 339', 'Pickerington', 'OH', '43147-9998', '6142812811', '6142541254', 'bphillips@email.com', '12-JAN-1963', '15246', 'F');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (2, 'Denise', 'Miller', 'S', '172 E Main St', 'Columbus', 'OH', '43215-9997', '6145412544', '6142541254', 'dsmith@email.com', '01-FEB-1964', '45246', 'S');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (3, 'Rocky', 'Smith', 'R', '45 Broad St', 'Westerville', 'OH', '43081-8888', '6145542211', '6142541254', 'rsmith@email.com', '02-APR-1950', '99999', 'J');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (4, 'Tammy', 'Stone', 'B', '88 E Broad St', 'Columbus', 'OH', '43215-7777', '6142812811', '6142541254', 'Tstone@email.com', '04-MAR-1958', '88855', 'R');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (5, 'Anne', 'Thomas', 'P', '172 E State St', 'Columbus', 'OH', '43215-7777', '6144667245', '6142541254', 'athomas@email.com', '05-MAY-1971', '99911', 'F');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (6, 'Paula', 'Miller', 'R', '250 Civic Center Dr', 'Columbus', 'OH', '43215-8888', '6142812811', '6142541254', 'Pthomas@email.com', '06- JuN-1972', '44555', 'R');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (7, 'Melissa', 'Harrison', 'G', 'PO BOX 154', 'Obetz', 'OH', '43147-4444', '6142812811', '6142541254', 'mHarrison@email.com', '07-JUL-1974', '77898', 'J');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (8, 'Ellen', 'Andrews', 'M', '17 E State St', 'Columbus', 'OH', '43215-5555', '6145415411', '6142541254', 'EAndrews@email.com', '08-AUG-1964', '11512', 'F');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (9, 'Mary', 'Green', 'S', '10 Broad Meadows Ct', 'Columbus', 'OH', '43214-6666', '6142812811', '6142541254', 'mGreen@email.com', '09-SEP-1963', '04455', 'S');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (10, 'Michael', 'Jones', 'W', '23 E Main St', 'Circleville', 'OH', '43113-4444', '6145415455', '6142541254', 'mjones@email.com', '10-OCT-1973', '55246', 'J');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (11, 'Timothy', 'Johnson', 'T', '45 S Scioto', 'Circleville', 'OH', '43113-3333', '4281281122', '6142541254', 'tjohnson@email.com', '11-NOV-1975', '95246', 'F');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (12, 'Ryan', 'Moore', 'E', '90 N Court St', 'Pickerington', 'OH', '43147-6666', '6145454455', '6142541254', 'Rmoore@email.com', '12-DEC-1978', '65246', 'R');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (13, 'Theresa', 'Anderson', 'C', '8 W High St', 'Columbus', 'OH', '43207-7777', '6145414455', '6142541254', 'tanderson@email.com', '03-APR-1973', '5746', 'S');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (14, 'Cathy', 'Ream', 'A', '123 Weber Rd', 'Worthington', 'OH', '43085-6666', '6148845544', '6142541254', 'Cream@email.com', '01-AUG-1968', '28246', 'R');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (15, 'Toni', 'Baloney', 'R', '2210 Trent Rd', 'Columbus', 'OH', '43229-8888', '6142812811', '6142541254', 'Tbaloney@email.com', '07-OCT-1969', '09090', 'J');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (16, 'Daphne', 'Miller', 'R', '10 Main St', 'Obetz', 'OH', '43101-4444', '6145415477', '6142541254', 'dmiller@email.com', '02-MAR-1971', '34343', 'F');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (17, 'Keith', 'Palmer', 'W', '205 Court St', 'Circleville', 'OH', '43113-9909', '7402812811', '6142541254', 'kpalmer@email.com', '22-JAN-1974', '84575', 'F');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (18, 'Renee', 'Pierson', 'S', '4578 Heatherton Dr', 'Columbus', 'OH', '43229-1234', '6148461415', '6142541254', 'rpierson@email.com', '04-JAN-1978', '98789', 'J');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (19, 'Maria', 'Gordon', 'P', '5515 Briarcliff', 'Reynoldsburg', 'OH', '43068-5644', '6142812811', '6142541254', 'mgordons@email.com', '12-Feb-1975', '43422', 'R');

INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status

) VALUES (20, 'Randy', 'Thompson', ' ', '1459 Northtown Blvd', 'Columbus', 'OH', '43229-4551', '6148891544', '6142541254', 'rthompson@email.com', '12-JAN-1963', '65454', 'S');

/* These are the insert statements for the Faculty table. Be sure to use a sequence for one of the tables */

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (1, 'sam', 'cook', 'p', '6142812811', '6142541254', 'newbie', '5 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (2, 'mike', 'jones', 'l', '6142812811', '6142541254', 'historian', '3 YEARS', 'T');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (3, 'denise', 'smith', 'h', '6142812811', '6142541254', 'professor', '25 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (4, 'tim', 'miller', 'o', '6142812811', '6142541254', 'Adjunct', '5 YEARS', 'L');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (5, 'ann', 'meek', 'p', '6142812811', '6142541254', 'Professor', '35 YEARS', 'R');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (6, 'betty', 'lane', 'w', '6142812811', '6142541254', 'associate', '5 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (7, 'greg', 'morris', 's', '6142812811', '6142541254', 'Professor', '5 YEARS', 'L');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (8, 'wayne', 'grill', 'a', '6142812811', '6142541254', 'associate', '15 YEARS', 'R');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (9, 'mika', 'wiley', 'q', '6142812811', '6142541254', 'associaTe', '10 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (10, 'debbie', 'wine', 'y', '6142812811', '6142541254', 'Adjunct', '5 YEARS', 'L');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (11, 'sonya', 'fellows', 'z', '6142812811', '6142541254', 'associate', '5 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (12, 'leslie', 'merritt', 'p', '6142812811', '6142541254', 'professor', '35 YEARS', 'R');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (13, 'thomas', 'prist', 'l', '6142812811', '6142541254', 'associate', '5 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (14, 'rodney', 'reynolds', 'm', '6142812811', '6142541254', 'TA', '2 YEARS', 'A');

INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status

) VALUES (15, 'cathy', 'frazier', 'n', '6142812811', '6142541254', 'associate', '5 YEARS', 'A');

/* These are the insert statements for the Course table. Be sure to use a sequence for one of the tables */

INSERT INTO COURSE (

courseid, coursenumber, coursename, description, credithours, status ) VALUES (1, 'ACT-1211', 'Financial Accounting', 'Financial Accounting', '3', 'E');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (2, 'CSCI-2370', 'Database Systems', 'Database Systems', '3', 'I');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (3, 'CSCI-2489', 'Mobile Development', 'Mobile Development', '4', 'D');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours , status) VALUES (4, 'CSCI-2447', 'JavaScript Fundamentals', 'JavaScript Fundamentals', '4', 'B');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (5, 'CSCI-2412', 'Web Database', 'Web Database', '3', 'A');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (6, 'GIS-2100', 'Intro to GIS Data', 'Intro to GIS Data', '3', 'E');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (7, 'GIS-1102', 'GIS in Industry', 'GIS in Industry', '4', 'I');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (8, 'GIS-1101', 'Acquire GIS Data', 'Acquire GIS Data', '4', 'E');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (9, 'HOSP-1153', 'Nutrition', 'Nutrition', '3', 'D');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (10, 'HOSP-1109', 'Food Principles', 'Food Principles', '3', 'S');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (11, 'IMM-2601', 'Game Development', 'Game Development', '4', 'R');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours , status) VALUES (12, 'IMM-1530', 'Screenwriting', 'Screenwriting', '4', 'Z');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (13, 'MKTG-1110', 'Marketing Principles', 'Marketing Principles', '3', 'U');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (14, 'SES-1006', 'Intro to Golf', 'Intro to Golf', '3', 'Y');

INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (15, 'SES-2216', 'Basics of Golf', 'Basics of Golf', '4', 'B');

/* These are the insert statements for the Location table. Be sure to use a sequence for one of the tables */

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (1, 'Eibling Hall', ' 209', 00030, 'A');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (2, 'Delaware Hall', ' 210', 00030, 'B');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (3, 'Nestor Hall', ' 211', 00030, 'C');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (4, 'Delaware', ' 212', 00030, 'D');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (5, 'Eibling Hall', ' 213', 00030, 'E');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (6, 'Acquinas Hall', ' 214', 00030, 'F');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (7, 'Nestor Hall', ' 215', 00030, 'G');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (8, 'Eibling Hall', ' 216', 00030, 'H');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (9, 'Nestor Hall', ' 217', 00030, 'I');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (10, 'Eibling Hall', ' 218', 00030, 'J');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (11, 'Delaware Hall', ' 220', 00030, 'K');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (12, 'Eibling Hall', ' 221', 00030, 'L');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (13, 'Eibling Hall', ' 222', 00030, 'M');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (14, 'Eibling Hall', ' 223', 00030, 'N');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (15, 'Eibling Hall', ' 224', 00030, 'O');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (16, 'Delaware Hall', ' 220', 00030, 'P');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (17, 'Eibling Hall', ' 221', 00030, 'Q');

INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (18, 'Delaware Hall', ' 222', 00030, 'R');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (19, 'Acquinas Hall', ' 223', 00030, 'S');

INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (20, 'Eibling Hall', ' 224', 00030, 'T');

/* These are the insert statements for the Section table. Be sure to use a sequence for one of the tables */