ITECH 2004 DATA MODELLING – Essaylink

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/
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

Table: movie_cast

ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 4 of 13

Table: movie

ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 5 of 13

Table: director

ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 6 of 13
Table: movie_direction

Table: genres

ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 7 of 13
Table: movie_genres

Table: rating

ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 8 of 13
Table: reviewer

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.
ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 9 of 13
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)

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.
ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 10 of 13
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.
ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 11 of 13
Documentation requirements

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

ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 12 of 13
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.
ITECH 2004 DATA MODELLING
CRICOS Provider No. 00103D ITECH2004 Assignment2 SQL 2020.docx Page 13 of 13
Marking Criteria/Rubric