Question

Use information from text to create (ERD) Entity Relationship Diagram . Make sure relationship sentences are consistent with relationship in ERD.

Video Store (AVS) runs a series of fairly standard video stores:

• Every customer must have a valid AVS customer card in order to rent a video. Customers rent videos for three days at a time. Every time a cus- tomer rents a video, the system must ensure that he or she does not have any overdue videos. If there are overdue videos, they must be returned and an overdue fee must be paid before the cus- tomer can rent more videos.

• If the customer has returned overdue videos, but has not paid the overdue fee, the fee must be paid before new videos can be rented. If the customer is a premier customer, the first two overdue fees can be waived, and the customer can rent the video.

• Every morning, the store manager prints a report that lists overdue videos; if a video is two or more days overdue, the manager calls the customer to remind him or her to return the video.

Answer 1

Diagram:

(i) In the above diagram there are total four entities described and they are as follows:

a. Customer

b. Video

c. Rent

d. Rent Details

(ii) The entity customer have the primary key card number as mentioned in the case study which can help each data in the entity to be identified uniquely. The other required attributes of customer are cname, address, phone and email.

(iii) A customer can rent more than one videos from the video store where the video entity have the primary key videoid. The relationship between customer and video is rent where the cardinality is 1 to many. Video entity have some other attributes like name, genre and duration.

(iv) When a customer rents a video the required details directly goes to the entity rent where the primary key is rentid and the foreign key is cardnumber which will help to keep track of customers who have rented videos. Rent entity stores the date when the videos where rented, due date, actual return date for keeping information about the overdue videos and there is also an attribute named totalamount which represents the amount need to be paid by the customer.

(v) As the single entity rent cannot stores the information of videos which were rented so, there is also an entity named rent details with two foreign keys- one is videoid from video entity and another one is rentid from rent entity. It also have an optional attribute named overdue amount as a video may or may not be overdue. Every detail of rent details belongs to only one rent so the relationship and cardinality is described like that.