The videoezy case study
This presentation is the property of its rightful owner.
Sponsored Links
1 / 25

The VideoEzy Case Study PowerPoint PPT Presentation


  • 71 Views
  • Uploaded on
  • Presentation posted in: General

The VideoEzy Case Study. Alister Eric Yee High Distinction Assignment, Autumn 2007. VideoEzy in the Real-World. Allows hiring of games and movies by members.

Download Presentation

The VideoEzy Case Study

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


The videoezy case study

The VideoEzy Case Study

Alister Eric Yee

High Distinction Assignment, Autumn 2007

Alister Eric Yee


Videoezy in the real world

VideoEzy in the Real-World

  • Allows hiring of games and movies by members.

  • Information under the main entities of: Members, Games, Movies, Prices, Directors and Actors (attributes) are stored for both front-end and back-end use.

  • The database being presented contains 21 Members, 20 Games and 20 Movies which is a small sample of the real life VideoEzy system database.

Alister Eric Yee


Entity relationship diagram

Entity-Relationship Diagram

Alister Eric Yee


Movie director 1 m relationship 1

Movie-Director1:M Relationship (1)

  • Extracted from ERD Diagram:

  • A director can direct many movies.

  • Actual tables on the next slide…

Alister Eric Yee


Movie director 1 m relationship 2

Movie-Director1:M Relationship (2)

DIRECTOR

Primary Key

Foreign Key

MOVIE

Alister Eric Yee


Actor movie m m relationship 1

Actor-Movie M:M Relationship (1)

  • Extracted from ERD Diagram:

  • Many actors can star in many movies.

  • Actual tables on the next slide…

Alister Eric Yee


Actor movie m m relationship 2

Primary Key

Actor-Movie M:M Relationship (2)

Foreign Key

STARRING

Foreign Key

ACTOR

Primary Key

MOVIE

Alister Eric Yee


A simple query of a single table

A simple query of a single table

  • Report all information in the Game table where the name of the Game title starts with ‘S’.

    SELECT * FROM Game WHERE title LIKE 'S%';

    N.B. “%” matches any character, one or more times.

Alister Eric Yee


A query which uses the words natural join 1

A query which uses the words “natural join” (1)

  • Select all gamecopies (gamecopyid, platform) of games (title) and their pricetype (Price table) which are of genre: ‘Fighting’. Order by gamecopyid.

    SELECT title, genre, gamecopyid, platform, pricetype FROM game NATURAL JOIN gamecopy NATURAL JOIN price WHERE genre = 'Fighting' ORDER BY gamecopyid;

Alister Eric Yee


A query which uses the words natural join 2

A query which uses the words “natural join” (2)

SELECT title, genre, gamecopyid, platform, pricetype FROM game NATURAL JOIN gamecopy NATURAL JOIN price WHERE genre = 'Fighting' ORDER BY gamecopyid;

Alister Eric Yee


The cross product equivalent to the natural join query 1

The cross product equivalent to the “natural join” query (1)

  • Select all gamecopies (gamecopyid, platform) of games (title) and their pricetype (Price table) which are of genre: ‘Fighting’. Order by gamecopyid.

    SELECT title, genre, gamecopyid, platform, pricetype FROM game, gamecopy, price WHERE game.gameid = gamecopy.gameid AND price.priceid = gamecopy.priceid AND genre = 'Fighting' ORDER BY gamecopyid;

Alister Eric Yee


The cross product equivalent to the natural join query 2

The cross product equivalent to the “natural join” query (2)

SELECT title, genre, gamecopyid, platform, pricetype FROM game, gamecopy, price WHERE game.gameid = gamecopy.gameid AND price.priceid = gamecopy.priceid AND genre = 'Fighting' ORDER BY gamecopyid;

Alister Eric Yee


A query involving a group by and also a having 1

A query involving a “Group by” and also a “Having” (1)

  • Select every director who has directed more than one movie. Give the directorid, name, and number of movies directed. Order by name.

    SELECT director.directorid, name, COUNT(*) FROM director, movie WHERE director.directorid = movie.directoridGROUP BY director.directorid, name HAVING COUNT(*) >=2 ORDER BY name;

Alister Eric Yee


A query involving a group by and also a having 2

A query involving a “Group by” and also a “Having” (2)

SELECT director.directorid, name, COUNT(*) FROM director, movie WHERE director.directorid = movie.directoridGROUP BY director.directorid, name HAVING COUNT(*) >=2 ORDER BY name;

Alister Eric Yee


A query which uses a sub query 1

A query which uses a sub query (1)

  • Select title and runningtime (Movie table), and display the movies which have more than or equal running time to the movie: ‘The Departed (2 Disk Special Edition)’.

    SELECT title, runningtime FROM movie WHERE runningtime >= (SELECT runningtime FROM movie WHERE title = 'The Departed (2 Disk Special Edition)');

Alister Eric Yee


A query which uses a sub query 2

A query which uses a sub query (2)

SELECT title, runningtime FROM movie WHERE runningtime >= (SELECT runningtime FROM movie WHERE title = 'The Departed (2 Disk Special Edition)');

Alister Eric Yee


A self join 1

A self join (1)

  • Select movieid, title and genre (Movie table) of movies which have the same genre as the movie: ‘Mission: Impossible’.

    SELECT movie1.movieid, movie1.title, movie1.genre FROM movie movie1, movie movie2 WHERE movie1.genre = movie2.genre AND movie2.title = 'Mission: Impossible';

Alister Eric Yee


A self join 2

A self join (2)

SELECT movie1.movieid, movie1.title, movie1.genre FROM movie movie1, movie movie2 WHERE movie1.genre = movie2.genre AND movie2.title = 'Mission: Impossible';

Alister Eric Yee


Check statements 1

Check Statements (1)

Create table Game

(

… truncated …

CONSTRAINT ValidGenre CHECK (Genre IN ('Action','Action Adventure',

'Action RPG','Adventure','Arcade','Children','Family','Fighting',

'MMOG','Music','Party','Puzzle','Racing','RPG','RTS','Shooter',

'Simulation','Sports','Stealth','Strategy','Survival/Horror')),

CONSTRAINT ValidRating CHECK (Rating IN ('G','G8+','PG','M15+','MA15+','E')),

CONSTRAINT ValidOnlineEnabled CHECK (OnlineEnabled IN ('Y','N')),

CONSTRAINT ValidOnlineRequired CHECK (OnlineRequired IN ('Y','N'))

);

Alister Eric Yee


Check statements 11

Check Statements (1)

Create table Movie

(

… truncated …

CONSTRAINT ValidGenre CHECK (Genre IN ('Action','Adult','Adventure','Animation','Anime','Biography',

'Children','Comedy','Crime','Cult','Documentary','Drama',

'Family','Fantasy','Film-Noir','Horror','Music','Musical', 'Mystery','RealityTV','Romance','ScienceFiction','Short',

'Special Interest','Sports','Thriller','Various','War','Western',

'Wrestling')),

CONSTRAINT ValidRating CHECK (Rating IN ('G','PG','M','MA15+','R18+')),

CONSTRAINT ValidRunningTime CHECK (RunningTime > 0 AND RunningTime <= 999)

);

Alister Eric Yee


On delete restrict and on delete cascade 1

ON DELETE RESTRICT and ON DELETE CASCADE (1)

  • If I delete a member from the database, should I delete their game hires?

    • YES (ON DELETE CASCADE)

  • If I delete a member’s hire of a game copy, should I delete the member?

    • NO (ON DELETE RESTRICT)

Alister Eric Yee


On delete restrict and on delete cascade 2

ON DELETE RESTRICT and ON DELETE CASCADE (2)

Create table GameHire

(

GameHireIDINTEGER NOT NULL, -- Unique GameHireID

MemberIDINTEGER NOT NULL, -- Unique MemberID

GameCopyIDINTEGER NOT NULL, -- Unique GameCopyID

DueDateDate NOT NULL, -- Date the game is due for return

ReturnStatusTEXT NOT NULL, -- Has the game been returned?

CONSTRAINT GameHireID_PK Primary Key (GameHireID),

CONSTRAINT MemberID_FK FOREIGN KEY (MemberID) REFERENCES Member

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT GameCopyID_FK FOREIGN KEY (GameCopyID) REFERENCES GameCopy

ON DELETE RESTRICT

ON UPDATE CASCADE,

CONSTRAINT ValidReturnStatus CHECK (ReturnStatus IN ('Y','N'))

);

Alister Eric Yee


Creating views

Creating Views

  • Create a view called moviedirector which contains movieid and title from the movie table and name from the director table.

    CREATE VIEW moviedirector (movieid, title, directorname) AS SELECT movieid, title, name FROM director, movie WHERE director.directorid = movie.directorid;

Alister Eric Yee


Querying views

Querying Views

  • Query the view: moviedirector.

    SELECT * FROM moviedirector;

Alister Eric Yee


Thank you

Thank you

Alister Eric Yee


  • Login