Class Projects

1. Develop a proposal for the project.
   First, groups should submit for approval their project ideas. This proposal should include:
   • A separate cover page indicating the title of your project, the full names of the group members (with e-mail), the course number and course section.
   • A narrative description of the business used for the project or application being created. This should also include a description of the problem or opportunity being addressed.
   • Identification of the information needs - what information would help solve the problem or allow one to take advantage of the opportunity.
   • Initial list of entities (tables) that have been identified. This should come naturally from the above discussions.
   • Distribution of duties for the project. List the names of each group member and what their primary role will be (e.g., systems analyst, application developer, documentation writer).

   Informal discussions with the professor can help to refine the project and proposal.
   Groups should not continue working on the proposal unless it has been approved by the instructor.

2. Systems Analysis
   The next step is for the group to take the requirements from the "users" and draw an Entity Relationship diagram.
   The E-R Diagram should then be submitted to the professor for approval before proceeding.
   Students may use a specific E-R modeling tool, drawing tools available in MS Word or MS Powerpoint, or in very neat pen or pencil.

   Note: The "Relationship View" in MS Access is not an E-R modeling tool.

3. Logical and Physical Modeling
   Given the E-R diagram and sets of attributes for each entity, the next step is to convert the E-R model into a relational model and go through the process of normalization. This step will require the group to list all of the functional dependencies.
   The normalized relations should be approved by the professor before proceeding.

4. Database Implementation
   Groups should then implement the database tables from the normalized set of relations created in the previous step. Data should be supplied for each table. The amount of data should be such that the need for a database is clear. In other words, provide enough examples to demonstrate why a database was required in the first place.
   
5. Application Implementation
   The application (forms, reports, queries, menus) can then be created on top of the tables. For Queries, provide the associated SQL statements.
6. Write Up Final Report
   The final step is to prepare a formal report and brief presentation. This report should include:
   • A separate cover page.
   • An introduction section similar to the proposal.
   • Two E-R diagrams: One showing just entities and relationships on a single page, and a second E-R with attributes and identifiers included.
   • The collection of normalized relations and functional dependencies, and a brief discussion as to the normal form(s) achieved, the methods used to achieve these normal forms, and reasons why any de-normalization was done.
   • A listing of the metadata for each table in the database.
   • An example printout of each of the forms, reports and queries accompanied by a description of the function of each.
   • A Menu Tree showing the organization of the menu items and a brief description of why you chose this layout of menu items. The "Organization Chart" feature of MS Powerpoint can be useful for this step.
   • A narrative conclusion section that describes:
     a) the group's experience with the project (which steps were the most difficult? Which were the easiest ? what did you learn that you did not imagine you would have? if you had to do it all over again, what would you have done differently?)
     b) if the proposed benefits can be realized by the new system
     c) any final comments and conclusions

Project Ideas

1. Scheduling Application - A consulting company has a lunch room, 12 conference rooms, 6 overhead projectors, 3 portable PCs, etc. They need to be able to schedule each of these resources for a given day and time period and avoid conflicting use of resources. Also, management would like reporting on resource utilization per week, month, year. They are also considering renting out resources to other companies if resource utilization is low. Queries might include:
   • When is the next day resource X is free between 1:00 and 5:00.
   • How many hours per week on average is conference room X occupied.
2. Supply Cabinet - A company centrally maintains supplies for each of its branch offices. They need a database to keep track of what they have in stock, requests from branch offices for supplies and purchasing of supplies from vendors. Should keep track of the vendor with the best price for a particular supply. They would also like to minimize shipping costs by shipping several supplies at one time to a given branch office. Queries might include:
   • When should supply X be replenished
   • How many shipments, on average, go out to the branch offices per week ?
3. Baseball Statistics - A professional baseball team would like to maintain a database that records player statistics on all team members and complete records of every game (on an inning-by-inning basis). Each player would have a set of offense and defense statistics. Queries include:
   • What is the batting average for player X
   • Who is the best relief pitcher to use against a left-handed batter
4. Portfolio Management System - a small hedge fund would like to track its investment portfolios.