1. (35 Points) Manually create the following database tables:
CREATE TABLE dept
( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(16),
LOC VARCHAR2(16)
);
CREATE TABLE dept_shadow
( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(16),
LOC VARCHAR2(16),
USER VARCHAR2(32),
MODTIME CHAR(17)
);
Create a trigger to track all inserts into a table. Specifically, for each reocrd
inserted into the DEPT table, the trigger should insert a duplicate record into the
DEPT_SHADOW table along with the information of the user who performs the insertion (the
USER column) as well as the date/time of the insertion (the MODTIME column). The MODTIME
column keeps track of the date/time in the following character string format:
MM/DD/YY hh:mm:ss.
Save your program in the script file prog4a.sql
2. (35 Points) For the DEPT table created in problem #1 above,insert the following records by using bulk binding
Dept = {(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'DALLAS'),
(50, 'MARKETING', 'BOSTON')}
Save your program in the script file prog4b.sql
3. (30 Points) Create a PL/SQL block to retrieve all the information about each department from the DEPT table and print the information to the screen by using a PL/SQL table of records.
Save your program in the script file prog4c.sql
Note: There are three parts to this assignment; each part may requires you to submit a file. So please create a folder for this assignment and submit an electronic copy of your solution files of every question/part, all in one folder zipped and named “LastName HW4” and must be submitted to your D2L/Assignment 4 Submission page. I will give you one submission locations on the course web site.
Again: For example, for assignment #4, you need to create a folder named your LastName HW4 under your c: home directory and save your script files prog4a.sql, prog4b.sql, and prog4c.sql under this folder. Then zip the folder and then submit the zipped file to your D2L/Assignment 4 Submission link
SUBMIT YOUR HW4 FOLDER AS ZIP FILE TO YOUR D2L ASSIGNMENT 5 SUBMISSION LINK FOR GRADING. Make sure only one copy submitted.
