Create the Grand Hill College Database Solution

rem

rem script to create the college database

rem Dave Leskiw

rem Aug 2022

rem

set echo on

rem

rem drop child tables first and then the parent tables

rem

drop table ghc_expertise cascade constraints;

drop table ghc_course cascade constraints;

drop table ghc_faculty cascade constraints;

drop table ghc_department cascade constraints;

rem

rem Create parent tables first and then their children after

rem

rem Create the department table without any constraints, add the

rem constraints after.

rem

CREATE TABLE GHC_DEPARTMENT (

    dept_no NUMBER,

    dept_name VARCHAR2(20)

);

rem

rem Add the constraints

rem

ALTER TABLE GHC_DEPARTMENT

    ADD CONSTRAINT sys_ghc_dept_pk PRIMARY KEY (dept_no);

ALTER TABLE GHC_DEPARTMENT

    MODIFY (dept_name NOT NULL);

ALTER TABLE GHC_DEPARTMENT

    ADD CONSTRAINT sys_hgc_dept_name_uk UNIQUE (dept_name);

rem

rem create ghc_course table with constraints

rem

CREATE TABLE GHC_COURSE (

   course_code CHAR(7) CONSTRAINT SYS_GHC_COURSE_PK PRIMARY KEY,

   course_title VARCHAR2(60) NOT NULL,

   credits NUMBER(2)

        NOT NULL

        CONSTRAINT SYS_GHC_COURSE_CREDITS_CK CHECK (credits between 1 and 9)

);

rem

rem create ghc_faculty table with constraints

rem notice that the foreign key is done at the column level

rem so you don't need the keyword foreign key

rem

CREATE TABLE GHC_FACULTY (

   faculty_id NUMBER CONSTRAINT SYS_GHC_FAC_PK PRIMARY KEY,

   surname VARCHAR2(50) NOT NULL,

   firstname VARCHAR2(50) NOT NULL,

   date_hired DATE NOT NULL,

   date_fired DATE,

   is_active NUMBER(1) NOT NULL

       CONSTRAINT SYS_GHC_IS_ACTIVE_CK CHECK (is_active in (0,1)),

   dept_no NUMBER CONSTRAINT SYS_GHC_FAC_GHC_DEPT_FK

      REFERENCES ghc_department(dept_no)

     NOT NULL

);

rem

rem create ghc_expertise table with constraints

rem the primary key constraint has to be done at the table level

rem

CREATE TABLE GHC_EXPERTISE (

    faculty_id NUMBER,

    course_code CHAR(7),

    CONSTRAINT sys_ghc_expert_pk PRIMARY KEY (faculty_id, course_code),

    CONSTRAINT sys_ghc_expert_faculty_fk FOREIGN KEY (faculty_id)

        REFERENCES GHC_FACULTY (faculty_id),

    CONSTRAINT sys_ghc_expert_course_fk FOREIGN KEY (course_code)

        REFERENCES GHC_COURSE (course_code)

);


now just add everything below do not change what is above

 

Write SQL code to perform the following modifications to the Grand Hill College Database.

 

1. Add a new department.

a. Department number: 100

b. Name: Astrophysics

 

2. Add a new instructor.

a. Faculty ID: 1001

b. Name: Danny Faulkner

c. Hired: Jan 1, 2022

d. This instructor is currently available and teaches in the Astrophysics department

 

3. Add the following courses that Danny Faulkner has expertise to teach: a. Course Code: APHY202, The Solar System, 5 credits b. Course Code: APHY203, Nebula, 5 credits c. Course Code: APHY204, Global Clusters, 5 credits

 

4. The Nebula Course has moved to the third semester, so its new course code is 'APHY302'. Make that change throughout the database.

 

Hint: You will need to add the new course first and then update/delete existing data.

 

5. Remove Danny Faulkner (ID: 1001) and the record of his expertise. Leave the new courses and the Astrophysics department in the databases.