Context:

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.

Instructions:

You need to create the database tables as per below entities and complete the tasks listed below:

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 firstname 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.