ASSIGNMENT # 4 Packages, Triggers, and Collections Solution

$35.00 $29.00

(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…

5/5 – (2 votes)

You’ll get a: zip file solution

 

Description

5/5 – (2 votes)
  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

  1. (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

  1. (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.

ASSIGNMENT # 4 Packages, Triggers, and Collections Solution
$35.00 $29.00