1 / 55

The ZhangLamHoyts Case Study

The ZhangLamHoyts Case Study. De Wen Zhong Vincent Tang Distinction Assignment, Autumn 2007. Introduction to ZhangLamHoyts. The database refers to the website: http://hoyts.ninemsn.com.au/ Holds information about 17 Hoyts cinemas in NSW Cinemas contact details

zulema
Download Presentation

The ZhangLamHoyts 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 ZhangLamHoyts Case Study De Wen Zhong Vincent Tang Distinction Assignment, Autumn 2007

  2. Introduction to ZhangLamHoyts The database refers to the website: http://hoyts.ninemsn.com.au/ • Holds information about 17 Hoyts cinemas in NSW • Cinemas contact details • “express code” – when entered into the phone links the person directly to the associated cinema. • 26 movies and their screening times. • The different pricing structures: • La Premiere pricing - Exclusive lounge. • Mums and Bubs pricing - specialised cinemas where mums are able to bring babies/toddlers/young children • Regular pricing.

  3. ERD Diagram

  4. SQL Queries on a Single Entity Tables

  5. Project – “Select” statement • Display Movie Title and Classification movietitle | movierating --------------------------------+------------- American Dreamz | M Darna Zaroor Hai | M Eight Below | PG Failure to Launch | M Final Destination 3 | MA15+ Go For Zucker | M Ice Age 2: The Meltdown | PG In The Mix | M Kokoda | M March of the Penguins | G Mission Impossible 3 | M Scary Movie 4 | M . . . (26 rows) Select movietitle, movierating from ZhangLamHoytsMovies; • It allows the user to choose which columns to come up • The order which they come up as

  6. Restrict - “Where” statement • Display Movies where their movie duration is greater than 100minutes select * from ZhangLamHoytsmovies WHEREmovieduration > 100; movieid | movietitle | movieduration | movierating ---------+--------------------------------+---------------+------------- 1 | American Dreamz | 107 | M 2 | Darna Zaroor Hai | 111 | M 3 | Eight Below | 120 | PG 11 | Mission Impossible 3 | 126 | M 13 | She's the Man | 106 | PG 16 | The Da Vinci Code | 153 | M 18 | The Inside Man | 129 | MA15+ 19 | The New World | 135 | M 23 | The World's Fastest Indian | 121 | PG 24 | Two for the Money | 122 | M 25 | V for Vendetta | 132 | MA15+ 26 | Water | 116 | M 27 | Zodiac | 158 | MA15+ (13 rows)

  7. Project and Restrict • Display Movie Title and Classification where the classification is “M” Select movietitle, movierating from ZhangLamHoytsMovies where movierating = ‘M’; movietitle | movierating --------------------------------+------------- American Dreamz | M Darna Zaroor Hai | M Failure to Launch | M Go For Zucker | M In The Mix | M Kokoda | M Mission Impossible 3 | M Scary Movie 4 | M The Da Vinci Code | M The New World | M The Ringer | M Two for the Money | M Water | M (13 rows) Operator Meaning = Equal to < Less than <= Less than or equal to > Greater than >= Greater than or equal to <> Not equal to

  8. IS NULL To find all the movies that do not have a durationtime recorded SELECT movietitle, movieduration FROM ZhangLamhoytsmovies WHERE movieduration IS NULL; movietitle | movieduration --------------------------------+--------------- The Hills Have Eyes | (1 row)

  9. IS NOT NULL Find all the movies that are rated ‘M’ and have a duration time recorded SELECT movietitle, movieduration FROM ZhangLamhoytsmovies WHERE movierating = ‘PG’ AND movieduration IS NOT NULL; movietitle | movieduration --------------------------------+--------------- Eight Below | 120 Ice Age 2: The Meltdown | 90 She's the Man | 106 The Benchwarmers | 85 The Shaggy Dog | 98 The World's Fastest Indian | 121 (6 rows)

  10. IN Find all the addresses of cinemas in Blacktown, Broadway and Penrith SELECT * FROM zhanglamhoytscinemas WHERE cinemasuburb IN ('Blacktown','Broadway','Penrith'); cinemaid | cinemaaddress | cinemasuburb ----------+-------------------------------------------------------------+-------------- 2 | Level 4, Patrick Street Westpoint Marketown Shopping Centre | Blacktown 3 | Broadway Shopping Centre Cnr Greek & Bay Street | Broadway 13 | Westfield Penrith Cnr Jane and Riley St | Penrith (3 rows) • Can also be done by • SELECT * FROMzhanglamhoytscinemas • WHEREcinemasuburb = 'Blacktown‘ ORcinemasuburb = 'Broadway‘ ORcinemasuburb = 'Penrith';

  11. NOT IN To find all movies that do not have the rating of PG, M or MA15+ SELECT * FROM zhanglamhoytsmovies WHERE cinemarating NOT IN (‘PG',’M',’MA15+'); movieid | movietitle | movieduration | movierating ---------+--------------------------------+-----------------+------------- 10 | March of the Penguins | 80 mins | G 17 | The Hills Have Eyes | TBC | R18+ (2 rows) • Can also be done by • SELECT * FROMzhanglamhoytsmovies • WHEREmovierating <> ‘PG‘ ANDmovierating <> ‘M‘ • ANDmovierating <> ‘MA15+';

  12. Ordering Columns Change the column order from movieid, movietitle, movie rating to movierating, movietitle, movieid SELECT movieid, movietitle, movierating FROM ZhangLamHoytsMovies WHERE movierating = 'G'; movieid | movietitle | movierating ---------+--------------------------------+------------- 10 | March of the Penguins | G (1 row) SELECTmovierating, movietitle, movieid FROMZhangLamHoytsMoviesWHEREmovierating = 'G'; movierating | movietitle | movieid -------------+--------------------------------+--------- G | March of the Penguins | 10 (1 row) • Columns are ordered as they appear after the select statement

  13. Ordering Rows Order movies rated ‘M’ from shortest to longest in duration SELECT * FROM ZhangLamHoytsMovies WHERE movierating = ‘M’ ORDER BY movieduration ASC; movieid | movietitle | movieduration | movierating ---------+--------------------------------+---------------+------------- 12 | Scary Movie 4 | 83 | M 6 | Go For Zucker | 91 | M 9 | Kokoda | 92 | M 22 | The Ringer | 94 | M 4 | Failure to Launch | 96 | M 8 | In The Mix | 96 | M 1 | American Dreamz | 107 | M 2 | Darna Zaroor Hai | 111 | M 26 | Water | 116 | M 24 | Two for the Money | 122 | M 11 | Mission Impossible 3 | 126 | M 19 | The New World | 135 | M 16 | The Da Vinci Code | 153 | M (13 rows)

  14. Calculating Convert the duration of movie ‘Eight Below’ from minutes to hours SELECT movietitle, movieduration FROM ZhangLamhoytsmovies WHERE movietitle = ‘Eight Below’; movietitle | movieduration --------------------------------+--------------- Eight Below | 120 (1 row) SELECT movietitle, movieduration/60 as hours FROM ZhangLamhoytsmovies WHERE movietitle = ‘Eight Below’; movietitle | hours --------------------------------+------- Eight Below | 2 (1 row)

  15. COUNT(*) and COUNT(X) Find the total number of cinemas in Chatwood SELECTcount(*)FROM Zhanglamhoytscinemas WHERE cinema suburb =‘Chatswood’; count ------- 2 (1 row) • Find the total number of cinemas in the database SELECTcount(cinemasuburb) FROM Zhanglamhoytscinemas ; count ------- 17 (1 row)

  16. Built-in Function (AVG) and (SUM) Calculate the average time of duration of all movies SELECTavg(movieduration) as average FROM Zhanglamhoytsmovies; average ---------------------- 106.1600000000000000 (1 row) • Calculate the total time of duration of all movies SELECTsum(movieduration) as sum FROM Zhanglamhoytsmovies; sum ------ 2654 (1 row

  17. Built-in Function (MIN)and (MAX) Find the shortest time of duration for all movies SELECTmin(movieduration) as minimum FROM Zhanglamhoytsmovies; minimum ----- 77 (1 row • Find the longest time of duration for all movies SELECTmax(movieduration) as maximum FROM Zhanglamhoytsmovies; maximum --------- 153 (1 row)

  18. LIKE using % Find movies that screen at 1:50pm SELECT * FROM Zhanglamhoytssessiontimes WHERE sessionscreentime LIKE ‘%1:50pm%’; movieid | cinemaid | sessionscreentime ---------+----------+---------------------------------------------------------- 7 | 10 | 11:10am, 12:30pm, 1:50pm, 3:30pm, 7:00pm, 8:40pm, 9:30pm 11 | 10 | 11:10am, 12:30pm, 1:50pm, 3:30pm, 7:00pm, 8:40pm, 9:30pm 26 | 7 | 11:30am, 1:50pm, 4:20pm, 6:50pm, 9:20pm (3 rows

  19. LIKE using _ Find movies with an ‘a’ as its second letter SELECT * FROM Zhanglamhoytsmovies WHERE movietitle LIKE ‘_a%’; movieid | movietitle | movieduration | movierating ---------+--------------------------------+---------------+------------- 2 | DarnaZaroorHai | 111 | M 4 | Failure to Launch | 96 | M 10 | March of the Penguins | 80 | G 26 | Water | 116 | M (4 rows)

  20. DISTINCT cinemasuburb ----------------------- Bankstown Blacktown Broadway Charlestown Chatswood Cinema Paris Eastgardens Entertainment Quarter Erina Merrylands Mt Druitt Penrith Warrawong Warringah Mall Wetherill Park (15 rows) Find suburbs with cinemas without duplicates SELECTDISTINCT cinemasuburb FROM Zhanglamhoytscinemas;

  21. Inserting Rows Add a new movie INSERT INTO ZhangLamhoytsmovies VALUES (27, ‘Zodiac’, 158, ‘MA15+’) ; This can also be done by INSERT INTO ZhangLamhoytsmovies (MovieID, MovieTitle, MovieDuration, MovieRating) VALUES (27, ‘Zodiac’, 158, ‘MA15+’) ; Although this would take longer

  22. Foreign Keys and Natural Joins

  23. Natural Joins Show the phone numbers of Chatswood cinemas SELECT cinemais, cinemasuburb, contactphoneno FROM ZhangLamHoytscinemas NATURAL JOINZhangLamHoytscontacts WHERE cinemasuburb = ‘Chatswood’; cinemaid | cinemasuburb | contactphoneno ----------+--------------+---------------- 5 | Chatswood | (02) 9411 8811 6 | Chatswood | (02) 9884 8588 (2 rows)

  24. Cross Product Cross Product • Show the phone numbers of Chatswood cinemas SELECTZhangLamhoytscinemas.cinemaid, cinemasuburb, contactphoneno FROM ZhangLamHoytscinemas, ZhangLamHoytscontacts WHERE ZhangLamhoytscinemas.cinemaid = ZhangLamhoytscontacts.cinemaid and cinemasuburb = 'Chatswood'; cinemaid | cinemasuburb | contactphoneno ----------+--------------+---------------- 5 | Chatswood | (02) 9411 8811 6 | Chatswood | (02) 9884 8588 (2 rows)

  25. Foreign Entities and Relationships

  26. One to Many Relationships From ER Diagram... Primary Key Foreign Key cinemaid | other columns ----------+---------------- 1 | ... 2 | ... 3 | ... ...etc... | ... (18 rows) cinemaid | contactphoneno | contactfaxno | contactexpresscode ---------+----------------+-----------------+------------------- 1 | (02) 9796 4888 | (02) 9796 4115 | 275 2 | (02) 9671 8000 | (02) 9671 8017 | 290 3 | (02) 9211 1911 | (02) 9211 7805 | 284 ...etc...| ... | ... | ... (17 rows)

  27. Many to Many Relationships • This is a many-to-many relationship between movies and cinemas • Many-to-many relationships are not ideal therefore it should they should be eliminated • If the designers of the database were to allow many-to-many relationships it would mean the database has not been normalised

  28. Many to Many Relationships CinemaID foreign key to Cinemas table • From the ER Diagram... • This shows sessions times used as an associative entity • The designer of the database has broken down the relationships • The many-to-many is eliminated MovieID foreign key to Movie table

  29. Group By, Sub-Queries and Complex Joins

  30. Group By Show the number of cinemas in each suburb SELECT cinemasuburb, count(*) as number FROM ZhangLamHoytscinemas GROUP BY cinemasuburb; cinemasuburb | number -----------------------+-------- Erina | 1 Cinema Paris | 1 Warringah Mall | 1 Entertainment Quarter | 1 Chatswood | 2 Mt Druitt | 1 Bankstown | 1 Merrylands | 1 Blacktown | 1 Broadway | 1 Wetherill Park | 1 Warrawong | 2 Penrith | 1 Eastgardens | 1 Charlestown | 1 (15 rows)

  31. Group By with ‘HAVING’ Show the number of cinemas in each suburb with more than 1 cinema in the suburb SELECT cinemasuburb, count(*) as number FROM ZhangLamHoytscinemas GROUP BY cinemasuburb HAVING count(*) >1; cinemasuburb | number --------------+-------- Chatswood | 2 Warrawong | 2 (2 rows

  32. Subqueries Find a movie that’s duration is less than the average duration of all movies but longer than 100 minutes SELECT movietitle, movieduration FROM zhanglamhoytsmovies WHERE movieduration > 100 and movieduration <( SELECT avg(movieduration) FROM zhanglamhoytsmovies); movietitle | movieduration --------------------------------+--------------- She's the Man | 106 (1 row)

  33. Subquery (MAX) Show the movie with the longest duration SELECT movietitle, movieduration FROM ZhangLamHoytsmovies WHERE movieduration = ( SELECT max(movieduration) FROM ZhangLamHoytsmovies); movietitle | movieduration --------------------------------+--------------- The Da Vinci Code | 153 (1 row)

  34. Subquery (MIN) Show the movie with the shortest duration SELECT movietitle, movieduration FROM ZhangLamHoytsmovies WHERE movieduration = ( SELECT min(movieduration) FROM ZhangLamHoytsmovies); movietitle | movieduration --------------------------------+--------------- The Squid and the Whale | 77 (1 row)

  35. Subquery (MAX using ALL) Show the movie with the longest duration SELECT movietitle, movieduration FROM ZhangLamHoytsmovies WHERE movieduration >= all ( SELECT movieduration FROM ZhangLamHoytsmovies WHERE movieduration >0); movietitle | movieduration --------------------------------+--------------- The Da Vinci Code | 153 (1 row)

  36. Subquery (MIN using ALL) Show the movie with the shortest duration SELECT movietitle, movieduration FROM ZhangLamHoytsmovies WHERE movieduration <= all ( SELECT movieduration FROM ZhangLamHoytsmovies WHERE movieduration >0); movietitle | movieduration --------------------------------+--------------- The Squid and the Whale | 77 (1 row))

  37. Subquery (ANY) Select the movies that screen during the times of 10:30am, 2:30pm, 6:45pm SELECT * FROM ZhangLamHoytsmovies WHERE movieid = any ( SELECT movieid FROM ZhangLamhoytssessiontimes WHERE sessionscreentime = ‘10:30am, 2:30pm, 6:45pm’); movieid | movietitle | movieduration | movierating ---------+--------------------------------+---------------+------------- 5 | Final Destination 3 | 98 | MA15+ 13 | She's the Man | 106 | PG (2 rows) • This can also be done using ‘IN’

  38. Subquery (IN) Select the movies that screen during the times of 10:30am, 2:30pm, 6:45pm SELECT * FROM ZhangLamHoytsmovies WHERE movieid in ( SELECT movieid FROM ZhangLamhoytssessiontimes WHERE sessionscreentime = ‘10:30am, 2:30pm, 6:45pm’); movieid | movietitle | movieduration | movierating ---------+--------------------------------+---------------+------------- 5 | Final Destination 3 | 98 | MA15+ 13 | She's the Man | 106 | PG (2 rows)

  39. Left Outer Join Find movie screening times of movies with a MA15+ rating and duration of over 130mins Selection using natural join would give an output of: SELECT * FROM ZhangLamhoytsmovies natural join ZhangLamhoytssessiontimes WHERE movierating = ‘MA15+’ and movieduration >130; movieid | movietitle | movieduration | movierating | cinemaid | sessionscreentime ---------+------------------+---------------+-------------+----------+------------------------ 25 | V for Vendetta | 132 | MA15+ | 3 | 1:00pm, 3:45pm, 8:45pm 25 | V for Vendetta | 132 | MA15+ | 9 | 10:15am (2 rows)

  40. Left Outer Join Find movie screening times of movies with a MA15+ rating and duration of over 130mins But using Left Outer join would give an output of: SELECT * FROM ZhangLamhoytsmovies left join ZhangLamhoytssessiontimes using(movieid) WHERE movierating = ‘MA15+’ and movieduration >130; movieid | movietitle | movieduration | movierating | cinemaid | sessionscreentime ---------+------------------+---------------+-------------+----------+------------------------ 25 | V for Vendetta | 132 | MA15+ | 3 | 1:00pm, 3:45pm, 8:45pm 25 | V for Vendetta | 132 | MA15+ | 9 | 10:15am 27 | Zodiac | 158 | MA15+ | | (3 rows)

  41. Right Outer Join Find the contacts of cinemas starting with the letter ‘B’ Using natural join would give an output of: SELECT cinemasuburb, contactphoneno FROM ZhangLamhoytscontacts natural join ZhangLamhoytscinemas WHERE cinemasuburb like ‘B%’; cinemasuburb | contactphoneno --------------+---------------- Bankstown | (02) 9796 4888 Blacktown | (02) 9671 8000 Broadway | (02) 9211 1911 (3 rows)

  42. Right Outer Join Find the contacts of cinemas starting with the letter ‘B’ But using Right Outer join would give an output of: SELECT cinemasuburb, contactphoneno FROM ZhangLamhoytscontacts right join ZhangLamhoytscinemas using(cinemaid) WHERE cinemasuburb like ‘B%’; cinemasuburb | contactphoneno --------------+---------------- Bankstown | (02) 9796 4888 Blacktown | (02) 9671 8000 Broadway | (02) 9211 1911 Belconnen | (4 rows)

  43. Self Join Find the movies that have the same duration time as the movie ‘Slither’ SELECT m2.movietitle FROM zhanglamhoytsmovies m1, zhanglamhoytsmovies m2 WHERE m1.movietitle = 'Slither' and m1.movieduration = m2.movieduration; movietitle -------------------------------- Failure to Launch In The Mix Slither (3 rows)

  44. Data Integrity with SQL

  45. Foreign Keys(Cinemas table joined to contacts table through a foreign key) cinemaid | cinemaaddress | cinemasuburb ----------+-------------------------------------------------------------+------------------ 1 | Cnr The Mall & Jacob Streets | Bankstown 2 | Level 4, Patrick Street WestpointMarketown Shopping Centre | Blacktown 3 | Broadway Shopping Centre Cnr Greek & Bay Street | Broadway 4 | 244 Pacific Highway | Charlestown 5 | Mandarin Centre cnr Albert & Victor Sts | Chatswood ... | ... | ... (17 rows)) cinemaid | contactphoneno | contactfaxno | contactexpresscode ----------+----------------+-----------------+-------------------- 1 | (02) 9796 4888 | (02) 9796 4115 | 275 2 | (02) 9671 8000 | (02) 9671 8017 | 290 3 | (02) 9211 1911 | (02) 9211 7805 | 284 4 | (02) 4942 1811 | (02) 4942 1828 | 280 5 | (02) 9411 8811 | (02) 9411 8276 | 272 ... | ... | ... | ... (17 rows)) CinemaID foreign key to Cinemas table

  46. Foreign Keys(Cinemas table joined to contacts table through a foreign key) Linked by CinemaID with primary key in Cinemas table and foreign key in contacts table CREATE TABLE ZhangLamHoytsCinemas (CinemaID INTEGER NOT NULL, CinemaAddress VARCHAR(100) NOT NULL, CinemaSuburb VARCHAR(25) NOT NULL, CONSTRAINT PKCinemaID PRIMARY KEY (CinemaID)); CREATE TABLE ZhangLamHoytsContacts (CinemaID INTEGER NOT NULL, ContactPhoneNo VARCHAR(20) NOT NULL, ContactFaxNo VARCHAR(20), ContactExpressCode INTEGER, CONSTRAINT PKContactPhoneNo PRIMARY KEY (CinemaID, ContactPhoneNo), CONSTRAINT FKCCinemaID FOREIGN KEY (CinemaID) REFERENCES ZhangLamHoytsCinemas ON DELETE RESTRICT ON UPDATE CASCADE );

  47. “CHECK” Constraints • Check that the PriceType can only be Adult, Child, Student or Pensioner CREATE TABLE ZhangLamHoytsPrices (…….., CONSTRAINT FKPCinemaID FOREIGN KEY (CinemaID) REFERENCES ZhangLamHoytsCinemas ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINTValidType CHECK (PriceType = 'Adult' OR PriceType = 'Child' OR PriceType = 'Student' OR PriceType='Pensioner') ); Output – When constraint is violated INSERT INTO ZhangLamHoytsPrices VALUES (1, 1, ‘JILL'); psql:ZhangLamHoytsPrices.txt:320: ERROR: new row for relation "zhanglamhoytsprices" violates check constraint "validtype"

  48. “ON DELETE RESTRICT” - Constraint • To prevent delete in ZhangLamHoytsCinemas without deleting data from ZhangLamHoytsPrices Table CREATE TABLE ZhangLamHoytsPrices (…….., CONSTRAINT FKPCinemaID FOREIGN KEY (CinemaID) REFERENCES ZhangLamHoytsCinemas ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT ValidType CHECK (PriceType = 'Adult' OR PriceType = 'Child' OR PriceType = 'Student' OR PriceType = Pensioner') ); Output – When constraint is violated DELETE FROM ZhangLamHoytsCinemas WHERE CinemaID = 1; ERROR: update or delete on "zhanglamhoytscinemas" violates foreign key constraint “fkpcinemaid" on “zhanglamhoytsprices"

  49. “ON UPDATE CASCADE” • To update other tables along with the ZhangLamHoytsPrices Table CREATE TABLE ZhangLamHoytsPrices (…….., CONSTRAINT FKPCinemaID FOREIGN KEY (CinemaID) REFERENCES ZhangLamHoytsCinemas ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT ValidType CHECK (PriceType = 'Adult' OR PriceType = 'Child' OR PriceType = 'Student' OR PriceType = 'Pensioner') );

  50. Normalization

More Related