IT360 - Spring 2014 Project – Final Project Due April 23, 2014 Before Class

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

Honor

1.        Collaboration between the members of the same team is required. Collaboration with anyone outside the team is not allowed.

Overview:

For this project, you will design and implement a database application with a web front end to help keep track the flow of certain objects and social networking in Bancroft Hall.

Part 0: 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 Model, ER to Relational, Normalization, etc). Each team member should be responsible* for at least two sections of the project. Each team member should be responsible both for some section in the purely database part (1-4) and some section on the web interface part (4). One member needs to be reponsible for the project report too.

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

 

Project leader: Send email to your instructor and your team with information in Point 3 above. This section of the project is due Wednesday March 19, 2014 before class

Part 1: Entity-Relational Model (Conceptual Design)

 

The goal is to design a database for all Bancroft Hall to help track the flow of certain objects and social networking. You receive the following specifications:

1.      There are 30 companies at USNA, each with many midshipmen, and each midshipman is part of exactly one company.

2.      Each company has the following staff: one company officer and one senior enlisted. Each company officer and senior enlisted is responsible for exactly one company.

3.      For all midshipmen and staff, at least the name, email, and a password are recorded.

4.      Midshipmen and staff in Bancroft Hall own CDs, DVDs, books, and video games that they would like to share. Each object is owned by a single person, and there are no items without owner. Since different people might own copies of the same item (e.g., many students own the textbook for a class), owners appropriately mark their items to distinguish these copies.

5.      An owner can lend an item to a single borrower. The borrower can pass this item on to another person, but only if the new borrower is a friend of the owner. Similarly, that borrower can further pass it on to yet another borrower, as long as that borrower is also a friend of the owner, and so on.

6.      Friendship status is determined based on personal messages sent from one person to another, i.e., each message has exactly one sender and one recipient. Each message is classified as being “nice” or “mean”. Person A is considered a friend of person B if in the last 30 days B sent at least 10 messages to A, A sent at least 5 messages to B, and for both directions at least 90% of the messages were “nice”.

7.      Every time somebody borrows an item, both participants of this exchange can rate each other. However, neither person is required to submit a rating. A rating consists of a number of stars between 1 and 5, plus an optional text comment. Ratings can only be submitted after the item was exchanged, and a rating has to be submitted within 2 weeks of the exchange. Within those two weeks, the text can be updated any time, but the star rating cannot.

8.      Whenever a person’s average rating drops from above 2 stars to 2 stars or lower, all owners of items currently borrowed by that person should be notified with the following information: whose rating fell, old average rating, new rating, company and company officer of the borrower.

Task for Part 1

Your task in Part 1 is to perform “Conceptual Database Design” using Entity Relationship Diagrams. While there are many ER variants, for this project, we expect you to use the Crow’s foot notation (Information Engineering notation) used during lecture. You can use pen-and-paper, ERWin, or other software to draw the ER diagram.

Hints for Part 1

a.      Like in the real world, some specifications might be ambiguous or incomplete. If that affects your ER design, make common-sense decisions for how to resolve them and briefly explain the problem and your solution in the report. In practice you would have to clarify these issues by talking to your “customer”. For this project, let’s assume the customer cannot give you more information. Of course, you can still contact us, but only for major issues. We want you to make your own design decisions, not try to guess ours.

b.      Remember that ER diagrams cannot represent certain constraints. You also cannot express queries or notifications. But you need to make sure that the diagram contains all entities, relationships, and attributes necessary for producing the information needed for these queries and notifications.

c.      If you find it difficult to model all entities and relationships together, start by modeling the individual requirements separately. Then see how they fit together.

d.      Some entities might participate in many relationships. For readability, you can duplicate entities in the diagram, i.e., draw the same entity rectangle multiple times. This is equivalent to attaching multiple relationships to a single box, but more readable if entities participate in more than 4 relationships.

e.      To receive full points for this part, you need to identify all the entities and relationships in a reasonable way.  We expect there to be multiple correct solutions.  (Remember that ER design is subjective.) Your goal should be to capture the given information. You should use the features of ER modeling that you have learned in class (minimum and maximum cardinalities, identifying and non-identifying relationships, IS-A hierarchies) as necessary.

 

Deliverables - Due Wednesday March 26, 2014 before class:

ER diagram created for Part 1. If there are some requirements you could not capture in the ER diagram, explicitly specify which requirements were not captured.

Part 2. ER Diagram to Relational Schema (Logical Database Design)

For the second part of the project, your task is to convert your ER diagrams into relational tables.  You are required to write SQL Data Definition Language statements for this part to create all tables, and to drop the tables. You should turn in two files:

  1. createTables.sql that contains all the create table statements needed to create the tables, in the correct order (remember that a table referenced by a foreign key constraint must be created before the table that references it). Make sure you include all the primary key, foreign key, alternate key and any constraints you might have for your tables
  2. dropTables.sql that contains a script to remove all the tables in the database, in the correct order (basically the reverse creation order)

Hints for Part 2

You should capture as many constraints from your ER diagrams as possible in your createTables.sql file. In your dropTables.sql, you should write the DROP TABLE statements necessary to destroy the tables you have created.

Each team will have a new database IT360teamX on cardhu.cs.usna.edu. Use your database to test your scripts.

Deliverables - Due Wednesday April 2, 2014 before class:

createTables.sql and dropTables.sql

Part 3. Normalization (Schema Refinement) and Data Generation

For each table in your database:

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) For each table, list all the functional dependencies (rules of the type X->Y) that are true for that table. Remember that each table has at least one functional dependency (due to the primary key).

3) For each table, say whether the table is in Boyce-Codd Normal Form or not. If a table is not in Boyce-Codd Normal Form, decompose the table such that the result tables are in Boyce-Codd Normal Form.

4) Update the createTables.sql and dropTables.sql created in Part 2 to contain the new tables which should be all in BCNF.

5) Insert a few rows in each table in your database: write all your SQL statements in a generateData.sql file to be executed in order to insert at least two rows in each table in your database.

Deliverables - Due Wednesday April 2, 2014 before class:

  1. A file named teamX_Normalization.doc, where X is your team number, containing all the functional dependencies for each table in part 2. For each table, say whether the table is in Boyce-Codd Normal Form. For the tables not in 1NF or not in BCNF, provide the decomposition in BCNF tables.
  2. Updated createTables.sql and dropTables.sql files
  3. New generateData.sql file with the insert statements

Part 4 – Enforcing Constraints with Triggers

The goal for this part is to work with constraints and triggers

In class we discussed triggers. Recall that Part 1 described several application constraints that we have not yet added to our database. The goal for this milestone is to make sure that we enforce the missing constraints as much as possible. We assume the following use of our system: Before performing an action, users try to enter the information about this action into the database. If a constraint or trigger causes an error, the real-world action is cancelled. For example, if a person tries to pass an item to a non-friend of the original owner, he/she first tries to enter this information into the database. We want the database to prevent this insert (which would tell the user to not perform this item exchange.)

1.      Go through all the requirements of the project scenario in Part 1 (except the trivial ones that are constraining the data type of an attribute) and do the following:

a.      Briefly state the requirement. E.g., “There are 30 companies”

b.      State if our current database implementation already enforces this requirement.

                                                    i.     If it does, state in one sentence (maybe two), why.

c.      If the requirement is not enforced yet, add your enforcement option of choice to the database (a trigger or a check constraint). Try to find a solution that achieves the same effect: you want to prevent updates that violate the constraints. For the requirements that specify that a notification should be sent, insert that notification in the database.

Deliverables - Due Wednesday April 9, 2014 before class

A report that contains the following items:

1.      A discussion of requirements in Part 1 and whether they are enforced by the database.

2.      The SQL code showing the definition of the triggers or the check constraints that you added to the database.

 

Part 5. Web Interface

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

  1. Login/Logout: all users need to login in order to access their information on the website. You need to create a login/logout mechanism: for login, ask for username and password, check the information against the information stored in the database, and if the information matches, allow access to the protected pages. Some information needs to be stored in session variables, so the user does not need to provide his/her password for every protected page.
  2. Profile page: Each user should have a profile page. The page should contain the name, company, list of objects owned, list of friends (as determined by the rules given in Part 1), and any notifications this user received. To handle notifications, you should first add a table to your database to store the notifications that each person might receive (in real life, these notifications should be emailed, not stored in the DB)
  3. Messages: Each user should be able to send messages to other users, see the messages he/she sent in the last 30 days, with most recent first, and the messages received in the last 30 days (most recent first).
  4. Exchanges: Each user should be able to insert information for a new exchange when it lends an item to some other user. Each user should also see a list of all items he/she lent and a list of all items borrowed and not yet returned.
  5. Ratings: Each user participating in an exchange should be able to rate the other participant in the exchange. Each user should also be able to see its own ratings and comments, as well as the average rating.   
  6. Innovative requirement: Add something interesting that we did not specify to this project.

Part 6. Project Report

Write a single-spaced project report, explaining your work in this project. The project report should be clear and well organized: have an introduction, main content, and conclusions. The report should include:

§  A cover sheet with your team number and project members.  Describe your innovative feature, any extra credit, and any requirements that you did not meet. 

§  A description of what each person did – who created each file, who tested?

§  Username and password for two users on your site.

§  A technical report of how the overall system works from a coding standpoint. We want to know how you use files, server-side scripts, etc. This should be no more than a few pages. This should serve as manual for someone who wants to modify your code.

§  User manual: a description on how your system should be used by the end users (include screen shots and step-by-step instructions)

§  All of your files should also be well documented (see Clarifications section)

§  Latest version of the ER diagram and createTables.sql with the create table statements

Other Requirements:

2.        This project is to be done using PHP, HTML, CSS, and optionally, JavaScript. All server-side programming should be done in PHP, using object-oriented programming style.

3.        Documentation: good documentation is expected for your code. The code should be well organized and well documented. At the very least, all PHP scripts and class files should have the author(s) name and a short description on top. All non-trivial methods/ functions should have a short description explaining the purpose of the method/function.

Extra credit

Your project must meet the basic requirements, with the extra "innovative" feature.  Then do any of the following for additional credit.  You may also propose other ideas; talk to your instructor.

1.        Create a poster that showcases the course and/or the project, and can be used as advertisement for the IT major. If multiple posters are created, we will vote on the best poster, and the winning team will receive an extra-extra credit. More information about how you can create a poster is available at http://www.usna.edu/Users/cs/needham/ProjectPosters/ProjectPosters.htm

 

2.        After you filled your database with enough interesting data, create the following SQL queries and run them on the database. (If you find it difficult to compute the result with a single query, create temporary tables in your SQL script or use views for intermediate results.):

a.      How many students are in each company?

b.      Which person (ID, name, and email) has borrowed the most books in the history of running the application?

c.      For each item (item’s ID), who (person’s ID) is its owner and who (person’s ID) has the item currently?

d.      Which person currently holds on to the most borrowed items?

            Turn in a SQL script showing each SQL query and its result as run on your database.

Late Policy

For each intermediate deadlines, there is a -2% (of overall grade) penalty for each business day being late. The complete project has to be submitted on time. No late submission accepted for the final project.

Acknowledgements:

Thanks to Mirek Riedewald for providing the idea and the initial requirements for this project.

Deliverables (to be updated as time progresses):

All items due at start of class unless otherwise noted.

·        Wednesday March 19, before class. Email to instructor and to all team members : The name of the project leader, the sections of responsibility, and the team member responsible for each section. When dividing the work amongst yourselves, remember that each team member should be responsible for both some section in the db part (1-4) and some section on the web interface part (5).

·        Wednesday March 26, before class. Part 1 ER Model due. Only one submission per team is required.

Presentation: The members of your group should be prepared to demonstrate your system to the class. Be sure to mention your required innovative feature and any extra credit. You’re not expected to prepare Powerpoint slides but if you wish place them in your team web directory for easy access.

Report: You will submit a single-spaced written project report as specified in Part 6.

Feedback and Peer Grading: You will be provided with an online form for giving feedback and grading the members of your team.  You will submit this individually – peer grades are confidential.  Check your email for more info.

 

Clarifications/Hints (to be updated as time progresses):

 

·        There are many MySQL functions dealing with date and time. See http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html for a detailed list. For example, if you have a messages table that has a msgDate column, to get all the messages in the last 30 days you could use the following query:

o   SELECT *

FROM Messages

WHERE DATEDIFF(NOW(),msgDate) <= 30;

 

NOW() gives you the current date-time. DATEDIFF() computes the number of days between 2 dates received as parameter (only the date part is considered, the time is ignored)

 

·        If you don’t know how to achieve the desired effect using a trigger, try to enforce the constraint in the PHP code.