HOMEWORK ASSIGNMENT 5

(30 points)

BCIS 4620 -- Spring 2009

Alameda Case

(#5.1: 15 points; #5.2: 15 points)

THIS ASSIGNMENT IS DUE: Mar 12, 2009 by class time.

PLACE THE ASSIGNMENTS IN THE CABINET IN THE 3RD FLOOR BCIS SUITE AREA.

For each step in the assignment you will turn in a printout of whatever SQL Views and Table Views that you are asked to create. Please use you judgment to print in either Portrait or Landscape format as appropriate. Separate by tab dividers each ALPHABETIC letter below. Put all material in a 3-ring binder, with a cover page, table of contents, and appropriate tab dividers.

Follow the same general process for creating, loading, accessing, changing and reporting which you followed in Homework 1 to complete this assignment.

The SQL commands should be entered, saved, and executed from the NX SQL DB2/SQL Wizard environment. Save Tables as tables and SQL statements as Queries.

Read the ALAMEDA CASE from the WINDSOR AND SPENCE Case book (This will be handed out in class)) Data for the case is found in its APPENDIX.

HOMEWORK #5.1 (15 points)

REQUIREMENTS:

Tab A. CREATE and LOAD two separate TABLES:

1. EMPLOYEE table with Employee Number, SS Number, Employee Name. Last Name, First Name, phone, sex, race, date of birth, education, and citizenship. Employee Address is OPTIONAL.

2. SKILLS table with Employee Number, Skill, Date, Training and Pimary/Secondary (P/S) skill.

3. Create 2 separate SELECT statements to list the complete contents of both Tables.

Tab B. CREATE a VIEW of your own choosing which combines at least one column of both tables.

This means JOIN the tables to a new VIEW table.

1. CREATE VIEW DBSLIST (turn in SQL View & Table View).

2. Create a SELECT statement against the VIEW. Then list Employee Number, SS Number, Employee Name, Skill, Skill Date. Sort by Employee Last Name, First Name (Turn in SQL View & Table View).

Tab C. Issue a SELECT which JOINS Employee Number of EMPLOYEE and Employee number of SKILLS

1. Print out employee information for each EMPLOYEE with ON-THE-JOB TRAINING (OJT) locations on the same report (SQL View and Table View).

Tab D. UPDATE all employees with the skill ROOFER to FRAMER.

1. Rerun the previous SELECT (Step C.) statement, HIGHLIGHT UPDATES! (SQL View & Table View)

Tab E. INSERT yourself into the employee table.

INSERT yourself into EMPLOYEE table and INSERT yours skills into the SKILLS Table.

1. Print out the INSERT statement for the EMPLOYEE and SKILL tables (SQL View).

2. Run a select that shows BOTH table INSERTS were successful. HIGHLIGHT the new inserted data! (Table View)

Tab F. EXPORTed Tables & Views

1. Include listings of your EXPORTed tables. Recommend these are .SQL files

a. EMPLOYEE table with data

b. SKILLS table with data

2. Also, save the SQL code you used to create the NEWVIEW and JOIN queries

a. NEWVIEW .SQL file

b. JOIN Query .SQL file

Assignment 5 (5.1 & 5.2)

ALAMEDA

Table of Contents

Tab	Description	Page
5.1	ALAMEDA Create Database Space
A	EMPLOYEE Table (EMPLOYEE) – CREATE SQL VIEW
A	EMPLOYEE Table – Table View
A	Skills Table (SKILLS) – CREATE SQL VIEW
A	Skills Table – Table View
A	EMPLOYEE Table – SELECT SQL VIEW
A	SKILLS Table – SELECT SQL View
B	Create NEWVIEW - SQL VIEW
B	NEWVIEW – Table View
B	Query for NEWVIEW – SQL VIEW
B	Results of Query – Table View
C	JOIN Query of EMPLOYEE & SKILLS – SQL VIEW
C	Results for Query – Table View
D	Update SQL skill Roofer to Framer -- SQL VIEW
D	Rerun Query for SKILL – Highlight Changes – Table View
E	Insert Personal Information into EMPLOYEE Table – SQL VIEW
E	Result for Insert (Highlight changes) – Table View
E	Insert Personal Data into Skills Table – SQL VIEW
E	Result for Insert (Highlight changes) – Table View
F	Exported EMPLOYEE.SQL file
F	Exported SKILLS.SQL file
F	Exported NEWVIEW.SQL file
F	Exported JOIN Query .SQL file
5.2	Semantic Object Model
G	Semantic Object Diagram
G	Semantic Object Report
G	SQL Generated Code
H	ER Diagram
H	Relation List
H	ERD Attribute Report