BCIS 4620 – Spring 2009 – ALAMEDA CASE

Assignment #8: NetExpress SQL Updates, Queries/Reports

(20 points)

DUE: April 16, 2009 PRIOR TO YOUR CLASS.

This is a continuation of the Alameda Case Project

-----------------------------------------------------------------------------------------------------------

A. TABLE UPDATE REQUIREMENTS (10 POINTS)

REVIEW CASE C: ALAMEDA construction company in windsor & spence casebook with appendix C data. Revisit the project and requirements section very carefully. Examine the DATA in Appendix C for understanding of the attribute DOMAINS. Include additional data for PROJECT table, including starting date (see website).

Other TEST DATA may be created by you. Please, create sufficient data to TEST your system. Generally this means choosing a robust set of transactions, which will explore "every" branch of your logic processing. Clearly, this may not be possible. However, choosing 50 transactions of exactly the same type is not as satisfactory as choosing 10 completely different transactions.

On page 102 of the CASE, question no. 6 mentions changing the database. Modify the database and the applications to meet the partners' needs for new information on the current projects and completion dates by employee Print copies of new tables, showing updates and/or changes. HIGHLIGHT changes with a colored marking pen.

This is part of the turn-in requirement in a. below.

In addition you should be able to ADD new data to each of these TABLES.

This will be part of your FINAL project requirements. Ideally this could be done from one of your MENU screens.

Turn in the following:

1. The ADD/UPDATE/DELETE needed for each of the simple tables/objects in YOUR database: EMPLOYEE, SKILLS_TRAINING, NEXT_OF_KIN, RACE, CITIZENSHIP, EDUCATION, for example. Conceptually begin thinking about how one application could update more than one physical table. Turn in all SQL source code and print outs.
2. MINIMUM REQUIREMENT: Perform the following ADD/UPDATEs:

1) ADD (INSERT) a NEW employee.

2) ADD (INSERT) several new items to SKILLS_TRAINING for the new employee. There are many parts to the work-history object; you may choose just one part.

3) UPDATE NEXT_OF_KIN for one employee

4) DELETE NEXT_OF_KIN for one employee

3. Additional Minimum Requirements (Set of 3 Proofs for Salary & Project Tables)

5) ADD (INSERT) new salary/project

6) UPDATE salary item/project item

7) DELETE salary/project

SUGGESTION:

When displaying add/updates/deletes to tables and objects the following sequence of printouts seems to be the most economical and easy to follow:

1. Print the Before table/object listing (highlight items that will be deleted)

2. Print the screen prints for each add/update/display screen (one of each)

3. Print the After table/object listing (changes highlighted); When deleting something, highlight the before item and show where it is missing on the after-printout.

NOTE: Your will need to do a PROOF of this operation for every table of your FINAL project. You MUST SHOW the COMPLETE HIGHLIGHTED DOCUMENTATION for each transaction. Use print screens of add/update/delete I/O screens to show what the data looks like going into the system.

B. ADDITIONAL REPORTING REQUIREMENTS (10 POINTS)

[Specify sort order used for each report]

1. State Government Report by Education Level of Worker

2. Federal Government Report(s) which provides information on the percentage of each by:

a. minority worker category;

b. gender

c. age; and

d. citizenship status.

NOTE: This may be 4 separate reports.

3. Insurance Company reports with required Next-of-kin data.

4. Employee Skill/Training Status report--Must include both skills and training

5. Employee Salary/History Report--Must include at their current title and salary at least.

6. Employee Roster with key data for each employee (name, address, phone, SSN, etc.). Indicate type of sorting in HEADING.

7. Employee Project Report (sort by Project, Start Date, and Name, show skills)

Name ____________________ TOTAL SCORE_____/20

HOMEWORK ASSIGNMENT #8

Alameda Construction Co. (20 POINTS)

POINTS SCORE

___NOTEBOOK _____/+1

___ORGANIZATION + 1/2

___INDEXED TABS + 1/2

___TABLE OF CONTENTS + 1/2

___A. UPDATES & ADDS (Before/After/Update screen w/HIGHLIGHTing -1 @) ____/+9

___EMPLOYEE (1 ADD/insert) + 1

___SKILL_INFO (3 ADDS/insert) + 2

___NEXT_OF_KIN (update) + 1

___NEXT_OF_KIN (Delete) + 1

Other Tables (PROOF OF OPERATION)

Before/Actions/After

___PROJECT (Add/Update/Delete) ___ ___ ___ + 2

___SALARY (NEW w/ REPORT) ___ ___ ___ + 2

___B. REPORTS ____/+10

	[Each Report: SQL Code + ½ TableView + ½]
___1.	State Government Report by Education Level of Worker		___ /+1
___2.	Federal Government Report(s) which provides information on the percentage of each by:		___ /+4
	a. minority worker category;	___ /+1
	b. gender	___ /+1
	c. age; and	___ /+1
	d. citizenship status.	___ /+1
	NOTE: This may be 4 separate reports.
___3.	Insurance Company reports with required Next-of-kin data.		___ /+1
___4.	Employee Skill/Training Status report--Must include both skills and training		___ /+1
___5.	Employee Salary/History Report--Must include at their current title and salary at least.		___ /+1
___6.	Employee Roster with key data for each employee (name, address, phone, SSN, etc.). Indicate type of sorting in HEADING.		___ /+1
___7.	Employee Project Report (sort by Project and START Date and Name; show skills)		___ /+1