Facts and Dimensions for Rental and Sales and Advertisement_Commercials
|
Business processes |
Dimensions |
|||||||||||||
|
Veichles_ Types |
VEICHLES_DETAILS |
CUSTOMER_INFO |
Member_Info_Class |
INTERNET_BOOKING |
DRIVER_DETAILS |
CONTRACTS_DETAILS |
RETURN_PROCESS |
DATE_DETAILS_Table |
Promotional_Items |
ITEMS |
Products_For_Sale |
Store_Details |
Media_Organization_Details |
|
|
Rental and Sales |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
||
|
Advertisement_Commercials |
X |
X |
X |
X |
X |
X |
X |
X |
The two business cases here are the Rental and Sales and Adevrtisement_Commercials. The associated facts and dimensions required for the two business processes are given in the above table. The crossed marks are presenting the facts and dimensions associated with the business processes.
The two diagrams given in the above section are representing the two business cases’ star schema. All the associated dimensions and facts of the databases are shown in the diagrams in table format. The rows of the tables are representing the attributes that will be designed in the database.
|
Fact table name |
Fact granularity |
Brief justification |
|
RENTAL_SALES |
RentsInTotal |
The monetary value that will be come from the rents of the cars will be stored in this fact. |
|
Rents_while_Promoting |
The amount of the money that will be get from the rents at the time of promotional activities, will be stored in this fact. |
|
|
Sold_Products |
Details regarding the already sold products will be stored in this fact. |
|
|
Total_Earned_Revenue |
Revenue earned by the company from all business activities will be stored in this fact. |
|
|
Start_Date_For_Rent |
When a customer will rent a car, then the starting date of the rent will be stored in this fact. |
|
|
End_Date_For_Rent |
The return dates of the rented car will be stored in this fact. |
|
|
ADVERTISEMENT_COMMERCIAL |
Advertisement_Cost |
The cost associated with the advertisements will be stored in this fact. |
|
Profit_From_Adds |
The profit amount got from the advertisements will be stored in this fact. |
|
|
Rents_while_Promoting |
This fact is for keeping the numbers of cars given in rent at the time period of the promotional activities. |
|
|
Profit_From_Media_Organization |
The profits got from the advertisement companies will be stored in this fact. |
|
|
Advertisements_time_Duration |
This fact is for recording the time duration required for the advertisements. |
|
Dimension table name |
Brief justification |
Attribute hierarchies |
|
VEICHLES _TYPES |
In this table, the description and price of the cars will be stored with reference to a unique typeID. |
typeID (PK) description price |
|
VEICHLES_DETAILS |
In this table, the details bout the car models with the other important specifications will be stored. |
registrationID (PK) colour Year_Of_Manufacturing model mileage availability photo |
|
CUSTOMER_DETAILS |
All the necessary details regarding the customers of the company will be stored in this table. Each customer has an unique ID. |
Customer_ID (PK) Last_Name First_Name birthDate address city state zip phone |
|
MEMBERS_DETAILS |
Having an unique classID, the details about the members classes will be stored in the table. |
ClassID (PK) Classdescription Recency Frequency Life_Time_Value |
|
INTERNET_BOOKING |
The customers are able to book the cars for rent over internet. This details will be stored in this table. |
BookingNO (PK) Pre_booking |
|
DRIVER_DETAILS |
The necessary details regarding the drivers will be stored in this table. This is very important for the safety of the cars and customers at the time of rent. |
DriverID (PK) Last_Name First_Name birthDate address city state zip phone licenceNO creditcard |
|
Contracts_Details |
The details regarding the insurance, condition of the cars and the mileage, will be stored in this table. |
CONTRACTID (PK) insurance paymentmethod condition limitmileage |
|
RETURN_PROCESS |
When a customer will return a car after renting, the details will be stored in this table. |
RETURN_PROCESSID (PK) damage usage overduedays totalcost |
|
DATE_DETAILS |
All the time scales regarding the rent, purchase, sale and promotions will be stored in this table. |
Date_ID (PK) Date Day DayOfWeek Month Year Quarter |
|
PROMOTION |
The types of the advertisements and money required for those will be stored in this table. |
PromotionID (PK) Type Cost |
|
ITEMS |
All the products of the company with the details like type, name and costs will be stored in this table. |
ITEMSID (PK) Type Name Cost |
|
Products_For_Sale |
The products that are ready for sale will be stored in this table. |
ItemID (PK)(FK) Customer_DetailsID (PK)(FK) ITEMSName Customer_DetailsName |
|
STORE_DETAILS |
Different stores are available for the organization. The details of all the stores with a unique id will be stored in this table. |
STORE_DETAILSID (PK) Name Address ContactNo FaxNo |
|
MEDIA_ORGANIZATION_DETAILS |
The details about the advertisement companies will be stored in the table. |
CompanyID (PK) Type Charge |
|
Design feature |
Brief description |
Brief justification |
|
Bridge Table |
When many to many relation is not sufficient to represent the schema, then the bridge table is used. This is applied in this case. |
Products_For_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 Products_For_Sale table. |
|
Conformed dimension |
Using this relationship same queries and outcomes will be seen through different perspectives. |
Customer_Details, Store_Details and RETURN_PROCESS can produce similar result from different types of queries. |
|
Junk Dimension |
This dimension will help to get the results in just positive or negative aspects. |
The Products_For_Sale is considered as junk dimension as the database will hold the information regarding the product even after sold out. |
|
Mini Dimension |
Very small changes will be stored in this dimension. |
Internet_booking is an example of this case. Whatever the status of the booking, the database will be updated. |
Question 1: Whether the promotion was profitable? That is, whether the Veichles_Detailss under promotion experienced an increase in rental during the promotional period?
- Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID
- DATE_DETAILS: Day, Month
- VEICHLES_DETAILS: model
Question 2: Which media type is most effective? That is, the profitable promotion used which media type?
- Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID, CompanyID
- DATE_DETAILS: Day, Month
- MEDIA_ORGANIZATION_DETAILS: CompanyID
Question 3: Any stores have rented out more Veichles_Detailss during the promotion on Christmas holiday?
- RENTAL_SALES:Total_Rent_Amount, Total_Rent_of_Vehilce_During_Promotion
- DATE_DETAILS: DATE, Month
- INTERNET_BOOKING: BOOKINGSNO
Question 4: What Veichles_Detailss were on promotion but did not rent?
- RENTAL_SALES: Total_Rent_of_Vehilce_During_Promotion, Start_Date_For_Rent
- DATE_DETAILS: DATE, Month
- INTERNET_BOOKING: BOOKINGSNO
Question 5: Who are top 5 customers make a year out of all stores.
- RENTAL_SALES: Customer_DetailsID, STORE_DETAILSID, RegistrationID, CONTRACTSID
- DATE: Year
- CUSTOMER_DETAILS: Last_Name, First_Name
Question 6: Which categories of Veichles_Detailss have made the highest profit in past year?
- RENTAL_SALES: registrationID, returnprotocolid, Total_Rent_Amount
- DATE_DETAILS: Year
- VEICHLES_DETAILS: model
- INTERNET_BOOKING: BOOKINGSNO
Question 7: What is the main age group of those customers renting online?
- RENTAL_SALES: Customer_ID, BOOKINGNO
- INTERNET_BOOKING: BOOKINGSNO
Question 8: What is the preferred payment method of customers?
- RENTAL_SALES: Customer_Details_Key, CONTRACTID
- CONTRACTS_DETAILS: paymentmethod
References:
Abai, N.H.Z., Yahaya, J.H. and Deraman, A., 2013. User requirement analysis in data warehouse design: a review. Procedia Technology, 11, pp.801-806.
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).
Cravero Leal, A., Mazón, J.N. and Trujillo, J., 2013. A business-oriented approach to data warehouse development. Ingeniería e Investigación, 33(1), pp.59-65.
Dedic, N. and Stanier, C., 2016. An evaluation of the challenges of multilingualism in data warehouse development.
Destercke, S., Buche, P. and Charnomordic, B., 2013. Evaluating data reliability: an evidential answer with application to a web-enabled data warehouse. IEEE Transactions on Knowledge and Data Engineering, 25(1), pp.92-105.
García-Moya, L., Kudama, S., Aramburu, M.J. and Berlanga, R., 2013. Storing and analysing voice of the market data in the corporate data warehouse. Information Systems Frontiers, 15(3), pp.331-349.
Khouri, S., Bellatreche, L., Jean, S. and Ait-Ameur, Y., 2014, October. Requirements driven data warehouse design: We can go further. In International Symposium On Leveraging Applications of Formal Methods, Verification and Validation(pp. 588-603). Springer, Berlin, Heidelberg.
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.
Park, T. and Kim, H., 2013. A data warehouse-based decision support system for sewer infrastructure management. Automation in Construction, 30, pp.37-49.
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).
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
