Project Description
Creating a mock database that will be used to run a movie (and TV show) ratings web site called myFlicks.com, similar to Flixster.com, without all of the features of Flixster. NOTE: You will not be creating the actual web site, nor will you need to know anything about web programming to complete this assignment. You will be creating a database that would sit behind such a site with the requirements being presented below. For your database, you will need to create all of the entities, attributes, and relationships necessary for your fake application, called myFlicks.com, to function.Â
Basic functionality of the myFlicks.com site would allow a person browsing the site to review information about movies, rate movies, and see ratings of their friends. There are undoubtely other features of such a site, but these are the main features that you must model within your deliverables.
To support such a database and its transactions, you need to design a database that would be used to keep track of all of the interactions that a user would make when interacting with the site as described above. Specifically, movies must be searchable/viewable in categories and include information such as the name of the movie, category, description, release date, producer and director(s). Different episodes of TV shows are classifiec as different videos. However, a TV episode will belong to a collection and have an epsiode number. Each collection may contain one or more seasons. One or more directors direct each movie or a TV episode. And each director is identified by their name and age. Each video could belong to one or more genre (e.g., action, comedy, romantic, horror, thriller, and historical). myFlicks will keep track of user ratings for each video (ie, 1-5). A particular user rates a particular video at a particular time. There is a built-in social network between users. Users can send 'friend requests' to other users. Such requests can be accepted or ignored. Once accepted, the two users are considered 'friends'. You will need to store when the requests were sent (timestamp), the result of the requests and when the request was answered (timestamp).
Project Deliverable #1
For this project, you will submit a data dictionary containing all of your tables (entities), their attributes, and the attributesâ€TM characteristics. You will need to ensure that the data has been normalized (data dependency diagrams may help). Be sure to state any assumptions you make. You will submit a complete report, including the data dictionary, and a justification of your table/attribute assignment/creation thoughts. The entire submission should be around 4 pages (2 pages for the data dictionary itself and 1-2 pages for an explanation of your design).
Project Deliverable #2
To support myFlicks.com's database transactions, you are now to design a database that would be used to keep track of the transactions that will be made according to your design/needs document submitted within Project Deliverable #1. You will submit a report containing the ERD diagram (Visio-based recommended, no hand-drawn ERDs) for your database, and an explanation/justification of the diagram, all within a single Word document. For the justification, you need to explain why you selected specific entities (if changed since the submission of project 1) and why the relationships are the way they appear in the ER diagram (1:M, M:N broken down, optional, etc.). The report/explanation and ER diagram must not be more than 5 pages single-spaced. Be sure to state any assumptions you make. Also be sure to identify the keys and non-key attributes (means that all attributes must be present) in the entity-relationship diagram.
Project Deliverable #3
Create the corresponding relational data model by implementing the database for myFlicks.com within the course's mySQL site, linked on the course's homepage within Blackboard. Be sure to populate your tables with dummy data, providing me with the SQL scripts used to create the tables and insert the data into the actual database within a Word document. You need only to have enough data so that your queries (explained below) can be processed and not a complete product or customer inventory.
For this project, you will also create at a minimum 10 important SELECT queries that you need for the day to day management and maintenance of the operation of the site to turn raw data into useful information. In other words, these should all be SELECT statements that myFlicks.com administration or the site itself would actually process to present data to the user or management. Run the queries against your database to ensure the results are correct. Justify why each of the queries you created are important to the application in a written report. Be sure that you make the best use of the data that you can.
I will look for at least two major areas in your implementation. (a) First, I will check the structure of the tables - has entity and referential integrity been enforced and does the structure of your relational database match the ER diagram you submitted as deliverable #2 (b) most importantly, do you have queries that support the transactions associated with myFlicks.com. Remember that I will be looking for and grading 10 queries, but at the same time, I grade on a difficulty scale. Points will be awarded based on complexity, meaning I am looking for aggregate information, joins, etc. and not just statements such as select * from users, etc. As an example, you may have 10 queries, but if one is a very simple and not very useful query, it may get only 2 points (each is worth 7 with the other points given to the database's integrity structure). Also, make sure no query or report produces a null output. I must see data in the output for all queries/reports. Remember, and I reiterate, grading for the queries/reports will be based on the complexity of the queries. Simple queries are allowed, but are awarded very few points.