IT360 Spring 2012

 

The New Facebook

 

 

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

·          No collaboration between teams is allowed.

 

 

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 which includes the answers to all intermediate parts of the project.

 

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, Signup, Login, Friends, PhotoAlbum, 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 (1-4) and some section on the web interface part (5).

 

*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 22, 2012 1200

 

Overview

In first part of the project, you will design a relational database for storing information about your Facebook social network.  You will begin with a detailed description of the content.  Then, you will need to systematically go through the conceptual and logical database design process you learned about in class.

For the second part of the project, you will create the web interface to allow creation of new users, adding friends, uploading photos, tagging, and you will generate some reports from the data.

Part 1. ER Design

As a starting point, we have done the initial “requirements analysis” for you.  The following is a brief description of the data that you will store in your database.  (In real life, you would ordinarily begin with much fuzzier information.)

User Information

There can be an unlimited number of users. Each user has the following information:

1.1   Login information
This includes the username and password. The password must be stored in encrypted form (char(40)).

1.2   Profile information
This includes the following attributes: first name, last name, year of birth, month of birth, date of birth, gender.

1.3   Hometown Location
A user’s hometown includes the following attributes: city, state, country.

1.4   Current Location
Exactly the same attributes as hometown location.

1.5   Education History
Education history is for college programs and above. A user could have participated in multiple such programs, and each one will have the following attributes: name of the institution (e.g., US Naval Academy), year of graduation, primary major (e.g. IT, CS, EE, etc.), and degree (e.g., BS, MS, PhD, etc.).

1.6   Friendship information
Each user can have any number of friends. Each friend must also be a Facebook user.

Photos

“Photos” is an important Facebook application. Each photo has the following associated information:

2.1   Album information
Each photo MUST belong to an album. An album has the following associated attributes:
album_ID, owner_ID (this refers to the owner’s Facebook ID), album_name, cover_photo_ID (this refers to a photo ID), album_created_time, album_modified_time , album_link and album_visibility.

2.2   Other information
Each photo has the following additional attributes: photo_ID, photo_caption, photo_created_time, photo_modified_time, and photo_ link.

Photo Tags

A photo tag identifies a Facebook user in a photo. It has the following associated attributes: 

3.1   Tag subject
tag_subject_id (this refers to a Facebook user ID)

3.2   Tag coordinates
tag_x_coordinate and tag_y_coordinate

3.3   Time created
tag_created_time

Note that there can be multiple tags at exactly the same (x, y) location. However, there can be only ONE tag for each subject in the photo; Facebook doesn’t allow multiple tags for the same subject in a single photo. For example, you cannot tag Lady Gaga twice in a photo, even if she appears at two different locations (whatever that means).

Task for Part 1

Your task in Part 1 is to perform “Conceptual Database Design” using ER 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 or ERWin to draw the ER diagram.

Hints for Part 1

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.

For the entity names, relationships names and attribute names that you will be using in your ER diagram, you can use the ones we have provided here, or you may also choose your own names, provided that they are intuitive and unambiguous.

Deliverables:

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

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)
  2. dropTables.sql that contains a script to remove all the tables in the database, in the correct order (basically the reverse creation order)

Deliverables:

createTables.sql and dropTables.sql

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 has a new database IT360teamX on cardhu.cs.usna.edu. Use your database to test your scripts.

 

Part 3. Normalization

For each table identified in part 2:

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.

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) If needed, update the createTables.sql and dropTables.sql created in Part 2 to contain the new tables.

Deliverables:

  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

           

Part 4. Getting Facebook Data

The next part of this project is actually populating your database.  For this part, we will provide you with some fake data.

Facebook data raw schema

            For your convenience, we will provide you with Facebook data (fake) in a set of MySQL            database tables. These tables actually give you some hints on the previous parts of the assignment. However, these tables are highly denormalized, and without any table constraints.

            The table names are:

USER_INFORMATION

ARE_FRIENDS

PHOTO_INFORMATION

TAG_INFORMATION

The fields of those tables are described as follows:

            USER_INFORMATION table:

1.      USER_ID
This is the Facebook unique ID for users

2.      PASSWORD

This is the encrypted Facebook password (fake!!)

3.      FIRST_NAME
Every user MUST have a first name on file

4.      LAST_NAME
Every user MUST have a last name on file

5.      YEAR_OF_BIRTH
Some user may not provide this information

6.      MONTH_OF_BIRTH
Some user may not provide this information

7.      DAY_OF_BIRTH
Some user may not provide this information

8.      GENDER
Some user may not provide this information

9.      HOMETOWN_CITY
Some user may not provide this information

10.   HOMETOWN_STATE
Some user may not provide this information

11.   HOMETOWN_COUNTRY
Some user may not provide this information

12.   CURRENT_CITY
Some user may not provide this information

13.   CURRENT_STATE
Some user may not provide this information

14.   CURRENT_COUNTRY
Some user may not provide this information

15.   INSTITUTION_NAME
Some user may not provide this information.
A single person may have studied in multiple institutions (college and above).

16.   PROGRAM_YEAR
Some user may not provide this information.
A single person may have enrolled in multiple programs.

17.   PROGRAM_CONCENTRATION
Some user may not provide this information.
This is like a short description of the program.

18.   PROGRAM_DEGREE
Some user may not provide this information.

 

            ARE_FRIENDS table

1.      USER1_ID

2.      USER2_ID

            Both USER1_ID and USER2_ID refer to the values in the USER_ID field of the USER_INFORMATION table. If two users appear in the same row (a relation), it means they are friends, otherwise they are not friends.

 

            PHOTO_INFORMATION table

1.      ALBUM_ID
ALBUM_ID is the Facebook unique ID for albums.

2.      COVER_PHOTO_ID
Each album MUST have a cover photo. The values are the Facebook unique IDs for photos.

3.      ALBUM_NAME

4.      ALBUM_CREATED_TIME

5.      ALBUM_MODIFIED_TIME

6.      ALBUM_LINK
The URL directly to the album

7.      ALBUM_VISIBILITY
One of the following values: EVERYONE, FRIENDS_OF_FRIENDS, FRIENDS, ONLY _ME, CUSTOM

8.      PHOTO_ID
This is the Facebook unique ID for photos.

9.      PHOTO_CAPTION
An arbitrary string describing the photo.

10.   PHOTO_CREATED_TIME

11.   PHOTO_LINK
The URL directly to the photo

 

            TAG_INFORMATION table

1.      PHOTO_ID
Unique Id of the corresponding photo

2.      TAG_SUBJECT_ID
Unique Id of the corresponding user

3.      TAG_CREATED_TIME

4.      TAG_X_COORDINATE

5.      TAG_Y_COORDINATE

 

            Fake data (guaranteed)

            Whether you have a Facebook account or not, everyone will have access to an artificial (fake) data set. The fake data includes five tables with exactly the same schema as those above, just the names have PUBLIC_ as prefix. These tables are stored in the it360data database on cardhu.

PUBLIC_USER_INFORMATION

PUBLIC_ARE_FRIENDS

PUBLIC_PHOTO_INFORMATION

PUBLIC_TAG_INFORMATION

You can access the public tables with the fake data by prefixing the table name with the database name (it360data). For example, to access the PUBLIC_USER_INFORMATION table, you need to refer to the table name as it360data.PUBLIC_USER_INFORMATION.

 

Populate Your Database

For this part of the project, you will populate your database with the Facebook data we just described.  You should turn in the set of SQL statements (DML) to load data from the public tables (e.g., PUBLIC_USER_INFORMATION, etc.) into your normalized tables. You should put all the statements into a file called “loadData.sql”.

Deliverables:

loadData.sql file containing the insert statements to get the data from public tables into your own tables designed in part 2,3.

Parts 1-4 Submission Checklist

 

You need to turn in the following files via Blackboard:

(Please put all your files in a single zip file and submit a single file per team)

  1. A report in Word (doc or docx) or PDF format that answers the following parts of the assignment.  (If you prefer, you may hand-draw your ER diagrams, and submit an electronic version by scanning the drawings.)
    1. ER diagram for Part 1
  2. The report from Part 3 – normalization with a list of the functional dependencies (X->Y) for each table, whether the table is in Boyce-Codd normal form, and for tables not in BCNF, the decomposition into BCNF tables.
  3. 3 SQL files
    1. createTables.sql (Part 2, updated after part3)
    2. dropTables.sql (Part 2, updated after part3)
    3. loadData.sql (Part 4)

 

Part 5. 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 for USNA. The look among pages should be consistent (use CSS). When finished, your system should support the following functionality:

  1. Search page: allow people to search for users by name (first name, or last name, or both). A list of current users matching the search keywords should be displayed, with links to their profile page (see below)
  2. Sign-up page: new users should be able to join. The information required for the new users should match the description in Part 1
  3. 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.
  4. (Not required for teams of 3 people) Update profile, Change Password: You should allow users to update at least some of the information in their profile, and change their password. This will probably be almost identical to the signup form, but you instead load their current info into the form fields for them to edit. 
  5. Profile page: Each user should have a profile page. The page should contain the biographical information (no password of course), a list of current friends (see below), and a list of the photo albums created by the user (see below)
  6. Add friends: you need to have a mechanism to allow users to add other users as friends
  7. Photo gallery: this will be an important part of the project. This section will allow users to create albums and add photos to an album. Thumbnail images should be displayed in rows and columns. Clicking a Thumbnail will result in the image opening in a new window but much larger. The large page will also display all the tags (first and last name of people) related to that photo, if applicable, in some order based on the x and y coordinates (see Tagging below). This will be dynamically generated, based on the information in the database.
  8. Tagging: Allow users to identify Facebook users appearing in a photo. When photos are displayed in the gallery, the list of identified users (First Name and Last Name) is also displayed.  
  9. Statistics/ reports (Updated March 29, 2012): Several interesting statistics about the users are required. This part involves writing more complex SQL queries, processing the results, and displaying the results. For each of the reports below, you should do as much of the processing in SQL, rather than retrieving all data and doing the processing in PHP.
    1. Find the “most popular” month of birth, meaning the month which has the most users born in that month, and the “least popular” month of birth, meaning the month with fewest users born in that month. For both the most popular and least popular month, retrieve and display the month and the name of users born on that month.
    2. Find “homebodies”: display the user information (name, gender, date of birth, hometown information, current location information) for all users that still live in the same hometown (their current city/state is the same as home city/state)
    3. Suggest friends based on current friends: For this task, you will suggest friends based on shared friends. In particular, you will find the top n pairs of users in the database who share the most common friends, but who are not friends themselves. Your output will consist of a set of pairs (user1_id, user2_id).  No pair should appear in the result set twice; you should always order the pairs so that user1_id < user2_id. If there are ties, you should give priority to the pair with the smaller user1_id.  If there are still ties, then give priority to the pair with the smaller user2_id.
    4. (Not required for teams of 3 people) Find friends to set up on dates: For this task, you should find the top n “match pairs” according to the following criteria:

·        One of the friends is female, and the other is male

·        Their age difference is within “yearDiff”

·        They are not friends with one another

·        They should be tagged together in at least one photo

 

  1. Innovative requirement: Add something interesting that we did not specify to this project.

 

Part 6. Project Report

Write a project report, 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 one 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. See more details in the Deliverables section.

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.

·          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

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 Kristen LeFevre for providing the idea and the initial partial requirements for this project.

Deliverables (to be updated as time progresses):

All items due at start of class unless otherwise noted.

·        Thursday March 22 noon. Email and hard-copy to instructor: 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 design part (1-4) and some section on the web interface part (5).

·        Thursday March 29, before lab. Parts 1-4 are due. Only one submission per team is required. Both electronic (Blackboard) and paper submissions are 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, which includes

§  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)

§  Updated ER diagram and createTables.sql with the create table statements

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):

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

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

·          Sub-part 4 (Update profile, Change Password) of the web interface is not required for teams of 3 people.

·          March 29, 2012: Updated description for part 9 Statistics/reports

·          April 9, 2012: Updated deliverables and changed due date for the project.

·          April 9, 2012: W3Schools has an easy tutorial for how to upload a file to the web server: http://www.w3schools.com/php/php_file_upload.asp