Task description;A list with information about movies needs to be organised in a database.;The list contains the following data, for...
Task description;A list with information about movies needs to be organised in a database.;The list contains the following data, for each movie;Movie Title, year of release, country, runtime, director name with their year of birth and nationality, main actors name with their year of birth and nationality.;The database should be populated with the data listed below, and queries should be created so that the users can;List name and surname of all actors in alphabetical order;List name and surname of all directors in alphabetical order;List title of all English movies in descending order of publication year;List name and surname of all people who have had a role of both director and actor in the same film;List name and surname of all people who have acted in a film produced in the same country as their nationality, in ascending order of year of birth.;List the name, surname and year of birth of all people who are both actors and directors.;Create a view for listing name and surname of all people who have acted in more than one movie, indicating the number of movies they have acted in.;Create a view showing all people and the title of the movies they have directed, so that those who have directed no movies have a null value shown against them.;Are the views in (7) and (8) updatable? Why? Give an example of updatable view.;Give an example of correlated nested query on the assignment database, explaining why it is correlated.;Deliverable;Produce a report including;a brief description of the ER model of the database and its mapping into tables;the SQL statements for creating the tables;the SQL statements for populating the tables;the SQL statements for solving the queries (1) to (8);your answer to questions (9) and (10).;data for populating the database;Silence of the Lambs, 1991, USA, 118min, DIRECTOR: Jonathan Demme, 1944, USA ACTORS: Anthony Hopkins, 1937, Welsh Jodie Foster, 1962, USA;Last of the Mohicans, 1992, USA, 122min, DIRECTOR: Michael Mann, 1943, USA ACTORS: Daniel Day-Lewis, 1957, English;Life is Beautiful, 1997, Italian, 124min, DIRECTOR: Roberto Benigni, 1952, Italian, ACTORS: Roberto Benigni, 1952, Italian;The Good, The Bad and The Ugly, 1966, Italian, 180min, DIRECTOR: Sergio Leone, 1929, Italian, ACTORS: Clint Eastwood, 1930, USA, Lee Van Cleef, 1925, USA;Dr. Strangelove, 1964, English, 93min, DIRECTOR: Stanley Kubrick, 1928, USA ACTORS: Peter Sellers, 1925, English;Escape from Alcatraz, 1979, USA, 112min, DIRECTOR: Donald Siegel, 1912, USA, ACTORS: Clint Eastwood, 1930, USA;Eyes Wide Shut, 1999, USA, 160min, DIRECTOR: Stanley Kubrick, 1928, USA, ACTORS: Tom Cruise, 1962, USA Nicole Kidman, 1967, USA;Midnight in the Garden of Good and Evil, 1997, USA, 155min, DIRECTOR: Clint Eastwood, 1930, USA, ACTORS: Kevin Spacey, 1959, USA;American Beauty, 1999, USA, 121min, DIRECTOR: Sam Mendes, 1965, English, ACTORS: Kevin Spacey, 1959, USA
Paper#73568 | Written in 18-Jul-2015Price : $77