Introduction
This assignment intends to show how effectively databases can be used to drive real-world applications.
You have to create an online community Web site, similar to
Orkut.
In the process, we expect you to learn the main challenges involved in providing such a service, and
appreciate the importance of database technology in today's data-driven world.
This assignment is broken into two phases. In the first phase you will plan your
implementation strategy at a high level, and submit documentation that describes the major
design decisions you have made. The first phase permits the TA's to evaluate your progress at an
early stage and provide feedback. In the second phase you will proceed to implement your
community Web site. At the end you are to give a live, interactive demonstration of your Web site in
front of a TA.
For this assignment, you can work in groups of three.
Phase I is due on Monday, 26 February at 3pm.
Details about the Phase II submission will be handed out later.
Features
Your community Web site must support the following features:
- Registration
Before a user can start using your system, he needs to register by providing his email address and choosing
a password. He might optionally provide his full name and street address. You are not required
to verify the validity of the email address, although most real-world sites do
so. Also, you can just store the password as clear text.
- Login/Logout
A user should be able to login with the email/password he provided while registration. For security,
no one should be able to view any page of your Web site without logging in (except the login page, of
course!) Also, once logged in, the user should not need to log in to view other pages. A login session
is valid until the user explicitly logs out or some threshold period expires.
The user must also be given the the 'Remember Me' option, which increases the expiration period of his
login session to 1 week.
- Personal Preferences
Your Web site should have a "Preferences" page, where the user can enter additional information about himself.
This is a community Web site, so users are going to search for other like-minded people. The preferences page
should atleast ask for the user's age, gender, occupation, hobbies and a one line description of himself.
- Search
A user must be able to search for people by their names, email or preferences (gender, occupation, hobbies, etc.)
As explained later, search should also work for communities.
- Friendship Management
Every user must be able to invite others (possibly people whom he found using the search feature) to be his friends.
An invitation needs to be approved by the person receiving it, and thereafter, both the sender and receiver are
friends of each other.
The user must be able to view his immediate friends (i.e., people who he invited himself, or accepted an invitation from).
as well as rate them (either while accepting an invitation, or later) on a scale of 4 --
acquintance, friend, good friend, best friend.
He should also be able to view "friends of friends", "friends of friends of friends", and so on if he
so desires.
Further, whenever he is looking at the profile/preview of another person (e.g., a person obtained through the search feature), he must
be able to see the path that connects him to that person, if such a path exists
(e.g., "Me > John > Smith"). This is similar to what Orkut shows for every person that you view.
- Instant Messaging
A user must be able to send messages to others. The recipient gets a notification of this message on his next
login. Messages are by default stored in the Inbox. If there are unviewed messages, the user should be
prompted every time he logs in. The user should also be able to delete messages (you need not implement a "recycle bin",
simply discard the message once the user decided to delete it). Messages can be associated with multiple labels
(as is done in GMail). The user must
be able to apply new labels to messages, delete labels from messages and filter messages using a label.
- Communities
Communities are similar to bulletin-boards, where a large number of people with some common interest participate
in various discussions. In your Web site, any user should be able to start a community. Communities can either
be 'free' (i.e., any person can subscribe it) or 'regulated' (i.e., membership requires the creator's approval).
The creator of a community should specify a description for the community, and other users can search for communities
based on this description. A user should be able to search for a community based on some identifying keywords, and
request for a membership (membership to free communities is automatically granted upon request).
A user should be able to list all communities that he is a part of. He should be able to post new messages on the
community forum, see previous messages, and delete his own messages (***ONLY HIS OWN MESSAGES***).
Setup
A Web server with PhP support has already been set up for you. To access it, you should log into your account (the one
you used for HW 0) and type the following commands:
$ cd $HOME
$ mkdir www
$ chmod 705 www
The
www directory will then be accessible via the url
http://<machine-name>/~<andrew-id>/
For example,
http://euterpe.datanium.cs.cmu.edu/~shashank/hw4/test.php
Deliverables
Phase I [10%] -- To begin with, you need to just create a design of the Web site on paper. You must submit a typed report
to Wean 8303 (slip it
under the door if it is closed). Phase I is intended as a way for us to help you, rather than evaluate you.
The more specific and detailed you are, the more feedback we will be able to give you, and the
easier Phase II will be. Your report should include:
- Your conceptual schema (using an Entity-Relationship diagram). Please omit entity set
attributes to make the diagram more readable, but be sure to include relationship set
attributes and to mark key constraints and participation constraints. You are to list the
attributes of each entity set separately (i.e., not directly on your ER diagram).
- Your logical schema, in the relational model. You may use the notation we use in class,
e.g.: Users(user_id integer, last_name string). Be sure to underline primary keys, and
describe other constraints (e.g., foreign keys, UNIQUE constraints, etc.).
- The DDL statements to create your relational schema. Be sure to include all appropriate
constraints!
- The SQL statement(s) that are to be invoked during each of the tasks listed earlier.
Phase II [90%] --
Phase II has two deliverables:
- The PHP and HTML files [40%]
- A live demonstration of your Web site [50%]
When you submit your PHP and HTML files, please
remove unused/temporary files to make grading easier.
Also, be sure to include extensive documentation and commenting. Unreadable code will not
receive a good grade, even if it works perfectly fine.
For your demonstration, plan on a 5-10 minute interactive session with a TA. (You will
receive an email regarding scheduling of your demo.) During your demonstration you will be
asked to do the following:
- For at least two tasks of our choosing:
- Walk us through how a user would perform that task.
- Demonstrate the kind of errors (missing or invalid inputs, duplicate primary keys,
etc.) that your system can handle gracefully.
- Explain how your system ensures the integrity of the database.
- Describe and justify the major design decisions/adjustments you made that depart from
your Phase I document. (You will not be penalized for departing from your Phase I plan --
in real life that sort of thing virtually always happens.)
- If you implemented any extra functionality, demonstrate it.
- Describe the system's limitations and your thoughts on how you might improve it.
Documentation
PHP is relatively easy to learn and use, even if
you have little or no experience with server-side scripting or HTML. This assignment is not
focused on teaching you PHP, but you will of course need some PHP know-how to complete the
assignment successfully. PHP documentation is available at
http://www.php.net/manual/en/.
PHP has very straighforward and intuitive functions to connect and query a Postgres database.
Documentation for this part is available at
http://us3.php.net/pgsql. The most
important functions you will need to use are
pg_connect, pg_query, pg_fetch_row, pg_free_result,
pg_close, but we recommend that you browse the list of available functions at least once
before you start writing PHP scripts.
FAQs
I am having trouble connecting to the Postgres database from my PHP code
These are the steps to be followed to let PHP gracefully connect to your Postgres server:
Step 1:
------
Start an instance of the Postgres server at a unique port. First check if you already
have an instance running from the previous homework. Type 'ps -ef' at the command line
and see if you can find a process labelled
'/usr/bin/postmaster -D /home/stu415_s06/<your-andrewid>/data -i -p <your-portnum>'
If yes, then note down the port that the process is using and directly jump to step 2,
otherwise read on.
Execute the following commands
- $ export PGDATA=$HOME/data
- $ pg_ctl start -o '-i -p <your-portnum>'
- $ createdb -p <your-portnum> hw4
Step 2:
------
In your php code, wherever you want to connect to the database server, type in the following:
$dbConn = pg_connect ("dbname=hw4 user=<your-andrewid> port=<your-portnum> host=localhost");
The user should also be able to view "friends of friends",
"friends of friends of friends", and so on ...'
You can simplify this feature as follows:
- Implement a browse-only solution:
To begin with the user can see only his own friends. When he clicks on a particular
person, he can see that person's friends and so on. So effectively, the user has to
browse to locate "friends of friends of .... friends." All your system has to do is
to display the friend's list of a single person, where the person is the user himself
or someone that the user clicked on.
- Implement a depth-wise solution:
This is more difficult, but doable and will earn you more credit. Take as input an
integer, and show all friends upto that distance from the user. If I am your friend,
we are at a distance 1 from each other.
How do I implement the "finding paths connecting different users" feature?
There are (at least) two possible simplifications:
- Show paths only for browsing and not for search. E.g., if I am John, and I click
on Smith in my friends list, and then click on Tom in Smith's friends list, I can easily
determine that one path connecting me to Tom is "Me > Smith > Tom". It is good enough to
show only this path for browsing, and avoid showing paths for search. This will involve some
way of tracking the user's browsing path (through the url, sessions or the database itself.)
- Limit the length of paths to some number like 4. And then for each user in your database,
precompute all people at a distance <= 4 from him (using breadth-first search).
Store these distances in a relation, and at runtime, simply query this relation to find if
there is a path between any two people. Of course, you will need to take care to keep this
relation uptodate when new friends are added by a user.
There could be more such simplifications. If you plan to do any of them,
just email me and I will let you know if its reasonable.