Definitely oracle SQl developer and oracle SQL database should be used.

Definitely oracle SQl developer and oracle SQL database should be used.


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 browsers) Put at the left upper corner your student name, student number, and the today’s date in all screen shots Apply the correct 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. Currently 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 errors 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 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 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 professor 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 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 FORMAT 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 5 Optional-Mandatory Delete Create a trigger to unassociate a section from a professor to delete the professor 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 trigger 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 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 enter 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, 0123456789, 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 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 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 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,