Question
Avoid using the " around object names because it can cause havoc with case sensitivity and other issues. The required comment header needs to be included.
Submit the entire script for the Ivey Database Model as a single text file. The file must include scripts to:
1. drop the tables you are creating (in case they've already been created within the schema I'm using)
2. create the tables for the project
3. insert sample data into the tables
4. produce the reports listed below
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
Solution Preview
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
---------------------------------------------------------- DDL for Table COURSE
--------------------------------------------------------
CREATE TABLE "COURSE" ("COURSEID" NUMBER, "COURSENAME" VARCHAR2(50), "PREREQUISITE" NUMBER)
;
Insert into COURSE (COURSEID,COURSENAME,PREREQUISITE) values (1,'Principles of Accounting I',null);
Insert into COURSE (COURSEID,COURSENAME,PREREQUISITE) values (2,'Principles of Accounting II',1);
Insert into COURSE (COURSEID,COURSENAME,PREREQUISITE) values (3,'Principles of Finance',null);
Insert into COURSE (COURSEID,COURSENAME,PREREQUISITE) values (4,'Investment Management',3);
Insert into COURSE (COURSEID,COURSENAME,PREREQUISITE) values (5,'Contemporary Applied Management',null);
Insert into COURSE (COURSEID,COURSENAME,PREREQUISITE) values (6,'Financial Markets and International Finance',4);
--------------------------------------------------------
-- Constraints for Table COURSE
--------------------------------------------------------
ALTER TABLE "COURSE" MODIFY ("COURSEID" NOT NULL );
ALTER TABLE "COURSE" ADD CONSTRAINT "COURSE_PK" PRIMARY KEY ("COURSEID") ;
--------------------------------------------------------
-- Ref Constraints for Table COURSE
--------------------------------------------------------...