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

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.

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.

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd