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 dBforums.
I realize this may be a TL;DR scenario, so let me break down my main concern:
VIDEO represents all the physical copies of videos available at the store.
CATALOG represents the movie as a work of art.
SALE represents the sale of a video to a MEMBER.
SALE is a weak entity set identified by VIDEO's primary key VideoId and discriminator DateSold.
Something just smells funny about that design, but I can't place my finger on it. When a SALE takes place, the corresponding VIDEO entity will be deleted, since VIDEO keeps track of the inventory of physical movies available.
If I am identifying a sale using a VideoID, does it create a problem that when the SALE takes place, that VideoID will no longer appear in the VIDEO entity set?
One solution I thought would be to not delete the VIDEO entity, but rather have a different attribute called Status that indicates whether the video is in stock or whether it has been sold and is no longer available. That way, if I need to display inventory of videos available in the application program, I can just filter out the videos using the Status attribute in the corresponding SQL query. I have doubts about that design too, but I can't quite figure out what exactly is wrong with it. All I can tell is that something doesn't seem right about it, mainly that if I have the date the video was sold, then it would be redundant to have that Status attribute, since I can derive whether or not a video was sold by seeing if its VideoID appears in SALE with a DateSold.
Hopefully that question makes sense. If someone could give me some guidance on that, I'd be much obliged.