CSC452 - DATABASE PROGRAMMING  - ASSIGNMENT # 3 - Solved

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

 

 

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)}