Abc Assignment Help

MIS602 Database Programming Evaluation Practical Assessment 2 Answer

ASSESSMENT BRIEF
Subject Code and TitleMIS602 Data Modelling & Database Design
AssessmentTwo – Database programming evaluation practical
Individual/GroupIndividual
LengthSee below for details
Learning Outcomesb, c
Weighting35%
Total Marks35 Marks

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 are required to create the database tables as per the ERD below, and then generate the SQL tasks listed in the table.

Movie

Movie_IdMovie_NameReleased_YearMovie_TimePlanned_BudgetDirector_IdGenre_Id
1001Finding Nemo20031073.530022009
1002The Incredibles20041160.530032009
1003Beyond the Sea2004118330072006
1004Avatar20091161030052008

GenreDirector

Task #Task Description
Task 1Create three tables with relevant keys as suggested in the above diagram and
the sample data tables.
Task 2Insert 10 records to Movies table.
Task 3Insert 5 records to Director table.
Task 4Write a query to display all the information about the Movies.
Task 5Write a query to display the Movie_Names of all the movies.
Task 6Write a query to display all the Movie_Names and their Planned_Budget


Task 7Write 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 8Write 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 9Write 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 10Write 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 11Write a query to display all the unique Director_Fname.
Task 12Write a query to display Movie_Name, Movie_Duration for all movies released
in 2001.
Task 13Write 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 14Write a query to count the total number of movies in the Movies table.
Task 15Write a query to display the Director_Name and the total number of Movies
produced by each Director in ascending order.
Task 16Write 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 17Write 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 18Write a query to display each Genre_Class and the total number of movies
belonging to each category.
Task 19Write a query to display all the Movie Names with ‘Animated’ as the Genre.
Task 20Write 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 21Write a query to display all the Genre_Class with no Movie name associated.
Task 22Write a query to display the movies with minimum and maximum
Planned_Budget.
Task 23Write a query to display all the Movie_Names and their director names.


Task 24Write 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.

Note: all non-submission or late submissions will be treated in accordance with the late assessment policy of the university.

Answer

for solution, connect with our online professionals. 

Customer Testimonials