SQL

a movie rental store. It needs a database system to track the rental of movies to its members. RentalAVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Gone with the Wind”. “Gone with the Wind” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete RDM is provided below.

RentalAVideo ERD (Figure 1)

⦁ Write the SQL code to create the table structures for the entities shown in Figure 1. The structures should contain the specified attributes. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD.

⦁ The following tables provide a very small portion of the data that will be kept in the database. This data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in problem 1.

Note: For each table, you can write insert statements for 2 rows. If it works, you can copy these data to Excel file. Then, you can import the Excel file to MS Access using the method provided in Access tutorial slide # 10. But please make sure to create primary key for each table and relationships between tables shown in Figure 1 after you import data

MEMBERSHIP
Mem_ Num Mem_ Fname Mem_ Lname Mem_Street Mem_City Mem_ State Mem_Zip Mem_ Balance
102 Tami Dawson 2632 Takli Circle Norene TN 37136 11
103 Curt Knight 4025 Cornell Court Flatgap KY 41219 6
104 Jamal Melendez 788 East 145th Avenue Quebeck TN 38579 0

105 Iva Mcclain 6045 Musket Ball Circle Summit KY 42783 15
106 Miranda Parks 4469 Maxwell Place Germantown TN 38183 0
107 Rosario Elliott 7578 Danner Avenue Columbia TN 38402 5
108 Mattie Guy 4390 Evergreen Street Lily KY 40740 0
109 Clint Ochoa 1711 Elm Street Greeneville TN 37745 10
110 Lewis Rosales 4524 Southwind Circle Counce TN 38326 0
111 Stacy Mann 2789 East Cook Avenue Murfreesboro TN 37132 8
112 Luis Trujillo 7267 Melvin Avenue Heiskell TN 37754 3
113 Minnie Gonzales 6430 Vasili Drive Williston TN 38076 0

RENTAL
Rent_Num Rent_Date Mem_Num
1001 01-MAR-09 103
1002 01-MAR-09 105
1003 02-MAR-09 102
1004 02-MAR-09 110
1005 02-MAR-09 111
1006 02-MAR-09 107
1007 02-MAR-09 104
1008 03-MAR-09 105
1009 03-MAR-09 111

DETAILRENTAL
Rent_Num Vid_Num Detail_Fee Detail_Duedate Detail_Returndate Detail_Dailylatefee
1001 34342 2 04-MAR-09 02-MAR-09 1
1001 61353 2 04-MAR-09 03-MAR-09 1
1002 59237 3.5 04-MAR-09 04-MAR-09 3
1003 54325 3.5 04-MAR-09 09-MAR-09 3
1003 61369 2 06-MAR-09 09-MAR-09 1
1003 61388 0 06-MAR-09 09-MAR-09 1
1004 44392 3.5 05-MAR-09 07-MAR-09 3
1004 34367 3.5 05-MAR-09 07-MAR-09 3
1004 34341 2 07-MAR-09 07-MAR-09 1
1005 34342 2 07-MAR-09 05-MAR-09 1
1005 44397 3.5 05-MAR-09 05-MAR-09 3
1006 34366 3.5 05-MAR-09 04-MAR-09 3
1006 61367 2 07-MAR-09 1
1007 34368 3.5 05-MAR-09 3
1008 34369 3.5 05-MAR-09 05-MAR-09 3
1009 54324 3.5 05-MAR-09 3
1001 34366 3.5 04-MAR-09 02-MAR-09 3

 

VIDEO
Vid_Num Vid_Indate Movie_Num
54321 18-JUN-08 1234
54324 18-JUN-08 1234
54325 18-JUN-08 1234
34341 22-JAN-07 1235
34342 22-JAN-07 1235
34366 02-MAR-09 1236
34367 02-MAR-09 1236
34368 02-MAR-09 1236
34369 02-MAR-09 1236
44392 21-OCT-08 1237
44397 21-OCT-08 1237
59237 14-FEB-09 1237
61388 25-JAN-07 1239
61353 28-JAN-06 1245
61354 28-JAN-06 1245
61367 30-JUL-08 1246
61369 30-JUL-08 1246

MOVIE
Movie_Num Movie_Name Movie_Year Movie_Cost Movie_Genre Price_Code
1234 The Cesar Family Christmas 2007 39.95 FAMILY 2
1235 Smokey Mountain Wildlife 2004 59.95 ACTION 1
1236 Richard Goodhope 2008 59.95 DRAMA 2
1237 Beatnik Fever 2007 29.95 COMEDY 2
1238 Constant Companion 2008 89.95 DRAMA 2
1239 Where Hope Dies 1998 25.49 DRAMA 3
1245 Time to Burn 2005 45.49 ACTION 1
1246 What He Doesn’t Know 2006 58.29 COMEDY 1

PRICE
Price_Code Price_Description Price_Rentfee Price_Dailylatefee
1 Standard 2 1
2 New Release 3.5 3
3 Discount 1.5 1
4 Weekly Special 1 .5

For questions 3– 31, use the tables that were created in Problem 1 and the data that was loaded into those tables in Problem 2.

⦁ Write the SQL command to change the movie year for movie number 1245 to 2006.

⦁ Write the SQL command to change the price code for all Action movies to price code 3.

⦁ Write a single SQL command to increase all price rental fee values by $0.50.

⦁ Write a query to display the movie title, movie year, and movie genre for all movies (result shown in Figure 2).

Figure 2 All Movies

⦁ Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending order (result shown in Figure 3)

Figure 3 Movies by year

⦁ Write a query to display the movie title, movie year, and movie genre for all movies sorted by movie genre in ascending order, then sorted by movie year in descending order within genre (result shown in Figure 4).

Figure 4 Movies with multicolumn sort

⦁ Write a query to display the movie number, movie title, and price code for all movies with a title that starts with the letter “R” (result shown in Figure 5).

Figure 5 Movies starting with R

⦁ Write a query to display the movie title, movie year, and movie cost for all movies that contain the word “hope” anywhere in the title. Sort the results in ascending order by title (result shown in figure 6).

Figure 6 Movies with “Hope” in the title

⦁ Write a query to display the movie title, movie year, and movie genre for all action movies (result shown in Figure 7).

Figure 7 Action movies

⦁ Write a query to display the movie number, movie title, and movie cost for all movies with a cost greater than $40 (result shown in Figure 8).

P8 Movies costing less than $40

⦁ Write a query to display the movie number, movie title, movie cost, and movie genre for movies that are either action or comedy movies or movies that have a cost that is less than $50. Sort the results in ascending order by genre. (Result shown in Figure 9.)
Figure 9 Action or comedy movies costing less than $50

⦁ Write a query to display the movie genre and the number of movies in each genre (result shown in Figure 10).

Figure 10 Number of movies in genre

⦁ Write a query to display the average cost of all of the movies (result shown in Figure 11).

Figure 11 Average movie cost

⦁ Write a query to display the movie genre and average cost of movies in each genre (result shown in Figure 12).

Figure 12 Average movie cost by genre

⦁ Write a query to display the movie title, movie genre, price description, and price rental fee for all movies with a price code (result shown in Figure 13).

 

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Quality Custom Writers has assembled a team of highly skilled writers with diverse experience in the online writing circles. Our aim is to become a one stop shop for all your Academic/ online writing. Check out below our amazing service!

Essays

Essay Writing Services

At Quality Custom Writers, we prioritize on all aspects that creates a good grade such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines. The principal purpose of essay writing is to present the author's evaluation concerning a singular subject about which they have made. Since Professionalism is the mother of every success, try our team of experienced writers in helping you complete your essays and other assignments.

Admissions

Admission Papers

You have been trying to join that prestigious institution you long yearned for, but the hurdle of an admission essay has become a stumbling block. We have your back, with our proven team that has gained invaluable experience over time, your chance of joining that institution is now! Just let us work on that essay.How do you write an admission essay? How do you begin the essay? For answers, try Quality Custom Writers Now!

Editing

Editing and Proofreading

Regardless of whether you're pleased with your composing abilities, it's never an impractical notion to have a second eye go through your work. The best editing services leaves no mistake untouched. We recognize the stuff needed to polish up a writing; as a component of our editing and proofreading, we'll change and refine your write up to guarantee it's amazing, and blunder free. Our group of expert editors will examine your work, giving an impeccable touch of English while ensuring your punctuation and sentence structures are top-notch.

Coursework

Technical papers

We pride ourselves in having a team of clinical writers. The stringent and rigorous vetting process ensures that only the best persons for job. We hire qualified PhD and MA writers only. We equally offer our team of writers bonuses and incentives to motivate their working spirit in terms of delivering original, unique, and informative content. They are our resources drawn from diverse fields. Therefore your technical paper is in the right hands. Every paper is assessed and only the writers with the technical know-how in that field get to work on it.

Coursework

College Essay Writing

If all along you have been looking for a trustworthy college essay service provider that provides superb academic papers at reasonable prices, then be glad that you search has ended with us. We are your best choice! Get high-quality college essay writing from our magnificent team of knowledgeable and dedicated writers right now!

Coursework

Quality Assignment/Homework Help

We give the students premium quality assignments, without alarming them with plagiarism and referencing issues. We ensure that the assignments stick to the rules given by the tutors. We are specific about the deadlines you give us. We assure you that you will get your papers well in advance, knowing that you will review and return it if there are any changes, which should be incorporated.

Open chat