Help With Assignments

Got confusion or query ???

Looking for customized, unused and Plagiarism free solutions ???

Submit your assignment here

.00

Keywords

CSE4DWD – Data Warehouse Concepts and Design
Assignment, Semester 1 2013
(30% of total assessment)
Dimensional Modelling Business Case
Due Date: 9.30am Friday, 17th May 2013
Objectives
• To create a Data Warehouse conceptual design using Star Schema Modelling • To document all steps during the design process
This is an INDIVIDUAL assignment. You are not permitted to work in a group when writing this assignment.
Copying, Plagiarism:
Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is your own. The Department of Computer Science and Computer Engineering treats plagiarism very seriously. When it is detected, penalties are strictly imposed.
Students are referred to the Department of Computer Science and Computer Engineering`s Handbook and policy documents with regard to plagiarism and assignment return, and also to the section on ‘Academic Misconduct’ in the unit outline distributed in the first lecture and available on LMS/WebCT.
No extensions will be given:
Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment being submitted on time, an application for special consideration may be made. See the departmental Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime.
Submission Guidelines:
• No hard-copy of the assignment is required. Only electronic copy of your assignment is to be submitted to the LMS at CSE4DWD subject web site.
• Do not forget to attach a Statement of Authorship to your assignment, sign and date it.
• You can zip all files in one for the submission since the system does not accept multiple files.
• Assignment should be typed, not written/drawn by hand. Use the Word Drawing toolbar (or another tool of your choice) to draw figures in your assignment.
Entertainment events Data Warehouse
Pure Entertainment is an innovative Event Management & Entertainment Agency that showcases worldclass artists and performers to create exciting entertainment for corporate events and major occasions. Pure Entertainment has a dedicated portfolio of both private and corporate events; it manages all types of entertainment events specialising in entertainment for weddings, major events, festivals, fashion shows, private parties, nightclub appearances, live music, new launch & more. Its services include professional consultation, full event planning, advertising, artist booking, and ticketing services. It offers bespoke events throughout Australia.
The company also offers sound and lighting hire. By making optimum use of the client’s budget the company plans the entire extravaganza, enjoyment. Pure Entertainment keeps a list of venues and equipment that may be used/rented for entertainment events.
Pure Entertainment Agency has access to international singers, actors, entertainers and celebrities in all categories available to book for corporate, private, and public events/tours. All Pure artists have been hand-selected to provide fresh and unique performances. The company can efficiently provide accurate information with respect to artist pricing and availability.
The advertising activity for the events organized by Pure Entertainment: There are different methods or types of advertisements undertaken at Pure Entertainment such as through distribution of flyers or posters an also through other media such as newspapers, television or radio. One of the business questions that Pure Entertainment wants to address is if an advertisement type is more cost effective than another.
The tickets for any performance are sold online via the pure website, as well as from promotion outlets, such as retail stores, cafes, restaurants, shops, and boutiques. Each event may have several performance sessions, such as a play that runs for several days and performs twice per day during this time. Ticket prices may vary between sessions and for various seating positions, or unallocated standing room, for certain events.
The company has a centralised operational database to capture their day-to-day ticket sales. The promotion outlets must be supplied with batches of tickets. Those tickets are allocated to each outlet and are not available to be sold online. To avoid unsold tickets, outlets are initially allocated minimal size batches of tickets and periodically request more batches on demand. If a ticket is sold online, the customer’s details are recorded. Tickets sold via promotional outlets generally do not record the customer details. The database tables that track these processes are given in Appendix A. These tables capture the basic operations of the company including:
• Ticket sales
• Ticket distribution to promotion outlets
What these tables do not store are details relating to client invoices and promotion outlet commissions. These details are currently stored in spreadsheets (flat files). Client invoices are printed and sent to the client in the format shown in Figure 1. At the end of each financial year Pure Entertainment distributes commissions to the promotion outlets based on the number of tickets they have sold. The percentage commission paid to each outlet for each event they have promoted over the past 10 years is stored in a spread sheet.
 Pure Entertainment
Invoice No: 269856
Invoice Date:
Client Company 03 March 2012
Name: Melbourne Theatre Company
Client Address: 35 Bourke St, Melbourne, 3001
Event/Show My Fair Lady
Venue Royal Theatre
Ticket sales
 Date Ticket Type Quantity Sold Commission
 12/02/2012 Standard 225 $675.00
 12/02/2012 Premium 130 $520.00
 12/02/2012 Gold 42 $210.00
 13/02/2012 Standard 242 $726.00
 13/02/2012 Premium 156 $624.00
 13/02/2012 Gold 17 $85.00
 14/02/2012 Standard 285 $855.00
 14/02/2012 Premium 146 $584.00
 14/02/2012 Gold 45 $225.00
 Total commission fee: $4,504.00
Figure 1: Sample Client Invoice
Pure Entertainment wishes to perform detailed analysis of their company`s performance and have decided that a simple reporting feature built on top of their operational database will not be adequate. Not only will it compromise the efficiency of their operational database, they also wish to incorporate their flat file data into their analysis. They have decided to commission a data warehouse to assist them in addressing the following business questions:
• What type of entertainment events are the most popular? Has this always been the case or has there been a shift in recent years?
• Are there certain times of the year when more events are held? Are certain types of events more common at particular times of the year?
• Which ticket promotion outlets sell the most tickets? Does this vary across different months or event types?
• Who is the most popular performer among those singers, actors, entertainers or celebrities in past 5 years (more ticket sales)?
• What ticket batch sizes minimise the likelihood of outlets having unsold tickets whilst maximising profit?
• What outlets sell more of the different ticket types?
• For which clients have we provided the most event tickets? How much do we make a year out of our top 5 clients?
• What ticket types most often sell to full capacity? What are the best ratios of the different ticket types for sustained profit maximisation?
• Which venue sections, rows, and groups of seat numbers sell the quickest and to full capacity?
• How can the ticket types be allocated to venue sections, rows, and groups of seat numbers, such that sustained profit is maximised?
• How much commission did we pay to promotion outlets in the last financial year? How does this compare to the past 5 years?
• What is the main location of those people (customers) purchasing tickets online? What event types are they attending? What ticket types are they purchasing?
Pure Entertainment has also decided they need the data warehouse to support data analysis by adhoc groupings.
Your Task: (100 marks total)
Your task is to design a data warehouse for Pure Entertainment using Star Schema Modelling. Your design needs to encompass the following steps:
(i) First construct a Data Warehouse Bus Matrix to identify the company`s business processes and any likely Data Marts. (10 marks)
(ii) Design the star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labelled, and that your attributes are named using verbose textual descriptions. (40 marks)
(iii) Create the following table with a row for each fact table in your design, indicating the granularity of each fact and a brief justification for choosing that granularity.
(12 marks).
Fact table name Fact granularity Fact table type Brief justification
(iv) Create the following table with a row for each dimension table in your design, giving a brief justification for choosing that dimension, and indicating any attribute hierarchies that exist within the dimension. (12 marks).
Dimension table name Brief justification Attribute hierarchies
(v) Create the following table with a row for each design feature you have used, such as handling of possible null foreign keys, and the inclusion of any fact-less fact tables, degenerate dimensions, role playing dimensions, junk dimensions, outriggers, minidimensions, or any other design techniques discussed in the lectures. Provide a brief description of each design feature used (how and where it is used – not the theory behind the concept) and a justification for its use (16 marks).
Design feature Brief description Brief justification
(vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed below. (10 marks)
o Which ticket promotion outlets sell the most tickets? Does this vary across different months or event types?
o What ticket batch sizes minimise the likelihood of outlets having unsold tickets whilst maximising profit?
o For which clients have we provided the most event tickets? How much do we make a year out of our top 5 clients?
o How much commission did we pay to promoters in the last financial year? How does this compare to the past 5 years?
o From which online customer locations do the more expensive event tickets (eg. premium and gold) sell?
Lecturer’s Notes:
? It is not enough to just draw the shapes of the facts and dimensions in the data warehouse/ data marts. Each fact/dimension should be completely defined, with keys and facts/attributes clearly described;
? All information from the given business problem should be recognisable in your final data warehouse;
? Your final data warehouse should be able to answer all business questions listed. If one question cannot be answered using your proposed solution, it means the solution is not completely correct and you have to rethink it;
Appendix A: Pure Entertainment Operational Database Tables
Table: Event
This table holds data that describes the entertainment event.
Attribute Name Attribute Description
eventID (PK) This is the operational database`s primary key for the Event table.
eventName The name of the event.
eventDescription A description of the event.
typeOf Event The type of the event (Play, Musical Theatre, Opera, Rock Concert, Comedy)
clientID (FK) The foreign key that links the event to the client company organising the event.
venueID (PK) This is the key of the venue.
Table: EventSession
This table holds data that describes each performance session for the entertainment event.
Attribute Name Attribute Description
eventSessionID (PK) Primary key
eventSessionNumber Event performance session number
eventDate Date of the performance. May be multiples for the same performance.
sessionTime Time of the performance session. May be multiples for the same performance.
venueID (PK) This is the key of the venue.
Table: Performers
This table holds names of possible performers to Pure Entertainment` event, that is the event Performers.
Attribute Name Attribute Description
PerformerID (PK) This is the operational database`s primary key for the Client table.
PerformerName The name of the client`s company.
PerformerType singers, actors, entertainers or celebrities etc.
PerformerAgentName The name of the Performer `s agent contact.
AgentContactPhoneNo The contact phone number of the agent’s contact
AgetnContactFaxNo The fax number of the client`s contact.
AgentStreetAddress The street number, street name, and street type (St, Rd, etc) of the company`s address.
suburb The suburb component of the company`s address.
postcode The postcode component of the company`s address.
country The name of the Performer `s agent contact.
Table: Client
This table holds data relating to Pure Entertainment` clients, that is the event organisers.
Attribute Name Attribute Description
clientID (PK) This is the operational database`s primary key for the Client table.
clientCompanyName The name of the client`s company.
streetAddress The street number, street name, and street type (St, Rd, etc) of the company`s address.
suburb The suburb component of the company`s address.
postcode The postcode component of the company`s address.
clientContactName The name of the client`s contact.
clientContactPhoneNo The contact phone number of the client`s contact
clientContactFaxNo The fax number of the client`s contact.
Table: Venue
This table holds data relating to the venues where the entertainment events are held.
Attribute Name Attribute Description
venueID (PK) This is the operational database`s primary key for the Venue table.
venueName The name of the venue.
streetAddress The street number, street name, and street type (St, Rd, etc) of the venue`s address.
suburb The suburb component of the venue`s address.
postcode The postcode component of the venue`s address.
venueCapacity The number of people the venue can hold.
venueManager The name of the manager of the venue.
managerPhoneNo The phone number of the venue manager.
Table: Seat
This table holds data relating to each seat in a venue. Not all venues will have allocated seating.
Attribute Name Attribute Description
seatNumber (PK) This is the seat number (a combination of the row and number).
venueID (PK) This is the venue to which the seat belongs. Note: different venues may have the same seat numbers, so venue is part of the primary key.
venueSection The section (eg. A, B, C …) of the venue where the seat is located
Table: TicketType
This table holds data relating to the ticket types for seats (or whole rows or sections) in a venue that sometimes attract higher priced tickets.
Attribute Name Attribute Description
ticketTypeID (PK) Primary key for ticket type
ticketTypeName Short name for the ticket type, eg. standard, premium, gold, standing room
ticketTypeDescription A description of the ticket type
Table: EventSessionTicketPrice
This table holds the event session ticket price for each ticket type.
Attribute Name Attribute Description
eventSessionID (PK) Foreign key for the EventSession table
ticketTypeID (PK) Foriegn key for ticket type
ticketPrice The individual ticket price for a particular ticket type for an event session
Table: Ticket
This table holds data relating to each ticket available for sale for an event session
Attribute Name Attribute Description
ticketNumber (PK) This is the number of the ticket (the ticket number is unique for each event only).
eventSessionID (PK) This is the event session for which the ticket is for.
ticketTypeID (FK) Foreign key for category of ticket seating
seatNumber (FK) This is the number of a seat at the event venue. It is a foreign key linking to the Seat table.
promotionOutletBatchID (FK) Batch ID if the ticket was distributed to a promotion outlet. Foreign key to the PromotionOutletBatch table
ticketSaleTransactionNo (FK) This foreign key links to the TicketSaleTransaction table to show which transaction the ticket was bought in.
Table: PromotionOutletBatch
This table holds data relating to batches of tickets distributed to promotion outlets
Attribute Name Attribute Description
promotionOutletBatchID (PK) Primary key
eventID (FK) This is the event for which the ticket batch is for.
promotionOutletID (FK) This is the id of the ticket promotion outlet that the batch has been distributed to. Foreign key linking to the PromotionOutlet table.
dateDistributed Date when batch was distributed to the promotion outlet
Table: TicketSaleTransaction
This table holds data relating to each transaction for ticket sales.
Attribute Name Attribute Description
ticketSaleTransactionNo (PK) This is the transaction number for the sale.
transactionDateTime This is the date and time that the transaction took place
customerID (FK) This is the id of the customer who bought the ticket (only valid for Online purchases – otherwise null). It is a foreign key linking to the Customer table.
paymentTypeID (FK) This foreign key links to TransactionPaymentType.
Table: Customer
This table holds data relating to those people who buy tickets online from Pure Entertainment .
Attribute Name Attribute Description
customerAccountNo (PK) This is the operational database`s primary key for the Customer table.
customerFirstName The first name of the customer.
customerLast Name The last name of the customer.
streetAddress The street number, street name, and street type (St, Rd, etc) of the customer`s address.
suburb The suburb component of the customer`s address.
postcode The postcode component of the customer`s address.
state The state component of the customer`s address.
country The country component of the customer`s address.
emailAddress The customer`s email address.
contactPhoneNo The customer`s contact phone number.
Table: PromotionOutlet
This table holds data relating to the outlets that sell tickets.
Attribute Name Attribute Description
promotionOutletID (PK) This is the operational database`s primary key for the table.
businessName The name of the promoter`s business.
streetAddress The street number, street name, and street type (St, Rd, etc) of the promoter`s address.
suburb The suburb component of the promoter`s address.
postcode The postcode component of the promoter`s address.
contactName The name of the promoter`s contact.
contactPhoneNo The contact phone number of the promoter`s contact.
contactFaxNo The fax number of the promoter`s contact.
promotionOutletCategoryID (FK) Foreign key linking to the PromoterCategory table.
Table: PromotionOutletCategory
This table holds data relating to the business category of promotion outlets. Categories could include cafes, restaurants, shops, boutiques, etc.
Attribute Name Attribute Description
promotionOutletCategoryID (PK) Primary key for the promotion outlet category
categoryName The name of the category.
categoryDescription A textual description of the category.
Table: TransactionPaymentType
This table holds data relating to the transaction payment type.
Attribute Name Attribute Description
paymentTypeID (PK) This is the primary key of the TransactionPaymentType table
paymentCategory `credit`, `cash`
creditCardTypeID(FK) This foreign key links to the CreditCardType table. It will be null if the payment was cash.
Table: CreditCardType
This table holds data relating to the type of credit card used in the transaction, if credit card used
Attribute Name Attribute Description
creditCardTypeID (PK) This is the primary key of the CreditCardType table
creditCardTypeName `Mastercard`, `Bankcard`, `VISA`, `American Express` etc
creditCardFee This is the fee charged to Prestige Events per credit card transaction for this type of card.

Question Set #185

Views: 476
Word Count: 1726 words including, Diagrams, Queries, Tables and References
File Format: Microsoft Word Document
Editable: Yes
Attachment: Click here to download