|
Business processes |
Dimensions |
|||||||||||||
|
Veichle_Type |
VEHICLE |
CUSTOMER_INFO |
Member_Info_Class |
BOOKINGS |
DRIVER_INFO |
CONTRACTS |
PROCEDURE_FOR_RETURN |
DATE_INFO_Table |
Promotional_Products |
ITEMS |
ITEMS_SAle |
STORE_INFOS |
Promotion_Company |
|
|
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 |
Introduction to CarHireOZ
The two main business processes are the “Rental and Sales” and “Advertisement Commercials”. The first one deals with the sale of the products of the company and the rent services of the cars provided by the company. This business process is the very important as it is directly associated with the revenue generation of the company. In order to promote the business of the company they are using some promotional activities through different media companies. These operations are associated with the second business process “Advertisement Commercials”. The database dimensions tables are mentioned in the first row of the matrix given in the above section. In the second row and third row the two business processes have been mentioned and cross sign in the cells of the matrix is showing the association of the dimensions with the business process of the company.
The star schema diagram for the previous mentioned two business cases are given in the two diagrams in the above section. The tables in the diagrams are representing the entities that will be design in the database and the rows of the tables are indicating the attributes of the entities.
|
Fact table name |
Fact granularity |
Brief justification |
|
RENTAL_SALES |
Rent_Amount_In_Total |
Total amount of the rents over a specific time period is stored in this fact. |
|
RentAmount_During_promotion |
At the time of promotion, the amount of the rents is stored in this fact. |
|
|
Products_Sold |
The products that are sold by the stores are stored in this dimension. |
|
|
Total_Revenue_Earned |
The total amount of revenues generated from the business is stored in this fact. |
|
|
Rent_Date |
This fact is used for storing the date on which a car is rented by a customer. |
|
|
Return_Date |
This fact stores the date of returning a rented car by customer. |
|
|
ADVERTISEMENT_COMMERCIAL |
Promotional_Cost |
The amount of money required for the advertisements is stored in this fact. |
|
Promotional_Profit |
The amount of profit made by utilizing the promotional activities is stored in this fact. |
|
|
RentAmount_During_promotion |
Number of cars during the time period of promotional activities is stored in this fact by car models or categories. |
|
|
Profit_From_Promotion_Company |
The total profit made from promotions made through media company. This fact will assist in better investment |
|
|
Duration_Of_Promotion |
This fact is for storing the time period of each of the promotional activities. |
|
Dimension table name |
Brief justification |
Attribute hierarchies |
|
VEICHLE_TYPE |
There are different types of the vehicles in the company. The details of each types of cars will be stored in this table. |
typeID (PK) description price |
|
VEHICLE |
This table is for string the details about each of ten cares present in the company. |
registrationID (PK) colour Manufacturing_Year model mileage availability photo |
|
CUSTOMER_INFO |
Details of the customers is required for the company. The name, address and other required details are stored in this table, where each and every customers can be identified by the unique customer id. |
Customer_ID (PK) Last_Name First_Name birthDate address city state zip phone |
|
MEMBER_INFO_CLASS |
The details about the members i.e., the customers who have the membership for renting the cars of the company are stored in this table. |
ClassID (PK) Classdescription Recency Frequency Life_Time_Value |
|
BOOKING_DETAILS |
Online booking facility is available for renting the cars. The details of the customers and the renting cars will be stored in this table. |
BookingNO (PK) Net_booking |
|
DRIVER_INFO |
The Driver_Info details are very essential for the organization. All the required details with the contact numbers of the drivers will be stored in this table. |
DriverID (PK) Last_Name First_Name birthDate address city state zip phone licenceNO creditcard |
|
CONTRACTS |
This table is for storing the details regarding each of the contracts regarding the rent of the sale of the cars with the customers. The insurance details and the damages will also be stored in this table. |
CONTRACTID (PK) insurance paymentmethod condition limitmileage |
|
PROCEDURE_FOR_RETURN |
The return details with any type of damages and usage details will be stored in this table. |
PROCEDURE_FOR_RETURNID (PK) damage usage overduedays totalcost |
|
DATE_INFO |
The DATE_INFO dimension table allows the organization to search results based on timescale |
Date_ID (PK) Date Day DayOfWeek Month Year Quarter |
|
PROMOTION |
This table is for string the details regarding the types and costs of the promotional activities. |
PromotionID (PK) Type Cost |
|
ITEMS |
The details of the ITEMSs will stored for identifying the income of the company from each of the items. |
ITEMSID (PK) Type Name Cost |
|
ITEMS_SALE |
Total sales of items is find through this dimension table |
ItemID (PK)(FK) Customer_InfoID(PK)(FK) ITEMSName Customer_InfoName |
|
STORE_INFO |
This table is for storing the details regarding the stores of the company like name, address and contact details. |
STORE_INFOID (PK) Name Address ContactNo FaxNo |
|
PROMOTION_COMPANY |
This table is for storing the details regarding the advertising organizations. |
CompanyID (PK) Type Charge |
|
Design feature |
Brief description |
Brief justification |
|
Bridge Table |
In this case, many to many relationship is not enough for presenting the schema. Therefore bridge table connection is used. |
Items_For_Saledimension 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_For_Saletable. |
|
Conformed dimension |
This is used for showing the relationship among the tables from different perspectives. |
Customer_Info, Store_Info and PROCEDURE_FOR_RETURNgenerate same types of outcomes but for different time line or propose. |
|
Junk Dimension |
Positive or negative answers from the queries cane be gathered through this dimension. |
The Items_For_Salecan be considered as a junk dimension because when the items is sold out, it will still hold the data in the database. This will help to identify whether an item is in stock or sold out by the organization. |
|
Mini Dimension |
The little changes in the database will be reflected through this dimension. |
Online_Bookingis this type of dimension where the database will always updated regardless the condition of the booking status. |
1:
- Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID
- DATE_INFO: Day, Month
- VEHICLE: model
2:
- Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID, CompanyID
- DATE_INFO: Day, Month
- PROMOTION_COMPANY: CompanyID
3:
- RENTAL_SALES:Total_Rent_Amount, Total_Rent_of_Vehilce_During_Promotion
- DATE_INFO: DATE, Month
- BOOKINGS: BOOKINGSNO
4:
- RENTAL_SALES: Total_Rent_of_Vehilce_During_Promotion, Rent_Date
- DATE_INFO: DATE, Month
- BOOKINGS: BOOKINGSNO
5:
- RENTAL_SALES: Customer_InfoID, STORE_INFOID, RegistrationID, CONTRACTSID
- DATE: Year
- CUSTOMER_INFO: Last_Name, First_Name
6:
- RENTAL_SALES: registrationID, returnprotocolid, Total_Rent_Amount
- DATE_INFO: Year
- VEHICLE: model
- BOOKINGS: BookingNO
7:
- RENTAL_SALES: Customer_InfoID, BOOKINGNO
- BOOKINGS: Net_bookingS
8:
- RENTAL_SALES: Customer_Info_Key, CONTRACTID
- CONTRACTS: paymentmethod
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.
Ahmed, T., Pedersen, T.B. and Lu, H., 2013, June. A data warehouse solution for analyzing rfid-based baggage tracking data. In Mobile Data Management (MDM), 2013 IEEE 14th International Conference on (Vol. 1, pp. 283-292). IEEE.
Alhyasat, E.B. and Al-Dalahmeh, M., 2013. Data warehouse success and strategic oriented business intelligence: a theoretical framework. arXiv preprint arXiv:1307.7328.
Choi, H., Son, J., Yang, H., Ryu, H., Lim, B., Kim, S. and Chung, Y.D., 2013, April. Tajo: A distributed data warehouse system on large clusters. In Data Engineering (ICDE), 2013 IEEE 29th International Conference on (pp. 1320-1323). IEEE.
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.
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.
Golfarelli, M. and Rizzi, S., 2013. Data warehouse testing. Developments in Data Extraction, Management, and Analysis, pp.91-108.
Inmon, W.H. and Linstedt, D., 2014. Data Architecture: A Primer for the Data Scientist: Big Data, Data Warehouse and Data Vault. Morgan Kaufmann.
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.
Park, T. and Kim, H., 2013. A data warehouse-based decision support system for sewer infrastructure management. Automation in Construction, 30, pp.37-49.
Su, S.P.T. and Needamangala, A., 2017. Harvesting information from a library data warehouse. Information Technology and Libraries, 19(1), pp.17-28.
Tebourski, W., Karâa, W.B.A. and Ghezala, H.B., 2013. Semi-automatic Data Warehouse Design methodologies: a survey. International Journal of Computer Science Issues (IJCSI), 10(5), p.48.
Thenmozhi, M. and Vivekanandan, K., 2013. A tool for data warehouse multidimensional schema design using ontology. Int. J. Comput. Sci. Issues (IJCSI), 10(2), pp.161-168.
Vaisman, A. and Zimányi, E., 2014. Data warehouse systems. Springer, Heidelberg.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
