Assignment 2: 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.
Timelines and Expectations
Percentage Value of Task: 25% (100 marks)
Due: Week 11 – Sunday at 11:59 pm
Minimum time expectation: Preparation for this task will take approximately 20 hours
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
K5. Describe relational algebra and its relationship to Structured Query Language (SQL).
S1. Interpret entity-relationship diagrams to implement a relational database.
S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
S3. Use a query language for data manipulation.
A1. Design and implement a relational database using a database management system.
A2. 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 (http://www.imdb.com/).
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.
Figure 1
From: https://www.w3resource.com/sql-exercises/
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..
Table: actor
act_id | act_fname | act_lname | act_gender
101 | James | Stewart | M
102 | Deborah | Kerr | F
103 | Peter | OToole | M
104 | Robert | De Niro | M
105 | F. Murray | Abraham | M
106 | Harrison | Ford | M
107 | Nicole | Kidman | F
108 | Stephen | Baldwin | M
109 | Jack | Nicholson | M
110 | Mark | Wahlberg | M
111 | Woody | Allen | M
112 | Claire | Danes | F
113 | Tim | Robbins | M
114 | Kevin | Spacey | M
115 | Kate | Winslet | F
116 | Robin | Williams | M
117 | Jon | Voight | M
118 | Ewan | McGregor | M
119 | Christian | Bale | M
120 | Maggie | Gyllenhaal | F
121 | Dev | Patel | M
122 | Sigourney | Weaver | F
123 | David | Aston | M
124 | Ali | Astin | F
Table: movie_cast
act_id | mov_id | role
101 | 901 | John Scottie Ferguson
102 | 902 | Miss Giddens
103 | 903 | T.E. Lawrence
104 | 904 | Michael
105 | 905 | Antonio Salieri
106 | 906 | Rick Deckard
107 | 907 | Alice Harford
108 | 908 | McManus
110 | 910 | Eddie Adams
111 | 911 | Alvy Singer
112 | 912 | San
113 | 913 | Andy Dufresne
114 | 914 | Lester Burnham
115 | 915 | Rose DeWitt Bukater
116 | 916 | Sean Maguire
117 | 917 | Ed
118 | 918 | Renton
120 | 920 | Elizabeth Darko
121 | 921 | Older Jamal
122 | 922 | Ripley
114 | 923 | Bobby Darin
109 | 909 | J.J. Gittes
119 | 919 | Alfred Borden
Table: movie
mov_id | mov_title | mov_year | mov_time | mov_lang | mov_dt_rel | mov_rel_country
901 | Vertigo | 1958 | 128 | English | 1958-08-24 | UK
902 | The Innocents | 1961 | 100 | English | 1962-02-19 | SW
903 | Lawrence of Arabia | 1962 | 216 | English | 1962-12-11 | UK
904 | The Deer Hunter | 1978 | 183 | English | 1979-03-08 | UK
905 | Amadeus | 1984 | 160 | English | 1985-01-07 | UK
906 | Blade Runner | 1982 | 117 | English | 1982-09-09 | UK
907 | Eyes Wide Shut | 1999 | 159 | English | | UK
908 | The Usual Suspects | 1995 | 106 | English | 1995-08-25 | UK
909 | Chinatown | 1974 | 130 | English | 1974-08-09 | UK
910 | Boogie Nights | 1997 | 155 | English | 1998-02-16 | UK
911 | Annie Hall | 1977 | 93 | English | 1977-04-20 | USA
912 | Princess Mononoke | 1997 | 134 | Japanese | 2001-10-19 | UK
913 | The Shawshank Redemption | 1994 | 142 | English | 1995-02-17 | UK
914 | American Beauty | 1999 | 122 | English | | UK
915 | Titanic | 1997 | 194 | English | 1998-01-23 | UK
916 | Good Will Hunting | 1997 | 126 | English | 1998-06-03 | UK
917 | Deliverance | 1972 | 109 | English | 1982-10-05 | UK
918 | Trainspotting | 1996 | 94 | English | 1996-02-23 | UK
919 | The Prestige | 2006 | 130 | English | 2006-11-10 | UK
920 | Donnie Darko | 2001 | 113 | English | | UK
921 | Slumdog Millionaire | 2008 | 120 | English | 2009-01-09 | UK
922 | Aliens | 1986 | 137 | English | 1986-08-29 | UK
923 | Beyond the Sea | 2004 | 118 | English | 2004-11-26 | UK
924 | Avatar | 2009 | 162 | English | 2009-12-17 | UK
926 | Seven Samurai | 1954 | 207 | Japanese | 1954-04-26 | JP
927 | Spirited Away | 2001 | 125 | Japanese | 2003-09-12 | UK
928 | Back to the Future | 1985 | 116 | English | 1985-12-04 | UK
925 | Braveheart | 1995 | 178 | English | 1995-09-08 | UK
Table: director
dir_id | dir_fname | dir_lname
201 | Fred | Caravanhitch
202 | Jackie | Claytonburry
203 | Greene | Lyon
204 | Miguel | Camino
205 | George | Forman
206 | Antartic | Scott
207 | Stanlee | Carbrick
208 | Bryon | Sanger
209 | Roman | Polanski
210 | Paul | Thomas Anderson
211 | Woody | Allen
212 | Hayao | Miyazaki
213 | Frank | Darabont
214 | Sam | Mendes
215 | James | Cameron
216 | Gus | Van Sant
217 | John | Boorman
218 | Danny | Boyle
219 | Christopher | Nolan
220 | Richard | Kelly
221 | Kevin | Spacey
222 | Andrei | Tarkovsky
223 | Peter | Jackson
Table: movie_direction
dir_id | mov_id
201 | 901
202 | 902
203 | 903
204 | 904
205 | 905
206 | 906
207 | 907
208 | 908
209 | 909
210 | 910
211 | 911
212 | 912
213 | 913
214 | 914
215 | 915
216 | 916
217 | 917
218 | 918
219 | 919
220 | 920
218 | 921
215 | 922
221 | 923
Table: genres
gen_id | gen_title
1001 | Action
1002 | Adventure
1003 | Animation
1004 | Biography
1005 | Comedy
1006 | Crime
1007 | Drama
1008 | Horror
1009 | Music
1010 | Mystery
1011 | Romance
1012 | Thriller
1013 | War
Table: movie_genres
mov_id | gen_id
922 | 1001
917 | 1002
903 | 1002
912 | 1003
911 | 1005
908 | 1006
913 | 1006
926 | 1007
928 | 1007
918 | 1007
921 | 1007
902 | 1008
923 | 1009
907 | 1010
927 | 1010
901 | 1010
914 | 1011
906 | 1012
904 | 1013
Table: rating
mov_id | rev_id | rev_stars | num_o_ratings
901 | 9001 | 8.40 | 263575
902 | 9002 | 7.90 | 20207
903 | 9003 | 8.30 | 202778
906 | 9005 | 8.20 | 484746
924 | 9006 | 7.30 |
908 | 9007 | 8.60 | 779489
909 | 9008 | | 227235
910 | 9009 | 3.00 | 195961
911 | 9010 | 8.10 | 203875
912 | 9011 | 8.40 |
914 | 9013 | 7.00 | 862618
915 | 9001 | 7.70 | 830095
916 | 9014 | 4.00 | 642132
925 | 9015 | 7.70 | 81328
918 | 9016 | | 580301
920 | 9017 | 8.10 | 609451
921 | 9018 | 8.00 | 667758
922 | 9019 | 8.40 | 511613
923 | 9020 | 6.70 | 13091
Table: reviewer
rev_id | rev_name
9001 | Righty Sock
9002 | Jack Malvern
9003 | Flagrant Baronessa
9004 | Alec Shaw
9005 |
9006 | Victor Woeltjen
9007 | Simon Wright
9008 | Neal Wruck
9009 | Paul Monks
9010 | Mike Salvati
9011 |
9012 | Wesley S. Walker
9013 | Sasha Goldshtein
9014 | Josh Cates
9015 | Krug Stillo
9016 | Scott LeBrun
9017 | Hannah Steele
9018 | Vincent Cadena
9019 | Brandt Sponseller
9020 | Richard Adams
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.
Table: director (Updates)
dir_id | dir_fname | dir_lname | dob
201 | Alfred | Hitchcock | 1964-08-12
202 | Jack | Clayton | 1955-11-22
203 | David | Lean | 1969-05-15
204 | Michael | Cimino | 1959-11-08
205 | Milos | Forman | 1988-09-12
206 | Ridley | Scott | 1956-02-07
207 | Stanley | Kubrick | 1967-11-08
208 | Bryan | Singer | 1960-02-28
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, see:
https://federation.edu.au/__data/assets/pdf_file/0018/190044/General-Guide-to-Writing-andStudy-Skills.pdf , page 36.
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
2. A copy of the database file you are required to create for questions 1–11, file name:
itech2004_2_queries_yourname_studentID.txt
Academic Presentation
Assignment should be presented in accordance with:
• General Guide to Referencing: https://federation.edu.au/__data/assets/pdf_file/0020/313328/FedUni-GeneralGuide-to-Referencing-2016ed.pdf
• General Guide to Writing and Study Skills: http://federation.edu.au/__data/assets/pdf_file/0018/190044/GeneralGuide-to-Writing-and-Study-Skills.pdf
• Guide to Layout and Appearance: https://federation.edu.au/__data/assets/pdf_file/0017/190043/General-Guideto-Layout-and-Appearance.pdf
Submission
The assignment is to be submitted via the Assignment 2 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell.
1. The report file described above EITHER as a MS word file or a PDF. If you are using a mac, please submit in a PDF.
2. The database file, as described above.
Please note that the SQL files must be either .txt or .sql so they can be run If they are in a word or pdf file there will be extra characters that will prevent the files from running without errors and will cost marks.
Marking Criteria/Rubric
Assessment Criteria
Marking Scale
Poor Excellent
1 ...................... 5
Presentation and Referencing
• Overall presentation of the report (including title page) 0
Relational Schema (Question 1)
• All tables included
• Data types correct, with
Primary and foreign keys indicated and referenced correctly 0
0
Data Import (Question 2)
• All data imported correctly 0
SQL Statements 0
• Question 3
• Question 4 0
• Question 5 0
• Question 6 0
• Question 7 0
• Question 8 0
• Question 9 0
• Question 10 0
• Question 11 0
• Question 12 0
• Question 13 0
• Question 14 0
• Question 15 0
• Question 16 0
• Question 17 0
• Question 18 0
Total Mark [100 marks] 100.0
Total Worth [25%] 25.0