ASSIGNMENT # 3 Database Interactions

    1. (50 Points) Create part of mail-order database described on page 9 of your book:

































    a. Write a script named prog3a.sql for creating the ZIPCODES, CUSTOMERS and EMPLOYEES tables.

    b. Write a script named prog3b.sql for population the ZIPCODES, CUSTOMERS and EMPLOYEES tables.

    c. Write a PL/SQL subprogram that takes the old and new values of the zip code and performs an update of zip code values in the mail-order database described on page 9 of the textbook. Note that zip code values appear in three different tables: ZIPCODES, CUSTOMERS and EMPLOYEES. Note also the foreign key referential integrities on zip code. You can assume that a given zip code is always mapped to exactly one city. However, your program should handle spcial situations such as when the zip code to be changed does not exist in the database. In such cases, an appropriate message should be printed. Further, your program should always maintain the integrity of the underlying database.

You should wrap your subprogram within an anonymous PL/SQL block. Save your program in the script file prog3c.sql

1

    2. (50 Points) Create the following database tables through a PL/SQL subprogram:

CREATE TABLE dept

( DEPTNO    NUMBER(3) PRIMARY KEY,
DNAME    VARCHAR2(16),
LOC    VARCHAR2(16));

CREATE TABLE emp

( EMPNO    NUMBER(4) PRIMARY KEY,
ENAME    VARCHAR2(16),
JOB    VARCHAR2(16),

MGR    NUMBER(4),
HIREDATE    DATE,
SAL    NUMBER(7, 2),
COMM    NUMBER(7, 2),
DEPTNO NUMBER(3) NOT NULL REFERENCES DEPT(DEPTNO));

After the tables have been created, in the same PL/SQL block, insert the following data into the tables. When inserting records for each table, if an error occurred, your PL/SQL block should commit the records that have been inserted before the one that caused the error and ignore the rest of the records starting from the one that caused the error condition.

Dept = {(10, 'ACCOUNTING', 'NEW YORK'),

(20, 'RESEARCH',    'DALLAS'),
(30, 'SALES',    'CHICAGO'),
(40, 'OPERATIONS', 'WASHINGTON (D.C.)'),
(50, 'MARKETING', 'BOSTON')}

emp = { (7839, 'KING',  'PRESIDENT', NULL, '17-NOV-81', 5000, NULL, 10), (7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-81', 2850, NULL, 30),

(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, NULL, 10),
(7566, 'JONES', 'MANAGER', 7839, '02-APR-81', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400, 30),

(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-81', 1600, 300, 30),
(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-81', 1500, NULL, 30),
(7900, 'JAMES', 'CLERK',    7698, '03-DEC-81', 950, NULL, 30),
(7521, 'WARD',    'SALESMAN', 7698, '22-FEB-81', 1250, 500, 30),
(7902, 'FORD',    'ANALYST', 7566, '03-DEC-81', 3000, NULL, 20),

(7369, 'SMITH', 'CLERK',    7902, '17-DEC-81', 800, NULL, 20),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-82', 4000, NULL, 20),

(7876, 'ADAMS', 'CLERK',    7788, '12-JAN-83', 1100, NULL, 20),
(7934, 'MILLER', 'CLERK',    7782, '22-JAN-82', 1300, NULL, 10),
(7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-81', 2850, NULL, 30),

(7935, 'JONES', 'ACCOUNT',    7782, '22-JAN-82', 1700, NULL, 10)}


Save your program in the script file prog3d.sql



2
Note: There are two 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 HW3” and must be submitted to your D2L/Assignment 3 Submission page. I will give you one submission locations on the course web site.


Again: For example, for assignment #3, you need to create a folder named your LastName HW3 under

your c: home directory and save your script files prog3a.sql, prog3b.sql, prog3c.sql and prog3d.sql under this folder. Then zip the folder and then submit the zipped file to your D2L/Assignment 3 Submission link



SUBMIT YOUR HW3 FOLDER AS ZIP FILE TO YOUR D2L ASSIGNMENT 3 SUBMISSION LINK FOR GRADING. Make sure only one copy submitted.















































3