Database Design – Making Database Tables 1NF

database-designnormalizationrelational-theory

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?

Best Answer

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.

Related Solutions

Mysql – Data normalization (from 0NF to 1NF)

Yes, it's atomic; your domain is the ISBN. If you were storing the domains for ISBNPrefix, ISBNRegistrationGroup, ISBNRegistrant, ISBNPublication and ISBNCheckDigit, then you'd split it up.

Consider the North American phone number +1-234-567-8901 ext 234567. Many systems choose to store such a number under two domains, such as PhoneNumber and PhoneNumberExtension, taking perhaps 2345678901 under the PhoneNumber domain and 234567 under the PhoneNumberExtension domain, ignoring the Phone Number Country Code altogether ( largely because it is always 1 ). Since the domains are defined as such, it is not incorrect to store them in such a manner. An example of violating 1NF in this case would be to store multiple extension numbers in the same row for a singular entity existing in the PhoneNumber domain.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567, 890123

Instead, to preserve 1NF, the PhoneNumberExtension attributes are stored separately.

| PhoneNumber | PhoneNumberExtension |
  2345678901    234567
  2345678901    890123

A system with more granular domains may choose to store phone numbers by PhoneNumberCountryCode, PhoneNumberAreaCode, PhoneNumberPrefix, PhoneNumberLineNumber and PhoneNumberExtension

| CountryCode | AreaCode | Prefix | LineNumber | Extension |
  1             234        567      8901         234567

Since the domains are defined in such a way, it is again not incorrect to store the data as such. The same logic applies to various "compound attributes," like your ISBN; geographical coordinate data and municipal mailing addresses are some common examples, but such "Domain grouping" happens even in basic data types - consider DATETIME, a raw datatype for an example - one part date, one part time.

The way you choose to define your domains will largely be a decision on how you intend to use them. In the telephone example, there tends to be little value in breaking apart AreaCode since other more appropriate demographics are often available, such as City, though splitting the domain apart is not a "wrong" choice. If there is little value for your purposes to split apart the ISBN, you are not violating 1NF by defining the domain on a less granular level - you just can't store two of them in the same row for the same entity.

Relation between first name and last name

I think the example is a very theoretical one. I have never seen any real world examples of separate FirstNames and LastNames tables. Of course you can normalize your database this way, but it wouldn't make much sense in most of the cases.

Back to the relation. I think there are two relations here:

First name - Person 1:1
Last name - Person 1:1

I don't think there is a relation between the first and last name.

We can take a slightly different approach with one surname(last name) and multiple "first" names. Then there can be a many to one relationship between first names and person.

However, in most cases first name and last name are just attributes of one person. To solve the issue of mail generation, you can have 3 columns: first name, middle name and last name. Then you use only first and last names for mail generation and store the middle name for other purposes.

Best Answer

Related Solutions

Mysql – Data normalization (from 0NF to 1NF)

Relation between first name and last name

Related Question