database modeling
Budget: $2 – $8 USD
The Following Tables are Designed for A Movie Rating Databaese
Movie ( mID, title, year, coverimage, director )
Desc: There is a movie with ID number mID, a title, a release year, and a director.
Director (dID,dName,dateofBirth)
Desc: There is a director with dID, name as dName, and birth date as dateofBirth
Reviewer ( rID, name, gender )
Desc: The reviewer with ID number rID has a certain name and gender.
RatingAndReview ( rID, mID, stars, review, ratingDate )
Desc: The reviewer rID gave the movie mID a number of stars rating (1-5) and write comment as review on a certain ratingDate.
For this database write the required ddl and dml codes that meet the following 5 requirements (Submit answers as a single text file in txt, docx or pdf format)
1- Check the tables and correct if there are any normalization problems according to you. Write the anomality and normalized tables.
2- Select proper data formats (char, nvarchar, int...) and write sql statements that generate the database and its tables.
3- Enter some data (2 tuples for each table is enough) into the tables. Write 2 different forms of insert statements.
4- Write 1 update and 1 delete statements that changes more than 1 tuple but not the whole.
5- Write the sql comments that create the following operations:
a- Find the average rating of female reviewers for all ratings (not for a specific movie)
b- List the movies and their directors (title, dName)
c(bonus)- List the movies and their average ratings and order the movies according to their average grades. (title, averageGrade)
Movie ( mID, title, year, coverimage, director )
Desc: There is a movie with ID number mID, a title, a release year, and a director.
Director (dID,dName,dateofBirth)
Desc: There is a director with dID, name as dName, and birth date as dateofBirth
Reviewer ( rID, name, gender )
Desc: The reviewer with ID number rID has a certain name and gender.
RatingAndReview ( rID, mID, stars, review, ratingDate )
Desc: The reviewer rID gave the movie mID a number of stars rating (1-5) and write comment as review on a certain ratingDate.
For this database write the required ddl and dml codes that meet the following 5 requirements (Submit answers as a single text file in txt, docx or pdf format)
1- Check the tables and correct if there are any normalization problems according to you. Write the anomality and normalized tables.
2- Select proper data formats (char, nvarchar, int...) and write sql statements that generate the database and its tables.
3- Enter some data (2 tuples for each table is enough) into the tables. Write 2 different forms of insert statements.
4- Write 1 update and 1 delete statements that changes more than 1 tuple but not the whole.
5- Write the sql comments that create the following operations:
a- Find the average rating of female reviewers for all ratings (not for a specific movie)
b- List the movies and their directors (title, dName)
c(bonus)- List the movies and their average ratings and order the movies according to their average grades. (title, averageGrade)