Answered step by step
Verified Expert Solution
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
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started