The following Entity Relationship Diagram (ERD) captures the data that needs to be stored for Elite...

 

Transcribed Image Text:

The following Entity Relationship Diagram (ERD) captures the data that needs to be stored for Elite Video, a small startup company providing concierge DVD kiosk service. Elite Video can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie "The Cesar Family Christmas". "The Cesar Family Christmas" would be one MOVIE and each copy would be a VIDEO. Each movie has a price code which determines the fee for renting that movie and the daily fee for returning it late. 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 many-to-many relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table which breaks this many-to-many relationship into two 1-to-many relationships. MEMBERSHIP PK MEM NUM MEM_FNAME MEM_LNAME MEM_STREET MEM_CITY MEM_STATE MEM_ZIP MEM_BALANCE PRICE PK PRICE CODE -H--O PRICE_DESCRIPTION PRICE_RENTFEE PRICE_DAILYLATEFEE PK RENTAL RENT NUM RENT_DATE FK1 MEM_NUM HO----O HI MOVIE PK MOVIE NUM MOVIE_TITLE MOVIE_YEAR MOVIE_COST MOVIE_GENRE FK1 PRICE_CODE DETAILRENTAL RENT NUM PK,FK1 PK,FK2 VID NUM FH-O DETAIL_FEE DETAIL DUEDATE DETAIL_RETURNDATE DETAIL_DAILYLATEFEE PK VIDEO VID_NUM VID_INDATE FK1 MOVIE_NUM Problem #1 (30pts): Translate the above ERD into a relational schema (a set of tables) and implement it in Access. Make sure you enforce all PK and FK constraints illustrated in the above ERD. Recall that in Access, FK constraints are enforced by creating relationships between tables and enforcing referential integrity for each relationship. Choose appropriate data types for your fields such that they can store the data illustrated below and insert this data into your table. The following Entity Relationship Diagram (ERD) captures the data that needs to be stored for Elite Video, a small startup company providing concierge DVD kiosk service. Elite Video can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie "The Cesar Family Christmas". "The Cesar Family Christmas" would be one MOVIE and each copy would be a VIDEO. Each movie has a price code which determines the fee for renting that movie and the daily fee for returning it late. 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 many-to-many relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table which breaks this many-to-many relationship into two 1-to-many relationships. MEMBERSHIP PK MEM NUM MEM_FNAME MEM_LNAME MEM_STREET MEM_CITY MEM_STATE MEM_ZIP MEM_BALANCE PRICE PK PRICE CODE -H--O PRICE_DESCRIPTION PRICE_RENTFEE PRICE_DAILYLATEFEE PK RENTAL RENT NUM RENT_DATE FK1 MEM_NUM HO----O HI MOVIE PK MOVIE NUM MOVIE_TITLE MOVIE_YEAR MOVIE_COST MOVIE_GENRE FK1 PRICE_CODE DETAILRENTAL RENT NUM PK,FK1 PK,FK2 VID NUM FH-O DETAIL_FEE DETAIL DUEDATE DETAIL_RETURNDATE DETAIL_DAILYLATEFEE PK VIDEO VID_NUM VID_INDATE FK1 MOVIE_NUM Problem #1 (30pts): Translate the above ERD into a relational schema (a set of tables) and implement it in Access. Make sure you enforce all PK and FK constraints illustrated in the above ERD. Recall that in Access, FK constraints are enforced by creating relationships between tables and enforcing referential integrity for each relationship. Choose appropriate data types for your fields such that they can store the data illustrated below and insert this data into your table.

Answer rating: 100% (QA)

Ans Step 1 CREATE SCHEMA elitevideo Step 2 CREATE TABLE elitevideomembership memnum INT NOT NULLmemfname VARCHAR45 NULLmemlname VARCHAR45 NULLmemsreet ... View the full answer