BCIS 4620 – Spring 2009
Assignment #Final Project:
[100 points; 40 BONUS points available]
DUE: Friday – April 30, 2009 by 5:00 pm;
=====================================================================
REVISION: APRIL 09,
2009
SUBJECT TO
REVISION DURING SEMESTER until the official Freeze Date: 4/13/2008
GENERAL
REQUIREMENTS
Much
of this project was completed while you were completing earlier assignments (In
particular #5, #6, #7 and #8). However,
the final project should be EXTREMELY WELL DOCUMENTED. The easier it is to find the required items
in your project folders the higher your final grade will be.
FINAL
PROJECT FOLDER
The
following outline was provided at the start of the semester. It is GENERIC outline for a typical Database
Project. You may need to adapt this
outline for YOUR project-- the ALAMEDA CASE.
A 3” to 5” three-ring binder should suffice.
FINAL PROJECT FOLDER OUTLINE
TITLE PAGE [1
Point]
COVER page; also, make sure your name is
clearly visible on the binder’s exterior spine
TABLE OF
CONTENTS [6 Points]
I suggest a "table of contents"
for each major section and sub-section as well.
I.
EXECUTIVE
SUMMARY & OVERVIEW (30 points)
** 1-2 pages (double-spacing is
permitted; font size may NOT be smaller 11 points).
A. EXECUTIVE SUMMARY
[3 points]
Please review the Executive Summary
Guidelines in EXECSUM.DOC on the website. HINT:
Write after you have finished all of the other requirements, but this
will count as much as 5% of the final report grade. The entire narrative section will count
between 20% and 30% of the final project grade.
B. PROJECT OVERVIEW [20 Points; includes Section I.C]
Narrative form (Be sure to include at
least one page for each of these sections)
1.
Overview of the
business environment. (2-3 pages)
Essentially a written description of
firm’s current business operating environment based upon the case materials and
your understanding of the business
[1-2 pages for
each of the items below:]
2.
Description of
the system design:
Technical specifications
What hardware and systems software was
used?
3.
Operational
functionality
What are the KEY
functions/tasks performed?
4.
Operational
procedures
How to manually
operate the system. Put in a copy of the Menu Hierarchy Chart.
5.
Description of
database design
Overview of design approach you used
with chart depicting the design (SO diagram or ER diagram).
6.
Assumptions made
and system limitations
Personnel
assumptions, cost, time period, data, system, organizational assumptions, etc.
7.
Recommended
future enhancements
As an internal or external consultant,
you will want to demonstrate other possibilities for future IT system
improvements that will drive the business model..
8.
Description of
end of "period" and year close out procedure (by file).
For example, weekly close out, monthly
closed out, quarterly close out and annual close out procedures. These may be
recommendations.
9.
Test procedures
followed
How was the system tested? What data? What software?
10. Anomaly prevention features & Data Integrity
Controls.
Normalization criteria used, primary keys, foreign key constraints, etc.
11. Systems security and control features
Password systems used; file protection;
backup & recovery systems; disaster recovery systems?
C. CONCLUSIONS AND RECOMMENDATIONS (1-2
pages)
II. APPENDICES [70
points; Most of your programming outputs go here]
Table of
Contents for the Appendix [2 points]
A. Diagrams (Must depict the current system):
-Semantic Object Diagrams (from
TableDesigner)
-E/R diagrams (from ACCESS or
equivalent)
-One-Page LIST of Relations (tables)
along with Primary & foreign key definitions
-TableDesigner SQLGen.Txt file
-Menu
Hierarchy Chart(s)
B. Domanin Definition
Reports from TableDesigner & ACCESS Analyzer
(Must depict the current system)
-TableDesigner
Object reports
-Domain/field Definitions from Access
Document Analyzer or equivalent ERD tool
C. TableView listing of each table and view.
TableViews with SQL SELECT statements
used to display the tables. The reports should be nice looking formatted
reports of the data in your system for each table and or view. Include the CREATE TABLE code and data
load procedure used. Code used to
create all primary keys must be shown; foreign keys (optional). Each table should include a set of the
following documents:
1.
TableView
2.
SQL
SELECT Statement
3.
CREATE
TABLE or VIEW statement or screen print
4.
Data
load procedure with data used (NOT required for View, unless data was loaded to
multiple tables through a View)
5.
Primary
key install documentation (NOT required for View)
D. Menu system programs and screen prints
1.
Any additional SELECT queries (e.g., Queries not used in Views)
2.
Any other SQL statements you have used, which is not specifically related to one of the required reports should be
included here, also
3.
Any CREATE Views from earlier assignments, not included elsewhere in
this report.
4. [BONUS POINTS: 10 POINTS; Partial credit may be awarded]
Create a minimum 2-level system with at least 5 objects or actions
per screen.
Use any code you wish (COBOL, for example). Lowest level must call/execute either SELECT,
INSERT, UPDATE, or DELETE SQL statements.
E. Specific Reporting Requirements for
Fully
documented, menu-driven system for Alameda Company, which will produce the
following reports or and which meets or exceeds all the CASE REQUIREMENTS on p.
102. Refer to Assignments #7 and #8 and
there related score sheet for specific printed requirements for each part: TableView, SQLView, sorted or Grouped, etc.
1. LIST OF EMPLOYEES AND THEIR SKILLS SORTED BY SKILL
TYPES (Homework 7 & 8) Sort alphabetically by name and skill.
2.
LIST OF
EMPLOYEES AND THE PROJECTS THEY ARE ASSIGNED TO
(Homework 7) Sort
by project name and employees’ starting date.
3.
EMPLOYEE NEXT OF
KIN REPORT.
(Homework
7) Be sure to include Employee Name,
Address, Phone Number, and the Next-of-kin Name, Address and Phone Number. Sort by Employee last name,
first name.
4.
AFFIRMATIVE
ACTION REPORTS.
(Homework
7 and 8) Table/Report
should display employee counts and percentages by:
a.
minority worker
category (race)
b.
gender
c.
age groups (very neat use of multiple views and an non-equi-join);
d.
Citizenship
status.
HINT:
This is an excellent time to use more than one view, a subquery, or
both.
BONUS [5 points]: Write this
report in COBOL in NX SQL Environment.
5.
STATE GOVERNMENT
REPORT (Homework 8)
a.
Sort
by Education levels and then Name (Last name, First name)
6.
INSURANCE
COMPANY REPORT (Homework 8)
a.
Show
employee name and Next of Kin. Sort by
Employee Name
7.
EMPLOYEE
SKILL/TRANING STATUS REPORT (Homework 8)
a.
Include both skills and training (names and codes)
8.
EMPLOYEE
SALARY/HISTORY REPORT (Homework 8)
a.
Include current title and salary and all primary and
secondary salaries
9.
EMPLOYEE ROSTER
(Homework 8)
a.
Include key employee data (not just codes); name,
address, phone, SSN, etc.
10. EMPLOYEE PROJECT REPORT (Homework 8)
a.
Sort by Project, Employee Name, show skill being
used
11. [5 BONUS
POINTS] EMPOLYEE ROSTER REPORT (#E.9 above) in COBOL with SQL.
F. Proof of System Operation & Listing of
all I-U-D [Insert (Add), Update, and Delete programs)] (a.k.a., CRUD).
CLEARLY demonstrate proofs of UPDATES/ADD/DELETE functionality. Perform at least one Update/Add/Delete on EVERY Table in your database with examples
of proof of functionality (COLOR-CODED
HIGHLIGHTING; for example, Red=Delete; Green=Add; Yellow=Update). Suggested documentation for each table should follow this pattern (including any intersection tables):
1)
Before table listing [1 TableView; highlight before update and delete
fields]
2)
Add/Update/Delete source code [3 separate statements; SQLView or Screen
Print]
3)
After table listing [1 TableView; highlight after add, update, and
deleted record’s “previous” location]
******************************
BONUS
25 MAXIMUM POINTS
G.
BONUS
assignments
[Additional Reports may be specified by management up to FREEZE
date; however, the maximum number of possible bonus points will not change.]
1.
[10
Bonus Points] Disk [CD or zip] with installable system.
a.
All SQL code
b.
All Datasets used to load data
c.
Include the .EXP files used to offload the system
d.
Include the .IMP files required to install the system at a new
location
e.
NOTE: Additional requirement: Include Word Document with instructions on
“How to Load System”
2.
[5
Bonus Points] Interactive Menu System with ID & Password Query
functionality.
a.
You may use any programming language that interfaces with NX SQL;
must use embedded SQL statements to maintain the ID/Password table(s).
b.
Program must interactively query the user for ID and Password
c.
Program must allow authorized user to add, update, and delete IDs
and passwords.
d.
IDs and Passwords must be stored in the NX SQL database as tables or
views.
3.
[5
Bonus Points] COBOL Update Program
a.
Program must use embedded SQL to update any field in the Employee
Table. Use transaction codes, similar to
those used in COBSQL program
b.
Must be able to add and delete selected records
c.
Use INFILE.FILE batch transaction file for inputs
d.
Use OUTFILE.FILE for transaction summary/audit report.
e.
Turn in copies of source code, input and output files.
f.
Show before and after Employee Table with highlighting.
4.
[5
Bonus Points] Interactive Update Program
a.
Same
as 3. above, except, you may use any language other than COBOL with embedded
SQL code
b.
Turn in copies of source code, input and output files, and SAMPLE
screen prints.
c.
Show before and after Employee Table with highlighting.
5.
[5
Bonus Points] AFFIRMATIVE ACTION REPORTS
in Host Language with SQL.
a.
Same
as E.4. above, except, you may use any language other than COBOL with embedded
SQL code
b.
Turn in copies of source code, input tables and output files.