Project Description for COP-5725, Fall 2010
School of Computing and Information Sciences, FIU
Instructor: Vagelis Hristidis
Xing Yang <xyang006@fiu.edu> will be responsible for answering questions and grading the project.
Students will be divided to groups of two for the project. If you cannot find a partner, email me to find one for you. One-student groups are not allowed.
You are asked to develop a social network system for music fans. In particular, the minimum amount of information that needs to be stored is:
User info (complete name, email, password, street address, date of birth, ).
Favorite songs (Artist, song title, album, year, etc) for each user. We assume that there are no two songs with the same combination of title and year. Note that even if two users like the same song, the songs information should be stored only once in the database.
Friends: Every user has a list of friends within the social network.
Artist fan groups (group name, favorite artist, description): A user can create a group that can be joined by other users.
Messages (date, from, to, subject, body): Users can send messages to their friends and to all the members of a group they belong to.
A (18 points). E-R diagram. The symbols and notation should be the same as in the book.
B (15 points). Relational Schema, in 3NF. The actual SQL CREATE TABLE commands are required. A graphical diagram of relations is optional.
C (5 points). Sample Data. Populate database with sample data. Insert at least 5 tuples per relation. Make sure that all queries below have non-empty answer. Put the used SQL commands in your report.
D (12 points). Create Views. Create (write English description and SQL syntax) 6 views that a user of the social network would find useful. From these 6, the first 2 are common for all groups and you can create your own last 4 ones.
View 1: Find pairs of users that have at least five favorite songs in common.
View 2: Get the list of users that have sent more than 3 messages to other users.
Examples of info to request in the other 4 views are:
Get associations through friends: List pairs of users that have more than five common friends and that are not friends between them.
Output the average number of friends per user.
E (15 points). Indexes. Create (show SQL commands) 4 indexes to support some of the queries (each view corresponds to a query) of Deliverable D. For each index, briefly explain (one sentence) how it will support a query (or queries).
F (35 points). Web interface. Create a Web interface in JSP running on Tomcat to manipulate the database. Instead of JSP/tomcat you can use your own Web programming language that connects to a database system, provided that SQL commands are used to interface the database. The minimum requirements are:
Insert, delete and update any information in the database. That is, insert, delete, and update the information of friends, songs, groups, messages between friends, and so on. Notice that a message can be deleted by the sender but may still appear to the receiver. Likewise, a group cannot be deleted if it has members.
View (in separate pages) all users, all groups, all songs, and so on.
Given a users email address, create a report page with all friends she has, along with all groups she belongs to and all messages sent and received.
Allow user to search for songs by title (to then add them to their favorites list), search for other users by name (to add them to their friends list) and search for artist fan groups (to join).
There should be a main home page from which there are links to all other pages, along with brief descriptions on what each page does.
In your report briefly describe (in one page) the capabilities of your system along with some screenshots showing its main functionality. Only attach the source code in the soft copy.
· Put all deliverables of Phase I in a single document (DOC or PDF) clearly separating each deliverable (A, B, C, D). Submit a hard copy and email an electronic copy by the date specified above.
· For Phase II, do the same, but only include your source code in the electronic and not the hard copy. You also need to demonstrate your JSP interface. Each group will get a time slot to demonstrate.
· No handwritten documents are allowed. Diagrams have to be created using a computer as well. ER diagrams must look like the ones in book, that is, use the same symbols.