IMDB Movie Dataset Analysis
Budget: £25 – £35 GBP
Data We will use a dataset from Kaggle containing information about 1000 popular movies (films). The data is available on Moodle in the file downloaded-imdb_top_1000.csv. Here is a description of the columns in the dataset: • Poster_Link - Link of the poster that IMDB uses • Series_Title - Name of the movie • Released_Year - Year in which that movie released • Certificate - Certificate earned by that movie • Runtime - Total runtime of the movie • Genre - Genre of the movie • IMDB_Rating - Rating of the movie at IMDB site • Overview - mini story/ summary • Meta_score - Score earned by the movie (from movie critics) • Director - Name of the Director • Star1,Star2,Star3,Star4 - Name of the actors starring in the move • No_of_votes - Total number of user votes for the movie • Gross - Money earned by that movie
Task A
First, take a look at the data in the file downloaded-imdb_top_1000.csv. Then specify a CREATE TABLE statement that would make a table IMDB_Movie, which this data could be loaded into. Pay attention to specifying the datatype for each column, the primary key, and which fields should be NOT NULL.
Task B
Discuss any issues that you can foresee loading the data into your table IMDB_Movie. (You do not need to load the data yet, we will do that in Stage 2).
Task C
1. Write a SQL SELECT statement that you could run on the table to find how many films there are for each certificate.
2. Write a SQL SELECT statement that you could run on the table to find how many films there are for each genre. (You do not need to run these queries yet, just to plan and write them. We will discover in Stage 2 whether you need to rethink them once the database is created).
Task D
The table you would create is not normalised. Discuss the issues with the table structure and propose a normalised schema for the same data. Explain the reasons for your changes.
The purpose of stage 1 is to ensure that you have a good database design for the technical implementation in Stage 2. Allocate approx. 4 – 6 hours’ work to Stage 1
submit a single file in PDF, ODT, or Word format.
As noted above, you’ll need to submit a single document in Word, LibreOffice or PDF format. Your ER Diagram you can save from diagrams.net (formerly draw.io) as a .jpg file, and paste this into your document. If you have used Excel, you’ll need to past or screenshot the Excel cells and paste these in. (Note that TurnitIn does not work with embedded documents). There are no layout requirements about fonts, margins etc, but just be sure your submission is legible.
Task A
First, take a look at the data in the file downloaded-imdb_top_1000.csv. Then specify a CREATE TABLE statement that would make a table IMDB_Movie, which this data could be loaded into. Pay attention to specifying the datatype for each column, the primary key, and which fields should be NOT NULL.
Task B
Discuss any issues that you can foresee loading the data into your table IMDB_Movie. (You do not need to load the data yet, we will do that in Stage 2).
Task C
1. Write a SQL SELECT statement that you could run on the table to find how many films there are for each certificate.
2. Write a SQL SELECT statement that you could run on the table to find how many films there are for each genre. (You do not need to run these queries yet, just to plan and write them. We will discover in Stage 2 whether you need to rethink them once the database is created).
Task D
The table you would create is not normalised. Discuss the issues with the table structure and propose a normalised schema for the same data. Explain the reasons for your changes.
The purpose of stage 1 is to ensure that you have a good database design for the technical implementation in Stage 2. Allocate approx. 4 – 6 hours’ work to Stage 1
submit a single file in PDF, ODT, or Word format.
As noted above, you’ll need to submit a single document in Word, LibreOffice or PDF format. Your ER Diagram you can save from diagrams.net (formerly draw.io) as a .jpg file, and paste this into your document. If you have used Excel, you’ll need to past or screenshot the Excel cells and paste these in. (Note that TurnitIn does not work with embedded documents). There are no layout requirements about fonts, margins etc, but just be sure your submission is legible.
Related categories:
SQL
Database Administration
Relational Databases
Database Design
Database Management