SQL programming. Consider the following Relational Database Schema for a DVD collection:

DVD(releaseNo, title, releaseDate, mainLanguage, director, genre)

primary key releaseNo

ACTOR(actorNo, firstName, lastName, gender, dateOfBirth)

primary key actorNo

PLAYS_IN(releaseNo, actorNo, character, credited, pay)

primary key (releaseNo, actorNo)

foreign key actorNo references ACTOR(actorNo)

foreign key releaseNo references DVD(releaseNo)

DVD_COPY(copyNo, dvdNo, dateOfPurchase, shelfNo)

primary key (copyNo, dvdNo)

foreign key dvdNo references DVD(releaseNo)

All attributes with suffix No are positive integer numbers, all attributes with prefix date are dates. Attribute gender in ACTOR is a fixed length string of length 1 which can be either f for female or m for male.

Write MySQL 5 compliant SQL code to implement the following tasks. You are only allowed to write one SQL statement per question and you are not allowed to declare any other tables than PLAYS IN.

(a) Delete the table PLAYS IN.

(b) Set up a table PLAYS IN, assuming that all other tables have been created according to the logical design above where attribute releaseNo in DVD and attribute actorNo in ACTOR are all of type integer unsigned. The following requirements should be met:

i. attribute character is a string not longer than 40 characters, and a value for this attribute must not be missing

ii. attribute credited is either the string yes or the string no

iii. attribute pay is a positive integer which is not larger than 16,777,215

iv. if a row in table DVD is removed from the database, relevant entries in PLAYS IN should be automatically removed as well.

v. if an actor number in ACTOR is changed, the corresponding entries in PLAYS IN should be automatically kept consistent

vi. any foreign key constraint should be declared with a name.

(c) Delete all dvd copies that have a missing purchase date.

(d) List first and last name of all actresses (i.e. female actors) who were born less than 35 years ago.

(e) List the shelf numbers that contain a dvd that is in the Western genre. The list should be ordered (smallest number on top) and should not contain repetitions.

(f) For all actors/actresses list in how many movies they appeared. The result table must have four columns: actor number, first name, last name (all with the column names as headings) and a fourth column with heading appearances. The result table should be ordered by the appearances with the highest number on top. Note that the number of appearances in movies may be different from the number of different characters an actor/actress played, because an actor/actress may appear as different characters in the same movie.

(g) List all those years (as 4 digit numbers) in which more DVDs in the romcom genre have been released than DVDs in the scifi genre. The result table should have one column called Years and should be ordered in such a way that the most recent year is listed first.

(h) Implement the following business rule for the given database without using triggers: If two dvd releases with different release numbers have the same title then their release date must be different.