|Subject Code and Title||MIS602 Data Modelling & Database Design|
|Assessment||Two – Database programming evaluation practical|
|Length||See below for details|
|Learning Outcomes||b, c|
|Total Marks||35 Marks|
The MIS602 Data Modelling & Database Design subject is designed for you to progressively add to your understanding of data and database management and its relevance with in business context. It also introduces you to some of the key features of database management system and designing database systems that will feature in later modules of this topic. In order for you to do well in this subject, it is imperative that you undertake all of the learning activities in the modules. The learning activities are presented as a way of scaffolding your learning so that you can attempt the building blocks of the assessments and be in a safe environment to fail and to learn from them. Therefore, doing your learning activities and seeking feedback from them from peers and from the learning facilitator is the single best way of preparing for doing well in this assessment.
You are required to create the database tables as per the ERD below, and then generate the SQL tasks listed in the table.
|1003||Beyond the Sea||2004||118||3||3007||2006|
|Task #||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|
|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|
|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.
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.
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).
Note: all non-submission or late submissions will be treated in accordance with the late assessment policy of the university.
for solution, connect with our online professionals.