BCIS 4620 – Spring, 2009

Assignment #2:  NetExpress SQL

CREATE EMP/DEPT Database

(10 points)

                                                               

DUE:  Class 4: Feb 12

                                                                                

The following files may be found on the course website: 

BCIS 4620 Faculty/Course Website; SQL_COBOL.zip file and EmpDept.Zip file.

 

REQUIREMENTS:

TURN IN Only those printouts, which have a double asterisk in front of the step number (**).   Use 3-ring binder (hard cover), tabs, cover page, etc.   I suggest organizing the work under 3 tabs:                                    

    1.  Table creates and loads, with views                                    

    2.  Sequentially ordered SELECT statements (by order executed)              

    3.  Table Alters, updates and refresh programs                             

 

***** PLEASE PUT YOUR NAME ON THE SPINE OF THE NOTEBOOK *****

 

You will need to obtain the following new input datasets, which may be found on 4620 Class Website: SQL_COBOL.zip file:

 

-EMPLOAD.SQL,

-DEPTLOAD.SQL

-Vphone.SQL

 

IMPORT the SQL code into SQL Wizard environment.  You may wish to use the RUN BATCH option.  BE SURE to change the file prefixes LLLL to your LOCATION name and SSSS to the SQLID you will be using before executing the SQL files. 

 

SECTION/TAB 1

 

**EMPLOAD.SQL  - creates a table called EMP and loads data into the EMP table                                                                    

Print out the Table View after loading the data

**DEPTLOAD.SQL - creates a table called DEPT  and loads data into the DEPT table

Print out the Table View after loading the data                           

 

**Vphone.SQL  - Creates a view using EMP and DEPT                            

Print out the SQL View and the Table View                                               

 

SECTION/TAB 2

You must translate the remaining files them to the NetExpress SQL/DB2 Environment.  Execute the following programs in the order shown. Be sure to recode NNXX to the LOCATION.SQLID that you have stored the tables in SECTION/TAB 1 above :

SELECT01.SQL - Simple query on the EMP table

 

ALTER1.SQL -  Creates indexes for both tables                             

 

ALTER2.SQL - Creates primary keys for both tables                         

 

**ALTER3.SQL  - Creates foreign keys for the table EMP                    

Write the SQL code, run and print out the SQL view.

 

**SELECT01.SQL                                                             

Be sure to print out FIRST RUN before you executed the job again.... for obvious reasons (unless you rename the file); Printout the SQL View and the Table          

 

**SELECT02.SQL - A set of queries on the tables EMP and DEPT. Printout the

SQL View and the Table View.                

 

SELECT03.SQL

Printout the SQL View and the Table View                                                            

 

SELECT04.SQL                                                              

Be sure to print out FIRST RUN before you executed the job again.... for obvious reasons (unless you rename the file)           

 

**UPDATE.SQL  - Changes the contents of the job field for EMP rows          

with job = ‘DESIGNER’ and workdept = ‘D11’; Print out the SQL View and the Table View                      

 **SELECT04.SQL- NOTE:  Run both before and after update        

Be sure to print out FIRST RUN before you executed the job again.... for obvious reasons (unless you rename the file)

 The jobs should be run in the sequence shown.

 

Assignment 2

Table of Contents

Tab

Description

Page

1

Emp Table – Table View (Landscape)

1

1

Dept Table – Table View

2

1

Create View – SQL

3

1

View – Table View

    4

2

Alter 3 – SQL View

5

2

Select 01 (after alters) – SQL

6

2

Result of Select 01 – Table View

7

2

Select 02 – SQL

    8

2

Result of Select 02 – Table View(s)

9

3

Select 04 (after update) – SQL View

10

3

Result of Select 04 – Table View

11