1 / 20

Moshtix Case Study

Moshtix Case Study. High Distinction assignment By Annie Tran. MOSHTIX-How it applies to the real world.

chaim
Download Presentation

Moshtix 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. Moshtix Case Study High Distinction assignment By Annie Tran

  2. MOSHTIX-How it applies to the real world • This website involves booking tickets for a variety of events in different states across Australia. Users can either purchase their moshcard at a Moshtix outlet and register their details on the site or they can purchase tickets online or through phone using a credit card. They can also register as a member without previously ordering any tickets. • On the website, events can be searched by date, venue, category (e.g. Live music, festivals) or by keyword.

  3. Moshtix website

  4. CreditCard creditCardName creditCardNumber creditCardType creditCardCVV creditCardExpiry Member memberID memberName memberEmail memberMobile memberGender memberDateofBirth Event eventID eventName eventVenue* eventDate eventTime eventType eventSaleCloseDate eventSaleCloseTime eventPrice bookingFee eventSoldOutClosed PostageContact postageID memberID* postageAddress postageSuburb postageCountry postageState postagePostcode Booking bookingID memberID* eventID* ticketType quantity entryMethod moshcardNumber Venue venueName venueAddress venueContact ENTITY RELATIONSHIP DIAGRAM

  5. Event eventID eventName eventVenue* eventDate eventTime eventType eventSaleCloseDate eventSaleCloseTime eventPrice bookingFee eventSoldOutClosed Venue venueName venueAddress venueContact Single-to-many relationship One venue can book for many different events

  6. Single-to-many relationships Foreign key Primary key

  7. Booking bookingID memberID* eventID* ticketType quantity entryMethod eventPrice bookingFee moshcardNumber Member memberID memberName memberEmail memberMobile memberGender memberDateofBirth Event eventID eventName eventVenue* eventDate eventTime eventType eventSaleCloseDate eventSaleCloseTime eventPrice bookingFee eventSoldOutClosed Many-to-many relationship • This ERD shows that in a many-to-many relationship, many members can attend many different events that are booked under Moshtix. However, only one person can book into a single event. Also, only one booking is made for one event.

  8. Many-to-many relationship

  9. QUERIES-Simple Query • Find all shows that are playing at the Metro Theatre • SELECT eventName, eventVenue FROM moshtix_event WHERE eventVenue = 'Metro Theatre’; eventname | eventvenue ----------------------+--------------- Dinosaur Jr Over 18s | Metro Theatre Dinosaur Jr All Ages | Metro Theatre Gotye All Ages | Metro Theatre

  10. QUERIES-Natural Join • Find the names of all people attending the Dinosaur Jr All Ages concert • SELECT memberName FROM moshtix_member NATURAL JOIN moshtix_booking where eventID = ‘10’; • membername • --------------- • Annie Tran • Rebecca Giang

  11. QUERIES-Cross Product • Find the names of all people attending the Dinosaur Jr All Ages concert. • SELECT memberName FROM moshtix_member, moshtix_booking WHERE moshtix_member.memberID = moshtix_booking.memberID AND moshtix_booking.eventID = ‘10’; membername --------------- Annie Tran Rebecca Giang

  12. QUERIES-Group By • GROUP BY clause must include a built in function e.g. COUNT, SUM, AVG • If any column is used in the SELECT statement, it must be used in the GROUP BY statement. • Find the number events in each venue • SELECT eventVenue, count(*) FROM moshtix_event GROUP BY eventVenue ORDER BY eventVenue eventvenue | count ------------------------+------- Amplifier Bar | 1 Bombay Rock Townsville | 1 Factory Theatre | 1 Hopetoun Hotel | 1 Manly Fishos | 1 Metro Theatre | 3 Rubys Lounge | 1 Spectrum | 1 The Basement | 1 The Gaelic | 2 The Vanguard | 2

  13. QUERIES-Group By with HAVING • Find dates with more than one event held on the same day • SELECT eventDate, eventName, count(*) FROM moshtix_event GROUP BY eventDate HAVING count(*) >1 ORDER BY eventDate • eventdate | count • ------------+------- • 2007-07-21 | 2 • 2007-07-07 | 2 • 2007-06-30 | 2 • 2007-06-29 | 2

  14. QUERIES-Sub Query • Find the earliest event/s booked through Moshtix • SELECT eventName, eventDate FROM moshtix_venue WHERE eventDate <=ALL (SELECT eventDate FROM moshtix_event); • eventname | eventdate • ----------------------------------+------------ • Celibate Rifles & Cosmic Psychos | 2007-06-29 • The Red Paintings | 2007-06-29

  15. QUERIES-Self Joins • Find all users that are booking for events at the Spectrum and the Vanguard • SELECT bkg1.bookingid, eve1.eventvenue,eve1.eventname, bkg1.memberid, bkg2.bookingid, eve2.eventvenue, eve2.eventname, bkg2.memberid • FROM moshtix_booking bkg1, moshtix_booking bkg2, moshtix_event eve1, moshtix_event eve2 • WHERE eve1.eventvenue = 'Spectrum' and eve2.eventvenue = 'The Vanguard' and bkg1.eventid = eve1.eventid and bkg2.eventid = eve2.eventid; • order by bkg1.bookingid;

  16. QUERIES-Self Joins

  17. Check Statements • These statements place a great emphasis on data integrity. They are constraints which are used to validate data entered into the attribute. It verifies any existing condition. If the data does not abide by the specified constraint, an error statement is generated. • CONSTRAINT di_table_moshtix_member_memberGender CHECK (memberGender IN ('M','F')), • CONSTRAINT di_table_moshtix_member_memberDateofbirth CHECK (memberDateofbirth >= ‘01-Jan-1907’) Shows which data statements can only be used in the attribute. In this statement, the user can only enter ‘F’ or ‘M’ as valid data Any data below this constraint is not considered as valid data

  18. Action statements CREATE TABLE moshtix_booking ( bookingID INTEGER NOT NULL, memberID INTEGER NOT NULL, eventID INTEGER NOT NULL *Insert other attributes* CONSTRAINT moshtix_booking_pk PRIMARY KEY (bookingID), CONSTRAINT moshtix_booking_fk_mid FOREIGN KEY (memberID) REFERENCES moshtix_member ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT moshtix_booking_fk_eid FOREIGN KEY (eventID) REFERENCES moshtix_event ON DELETE CASCADE ON UPDATE CASCADE, This will restrict the user from deleting the information from the member table without deleting anything from the booking table This will allow the user to delete/update any entry made to the booking and the event table.

  19. CREATE A VIEW • Create a view to find what events stop selling their tickets on ‘07/07/2007’ • CREATE VIEW EVENT (name, dateclose, timeclose) AS SELECT eventName, eventSaleCloseDate, eventSaleCloseTime FROM moshtix_event WHERE eventSaleCloseDate = ‘07-Jul-2007’; CREATE VIEW

  20. Use of a view • Find what events stop selling their tickets on ‘07/07/2007’ name | dateclose | timeclose ----------------------+-------------+----------- Dallas Crane | 07-Jul-2007 | 06:00PM Dinosaur Jr Over 18s | 07-Jul-2007 | 05:00PM

More Related