CST2355 - Database Systems
Assignment #3 Database Construction and Processing [15%]
This assignment relates to the following Course Learning Requirements:
CLR 2 - Develop Advanced Database Design and Normalization
CLR 4 - Use Oracle Procedural programming language (PL/SQL) to write programs that contain SQL statements
CLR5 - Develop advanced Database Queries
Background
You are working for a college, designing a new grading system for students. From talking with the administration, you drafted a conceptual data model. From it you will construct a database and implement basic processing on the data.
Conceptul Data Model
Instructions
Follow the steps and ONLY submit your own work.
Adhere to the Academic Integrity policy -- no sharing, no copying, no group work
Use ONLY the following resources and tools --
Modules 8 and 9
Complex Programming video
Tools specified by the instructions and your facilitator (no substitutions)
Scripts provided with this assignment
Screenshot Check List. Make sure you have the following in each screenshots when applicable --
Maximize the output window
Include ONLY the output specified in the instructions
Include ONLY the information requested in a single screen shot (exclude menus, toolbars, and object
owsers)
Put at the left upper corner your student name, student number, and the today’s date in all screen shots
Apply the co
ect naming standards in diagrams and manually created SQL scripts for entities, tables, attributes, fields, and constraints
Resize each object so all text you modified is visible
Add your first and last name initials at the end of each table’s name
Enlarge and crop your screen shot for ease of viewing
1
Intersection Tables
The conceptual data model specifies that there is a N:M relationship for PROGRAM and COURSE. Cu
ently the database implementation is a 1:N. You will change the PROGRAM -- COURSE relationship to N:M by adding an intersection table.
Go to the SQL CLI tool and run the script
CST2355 - A3 - Create DB.SQL
Run the script again to ensure there are no run e
ors
Go to the SQL GUI tool and create a new script. Scan the Create DB script for snippets of code to complete the following steps.
Add a statement to drop the foreign key constraint for PROGRAM -- COURSE
Add a CREATE TABLE statement
Name the table to be created based on the position of the PROGRAM entity to the COURSE entity. If COURSE is left of PROGRAM the name is COURSE_PROGRAM_INT_NN. If the COURSE is right of PROGRAM the name is PROGRAM_COURSE_INT_NN. NN is your first and last name initials.
Add to the CREATE TABLE a primary key column ProgramCourseIntID and primary key constraint ProgramCourseIntPK
Add to the CREATE TABLE a foreign key column ProgramID and a foreign key constraint IntProgramFK for PROGRAM -- PROGRAM_COURSE_INT
Add TO THE CREATE TABLE a foreign key column CourseID and a foreign key constraint IntCourseFK for COURSE -- PROGRAM_COURSE_INT
Before the CREATE TABLE statement, add a DROP TABLE statement to drop the intersection table
After the CREATE TABLE statement add the following statement to add the data to the intersection table by filling in the
and INSERT INTO (, ProgramID, CourseID)
SELECT C.CourseID, C.ProgramID, C.CourseID
FROM PROGRAM P JOIN COURSE C ON P.ProgramID = C.ProgramID;
COMMIT;
Add your name, student number, and today’s date as a comment on the first line of your script
Go to the SQL CLI, clear your screen, maximize your screen, and then copy/paste to run your script
Add a single screenshot of your script’s SQL and its results on the next page
Submit Intersection Table Script and Results
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
2
Database Change Request
In the conceptual data model there is a SectionNumber. This column was implemented as a number data type. The school now wants SectionNumber to be an alpha-numeric (containing both numbers and letters). You will change SectionNumber to a VARCHAR2 column.
Go to the SQL GUI tool and create a new script
Walk through the CST2355 - A3 - Create DB script and locate the code that changes the size of the description field. Create a new script and copy/paste that code into that script.
Change your new script so when ran it replaces the SectionNumber data type in SECTION to VARCHAR2 (10)
Add your name, student number, and today’s date as a comment on the first line of your script
Go to the SQL CLI, clear your screen, maximize your screen and then copy/paste to run your script
Run DESC SECTION
Add a single screenshot of your script’s SQL and its results on the next page
Submit Database Change Request Script
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
Optional Parent and Mandatory Child
The benefits of a mandatory parent optional child is that no additional code needs to be added to enforce the relationship. However, the conceptual data model shows PROFESSOR -- GRADE and SECTION -- PROFESSOR are optional parent - mandatory child relationships. This means you need to add additional code to enforce these relationships. You will use mandatory-mandatory code to create optional-mandatory code.
3
Optional-Mandatory Insert
Create a triggers with the following logic
- inserting a professor and a new section if one doesn’t exist
- inserting only the new section when there is no professor specified
- inserting a new section and new professor when that professor doesn’t exist
Go to the SQL GUI tool and create a new script
Walk through the CST2355 - A3 - Mandatory - Mandatory script and locate the INSERT section. Copy and paste the code into a new script.
Add your name, student number, and today’s date as a comment on the first line of your script
Change the comment from Mandatory - Mandatory to Optional - Mandatory
Rename columns, variables, and triggers. If named department (parent) then change to professor (parent). If named program (child) then change to section (child). Follow the naming standard (UPPER_CASE / ProperCase) used in the script.
In the PROFESSOR_INSERT trigger change the INSERT INTO PROFESSOR statement to insert all new column values into professo
In the SECTION_INSERT trigger change the IF statement to specify that both ProfessorCount = 0 and the new ProfessorID IS NOT NULL. Change the INSERT INTO SECTION statement to insert all new column values accept for SectionID
Go to the SQL CLI, clear your screen, and then copy/paste to run your script
Add a single screenshot of your script’s SQL and its compiled results on the next page
Submit Optional-Mandatory Insert Script
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
4
Testing Optional-Mandatory Insert
Go to the SQL CLI
Setup your tests by running the following…
DELETE FROM V_SECTION WHERE SectionID >= 31;
DELETE FROM V_PROFESSOR WHERE ProfessorID >= 6;
SET LINESIZE 200
COLUMN SectionID FORMAT 99
COLUMN Semester FORMAT A3
COLUMN SectionName FORMAT A20
COLUMN CourseID FORMAT 99
COLUMN ProfessorID FORMAT 99
COLUMN SectionNumber FORMAT A4
COLUMN FirstName FORMAT A10
COLUMN LastName FORMAT A10
COLUMN Email XXXXXXXXXXFORMAT A10
Clear the screen and add your name, student number, and today’s date as a comment
Test to insert a SECTION with no PROFESSOR by running the following…
INSERT INTO V_SECTION (Semester, SectionNumber, StartDate, EndDate, CourseID, ProfessorID)
VALUES ('22F', '450A', CURRENT_DATE, CURRENT_DATE, 1, null);
COMMIT;
SELECT * FROM SECTION WHERE SectionID >= 31;
Test to insert a SECTION and the new PROFESSOR specified…
INSERT INTO V_SECTION (Semester, SectionNumber, StartDate, EndDate, CourseID, ProfessorID)
VALUES ('22F', '450B', CURRENT_DATE, CURRENT_DATE, 1, 6);
COMMIT;
SELECT * FROM SECTION WHERE SectionID >= 31;
SELECT * FROM PROFESSOR WHERE ProfessorID >= 6;
Add a single screenshot of your test results on the next page
Submit Optional-Mandatory Insert Test
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
5
Optional-Mandatory Delete
Create a trigger to unassociate a section from a professor to delete the professo
Go to the SQL GUI tool and create a new script
Walk through the CST2355 - A3 - Mandatory - Mandatory script and locate the DELETE section. Copy and paste the code into a new script.
Add your name, student number, and today’s date as a comment on the first line of your script
Remove the PROGRAM_DELETE trigge
Rename columns, variables, and triggers. If named department (parent) then change to professor (parent). If named program (child) then change to section (child). Follow the naming standard (UPPER_CASE / ProperCase) used in the script.
In the SECTION_DELETE trigger change DELETE FROM SECTION to UPDATE SECTION and have it set the ProfessorID to NULL. Do not change the WHERE clause.
Go to the SQL CLI, clear your screen, and then copy/paste to run your script
Add a single screenshot of your script’s SQL and its compiled results on the next page
Submit Optional-Mandatory Delete
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
6
Batch Exports and Imports
In Windows you can do extracts using .BAT files in DOS. Each .BAT file runs a .CTL or .CTRL file when extracting from and importing into the database. You will extract records from and import records into your GRADE table.
Extract CST2355 - A3 - EXTRACT-IMPORT.ZIP to the folder C:\CST2355\A3. Rename each file accordingly --
- EXTRACT_PROG.CTL and rename to EXTRACT_GRADE.CTL
- IMPORT_PROG.CTL and rename to IMPORT_GRADE.CTL
Modify EXTRACT.BAT using a text editor. Edit the file to specify --
- user and password for where your GRADE table is located
- CST2355\A3 path and the EXTRACT_GRADE CTL file name
Modify EXTRACT_GRADE.CTL using a text editor. Edit the file to specify --
- CST2355\A3 path and GRADE.CSV to file name to be created
- selecting all columns and rows from the grade table
Modify IMPORT.BAT using a text editor. Edit the file to specify --
- user and password for where your GRADE table is located
- CST2355\A3 path and the IMPORT_GRADE CTL file file name
Modify IMPORT_GRADE.CTL using a text editor. Edit the file to specify --
- path and file name for then GRADE.CSV to be imported
- change table to GRADE
- replace the columns with those from GRADE (same order as in GRADE)
Ensure all files are saved. Go to DOS and maximize the window
Copy and paste the following into DOS then hit ente
CLS
CD C:\CST2355\A3
EXTRACT.BAT
EXIT
IMPORT.BAT
On the next line add your name, student number, and today’s date using REM. For example --
Bob Smith, XXXXXXXXXX, Jan 10, 2010
Add a single screenshot of DOS window on the next page
Submit Batch Export / Import Screenshot
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
7
Rollups
The GROUP BY options ROLLUP and CUBE allow you store a total in the same column as the values that determine that total. It does this by adding addition rows to your results.
Go to the SQL GUI tool and create a new script
Walk through the CST2355 - A3 - ProgramCount script and locate the ROLLUP section. Copy and paste the code into a new script.
Add your name, student number, and today’s date as a comment on the first line of your script
Change your new script so it produces a ROLLUP like the following. Join the PROGRAM_COURSE_INT and COURSE tables. Then change the field used in the COUNT aggregate function.
DepartmentName
ProgramName
CourseCount
Business
Finance
3
Computer Science
Computer Programming
3
Engineering
Electrical Engineering
3
Humanities
Philosphy
3
Mathematics
Applied Math
3
Business
3
Computer Science
3
Engineering
3
Humanities
3
Mathematics
3
15
Go to the SQL CLI, clear your screen, and then copy/paste to run your script
Add a single screenshot of your script’s SQL and its results on the next page
Submit ROLLUP SELECT and Results
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here
8
Pivots
Pivoting query results is heavily used for reporting. It allows users to compare numbers and see patterns. A PIVOT is where you take data that is in rows and convert it to show that same data as column headings.
Go to the SQL GUI tool and create a new script
Walk through the CST2355 - A3 - ProgramCount script and locate PIVOT section. Copy and paste the code into a new script.
Add your name, student number, and today’s date as a comment on the first line of your script
Run the script in SQL GUI and review the results. Determine how the results are determined by how DEPARTMENT parent and PROGRAM child are used in the query.
Change your new script to display course counts under three programs. For this query there is a PROGRAM parent and COURSE child. Output should resemble the following.
CourseName
'Applied Math'
'Electrical Engineering'
'Finance'
BUS1000
0
0
1
BUS2000
0
0
1
BUS3000
0
0
1
CST1000
0
0
0
CST2000
0
0
0
CST3000
0
0
0
ENG1000
0
1
0
ENG2000
0
1
0
ENG3000
0
1
0
HUM1000
0
0
0
HUM2000
0
0
0
HUM3000
0
0
0
MAT1000
1
0
0
MAT2000
1
0
0
MAT3000
1
0
0
Go to the SQL CLI, clear your screen, and then copy/paste to run your script
Add a single screenshot of your script’s SQL and its results on the next page
Submit PIVOT SELECT and Results
Confirm your screenshot satisfies the screenshot checklist on page 2
paste your screenshot here>
9
Functions
You will create a new function that determines the letter grade for a student for a student.
Go to the SQL GUI tool and create a new script
Walk through the CST2355 - A3 - Create DB script and locate ACADEMIC PACKAGE/PACKAGE BODY. Copy and paste the code into a new script.
Add your name, student number,