Submission Sheet
IT360 Spring 2008 Project
Department financial tracker
Project Leader info Due:
Wednesday, 30 January, 2008
Project Requirements and
Responsibilities Due: Wednesday, 6 February, 2008
ER Model Due: Wednesday, 20
February, 2008
Relational Model,
Normalization, SQL Due: Wednesday, 5 March, 2008
Final Project Due: Wednesday,
23 April, 2008
Team:__________________________________________________
Team Member Names:_________________________________________________
Extra Credit Performed: _______________________________________________
This
is a Programming Project. For this project, midshipmen will work in
groups of four or five. The individual grade for the project will be based on
the team grade and the individual performance on completing the project. You
may not discuss the project with anyone outside of your group. You may seek
clarifications pertaining to system requirements from Capt. Miller, Dr.
Crainiceanu, or Dr. DeLooze.
Final submission
requirements (each team will have one submission):
·
Electronic submission: Upload
the files created for each part of the project to Project on Blackboard
·
Hard copy: Hard copies of the files
created for each part of the project, stapled to this submission sheet.
Project Description
In this project you will create a
department financial tracker for material and travel budgeting. This
application will be used by the Computer Science Department at USNA, and
possibly by other departments. The point of contact for your final client, the
CS Department, is Capt. Kasey Miller, USMC, the financial officer of the
department. Dr. Crainiceanu and Dr. DeLooze can also
answer questions regarding the requirements for the project.
Each department at USNA, including the Computer Science Department, has a budget for the expenses incurred by the department. These expenses include travel (faculty travel regularly to conferences, and expenses are paid by the department) and materials (books, computers, printers, supplies, etc.). The financial officer is in change of keeping track of all expenses, and making sure the department is not overspending.
The attached Excel spreadsheet is what Capt. Miller is currently using to manage the expenses. He would like you to create a department financial tracker for material and travel budgeting. The DB system would be based on the attached documents (Excel spreadsheet and Purchase Request) with four user groups:
User group Individual Actions
-Requestor faculty and staff submit and view records
-department secretary Kecia McGill submit, review, edit
-finance officer Capt. Kasey Miller, USMC full permissions
-supervisor CAPT Thomas Logue , USN read only
The final result should be an online system that can replace the current paper and Excel spreadsheets, and make finance handling easier for the department financial officer, faculty, and everyone else involved in handling the finances of the department. The system should have a web interface, created with XHTML, JavaScript, and PHP, and a backend MySQL database.
Project Steps:
Part 0: Project
Leader (2 points)
For this part of the project your team is to:
1. Choose a project leader. The project leader will be the instructor’s point of contact with your team. The project leader is also responsible for ensuring that work is distributed in a fair manner to each team member and that the work is getting done.
2. Choose a team name.
TURN IN: Send email to your instructor containing the project leader and the team name. This section of the project is due 30 January, 2008, before class.
Part 1: Project
Requirements and Responsibilities (10 points)
Read through the project description and attached documents and divide up the project in functional sub-modules (For example: login module, request submission, request review/approval, etc.). Consult with Capt. Miller, Dr. Crainiceanu or Dr. DeLooze if you need more information on the project requirements.
TURN IN (both electronic and paper): A file named teamX_requirements.doc, where X is your team number, containing the information in part 1 and 2 above. This section of the project is due 6 February, 2008, before class.
Part 2: Entity-Relationship model (10 points)
Create an Entity-Relationship model that incorporates the requirements you created in Part 1. You must specify the entities, their attributes and identifiers, the relationships between entities with an appropriate verb phrase, and the appropriate minimum and maximum cardinalities for all relationships. Use the crow’s foot notation in the diagram. In your design, make and state reasonable assumptions about the data, if not clearly specified in the requirements. If there is some information you cannot capture in the ER diagram, specify so.
TURN IN (both
electronic and paper): A file named teamX_ERModel.doc,
where X is your team number, containing the ER diagram that captures the requirements in Part 1. This section
of the project is due 20 February, 2008,
before class.
Part 3: Relational model (10 points)
Transform the ER
model developed in Part 2 into a relational model. Follow the steps described
in “ER to Relational” lecture, or Chapter 6 of the Kroenke
textbook.
Write the SQL
statements (use MySQL data types, nor SQL Server data
types, as the backend for your application will be MySQL
Database Management System) to create the tables, with their columns, data
types and whether null values are allowed for the column or not, default values
(if any), primary keys for each table, alternate keys and foreign keys if
appropriate.
Note that if a
table R has a foreign key attribute referencing another table S, you must first
create S and then create R. If a table R has a foreign key attribute
referencing a table S, and S has a foreign key attribute referencing R, you
have to create both R and S, without the foreign key constraints, and then use
ALTER TABLE statements to add the foreign key constraints to each table.
If there is some semantics in the ER diagram that you cannot capture in the relational tables, explicitly state so.
TURN IN (both electronic and paper): A file named teamX_RelationalModel.sql, where
X is your team number, containing all your SQL statements for this section.
This section of the project is due 5
March, 2008, before class.
Part 4: Normalization (10 points)
For each table
identified in part 3:
1) Ensure the
table is in 1st Normal Form. If the table is not in 1St
Normal Form, re-design the table, so the resulting tables are in 1st
Normal Form.
2) Determine all
the functional dependencies in each of the tables. If a table is not in Boyce-Codd Normal Form, decompose the table such that the result
tables are in Boyce-Codd Normal Form.
TURN IN (both
electronic and paper): A file named teamX_Normalization.doc, where X is your
team number, containing all the functional dependencies for each table in part
3 (remember that each table has at least one functional dependency), and, for
the tables not in 1NF or not in BCNF, the decomposition in BCNF
tables. This section of the project is due 5 March, 2008, before class.
Part 5: Create tables in MySQL
(8 points)
Write the SQL
statements to create all the tables you designed in Part 4 (you should already
have most of the statements for Part 3).
Create a file that
contains all the SQL statements from Part 3 and Part 5, in the correct order.
Executing this file in MySQL should create all the
tables for your database.
TURN IN (both
electronic and paper): A file named teamX_CreateTables.doc, where X is your
team number, containing all the SQL statements to create the entire database. This
section of the project is due 5 March,
2008, before class.
Part 6: Design and Implement
the Web Interface to the Database (40 points)
Part 7: User Manual (10
points)
Deliverables for Part 6 and 7
will be announced at a later date.