Part B (10 marks)

Write your answers in the answer sheet provided and submit a soft copy to Moodle.

Question 1

Douban is an onlinemovie review system where the users can rate the movies. The following database table is used to store their transaction data:

USER_MOVIE(UserID, UserName,Phone, MovieNO, Movie_category, DirectorID, DirectorName, Publisher, PublisherCountry, Rating, Date, No.OfReviewers)

UserID	UserName	Phone	MovieNO	Movie_category	Director ID	DirectorName	Publisher	PublisherCountry	Rating	RatingDate	No.Of Reviewers
U01	Susan	91235566	M01	I	D01	ALFRED	P01	USA	10	2020-8-1	104
U01	Susan	91235566	M02	II	D04	HOWARD	P09	China	5	2020-8-2	95
U01	Susan	91235566	M03	III	D10	AKIRA	P06	USA	7	2020-8-1	80
U02	Joe	63337890	M04	IV	D11	BUSTER	P06	USA	7	2020-8-3	65
U02	Joe	63337890	M02	II	D04	HOWARD	P09	China	2	2020-8-4	95
U02	Joe	63337890	M01	I	D01	ALFRED	P01	USA	9	2020-8-3	104
U04	Tom	64441111	M01	I	D01	ALFRED	P01	USA	10	2020-8-4	104
.	.	.								..

The following is an example of how the table may be used to store data.

Assumptions of storing data in the table:

1. Every user has one unique UserID. One user only has one UserName and one Phone number.
2. Each movie has one unique MovieNO, one Movie_category.

2. Each user can rate each movie for only one time but he/she can rate any movies.

3. Each movie is directed by one director and is published by one publisher only.

4. Each publisher only belongs to one country.

5. The No.ofReviewers is the number of all reviewers for the movie.

1. Explain why UserID alone is not sufficient to form a primary key. Hence, suggest a primary key for the database table USER_MOVIE. (2 marks)
2. Are there any redundant fields or repeated fields? Remove, if any, and write down the modified schema. Remember to underline the primary key. (2 marks)

Redundant fields:

Repeated fields:

Modified database schema:

(c) Write down the database USER_MOVIE in normal form. Remember to underline the key of each table. (6 marks)