60994 – Assignment 2: SQLOverviewThe purpose of this task is

SOLUTION AT Australian Expert Writers

Details:
Assignment 2: purpose of this task is to develop student’s skills in designing and implementing a relational database for a given case study.Timelines and ExpectationsPercentage Value of Task: 25% (100 marks)Due: Week 11 – Sunday at 11:59 pmMinimum time expectation: Preparation for this task will take approximately 20 hoursLearning Outcomes AssessedThe following course learning outcomes are assessed by completing this :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 from a database.Assessment DetailsBackgroundIn 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/).RequirementsQuestion 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 1From: 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: actoract_id | act_fname | act_lname | act_gender101 | James | Stewart | M102 | Deborah | Kerr | F103 | Peter | OToole | M104 | Robert | De Niro | M105 | F. Murray | Abraham | M106 | Harrison | Ford | M107 | Nicole | Kidman | F108 | Stephen | Baldwin | M109 | Jack | Nicholson | M110 | Mark | Wahlberg | M111 | Woody | Allen | M112 | Claire | Danes | F113 | Tim | Robbins | M114 | Kevin | Spacey | M115 | Kate | Winslet | F116 | Robin | Williams | M117 | Jon | Voight | M118 | Ewan | McGregor | M119 | Christian | Bale | M120 | Maggie | Gyllenhaal | F121 | Dev | Patel | M122 | Sigourney | Weaver | F123 | David | Aston | M124 | Ali | Astin | FTable: movie_castact_id | mov_id | role101 | 901 | John Scottie Ferguson102 | 902 | Miss Giddens103 | 903 | T.E. Lawrence104 | 904 | Michael105 | 905 | Antonio Salieri106 | 906 | Rick Deckard107 | 907 | Alice Harford108 | 908 | McManus110 | 910 | Eddie Adams111 | 911 | Alvy Singer112 | 912 | San113 | 913 | Andy Dufresne114 | 914 | Lester Burnham115 | 915 | Rose DeWitt Bukater116 | 916 | Sean Maguire117 | 917 | Ed118 | 918 | Renton120 | 920 | Elizabeth Darko121 | 921 | Older Jamal122 | 922 | Ripley114 | 923 | Bobby Darin109 | 909 | J.J. Gittes119 | 919 | Alfred BordenTable: moviemov_id | mov_title | mov_year | mov_time | mov_lang | mov_dt_rel | mov_rel_country901 | Vertigo | 1958 | 128 | English | 1958-08-24 | UK902 | The Innocents | 1961 | 100 | English | 1962-02-19 | SW903 | Lawrence of Arabia | 1962 | 216 | English | 1962-12-11 | UK904 | The Deer Hunter | 1978 | 183 | English | 1979-03-08 | UK905 | Amadeus | 1984 | 160 | English | 1985-01-07 | UK906 | Blade Runner | 1982 | 117 | English | 1982-09-09 | UK907 | Eyes Wide Shut | 1999 | 159 | English | | UK908 | The Usual Suspects | 1995 | 106 | English | 1995-08-25 | UK909 | Chinatown | 1974 | 130 | English | 1974-08-09 | UK910 | Boogie Nights | 1997 | 155 | English | 1998-02-16 | UK911 | Annie Hall | 1977 | 93 | English | 1977-04-20 | USA912 | Princess Mononoke | 1997 | 134 | Japanese | 2001-10-19 | UK913 | The Shawshank Redemption | 1994 | 142 | English | 1995-02-17 | UK914 | American Beauty | 1999 | 122 | English | | UK915 | Titanic | 1997 | 194 | English | 1998-01-23 | UK916 | Good Will Hunting | 1997 | 126 | English | 1998-06-03 | UK917 | Deliverance | 1972 | 109 | English | 1982-10-05 | UK918 | Trainspotting | 1996 | 94 | English | 1996-02-23 | UK919 | The Prestige | 2006 | 130 | English | 2006-11-10 | UK920 | Donnie Darko | 2001 | 113 | English | | UK921 | Slumdog Millionaire | 2008 | 120 | English | 2009-01-09 | UK922 | Aliens | 1986 | 137 | English | 1986-08-29 | UK923 | Beyond the Sea | 2004 | 118 | English | 2004-11-26 | UK924 | Avatar | 2009 | 162 | English | 2009-12-17 | UK926 | Seven Samurai | 1954 | 207 | Japanese | 1954-04-26 | JP927 | Spirited Away | 2001 | 125 | Japanese | 2003-09-12 | UK928 | Back to the Future | 1985 | 116 | English | 1985-12-04 | UK925 | Braveheart | 1995 | 178 | English | 1995-09-08 | UKTable: directordir_id | dir_fname | dir_lname201 | Fred | Caravanhitch202 | Jackie | Claytonburry203 | Greene | Lyon204 | Miguel | Camino205 | George | Forman206 | Antartic | Scott207 | Stanlee | Carbrick208 | Bryon | Sanger209 | Roman | Polanski210 | Paul | Thomas Anderson211 | Woody | Allen212 | Hayao | Miyazaki213 | Frank | Darabont214 | Sam | Mendes215 | James | Cameron216 | Gus | Van Sant217 | John | Boorman218 | Danny | Boyle219 | Christopher | Nolan220 | Richard | Kelly221 | Kevin | Spacey222 | Andrei | Tarkovsky223 | Peter | JacksonTable: movie_directiondir_id | mov_id201 | 901202 | 902203 | 903204 | 904205 | 905206 | 906207 | 907208 | 908209 | 909210 | 910211 | 911212 | 912213 | 913214 | 914215 | 915216 | 916217 | 917218 | 918219 | 919220 | 920218 | 921215 | 922221 | 923Table: genresgen_id | gen_title1001 | Action1002 | Adventure1003 | Animation1004 | Biography1005 | Comedy1006 | Crime1007 | Drama1008 | Horror1009 | Music1010 | Mystery1011 | Romance1012 | Thriller1013 | WarTable: movie_genresmov_id | gen_id922 | 1001917 | 1002903 | 1002912 | 1003911 | 1005908 | 1006913 | 1006926 | 1007928 | 1007918 | 1007921 | 1007902 | 1008923 | 1009907 | 1010927 | 1010901 | 1010914 | 1011906 | 1012904 | 1013Table: ratingmov_id | rev_id | rev_stars | num_o_ratings901 | 9001 | 8.40 | 263575902 | 9002 | 7.90 | 20207903 | 9003 | 8.30 | 202778906 | 9005 | 8.20 | 484746924 | 9006 | 7.30 |908 | 9007 | 8.60 | 779489909 | 9008 | | 227235910 | 9009 | 3.00 | 195961911 | 9010 | 8.10 | 203875912 | 9011 | 8.40 |914 | 9013 | 7.00 | 862618915 | 9001 | 7.70 | 830095916 | 9014 | 4.00 | 642132925 | 9015 | 7.70 | 81328918 | 9016 | | 580301920 | 9017 | 8.10 | 609451921 | 9018 | 8.00 | 667758922 | 9019 | 8.40 | 511613923 | 9020 | 6.70 | 13091Table: reviewerrev_id | rev_name9001 | Righty Sock9002 | Jack Malvern9003 | Flagrant Baronessa9004 | Alec Shaw9005 |9006 | Victor Woeltjen9007 | Simon Wright9008 | Neal Wruck9009 | Paul Monks9010 | Mike Salvati9011 |9012 | Wesley S. Walker9013 | Sasha Goldshtein9014 | Josh Cates9015 | Krug Stillo9016 | Scott LeBrun9017 | Hannah Steele9018 | Vincent Cadena9019 | Brandt Sponseller9020 | Richard AdamsFor 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 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 | dob201 | Alfred | Hitchcock | 1964-08-12202 | Jack | Clayton | 1955-11-22203 | David | Lean | 1969-05-15204 | Michael | Cimino | 1959-11-08205 | Milos | Forman | 1988-09-12206 | Ridley | Scott | 1956-02-07207 | Stanley | Kubrick | 1967-11-08208 | Bryan | Singer | 1960-02-28Question 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 requirements1. Prepare a style report which contains the following:a). Each query and associated outputb). Full APA referencing of any resources usedA suggested structure for the above report is provided. You can use it as a guideline for presenting your business report. For assistance in report 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_studentIDTitle pageExecutive summaryTable of contents1.0 Introduction2.0 SQL Statements Report2.1 Question 12.2 Question 22.3 Question 32.4 Question 4…2.18 Question 183.0 Conclusion4.0 Bibliography2. A copy of the database file you are required to create for questions 1–11, file name:itech2004_2_queries_yourname_studentID.txtAcademic PresentationAssignment 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.pdfSubmissionThe 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, 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/RubricAssessment CriteriaMarking ScalePoor Excellent1 …………………. 5Presentation and Referencing• Overall presentation of the report (including title page) 0Relational Schema (Question 1)• All tables included• Data types correct, withPrimary and foreign keys indicated and referenced correctly 00Data Import (Question 2)• All data imported correctly 0SQL 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 0Total Mark [100 marks] 100.0Total [25%] 25.0

Order from Australian Expert Writers
Best Australian Academic Writers

QUALITY: 100% ORIGINAL NO PLAGIARISM – CUSTOM PAPER