Environment Configuration: MyEclipse for Spring 9.0 + Tomcat 6.0 + Data Studio 3.1 + DB2

You will design and implement a relational database system to support the operations of a social networking Web site and you will use HTML for the user interface.

1. A person can sign in as a new user of your Web site. You should store: user ID, password, Last Name, First Name, Address (City, State, Zip Code, Telephone, E-mail Address), User Creation Date, Preferences (see advertisement below), sex, date of birth, etc.

2. Each user should be able to search and ask for the friendship of other users and group his friends into circles (for family members, for co-workers, etc). You should store: circle ID, owner, circle name and members for circles.

3. Each user has an associated Wall on which he or his friends can make posts and comment on existing posts. You should store: Post ID, wall owner, date, content, and comments (authors, date and times, contents).

4. Each user has a profile page that lists his contact info and his friends.

5. Each user has a default/personal page of updates where he can see the posts posted on his friends’ walls in chronological order and his friend requests.

6. Users should be able to send each other messages. These messages should be grouped into discussions by message subject. One user can send one message to multiple other users. They should all see the discussion. You should store: Message ID, Date, Subject, Content, Sender, Receivers.

7. Users should be able to create Special Interest Pages (SIPs), invite other users to join SIPs, search and request to join a SIP, and moderate (accept or deny) the request to join a SIP. Each SIP has one or more moderators (at least one). The creator of a SIP is the first moderator of that SIP. Any moderator of a SIP can assign/remove other participants as moderators. Each such SIP has an associated a page on which members can make posts and comment on existing posts.

8. Your social network site will also support targeted advertising as a mechanism for the site to generate revenue. Users will specify their preferences (e.g. cars, clothing, life insurance) as to the type of advertising they wouldn’t mind seeing while visiting the site. Likewise, the site will ensure that only advertisements of these types are displayed to the user. A user can click on an advertisement to purchase one or more of the items being advertised, and the site receives 10% of all such transactions. You should store: Advertisement ID, item, Company, unit price, number of units available for each ad. You should also store sales data: Transaction ID, Date and Time, Advertisement ID, number of units, user ID.

You are responsible for arranging the data items into tables, determining the relationships among tables and identifying the key attributes. In addition, you should include indices in your tables to speed up query processing. You should specify and enforce integrity constraints on the data, including referential integrity constraints.

Some User-Level Transactions:
1 Sign up on the Web site
2 Create/delete circles
3 Search for a user ask for friendship
4 Accept a friendship request / Delete a friend
5 Add/remove friends to/from circles
6 Make/remove/modify posts
7 Comment on posts
8 Search/Join SIPs
9 Moderate SIPs
10 Make/remove/modify own SIP posts
11 Comment on SIP posts
12 Send / Receive / Delete messages
13 Purchase one or more copies of an advertised item

Web Site Manager-Level Transactions: a separate Web site for site managers should allow managers to:

1 Create/delete an advertisement
2 List the sales report for a particular month
3 Produce a comprehensive listing of all items being advertised on the site
4 Produce a list of transactions by item name or by customer name
5 Produce a summary listing of revenue generated by a particular item, item type, or customer
6 Determine which customer generated most total revenue
7 Determine which product (ad/item) generated most total revenue
8 Produce a sorted list of sold items by number of units sold
9 Produce a list of all customers who have purchased a particular item
10 Produce a list of all items for a given company sorted by number of units sold
11 Produce customer mailing lists for a given product
12 Produce a list of item suggestions for a given customer (based on that customer’s past transactions) – items of the same types

The Web site should include a comprehensive Help facility both for users and managers.

1. Provide the Entity-Relationship (E-R) diagram of the complete database scheme.
2. Provide the description of the relational database scheme including a discussion of the reasoning behind your design decisions. Make clear how your design supports efficient query processing.
3. Provide a list of all functional dependencies in the relational database scheme.
4. Describe the integrity constraints including referential integrity.
All printouts should be in PDF. You can use some appropriate document-preparation software for your tables (e.g., Microsoft Visio is available for all SBU students for free).


1. create new project: MyEclipse → File → New → Web Project

2. structure: ProjectName → src → package → Servlet

.                                              → WebRoot → (jsp folder) → folder → JSP

.                                                                    → WEB-INF → lib → db2jcc_license_cisuz.jar/db2jcc.jar/db2jcc4.jar

.                                                                                          → web.xml

3. JSP: (initial lower case: prevent URL input error)

form: <form action=”servlet/Login” method=”get”></form> <%–login,jsp–%>

input: UserName: <input type=text name=username value=Yueying />

.                  Password: <input type=”passwordname=password /”>

.                  <input type=”submitvalue=”Submit” />

.                  <input type=”buttononclick=”msg(0)” value=”SignUp” />

.                  function msg(value)

.                 {
.                       if(value==0)
.                       location.href=”jsp/Login/signup.jsp”;
.                  }

.                  <input type=”radioname=”sex” value=”male” /> Male

.                   <input type=”radio name=”sex” value=”female” /> Female

.                   <input type=”checkboxname=”semester” value=”spring” /> Spring <%–createmessage,jsp–%>

.                    <input type=”checkbox” name=”semester” value=”fall” /> Fall

.                     String semester[] = request.getParameterValues(“semester”); //CreateMessageS.java

.                     <input type=”hiddenname=”messageid_choose” value=<%=messageid%> /> <%–messagelist.jsp–%>

select: Department <select name=”department”> <%–signup.jsp–%>

.                     <option value=”CSE”>CSE</option>

.                      <option value=”MAT”>MAT</option></select>

4. Servlet: (initial capital case: like-named java class)

5. JSP → Servlet: String s = request.getParameter(“s”); //LoginS.java

6. Servlet → JSP: 1. response.sendRedirect(“jsp/Login/login.jsp”); //LoginS.java

.                                         2. request.setAttribute(“”friendid_list”, friendid_list”); //MessageS.java

.                                              request.getRequestDispatcher(“jas/Message/createmessage.jsp”);

.                                             ArrayList friendid_list = (ArrayList)request.getAttribute(“friendid_list”); <%–createmessage.jsp–%>

.                                        3. HttpSession session = request.getSession(); //LoginS.java

.                                             session.setAttribute(“hostid”, userid_db);

.                                             int senderid = 0; //CreateMessageS.java

.                                             HttpSession session = request.getSession();

.                                             senderid = (Integer)session.getAttribute(“hostid”);

7. web.xml: XML file