Reference no: EM132221743
Assignment Description
Answer All Questions (Part A and Part B)
Part A:
Paste below the summary of your Moodle Assignment 1 quiz. The quiz will be open after week 5
Part B:
1. The snapshot of MovieDB database structure is given below. MovieDB is a database that keeps track of information about the movies, directors, and stars (i.e. actors and actresses) in a video store.
You are working as an IT specialist in this organisation and are required to extract information from this database by executing SQL queries according to the instructions given below.
The primary keys are marked with the key symbol in the following snapshot of MovieDB database shown in figure 1.
Snapshot of MovieDB database structure
![405_DB.jpg](https://secure.expertsmind.com/CMSImages/405_DB.jpg)
Description of the schema
actor - stores details of the actors in the database MovieDB
director - stores details of the directors
movie - keeps track of movie details
genres - stores movie categories, such as action, horror, adventure, drama, romance, comedy
a. First you need to create the above database structure on MS Access and populate all the tables with suitable data (at least 3 records per table) using the following SQL statement or data sheet view in MS Access.
INSERT into TableName
VALUES ("..","..",.....)
b. Write SQL queries (do not use QBE) for the following questions and execute the queries after creating the above database on MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions.
1. Displaydetails of all movie titles released after 2017. Your result set should be sorted on descending order of the mov_title.
2. List titles of all horror movies.
(Hint:Join movie and movie_genres and gen_title should be "horror")
3. Display movie title, year released of all movies and names of directors who directed them.
(Hint: you need to join 3 tables; movie, director, movie_direction tables)
4. List titles of movies directed by "James Cameron"
5. Create a new table named "JamesCameron_MOVIES" that includes titles of movies directed by "James Cameron.
6. Assume that you want to count how many movies were released after 2017. Write a query to find the number.
2. a. Determine the Functional Dependencies that exist in the following Ordertable.
Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo, ItemNum, Description, NumOrdered, QuotedPrice)
b. Normalize the above relation to 3rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.
3. a. A data warehouse (DW) is a collection of corporate information and data derived from operational systems and external data sources. Research more about this topic and find out benefits of using a Data Warehouse for business.
b. Explore the job listings advertised online and find out the skills you need to become a database developer.