MovieMania Database - Report Example

MovieMania database Report GROUP MEMBERS: Table of Contents MovieMania database Report 1 Table of Contents 2 1.Introduction 3 2.Design 4 3.Implemetation 11 4.Reports 33 1. Introduction MovieMania is a new online DVD rental service where users can register by providing their personal details accompanied by their payment via an online website. A user is able to create a list by selecting the desired movies from an online catalogue where one of the movies is delivered to the customer which they should return before another is sent to them. The users are also able to do film reviews from the MovieMania website and provide ratings on a given movie on a scale of 1 to 10. This reviews and ratings together with the user’s list will be used to gather information on the user’s interests and what may be the best movie to suggest to them. The database should allow for movie search using several filters but mainly including actors, directors and genres. In addition to this, the database should allow for storage of data that will generate movie suggestions when certain algorithms are applied. This report addresses the development of the Movie mania database by providing information on the design and implementation of the database as well as extraction of reports and movie suggestion data. It provides samples of the results where a query is run against the database 2. Design 2.1 Entities 2.1.1 Entity Definitions The MovieMania system has several entities that will interact with each other to make the system work as it is intended. The entities include: User This entity stores details on the system users acquiring services from MovieMania DVD rental. UserMovies This entity contains details of a user’s movie. This is the list that a user selects and is used by the rental service to know what the customer wants Movie This entity contains details of a movie including the title, summary and release information Genre This entity contains the details about a genre the movie can belong to. Actor This entity contains details about a movie actor. Director This entity contains details about a movie director. UserReview This entity contains details of a movie review and rating 2.1.2 Entity-Relationship Diagram (ERD) The following Entity-Relationship diagram (ERD) shows the relationship between the entities shown above and their cardinality constraints. 2.2 Business Rule 2.2.1 User and Lists Each User has one List Each list is of one user 2.2.2 List and Movies Each List has zero or more movies A movie is in zero or more lists 2.2.3 Movies and Directors A movie has one or more directors A director can direct one or more movies 2.2.4 Movies and actors A movie has one or more actors An actor can act in one or more movies 2.2.5 Movie and review A movie has zero or more review A review is of one movie 2.2.6 Movie and Genre A genres has zero or more movies A movie belongs to one or more genres 2.3 Data dictionary 2.3.1 Table Actors Field name Description Type PK / FK Reference Table Mandatory ActorID A unique identifier in the actor table Number PK Yes FirstName The actor’s first name TEXT Yes LastName The actor’s last name TEXT Yes DateOfBirth The actor’s date of Birth DATE No Information Any extra information available about the actor TEXT No 2.3.2 Table Directors Field name Description Type PK / FK Reference Table Mandatory DirectorID A unique identifier in the Director table Number PK Yes FirstName The Director’s first name TEXT Yes LastName The Director’s last name TEXT Yes DateOfBirth The Director’s date of Birth DATE No Information Any extra information available about the Director TEXT No 2.3.3 Table Users Field name Description Type PK / FK Reference Table Mandatory UserID A unique identifier in the user table Number PK Yes FirstName The user’s first name TEXT Yes LastName The user’s last name TEXT Yes DateOfBirth The user’s date of Birth DATE No Information Any extra information available about the user TEXT No 2.3.4 Table Genres Field name Description Type PK / FK Reference Table Mandatory GenreID A unique identifier in the Genre table Number PK Yes Name Name of the genre TEXT Yes Description A description of the genres TEXT No 2.3.5 Table Certificates Field name Description Type PK / FK Reference Table Mandatory CertificateID A unique identifier in the Certificate table Number PK Yes Name Name of the certificate TEXT Yes Description A description of the certificate TEXT No 2.3.6 Table Movies Field name Description Type PK / FK Reference Table Mandatory MovieID A unique identifier in the movies table Number PK Yes Title The title of the movie TEXT Yes YearOfRelease The year when the movie was released Number Yes Duration The length in minutes of the movie Number Yes Description A short description of the movie Text No GenreID The foreign key to genres tables indicating the genre of the movie Number FK Genres Yes CertificateID The foreign key to certificates tables indicating the certification of the movie Number FK Certificate Yes 2.3.7 Table MovieDirectors Field name Description Type PK / FK Reference Table Mandatory MovieDirectorID A unique identifier in the MovieDirector table Number PK Yes MovieID The foreign key to Movies tables indicating the movie being directed Number FK Movies Yes DirectorID The foreign key to Directors tables indicating the person directing the movie Number FK Director Yes 2.3.8 Table MovieActors Field name Description Type PK / FK Reference Table Mandatory MovieActorID A unique identifier in the MovieActor table Number PK Yes MovieID The foreign key to Movies tables indicating the movie being directed Number FK Movies Yes ActorID The foreign key to Actors tables indicating the person directing the movie Number FK Actor Yes 2.3.9 Table UserMovies Field name Description Type PK / FK Reference Table Mandatory UserMovieID A unique identifier in the UserMovies table Number PK Yes MovieID The foreign key to Movies tables indicating the movie being directed Number FK Movies Yes UserID The foreign key to Users tables indicating the person directing the movie Number FK Users Yes 2.3.10 Table UserReviews Field name Description Type PK / FK Reference Table Mandatory UserReviewID A unique identifier in the UserMovies table Number PK Yes Title Title of the review Text Yes Content Content of the review Text Yes Rating This is the rating of the movie from 1-10 according to the user Number Yes MovieID The foreign key to Movies tables indicating the movie being directed Number FK Movies Yes UserID The foreign key to Users tables indicating the person directing the movie Number FK Users Yes 3. Implemetation 3.1 Create Tables 3.1.1 Create Table Actors This table is supposed to store actors who may or may have not featured in a given movie. The table contains several mandatory and non mandatory fields due to the possibility of lack of information. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table Actors Add an auto increment column which is the primary key and should never be empty Add four other columns with the different data types CREATE TABLE Actors ( ActorID AUTOINCREMENT PRIMARY KEY NOT NULL, FirstName TEXT (50) NOT NULL, LastName TEXT (30) NOT NULL, DateOfBirth Date, Information Memo ); 3.1.2 Create Table Certificates This table is supposed to store the different kinds of certificates available for movies. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table Certificates Add an auto increment column which is the primary key and should never be empty Add two other columns with the different data types CREATE TABLE Certificates ( CertificateID AUTOINCREMENT PRIMARY KEY NOT NULL, Name TEXT (50) NOT NULL, Code TEXT(10) NOT NULL, CertificateDescription memo ); 3.1.3 Create Table Directors This table is supposed to store movie directors who directed in a given movie. The table contains several mandatory and non mandatory fields due to the possibility of lack of information. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table Directors Add an auto increment column which is the primary key and should never be empty Add four other columns with the different data types CREATE TABLE Directors ( DirectorID AUTOINCREMENT PRIMARY KEY NOT NULL, FirstName TEXT (50) NOT NULL, LastName TEXT (30) NOT NULL, DateOfBirth Date, Information Memo ); 3.1.4 Create Table Genres This table is supposed to store Genres that a movie may be in. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table Genres Add an auto increment column which is the primary key and should never be empty Add two other columns with the different data types CREATE TABLE Genres ( GenreID AUTOINCREMENT PRIMARY KEY NOT NULL, Name TEXT (50) NOT NULL, Description Memo ); 3.1.5 Create Table Movies This table is supposed the movies available in the rental service. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table Movies Add an auto increment column which is the primary key and should never be empty Add four other columns with the different data types Add two foreign keys to the table Genres and Certificates CREATE TABLE Movies ( MovieID AUTOINCREMENT PRIMARY KEY NOT NULL, Title TEXT (50) NOT NULL, YearOfRelease INTEGER NOT NULL, Duration INTEGER NOT NULL, Description Memo, GenreID Integer REFERENCES Genres (GenreID) NOT NULL, CertificateID Integer REFERENCES Certificates (CertificateID) NOT NULL ); 3.1.6 Create Table MovieActors This is a mapping table between the movies and actors. It stores information on which actor acted in a particular movie. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table MovieActors Add an auto increment column which is the primary key and should never be empty Add two other columns with the different data types that reference table Movies and Actors CREATE TABLE MovieActors ( MovieActorID AUTOINCREMENT PRIMARY KEY NOT NULL, ActorID INTEGER REFERENCES Actors (ActorID) NOT NULL, MovieID INTEGER REFERENCES Movies (MovieID) NOT NULL ); 3.1.7 Create Table MovieDirectors This is a mapping table between the movies and directors which stores information on which director directed a particular movie. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table MoviesDirector Add an auto increment column which is the primary key and should never be empty Add two other columns with the different data types that reference table Movies and Directors CREATE TABLE MovieDirectors ( MovieDirectorID AUTOINCREMENT PRIMARY KEY NOT NULL, DirectorID INTEGER REFERENCES Directors (DirectorID) NOT NULL, MovieID INTEGER REFERENCES Movies (MovieID) NOT NULL ); 3.1.8 Create Table Users This table is supposed to store users utilizing the MovieMania rental service. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table Users Add an auto increment column which is the primary key and should never be empty Add four other columns with the different data types CREATE TABLE Users ( UserID AUTOINCREMENT PRIMARY KEY NOT NULL, FirstName TEXT (50) NOT NULL, LastName TEXT (30) NOT NULL, DateOfBirth DATE NOT NULL, Information Memo ); 3.1.9 Create Table UserMovies This is a mapping table between Users and Movies which should provide information on the list of movies that a user has selected and the status of delivery. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table MovieUsers Add an auto increment column which is the primary key and should never be empty Add four two columns with the different data types Add two references to table Movies and Users CREATE TABLE UserMovies ( UserMovieID AUTOINCREMENT PRIMARY KEY NOT NULL, Status TEXT (20) NOT NULL, DateOfStateChange DATETIME NOT NULL, UserID INTEGER REFERENCES Users (UserID) NOT NULL, MovieID INTEGER REFERENCES Movies (MovieID) NOT NULL ); 3.1.10 Create Table UserReviews This is a mapping table between Users and Movies which should provide information on the review a user has added and the rating of the movie. It has a primary key which is an auto increment field. The SQL statement below has been used to create the table. In brief this is what the SQL statement does Create a table MovieReviews Add an auto increment column which is the primary key and should never be empty Add four three columns with the different data types Add two references to table Movies and Users CREATE TABLE UserReviews ( UserReviewID AUTOINCREMENT PRIMARY KEY NOT NULL, Title TEXT (50) NOT NULL, Content Memo NOT NULL, MovieRating INTEGER NOT NULL, UserID INTEGER REFERENCES Users (UserID) NOT NULL, MovieID INTEGER REFERENCES Movies (MovieID) NOT NULL ); 3.2 Data 3.2.1 Actor Table Data ActorID FirstName LastName DateOfBirth Information 2 Celia Johnson     3 Cyril Raymond     4 Stanley Holloway     5 Trevor Howard     6 Claude Rains     7 Humphrey Bogart     8 Ingrid Bergman     9 Paul Henreid     10 Ethan Hawke     11 Julie Delpy     12 Jean Seberg     13 Jean-Paul Belmondo     14 Maggie Cheung Man-Yuk     15 Rebecca Pan     16 Tony Leung Chiu-Wai     17 Faye Dunaway     18 Jack Nicholson     19 John Huston     20 Charlton Heston     21 Janet Leigh     22 Marlene Dietrich     23 Orson Welles     24 Zsa Zsa Gabor     25 Barbara Bel Geddes     26 James Stewart     27 Kim Novak     28 Alan Vint     29 Martin Sheen     30 Ramon Bieri     31 Sissy Spacek     32 Warren Oates     33 Machiko Kyo     34 Masayuki Mori     35 Toshiro Mifune     36 Carol Kane     37 Diane Keaton     38 Paul Simon     39 Tony Roberts     40 Woody Allen     41 Ken Davitian     42 Pamela Anderson     43 Sacha Baron Cohen     44 George Raft     45 Jack Lemmon     46 Joe E Brown     47 Marilyn Monroe     48 Tony Curtis     49 Kristen Miller     50 Matt Stone     51 Trey Parker     52 George C Scott     53 Peter Sellers     54 Sterling Hayden     55 Dennis Hopper     56 Frederic Forrest     57 Laurence Fishburne     58 Marlon Brando     61 Robert Duvall     62 Cary Grant     63 Eva Marie Saint     64 James Mason     65 Jessie Royce Landis     66 Leo G Carroll     67 Martin Landau     68 Charles Bronson     69 Claudia Cardinale     70 Henry Fonda     71 Jason Robards     72 Ernest Borgnine     73 Robert Ryan     74 William Holden     75 Burt Reynolds     76 Jon Voight     77 Ned Beatty     3.2.2 Certificate Table Data CertificateID Name Code CertificateDescription 2 Universal U All ages admitted, there is almost nothing unsuitable for children aged 4 years and over. 3 Parental Guidance PG All ages admitted, but certain scenes may be unsuitable for young children. Should not disturb children aged 8 years or over. May contain mild language and sex/drugs references. May contain moderate violence if justified by context (e.g. fantasy). Parents may wish to check the film before they let their children watch it. 4 12A 12A This category applies to cinema only releases since 2002. Films under this category are considered to be unsuitable for young children. Cinemas are only permitted to supply tickets to see a 12A-rated film to children under the age of 12 years if they are accompanied by an adult, aged at least 18 years. However, it is unlikely to be suitable for children under the age of 12. Films under this category can contain mature themes, discrimination, soft drugs, infrequent strong language, very strong language (introduced in 2015) and moderate violence, sex references and nudity. Sexual activity may be briefly and discreetly portrayed. Sexual violence may be implied or briefly indicated. 5 12 12 This category applies to cinema releases from August 1989 to 2002, and home media since 1994. It is suitable only for persons aged 12 years and over, not to be supplied to anyone below that age. It is illegal to knowingly rent or sell a 12-rated VHS, DVD, Blu-ray Disc, UMD or game to anyone under the age of 12. 12A-rated films are usually given a 12 certificate for the VHS/DVD version unless extra material has been added that requires a higher rating. The content guidelines are identical to those used for the 12A certificate. 6 15 15 Suitable only for persons aged 15 years and over, not to be supplied to anyone below that age. It is illegal to knowingly rent or sell a 15-rated VHS, DVD, Blu-ray Disc, UMD or game, or supply cinema tickets for a film with this rating, to anyone under the age of 15. Films under this category can contain adult themes, hard drugs, regular use of foul language and limited use of very strong language, strong violence and strong sex references, and nudity without graphic detail. Sexual activity may be portrayed but without any strong detail. Sexual violence may be shown if discreet and justified by context. 7 18 18 Suitable only for persons aged 18 years and over, not to be supplied to anyone below that age. It is illegal to knowingly rent or sell an 18-rated VHS, DVD, Blu-ray Disc, UMD or game, or supply cinema tickets for a film with this rating, to anyone under the age of 18. Films under this category do not have limitation on the foul language that is used. Hard drugs are generally allowed, and explicit sex references along with detailed sexual activity are also allowed. Scenes of strong real sex may be permitted if justified by the context. Very strong, gory, and/or sadistic violence is usually permitted. Strong sexual violence is permitted unless it is eroticized or excessively graphic. 8 Restricted 18 R18 Can only be shown at licensed adult cinemas or sold at licensed sex shops. It is illegal to sell a film with this rating to anyone under the age of 18, and it is illegal to sell a film with this rating by means of mail order, or online or by telephone. Films under this category always contain hard-core pornography, defined as material intended for sexual stimulation and containing clear images of real sexual activity, strong fetish material, explicit animated images, or sight of certain acts such as triple simultaneous penetration and snowballing. There remains a range of material that is often cut from the R18 rating: strong images of injury in BDSM or spanking works, urolagnia, scenes suggesting incesteven if staged, references to underage sex or childhood sexual development and aggressive behavior such as hair-pulling or spitting on a performer are not permitted. More cuts are demanded in this category than any other category.[28] 3.2.3 Director Table Data DirectorID FirstName LastName DateOfBirth Information 2 Francis Coppola     3 Alfred Hitchcock     4 Sergio Leone     5 Sam Pekinpah     6 John Boorman     7 Woody Allen     8 Larry Charles     9 Billy Wilder     10 Trey Parker     11 Stanley Kubrick     12 Roman Polanski     13 Orson Welles     15 Terrence Malik     16 Akira Kurosawa     18 Orson Welles     22 David Lean     23 Michael Curtiz     24 Richard Linklater     26 Jean-Luc Godard     3.2.4 Genre Table Data GenreID Name Description 1 Romance  Romantic movies 2 Crime  Story revolves around crime 3 Comedy  Funny 4 Action  A lot of fight scenes 5 Arthouse and Drama   6 Sci-Fi and Fantasy  Science fiction 7 Horror  Horrifying events 3.2.5 Movie Table Data MovieID Title YearOfRelease Duration Description GenreID CertificateID 34521313 Brief Encounter 1945 110   1 3 34521314 Casablanca 1942 130   1 5 34521315 Before Sunrise 1995 115   1 5 34521316 Before Sunset 2004 118   1 6 34521317 Breathless 1960 120   1 5 34521318 In the Mood for Love 2000 114   1 4 34521319 Chinatown 1974 115   2 5 34521320 Touch of Evil 1958 133   2 6 34521321 Vertigo 1958 117   2 7 34521322 Badlands 1973 140   2 6 34521323 Rashomon 1950 119   2 7 34521324 Annie Hall 1977 120   3 3 34521325 Borat 2006 121   3 4 34521326 Some Like it Hot 1959 122   3 4 34521327 Team America 2004 130   3 2 34521328 Dr Strangelove 1964 124   3 3 34521329 Apocalypse Now 1979 125   4 4 34521330 North by Northwest 1959 126   4 3 34521331 Once Upon a Time in the West 1968 141   4 3 34521332 The Wild Bunch 1969 128   4 2 34521333 Deliverance 1972 129   4 3 3.2.6 MovieDirector table Data MovieDirectorID DirectorID MovieID 1 2 34521329 2 22 34521313 3 23 34521314 4 24 34521315 5 24 34521316 6 26 34521317 7 3 34521330 8 4 34521331 9 5 34521332 10 6 34521333 11 7 34521324 13 8 34521325 14 9 34521326 15 10 34521327 16 11 34521328 17 12 34521319 18 13 34521320 20 3 34521321 21 15 34521322 22 16 34521323 3.2.7 MovieActor Table Data MovieActorID ActorID MovieID 1 17 34521319 2 18 34521319 3 19 34521319 4 6 34521314 5 7 34521314 6 8 34521314 7 9 34521314 8 2 34521313 9 3 34521313 10 4 34521313 11 5 34521313 12 10 34521315 13 11 34521315 14 10 34521316 15 11 34521316 16 12 34521317 17 13 34521317 18 14 34521318 19 15 34521318 20 20 34521320 21 21 34521320 22 22 34521320 23 23 34521320 24 24 34521320 25 25 34521321 26 26 34521321 27 27 34521321 28 28 34521322 29 29 34521322 30 30 34521322 31 31 34521322 32 32 34521322 33 33 34521323 34 34 34521323 35 35 34521323 36 36 34521324 37 37 34521324 38 38 34521324 39 39 34521324 40 40 34521324 41 41 34521325 42 42 34521325 43 43 34521325 44 44 34521326 45 45 34521326 46 48 34521326 47 47 34521326 48 48 34521326 49 49 34521327 50 50 34521327 51 51 34521327 52 52 34521328 53 53 34521328 54 54 34521328 55 55 34521329 56 56 34521329 57 57 34521329 58 58 34521329 59 61 34521329 60 62 34521330 61 63 34521330 62 64 34521330 63 65 34521330 64 66 34521330 65 67 34521330 66 68 34521331 67 69 34521331 68 70 34521331 69 71 34521331 70 72 34521332 71 73 34521332 72 74 34521332 73 75 34521333 74 76 34521333 75 77 34521332 3.2.8 User table Data UserID FirstName LastName Gender DateOfBirth Information 1 John Doe Male 02-Apr-90   2 Jane Doe Female 01-Mar-95   3 Morgan Davids Male 02-Aug-80   4 Grace Katelin Female 10-Jul-88   5 Paul Daniels Male 15-Aug-91   6 Richard Renolds Male 12-Nov-95   7 Victoria Walts Female 11-Feb-83   8 Rachel Oliver Female 22-Jan-93   9 Angie Winslet Female 24-Dec-90   10 Trevor Wilfred Male 04-Jul-92   3.2.9 UserMovies Table Data UserMovieID Status DateOfStateChange UserID MovieID 1 New 18-Apr-15 1 34521319 2 New 10-Apr-15 1 34521320 3 Delivered 10-Apr-15 9 34521333 4 Returned 13-Apr-15 1 34521321 5 New 08-Apr-15 5 34521331 6 New 02-Apr-15 6 34521330 7 Delivered 03-Apr-15 3 34521324 8 Returned 10-Apr-15 2 34521328 9 Delivered 19-Apr-15 7 34521319 10 New 15-Apr-15 4 34521318 11 New 01-Mar-15 2 34521333 12 Returned 10-Apr-15 9 34521313 13 New 09-Apr-15 10 34521315 14 New 10-Apr-15 6 34521331 15 Returned 03-Mar-15 5 34521330 16 New 02-Apr-15 1 34521326 17 New 09-Apr-15 1 34521322 18 New 15-Apr-15 8 34521319 19 New 19-Apr-15 8 34521320 20 New 19-Apr-15 8 34521321 22 New 15-Apr-15 9 34521321 23 New 02-Apr-15 3 34521333 24 Delivered 03-Apr-15 4 34521333 25 New 08-Apr-15 7 34521333 26 New 11-Mar-15 5 34521332 27 Delivered 16-Apr-15 8 34521332 28 Returned 19-Mar-15 10 34521332 29 Returned 02-Apr-15 10 34521329 30 New 08-Apr-15 8 34521329 31 Delivered 08-Apr-15 3 34521329 32 Delivered 09-Apr-15 2 34521330 33 New 08-Apr-15 8 34521330 34 Delivered 08-Apr-15 8 34521326 3.2.10 UserReview Table Data UserReviewID Title Content MovieRating UserID MovieID 1 Awesome Best classic av seen yet 8 1 34521321 2 Nice This is a very good movie. The story, the drama, the casting… 10 1 34521333 3 Funny Great Movie, lots odf hilarious stuff 7 4 34521325 4 Not so Cool Content is a bit childish 4 3 34521325 5 Funny Hilarious 8 2 34521325 6 Classic This is a great all time romantic movie 10 3 34521314 7 Boring Couldn’t stay awake 3 8 34521314 8 Great This is still a great movie to this data 8 7 34521314 9 Funny Seriously funny 8 8 34521327 10 Great Good Copy and nice moview 7 5 34521331 11 Thumb Down Not Good 3 2 34521328 12 Great Great 10 7 34521323 13 Good Enough Good enough 6 1 34521319 14 Average Average 10 6 34521328 15 Great Movie This is what perfection looks like 10 1 34521330 16 Really funny This movie made my ribs crack out of laughter :D 10 8 34521325 4. Reports 4.1 Cinema Seek Reports 4.1.1 Get Actors and movies by Directors For this situation, a user is allowed to a director into the system and they should see a list of movie in which the director has directed and also the actors who have acted in that particular movie. The results should also be limited in a way that the user will not be overwhelmed with information at one go. The query below has been used to allow the data to be extracted. In this case, the director’s first and last names will be required so as to search the database for movie that have been directed by this director. SELECT TOP 100 Directors.FirstName + " " + Directors.LastName AS [Director Name], Actors.FirstName + " " + Actors.LastName AS [Actor Name], Movies.Title AS [Movie Title] FROM (((Directors INNER JOIN MovieDirectors ON Directors.DirectorID=MovieDirectors.DirectorID) INNER JOIN Movies ON Movies.MovieID=MovieDirectors.MovieID) LEFT JOIN MovieActors ON Movies.MovieID=MovieActors.MovieID) LEFT JOIN Actors ON MovieActors.ActorID=Actors.ActorID WHERE Directors.FirstName="" + [Director's First Name] AND Directors.LastName="" + [Director's Last Name]; This is an explanation of the query above: Select the top 100 records from a dataset obtained by: Join the directors and movies that they has directed using the MovieDirecotor’s mapping table Then joining this dataset with the actors using the MovieActor’s mapping table. The search from this joined dataset where the director’s first name is equel to an input provided by user and the last name is equal to another input provided by the user Using the query above and the Director’s first name as Alfred and Director’s last name as Hitchcock the following dataset was obtained. Director Name Actor Name Movie Title Alfred Hitchcock Cary Grant North by Northwest Alfred Hitchcock Eva Marie Saint North by Northwest Alfred Hitchcock James Mason North by Northwest Alfred Hitchcock Jessie Royce Landis North by Northwest Alfred Hitchcock Leo G Carroll North by Northwest Alfred Hitchcock Martin Landau North by Northwest Alfred Hitchcock Barbara Bel Geddes Vertigo Alfred Hitchcock James Stewart Vertigo Alfred Hitchcock Kim Novak Vertigo If there is no director name input, the dataset is as shown below. Note that you can also user the DirectorID field to search for the data but that will require to know which director is identified by which ID prior to the query. Director Name Actor Name Movie Title Francis Coppola Dennis Hopper Apocalypse Now Francis Coppola Frederic Forrest Apocalypse Now Francis Coppola Laurence Fishburne Apocalypse Now Francis Coppola Marlon Brando Apocalypse Now Francis Coppola Robert Duvall Apocalypse Now David Lean Celia Johnson Brief Encounter David Lean Cyril Raymond Brief Encounter David Lean Stanley Holloway Brief Encounter David Lean Trevor Howard Brief Encounter Michael Curtiz Claude Rains Casablanca Michael Curtiz Humphrey Bogart Casablanca Michael Curtiz Ingrid Bergman Casablanca Michael Curtiz Paul Henreid Casablanca Richard Linklater Ethan Hawke Before Sunrise Richard Linklater Julie Delpy Before Sunrise Richard Linklater Ethan Hawke Before Sunset Richard Linklater Julie Delpy Before Sunset Jean-Luc Godard Jean Seberg Breathless Jean-Luc Godard Jean-Paul Belmondo Breathless Alfred Hitchcock Cary Grant North by Northwest Alfred Hitchcock Eva Marie Saint North by Northwest Alfred Hitchcock James Mason North by Northwest Alfred Hitchcock Jessie Royce Landis North by Northwest Alfred Hitchcock Leo G Carroll North by Northwest Alfred Hitchcock Martin Landau North by Northwest Sergio Leone Charles Bronson Once Upon a Time in the West Sergio Leone Claudia Cardinale Once Upon a Time in the West Sergio Leone Henry Fonda Once Upon a Time in the West Sergio Leone Jason Robards Once Upon a Time in the West Sam Pekinpah Ernest Borgnine The Wild Bunch Sam Pekinpah Robert Ryan The Wild Bunch Sam Pekinpah William Holden The Wild Bunch Sam Pekinpah Ned Beatty The Wild Bunch John Boorman Burt Reynolds Deliverance John Boorman Jon Voight Deliverance Woody Allen Carol Kane Annie Hall Woody Allen Diane Keaton Annie Hall Woody Allen Paul Simon Annie Hall Woody Allen Tony Roberts Annie Hall Woody Allen Woody Allen Annie Hall Larry Charles Ken Davitian Borat Larry Charles Pamela Anderson Borat Larry Charles Sacha Baron Cohen Borat Billy Wilder George Raft Some Like it Hot Billy Wilder Jack Lemmon Some Like it Hot Billy Wilder Tony Curtis Some Like it Hot Billy Wilder Marilyn Monroe Some Like it Hot Billy Wilder Tony Curtis Some Like it Hot Trey Parker Kristen Miller Team America Trey Parker Matt Stone Team America Trey Parker Trey Parker Team America Stanley Kubrick George C Scott Dr Strangelove Stanley Kubrick Peter Sellers Dr Strangelove Stanley Kubrick Sterling Hayden Dr Strangelove Roman Polanski Faye Dunaway Chinatown Roman Polanski Jack Nicholson Chinatown Roman Polanski John Huston Chinatown Orson Welles Charlton Heston Touch of Evil Orson Welles Janet Leigh Touch of Evil Orson Welles Marlene Dietrich Touch of Evil Orson Welles Orson Welles Touch of Evil Orson Welles Zsa Zsa Gabor Touch of Evil Alfred Hitchcock Barbara Bel Geddes Vertigo Alfred Hitchcock James Stewart Vertigo Alfred Hitchcock Kim Novak Vertigo Terrence Malik Alan Vint Badlands Terrence Malik Martin Sheen Badlands Terrence Malik Ramon Bieri Badlands Terrence Malik Sissy Spacek Badlands Terrence Malik Warren Oates Badlands Akira Kurosawa Machiko Kyo Rashomon Akira Kurosawa Masayuki Mori Rashomon Akira Kurosawa Toshiro Mifune Rashomon 4.1.2 Get User’s Movie Statistics For this report, statistics are required about a user’s movies as selected in the database. The statistics provided in the number of movies within a genre that has been certified and what certification it has been given. Do get this data, the following query was used: SELECT UserMovieList.GenreName, UserMovieList.CertificateName AS CertificateName, UserMovieList.Code, Count(*) AS CertificateCount FROM ( SELECT Users.LastName, Movies.Title, Genres.Name AS GenreName, Certificates.Code, Certificates.Name AS CertificateName FROM (((Users INNER JOIN UserMovies ON UserMovies.UserID=Users.UserID) INNER JOIN Movies ON Movies.MovieID=UserMovies.MovieID) LEFT JOIN Certificates ON Movies.CertificateID=Certificates.CertificateID) LEFT JOIN Genres ON Movies.GenreID=Genres.GenreID ) AS UserMovieList GROUP BY UserMovieList.GenreName, UserMovieList.CertificateName, UserMovieList.Code; The following is an explanation of the query above: Get the genre, certification and count of movies in each certification from a sub query which: Gets the Genre, Certification of all movies belonging to a user by: Joining the users to the movies using the UserMovies mapping table Then joining the movies dataset to genres and Certification And limiting this to a given user ID In this example user with user ID 8 has been used. GenreName CertificateName Code CertificateCount Comedy 12A 12A 1 Crime 15 15 3 Crime 18 18 1 An in this example, In this example user with user ID 1 has been used. GenreName CertificateName Code CertificateCount Action 12 12 1 Action 12A 12A 1 Action Parental Guidance PG 1 Comedy 12A 12A 1 Crime 15 15 2 Crime 18 18 1 If no user ID is provided, the data set returned will give the total count of all the certified movies in different genres selected by users. This may show the more popular movies being requested for. GenreName CertificateName Code CertificateCount Action 12 12 3 Action 12A 12A 3 Action Parental Guidance PG 11 Comedy 12A 12A 2 Comedy Parental Guidance PG 2 Crime 15 15 6 Crime 18 18 3 Romance 12 12 1 Romance 12A 12A 1 Romance 15 15 1 4.1.3 Get Movie Rating For this report, the most popular movies were to be obtained. This would require the user ratings for the movies as provided by users during their movie reviews. Then an average of the total users rating per movie is applied in a descending order. For this report, two queries were used. One was to get a rating of all the movies regardless of the genre they are in while the other obtained rating of movies per genre. If a movie has not been rated by any user, it does will be at the bottom of the dataset. User rating for movies SELECT MovieList.MovieTitle, MovieList.CertificateCode, MovieList.UserRating AS UserRating FROM (SELECT Movies.Title AS MovieTitle, Certificates.Code AS CertificateCode, AVG (UserReviews.[MovieRating]) AS UserRating FROM (Movies INNER JOIN UserReviews ON UserReviews.MovieID=Movies.MovieID) LEFT JOIN Certificates ON Certificates.CertificateID=Movies.CertificateID GROUP BY Movies.Title, Certificates.Code) AS MovieList ORDER BY MovieList.UserRating DESC; The following is an explanation of the query above: Select the movie title the certificate simple and user rating from a dataset obtains by using the sub query that: Selects movies and joins them to the reviews using the UserReviews Mapping table Get the average of the total user ratings per movie (grouped) Join this to the table Certificates Movie Title Certificate Symbol User Rating Rashomon 18 10 North by Northwest PG 10 Deliverance PG 10 Vertigo 18 8 Team America U 8 Borat 12A 7.25 Once Upon a Time in the West PG 7 Casablanca 12 7 Dr Strangelove PG 6.5 Chinatown 15 6 User rating for movies by Genre SELECT MovieList.GenreName AS [Movie Genre], MovieList.MovieTitle AS [Movie Title], MovieList.CertificateCode AS [Certificate Symbol], MovieList.UserRating AS [User Rating] FROM (SELECT Movies.Title AS MovieTitle, Certificates.Code AS CertificateCode, Genres.Name AS GenreName, AVG(UserReviews.[MovieRating]) AS UserRating FROM ((Movies INNER JOIN Genres ON Genres.GenreID=Movies.GenreID) INNER JOIN Certificates ON Certificates.CertificateID=Movies.CertificateID) LEFT JOIN UserReviews ON UserReviews.MovieID=Movies.MovieID GROUP BY Genres.Name, Movies.Title, Certificates.Code) AS MovieList ORDER BY MovieList.GenreName, MovieList.UserRating DESC; The following is an explanation of the query above: Select the movie title the certificate simple and user rating from a dataset obtains by using the sub query that: Selects movies and joins them to the reviews using the UserReviews Mapping table Join the movie dataset to genres and Certificates then group by genre Get the average of the total user ratings per movie (grouped by genre) The result for the query above is as shown Movie Genre Movie Title Certificate Symbol User Rating Action Deliverance PG 10 Action North by Northwest PG 10 Action Once Upon a Time in the West PG 7 Action The Wild Bunch 12   Action Apocalypse Now 12A   Comedy Team America U 8 Comedy Borat 12A 7.25 Comedy Dr Strangelove PG 6.5 Comedy Annie Hall PG   Comedy Some Like it Hot 12A   Crime Rashomon 18 10 Crime Vertigo 18 8 Crime Chinatown 15 6 Crime Touch of Evil 15   Crime Badlands 15   Romance Casablanca 12 7 Romance In the Mood for Love 12A   Romance Before Sunrise 12   Romance Before Sunset 15   Romance Breathless 12   Romance Brief Encounter 15   4.2 Film forage Read More