I need a solution for: Submit the entire script for the Ivey Database Model as a

Question

I need a solution for: Submit the entire script for the Ivey Database Model as a single text file. The file must include scripts to: drop the tables you are creating (in case they've already been created within the schema I'm using) create the tables for the project insert sample data into the tables produce the reports listed below using aliases Reports Faculty: List of all faculty Fields: first name Sort: last name Input: none Advisors: List of all students and their advisors Fields: student's first name, student's last name, advisor's first name, advisor's last name Sort: student's last name Input: none Student status: List of all students and their enrollment status Fields: first name, last name, current enrollment status, years enrolled, academic advisor Sort: years enrolled Input: none Course enrollment report for selected term Fields: course number/name, professor (last name comma first name), student (last name comma first name) Sort: course number Input: term Course completion status report: List of all courses a student has started and the status Fields: student last name, student first name, course number, course name, status Sort: Student last name, course number Input: none Thesis committee report Fields: student name (as last comma first), committee member name (as last comma first), chair indicator (a yes/no indication of whether that committee member is the chair) Sort: student last name, committee member last name Input: none Course sequence: List of all courses and their prerequisite Fields: course number, course name, course prerequisite number, course prerequisite name Sort: course number Input: none Please review the complete "Create and Use Database Instructions" page before submitting this assignment.

As you will see, I have already added values. These are my created tables I MUST use:

DROP TABLE Advisor CASCADE CONSTRAINTS; DROP TABLE Office CASCADE CONSTRAINTS; DROP TABLE Committee CASCADE CONSTRAINTS; DROP TABLE ThesisCommittee CASCADE CONSTRAINTS; DROP TABLE Thesis CASCADE CONSTRAINTS; DROP TABLE Enroll CASCADE CONSTRAINTS; DROP TABLE Student CASCADE CONSTRAINTS; DROP TABLE Faculty CASCADE CONSTRAINTS; /*------------------------------------------------------------------------ */ /* create tables */ /*------------------------------------------------------------------------ */ CREATE TABLE faculty ( facultyid NUMBER(10) NOT NULL PRIMARY KEY , fname varchar2(20) NOT NULL , lname varchar2(20) NOT NULL ); CREATE TABLE enroll ( enrollid NUMBER(10) NOT NULL PRIMARY KEY , enrolldate DATE , enrolltype varchar2(38) ); CREATE TABLE student ( studentid NUMBER(10) NOT NULL PRIMARY KEY , firstname varchar2(15) NOT NULL , lastname varchar2(30) NOT NULL , streetaddress varchar2(30) , state char(2) , zip varchar2(9) , enrollid NUMBER(10) ); CREATE TABLE advisor ( Studentid NUMBER(10), facultyid NUMBER(10) , Advisor_Type varchar2(20) ); CREATE TABLE office ( officeid NUMBER(10) NOT NULL PRIMARY KEY , officenumber NUMBER(4) , facultyid NUMBER(10) ); CREATE TABLE thesis ( thesisid NUMBER(10) NOT NULL PRIMARY KEY , thesistitle varchar2(50) , studentid NUMBER(10) ); CREATE TABLE thesiscommittee ( thesiscommitteeid NUMBER(5) NOT NULL PRIMARY KEY , thesisid NUMBER(10) ); CREATE TABLE committee ( committeemember varchar(25) , thesiscommitteeid NUMBER (5) , facultyid NUMBER(10) , ischair varchar2(3) ); /*------------------------------------------------------------------------ */ /* add constraints */ /* beyond primary keys already implemented /*------------------------------------------------------------------------ */ ALTER TABLE student ADD ( CONSTRAINT student_enrollid_fk FOREIGN KEY (enrollid) REFERENCES enroll(enrollid) ); ALTER TABLE thesis ADD (CONSTRAINT thesis_studentid_fk FOREIGN KEY (studentid) REFERENCES student(studentid) ); ALTER TABLE office ADD (FOREIGN KEY (facultyid) REFERENCES faculty(facultyid) ); /*------------------------------------------------------------------------ */ /* insert data */ /*note: because of referential constraint, */ /* enroll data must be loaded first */ /*------------------------------------------------------------------------ */ INSERT INTO enroll VALUES (4444, '17-DEC-11', 'full-time'); INSERT INTO enroll VALUES (4445, '02-FEB-13', 'part-time'); INSERT INTO enroll VALUES (4446, '12-OCT-15', 'withdrawn'); INSERT INTO enroll VALUES (4447, '13-NOV-11', 'on leave'); INSERT INTO enroll VALUES (4448, '19-SEP-11', 'part time'); INSERT INTO student VALUES (1, 'Lousia', 'Alcott', 'Wind Ave', 'NY', 13202, 4444); INSERT INTO student VALUES (2, 'Isaac', 'Asimov', 'Rose Lane', 'NY', 13213, 4445); INSERT INTO student VALUES (3, 'Emily', 'Bronte', 'River Street', 'NY', 13211, 4446); INSERT INTO student VALUES (4, 'Thomas', 'Eliot', 'Brook Street', 'NY', 13312, 4447); INSERT INTO student VALUES (5, 'Kurt', 'Vonnegut', 'Stream Street', 'NY', 13222, 4448); /*------------------------------------------------------------------------ */ /*commit */ /*------------------------------------------------------------------------ */ COMMIT; /*------------------------------------------------------------------------ */ /* insert data */ /*note: because of referential constraint, */ /* faculty data must be loaded first */ /*------------------------------------------------------------------------ */ INSERT INTO faculty VALUES (500, 'Marth', 'Graham'); INSERT INTO faculty VALUES (501, 'Mikhail', 'Baryshinokov'); INSERT INTO faculty VALUES (502, 'Jerome', 'Robbins'); INSERT INTO faculty VALUES (503, 'Arthur', 'Mitchell'); INSERT INTO faculty VALUES (504, 'Kir', 'Jooss'); INSERT INTO office VALUES (300, 32, 500); INSERT INTO office VALUES (301, 54, 501); INSERT INTO office VALUES (302, 120, 502); INSERT INTO office VALUES (303, 66, 503); INSERT INTO office VALUES (304, 230, 504); INSERT INTO advisor VALUES (1, 500, 'Academic Advisor'); INSERT INTO advisor VALUES (2, 501, 'Academic Advisor'); INSERT INTO advisor VALUES (3, 502, 'Academic Advisor'); INSERT INTO advisor VALUES (4, 503, 'Academic Advisor'); INSERT INTO advisor VALUES (5, 504, 'Academic Advisor'); /*------------------------------------------------------------------------ */ /*commit */ /*------------------------------------------------------------------------ */ COMMIT; /*------------------------------------------------------------------------ */ /* insert data */ /*note: because of referential constraint, */ /* thesis data must be loaded first */ /*------------------------------------------------------------------------ */ INSERT INTO advisor VALUES (1, 502, 'Thesis advisor'); INSERT INTO advisor VALUES (2, 504, 'Thesis Advisor'); INSERT INTO advisor VALUES (3, 503, 'Thesis Advisor'); INSERT INTO advisor VALUES (4, 501, 'Thesis Advisor'); INSERT INTO advisor VALUES (5, 500, 'Thesis Advisor'); INSERT INTO thesis VALUES (201, 'Little Women', 1); INSERT INTO thesis VALUES (202, 'I, Robot', 2); INSERT INTO thesis VALUES (203, 'Is 5', 4); INSERT INTO thesis VALUES (204, 'Cyber Security', 3); INSERT INTO thesis VALUES (205, 'Web Architecture', 5); INSERT INTO THESISCOMMITTEE VALUES (600, 201); INSERT INTO THESISCOMMITTEE VALUES (602, 202); INSERT INTO THESISCOMMITTEE VALUES (603, 203); INSERT INTO THESISCOMMITTEE VALUES (604, 204); INSERT INTO THESISCOMMITTEE VALUES (605, 205); INSERT INTO committee (thesiscommitteeid, facultyid) VALUES (600, 500); INSERT INTO committee (thesiscommitteeid, facultyid, ischair) VALUES (601, 501, 'Yes'); INSERT INTO committee (thesiscommitteeid, facultyid) VALUES (602, 502); INSERT INTO committee (thesiscommitteeid, facultyid, ischair) VALUES (603, 503, 'Yes'); INSERT INTO committee (thesiscommitteeid, facultyid) VALUES (604, 504); COMMIT; DROP TABLE Course CASCADE CONSTRAINTS; DROP TABLE Prereq CASCADE CONSTRAINTS; DROP TABLE Section CASCADE CONSTRAINTS; DROP TABLE Term CASCADE CONSTRAINTS; DROP TABLE Professor CASCADE CONSTRAINTS; DROP TABLE Sectionenrollment CASCADE CONSTRAINTS; CREATE TABLE course ( courseno NUMBER(5) NOT NULL PRIMARY KEY , coursename varchar2(35) , prereqid NUMBER(5) ); CREATE TABLE prereq ( prereqid NUMBER(5) NOT NULL PRIMARY KEY , courseno NUMBER(5) ); CREATE TABLE section ( sectionid NUMBER(5) NOT NULL PRIMARY KEY , sectionno NUMBER(5) , courseno NUMBER(5) , facultyid NUMBER(5) ); CREATE TABLE term ( termid NUMBER(5) NOT NULL PRIMARY KEY , termname varchar2(10) ); CREATE TABLE professor ( professorid NUMBER(5) NOT NULL PRIMARY KEY , professorname varchar(30) , facultyid NUMBER(10) ); CREATE TABLE sectionenrollment ( Studentid NUMBER (10) NOT NULL REFERENCES student(studentid) , sectionid NUMBER(5) NOT NULL REFERENCES section(sectionid) , status VARCHAR2(15) ); /*------------------------------------------------------------------------ */ /* add constraints */ /* beyond primary keys already implemented /*------------------------------------------------------------------------ */ ALTER TABLE course ADD ( FOREIGN KEY (prereqid) REFERENCES prereq(prereqid) ); ALTER TABLE prereq ADD ( FOREIGN KEY (courseno) REFERENCES course(courseno) ); ALTER TABLE section ADD (FOREIGN KEY (courseno) REFERENCES course(courseno), CONSTRAINT section_facultyid_fk FOREIGN KEY (facultyid) REFERENCES faculty(facultyid) ); ALTER TABLE professor ADD (CONSTRAINT professor_facultyid_fk FOREIGN KEY (facultyid) REFERENCES faculty(facultyid) ); /*------------------------------------------------------------------------ */ /* insert data */ /*note: because of referential constraint, */ /* prerequisite data was loaded first */ /*------------------------------------------------------------------------ */ INSERT INTO course (courseno, coursename) VALUES (101, 'Introduction to Biology'); INSERT INTO course (courseno, coursename) VALUES (102, 'Database I'); INSERT INTO course (courseno, coursename) VALUES (103, 'Introduction to Networks'); INSERT INTO course (courseno, coursename) VALUES (104, 'Algebra'); INSERT INTO course (courseno, coursename) VALUES (105, 'Introduction to Statistics'); INSERT INTO prereq VALUES (802, 101); INSERT INTO prereq VALUES (803, 102); INSERT INTO prereq VALUES (804, 103); INSERT INTO prereq VALUES (805, 104); INSERT INTO prereq VALUES (806, 105); INSERT INTO course VALUES (201, 'Advanced Biology', 802); INSERT INTO course VALUES (202, 'Database II', 803); INSERT INTO course VALUES (203, 'Advanced Networks', 804); INSERT INTO course VALUES (204, 'Geometry', 805); INSERT INTO course VALUES (205, 'Statistics II', 806); /*------------------------------------------------------------------------ */ /*commit */ /*------------------------------------------------------------------------ */ COMMIT; /*------------------------------------------------------------------------ */ /* insert data */ /*note: because of referential constraint, */ /* term data and section data must be loaded first */ /*------------------------------------------------------------------------ */ INSERT INTO professor VALUES (1000, 'Marth Graham', 500); INSERT INTO professor VALUES (1001, 'Mikhail Baryshinokov', 501); INSERT INTO professor VALUES (1002, 'Jerome Robbins', 502); INSERT INTO professor VALUES (1003, 'Arthur Mitchell', 503); INSERT INTO professor VALUES (1004, 'Kir Jooss', 504); INSERT INTO term VALUES (2000, 'Fall'); INSERT INTO term VALUES (2001, 'Winter'); INSERT INTO term VALUES (2002, 'Spring'); INSERT INTO term VALUES (2003, 'Summer'); INSERT INTO section VALUES (4001, 21, 101, 500); INSERT INTO section VALUES (4002, 22, 102, 501); INSERT INTO section VALUES (4003, 23, 103, 502); INSERT INTO section VALUES (4004, 24, 104, 503); INSERT INTO section VALUES (4005, 25, 105, 504); INSERT INTO section VALUES (4006, 26, 201, 500); INSERT INTO section VALUES (4007, 27, 202, 501); INSERT INTO section VALUES (4008, 28, 203, 502); INSERT INTO section VALUES (4009, 29, 204, 503); INSERT INTO section VALUES (4010, 30, 205, 504); INSERT INTO sectionenrollment VALUES (1, 4001, 'B+'); INSERT INTO sectionenrollment VALUES (2, 4002, 'B'); INSERT INTO sectionenrollment VALUES (3, 4003, 'A-'); INSERT INTO sectionenrollment VALUES (4, 4004, 'B-'); INSERT INTO sectionenrollment VALUES (5, 4005, 'Waived'); INSERT INTO sectionenrollment VALUES (1, 4006, 'In progress'); INSERT INTO sectionenrollment VALUES (2, 4007, 'Incomplete'); INSERT INTO sectionenrollment VALUES (3, 4008, 'In progress'); INSERT INTO sectionenrollment VALUES (4, 4009, 'B+'); INSERT INTO sectionenrollment VALUES (5, 4010, 'Incomplete'); /*------------------------------------------------------------------------ */ /*commit */ /*------------------------------------------------------------------------ */ COMMIT;

Explanation / Answer

--Faculty: List of all faculty

--Fields: first name

--Sort:last name

--Input: none

SELECT fname

FROM faculty

ORDER BY lname;

--Advisors: List of all students and their advisors

--Fields: student's first name, student's last name, advisor's first name, advisor's last name

--Sort: student's last name

--Input: none

SELECT student.firstname , student.lastname, faculty.fname,faculty.lname

FROM student,advisor,faculty

WHERE (student.studentid=advisor.studentid) AND

(faculty.facultyid=advisor.facultyid)

ORDER BY student.lastname;

--Student status: List of all students and their enrollment status

--Fields: first name, last name, current enrollment status, years enrolled, academic advisor

--Sort: years enrolled

--Input: none

SELECT student.firstname , student.lastname, enroll.enrolltype, (SYSDATE-enrolldate)/365 yearsenrolled,faculty.fname,faculty.lname

FROM student,advisor,faculty,enroll

WHERE (student.studentid=advisor.studentid) AND

(faculty.facultyid=advisor.facultyid) AND

(student.enrollid=enroll.enrollid)

ORDER BY yearsenrolled;

--Thesis committee report

--Fields: student name (as last comma first), committee member name (as last comma first), chair indicator (a yes/no indication of whether that committee member is the chair)

--Sort: student last name, committee member last name

--Input: none

SELECT student.lastname , student.firstname , committeemember,ischair

FROM student,thesiscommittee,committee,thesis

WHERE (student.studentid=thesis.thesisid) AND

(thesiscommittee.thesisid=thesis.thesisid) AND

(committee.thesiscommitteeid=thesiscommittee.thesiscommitteeid)

ORDER BY student.lastname;

--Course sequence: List of all courses and their prerequisite

--Fields: course number, course name, course prerequisite number, course prerequisite name

--Sort: course number

--Input: none

SELECT *

FROM course

ORDER BY courseno;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.