Assignment Part 1


Group Member 1: Yi Peng, S4526972
Group Member 2: Wenfeng Wang, S4526266
Group Member 3: Yuze Ji, S4496781








Note: Please edit the name and student ID number to reflect your identity and
do not modify the design and layout of the cover page (font, font size, spacing,
etc.). Also, do not change the page layout in the given assignment template.
YOUR ER DIAGRAM MUST FIT ON THIS PAGE


YOUR FINAL MAPPING MUST FIT ON THIS PAGE
Entity ‘product’
Description
This entity represents the product on the site.
Key Attribute Data Type Not Null Unique Description
PK ProductId int Yes Yes This is the
primary key of
product, It
uniquely
identifies the
product
FK(producttype.
ProductTypeId)
ProductTypeId int Yes No This is the
product type, It
identifies the
type of the
product.
Title varchar No No This is the title of
the product.
Code varchar Yes No This is the
unique code of
the product.
Synopsis varchar No No This is the
Synopsis of the
product.
AudioLanguage int No No This is the
AudioLanguage
of the product.
FK(product.ProductId) SequelId int No No If the product is
movie, this is the
sequel id of the
movie.
ReleaseDate datetime No No This is the
ReleaseDate of
the product.
Runtime int No No This is the
Runtime of the
product.
SubtitleLanguage int No No This is the
SubtitleLanguage
of the product.

Entity ‘producttype’
Description
This entity represents the type of the product.
Key Attribute Data Type Not Null Unique Description
PK ProductTypeId int Yes Yes This is the
product type id.
ProductTypeName varchar No No This is the
product type
name, movie or
TV show.
Entity ‘tvepisode’
Description
This entity represents the episode information of the TV show.
Key Attribute Data Type Not Null Unique Description
PK TVEpisodeId int Yes Yes This is the
episode id of
the TV show.
FK(product.ProductId) ProductId int Yes No This is the
product id. It
shows which
product this
episode belongs
to.
SeasonNumber int No No This is the
season number
of the TV show.
EpisodeNumber int No No This is the
episode
number of the
TV show.
Runtime int No No This is the run
time of the TV
show.
ReleaseDate datetime No No This is the
release date of
the TV show.

Entity ‘tag’
Description
This entity represents the list of the tag.
Key Attribute Data Type Not Null Unique Description
PK TagId int Yes Yes This is the id of
the tag.
TagName varchar No No This is the name
of the tag.

Entity ‘taglist’
Description
This entity represents the tag of the product. It can indicate the tag of the product.
Key Attribute Data Type Not Null Unique Description
PK TagListId int Yes Yes This is the id of
the row.
FK(product.ProductId) ProductId int Yes No This is the id of
the product.
FK(tag.TagId) TagId int Yes No This is the id of
the tag.

Entity ‘cast’
Description
This entity represents the list of the cast. It contains all the cast.
Key Attribute Data Type Not Null Unique Description
PK CastId int Yes Yes This is the id of
the cast.
Name varchar No No This is the name
of the cast.
Nationality varchar No No This is the
nationality of
the cast.

Entity ‘castlist’
Description
This entity represents the cast of the product. It can indicate the cast of the product.
Key Attribute Data Type Not Null Unique Description
PK CastListId int Yes Yes This is the id of
the row.
FK(product.ProductId) ProductId int Yes No This is the id of
the product.
FK(cast.CastId) CastId int Yes No This is the id of
the cast.
Role varchar No No This is the role
of an actor.

Entity ‘user’
Description
This entity represents the user of the site.
Key Attribute Data Type Not Null Unique Description
PK UserId int Yes Yes This is the id of
the user.
Name varchar No No This is the name
of the user.
Username varchar Yes Yes This is the
username of the
user.
Email varchar Yes Yes This is the email
of the user.
BirthDate datetime No No This is the birth
date of the user.
RegistrationDate datetime No No This is the
registration date
of the user.
Subscription enum No No This is the
subscription
type of the user.


Entity ‘watchingrecord’
Description
This entity represents the watching record of the user.
Key Attribute Data Type Not Null Unique Description
PK WatchingRecordId int Yes Yes This is the id
of the
watching
record.
FK(user.UserId) UserId int Yes No This the id of
the user.
Fk(product.ProductId) ProductId int Yes No This is the id
of the
product.
FK(producttype.ProductTypeId) ProductTypeId int Yes No This is the id
of the
product type.
FK(tvepisode.TVEpisodeId) TVEpisodeId int Yes No This is the id
of the tv
episode.
StoppedTimestamp timestamp No No This is the
stopped
timestamp.

Entity ‘friendlist’
Description
This entity represents the friend list of the user.
Key Attribute Data Type Not Null Unique Description
PK FriendListId int Yes Yes This is the id of
the row.
FK(user.UserId) UserId int Yes No This is the id of
the user.
FK(user.UserId) FriendId int Yes No This is the id of
another user.

Entity ‘playlist’
Description
This entity represents the paly list of the user.
Key Attribute Data Type Not Null Unique Description
PK PlayListId int Yes Yes This is the id of
the row.
FK(user.UserId) UserId int Yes No This is the id of
the user.
PlayListName varchar Yes No This is the name
of the playlist.

Entity ‘playlistitem’
Description
This entity represents the items of the playlist.
Key Attribute Data Type Not Null Unique Description
PK PlayListItemId int Yes Yes This is the id of
the item.
FK(product.ProductId) ProductId int Yes No This is the id of
the product.
FK(playlist.PlayListId) PlayListId int Yes No This is the id of
the playlist.


YOUR FUNCTIONAL DEPENDENCIES MUST FIT ON THIS PAGE
Entity ‘product’
ProductId → (ProductTypeId,Title,Code,Synopsis,AudioLanguage,Sequelid,ReleaseDate,Runtime,SubtitleLanguage)
Entity ‘user’
UserId → (Name,Username,Email,BirthDate,RegistrationDate,Subscription)
Entity ‘tvepisode’
TVEpisodeId → (ProductId,SeasonNumber,EpisodeNumber,Runtime,ReleaseDate)
Entity ‘tag’
TagId → (TagName)
Entity ‘taglist’
TagListId → (ProductId,TagId)
Entity ‘playlist’
PlayListId → (UserId,PlayListName)
Entity ‘playlistitem’
PlayListItemId → (ProductId,PlayListId)
Entity ‘producttype’
ProductTypeId → (ProductTypeName)
Entity ‘cast’
CastId → (Name,Nationality)
Entity ‘castlist’
CastListId → (ProductId,CastId,Role)
Entity ‘friendlist’
FriendListId → (UserId,FriendId)
Entity ‘watchingrecord’
WatchingRecordId → (UserId,ProductId,ProductTypeId,TVEpisodeId,StoppedTimestamp)
ProductId → (ProductTypeId)

YOUR NORMALISATION MUST FIT ON THIS PAGE
Entity ‘watchingrecord’
Drop the ProductTypeId.
Key Attribute Data Type Not Null Unique Description
PK WatchingRecordId int Yes Yes This is the id
of the
watching
record.
FK(user.UserId) UserId int Yes No This the id of
the user.
Fk(product.ProductId) ProductId int Yes No This is the id
of the
product.
FK(tvepisode.TVEpisodeId) TVEpisodeId int Yes No This is the id
of the tv
episode.
StoppedTimestamp timestamp No No This is the
stopped
timestamp.