Simple Movie Database

1
Simple Movie Database

• Ankur Kandikatla
• Rumana Islam
• Rohan Venkatraman

2
Real World Application

• This movie database contains basic information on
  the movie, its actors, genre, director(s) and
  awards
• Allows the user to perform a number of queries on
  the database to extract information.
• For example The user can search for specific
  movies of choice in terms of genre.
• The database consists of 5 tables interlinked to
  each other.

3
Description

• Actors Table
• This table provides basic information on all
  actors in the sample data provided.
• It contains their first and last names, gender,
  date of birth and death (if applicable).
• Primary key is DMActorID.
• Movies Table
• The movies table is similar to that of the actor
  table.
• It contains the movie name, production year,
  release company and genre provided. There is also
  a foreign key to the directors table that allows
  us to find who directed each movie.
• Primary Key is DMMovieID

4
Description

• ActorsInMovie Table
• This table links the Actors and Movies Table
  together.
• The primary key is a composite primary key
  consisting of two foreign keys DMActorID and
  DMMovieID taken from the Actors and Movies table.
  
• Directors Table
• This table contains information about Directors.
• It has the Directors First and Last name Birth
  and possible death.
• Its primary key is DMDirectorsID.

5
Description

• Awards Table
• The Awards table contains the awards from the
  Academy and Golden Globes that are related to
  movies, actors and directors. This table has
  three foreign keys one each from Actors, Movies
  and Directors.

6
DallehMovies2005 ERD
DALLEHMOVIES2005DIRECTORS DMDirectorID DMDirFirst
Name DMDirLastName DMDirBirth DMDirDeath
directs
Has acted
acts
awarded
DALLEHMOVIES2005AWARDS DMAwardsID DMMovieID DMAc
torID DMDirectorID DMAwardName DMAwardType
awarded
awarded
7
SQL Queries on a Single Entity/ Table
8
The Projection Concept
Movie names and year released SELECT
DMMovieName, DMMovieYear FROM DALLEHMOVIES2005MOV
IES
9
The Use of WHERE

• Best Actor award winners
• SELECT
• FROM DALLEHMOVIES2005AWARDS
• WHERE DMAwardName 'Best Actor (win)'

10
Project and Restrict Combination

• Movies in the Science Fiction Genre
• SELECT DMMovieID, DMDirectorID, DMMovieName,
  DMMovieGenre, DMMovieYear, DMMovieReleaseCompan
  y
• FROM DALLEHMOVIES2005MOVIES
• WHERE DMMovieGenre 'Science Fiction'

11
IS NOT NULL

• Best Director awards
• SELECT DMMovieID, DMActorID, DMDirectorID,DMAwardI
  D, DMAwardName, DMAwardType
• FROM DALLEHMOVIES2005AWARDS
• WHERE DMDirectorID IS NOT NULL

12
NULL

• Directors still alive
• SELECT dmdirfirstname, dmdirlastname, dmdirbirth
  from
• DALLEHMOVIES2005DIRECTORS WHERE dmdirdeath IS
  NULL

13
IN

• Drama Movies
• SELECT
• FROM DALLEHMOVIES2005MOVIES
• WHERE DMMovieGenre IN ('Drama')

14
NOT IN

• Movies other than Drama
• SELECT
• FROM DALLEHMOVIES2005MOVIES
• WHERE DMMovieGenre NOT IN ('Drama')

15
AVG Function

• Average birth year of actors
• SELECT AVG(DMActorBirth) as AverageBirthDate
• FROM DALLEHMOVIES2005ACTORS

16
COUNT()

• Total Number of Actors
• SELECT COUNT()
• FROM DALLEHMOVIES2005ACTORS

17
COUNT (X)

• Total number of actors
• SELECT COUNT(DMActorID)
• FROM DALLEHMOVIES2005ACTORS

18
DISTINCT

• Award Winning Directors
• SELECT COUNT(DISTINCT DMDirectorID)
• FROM DALLEHMOVIES2005AWARDS

19
LIKE

• Directors with Names starting with C
• SELECT DMDirFirstName
• FROM DALLEHMOVIES2005DIRECTORS
• WHERE DMDirFirstName LIKE 'C'

20
INSERT Statement

• INSERT INTO DALLEHMOVIES2005DIRECTORS VALUES (
  10, Francis', Coppola', 1939, NULL)

21
Foreign Keys and Natural Joins
22
DALLEHMOVIES2005DIRECTORS
Primary keys
DALLEHMOVIES2005MOVIES
Foreign keys
23
SELECT DMMovieID, DMMovieName, DMDirFirstName,
DMDirLastNameFROM DALLEHMOVIES2005MOVIES Natural
Join DALLEHMOVIES2005DIRECTORSWHERE
DMMovieGenre 'Drama'
NATURAL JOIN
Movie ID, name of movie and director of movie
genre Drama using natural join operator.
24
Cross Product
Movie ID, name of movie and director of movie
genre Drama using cross product form.

• SELECT DMMovieID, DMMovieName, DMDirFirstName,
  DMDirLastName
• FROM dallehmovies2005movies, dallehmovies2005direc
  tors
• WHERE DALLEHMOVIES2005MOVIES.DMDirectorID
  DALLEHMOVIES2005DIRECTORS.DMDirectorID
• AND DMMovieGenre 'Drama'

25
Disambiguation between same names

• Actor ID, Full Name, Last Name, Movie Name and
  Award type of all female actors who have won and
  award
• SELECT DALLEHMOVIES2005AWARDS.DMActorID,
  DMActorFirstName, DMActorLastName, DMMovieName
• FROM DALLEHMOVIES2005ACTORS,DALLEHMOVIES2005AWARDS
  , DALLEHMOVIES2005MOVIES
• WHERE Dallehmovies2005Actors.DMActorID
  Dallehmovies2005Awards.DMActorID
• and Dallehmovies2005Awards.DMMovieID
  DallehMovies2005Movies.DMMovieID
• and DMActorGender 'F'

26
Foreign Keys and Relationships
27
1m Relationship
Director Movies
DALLEHMOVIES2005MOVIES
DALLEHMOVIES2005DIRECTORS
Foreign key
28
mm Relationship
Actors Movies
DALLEHMOVIES2005ACTORS
DALLEHMOVIES2005ACTORSinMOVIE
mm relationship
DALLEHMOVIES2005MOVIES
29
Group by, sub-queries and complex joins
30
Group By Without HAVING

• Number of movies released on a particular year
• SELECT DMMovieYear, count ()
• FROM DALLEHMOVIES2005MOVIES
• GROUP BY DMMovieYear ORDER BY DMMovieYear

31
Group By with HAVING

• Movie years where more than 1 movie was released
• SELECT DMMovieYear, COUNT ()
• FROM DALLEHMOVIES2005MOVIES
• GROUP BY DMMovieYear HAVING count() 1
• ORDER BY DMMovieYear

32
Subquery

• List of actors first name and last name and
  movie where they have worked with the actor
  Morgan Freeman.
• SELECT DMActorfirstname, DMActorlastname,
  DMMoviename
• FROM dallehmovies2005actorsinmovieNATURAL JOIN
  dallehmovies2005movies NATURAL JOIN
  dallehmovies2005actors
• WHERE DMMovieID IN
• (Select DMMovieID FROM Dallehmovies2005actorsinmov
  ie NATURAL JOIN dallehmovies2005movies NATURAL
  JOIN dallehmovies2005actors WHERE DMactorID 3)
• AND DMactorid 3

33
MAX and MIN Subqueries

• First and last names of youngest actors
• SELECT DMActorfirstname, DMActorlastname,
  DMActorbirth
• FROM DALLEHMOVIES2005ACTORS
• WHERE DMActorBirth (SELECT max(DMActorbirth)
  FROM DALLEHMOVIES2005ACTORS)

First and last names of oldest actors SELECT
DMActorfirstname, DMActorlastname, DMActorbirth
FROM DALLEHMOVIES2005ACTORS WHERE
DMActorBirth DALLEHMOVIES2005ACTORS)
34
Equivalent Use of ALL

• First and last names of youngest actors
• SELECT DMActorFirstName,DMActorLastName,DMACtorBir
  th
• FROM DALLEHMOVIES2005ACTORS
  
• WHERE DMActorBirthALL (SELECT DMActorBirth FROM
  DallehMovies2005Actors)

First and last names of oldest actors SELECT
DMActorFirstName,DMActorLastName,DMACtorBirth
FROM DALLEHMOVIES2005ACTORS
WHERE
DMActorBirthDallehMovies2005Actors)
35
ANY

• First and last names and date of birth of all
  female actors
• SELECT DMActorFirstName, DMActorLastName,
  DMActorBirth
• FROM DALLEHMOVIES2005ACTORS
  
• WHERE DMActorGender ANY (SELECT DMActorGender
• FROM dallehmovies2005Actors
• WHERE DMActorGender 'F')

36
Equivalent Use of IN

• First and last names and date of birth of all
  female actors
• SELECT DMActorFirstName, DMActorLastName,
  DMActorBirth
• FROM DALLEHMOVIES2005ACTORS
  
• WHERE DMActorGender IN (SELECT DMActorGender
• FROM dallehmovies2005Actors
• WHERE DMActorGender 'F')

37
Left Outer Join

• All female actors, some of which have won an
  award
• SELECT DMActorLastName, DMAwardName
• FROM DALLEHMOVIES2005ACTORS LEFT JOIN
  DALLEHMOVIES2005AWARDS USING (DMActorID)
• WHERE DMActorGender 'F'

38
Right Outer Join
Number of awards won by every movie. Even movies
that have won no awards are shown in the results.

• SELECT dmmoviename, COUNT(dmawardname) as No of
  Awards
• FROM DALLEHMOVIES2005AWARDS RIGHT JOIN
  DALLEHMOVIES2005MOVIES USING(DMMovieId)
• GROUP BY dmmoviename

39
Self Join
m1
m2

• Two movies with the same release company
• SELECT m1.dmmoviename, m2.dmmoviename,
  m1.dmmoviereleasecompany
• FROM dallehmovies2005movies m1,
  dallehmovies2005movies m2
  
• WHERE m1.dmmoviereleasecompany
  m2.dmmoviereleasecompany
• and m1.dmmoviereleasecompany 'Columbia
  Pictures'
• and m2.dmmoviereleasecompany 'Columbia
  Pictures'
• and m1.dmmovieid m2.dmmovieid

40
Data Integrity with SQLForeign Key (1)

• CREATE TABLE DALLEHMOVIES2005MOVIES
• (
• DMMovieID INTEGER NOT NULL,
• DMMovieName CHAR (80) NOT NULL,
• DMMovieGenre CHAR (30) NOT NULL,
• DMMovieYear INTEGER NOT NULL,
• DMDirectorID INTEGER NOT NULL,
• DMMovieRealeaseCompany CHAR (40) NOT NULL,
• CONSTRAINT PKDALLEHMOVIES2005Movies PRIMARY KEY
  (DMMovieID),
• CONSTRAINT FKDALLEHMOVIES2005DirectorID FOREIGN
  KEY (DMDirectorID)
• REFERENCES DALLEHMOVIES2005DIRECTORS
• ON DELETE RESTRICT
• ON UPDATE CASCADE,
• CONSTRAINT DMMovieYear CHECK (DMMovieYear
  BETWEEN 1800 AND 2005)
• )

41
Data Integrity with SQLForeign Key (2)

• CREATE TABLE DALLEHMOVIES2005ACTORSinMOVIE
• (
• DMActorID INTEGER NOT NULL,
• DMMovieID INTEGER NOT NULL,
• CONSTRAINT PKDALLEHMOVIES2005ActorsinMovie
  PRIMARY KEY (DMActorID, DMMovieID),
• CONSTRAINT FKDALLEHMOVIES2005ActorID FOREIGN KEY
  (DMActorID)
• REFERENCES DALLEHMOVIES2005ACTORS
• ON DELETE CASCADE
• ON UPDATE CASCADE,
• CONSTRAINT FKDALLEHMOVIES2005movieID FOREIGN KEY
  (DMMovieID)
• REFERENCES DALLEHMOVIES2005MOVIES
• ON DELETE CASCADE
• ON UPDATE CASCADE
• )

42
Action Statements

• On Delete Restrict
• CONSTRAINT FKDALLEHMOVIES2005DirectorID FOREIGN
  KEY (DMDirectorID)
• REFERENCES DALLEHMOVIES2005DIRECTORS
• ON DELETE RESTRICT
• ON UPDATE CASCADE,
• On Delete Cascade
• CONSTRAINT FKDALLEHMOVIES2005AwardMovie FOREIGN
  KEY (DMMovieID)
• REFERENCES DALLEHMOVIES2005MOVIES
• ON DELETE CASCADE ON UPDATE CASCADE,

43
Normalization

• Currently the simple movie database is in BCNF,
  i.e., each non-key is identified by the whole
  key.

DALLEHMOVIES2005DIRECTORS DMDirectorID DMDirFirst
Name DMDirLastName DMDirBirth DMDirDeath
directs
Has acted
acts
awarded
DALLEHMOVIES2005AWARDS DMAwardsID DMMovieID DMAc
torID DMDirectorID DMAwardName DMAwardType
awarded
awarded
44
1st Normal Form

• Lets assume a big movie database table
  (disregarding the awards table)
• Part of key referring to non keys

45
2nd Normal Form (1)

• We split the previous table to obtain..

46
2nd Normal Form (2)

• The movies table has the problem of a non key
  being referred by a non key

47
3rd Normal Form

• We split the movie table further.

• This database was in BCNF due to presence of a
  unique ID for each table.
• For illustration purposes, consider an additional
  attribute DMDirPrimaryPhone, a directors phone
  number, as highlighted above
• DMDirectorID - DMDirPrimaryPhone but
• Since phone numbers are also unique,
  DMDirPrimaryPhone - DMDirectorID

48
BCNF

• Solution Split into two tables

49
Views

• Creating a view for all awarded actors
• CREATE VIEW AWARDEDACTORS(actorfirstname,
  actorlastname, actorgender, awardtype)
• AS SELECT dmactorfirstname, dmactorlastname,
  dmactorgender, dmawardtype
• FROM DALLEHMOVIES2005ACTORS NATURAL JOIN
  DALLEHMOVIES2005AWARDS
• Selecting female awarded actors
• SELECT actorfirstname, actorlastname,
  actorgender, awardtype
• FROM AWARDEDACTORS WHERE actorgender'F'