logo Use CA10RAM to get 10%* Discount.
Order Nowlogo
(5/5)

Using MySQL Workbench, reverse engineer the tiny_video schema to create the ERD.

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

Instructions:

Use the ‘Tiny Video’ SQL script file found below

Paste the contents of this script into a query tab of MySQL Workbench and execute it. This will create the schema named tiny_video and populate it for you. After running the script, you no longer need it.

Using MySQL SELECT commands answer the questions listed below using the tiny_video schema. Read each question carefully! 

Copy/paste your SQL query, and the output for each problem, into the document and submit 

Assignment Details:

1. (1 point) Using MySQL Workbench, reverse engineer the tiny_video schema to create the ERD. Add a text box to display your name within the ERD. Include a screenshot of your results here. This ERD is useful to help you determine which tables to use for the remainder of the assignment.

 

1. (4 points) Determine your startup investment:  write a query to return title, year_released, cost, genre, copies and purchase_price. The results should be sorted by the purchase date from newest to oldest.

 

1. (4 points) Inventory considerations: write a query to display video id, title, cost and rental id. Only display videos that were never rented or never returned from their prior rental.

 

1. (5 points) Write a query to display membership name (concatenate the first name and last name with a space between them into a single column) called member_name. Add two additional columns:

 

1.

 . rental_count: to display how many times each member has rented

a. videos_rented: to display the number of videos each member has rented

 

1. (5 points) Write a query to display membership id, first name and last name for any member who has no rental history.

 

1. (6 points) Using a subquery, write a query to display membership id, first name and last name for any member who has rented multiple times. Does this match your results from Question 4?

Script is below

 

 

 

 

/* Database Systems, 9th Ed., Coronel/MOrris/Rob */

/* Type of SQL : MySQL */

 

CREATE SCHEMA IF NOT EXISTS TINY_VIDEO;

 

USE TINY_VIDEO;

 

DROP TABLE IF EXISTS detail_rental;

DROP TABLE IF EXISTS rental;

DROP TABLE IF EXISTS membership;

DROP TABLE IF EXISTS video;

DROP TABLE IF EXISTS movie;

DROP TABLE IF EXISTS price;

 

/*Create table price*/

CREATE TABLE price

(price_id        INTEGER     PRIMARY KEY AUTO_INCREMENT,

 description     VARCHAR(20) NOT NULL,

 rental_fee      DECIMAL(5,2),

 daily_late_fee  DECIMAL(5,2));

 

 

/*Insert data into price*/

INSERT INTO price VALUES(1,'Standard',2.5,1);

INSERT INTO price VALUES(2,'New Release',4.0,3);

INSERT INTO price VALUES(3,'Discount',2.0,1);

INSERT INTO price VALUES(4,'Weekly Special',1.5,.5);

 

 

/*Create table movie*/

CREATE TABLE movie

(movie_id       INTEGER     PRIMARY KEY AUTO_INCREMENT,

 title          VARCHAR(75) NOT NULL,

 year_released  INTEGER,

 cost           DECIMAL(5,2),

 genre          VARCHAR(50),

 price_id       INTEGER,

 FOREIGN KEY(price_id) REFERENCES price(price_id));

 

/*Insert data into movie*/

INSERT INTO movie VALUES(1234,'The Cesar Family Christmas',2007,39.95,'FAMILY',2);

INSERT INTO movie VALUES(1235,'Smokey Mountain Wildlife',2004,59.95,'ACTION',3);

INSERT INTO movie VALUES(1236,'Richard Goodhope',2008,59.95,'DRAMA',2);

INSERT INTO movie VALUES(1237,'Beatnik Fever',2007,29.95,'COMEDY',2);

INSERT INTO movie VALUES(1238,'Constant Companion',2008,89.95,'DRAMA',NULL);

INSERT INTO movie VALUES(1239,'Where Hope Dies',1998,25.49,'DRAMA',3);

INSERT INTO movie VALUES(1245,'Time to Burn',2006,45.49,'ACTION',3);

INSERT INTO movie VALUES(1246,'What He Doesn''t Know',2006,58.29,'COMEDY',1);

 

 

/*Create table video*/

CREATE TABLE video

(video_id       INTEGER PRIMARY KEY AUTO_INCREMENT,

 purchase_date  DATE,

 movie_id       INTEGER,

 

 

(5/5)
Attachments:

Expert's Answer

402 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

402 Times Downloaded

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
Um e HaniScience

512 Answers

Hire Me
expert
Muhammad Ali HaiderFinance

750 Answers

Hire Me
expert
Husnain SaeedComputer science

656 Answers

Hire Me
expert
Atharva PatilComputer science

712 Answers

Hire Me

Get Free Quote!

361 Experts Online