Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Post Video Store Database

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    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.
    Last edited by r123456; 12-22-03 at 07:16.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    Most likely Microsoft SQL Server (MSDE)

  4. #4
    Join Date
    Dec 2003
    Posts
    7
    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?

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Table: Dvd (Subtype)
    DvdID (PK), MovieID (FK), Region
    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)
    Last edited by r123456; 12-25-03 at 09:46.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Dec 2003
    Posts
    7
    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.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Last edited by r123456; 12-23-03 at 03:18.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Dec 2003
    Posts
    7
    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???

  9. #9
    Join Date
    Dec 2003
    Posts
    7
    Here is a quick ER diagram of what I was thinking?
    Does it some how seem incorrect?
    Attached Thumbnails Attached Thumbnails er.jpg  

  10. #10
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  11. #11
    Join Date
    Dec 2003
    Posts
    7
    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?

  12. #12
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Last edited by r123456; 12-23-03 at 05:23.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  13. #13
    Join Date
    May 2012
    Posts
    14

    Smile

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •