CODEWITHSUNDEEP
database-designnormalization
CrazyHorse
CrazyHorse

Reputation: 175

Normalization issue

I'm trying to normalize this relation:

film_year  film_name  critic_id critic_name cinemas_debut           score
     2004  I robot          111        John  NY_cinema, LA_cinema       4 
     2004  I robot          222     Mathiew  NY_cinema, LA_cinema       5

Where ...

I'm not able to transform relation to 3FN. This is that I have tried:

To 1NF:

film_year  film_name  critic_id critic_name cinemas_debut           score
     2004  I robot          111        John  NY_cinema                  4 
     2004  I robot          222     Mathiew  LA_cinema                  5
     2004  I robot          111        John  NY_cinema                  4 
     2004  I robot          222     Mathiew  LA_cinema                  5

To 2NF:

I identify critics relation as critics ( critic_id (pk), critic_name ) Also reviews relation as reviews(film_year (pk), film_name (pk), critic_id (pk), score) But I don't know what to do with resultant relation:

film_year  film_name  critic_id cinemas_debut          
     2004  I robot          111    NY_cinema                   
     2004  I robot          222    LA_cinema                  
     2004  I robot          111    NY_cinema                   
     2004  I robot          222    LA_cinema

What is wrong in my approach? Can someone translate this relation to 3FN? Thanks.

Upvotes: 1

Views: 172

Answers (3)

Serge Bollaerts
Serge Bollaerts

Reputation: 324

Ok,

  • 1St NF - No repeating elements or groups of elements
    You did it: deduplicate cinemas

  • 2nd NF: No partial dependencies on a concatenated key
    In other word, ensure there is a value (couple of values) identifying uniquely the other attributes

Critic(critic_id, critic_name, score) (/* I guess the score is part of the critic*/)

Film(film_name, film_year): you could use those 2 fields to identify uniquely the film, but it won't be 3rd NF -> Add a PK film_id Film(film_id (FK), film_name (FK), film_year (FK))

Cinema (cinema_name): won't be in 3rd NF -> Add a PK cinema_id Cinema(cinema_id, cinema_name) Film(film_id, film_name, film_year)

You need to associate a film to a cinema, to a critic. Depending on the context of critic:

  • The score depends on the film and the cinema Show(cinema_id, film_id, critic_id)

  • The score depends only on the film Show(film_id, critic_id) where the primary key is the couple of foreign keys (identifies one couple of cinema/film/critic) and is in 3rd normal form (you don't process on of these 3 FK separaterly) Film(cinema_id (FK), film_id, film_name, film_year)

  • 3rd NF: No Dependencies on Non-Key Attributes In other word, be sure that no attribute is identified by a part of a complex key. You don't have any attribute depending on the only complex key of the system (cinema_id/film_id/critic_id in show in alternative 1 only)

Sounds clear? Serge

Upvotes: 1

Taryn
Taryn

Reputation: 247630

My suggestion would be to use the following:

create table films
(
  film_id int,
  film_year int,
  film_name varchar(50)
);

create table critics
(
  critic_id int,
  critic_name varchar(50)
);

After you create the table for the films and critics create a join table between the two

create table film_critic
(
  film_id int,
  critic_id int,
  score int
);

Then to get the locations, create a table for premier cities/locations

create table premiere_locations
(
  location_id int,
  location_name varchar(50)
);

Finally create a join table between the film and the location cities

create table film_location
(
  film_id int,
  location_id int
);

Then to query your data you would use:

select f.film_year,
  f.film_name,
  c.critic_id,
  c.critic_name,
  fc.score,
  l.location_name
from films f
left join film_critic fc
  on f.film_id = fc.film_id
left join critics c
  on fc.critic_id = c.critic_id
left join film_location fl
  on f.film_id = fl.film_id
left join premiere_locations l
  on fl.location_id = l.location_id

See SQL Fiddle with Demo

Upvotes: 7

Serge Bollaerts
Serge Bollaerts

Reputation: 324

I would have modeled differently. Based on the parameters, there would be 3 entities: - Cinemas (cinemas_debut) >> Note, you must create a primary key (i.e cinemas_id) - Films (film_year, film_name) >> Note, you must create a primary key (i.e. film_id) - Critics (critic_id (PK), critic_name, score)

Now, it is a matter of interpretation. In my opinion, the critics are related to 1 film played in one particular cinema. Therefore, I would create an association - Show(cinemas_id (FK), films_id (FK), critics_id (FK))

This way, you will have:

  • Cinemas: 2 records (C1, C2)
  • Films: 1 record (F1)
  • Critics: 2 critics (CR1, CR2)
  • Shows: 2 records (C1-F1-CR1, C2-F1-CR2)

Does it sound better? Serge

Upvotes: 1

Related Questions