Q1. Maintain and Manage Database Server EliteVideo is startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo 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 . 6) Update the PRICE table to place the values shown in the following table in the PRICE_RENTDAYS attribute. 7) Create a trigger named trg_late_return that will write the correct value to DETAIL DAYSLATE in the DETAILRENTAL table whenever a video is returned. The trigger should execute as a BEFORE trigger when the DETAIL RETURNDATE or DETAIL DUEDATE attributes are updated. The trigger should satisfy the following conditions: a. If the return date is null, then the days late should also be null. b. If the return date is not null, then the days late should determine if the video is returned late. c. If the return date is noon of the day after the due date or earlier, then the video is not considered late, and the days late should have a value of zero (0). d. If the return date is past noon of the day after the due date, then the video is considered late, so the number of days late must be calculated and stored. Q2. Database Security: Install MySQL Community Edition on your computer 1) Create a Create new user in EliteVideo database. 2) Define user roles and permissions to control access to the database 3) Test the access controls by logging in as different users and trying to perform various actions on the database. Q3. Backup and Recovery: 1) You are requested to choose a backup technique for the business database. In a short report, describe the following: a. Full backup technique and how it works. b. Illustrate through sereenshots a Full backup to database EliteVideo c. Advantages and disadvantages of full backup d. Justify why full backup is suitable for the EliteVideo database. Report Submission: - The Due date for submission is 15/3/2023. - Submit the report of the Assignment through your Student Portal. Q1. Maintain and Manage Database Server EliteVideo is startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo 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 . 6) Update the PRICE table to place the values shown in the following table in the PRICE_RENTDAYS attribute. 7) Create a trigger named trg_late_return that will write the correct value to DETAIL DAYSLATE in the DETAILRENTAL table whenever a video is returned. The trigger should execute as a BEFORE trigger when the DETAIL RETURNDATE or DETAIL DUEDATE attributes are updated. The trigger should satisfy the following conditions: a. If the return date is null, then the days late should also be null. b. If the return date is not null, then the days late should determine if the video is returned late. c. If the return date is noon of the day after the due date or earlier, then the video is not considered late, and the days late should have a value of zero (0). d. If the return date is past noon of the day after the due date, then the video is considered late, so the number of days late must be calculated and stored. Q2. Database Security: Install MySQL Community Edition on your computer 1) Create a Create new user in EliteVideo database. 2) Define user roles and permissions to control access to the database 3) Test the access controls by logging in as different users and trying to perform various actions on the database. Q3. Backup and Recovery: 1) You are requested to choose a backup technique for the business database. In a short report, describe the following: a. Full backup technique and how it works. b. Illustrate through sereenshots a Full backup to database EliteVideo c. Advantages and disadvantages of full backup d. Justify why full backup is suitable for the EliteVideo database. Report Submission: - The Due date for submission is 15/3/2023. - Submit the report of the Assignment through your Student Portal