preview

Relational Schema For Database And Database

Better Essays
Open Document
Question 1: Relational Schema (6 Marks)
• Provide a relational schema for the database you will create.
• Identify the primary keys (1 mark), the foreign keys (1 mark), and the constraints (1 mark).
• Identify the data types (1 mark) and write a query to create the database with MySQL (1 mark) and one to import the sample dataset you were provided with (1 mark)

Answer 1:

• Provide a relational schema for the database you will create.

Movies_n9000348

movieID
Title Year

Ratings_n9000348 reviewerID movieID rating comments

Reviewers_n9000348

reviewerID name yearJoined trustRating

• Identify the primary keys (1 mark), the foreign keys (1 mark), and the constraints (1 mark).

Listed below are the Established Relationships: -

1) movieID is the ‘Primary Key’ in the Movies_n9000348 table.
2) Combination of reviewerID and movieID is a ‘Composite Primary Key’ in the Ratings_n9000348 table.
3) movieID from Ratings_n9000348 table is the ‘Foreign Key’ to movieID column in the Movies_n9000348 table.
4) reviewerID from Reviewers_n9000348 table is the ‘Foreign Key’ to reviewerID column in the Ratings_n9000348 table. • Identify the data types (1 mark) and write a query to create the database with MySQL (1 mark) and one to import the sample dataset you were provided with (1 mark)

• Identify the data types (1 mark)

DATATYPES:

Datatypes identified for the column variables in Movies_n9000348 table:

1) movieID: INT
2) Title: VARCHAR(100)
3) Year: INT

Datatypes
Get Access
Related
Get Access