1

I am creating a database for a DVD rental shop, I have various entities that are related to this question, such as Film, FilmStar.

For each film, you record its unique number, title, the year in which it was made, its category (action adventure, science fiction, horror, romance, comedy, classic, children's), its director, and all stars that appeared in it. For each film, you also want to store the type of DVD hire (new release, classics, other).

I am mostly unsure about "all the stars that appeared in it". I first thought just having an attribute in the 'Film' Entity, for example filmStar and then each star would be inserted into that attribute, for example: "John Doe, Jane Doe" for each film. But then I realised that this wouldn't be 1NF as : "the domains of attributes must include only atomic values, the value of an attribute must be a single value from the domain of that attribute", as it contains more than one value and isn't atomic.

I then thought about having a separate entity that contains certain attributes such as: filmID, filmStarID. So John Doe would have the filmStarID of '0001' (all of this would be in the FilmStar entity, which is a separate entity). But then the same problem would occur, for example the filmID attribute would have all of the filmID's that the filmStar has starred in, for example: John Doe would have "101, 115, 009". Which again wouldn't be 1NF.

I was just wondering what your thoughts are on this?

Improve this question
2
  • You need three tables - one for the film, one for the star, and then a third which associates your FilmID to StarID.
    – Avarkx
    Oct 8, 2015 at 16:09
  • @Avarkx thats what I thought, but in the third table the same error would occur. As I have stated in the last paragraph? Because the star could have been in more than one film
    – Tom
    Oct 8, 2015 at 17:59

2 Answers 2

Reset to default
3

You're on the right track with splitting up your domains and using a linking entity. Separating the Films from the Stars is absolutely the first step. 

 ---------------------------    -------------------------
| FilmID |     FilmName     |  | StarID |   StarName     |
|--------+------------------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II     |  | 000002 | Sarah Berry    |
| EVIL03 | Army of Darkness |  | 000003 | Marcus Gilbert |
 ---------------------------    -------------------------

The missing piece is tying the two together. Obviously, stuffing the StarIDs into the Film table is in violation of the first normal form, since you end up needing to store multiple references to actors in a single row.

 --------------------------------------------
| FilmID |     FilmName     |     StarIDs    |
|--------+------------------+----------------|
| EVIL01 | The Evil Dead    | 000001         | 
| EVIL02 | Evil Dead II     | 000001, 000002 |   Wrong,
| EVIL03 | Army of Darkness | 000001, 000003 |   Wrong
 --------------------------------------------

Splitting the offending column out to its own table restores the integrity of the original Film table, but continuing to store multiple references in a single attribute still invalidates 1NF for the data model.

 ---------------------------    -------------------------
| FilmID |     FilmName     |  | FilmID |     StarIDs    |
|--------+------------------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | EVIL01 | 000001         |
| EVIL02 | Evil Dead II     |  | EVIL02 | 000001, 000002 |   Still
| EVIL03 | Army of Darkness |  | EVIL03 | 000001, 000003 |   Wrong
 ---------------------------    -------------------------

To rectify this is fairly simple, as you merely need to associate each FilmID with the respective StarID, bringing your model in-line with the tenants of 1NF.

 ---------------------------    -----------------    -------------------------
| FilmID |     FilmName     |  | FilmID | StarID |  | StarID |   StarName     |
|--------+------------------|  |--------+--------|  |--------+----------------|
| EVIL01 | The Evil Dead    |  | EVIL01 | 000001 |  | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II     |  | EVIL02 | 000001 |  | 000002 | Sarah Berry    |
| EVIL03 | Army of Darkness |  | EVIL02 | 000002 |  | 000003 | Marcus Gilbert |
 ---------------------------   | EVIL03 | 000001 |   -------------------------
                               | EVIL03 | 000003 |
                                -----------------

Now, with the the data normalized in this manner, it is easy to add attributes to expand the model. Say an actor's character name in a movie was desirable, you could add CharacterName to the FilmStar table - maybe useful for getting a list of everybody who's ever played "James Bond," for instance.

Improve this answer
6
  • Thank you very much for your detailed answer, was a great help. So for the FilmStar table (which has FilmID and StarID), what would the primary key be? would it be both? PRIMARY KEY (FilmID, StarID)?
    – Tom
    Oct 9, 2015 at 21:27
  • You absolutely could use both columns for the primary key and would be absolutely right to do so. I generally create a surrogate key ( using say, INTEGER and IDENTITY ) and certainly would in this case, but not doing so is an equally viable route.
    – Avarkx
    Oct 9, 2015 at 22:02
  • Thank you. I agree. I need to record the cardinality ratios for everything, as this will be displayed on an ERD. So FilmStar 1.1--- 1.* FilmCastMember 1.* --- 1.1 Film OR FilmStar 1.* --- 1.1 FilmCastMember 1.1 --- 1.* Film. The FilmCastMember is the table in the middle which has filmID and starID
    – Tom
    Oct 10, 2015 at 0:48
  • The former :) one film can have many stars, one star can have many films.
    – Avarkx
    Oct 10, 2015 at 1:22
  • So would that then be FilmCastMember 1.* -- 1.* Film? (because I really want to avoid many-to-many relationships)
    – Tom
    Oct 10, 2015 at 1:26
0 
tblFilm
-ID
-FilmName
-YearReleased

tblActor
-Id
-name

tblFilmActor
-FA_ID
-tblFilm.ID
-tblActor.ID

tblCategory
-Id
-CategoryName (horror, sci-fi, fantasy, romance,etc)

tblFimCatogory
-FC_ID
-tblFilm.Id
-tblCategory.Id

This will allow you to write a queries for:

  • all films by an actress.
  • All actors in a film
  • All Films with more than 1 category. (e.g. Prometheus may be sci-fi, but could also be classified as Suspense or horror).
  • all films that are sci-fi.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.