Exercise goal is to create a valid database structure and store data about the movies.
You will learn:
- database structure design;
- integrity constraints;
- executing queries;
Source Data @_data
Before the exercise go through the data in .csv files, they can be opened with Excel / LibreOffice / etc.
movies.csv contains information about the movies, together with directors, actors and other information.
ratings.csv contains information about ratings which where left for each movie by users.
Parsing is already done and you don't need to do anything, take a look ./src/data & ./test/data
Tests are in ./test directory, work on them in ascending order.
Each test is creating a new database file from the previous test file and storing it in the ./_db directory. Which means that you may want to move some steps backwards also, even if all the tests were green.
To run test execute npm run test-xx where xx is test prefix, for example npm run test-00. Take a look if the database file was created.
If test is green you are ready to go!
To store all the information from the source files we will need tables, but you may have noticed that there are lots of nested information in the movies.csv file. Tables are built a little bit differently, we will have separate tables for directors, actors, etc.
Make all the tests green by writing correct statements for database creation.
Table can be created by using a simple SQL Statement.
There are multiple things to keep in mind:
- Each of the column must have a proper datatype, see SQLite Datatypes.
- Each table must have a primary key.
- Think about which of the columns cannot be
nullin any circumstances, add NOT NULL Constraints. - These tables are quite big, for example
ratings.csvhas more than 1 million records, you may want to add an index to some tables. - Are there any columns or combination of columns which must be unique? Try adding unique index.
When test is green, open database file in DBVis to see a database structure.
Now our tables are ready, but there is no data.
Write proper insert statements to fill the data. All of the inserts are in batches (there is a limit), you need to insert multiple values at once.
When you have this test green, open database file in SQLite Browser and try to execute few queries to see that everything works fine.
This step is very similar to the previous one, we need to insert data from reviews.csv. This will be a little bit trickier, because each review is given to a movie from the movies table.
Create tables which stick together our existing tables.
You may want to repeat about Relationships in Database Design, for example here @database.guide
Fill created relationship tables with the data.
Simple queries.
If you are stuck check out examples @sqlzoo.net and @w3schools.com.
More complex queries, mainly joins between tables.
If you are stuck check out examples @sqlzoo.net and @w3schools.com.
Can movie review exist without the movie?