IT360 Spring 2010 Project - United States Naval Academy



IT360 Spring 2010 Project

USNA’s Radio Station WRNV Management

Entire Project Due: April 30, 2010

Partial Deadlines:

Thursday, 25 March, 2010, COB: Part 1- Project Responsibilities

Monday, 29 March, 2010, before class: Part 2 – Requirements and ER model

Friday, 16 April, 2010, during lab: Part 3,4,5, and login

This is a Programming Project. For this project, midshipmen will work in groups of three. 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 Dr. Crainiceanu.

Submission requirements (each team will have one submission):

• Electronic submission: The team leader should upload the files created for each part of the project to Final Project on Blackboard

• Hard copy: Hard copy of the project report.

Project Description

In this project you will develop a database and web interface for the Academy’s radio station WRNV.

The radio station is a very dynamic organization; they do everything from broadcasting shows, to recording performers, and even setting up live events. However, there are two major things that prevent WRNV from properly utilizing all of their potential. They have many members and all of the members have varying degrees of experience, and they have piles of equipment that are not well organized and definitely underutilized as a result. They need a utility that is easy to update, accessible online, and allows a clear picture of what equipment and members are available to staff and equip events.

The goal of this project is to design and implement a database for WRNV, and then to develop a web based interface for the database. You will be using MySQL and PHP, XHTML and CSS.

Part 1: Project Responsibilities

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. Read through the project description and divide up the project in sections of responsibility (ex. ER Diagram, Normalization, Events Interface, Inventory. Roster, etc). Each team member should be responsible* for some section of the project. Each team member should be responsible for both some section in the design part and some section on the web interface part.

*Responsibility for a section of the project does not mean that the team member is to do that section of the project alone; it simply means that the member will supervise that section.

3. Record the name of the project leader, the sections of responsibility, and the team member responsible for each section.

Send email to your instructor with information in Point 3 above. Turn in a hard copy with the same information. This section of the project is due Thursday March 25, 2010 COB

Part 2: Requirements and Entity-Relationship model

Create an Entity-Relationship model that incorporates the specifications described below and some extra requirements you have to specify. You must give 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 assignment. If there is some information you cannot capture in the ER diagram, specify so.

Partial Design Specification

Below are the minimum requirements for your database. You will have a meeting with the station president (2/C Burkardt) to fully clarify what is needed.

Your database will have a web interface and Station officers and members can login into the website. Only members and officers with valid login/password information can access the website. Members may only view the contents of the database, they may not make any modifications. Officers must be able to view and update the database and receive requests from members to make updates to the database.(eg. If a member wants to run his own event, he must ask an officer first.)

The station has many members, some of the members are officers. Members of the station have a name, alpha, company, phone number, favorite music, and number of events that they have staffed.

The station also has a lot of equipment. There are many categories of equipment and the equipment can only be a part of one category : speakers, cables, amps, microphones, speaker stands, microphone stands, mixers, computers, iPods, generators, cases, adapters, and miscellaneous. The miscellaneous category applies to equipment that is kept in the inventory that is of value but is very infrequently removed from the station.

The radio station also has many events. Each event has an officer in charge. It also has at least one member assigned to setup, operate, and teardown the equipment. The event has a date and time as well as a point of contact for the event. In addition, each event has a list of equipment that will be used. The equipment can only be used for one event at a time.

TURN IN (both electronic and paper): A file named teamX_ERModel.doc, where X is your team number, containing all the requirements for the database and the ER diagram that captures all the requirements. This section of the project is due Monday March 29, 2010, before class.

Part 3: Relational model

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

Write the SQL statements to create the tables in MySQL, 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 is due Friday, 16 April 2010, before class.

Part 4: Normalization

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 Friday, 16 April 2010, before class.

Part 5: Create normalized tables in MySQL

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.sql, where X is your team number, containing all the SQL statements to create the entire database. This section of the project is due 16 April 2010, before class.

Part 6: Web Interface

You should add now a web interface for your database. There are no specific requirements for the website design, but the overall website should be tasteful and appropriate as an ECA. The look among pages should be consistent (use CSS). When finished, your system should support the following functionality:

1. Officers and members can login into the website. Only members with valid login/password information can access the website. Only officers may modify the contents of the database.

2. Upon login, one of two menus are presented:

1. “Regular menu” has the following options

• Change password

• Search events

• Search inventory

• Search roster

• Contact officer

• Request event

• Logout

2.2. “Administrator menu” has the following options

• Change password

• Add/Update Inventory

• Add/update member information

• Add event

• Search events

• Search inventory

• Search roster

• Logout

The “Regular menu” and its options should be accessible to regular midshipmen. The “Administrator menu” and its options should be accessible only to officers in the Station.

3. Each option in the menu should link to a script that performs the appropriate action(s). Note that all scripts should execute only if a valid member with the appropriate level of privilege is logged in.

4. Upon logout, no information on the website is available, other than the page asking for login information.

More details:

Here are some details about what each option in the menus presented to the user should accomplish:

• Change password: you should allow a user to change his/her password. You should ask the user for the old and new password. The new password should be entered twice. If the old password matches the password in the database, and the 2 new passwords are identical, then the password in the database should be updated to the new password, and a confirmation message should be displayed to the user. If for some reason the password cannot be changed, an explicative error message should be displayed to the user.

• Add/Update member: This option should allow a user to add or update the member information. The form should be populated with the data already in the database (if any exists), and allow the user to modify his/her information. The latest data should be recorded in the appropriate tables in the database. You need to enable users to update their experience based on the number of events that they have participated in.

• View roster: Allow the user to search for bio information, based on some criteria (it is your choice of what kind of criteria you want to support). If no criteria are used, all information from all bios should be displayed.

• Add event: Allow a user to add events. Information about events should be stored in the database, so it can be retrieved later.

• Search events: Allow the user to search for events, based on some criteria (it is your choice of what kind of criteria you want to support). If no criteria are used, all information about all events should be displayed.

• Search inventory: Allow the user to search for equipment, based on some criteria (it is your choice of what kind of criteria you want to support). If no criteria are used, all information about all equipment should be displayed.

• Contact officer: allows members to email officers directly

• Request event: allows members to request events at certain days and times. The request is sent to the event manager who can approve or deny it.

• Logout: After logout, no pages on the website should be accessible (even if the user hits the back button), except the login page.

For all forms, you have to check that all required fields are filled out, and correct information is entered (what “correct” means depends on the form). If there is an error on the page, an explicative message should be displayed to the user, and the page with the filled information should be re-displayed.

TURN IN: Partial and final deliverables for Part 6 are detailed below.

Part 8: Project report (see below)

DELIVERABLES:

Thursday, 25 March, 2010, COB: Part 1- Project Responsibilities

Monday, 29 March, 2010, before class: Part 2 – Requirements and ER model

Friday, 16 April, 2010, during lab:

Part 3,4,5 - Transform ER model to relational model, verify tables are normalized, write SQL to create the tables in MySQL.

During lab, demo to your instructor how a user can login into the system, and the appropriate menu is displayed based on user permissions.

Friday, 30 April, 2010 Complete Project Due, presentations in class

Report: Write a project report, addressed to your final client, the president of the WRNV, explaining your work in this project. Include any extra credit, and any requirements that you did not meet. Include a description of your innovative feature. For testing purposes, include the user name and password for an admin user and a regular user. The project report should also serve as a user manual on how to use the software you wrote, so screen shots and step-by-step instructions should be included. The project report should be clear and well organized: have an introduction, main content, and conclusions. Include the up-to-date ER model and the SQL statements to create your tables in the report.

Presentation: Your team should be prepared to present your system in class. You will have 10-15 minutes to do so.

Feedback and Peer Evaluation: You will have to fill out an on-line survey to provide feedback on the project and evaluate your team members. Peer evaluations will be kept confidential.

Electronic submission: the team leader needs to upload all files related to the project to Project assignment on Blackboard.

Paper submission: Hard-copy of the project report should be submitted before class on the due date.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download