Data Systems for Software Engineers Assignment 2

SOEN 363: Data Systems for Software Engineers Assignment 2

Overview In this assignment, you create a local database of movies and their information. The assignment targets writing SQL queries for data denition, data collection, as well as search queries. While IMDB [1] (or any other movie public database) may be used as the main source of the data, in this assignment you are required to create your own database and populate the data. The data population may be done through public APIs [2] such as IMDbOT [3]. Most APIs return data in JSON format. Using a JSON Visualizer [5] would be extremely helpful in locating the info items that are returned by the API. GET https://search.imdbot.workers.dev/?tt=tt0068646 You may optionally start with some pre-populated database (see ref. [4]), however, your database must strictly follow the following design. 1 Movies are uniquely identied by their associated imdb-ids. For each movie, store: title, description / plot, rating, content rating, genres, actors, directors, creators, release year, runtime, AKAs, countries, languages, keywords, and number of reviews. Note that both IMDbOT and the above Sample Movies Database are given as examples. You may nd alternatives on the internet. You are allowed to use any data source that ts the purpose. Implementation Platform In this assignment, you may use either MySQL [6] or PortgreSQL [7] to implement the database tables. Clearly indicate the RDBMS system that you use. SQL Queries Provide answer to each of the following parts in separate .sql les. Part 1. [15 pts] Provide the DDL queries for creating the database tables that correspond entities and their relationships. ˆ Use internal integer primary keys. ˆ Include referential integrity / unique constraints. ˆ Dene a person table and use IS-A relation to address actors, directors, and creators. Note that actors include character names. ˆ Include full country name and short code for countries. ˆ Create a foreign tables for genres, content rating, and keywords. ˆ Use singular names for tables / relations. Part 2. [25 pts] Provide the DML queries that populates the data in the database. Make sure you create at least 50 movies. While you may use any approach to populate the data into your database, you need to export your database tables into insert queries. Note that the graders will run the DDL queries in the previous section to create your empty database 2 followed by the insert queries in this section. Make sure the queries are provided in correct order. Part 3. [10 pts] Create the following views: ˆ movie-summary: that displays imdb key, title, description / plot, rating, content rating, runtime, number of keywords, number of countries. ˆ people: imdb key, name, is-actor (yes/no), is-director (yes/no), is-creator (yes/no), total-number-of-movies. Part 4. [50 pts] Provide the following queries: A) [5 pts] Find total number of movies in the database. B) [5 pts] Pick an actor. Find all movies by that actor that is release prior to 2010. List imdb id, movie title, release date, C) [5 pts] Find movies that have highes number if keywords. Provide the query with and without using the view. D) [5 pts] Find number of movies that are in more than one language. E) [5 pts] For each language list how many movies are there in the database. Order by highest rank. F) [5 pts] List all French Canadian movies. G) [5 pts] List all non-English movies that are not made in Canada / USA. H) [5 pts] List all movies that are directed by one of its actors. I) [5 pts] Find top 3 movies (higher ratings). Sort by rating as well as number of reviews. J) [5 pts] Find top 3 movies that have at least 10 reviews. Make sure all above queries return data. Modify the data in your database, if necessary. Submit your assignment electronically on Moodle: https://moodle.concordia.ca Include your name and student ID in the submission. Make sure that you upload the assignment to the correct assignment box on Moodle. No email submissions are accepted. Assignments uploaded to the wrong system, wrong folder, or submitted via email will be discarded and no resubmission will be allowed. Make sure you can access Moodle prior to the submission deadline. The deadline will not be extended. 3 References 1. https://www.imdb.com/ 2. https://github.com/public-apis/public-apis 3. https://github.com/SpEcHiDe/IMDbOT 4. https://www.databasestar.com/sample-database-movies/ 5. https://jsonviewer.stack.hu/ 6. https://www.mysql.com/ 7. https://www.postgresql.org/ 4