Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Assignment 2 Q1. Maintain and Manage Database Server Elite Video is startup company providing concierge DVD kiosk service in upscale neighborhoods. Elite Video can

image text in transcribed

Assignment 2 Q1. Maintain and Manage Database Server Elite Video is startup company providing concierge DVD kiosk service in upscale neighborhoods. Elite Video can own several copies (VIDEO) of each movie (MOVIE). For example, a kiosk may have 10 copies of the movie Twist in the Wind. In the database, Twist in 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 an M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided. 1) Use MySQL to create the table structures for the entities shown in the above figure. The structures should contain the attributes specified in the ERD. Use data types that are 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. Insert dummy data into each table (five records at least in each table). 2) Alter the MEMBERSHIP table to include a derived attribute named MEMBER AGE to store integers of up to three digits. The attribute should accept null values. 3) Alter the VIDEO table to include an attribute named VID STATUS to store character data up to four characters long. The attribute should not accept null values. The attribute should have a constraint to enforce the domain ("IN". "OUT", and "LOST") and have a default value of "IN". 4) Update the VID STATUS attribute of the VIDEO table to set the VID STATUS to "OUT" for all videos that have a null value in the DETAIL RETURNDATE attribute of the DETAILRENTAL table. 5) Alter the PRICE table to include an attribute named PRICE RENTDAYS to store integers of up to two digits. The attribute should not accept null values, and it should have a default value of 3.1 6) Update the PRICE table to place the values shown in the following table in the PRICE RENTDAYS attribute.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Intermediate Accounting

Authors: James D. Stice, Earl K. Stice, Fred Skousen

16th Edition

324376375, 0324375743I, 978-0324376371, 9780324375749, 978-0324312140

More Books

Students also viewed these Accounting questions