Please I need this done correctly, I have posted many times and nobody has been
ID: 3593615 • Letter: P
Question
Please I need this done correctly, I have posted many times and nobody has been able to explain and answer it for me. Thank you.
(8 points) Suppose that we have a logical schema representing movies, movie critics and their movie ratings. The relationship "ratings" captures which reviews were posted by which critics with the rating (0-100) and date. The logical schema is given below: Movie: (Title, Subtitle, Year, Duration, Edition) Ratings: (RelD, Fitle Subtitle, Rating, RDate) Reviewer: ReviewerlD, Name, Address, Affiliation) Assume that the tables Movie and Reviewer have already been created with all of the necessary attributes, domains, and keys. Fill out the SOL taple below to create the table Ratings with all of the necessary attributes, domains, and keys. Assume that Reviewer ID is an 8 characters string and that neither title nor sub-title needs more than 26 characters and that the rating value cannot be NULL. You only need to create the Ratings table. Do not worry about creating Movie or Reviewer tables. CREATE TABLE Ratings ( RevlD number (8) Title Char (50) Subtitle (50) Rating int NOT NULL, RDate DATE, CONSTRAINT UNIQUE_CONSTRAINT_PK1 PRIMARY KEY CONSTRAINT UNIQUE_CONSTRAINT_FK2 FOREIGN KEY (REVID REFERENCES _Reviewer( CONSTRAINT UNIQUE CONSTRAINT_FK3 FOREIGN KEYSubtitle REFERENCES Movie (Subtitle)- );Explanation / Answer
Solution :
CREATE TABLE Ratings(
RevId number (8),
Title char (50),
Subtitle (50)
Rating int NOT NULL,
RDate DATE,
CONSTRAINT UNIQUE_CONSTRAINT_PK1
PRIMARY KEY(RevId,Title,Subtitle),
CONSTRAINT UNIQUE_CONSTRAINT_FK2
FOREIGN KEY(RevId)
REFERENCES Reviewer(ReviewerID),
CONSTRAINT UNIQUE_CONSTRAINT_FK3
FOREIGN KEY(Title,Subtitle)
REFERENCES Movie(Title,Subtitle)
);
Explanation :
1.These all are the columns for Ratings Table
RevId number (8),
Title char (50),
Subtitle (50)
Rating int NOT NULL,
RDate DATE
2.Primary Key
CONSTRAINT UNIQUE_CONSTRAINT_PK1 -> Primary Key Contraint
PRIMARY KEY(RevId,Title,Subtitle) -> Set RevId,Title,Subtitle as primary key
3.Foreign Key from Reviewer Table
CONSTRAINT UNIQUE_CONSTRAINT_FK2 ->Foreign Key Constraint
FOREIGN KEY(RevId) ->Set these columns as foreign key
REFERENCES Reviewer(ReviewerID) ->Foreign key reference table and columns(primary key of reviewer table)
4.Foreign Key from Movie Table
CONSTRAINT UNIQUE_CONSTRAINT_FK3 ->Foreign Key Constraint
FOREIGN KEY(Title,Subtitle) ->Set these columns as foreign key
REFERENCES Movie(Title,Subtitle) ->Foreign key reference table and columns(pwrimary key of movie table)