Guide to Project for Supervisors ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 1 of 13 Assignment 2: SQL Overview The purpose of this task is to develop...

ITECH2004 Assignment2 SQL 2020.pdf


Guide to Project for Supervisors ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 1 of 13 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/). ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 2 of 13 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/ https://www.w3resource.com/sql-exercises/ ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 3 of 13 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 ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 4 of 13 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