Project 2

Project 1

Identify and describe the entities and their attributes.

Movies: This entity stores all the information related to Movies that are available in the Store.

Movie has following attributes:

MovieID - Unique id of the movie that are available in our store.

MovieTitle - Title of the movie.

Duration - Length of the movie.

Rating – Rating of the movie.

YearReleased – Year in which movie is released.

MovieTypeID – Stores Category of the movie.

Customers: This entity stores information related to the customer of that store.

Customer has following attributes:

CustomerID – Unique id of the customer that are using our services.

CustomerName – Name of the customer.

StreetAddress – Address of the customer.

PostalCode - Postal Code of the customer.

Phone - Phone number of the customer.

Distributors: This entity stores distributor details.

Distributor entity has following attributes:

DistributorID – Unique id of the distributor.

DistributorName – Name of the distributor.

PhoneNo – Phone Number of the distributor.

Address – Address of the distributor.

DVDVideos: This entity stores list of all movies format available.

DVDVideos entity has following attributes:

DVDVideoID – Unique identification number of video or DVD.

MovieFormat – Format of the movie i.e DVD or Video.

Price: Price of that video or DVD.

MovieID: Movie of that video or DVD.

DistributorID: Distributor of that video or DVD.

Rentals: This entity stores all movies that are rented out.

Rentals entity has following attributes:

RentID - Unique id of each movie that is rented out.

CheckOutDate – Date on which movie is rented out.

CheckInDate – Date in which that movie is returned.

LateFee – Fee if movie is not returned on time.

DamageFee – Fee if movie is damaged.

Taxes – Taxes applicable.

CustomerID – Customer that rented out.

DVDVideoID – DVD or video that is rented out.

DealsIn: This entity stores type of movies that distributor deals in.

DealsIn entity has following attributes:

DistributorID – Id of the distributor.

MovieTypeID – Type of movie it deals in.

MovieType: This entity stores categories of movies.

MovieType entity has following attributes:

MovieTypeID – Unique id of movie category.

MovieType – Description of movie category.

MovieActors: This entity stores part actor played in each movie.

MovieActors entity has following attributes:

MovieID – Id of the movie

ActorID – Id of the actor.

Role – Roles actor plays in movies.

Actors: This entity stores information of the actors.

Actors entity has following attributes:

ActorID – Unique id of the actor.

ActorName – Name of the actor.

Age – Age of the actor.

Gender – Gender of the actor.

Directors: This entity stores information of the directors.

Directors entity has following attributes:

DirectorID – Unique id of the director.

DirectorName – Name of the director.

Gender – Gender of the director.

MovieDirectors: This entity stores information of who directed that movie.

MovieDirectors entity has following attributes:

MovieID – Id of the movie.

DirectorID – Id of the director.

Awards: This entity stores information of the awards.

Awards entity has following attributes:

AwardID – Unique id of the awards.

AwardTitle – Title of that award.

Description – Description of that award.

AwardsWon: This entity stores information of awards won by the movie.

AwardsWon entity has following attributes:

MovieID – Id of the movie.

AwardID – Id of the award.

YearWon – Year that award is won.

Develop relationship sentence pairs.

RENTALS - CUSTOMER

· Each RENTAL includes a CUSTOMER

· Each CUSTOMER may be involved in one or more RENTALS.

RENTALS - DVDVideos

· Each RENTAL includes a video or a DVD

· Each VIDEO may be involved in one or more RENTALS

MOVIES - DVDVideos

· Each MOVIE can have one or more video or a DVD

· Each VIDEO is related with one MOVIE.

MOVIES - MOVIETYPE

· Each MOVIETYPE can have one or more MOVIES

· Each MOVIE is related to at least one MOVIETYPE

MOVIE –ACTORS

· Each MOVIE can have one or more ACTORS

· ACTOR can works in multiple MOVIES

MOVIE –DIRECTORS

· Each MOVIE can have one or more DIRECTORS

· DIRECTORS can direct one or more MOVIES

MOVIE –AWARDS

· Each MOVIE can have one or more AWARDS

· AWARD can be given to multiple MOVIES

Mom and Pop Johnson Video Store Database E/R Diagram:

Proposed Database Tables and Columns based on E/R Diagram.

Movies
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
MovieID	Integer		N	Y
MovieTitle	VarChar	40
Duration	integer
Rating	integer
YearReleased	Integer
MovieTypeID	Integer				Y

Customers
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
CustomerID	Integer		N	Y
CustomerName	VarChar	25
StreetAddress	VarChar	100
PostalCode	VarChar	5
Phone	VarChar	15

Distributors
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
DistributorID	Integer		N	Y
DistributorName	VarChar	25
PhoneNo	VarChar	15
Address	VarChar	100

DVDVideos
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
DVDVideoID	Integer		N	Y
MovieFormat	VarChar	12
Price	Integer
MovieID	Integer				Y
DistributorID	Integer				Y

Rentals
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
RentID	Integer		N	Y
CheckOutDate	Date
CheckInDate	Date
LateFee	Integer
DamageFee	Integer
Taxes	Integer
CustomerID	Integer				Y
DVDVideoID	Integer				Y

DealsIn
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
DistributorID	Integer		N	Y	Y
MovieTypeID	Integer		N	Y	Y

MovieType
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
MovieTypeID	Integer		N	Y
MovieType	VarChar	20

Actors
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
ActorID	Integer		N	Y
ActorName	VarChar	20
Age	Integer
Gender	VarChar	1

MovieActors
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
MovieID	Integer		N	Y	Y
ActorID	Integer		N	Y	Y
Role	VarChar	10

Directors
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
DirectorID	Integer		N	Y
DirectorName	VarChar	20
Gender	VarChar	1

MovieDirectors
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
MovieID	Integer		N	Y	Y
DirectorID	Integer		N	Y	Y

Awards
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
AwardID	Integer	10	N	Y
AwardTitle	VarChar	20
Description	VarChar	100

AwardsWon
Field Name	Data Type	Length	Null	Primary Key	Foreign Key
MovieID	Integer		N	Y	Y
AwardID	Integer		N	Y	Y
YearWon	Integer