Physical Design & Implementation

Janelle Chpaman – Project 1 April 13, 2014

List of entities:

Actor Actor

Borrowing Rental History

Cast Actor in MovieTitle

Customer Customer

Discount Discount prices for certain movies or types of movies

Distributor Distributor of Movies

DistributorMovieType What movie types distributor provides

DVD Descendant of Movie format

Fee Late and damaged fees, failure to rewind fees and so on for each Borrowing

Movie Video or DVD of Movie

MovieAward Academy Awards won (by the movie, the actors, the actresses and/or the directors)

MovieAwardType Academy Award type

MovieTitle Name of Movie

MobieTitleType Movie classification

MovieType Types of Movie (suspense, horror, mystery, comedy, etc.)

Video Descendant of Movie format

Relationships:

Actor Cast Actor takes part in many movie Casts.

Borrowing Fee When custmer Borrows a movie, one or more Fees are connected with this action.

Borrowing Customer One Customer Borrows zero or more movies (with assumption that customer can only chose to register).

Borrowing Movie Movie can be Borrowed by one or more customers.

Cast MovieTitle Each MovieTitle can play multiple Actors (M:N relationship replaced by Cast entity).

Discount MovieTitle One MovieTitle can take part in several (time limited) Discount offers.

Discount MovieType One MovieType can take part in several (time limited) Discount offers.

Distributor Movie Each Distributor provides one or more Movies.

Distributor DistributorMovieType Each Distributor provides one ore more MovieTypes and each MovieType can be provided by multiple Distributors so this M:N relationship was replaced by the DistributorMovieType entity.

Janelle Chpaman – Project 1 April 13, 2014

DistributorMovieType MovieType Each Distributor provides one ore more MovieTypes and each MovieType can be provided by more Distributors so this M:N relationship was replaced by DistributorMovieType entity

DVD Movie DVD is descendant of Movie.

Video Movie Video is descendant of Movie.

Movie MovieTitle Each MovieTitle exists on one or more media types.

MovieAward MovieAwardType Movie Award is one of the Movie Award Types.

MovieAward MovieTitle One MovieTitle can win zero or multiple Awards.

MovieTitle MovieTitleType Each MovieTitle can be assigned to multiple MovieTypes and multiple Movies of each MovieType exists so this M:N relationship was replaced by MovieTitleType entity.

MovieTitleType MovieType Each MovieTitle can be assigned to multiple MovieTypes and multiple Movies of each MovieType exists so this M:N relationship was replaced by MovieTitleType entity.

Janelle Chpaman – Project 1 April 13, 2014

erd MomAndPop

Movie

DVD Video

MovieT itle

Distributor

MovieT ype

DistributorPrice

IdNumber

IdNumber

SerialNumber

Name

CatalogDescription

Customer

Borrowing

DateRented

DateReturned

FeeT ype

Fee

T oDate

Actor

CustomerPrice

Discount

Amount

DateFrom DateT o

RunningLength

Rating

YearReleased Director

MovieAward

Name

Address

Phone

Email

Cast

DistributorMovieT ype

MovieT itleT ype

Amount

FirstName LastName

Street

City

ZIP

CustID

FirstName

LastName

Year

NameCodeFirstName LastName

Name

Code

Role

MovieAwardT ype

Id Name

Id

Id

1

0..*

0..*

1

1 0..*

1

1..*

1 0..*

1

0..*

1

0..*

1 1..*

1

1..*

1

1..*

0..*

1..

0..*

1

0..* 1

0..*

1

0..*1

Janelle Chpaman – Project 1 April 13, 2014

create table CUSTOMER ( CUST_ID bigint not null, FIRST_NAME varchar(50) not null, LAST_NAME varchar(50) not null, STREET varchar(50), CITY varchar(50) not null, ZIP varchar(10), EMAIL varchar(50), PHONE varchar(20) PRIMARY KEY (CUST_ID) ) GO create table DISTRIBUTOR ( CODE varchar(15) not null, NAME varchar(50) PRIMARY KEY (CODE) ) GO create table MOVIE_TYPE ( CODE char(5) not null, NAME varchar(50) PRIMARY KEY (CODE) ) GO create table ACTOR ( ACTOR_ID int not null, FIRST_NAME varchar(50) not null, LAST_NAME varchar(50) not null PRIMARY KEY (ACTOR_ID) ) GO create table MOVIE_TITLE ( ID_NUMBER bigint not null, NAME varchar(50) not null, RATING smallint, RUNNING_LENGTH smallint,

Janelle Chpaman – Project 1 April 13, 2014

CATALOG_DESCRIPTION text, YEAR_RELEASED smallint PRIMARY KEY (ID_NUMBER) ) GO create table MOVIE_TITLE_TYPE ( MOVIE_TITLE_ID bigint not null FOREIGN KEY REFERENCES MOVIE_TITLE(ID_NUMBER), MOVIE_TYPE_CODE char(5) not null FOREIGN KEY REFERENCES MOVIE_TYPE(CODE), PRIMARY KEY (MOVIE_TITLE_ID, MOVIE_TYPE_CODE) ) GO create table MOVIE ( ID_NUMBER bigint not null, MEDIA_TYPE char(1) not null CHECK (MEDIA_TYPE in ('D', 'V')), -- D=DVD, V=Video DISTRIBUTOR_PRICE numeric(5,2) not null, SERIAL_NUMBER varchar(30) not null, CUSTOMER_PRICE numeric(5,2) not null, MOVIE_TITLE_ID bigint not null FOREIGN KEY REFERENCES MOVIE_TITLE(ID_NUMBER) PRIMARY KEY (ID_NUMBER) ) GO create table [CAST] ( ACTOR_ID int not null FOREIGN KEY REFERENCES ACTOR(ACTOR_ID), MOVIE_TITLE_ID bigint not null FOREIGN KEY REFERENCES MOVIE_TITLE(ID_NUMBER), [ROLE] varchar(100) PRIMARY KEY (ACTOR_ID, MOVIE_TITLE_ID) ) GO create table MOVIE_AWARD_TYPE ( ID smallint not null, NAME varchar(50) not null, PRIMARY KEY (ID) ) GO create table MOVIE_AWARD

Janelle Chpaman – Project 1 April 13, 2014

( MOVIE_TITLE_ID bigint not null FOREIGN KEY REFERENCES MOVIE_TITLE(ID_NUMBER), [YEAR] smallint not null, MOVIE_AWARD_TYPE_ID smallint not null FOREIGN KEY REFERENCES MOVIE_AWARD_TYPE(ID) PRIMARY KEY (MOVIE_TITLE_ID, [YEAR],MOVIE_AWARD_TYPE_ID) ) GO create table DISCOUNT ( ID bigint not null, MOVIE_TITLE_ID bigint null FOREIGN KEY REFERENCES MOVIE_TITLE(ID_NUMBER), MOVIE_TYPE_CODE char(5) FOREIGN KEY REFERENCES MOVIE_TYPE(CODE), DATE_FROM datetime not null, DATE_TO datetime not null, AMOUNT numeric(5,2) PRIMARY KEY (ID) ) GO create table DISTRIBUTOR_MOVIE_TYPE ( DISTRIBUTOR_CODE varchar(15) not null FOREIGN KEY REFERENCES DISTRIBUTOR(CODE), MOVIE_TYPE_CODE char(5) not null FOREIGN KEY REFERENCES MOVIE_TYPE(CODE), DATE_FROM datetime not null, DATE_TO datetime not null, AMOUNT numeric(5,2) PRIMARY KEY (DISTRIBUTOR_CODE, MOVIE_TYPE_CODE) ) GO create table BORROWING ( ID bigint not null, CUSTOMER_ID bigint not null FOREIGN KEY REFERENCES CUSTOMER(CUST_ID), MOVIE_ID bigint not null FOREIGN KEY REFERENCES MOVIE(ID_NUMBER), DATE_RENTED datetime not null, TO_DATE datetime not null, DATE_RETURNED datetime PRIMARY KEY (ID) ) GO create table FEE

Janelle Chpaman – Project 1 April 13, 2014

( BORROWING_ID bigint not null FOREIGN KEY REFERENCES BORROWING(ID), FEE_TYPE char(1) CHECK (FEE_TYPE in ('L', 'D', 'F')), -- L=Late, D=Damage, F=Failure FEE_DATE numeric(5,2) not null, AMOUNT numeric(5,2) not null, PRIMARY KEY (BORROWING_ID, FEE_TYPE, FEE_DATE) ) GO