CPCC DB Phase 3

1. Create the database for Continental Palms Community College (CPCC) in Oracle. Enforce as many integrity constraints as you can.

2. Enter the following sample data into the database.

Department
CODE NAME        CAMPUS  BUILDING   PHONE       FAX         CHAIR
---- ----------- ------- ---------- ----------- ----------- ----------
BUS  Business    East    Lubar      4142292000  4142295999  999123
ENG  Engineering East    Englemann  4142291200  4142294566  999201
ART  Arts        West    Lapham     4142292000  4142295999  999329

Program
NAME                 MINCREDITS MINGPA  DEPARTMENT
-------------------- ---------- ------- ----------
ITM                  20         2.2     BUS
Accounting           20         2.2     BUS
Marketing            20         2.2     BUS
Supply Chain         20         2.2     BUS
Electrical           21         2.5     ENG  
Dancing              20         2       ART

Person
ID                     EMAIL      FIRSTNAME  LASTNAME   GENDER
---------------------- ---------- ---------- ---------- ------
714528                 mvs        Tami       Szczewski  F
741806                 swenson4   Adam       Swenson    M
534076                 srolwig    Gregory    Olwig      M
716469                 efillner   Carter     Fillner    M
662694                 djpeskie   Nathaniel  Peskie     F
725643                 ssalimi    Ryan       Salimi     M
716197                 tlspoerl   Richard    Spoerl     M
688892                 garciaan   Justin     Garcia     M
701712                 grierso2   Betty      Grierson   F
999123                 cjegan     Philip     Egan       M
999201                 ndbrewer   Patrick    Brewer     M
999329                 rhj        Nancy      Johnson    F

Student
ID                     HIGHSCHOOLGPA          MAJOR 
---------------------- ---------------------- --------------------
714528                 2.9                    ITM
741806                 3.2                    ITM
534076                 3.5                    ITM
716469                 3.1                    ITM
662694                 3.2                    ITM
725643                 2.5                    ITM
716197                 2.9                    Accounting
688892                 3.5                    Electrical
701712                 2.2                    Dancing

Course
DEPARTMENT NUM TITLE                CREDITS  PREREQDEPT PREREQNUMBER
---------- --- -------------------- -------- ---------- --------------
BUS        110 Intro to ITM         5
BUS        120 Intro to Accounting  5
BUS        210 Visual Programming   6        BUS        110
BUS        220 Data Management      6        BUS        110
BUS        317 Project Management   6        BUS        210
ENG        110 Digital Electronics  4

Curriculum
PROGRAM              DEPARTMENT COURSENUMBER           REQUIRED
-------------------- ---------- ---------------------- --------
ITM                  BUS        110                    Y
ITM                  BUS        120                    N
ITM                  BUS        210                    N
ITM                  BUS        220                    Y
ITM                  BUS        317                    N
Accounting           BUS        110                    N
Accounting           BUS        120                    Y
Electrical           ENG        110                    Y

Semester
YEAR                   TERM STARTDATE                 ENDDATE
---------------------- ---- ------------------------- -----------------
2021                   SP   27-JAN-21                 15-MAY-21
2021                   SU   23-MAY-21                 23-AUG-21
2021                   F    01-SEP-21                 20-DEC-21
2022                   SP   26-JAN-22                 14-MAY-22

Section
YEAR TERM DEPART COURSE SECTION BUILDING  ROOM START ENDT SEATS INSTRUCTOR
---- ---- ------ ------ ------- --------- ---- ----- ---- ----- ----------
2021 SP   BUS    110     1      Lubar     123  540   615  20
2021 SP   BUS    110     2      Lubar     301  1050  1185 20
2021 SP   BUS    120     1      Lubar     223  540   615  25
2021 SP   BUS    210     1      Lubar     123  720   795  20
2021 SU   BUS    110     1      Lubar     123  540   615  20
2021 F    BUS    220     1      Lubar     123  540   615  20
2021 F    BUS    317     1      Lubar     123  720   795  20
2021 F    ENG    110     1      Englemann 123  720   795  20

SectionMeetingDay
YEAR TERM DEPARTMENT COURSENUMBER SECTIONNUMBER          DAY
---- ---- ---------- ------------ ---------------------- ----
2021 SP   BUS        110          1                      1
2021 SP   BUS        110          1                      3
2021 SP   BUS        110          2                      2
2021 SP   BUS        120          1                      2
2021 SP   BUS        120          1                      4
2021 SP   BUS        210          1                      2
2021 SP   BUS        210          1                      4
2021 SU   BUS        110          1                      1
2021 SU   BUS        110          1                      3
2021 SU   BUS        110          1                      5
2021 F    BUS        220          1                      1
2021 F    BUS        220          1                      3
2021 F    BUS        317          1                      2
2021 F    BUS        317          1                      4

Enrollment
YEAR TERM DEPARTMENT COURSENUMBER SECTIONNUMBER STUDENT GRADE
---- ---- ---------- ------------ ------------- ------- -----
2021 SP   BUS        110          1             714528  F
2021 SP   BUS        110          1             741806  A
2021 SP   BUS        110          2             534076  B
2021 SP   BUS        110          2             716469  C
2021 SP   BUS        120          1             534076  B
2021 SP   BUS        120          1             662694  B
2021 SP   BUS        120          1             716197  A
2021 SP   BUS        210          1             534076  B
2021 SP   BUS        210          1             716469  B
2021 SP   BUS        210          1             662694  D
2021 SU   BUS        110          1             714528  B
2021 SU   BUS        110          1             662694  A
2021 SU   BUS        110          1             725643  A
2021 F    BUS        220          1             741806  B
2021 F    BUS        220          1             716469  A
2021 F    BUS        220          1             662694  F
2021 F    BUS        317          1             534076  A
2021 F    BUS        317          1             716469  A
2021 F    BUS        317          1             662694  B
2021 F    BUS        317          1             714528  B

3. Answer the following queries in Oracle SQL. Sample results are available on Canvas.

• Q1: Get the name, location (campus, building), and phone number of every department.
• Q2: Get the name, building, and phone number of every department at the East campus.
• Q3: Get the list of programs (name and graduation requirements) available at the East campus.
• Q4: Get the list of students (name and email) majoring in Information Technology Management at the East campus.
• Q5: Get the list of students (name and email) majoring in Information Technology Management at the East campus who have completed the number of credits required by the major.
• Q6: Get the list of students (name and email) majoring in Information Technology Management at the East campus who have completed all the courses required by the major. (Hint: This query involves the Division operation in relational algebra, but is notoriously difficult to write in SQL. Consider using sub-queries. Restate the query as "Find every majoring in Information Technology Management at the East campus, such that there does NOT exist a course required by the Information Technology Management major that he or she has not taken yet").
• Q7: Get the list of students (name and email) majoring in Information Technology Management at the East campus who have completed all the courses required by the major and the number of credits required by the major.
• Q8: Get the list of students (name and email) majoring in Information Technology Management at the East campus who have completed all the courses required by the major but not the number of credits required by the major yet.
• Q9: Get the number, title, type (required or elective), and prerequisite (if any) of each course listed in the Information Technology Management program.
• Q10: List the following statistics about each course section listed in the Information Technology Management program offered in Fall 2021: number of credits, total meeting time per week, number of students enrolled, revenue (tuition per credit is $350), and revenue per hour.