Database Systems for Website Applications Assignment:
In this assignment, you will review the below schema then answer the three questions listed afterwards.
Assume and review the following Internet movie database schema:
Database:
Table: movie
id - int, primary key
title - char
year - int
rating - float
Table: person
id - int, primary key
name - char
sex - char
age - int
Table: role
personid - foreign key references person - int
movieid - foreign key references movie -- int
character - char
Based on your review of the Internet movie database schema above, respond to the following:
• Explain the role of primary and foreign keys in databases. Explain why we need them and whether we can extract information from tables without having primary or foreign keys.
• Write and execute a SQL for each of the following scenarios:
o An SQL with a nested subquery.
o A select query that uses GROUP BY
o A select query that uses ORDER BY
o A select query that uses Inner JOIN
o A select query that uses an Outer JOIN
o A select query that uses UNION
o A select query that uses EXCEPT
• Make sure you give a screenshot of the output from executing your queries.