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 Movie “God Father”. 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 ratings
Requirement
According to the database design made by you, the movie database includes multiple tables. In particular, you need to consider seven tables: users, movies, taginfo, genres, ratings, tags, hasagenre. In this phase, you must create these tables and load the corresponding data into these tables.
1. The description of the tables is as follows. You should also check the requirement in thegiven graphic description:
users: userid (int, primary key), name (text)
movies: movieid (integer, primary key), title (text)
taginfo: tagid (int, primary key), content (text)
genres: genreid (integer, primary key), name (text)
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 by yourself. The key information in the requirement is not complete and attributes can be primary keys and foreign keys at the same time.
Remarks
1. All table names and attribute names must be in lowercase letters and exactly same with the specification.
2. You can use COPY FROM / INSERT to load data and test your tables but don’t put it inthe submission.
Test data
We provide some test data for you to try. The delimiter of all files is “%”. But the grading system will use more test data and test cases to try your SQL script.
Data link:
https://github.com/jiayuasu/Coursera-ASU-
Database/tree/master/course1/assignment1/exampleinput
(https://github.com/jiayuasu/Coursera-ASU-
Database/tree/master/course1/assignment1/exampleinput)
Submission
Submit a single plain text file “solution.sql”
