Moshtix Case Study

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
ENTITY RELATIONSHIP DIAGRAM
5
Single-to-many relationship

• One venue can book for many different events

6
Single-to-many relationships
Foreign key
Primary key
7
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.event
  ID 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() gt1
• 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 ltALL (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.eventn
  ame, 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_memberDateofbir
  th CHECK (memberDateofbirth gt 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 0600PM
• Dinosaur Jr Over 18s 07-Jul-2007 0500PM