|
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?
Question 2: Which media type is most effective? That is, the profitable promotion used which media type?
Question 3: Any stores have rented out more Veichles_Detailss during the promotion on Christmas holiday?
Question 4: What Veichles_Detailss were on promotion but did not rent?
Question 5: Who are top 5 customers make a year out of all stores.
Question 6: Which categories of Veichles_Detailss have made the highest profit in past year?
Question 7: What is the main age group of those customers renting online?
Question 8: What is the preferred payment method of customers?
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).
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download