1. Data Warehouse Bus Matrix: 2. Star Schema for the Data Marts:
2.1 Rental and Sales:2.2 Advertisement_Commercials:
3. Fact Table, Granularity and Justification against Granularity:
|
Fact table name |
Fact granularity |
Brief justification |
|
RENTAL_SALES |
TotalRentAmount |
This fact is for storing the amount of rent price collected for each vehicle or model in a span of time |
|
TotalRentOfVehilceDuringPromotion |
The quantity of rents made for a particular vehicle or model is stores in this fact. |
|
|
TotalItemSold |
The items are stored in a dimension table. This fact represents the quantity of items sold. |
|
|
TotalRevenue |
Revenue reflects the total business growth. This fact stores the details of the total revenue in currency form |
|
|
Rent_Start_Date |
The DATE_TABLE on which the rent starts |
|
|
Rent_End_Date |
The DATE_TABLE on which the rent ends |
|
|
ADVERTISEMENT_COMMERCIAL |
TotalCosOfPromotion |
This fact allows the organization to find the entire cost of promotions |
|
TotalProfitFromPromotion |
The entire profit made from individual promotions are stored in this fact |
|
|
TotalRentOfVehilceDuringPromotion |
This fact stores the sales of vehicles based on the models |
|
|
TotalProfitFromMediaCompany |
The total profit made from promotions made through media company. This fact will assist in better investment |
|
|
PromotionPeriod |
This fact stores the time period of the promotions |
4. Justification and Attribute Hierarchy of the Dimension Tables:
|
Dimension table name |
Brief justification |
Attribute hierarchies |
|
VEHICLE_TYPE |
Vehicle type is generated solely for collecting the details of the type of vehicle. This dimension has huge impact on business as various cost of rent highly depends on the vehicle type. |
typeID (PK) description price |
|
VEHICLE |
The vehicle dimension table is proposed for storing the data associated with every vehicle present at the organization. This dimension table holds the registration number of the vehicle and the model number also. |
registrationID (PK) colour madeyear model mileage availability photo |
|
CUSTOMER |
Customers are source of business. They are the key of producing more profit. In order to make more money, the organization must be able to analyse the customer behaviours and other details properly. The promotions are based mostly on this dimension table |
customerID (PK) lastName firstName birthDate address city state zip phone |
|
MEMBER_CLASS |
The member class has a high place in the warehouse designs. It is because, the class of the member is base of rent charge and discounts |
ClassID (PK) Classdescription Recency Frequency Life_Time_Value |
|
BOOKINGS |
The bookings are made for each rent. collecting and storing these records are very essential for the business. The bookings is for identifying whether the booking has been done online or not. |
BookingNO (PK) OnlineBooking |
|
DRIVER |
The driver details are very essential for the organization. Without the details of the drivers, the organization cannot rent a car |
driverID (PK) lastName firstName birthDate address city state zip phone licenceNO creditcard |
|
CONTRACTS |
The contracts act as the proof of settled services and returns. This dimension allows the business to find the details of mode of payment for each rent, start and end dates of rent. |
CONTRACTSID (PK) insurance paymentmethod condition limitmileage |
|
RETURN_PROTOCOL |
This dimension allows the organization to find the damages for each rent or damages a vehicle has faced during lifetime. |
RETURN_PROTOCOLID (PK) damage usage overduedays totalcost |
|
DATE_TABLE |
The DATE_TABLE dimension table allows the organization to search results based on timescale |
Date_ID (PK) Date Day DayOfWeek Month Year Quarter |
|
PROMOTION |
The promotion tables holds the type of promotion such as TV or other and the cost of that particular promotion |
PromotionID (PK) Type Cost |
|
ITEMS |
The details of the ITEMSs are required to determine the total revenue |
ITEMSID (PK) Type Name Cost |
|
ITEMS_SALE |
Total sales of items is find through this dimension table |
ItemID (PK)(FK) customerID (PK)(FK) ITEMSName CustomerName |
|
STORES |
The details of the stores are very essential for understanding business perspective and taking various decisions |
STORESID (PK) Name Address ContactNo FaxNo |
|
MEDIA_COMPANY |
This dimension assist in finding the details of media company |
CompanyID (PK) Type Charge |
5. Data Warehouse Design Features:
|
Design feature |
Brief description |
Brief justification |
|
Bridge Table |
The many-to-many relations are not suitable for any database. The bridge table serves as the connection between two relations in many-to-many relation and divide into two relations. |
ITEMS_SALE dimension table acts as the connector between Items and Sales dimensions. The primary key of each of the two tables are the foreign key in ITEMS_SALE table. |
|
Conformed dimension |
This conformed dimensions allows the organization to find similar results for different perspectives. Such as sales amount for individual employee or a whole store |
Custmer, STORE and RETURN_PROTOCOL generate similar results but for different time line or propose. |
|
Junk Dimension |
This dimension provides the answer of a query in positive or negative. |
The ITEMS_SALE can be considered as a junk dimension as the item sold at least once will be recorded into this dimension. By finding the existence of an item in this table will allow the organization finding whether that particular item has even sold or not. |
|
Mini Dimension |
This dimension is for portraying the quickest change in the warehouse. |
booking dimension is the first dimension that is called when a car rent process initiates. No matter what the status of booking is this table will be updated. Therefore, this dimension reflects the quickest changes. |
6. Identification of Fields:
Question 1:
- Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID
- DATE_TABLE: Day, Month
- VEHICLE: model
Question 2:
- Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID, CompanyID
- DATE_TABLE: Day, Month
- MEDIA_COMPANY: CompanyID
Question 3:
- RENTAL_SALES: Total_Rent_Amount, Total_Rent_of_Vehilce_During_Promotion
- DATE_TABLE: DATE, Month
- BOOKINGS: BOOKINGSNO
Question 4:
- RENTAL_SALES: Total_Rent_of_Vehilce_During_Promotion, Rent_Start_Date
- DATE_TABLE: DATE, Month
- BOOKINGS: BOOKINGSNO
Question 5:
- RENTAL_SALES: customerID, STORESID, RegistrationID, CONTRACTSID
- DATE: Year
- CUSTOMER: lastName, firstName
Question 6:
- RENTAL_SALES: registrationID, returnprotocolid, Total_Rent_Amount
- DATE_TABLE: Year
- VEHICLE: model
- BOOKINGS: BookingNO
Question 7:
- RENTAL_SALES: customerID, BOOKINGNO
- BOOKINGS: OnlineBOOKINGS
Question 8:
- RENTAL_SALES: Customer_Key, CONTRACTID
- CONTRACTS: paymentmethod
Chaplot, V., 2016. DESIGN & IMPLEMENTATION OF DATA WAREHOUSE PROTOTYPE WITH IN THE CONTEXT OF RELATIONAL ONLINE ANALYTICAL PROCESSING (DATA ANALYSIS). International Education and Research Journal, 2(9).
Chowdhury, R., Datta, S., Dasgupta, S. and De, M., 2015. Implementation of Central Dogma Based Cryptographic Algorithm in Data Warehouse Architecture for Performance Enhancement. International Journal of Advanced Computer Science and Applications, 6(11).
Dedic, N. and Stanier, C., 2016. An evaluation of the challenges of multilingualism in data warehouse development.
Khojah, M. and Mannino, M., 2017. Mastering Data Warehouse Maturity Concepts Using a Serious Game: Design and Implementation of Emerge2Maturity. In Proceedings of the EDSIG Conference ISSN (Vol. 2473, p. 3857).
Kraus, C. and Valverde, R., 2014. A data warehouse design for the detection of fraud in the supply chain by using the benford’s law. American Journal of Applied Sciences, 11(9), pp.1507-1518.
Ross, T.R., Ng, D., Brown, J.S., Pardee, R., Hornbrook, M.C., Hart, G. and Steiner, J.F., 2014. The HMO Research Network Virtual Data Warehouse: a public data model to support collaboration. Egems, 2(1).
Shahid, M.B., Sheikh, U., Raza, B., Munam Ali Shah, A.K., Anjum, A. and Javaid, Q., 2016. Application of Data Warehouse in Real Life: State-of-the-art Survey from User Preferences’ Perspective. International Journal of Advanced Computer Science and Applications, 7(4), pp.415-426.
Vaisman, A. and Zimányi, E., 2014. Data warehouse system.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
