Description

Specifications 

• For this homework, you need to submit two files: 

o APEX-encoded SQL script (.sql) with your queries to answer each question. (Note: use the “Export” option under SQL Scripts.) Use SQL comments (--) to mark question number in your script. 

o Word or PDF file with screenshots of the code and output for each question 

• Your file names should contain your last name and the assignment name (e.g., “lastname_homework4.sql”, “lastname_homework4.docx”) 

• All files should be attached in one submission: 

o If you are using a cloud service (e.g., Google Drive, OneDrive), to save your files, you may need to download the files and submit the local copies (stored on your computer) to ICON 

o If you need to re-submit your assignment, make sure to include all files

Download JOLIE.sql from ICON. In your APEX workspace, go to SQL Workshop > SQL Scripts. Import the JOLIE.sql script file, then run it. The Jolie database contains data pertaining to the career of Angelina Jolie (https://en.wikipedia.org/wiki/Angelina_Jolie) sourced from IMDB and Wikipedia. The normalized database contains 5 tables: 

• MOVIE_T tracks the unique ID, title, release year, running time (in minutes), average IMDB user rating (out of 10), and total number of user votes for 42 movies where Jolie had a principal role. 

• GENRE_T records the ID and genre (e.g., Comedy, Action, Drama) of each movie. Each movie is associated with 1-3 genres, with 113 rows total. 

• PERSON_T contains the unique ID, name, birth year, and death year for 230 people that have worked on movies with Angelina Jolie. Some people are missing name information, birth year, and/or death year. 

• CAST_T records the principal people involved in each movie, including lead actors, directors, and/or producers. There are up to 10 principals for each movie, and a person can have multiple roles/jobs on the same film. For instance, Jolie acted in and produced Maleficent. Each of the 291 rows records a unique cast ID, movie ID, person ID, and role. For acting roles, there is also a character name. 

• HONOR_T contains 25 rows with information about honors and awards that Jolie has earned during her career. Each row has a unique ID, year, award name, organization that gives the award, and a binary (0/1) variable tracking whether Jolie won the award. In addition, each record is linked to Jolie’s unique person ID, and some awards are linked to specific movies. 

You should review each table in the Object Browser and/or examine the SQL script to make sure that you understand the field definitions, constraints, primary key(s), foreign key(s), and relationships. A graphical relational schema for the database is shown below: