Vehiclerentaloz data warehousevehiclerentaloz is a large

Assignment Help Database Management System
Reference no: EM13374855

VehicleRentalOZ Data Warehouse

VehicleRentalOZ is a large chain of vehicle rental company over 500 stores distributed over all states in Australia. VehicleRentalOZ lends vehicles such as cars, vans and buses, trucks. Customers can rent products online via the VehicleRentalOZ website, as well as from rental stores. The stores also sell drinks, candies, posters, traveling maps and VIP cards etc. The revenue of the company is generated by rental fees, possible overdue fees (collected as part of returns). Rental price may vary between sessions, promotion periods or special events. The company has an operational database for the company.

The database describes the following data:  

  • Each vehicle is identified by a registration number, the colour of the vehicle, the year of made, the model, mileage, type of the vehicle: family, small, four-wheel drive, truck, bus etc. There is also a state for the availability of the vehicle and the rental price (according to the type of the vehicle).
  • Each customer is described by a customer ID, a name, an address and birthday, telephone when booking a vehicle. 
  • A booking record includes a booking number, type of the car and customer's information.
  • A contract record is made when the customer collecting the vehicle. The contract stipulating the rental period, the rental rate, the is-state and vehicle insurance policy, so on. The driver's license, the registration number of the vehicle, the driver's credit card number and payment method must be included in the contract. 
  • The data about each returning of vehicle will be recorded. The contract file will be read out when the driver returns the vehicle. Fill in all items for the state of returned vehicle to check any damage, the fuel, insurance Status, kilometre usage, the rental extension or earlier return etc.  The final payment including damage or extension penalty will be calculated.
  • The sales of the drinks are also recorded.

Every week, HQ sends a central list with all available vehicles from the different suppliers, including price, availability information, the ratings and categories of Vehicles.

The VIP Cards transactions are different than pay cash/credit transaction.

  • Each time a Vehicle is rented, a certain amount (5% less than the regular rental price) is deducted from the card balance.
  • Customers can put more money on their cards with top-ups to increase the card amount by reloading.

HQ management also analyses the usage of the cards for all customer classifications by store.

They want the following information about each card (monthly figures):

  • Starting balance
  • Ending balance
  • Average transaction amount
  • Average top-up amount
  • Number of top-ups during the month
  • Number of rentals during the month

Each store has a local operational database to capture their day-to-day rental and sales and sends the following files to headquarter (HQ) at middle night of every day:

  • A receipts file containing all the individual receipts with their rental items.

Every day, the detail rental items are incorporated into the summary rows for the current month  

  • A customer file containing
  • A list of all customer data captured by stores when new customers apply for membership as well as changes that existing customers communicate (such as change of address or changes in family composition).
  • The identification of the class they are currently assigned to.
    • Customers are assigned to classes (Platinum, Gold, Silver, Regular) based on all sorts of personal (age, occupation, and so on) and demographics information about the neighbourhoods people live in. Classes are identified by a number of thresholds (LOW and HIGH values) for the following three attributes:

1. Recency: How long ago did the customer visit one of our stores.

2. Frequency: During the x last months, how often did the customer visit us?

3. LTV (Life Time Value): What is our estimation of the potential business this customer could generate over the next x months?

Management wishes to perform detailed analysis of their company's performance and have decided to build a data warehouse to assist their business analysis and decision making for new vehicle purchase.

Rental and Sales Analysis:

Requirements were gathered from all interested business departments as follows:

  • Historical analysis of rentals and sales volumes
  • Historical analysis of rentals and sales profit (revenue, costs)
  • Optimize human resource utilization at the POS (Point Of Sale= Cashier's desk)

Therefore, it is required to build a monthly/quarterly/yearly top 10 list of

  • Categories of Vehicles (Eg., family, small, four-wheel drive, truck, bus)
  • Per individual store and per suburb of stores (for example, Bundoora, Melbourne).
  • Per class of customers (Regular, Gold, Silver, ...) or across the entire customer base

The business questions are often asked as follows:

  • What categories of Vehicles are less popular?
  • What Vehicle categories most often rent to full capacity? What are the best ratios of the different Vehicle categories for sustained profit maximisation?
  • 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?
  • What is the main location of those people (customers) renting online? What event types are they attending?

Revenue analysis:

In order to get a complete picture of the revenue this business is generating, the following revenue streams must be analysed:

  • Rentals
  • Returns (overdue fees)
  • VIP cards
  • Sales Item sales.

The revenue streams will be performed using only the actual price of individual vehicles as indicated in the transaction records. That price indicates the price including any promotions.

The management compare the different revenue stream:

How much do we make on overdue fees in last 5 years?

  • How much do we make on rental in the last financial year? How does this compare to the past 2 years ?
  • For which customers have we provided the most vehicles? How much do we make a year out of our top 5 customers?
  • Which categories of Vehicles have made the highest profit?

Promotion Revenue analysis:

In order to compute the effectiveness of the promotion, several factors have to be considered:

  • The total cost of the promotion
  • The rental revenues generated for the targeted vehicles (lift)
  • The impact on revenues for other vehicles within the same category during that same period (cannibalization and/or upsell)

The need to analyze rental/sales revenue that includes the promotions were applied for the Vehicles that were rented.  The Rental and Sales records contain information about which promotion affected the actual price of the rented or sold item.

The promotions can be decided centrally or locally, depending on the cases. Each promotion is specified with a promotion_id and the applied state. It is then the responsibility of the store manager to apply promotions during the sales/rental activities. Promotion files contain information about the different promotions.

-        Subject (on which objects)

-        Media campaign type (for example, TV Ad, Coupon, Local Newspaper Ad)

-        Start and end dates (that is, for seasonal promotions)

-        Overall cost Vehicle

Vehicles will refer to the applied promotions using the promotion_id.

The subject of the promotion describes its scope (which products are affected

  • a list of specific Vehicles (for example, all 4WD Vehicles),
  • a specific category of Vehicles (all van Vehicles during Xmas)
  • and which customers may benefit from it
  • all customers or a specific class (Gold and Platinum)).

The management is looking for determining whether the promotion was effective:

  • Whether the products under promotion showed a drop in rental just prior to or after the promotion thereby cancelling any gain
  • Whether the promotion was profitable
  • Whether the products under promotion experienced an increase in rental during the promotional period?
  • Which stores have rented out more Vehicles during the promotions? Does this vary across different months or event types?
  • What products were on promotion but did not sell?

Your Task:

Your task is to design a data warehouse for VehicleRentalOZ using Star Schema/Snowflake 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.

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

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

Fact table name

Fact granularity

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. 

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, mini-dimensions, 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.

  • Whether the promotion was profitable? That is, whether the products under promotion experienced an increase in rental during the promotional period?
  • Any stores have rented out more Vehicles during the promotions? Does this vary across different months or event types?
  • What products were on promotion but did not sell?
  • For which customers have we provided the most products? How much do we make a year out of our top 5 customers?
  • Which categories of Vehicles have made the highest profit?
  • What is the main location of those people (customers) renting online? What event types are they attending?
  • Whether the products under promotion showed a drop in rental just prior to or after the promotion thereby cancelling any gain
  • How much do we make on overdue fees in last 5 years?
  • How much do we make on rental in the last financial year? How does this compare to the past 2 years ?

Verified Expert

This assignment is about designing data warehouse. The case study is based on carbide organization. Two data warehouse schema diagram is created. All the description of the warehouse like fact table granularities, bus matrix is provided within the solution.

Reference no: EM13374855

Questions Cloud

Reading 1what was the russian revolution aboutwhat kind of : reading 1what was the russian revolution about?what kind of factors did bring it to existence and make it
Section anbspuse your kills to analyze compare criticize : section anbspuse your kills to analyze compare criticize evaluate and justify the answers in a process to solve the
Need an expert who can model a drill in simulink working : need an expert who can model a drill in simulink. working model of a drill needing for an improvment to behave more
Rough proposal of dissertationresearch topicsince human is : rough proposal of dissertationresearch topicsince human is the fundamental element of all business and managerial
Vehiclerentaloz data warehousevehiclerentaloz is a large : vehiclerentaloz data warehousevehiclerentaloz is a large chain of vehicle rental company over 500 stores distributed
Prepare a report - the report should be related to business : prepare a report - the report should be related to business communications.topics to cover in your written reportbull
1 choose one of the media articles or case studies listed : 1. choose one of the media articles or case studies listed by the lecturer in interact.2. use the title of the
Question 1big guy limited is a lorry manufacturer on 1 : question 1big guy limited is a lorry manufacturer. on 1 january 2012 the company entered into an operating lease as a
Introductionthe building industry - research project on the : introductionthe building industry - research project on the building industry to learn more and find out more about

Reviews

Write a Review

 

Database Management System Questions & Answers

  Calculate confidence and support of rule

Assume that 30% of science students are majoring in biology. Would you consider to be novel with respect to rule? Describe. Calculate confidence and support of rule.

  Develop a use case for each of the major processes

Develop a Use Case for each of the major processes and create the context diagram for the system described

  What is the output when the following script is executed

Determine the answer (expected result) based on your reading of from text book: -  Just show the expected output when you run the syntax.

  Create a relational database schema for this model assuming

Consider the EER diagram for a car dealer in the figure below. Map the EER schema into a set of relations.

  Could a relation be stored as a sequential file

Suppose a sequential file contains 50,000 records, and 5 milliseconds are required to interrogate an entry. How long should we expect to wait when retrieving a record from the middle of the file?

  Construct a relational schema for the er-diagram

Construct a relational schema for the ER-diagram. Make sure that you correctly translate Specialization and Many-to-Many relationships. Please follow carefully the following guidelines when you ?nish this question.

  What is meant by data independence

What is meant by data independence? Explain your answer and identify two benefits of separating application software from the database management system.

  Develop an er model

You are required to develop an ER model to represent the information requirements for a database that Queensland Liquor National online trader system will use as described on last page.

  Use javascript to ensure that an entry has been made

When the database is set up it should be populated with the data that you have chosen. Display this data as part of your documentation. Each table should have from 3 to 6 records initially.

  What is the goal of computer forensics

From your knowledge and experience how are computer forensic investigators, in today's world of complex technology, are able to retrieve and analyze data that can be used in computer forensic investigations.

  Construct an rea diagram

Design a minimal relational database for Pieces Boutique. By minimal, it is meant that option to post a key into existing table because of either needed participation or a discernible high load could be exercised where appropriate.

  Write names-e-mail addresses for all customers from table

Show all the data in each of the four tables. Do not show foreign key columns. Write names and e-mail addresses for all customers who have had a stove repair that cost more than $50.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd