Hi all, I'm currently having a hard time finalising an ER diagram for a video store application. Below is a minimal list of requirements:

* Customers can rent both movies and games.
* Movies can be either dvds or vhs, but dvds may store additional information that a vhs would not. For example a dvd may contain region codes etc.
* There may be many different copies of one movie, either on dvd or vhs. For example one copy of a dvd (which is the same movie) may be region 4 and a different copy may be region 1.
* All games and movies should have a unique ID which should not be repeated in any table, this unique ID is used to identify either a game, vhs or dvd in an rental.

I thought it would be a good idea to implement dvd and vhs as a subtype of the supertype movie.

What i'm not too sure on, is how to assign an unique ID to each copy of a dvd, vhs and game. And then how do I relate this ID to say an order line which relates back to a larger total order.

Thanks for any feedback.
If you need any more information, please don't hesitate to put up another post

The creation of the unique ID depends on the DBMS you are using. To create a foreign key, the field(s) to be referenced must be either the primary key or created with UNIQUE constraints.

Regarding primary / foreign keys of super/sub types.

The primary key of each sub-type will be the key of the super type enforced by foreign key constraints, thus the primary key of each sub entity will also be a foreign key referencing it's parent entity.

If you require the syntax to create the above constraints you will need to specify the DBMS you are using.

Most likely Microsoft SQL Server (MSDE)

Table: Movie (Supertype)
MovieID (PK), Title

Table: Dvd (Subtype)
DvdID (PK), MovieID (FK), Region

Table: Vhs (Subtype)
VhsID (PK), MovieID (PK)

Table: Game
GameID (PK)

Table: Hire
HireID (PK)

Table: HireLine
HireLineID (PK), HireID (FK), **ItemID

I was looking at this possible solution, but my biggest worry is how to give the Dvd, Vhs and Game tables the ItemID field (which should be unique across all three tables) so that an individual product (a vhs, dvd or game) can be included in a hire.

Any suggestions?

This is not a sub-type entity, rather an independent DVD-entity that contains it's own id unique to a dvd with a foreign key constraint enforcing that the dvd must correspond to a movie. This does not appear to make sense for 2 reasons. Firstly not as a sub-type and secondly it represents a "dvd-copy" which although is correct your hire entity can not reference another "itemCopy" entity in addition to the above entity through foreign a foreign key. Aa sub-entity has as its primary key the primary key of the parent entity which is enforced through a foreign key constraint.

Table Movie
(movie_ID, title, etc)
primary key(movie_ID)

Table DVD
(DVD_ID, title, dvd specific attributes)
primary key(DVD_ID)
foreign key DVD_ID references Movie(movie_ID)

Excellent, thanks.
But how do I handle the itemID for all games, vhs and dvds so that it doesnt clash? And so the itemID can be used to identify which copy of either a dvd, a vhs or a game was hired? I basically just need a way that any item, regardless of its type can be added to a hire.

Hire_ID will be the unique identifier and most likely be auto-generated. When a customer 'hires' from the video store the customer_id and details of the order need to be recorded. The only remaining decision is how do we implement the following business logic,

"A customer may hire any number of (dvd/vhs/etc) upto an optional specified limit. Each item will be hired out on a specific date thus each individual item will have it's own return date which may be different from that of the other items."

Here, you have 2 options. Given though that you have mentioned HireLine, I believe your solution relates to creating an entity 'hire' based on the event of hiring as opposed to the actual individual hire of each movie. Thus each time a customer rents any set of movies this is recorded as a transaction with the date recorded.

Each movie can then be stored in another table, hire_details that contains for each combination of hire_id and item_id (movie), the due date for each rental item.

Should you prefer the original first option, then

Hires(hire_id, customer_id, item_id, hire_date, due_date) primary key(hire_id).

I have provided two functional implementations to the problem, however the data modeller must now decide which one is more appropiate. This decision will be centered around,

2) The analyst's understanding / interpretation of the business requirements.

Cheers.

Both solutions are certainly possible...

Table: Hire
HireID (PK), CustomerID (FK), Date

Table: HireLine
HireLineID (PK), HireID (FK), ItemID (FK), ReturnDate

I was considering the above structure, making it possible to track transactions as a whole as well as individual item hires. But the problem I am still having trouble getting my head around is this:

As far as I know it is impossible for a foriegn key to come from many different tables for example in this next implemenation:

Table: Dvd
DvdID (PK), ItemID

Table: Vhs
VhsID (PK), ItemID

Table: HireLine
HireLineID (PK), ItemID (FK from both dvd and vhs).

So back to my orginal issue.
How do I give every different type of item, wether it be a vhs, a dvd or a game a ID that will make it unique to the video store that it is rented from but still store them in seperate tables?

Perhaps another table?
Table: Item
ItemID (PK)

Which would generate a many-to-many relationship which could be solved like so?

Table: Item
ItemID (PK)

Table: dvdItem
DvdID (FK), ItemID(FK)

Table: Dvd
DvdID (PK)

and repeated for the other, VHS and Game?
Would this rely too much on the fact that in order to assign an vhs/game/dvd a unique itemID all three tables would need to be checked for consitency?

Am I really making this issue more complicated than it really is, I think so???

Here is a quick ER diagram of what I was thinking?
Does it some how seem incorrect?

Table: Movies
movie_id, etc

Table: Dvd
DvdID (PK), ItemID
foreign key dvdID references Movies(movie_id)

Table: Vhs
VhsID (PK), ItemID
foreign key vhsID references Movies(movie_id)

Table: HireLine
HireLineID (PK), ItemID
Foreign key itemID references Movies(movie_id)

By using intutition however, we assume that a movie will have more than one copy. Another table is thus required being 1:N to represent this relationship. Now, replace itemID in hireLine with copyID.

Cheers.

But what about games?
They have no relationship to movies, except for the fact they are another hireable item.
Won't this generate the problem of having too many tables trying to force a foriegn key relationship?

I would recommend having a generic Item Entity. A movie is a sub-entity of Item as is Game also. The movie entity contains information about dvd's and vhs formats. The game entity contains information specific to a game. If there are significant differences between a dvd and vhs then another parent entity is required (movie) with dvd and vhs being sub entities.

Whether or not Significant differences between a dvd and vhs exist to warrant a new parent entity will be the key, in your decision, though I still recommend a single table representing both a dvd and vhs.

I do not understand your HireLine and Hire table. In HireLine the primary key should be composite with a customer_id. There is no need for hireLine to contain its own unique ID.

Can anyone please help!! I have a project that I have to hand in very soon and I can't put it together.
Its about a Video/Dvd/Game store that hire out DVD, Bluray and VHS and also game titles. I have to build an Access Database application with at least 7 tables. Each Movies and Games must have a unique ID beginning with AA followed by 5 digits.

Thanks