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 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.
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
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.