Need Criticism on my E-R Diagram for Video Rental Chain

[edw211]

Hello everyone,

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:

http://doubletap.xtreemhost.com/diagram.pdf

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.

http://doubletap.xtreemhost.com/design.doc

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.

-Evan Williams

[griswolf]

Not clear to me why you have table CATALOG instead of having that information directly in table VIDEO (unless you are planning to add non-video items? Even so, I think that the VIDEO table is the CATALOG table (and add a "type" column to it so you know if it is a video or a bag of popcorn)

This ER diagram doesn't describe the "manyness" of the connection, unless the double-edge diamonds are many-to-many?

Does a customer have to be a member? Why? In the real world, you convince them by offering something, but not all customers will accept that offer. You may be ok with a special "non-member member" who gets every non-member sale (you can insist on membership for rentals, I suppose). Did you ever deal with Radio Shack when they used to insist on collecting your contact info for every transaction? I didn't notice when it stopped, but it is a relief that they did stop.

Are you sure every video has exactly one vendor? If you design it that way, then videos that mere humans consider "the same" end up with different VideoID. On the other hand maybe the vendor really is part of the item: Pricing might be different, or you might buy rentals from one place and sale items from another.

Ditto employees. Are there some folks who work weekends at store #44 and weekday evenings at store #77?

You want that video ID are never removed from the database. Add some columns that deal with status: in stock / on order / back-ordered / extinct or some such set of options. Of course you want more info too: when ordered, when expected, etc. That way, there's no transaction that has data missing. Bear in mind that modern databases handle multi-millions of records with ease. Calculate how long you need to retain records, how many videos and customers are feasible in that time period, and stop worrying ... :)

Also: think about the queries you will need in order to provide reports. Things like how many are overdue ordered by days over; which members are often late, with statistics; employee transaction records (who's the best salesperson, who's the best rental-pusher, is there an employee who's sales or rental records are associated with good (or bad) members more than other employees? Graph of the sales/rental curve over time of each title (allows a hope of predicting when to move them off the shelves, order more, etc).

I don't know what your doc file holds: I tried opening it in my LibreOffice suite and it hung.

[edw211]

VIDEO represents a physical copy of a movie. If I use only the VIDEO entity, and have something like twenty copies of the same movie on different VIDEO entities, then I will be storing title/runtime/etc... information redundantly. So instead, I have a VIDEO entity to represent a physical copy of a video, and a CATALOG that represents the movie itself as a work of art...

I'll be linking VideoID (an ID number for the video itself) to a CatalogID (an ID number for the movie that the store offers, like Forrest Gump). I also included a PRICING entity. This allows me to assign PriceCodes in what will be CATALOG's schema to each movie, so I can make overarching changes in price to groups of movies.

So the relationship from VIDEO to CATALOG is many-to-one. There is one item in the CATALOG that describes a particular movie, but the store might have many copies of that movie.

I was thinking of that extra attribute in VIDEO as a possible solution too, maybe including a status on the video. That way it would be really easy to determine which videos I sold, which I have currently rented out, and which are in stock. I've been having a little philosophical debate with myself on whether I should be removing VIDEO entities at all when I sell them off.

I don't think I have to go quite that in depth as far as the employees and their reports are concerned, but I do very much appreciate the ideas and your input. Those are some good ideas if I'm feeling like implementing some extra credit XD

[griswolf]

I'm having a hard time wrapping my mind around individual UID for each item in stock. That makes sense for one-off things like original artwork, or service calls; but for the things in a video store, not so much (in my opinion). And having a one-to-one relationship between your database items and actual items leads to your issue about whether to remove the database items when they are (sold/discarded/stolen). On that point: Just don't remove them. Use a status to indicate what happened. That way, you don't have to think about what happens in tables that have a FK referencing that item's entry when the item goes away. If necessary (its not), you can partition tables by date to avoid overfilling them.

[edw211]

I do see your point about that one-to-one relationship, one tuple in the VIDEO entity for each physical movie there is, but this is why I did that.

Imagine I have 1000 copies of Forrest Gump spread out among a nation-wide video chain.

If I have only a VIDEO entity like this:

VIDEO(VideoID, Title, Genre, blah blah blah...)

Then I will be listing that title once for each copy of the video there is. That wastes space and is redundant. That and if I have to insert a new tuple and enter the title and genre each time, as soon as a clerk screws up and punches in the wrong title, boom, inconsistent database. That redundancy can breed inconsistency.

So my solution to that was to have one entity set VIDEO represent a physical copy of a video. CATALOG represents a movie that the store carries. So I have one CATALOG entry that can be associated with many different VIDEO entities. So CATALOG represents the movie as a "product" and VIDEO represents the movie as an actual physical disc/cassette.

This has the added benefit of allowing me to add a movie to the CATALOG before the store actually receives any VIDEOs for it. Thus we can have decided on the pricing of the movie using the relationship between CATALOG and PRICING before I actually receive the shipment of VIDEOs and enter those into the database.

But yeah, you answered my question. I like the idea to just have a status attribute in VIDEO indicating whether the video is rented out, sold, in-stock, etc... that will make a lot of my reporting functions in my JDBC application a lot easier to write and less expensive to execute.

Thank you so much for your help, griswolf. I really appreciate it. If you or anyone else have any further comments, feel free to poke as many holes in my design as you'd like =)

[griswolf]

OK, I see your point. But. If I were doing it, I'd have a CATALOG of items, and each store would have an INVENTORY of count (and other stuff?) and FK into the catalog. There would be no VIDEO table. All the advantages you note above and no need to individually mark each "Forrest Gump" disc. Probably the price table has a STORE FK to allow the possibility that Forrest Gump sells for more in New Orleans than in Boise, or whatever.

[edw211]

Ahhhh okay I see what you're saying now.

Although yeah, the professor did indicate that he wants us to keep track of individual copies, so I suppose it will be necessary to assign the physical copy an entity.

Correct me if I'm wrong, but I do believe I would be able to model a city's comparative sales since the VIDEO entity when broken down into a relation will include the StoreID in which it is/was kept, because it participates in a many-to-one relationship with STORE. So any sale or rental that takes place will be able to be traced back to its location by looking at the city for the corresponding store of what will be the VIDEO relation's StoreID attribute, which would be FK'ed to the STORE relation's PK

[griswolf]

Yeah. One of the many advantages of normal form is that it is pretty easy to be sure you have a chain of connection between any related entity, no matter how remote. Though you do have to think about the direction of the relationship. I've ended up with many-to-many join tables a few times when I realized I needed to go either direction along what might otherwise have been a one-to-one relationship.

I've found in my own work that it is good to think "nearly first" about what reports you will need: When you have options about details of the schema, thinking about how you will make a query to give you the (whatever) report can help you get it right.