Data Warehouse Design for Olympus: BUS5WB Project, La Trobe University

This assignment presents a comprehensive data warehouse design for Olympus, a professional sports service provider aiming to enhance client performance. The design addresses business objectives such as increasing sporting performance, reducing injury risk, and improving overall fitness. The solution encompasses requirements analysis, including the need for a centralized database to track client history, consultations, and performance metrics. It details the use of wearable technologies for athlete monitoring and incorporates SQL queries for data retrieval, covering billing, performance plans, wellness center costs, and client performance management. The assignment also explains dimensional modeling, including fact and dimension tables, and provides a data dictionary. The design aims to improve data capture and enable different consultants to access the same dataset. The conclusion emphasizes the importance of an optimized dimensional model and a robust data dictionary to handle a large-scale framework, ensuring effective service and automated billing for Olympus.

Running Head: DATABASE MANAGEMENT SYSTEMS 1
Database Management Systems
Name
Institution

Database Management Systems 2
Table of Contents
Introduction..........................................................................................................................................3
Requirements........................................................................................................................................4
Wearable Technologies........................................................................................................................5
SQL......................................................................................................................................................6
Consultation.........................................................................................................................................7
Dimensional models.............................................................................................................................8
Data Dictionary..............................................................................................................................12
Demonstration....................................................................................................................................13
Conclusion..........................................................................................................................................13
References..........................................................................................................................................15

Database Management Systems 3
Introduction
Olympus is a professional sport provider that is on a mission is to have every client's
performance enhanced, and the company strives to have a positive contribution on the sportsmen
and women careers1. The main areas of Olympus are increasing the sporting performance,
improving overall fitness and reducing the risk of injury. Through their subscription model, the
company requires athletes to register up front. Having been in the business for an extended period,
the firm believes that building of data warehouse would lead to efficiency and effectiveness of the
business operations. The business, therefore, needs to focus on the building a data warehouse for
consultations. The consultation procedure needs to be captured to keep the history of the athlete and
how he was attended to by different specialists. Once an athlete is registered, their consultation
information is registered, their past training, nutritional plans. There are many decisions to be made
on an athlete regarding consultations regarding performance and deciding which medical attention
to be given to them. There is a need to have injury updates and history that would be used by a
physician to make decisions during consultations. The business needs to improve its data capture
and enable different consultants to attend to the same patient using the same dataset.
In the performance plan management, the performance needs to be traced to measure the
progress over time. There are many performance indicators such as the status of the injuries and
other scores of tests such as mental wellbeing. Each performance and achievements have
maintained the numerical scores of the clients towards achieving the best scores.
1 Zhang, C., Naughton, J., DeWitt, D., Luo, Q. and Lohman, G., 2001, May. On supporting
containment queries in relational database management systems

Database Management Systems 4
Requirements
Therefore from the above requirements, the history of the client needs to be stored in a
central database so that doctors can be rescheduled and the clients attended to by different clients.
The requirements such as physiotherapy, sauna, massage, and yoga retreats are offered in house,
and the database needs to be put in place to trace the progress of every individual. The system also
needs to track the services from the third party providers2. The customer feedback is also of
importance and comments need to be saved. At the same time, the system will maintain one to one
link with the third party providers. The system will also need to trace billing the wellness center,
and there are additional charges for every client. The billing will also maintain individual records
such as employee expenses and salaries.
The system will need a collaboration app for accessing data online easily. The application
will help other individuals to get work done. The application will assist in sending of email to
customers, and the application will ensure that the right people get the right information at the time
they need it. The collaboration software will alert the customer when they need to visit a hospital
when they need to have any sort of consultation. The tools will also be applicable to doctors when a
patient needs to have a consultation. The available applications are the cloud-based applications that
people may access through logins. The social website like Facebook would be important and may
be considered as a collaboration application. The best collaboration application tools would be
slack. The application has earned the honor in the team collaboration and could be applied to both
doctor and patients alike to book appointments. Using this application, the collaboration between
the doctors. All messages to a particular client may be shared between the doctors. Slack is the best
tool for the application because of the many collaboration.
2 Griffiths, P.P. and Wade, B.W., 1976. An authorization mechanism for a relational database
system. ACM Transactions on Database Systems (TODS), 1(3), pp.242-255.

Database Management Systems 5
Wearable Technologies.
The world of wearable technologies has opened a new world of possibilities in the world.
The technologies have like biofeedback machines and scanners, and these wearable technologies
can soon replace the facilities offering the same services to clients3. Instead of looking at the
wearable technologies from that perspective, the wearable technologies can be used by the fitness
centers to evaluate the progress of the athletes4. The technologies can equip the athletes with
required education, dedication and improve the performance of the clients. With these devices, the
workaround of the athletes can be evaluated online, and their progress can be charted. As a result,
more clients be recruited into the program since it is now automated.
One example of a wearable device that could be used by an athlete display their fitness
schedules and results to a personal computer5. Every aspect of their training from pulse rate,
barometric pressure and altitude are converted to technology. The good side of the wearable
technologies is that they improve on the overall health of the individuals. The devices have gone the
wide range and currently includes tennis, skiing and even golf. The devices will be huge in cost
savings especially in monitoring the boundary between unhealthy individual, moderately healthy to
the healthy category. However good technology is, they cannot relay near-perfect information. For
example when a heart rate through a sensor and wearable technology, it might sound alarmed, but
other factors like the weather could be attributed. The good thing is that if the data can be analyzed,
3 Chlipala, A., Malecha, G., Morrisett, G., Shinnar, A. and Wisnesky, R., 2009, August. Effective interactive
proofs for higher-order important programs.
4 Bhargava, et al.,1971. Feature analysis of generalized database management systems:
CODASYL Systems Committee.
5 Hickey, J.J., 1996. Formal objects in type theory using very dependent types. Foundations of Object-Oriented
Languages

Database Management Systems 6
then more and more clients can be monitored. With a piece of expert advice, it means that the
training can take advantage of what data relays.
SQL
The five decision-making needs include
1. General Billing that can be queried from the General Billing model Using the following
query
SELECT * FROM GENERAL_BILLING
2. There is also the costs associated with the performance plan. These costs can be queried
as follows;
SELECT * FROM PERFORMANCE_PLAN
3. All the costs incurred in the wellness center can be retrieved using the following Query:
SELECT * FROM WELLNESS_CENTER
4. The services offered from the Wellness center
SELECT FROM CLIENT JOIN WELNESS_SERVICE_DELIVERY ON
CLENT.CLIENTID = WELNESS_SERVICE_DELIVERY.CLIENTID
JOIN WELNESS_SERVICE
ON(WELNESS_SERVICE_DELIVERY.SERVICEID=
WELLNESS_SERVICE.SERVICEID)

Database Management Systems 7
5. Client performance management: Select athletes that are registered in the system.
SEELCT * FROM CLIENT JOIN CAREPLUS ON
CLIENT.CLIENTID = CAREPLAN.CLIENTID WHERE OCCUPATION =
“Athletes”
Consultation Queries
6. Number of clients by sport and playing position
SELECT *, COUNT(CLIENTID) FROM CLIENT JOIN CONSULTATION ON
CLIENT.CLIENTID= CONSULTATION.CLIENTID ORDER BY SPORTID,
PLAYING POSITION
Consultation
• Demographic distribution and types of injuries of clients who do not return after the first
consultation
SELECT CONDITIONID, NAME,TYPE FROM
CLIENT JOIN CLIENTCONDITION ON
CLIENT.CLIENTID = CLIENTCONDITION.CLIENTID
JOIN
MEDICALCONDITION ON
MEDICALCONDITION.CONDITIONID=CLIENTCONDITION.CONDITIONID
GROUP BY WORKADRRESS
Dimensional models
The dimensional models is a data structure technique that is optimized for data warehousing
tools. The concept of dimensional models was developed by Kimball and comprised of fact and

Running Head: DATABASE MANAGEMENT SYSTEMS 8
dimension tables6. The dimension models are used in summarizing, and analyzing the numeric data.
The data could be values, balances and weights. Dimensional models are optimized for addition,
deletion of real-time data on real-time Online Transaction System7. The main aim of dimension
model is that it makes data easy to read and retrieve but does not eliminate redundancy as shown in
relational models8. The data for the fitness center, therefore, requires 3 dimension tables that will be
used in monitoring payments for clients. The dimension tables are shown below.
6 Schlesinger, L., Irmert, F. and Lehner, W., 2005. Supporting the ETL-process by Web Service
technologies
7 Shanker, U., Misra, M. and Sarje, A.K., 2008. Distributed real-time database systems: background
and literature
8 Drosatos, G.C., Efraimidis, P.S. and Karakos, A., 2006. Secure Mobile Database Applications: A
Case Study

Database Management Systems
9
General Billing
Bill Amount
Discount
Totals
Additional Expenses
Discount
Total
Hospital Bill
Wellness Center
Client ID
Total Amount
Discount

Running Head: DATABASE MANAGEMENT SYSTEMS 10
Performance Plan
Performance Score1
Performance Score2
Performance Score3
Injury Risks1
Injury Risks2
Injury Risks3
TotalScore1
TotalScore1

Database Management Systems 11

Database Management Systems 12
Data Dictionary
Table Name Column Name Col_Datatype PK FK
General Billing Billingid varchar(100) Y
ClientID int Y
BillingAmount varchar(100)
Totals varchar(100)
text
PerformancePlan ClientID varchar(100) Y
PScore1 varchar(100) Y
PScore2 varchar(100)
PScore3 varchar(100)
InjuryRisk1 varchar(100)
InjuryRisk2 varchar(100)
InjuryRisk3 varchar(100)
TotalScore1 varchar(100)
TotalScore2 varchar(100)
Hospital HospitalID varchar(100) Y
HospitalName varchar(100)
Address varchar(100)
Phone varchar(100)
Email varchar(100)
varchar(100)
Welness Center ClientID varchar(100) Y
TotalAmount varchar(100)
Discount varchar(100)
FinalTotal varchar(100)
Client ClientID varchar(100) Y
Name varchar(100)
Gender varchar(100)
DOB varchar(100)
varchar(100)
Invoice InvoiceID varchar(100)
ClientID varchar(100)
Date varchar(100)
Discount varchar(100)
Total varchar(100)
HospitalBill BillID varchar(100)
ClientID varchar(100)
BillDate varchar(100)
Amount varchar(100)

Database Management Systems 13
Total varchar(100)
Demonstration
There are four dimeson tables combined with the three fact tables9. Dimension tables are
used in to store figures such as performance score, injury risks and total scores10. The Billing
dimensional table is used for billing a customer and is connected to the client fact table to give
which client has been billed11. The General billing is useful in making the decision on how to
improve revenue collections for the company12. When Olympus need to know how much services
are being outsourced, they need to have the external hospital billing. This model has been
represented in the Hospital fact table.
Conclusion
The paper describes the primary aim of a professional sport service provider wants to
enhance the performance via consultations as well as creating a data warehouse for the company
which can provide effective service to its customers. The performance plan management for the
enhancement of the service to the customers is broadly examined and then the outcomes were
9 Perl, S.E. and Seltzer, M., 2006, November. Data Management for Internet-Scale Single-
Sign-On. In WORLDS.
10 Schlesinger, L., Irmert, F. and Lehner, W., 2005. Supporting the ETL-process by Web Service technologies.
11 Godfrey, P., Gryz, J. and Zuzarte, C., 2001, May. Exploiting constraint-like data characterizations in query
optimization
12 McMaster, K., Anderson, N. and Bilyeu-Dittman, D., 2005. A Reverse Life-Cycle Database Course With
Mini-Projects.

Database Management Systems 14
decided by the company. The requirements of the customers were thoroughly examined and the
possible technologies are stated in accordance to cope with the requirements of the customers. The
dedication of the company to provide satisfactory service to its customers is quite commendable.
Hence the company decided to come with the data warehousing technique to provide an effective
service to their customers. The data warehousing technique actually refers to the collection and
maintenance of the data of the customers. But in execution of this technique dimensional models
needs to be implemented in this framework. But the models that are stated above are depicted in
simple means whereas the framework is quite big to be handled by this model in a large scale.
Hence it is recommended that the model should be made compatible to handle the desired large
scale framework. An optimized dimensional model is required by the company to frame the whole
execution of the business as these models are used to store the main details of the customers.
Lastly it can be concluded that the designed data warehouse of the company is based on the
module of the data dictionary. The data dictionary of the company must be composed in such a
manner that it can hold a large amount of customer’s credentials for future use by the company
itself. This credentials will help with the billing system to be automated for which the efforts of the
company could get reduced.

Database Management Systems 15
References
Atkey, R., Ghani, N. and Johann, P., 2014, January. A relationally parametric model of dependent
type theory. In ACM SIGPLAN Notices (Vol. 49, No. 1, pp. 503-515). ACM.
Bhargava, B.K., Bray, O.H., Oeppe, M.E., DeSmith, D.A., Everest, G.C., Fry, J.P., Hayward, R.R.,
Herre, H.F., Kimbteton, S.R., Miller, M.W. and Moss, C.L., 1971. Feature analysis of
generalized data base management systems: CODASYL Systems Committee, May 1971.
Chlipala, A., Malecha, G., Morrisett, G., Shinnar, A. and Wisnesky, R., 2009, August. Useful
interactive proofs for higher-order important programs. In ACM Sigplan Notices (Vol. 44,
No. 9, pp. 79-90). ACM.
Codd, E.F., 1971. Relational completeness of data base sublanguages. Courant Computer Science
Symposia, Vol. 6: Data Base Systems.
Drosatos, G.C., Efraimidis, P.S. and Karakos, A., 2006. Secure Mobile Database Applications: A
Case Study. Democritus University of Thrace, Vas. Sophias, 12, p.67100.

Database Management Systems 16
Godfrey, P., Gryz, J. and Zuzarte, C., 2001, May. Exploiting constraint-like data characterizations
in query optimization. In ACM SIGMOD Record (Vol. 30, No. 2, pp. 582-592). ACM.
GRAY, J.N., LORIE, R.A., PUTZOLU, G.R., AND TRAIGER, I.L. Granularity of locks and
degrees of consistency in a shared data base. Proc. IFIP Working Conf. on Modelling of
Data Base Management Systems, Freudenstadt, Germany, Jan. 1976, pp. 695-723.
Griffiths, P.P. and Wade, B.W., 1976. An authorization mechanism for a relational database system.
ACM Transactions on Database Systems (TODS), 1(3), pp.242-255.
Hickey, J.J., 1996. Formal objects in type theory using very dependent types. Foundations of Object
Oriented Languages, 3, pp.117-170.
Kopylov, A., 2003, June. Dependent intersection: A new way of defining records in type theory. In
18th Annual IEEE Symposium of Logic in Computer Science, 2003. Proceedings. (pp. 86-
95). IEEE.
McMaster, K., Anderson, N. and Bilyeu-Dittman, D., 2005. A Reverse Life-Cycle Database Course
With Mini-Projects. In Proc. of Information Systems Education Conference.(ISECON).
McMaster, K., Anderson, N. and Blake, A., 2006. Teaching Relational Algebra and Relational
Calculus: A Programming Approach. In Proceedings ISECON.
Perl, S.E. and Seltzer, M., 2006, November. Data Management for Internet-Scale Single-Sign-On.
In WORLDS.
Schlesinger, L., Irmert, F. and Lehner, W., 2005. Supporting the ETL-process by Web Service
technologies. International Journal of Web and Grid Services, 1(1), pp.31-47.
Shanker, U., Misra, M. and Sarje, A.K., 2008. Distributed real time database systems: background
and literature review. Distributed and parallel databases, 23(2), pp.127-149.
Zhang, C., Naughton, J., DeWitt, D., Luo, Q. and Lohman, G., 2001, May. On supporting
containment queries in relational database management systems. In Acm Sigmod Record
(Vol. 30, No. 2, pp. 425-436). ACM.