Reference no: EM132660020

Assignment: SQL

Overview
The purpose of this task is to develop student's skills in designing and implementing a relational database for a given casestudy.

Learning Outcome 1: Describe relational algebra and its relationship to Structured Query Language (SQL).
Learning Outcome 2: Interpret entity-relationship diagrams to implement a relational database.
Learning Outcome 3: Demonstrate skills in designing and building a database application using a commercially availabledatabase management system development tool.
Learning Outcome 4: Use a query language for data manipulation.
Learning Outcome 5: Design and implement a relational database using a database management system.
Learning Outcome 6: Utilise a query language tools and techniques to obtain data and information from a database.

Assessment Details
Background

In this assignment you are asked to write several SQL queries on a relational movie database. The data in this database is from the IMDB website

Requirements

Question 1.Write the SQL code to create the table structures for the entities shown in Figure 1. The diagram was created using Microsoft Access designer. The structures should contain the attributes specified in the diagram. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the diagram.

Question 2.The following tables provide some example data that will be kept in the database. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1.Alternatively provide the text files (copy and pasted into your final report) and the open/insert from file commands..

For Questions 3-18, use the tables that were created in Question 1 and the data that was loaded into those tables in Question 2.

Question 3.Write the SQL command to find the name and year of the movies that contain the word "the".

Question 4.Write the SQL command to return the name of all genres and name of movies together in a single list.

Question 5.Write the SQL command to aggregate sum the number of ratings and average number of stars per reviewer.

Question 6.Write the SQL command to list the names of the actors who did not play a role in the movie Deliverance.

Question 7.Write the SQL command to find the titles of all movies that have more than one reviewer.

Question 8. Write the SQL command to find the movie title, and the total number of actors that appeared in that movie, and arrange the result according to the number of actors in descending order.

Question 9. Write the SQL command to find the titles of all movies where the average stars rating is higher than 8.0 and the actor was Christian Bale.

Question 10.Update the director table to set the values in DETAIL_RETURNDATE to include a date of birth (dob) component, and also correct some of the name mistakes.For those not listed, assume the original name is correct, and also give a null value for dob. Make each entry match the values shown in the following Table.

Question 11. Write the SQL command to list all the directors who have not directed a movie since 2000.

Question 12. Write the SQL command to list all the last movie that each actor has appeared in.

Question 13. Write the SQL command that determines the total length of film time for all the films that an actor has appeared in.

Question 14. Write the SQL command to find movie title and number of stars for each movie that has at least one reviewer and find the least number of stars that movie received and sort the result by number of stars.

Question 15. Write the SQL command to determine if any actors worked with the same director more than once.

Question 16. Write the SQL command to list all directors in descending order of the number of films they directed.

Question 17. Write the SQL command to find the film(s) with the largest cast. Find the film(s) with the smallest cast. In both cases, also return the size of the cast.

Question 18. Write the SQL command to find the films with more women actors than men.

Documentation requirements
1. Prepare a business style report which contains the following:
a). Each query and associated output
b). Full APA referencing of any resources used
A suggested structure for the above report is provided. You can use it as a guideline for presenting your business report.For assistance in report writing techniques,

Report file name: itech2004_2_report_yourname_studentID

Title page
Executive summary
Table of contents
1.0 Introduction
2.0 SQL Statements Report
2.1 Question 1
2.2 Question 2
2.3 Question 3
2.4 Question 4
...
2.18 Question 18
3.0 Conclusion
4.0 Bibliography

Attachment:- DATA MODELLING.rar