SQL $(25$ marks$)$

Answer the questions based on the following database which contains information about actors, movies and roles performed:

Actor $($actor$\_$id$,$ name, year$\_$born$)$

Movie $($movie$\_$id$,$ title, author, year$\_$show$)$

Role $($actor$\_$id$,$ character$\_$name, movie$\_$id$)$

Where:

$$ Actor is a table of actors, their names, and the year they were born. Each actor has a unique actor$\_$id$,$ which is a key.

$$ Movie is a table of movies, giving the title, author name, and the year of shows for each movie. Each movie has a unique movie$\_$id$,$ which is a key.

$$ Role records which actors have performed which roles $($characters$)$ in which movie. Attributes actor$\_$id and movie$\_$id are foreign keys to Actor and Movie respectively. All three attributes make up the key since it is possible for a single actor to play more than one character in the same movie.

a$)$ Write the SQL statements that define $/$ create the relational schema $($tables$)$ for this database. Assume that actor$\_$id$,$ movie$\_$id$,$ year$\_$born and year$\_$written are all integers, and that name, title, author, and character$\_$name are strings. Be sure to define appropriate keys and foreign key constraints. $(11$ marks$)$

b$)$ Show the relationship of the above tables in ER diagram using Crow$$s Foot model. $(6$ marks$)$

c$)$ What does it meant by referential integrity? Show how this integrity can be achieved through the example of tables as the above. $(4$ marks$)$

d$)$ Write an SQL statement to find the name of the actor who born in year $1970.(4$ marks$)$