Reference no: EM132683681

MIS602 Data Modelling & Database Design - Laureate International Universities

Two - Database programming evaluation practical

Instructions:
You are required to create the database tables as per the ERD below, and then generate the SQL tasks listed in the table.

Note: The following tables give you an idea of what sample data looks like:

Movie

Movie_Id	Movie_Name	Released_Year	Movie_Time	Planned_Budget	Director_Id	Genre_Id
1001	Finding Nemo	2003	107	3.5	3002	2009
1002	The Incredibles	2004	116	0.5	3003	2009
1003	Beyond the Sea	2004	118	3	3007	2006
1004	Avatar	2009	116	10	3005	2008

Genre

Genre_Id	Genre_Class
2001	Action
2002	Adventure
2003	Comedy
2007	Historical
2008	Science Fiction
2009	Cartoon

Director

Director_Id	Diector_Fame	Director_Fame
3001	John	Lasseter
3002	Pete	Docter
3003	Andrew	Stanton
3004	Brad	Bird
3005	James	Cameron
3006	Brenda	Chapman
3007	Kevin	Spacey

Task Description

Task 1 Create three tables with relevant keys as suggested in the above diagram and the sample data tables.

Task 2 Insert 10 records to Movies table.

Task 3 Insert 5 records to Director table.

Task 4 Write a query to display all the information about the Movies.

Task 5 Write a query to display the Movie_Names of all the movies.

Task 6 Write a query to display all the Movie_Names and their Planned_Budget

Task 7 Write a query to update the Movie_Time of ‘Finding Nemo' to 120 minutes. Make sure to insert some data that satisfy the criteria before executing the query.

Task 8 Write a query to display the Movie_Id, Movie_Name of all the movies with a planned budget above 3 million. Make sure to insert some data that satisfy the criteria before executing the query.

Task 9 Write a query to increase the planned budget of all Movies by 5% for all the movies with a Planned_Budget less than 5 million.

Task 10 Write a query to display the all the details of the Movies directed by Director_Id ‘3001'. Make sure to insert some data that satisfy the criteria before executing the query.

Task 11 Write a query to display all the unique Director_Fname.

Task 12 Write a query to display Movie_Name, Movie_Duration for all movies released in 2001.

Task 13 Write a query to display the list of all the Movie_Names with Movie_Time in the range of 100 - 200 minutes in Descending order.

Task 14 Write a query to count the total number of movies in the Movies table.

Task 15 Write a query to display the Director_Name and the total number of Movies produced by each Director in ascending order.

Task 16 Write a query to delete the record of the Director whose firstvname is ‘James' and last name is ‘Alex'. Make sure to insert some data that satisfy the criteria before executing the query.

Task 17 Write a query to display all the movies written by Director "James Cameroon". Make sure to insert some data that satisfy the criteria before executing the query.

Task 18 Write a query to display each Genre_Class and the total number of movies belonging to each category.

Task 19 Write a query to display all the Movie Names with ‘Animated' as the Genre.

Task 20 Write a query to display all the Movie Names and the first name of the director who directed the movie in ascending order by Director_Fname.

Task 21 Write a query to display all the Genre_Class with no Movie name associated.

Task 22 Write a query to display the movies with minimum and maximum Planned_Budget.

Task 23 Write a query to display all the Movie_Names and their director names.

Task 24 Write a query to display the details of the movie including, the Movie_Name, Planned_Budget, Movie_Genre and the Director details.

Submission Instructions

This assessment comprises of Part 1 and Part 2.

Part 1: This part is due on Week 8, Thursday 23:59 (Sydney time). It composes of the following two files, which must be submitted to Blackboard in a single zipped file.

1. SQL file (24 tasks) with outputs in a word document.
2. An Experience/Reflection report (Approximately 1000 words) in a word document summarizing your experience of doing this assignment.

Note: Following the submission of Part 1, you are also required to post the SQL file (Not experience report) onto the discussion forum. A thread has already been created in the discussion forum to post the SQL file, which you will be able to view on Week 8, Thursday 00:00 (Sydney time).

Part 2: This part is due on Week 8, Sunday 23:59 (Sydney time).
• From the pool of SQL files posted on the forum, you are required to write a short critique (Approximately 500 words) in a separate word document. Please DO NOT publish your critique onto discussion forum.
• Please note that your critique should provide constructive feedback highlighting the strengths and areas for improvement in the report. You should provide feedback on their usage of SQL commands and fulfil the following minimum requirements:
1. Comment on the overall usage of SQL statements and commands used.
2. Provide constructive criticism on how the author can improve their understanding of SQL statements.
3. Provide some useful readings the author may pursue to help in developing the understanding of SQL statements.
• Submit the critique via Blackboard by following the same submission link as in Part 1.

Attachment:- Database programming evaluation practical.rar