Reference no: EM133127779 , Length: word count:2000
CPS 662 Database Management Systems
Project Specification
Introduction
Social networking websites, such as Facebook, provide an interactive place for users to post blogs, upload files/photos/videos, and search contents posted by others. Typically, a registered user will have a user-profile and associated contents, e.g. files, photos, videos, and blogs. A registered user can create a group and become the owner of the group. A group belongs to a category, e.g. money, romance, or games. Users can choose groups to join and leave. A Social networking web site also provides sophisticated searching capacity. For example, users can search based on user profiles such as name, gender, school, or their combination. They can also search files, photos, and videos according to title, date posted, and categories.
Behind the Facebook website, there is a database. It stores information about user profiles, files and its owners, groups and its owner/members etc.
Your tasks
First, divide into teams of five. Then, your task is to design and implement the database behind a mini-Facebook mentioned in the introduction. You work can be staged into four phases.
1. First phase: you will need to investigate the Facebook website and try to "guess" what the database behind it should look like. You may want to start out by figuring out what information needs to be stored to support the queries of the Facebook website. Then try to see the relationship among the information, e.g., user and group.
2. Second phase: you will put down your thoughts in the first step by drawing an ER diagram. Make sure you document all the assumptions for your ER diagram if it is not obvious from your investigation of the Facebook website. In this step, you have the freedom to choose the most important entities and relationships. In other words, you can omit some features to avoid getting a too large system. I am expecting around 10 to 20 entities.
3. Third phase: you will convert your ER design into relational tables. You will need to write the "CREATE TABLE ..." statements and run them in your Oracle account to create all the tables. Then you populate your tables by using either "INSERT INTO..." statements or Oracle bulk load. To get some realistic data, my suggestion is to grab and process some data from the Facebook website. Although you may not be able to populate a table with lots of content, you are required to estimate the number of records for tables that you created. The absolute numbers are not as important as the relative sizes of the tables. This information is important for choosing indexing and query optimization.
4. Fourth phase: you will come up with 10 typical queries that you expect to run frequently on your database. A good place to start is to look at the search and browse sections of the Facebook website. You will need to write down the English question and its SQL statement for each query and run them in your Oracle database account.
1. What to turn in?
• A final report (.doc, .pdf, .txt, or others) of about 8 pages that includes:
o ER diagram for your design including assumptions that you made, and justification of your assumptions. Please draw you diagram using your favorite drawing tools to give the design a formal feel.
o Any Conversion rules you used when you create your relational database from your ER diagrams.
o Table schemas (table name, attributes, primary keys, and foreign keys) of your database
o Methods you used to populate your tables, the number of records in each of you table, and the estimated number of records in each table in a real-world setup
o The 10 typical queries you came up with in English
o Any other remarks about your design and implementation
• SQL and results:
o Two working SQL files. The SQL file that you used to create all the tables should be saved as createminiFacebook.sql (possibly modified) and your 10 queries should be saved as queryminiFacebook.sql. The associated English question should precede it as a comment (use -- to comment out a line in SQL files).
o Results you obtained for the 10 typical queries
Hints
Spend sufficient time to look at the Facebook website and try to reconstruct the database behind it.
Keep in mind that you are designing a database system that stores data and support queries for the website. When you decide what entities you want to create for your database, try to think what kind of data you are expecting to put into the corresponding table. If the table corresponding to an entity does not have data at all, it is likely that the entity is not needed.
When you try to choose a good query plan, you will need to consider the expected sizes of the tables. For choosing indexing schemes, the frequency of queries posted to the system is important since you want to speed up the most frequent queries first.
1) Provide a web interface
2) Implement a substantial database emulation of the entire Facebook site
Attachment:- Database Management Systems.rar