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,

 

 

Attachments:

Expert's Answer

522 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