Determine the functional dependencies

Assignment Help Database Management System

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

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.

Reference no: EM132221743

Questions Cloud

Display the names and salaries of employees : Find the mean(averages) salary and display the names and salaries of employees who earn within a range of $5000 from the mean.

How many mothers are examined by the clinic : Use the average waiting time and maximum waiting time to advise managementon how to improve the antenatal service clinic.

Compute the predicted break-even point in dollar sales : Compute the predicted break-even point in dollar sales for 2020 assuming the machine is installed and there is no change in the unit selling price

Create a class diagram of the initial code : Build the solution and inspect the output. Spend some time becoming familiar with the code before you do anything else.

Determine the functional dependencies : Write SQL queries (do not use QBE) for the following questions and execute the queries after creating the above database on MS Access.

Why are these defenses not used extensively : CP5603 : What are the defenses to protect against SQL injection attacks, XML injection attacks, and XSS?

Compute the cost of goods manufactured : Using the following data from both Garcon Company and Pepper Company for the year ended December 31, 2019, compute (1) the cost of goods manufactured

Design the core classes for your simple game : Design the core classes for your simple game. Do this using a UML class diagram.

Determine and list your entities : Determine and list your entities. Then create relationship sentence pairs between those entities that are related.

User Account

All Pages