Reference no: EM132643502
ITECH 2004 Data Modelling - Federation University
Assignment: SQL
Overview
The purpose of this task is to develop student's skills in designing and implementing a relational database for a given case study.
Learning Outcome 1: Describe relational algebra and its relationship to Structured Query Language (SQL). S1. Interpret entity-relationship diagrams to implement a relational database.
Learning Outcome 2: Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
Learning Outcome 3: Use a query language for data manipulation.
Learning Outcome 4: Design and implement a relational database using a database management system.
Learning Outcome 5: 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.
Attachment:- Data Modelling.rar