[SOLVED] SQL database CSCI 620/Section 04/Mior, Introduction to Big Data, Spring 2185

CSCI 620/Section 04/Mior, Introduction to Big Data, Spring 2185
Assignment 1 Relational model
Description
Lets model IMDB (the Internet Movie Database). You can find a description and the datasets to download here: https://www.imdb.com/interfaces. The database system we are going to use in this assignment is PostgreSQL (any 9.X or later version will work).
Your tasks
1. Provide a description of the contents of the files.
(15 points)
2. Provide a relational model to store IMDB information. Restrict yourself to non-adult movies (including genres, ratings and votes), actors (not including character information), directors, writers and producers (we are not interested in jobs, professions or known for titles). Provide a diagram and SQL scripts to create the whole schema including users and privileges. You need to explore the dataset to decide the sizes of the attributes in advance and be careful when using reserved words like order or character. Integers are preferred over strings for primary keys. Provide a brief description on how you solved these issues. (25 points)
3. Provide a program to load the IMDB data from the text files into the database. You should take into account the charset of the text files. Your program needs to load the whole database in approximately four hours using commodity hardware. Provide a brief description on how you solved these issues and report your timings.
(45 points)
4. Provide a program that connects to the previous database and creates a transaction to insert three rows of data. Force an error in row #2 in such transaction and ensure that the database is in the same state as before. (15 points)