1 / 25

The VideoEzy Case Study

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.

natalie-lucas
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The VideoEzy Case Study Alister Eric Yee High Distinction Assignment, Autumn 2007 Alister Eric Yee

  2. 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

  3. Entity-Relationship Diagram Alister Eric Yee

  4. Movie-Director1:M Relationship (1) • Extracted from ERD Diagram: • A director can direct many movies. • Actual tables on the next slide… Alister Eric Yee

  5. Movie-Director1:M Relationship (2) DIRECTOR Primary Key Foreign Key MOVIE Alister Eric Yee

  6. 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

  7. Primary Key Actor-Movie M:M Relationship (2) Foreign Key STARRING Foreign Key ACTOR Primary Key MOVIE Alister Eric Yee

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. ON DELETE RESTRICT and ON DELETE CASCADE (2) Create table GameHire ( GameHireID INTEGER NOT NULL, -- Unique GameHireID MemberID INTEGER NOT NULL, -- Unique MemberID GameCopyID INTEGER NOT NULL, -- Unique GameCopyID DueDate Date NOT NULL, -- Date the game is due for return ReturnStatus TEXT 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

  23. 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

  24. Querying Views • Query the view: moviedirector. SELECT * FROM moviedirector; Alister Eric Yee

  25. Thank you Alister Eric Yee

More Related