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.
