Reference no: EM132568478
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