1. In the Week 3: Reading - Chapter 8, go to 8-9e Cases in the Chapter Review section at the end of the module and review the EliteVideo case from the perspective of a database administrator assigned to build this database. Select one of the entities shown in the ERD for the EliteVideo database in Figure P8.47 (reproduced below). What data types would you use for the attributes in this entity? How would you code a CREATE TABLE statement to implement this entity as a table in the database?

2. Continue examining the EliteVideo case at the end of the Chapter 8 8 reading, but this time from the perspective of a manager in the organization. What is one example of a report from the database that a manager might want to receive? What data items would appear on the report, and what attributes and entities would these data items come from? How would you code a SQL SELECT query to retrieve this information?

Here's Figure P8.47 which shows the ERD for this database:

This image shows Entites (tables), Attributes, Primary and Foreign Keys, and relationships between six tables.

There are tables listed. MEMBERSHIP, RENTAL, DETAILRENTAL, PRICE, MOVIE, and VIDEO.

MEMBERSHIP table has a primary key, MEM_NUM, and seven additional attributes: MEM_FNAME, MEM_LNAME, MEM_STREET, MEM_CITY, MEM-STATE, MEM_ZIP, AND MEM_BALANCE. This table is related to the Rental table with a 1 to many relationship.

RENTAL table has a Primary Key of RENT_NUM with a Foreign Key of MEM_NUM and an attribute of Rent_Date. RENTAL is related to MEMBERSHIP with Many to 1 relationship and DETAILRENTAL as a 1 to Many relationship.

DETAILRENTAL table has a Primary Key of RENT_NUM with Foreign Key of VID_NUM, and has 4 additional attributes: DEATIL_FEE, DETAIL_DUEDATE, DETAIL_RETURNDATE, AND DETAIL_DAILYLATEFEE. It is related to RENTAL with a many to 1 relationship and VIDEO with a Many to One relationship.

VIDEO TABLE has a Primary Key of VID_NUM and a Foreign Key of MOVI_NUM plus one additional attribute of VID_INDATE.

MOVIE table has a Primary Key of MOVIE_NUM and a Foreign Key of PRICE_CODE. It also has four attributes of MOVIE-TITLE, MOVIE-YEAR, MOVIE-COST, MOVIE-GENRE. It is related to VIDEO table with a 1 to many relationship and to PRICE with a Many to more than one relationship.

PRICE table has a Primary Key of PRICE_CODE, and three additional attributes of PRICE_DESCRIPTION, PRICE-RENTFEE, AND PRICE-DAILYLATEFEE.

View Instructor posts