views:

33

answers:

3

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)
);
A: 

if all can not be duplicated again then it is included 3NF. make sure there are not duplicate data again

klox
A: 

It has e PK, so it's in 1NF. It's PK is not composite, so it's in 2NF. All the columns are dependent on nothing but the key, so it's in 3NF. There no keys other than PK, so it's in BCNF.

wqw
A: 

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.
Larry Lustig