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.