Provision 2: SQL
Overview
The end of this labor is to unfold student’s skills in maneuvering and instrumenting a conceptional groundsbase coercion a attached circumstance con-over.
Timelines and Expectations
Percentage Esteem of Labor: 25% (100 marks)
Due: Week 11 – Sunday at 11:59 pm
Minimum era expectation: Preparation coercion this labor earn receive approximately 20 hours
Lore Outcomes Assessed
The subjoined passage lore outcomes are assessed by completing this assessment:
K5. Describe conceptional algebra and its relation to Constructiond Inquiry Expression (SQL).
S1. Interpret entity-relation diagrams to instrument a conceptional groundsbase.
S2. Demonstrate skills in maneuvering and architecture a groundsbase contact using a commercially beneficial groundsbase skill method unfoldment instrument.
S3. Rectifyion a inquiry expression coercion grounds production.
A1. Design and instrument a conceptional groundsbase using a groundsbase skill method.
A2. Utilise a inquiry expression instruments and techniques to conquer grounds and referableification from a groundsbase.
Assessment Details
Background
In this provision you are asked to transcribe different SQL queries on a conceptional movie groundsbase. The grounds in this groundsbase is from the IMDB website (http://www.imdb.com/).
Requirements
Question 1. Transcribe the SQL principle to compose the board constructions coercion the entities shown in Figure 1. The diagram was composed using Microsoft Access designer. The constructions should embrace the indications restricted in the diagram. Rectifyion grounds types that are embezzle coercion the grounds that earn need to be stored in each indication. Enforce first explanation and coercioneign explanation constraints as implied by the diagram.
Figure 1
From: https://www.w3resource.com/sql-exercises/
Question 2. The subjoined boards agree some speciman grounds that earn be kept in the groundsbase. Transcribe the INSERT enjoins indispensable to establish the subjoined grounds in the boards that were composed in Question 1. Alternatively agree the passage rasps (observation and pasted into your developed mant) and the open/insert from rasp enjoins..
Table: perpetrator
act_id | act_fcperfect | act_lcperfect | 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 | Coerciond | M
107 | Nicole | Kidman | F
108 | Stephen | Baldwin | M
109 | Jack | Nicholson | M
110 | Mark | Wahlberg | M
111 | Woody | Perfecten | M
112 | Claire | Danes | F
113 | Tim | Robbins | M
114 | Kevin | Spacey | M
115 | Kate | Winslet | F
116 | Robin | Earniams | 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_heading | mov_year | mov_era | 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 Leakner | 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 Hperfect | 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 Earn 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: leader
dir_id | dir_fcperfect | dir_lname
201 | Fred | Caravanhitch
202 | Jackie | Claytonburry
203 | Greene | Lyon
204 | Miguel | Camino
205 | George | Coercionman
206 | Antartic | Scott
207 | Stanlee | Carbrick
208 | Bryon | Sanger
209 | Roman | Polanski
210 | Paul | Thomas Anderson
211 | Woody | Perfecten
212 | Hayao | Miyazaki
213 | Frank | Darabont
214 | Sam | Mandes
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
Coercion Questions 3–18, rectifyion the boards that were composed in Question 1 and the grounds that was loaded into those boards in Question 2.
Question 3. Transcribe the SQL enjoin to perceive the cperfect and year of the movies that embrace the signal “the”.
Question 4. Transcribe the SQL enjoin to render the cperfect of perfect genres and cperfect of movies concertedly in a individual register.
Question 5. Transcribe the SQL enjoin to gist sum the sum of ratings and middle sum of stars per reviewer.
Question 6. Transcribe the SQL enjoin to register the calls of the perpetrators who did referable enact a role in the movie Deliverance.
Question 7. Transcribe the SQL enjoin to perceive the headings of perfect movies that entertain past than individual reviewer.
Question 8. Transcribe the SQL enjoin to perceive the movie heading, and the whole sum of perpetrators that appeared in that movie, and parcel the upshot according to the sum of perpetrators in descending dispose.
Question 9. Transcribe the SQL enjoin to perceive the headings of perfect movies where the middle stars rating is conspicuous than 8.0 and the perpetrator was Christian Bale.
Question 10. Upbound the leader board to fixed the esteems in DETAIL_RETURNDATE to embrace a bound of origin (dob) content, and so rectify some of the cperfect mistakes. Coercion those referable registered, feign the peculiar cperfect is rectify, and so impart a inoperative esteem coercion dob. Make each referablee tally the esteems shown in the subjoined Board.
Table: leader (Updates)
dir_id | dir_fcperfect | dir_lcperfect | 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 | Coercionman | 1988-09-12
206 | Ridley | Scott | 1956-02-07
207 | Stanley | Kubrick | 1967-11-08
208 | Bryan | Singer | 1960-02-28
Question 11. Transcribe the SQL enjoin to register perfect the leaders who entertain referable directed a movie since 2000.
Question 12. Transcribe the SQL enjoin to register perfect the developed movie that each perpetrator has appeared in.
Question 13. Transcribe the SQL enjoin that individualizes the whole prolixity of film era coercion perfect the films that an perpetrator has appeared in.
Question 14. Transcribe the SQL enjoin to perceive movie heading and sum of stars coercion each movie that has at lowest individual reviewer and perceive the lowest sum of stars that movie ordinary and class the upshot by sum of stars.
Question 15. Transcribe the SQL enjoin to individualize if any perpetrators worked with the corresponding leader past than once.
Question 16. Transcribe the SQL enjoin to register perfect leaders in descending dispose of the sum of films they directed.
Question 17. Transcribe the SQL enjoin to perceive the film(s) with the largest air. Perceive the film(s) with the lowest air. In twain circumstances, so render the bigness of the air.
Question 18. Transcribe the SQL enjoin to perceive the films with past woman perpetrators than man.
Documentation requirements
1. Prepare a transaction phraseology mant which embraces the subjoined:
a). Each inquiry and associated output
b). Full APA referencing of any instrument rectifyiond
A suggested construction coercion the over mant is agreed. You can rectifyion it as a guideline coercion presenting your transaction mant. Coercion help in mant congruity techniques, see:
https://federation.edu.au/__data/assets/pdf_file/0018/190044/General-Guide-to-Writing-andStudy-Skills.pdf , page 36.
Ment rasp call: itech2004_2_report_yourname_studentID
Heading page
Executive summary
Board of contents
1.0 Introduction
2.0 SQL Statements Mant
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 observation of the groundsbase rasp you are required to compose coercion questions 1–11, rasp call:
itech2004_2_queries_yourname_studentID.txt
Academic Bestowal
Provision 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 Congruity and Con-over 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 provision is to be resignted via the Provision 2 resignation hustle in Moodle. This can be base in the Assessments exception of the passage Moodle shell.
1. The mant rasp pictorial over EITHER as a MS signal rasp or a PDF. If you are using a mac, delight resign in a PDF.
2. The groundsbase rasp, as pictorial over.
Delight referablee that the SQL rasps must be either .txt or .sql so they can be leak If they are in a signal or pdf rasp there earn be extra characters that earn obviate the rasps from leakning extraneously errors and earn consume marks.
Marking Criteria/Rubric
Assessment Criteria
Marking Scale
Poor Excellent
1 …………………. 5
Bestowal and Referencing
• Overperfect bestowal of the mant (including heading page) 0
Conceptional Schema (Question 1)
• Perfect boards embraced
• Grounds types rectify, with
First and coercioneign explanations implied and referenced rectifyly 0
0
Grounds Import (Question 2)
• Perfect grounds vital rectifyly 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
Whole Mark [100 marks] 100.0
Whole Worth [25%] 25.0

~~~For this or similar assignment papers~~~