DA

DATABASE CAN BE DOWNLOADED AT https://dev.mysql.com/doc/index-other.html labeled 'sikila database'

INFSCI 1022 Database Systems-Fall 2016 SQL JOINS AND AGGREGATE FUNCTIONS Connect to an external database server with MySQL Workbench using the following settings and credentials 1. Connection name: INFSCI1022 REMOTE 2. Hostname: 162.243.106.12 3. Port: 3306 4. Username: is1022 5. Password: i$1022 Once you are connected, you should see two databases under the "SCHEMAS" section of your MySQL Workbench - world and sakila. For this homework, you will be using the sakila database Task 1 (10 points): Reverse-engineer the sakila schema into an E-R model - this will help you visualize the relationship between tables. Save the reversed-engineered file as a Workbench .mwh file. Name that file LastName_Assignmentff.mwb. You will have to submit this file as part of your homework assignment. Task 2 (20 points): Write a query that produces a list of documentary movies (where movie category is 'Documentary) and a movie description contains the word 'Drama'. You will have to create a triple join between category, film category, and category tables. Your result should contain the following columns with meaningful aliases: 1. Category Name 2. Film Title 3. Film Description 4. Release Year Hint: you will need to create aliases for all tables involved in this query... Task 3 (20 points): Write a query that produces a list of movies an actress by the name of JULIA MCQUEEN played a role. You will have to create a triple join between film, ilmactor. and actor tables. Your result should contain the following columns with meaningful aliases: 1. Film Title 2. Actor name (in a single column, last name and first name concatenated into a single string and separated by a comma) Task 4 (10 points): Modify the query in a previous task to list all actors who played in a movie called "AMADEUS HOLY". Do not modify the original query - make a copy of it and modify that copy Task 5 (30 points): Write a query that produces a list of movies rented by KATHLEEN ADAMS. You will have to join the following 4 tables: 1. customer 2. rental 3. inventory 4. film Review the ER-model that you reverse-engineered at the beginning of this assignment to figure out primary/foreign key relationships in these 4 tables. You can select all columns SELECT Task 6 (10 points): Review the E-R model created in the first step. Identify and write three questions that would be useful for a video rental store that you could answer with available data. You can write your questions as SQL comments at the bottom of your SQL script