I'm new to the forums and also a newbie to database design studying at university. I am attempting the apparently notorious video chain database project. Below are the project specifications:
"Consider the operations of a video sales and rental chain. Such a company purchases videos from vendors and stocks them in one of many stores. Each store has several employees who rent or sell these videos to customers. All customers are members of the video chain. Members are required to return rented videos by the due date, otherwise a fine will be imposed. Commissions are awarded to employees based on their sales volume.
The database design should include tables for STORE, EMPLOYEES, VIDEOS, MEMBERS, RENTALS, SALES, and VENDORS. You should choose appropriate attributes for these tables and specify constraints.
The STORE table records the store numbers and addresses of the individual store. The EMPLOYEES table records information about the employees and the stores they work in. The VIDEOS table contains information bout all the videos in the company. It should contain a stock_number attribute to indicate which store the videocassette belongs to. Information about the members is kept in the MEMBERS table. Members are given bonus points every time they rent a video, and accumulated points are recorded in this table. Members are eligible for a free rental after accumulating a certain number of bonus points (your choice). The RENTALS and SALES tables record transactions. For rentals, the date checked out, the frequency for the checkout (how many days), and the date returned are recorded. The VENDORS table records information about the vendors from whom the videos are purchased."
I don't expect anyone here to do my homework for me. What I am seeking are constructive criticisms on the E-R design I have so far. I put the E-R design on my personal website here:
My design document, if you care to look at it, is also on my website below After hammering out the E-R design a bit more if needed, I plan to move on to converting to relations. I want to make sure I have the best possible E-R first though.
I guess the main thing I'm worried about is how I've handled a sale. I have SALE as a weak entity set with VIDEO as the identifying entity set. However, the VIDEO with the VideoID that is sold is obviously taken out of the database when the sale is processed, unlike a RENTAL. But, the SALE will depend on VideoID of its identifying entity set VIDEO to uniquely identify it. I can't use MEMBER as the identifying set because a customer might purchase multiple videos on the same day, making the discriminator of SALE worthless in that case. So, my only other option is to use DateSold as a discriminator in SALE combined with VideoID in VIDEO.
I'm trying to figure out whether it is okay to have a sales record using a VideoID that is no longer stocked, or whether my design is flawed in that respect. It would seem there is almost no way around doing that, though. I have to uniquely identify videos with an identification number because multiple copies of the same movie may be stocked in the same store. But when the store sells a video, that identification number is presumably retired. My sales relation will probably end up looking like this:
Sale(VideoID, DateSold, TransactionAmount)
But that VideoID won't be in the system anymore because I will have removed the video from inventory. So I won't be able to tell what movie the customer bought, only the ID number of the video. Any suggestions? I'm really stuck on that. Maybe I can do something by associating SALE and CATALOG? I dunno... =/
Any constructive criticism would be highly appreciated. Thank you, and it's a pleasure to join you all here on Daniweb.