CSE511 Assignment1- Create a Movie Database Solution

Create a Movie Database

Introduction:
You have learned how to design a movie recommendation database. The assignment will give you an opportunity to create such a database from scratch and build applications on top of this database.

Background:
In this database, a movie has two attributes: id, title. A possible movie record is as follows: 54796, 2 Days in Paris (2007).
A user can give a 5-star scale rating (0-5) to a movie. For instance, User (ID 4) gave 4 stars to the movie “The GodFather”. A user can only rate a movie once. The database needs to log each rating operation. The database should not allow any out-of-range movie ratings.

Two days later, he added a new tag, “unbelievable”, to the same movie. Each tag is typically a single word or short phrase. The meaning, value, and purpose of a particular tag are determined by each user. The database needs to log each tagging operation.
Requirements:
Based on the movie database design, the movie database includes multiple tables. For this assignment, you need to create seven tables: users, movies, taginfo, genres, ratings, tags, hasagenre. Then load the corresponding data into the tables.



1. The description of the tables is as follows:
A. users: userid (int, primary key), name (text)
B. movies: movieid (integer, primary key), title (text)
C. taginfo: tagid (int, primary key), content (text)
D. genres: genreid (integer, primary key), name (text)
G. hasagenre: movieid (int, foreign key), genreid (int, foreign key)
2. The requirement only tells you the name and data type of each attribute in each table. You need to figure out the primary keys, foreign keys, constraints or other necessary settings for the database by yourself. The key information in the requirement is just a skeleton and not complete; attributes can be primary keys and foreign keys at the same time.
Test Data:
We provide some test data for you to try. The delimiter of all files is “%”. Note that the grading system will use more test data and test cases to try your SQL scripts.
Data link:
https://github.com/jiayuasu/Coursera-ASU-Database/tree/master/course1/assignment1/example input
To download the data:
1. Go to the file you want to download.
2. Click it to view the contents within the GitHub UI.
3. In the top right, right click the Raw button.
4. Right click and select Save as.

Assignment Tips:
1. All table names and attribute names must be in lowercase letters and match the specification.
2. You can use COPY FROM / INSERT to load data to test your tables but don’t include the test data in the submission. Information on test data is provided in the Test Data section below.
Submission:
Submit a single plain text file “solution.sql”. This file should contain the SQL scripts for creating the seven tables.
Feedback:
There are five test cases for a total of 1 point, so each test case is worth 0.2 points. If your .sql fails, you will see the corresponding .sql error logs that indicate where the error occurred. In the end, if the submission runs correctly, you will see feedback "You passed 5/5 tests."
Test cases are not executed in parallel. You have to unlock all previous test cases to execute the current test case. For example, to execute test case 2 your code must be able to successfully execute test case 1. The test case number in the feedback will provide you an indication of which part of the submission caused the error.