Search code examples
sqlmysqlnormalization3nf

sql 3NF Normalization


is this in 3NF ?

create table movies( 
  id numeric primary key not null default autoincrement,
  name varchar(50) not null, 
  release-date Date, 
  price numeric, 
  rating numeric,
  length numeric,
  description varchar(1500)
);

create table movies( 
  id numeric primary key, 
  name varchar(20) 
);

create table genre(
  name varchar(20) primary key
);

create table directors(
  id numeric primary key not null default autoincrement, 
  first-name varchar(32) not null, 
  last-name varchar(32) not null, 
  gender varchar(8), 
  dob Date, 
  biography varchar(1000)
);

create table movie-Star(
  id numeric primary key not null default autoincrement, 
  first-name varchar(20) not null, 
  last-name varchar(20) not null,
  gender varchar(8), 
  dob Date, 
  hometown varchar(20)
);

create table movies-cast(
  movie-id numeric references movies(id), 
  actor-id numeric references movie-Star(id), 
  role varchar(32), 
  primary key (movie-id, actor-id)
);

Create table Studio( 
  studio-id numeric references directors(id)
  Directer-name varchar(20) not null
  name varchar(20) primary key
);

create table directors(
  id numeric primary key not null default autoincrement, 
  first-name varchar(32) not null, 
  last-name varchar(32) not null, 
  gender varchar(8), 
  dob Date, 
  biography varchar(1000)
);

Solution

  • It looks pretty well structured. I don't see any normalization problems. However:

    • Movies and Directors tables are created twice.
    • Genre table is not used for anything (presumably should be in movies).
    • Same with Studios.
    • Current arrangement allows only one director per studio. This should probably be A) one studio per director (add studio_id column to directors) or more likely B) many-to-many relationship between studio and director (add new studio_directors table).
    • Current arrangement does not associate Director with Movie.
    • You might consider combining Director and Movie-Start into one table called Talent. You have data duplication in which a star is also a director. This is the biggest normalization issue with your design.