I need a formal written description of the tables and relationships between the

Question

I need a formal written description of the tables and relationships between the tables as well as a list of any additional assumptions.

I need the relational schema for the various forms.

Blockbuster Video Rental is thinking about entering the DVD Kiosk business to complete with RedBox. The concierge DVD kiosk service is usually placed near commercial chain stores such as CVS, RiteAid and Walgreens. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie "Remember the Titans "Remember the Titans" 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). Avideo can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. Using the model tool in MySQL workbench, construct and adjust the ERD listed below. Once you have constructed the E-R model, DETAILRENTAL MEMBERSHIP RENTAL PK,FK1 RENT NUM PK MEM NUM PK,FK2 VID NUM PK RENT NUM MEM FNAME DETAIL FEE RENT DATE MEM LNAME DETAIL DUEDATE MEM STREET FK1 MEM NUM DETAIL RETURNDATE MEM CITY DETAIL DAILYLATEFEE MEM STATE MEM ZIP MEM BALANCE MOVIE VIDEO EPK MOVIE NUM PK VID NUM PRICE MOVIE TITLE VID INDATE PK PRICE CODE MOVIE YEAR FK 1 MOVIE NUM MOVIE COST PRICE DESCRIPTION MOVIE GENRE PRICE RENTFEE FK 1 PRICE CODE PRICE DAILY LATEFEE

Explanation / Answer

In the given table diagram,there are 6 entities. Namely membership,rental,detailrental,price,movie,video.

1.membership.:- it has mem_num which is member number is the primary key of this entity.it includes mem_fname(first name) ,mem_lname (last name) ,mem_street (street of address) ,mem_city (city where he lives) mem_state(state) ,mem_zip,mem_balance (balance of payment)

2.rental

Rent_num is the primary key and it also consist of one foreign key mem_num , which is derived tfrom membership entity set. And also have one more attribute rent_date.

3. detailrental:- it has two primary and two foreing key,namely rent_num and vid_num.

Other attributes are,detail_fee ,detail_deudate,detail_returndate,detail_dailylatefee.

4. price:- primary key=price code, others are price_description,price_rentee,price_dailylatefee

5.movie :- pk=movie_num ,movie_title,movie_year ,ovie_cost ,movie_genre ,price_code

6.video:- pk=vid_num,rest are vid_indate, there is one foreign key movie-num.

*in this question it has used crow foot notation to show the relation.

Membership and rental have m:1 relation with multiplicity with one mandatory.

-rental and detailrental has m:1 relation, a one through many notion on one side of relationship and a one and only one on the other.

-detailrental and video has m:1 ,a zero through many notion on one side of a relationship and a one and a one and only one .

-video and movie are related and hase zero or many implication. and movie ahd price has zero or one from price table and zero or more from movie side.

Relation schema:-

Membership(mem_num,mem_name,mem_addr,mem_balance)

Rental(rent_num,detail_rental)

Movie(movie_num,vid_num,movie_title,movie_year,price_code,price_description)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.