SMB4102 Data and Information Management: Health Care System Model

This report provides a comprehensive overview of data models within a Health Care System, starting with the conceptual model, which represents the real-world scenario and is designed by end-users to capture the information system's requirements. It then discusses the transformation of the conceptual model into a logical model, specifically focusing on the Entity Relationship model, which outlines entities like sponsors, consumers, and insurance companies, along with their relationships. The report also touches upon the relational model, implemented using MS Access RDBMS, highlighting its role in creating a practical database. The study emphasizes the importance of each model type and their uses in database design and information systems development, providing a clear understanding of the data modeling process from initial concept to database implementation.

SMB4102 Data and Information
Management
September 13
2018
Student ID:
Student Name:
Module Tutor:

Report
Introduction
The problem discussed in the report is “Health Care System”. The report is showing the
conceptual model of Health Care System. After making the conceptual model of Health Care
System, the Logical model will be designed from the conceptual model.
The conceptual model first converts into the logical model which shows the database design for
the information systems. The logical model represents the reality described in the conceptual
model in the format of the required database model.
The logical model later converted into the Relational Data Model which shows the actual
database that is based upon the specific database software package.
Conceptual Model
The conceptual model represents the real condition of any scenario that may helps in making the
information system in different formats. The conceptual model is designed by end users or
application designers. It is not developed by the database developers. It is the first step in making
the real time information system. These are the major features of the conceptual design-
- It does not depend upon any hardware or software
- It does not depend upon any model type logical or physical etc.
The following figure is showing the conceptual model of the “Health Care System”.

(MacKay B. 2018)
In normal market transactions, the consumers and producers involve in the transactions directly
as shown in the lower part of the above image. There is no third party payer in the below portion.
This type of situation cannot occur in the medical field as there is no fixed treatment and
procedures.
In external fields, the buyers and sellers know very well about the cost of the product. The buyer
can return the product, if the cost is high. In medical field, it is not possible as sometimes after all
the tests, the cost cannot be confirmed as it depends upon the situation of the patient which is
undergoing the treatment. Therefore; it is not as simple as buyer seller relationship.
The third party payers involves when the patient pay its medical bills by claiming the insurance
companies. The insurance companies work as a third party payer. The hospitals take their bills
from the insurance companies in spite of patients.
It is not possible to remove the third party buyers from the health care systems like normal
market systems. Third party payers play a very major role in the health care systems.

Transformation of Conceptual Model to Logical Model
The conceptual model can be converted into the logical model. The conceptual model shows the
reality of the scenario in a normal way while the logical model shows the same reality in the type
of logical model like object-oriented or relational.
The logical model is also hardware and software independent just like the conceptual model but
the logical model depends upon the logical model type - object-oriented or relational.
The conceptual model can be converted into the logical model by following steps-
- For every user interface develop a logical model by using the normalization techniques.
- Combine all the logical models to make one consolidated logical model.
- Translate the Entity Relationship model into normalized requirements.
- Compare the Entity Relationship model with the consolidated logical model and create
one logical data model for the entire application.
The following figure is showing the logical model (Entity Relationship model) for the Health
Care System-

(Guru99 2016)
(Watt A. n.d.)
(Lucid Software Inc. 2015)
The detail of sponsors will be stored into the database along with name, address etc. the Sponsor
ID will be the primary key to uniquely identify the sponsors.
The detail of consumers will be stored into the database along with name, address etc. the
Consumer ID will be the primary key to uniquely identify the consumers.
The detail of insurance companies will be stored into the database along with name, address etc.
the ID will be the primary key to uniquely identify the insurance company.
A policy may be connected with any number of health care providers. In this case, the consumer
can contact any one of the health care providers for insurance claim.

The detail of health care providers will be stored into the database along with name, address etc.
the Health Care ID will be the primary key to uniquely identify the health care provider.
A consumer may be connected with a sponsor but the consumer cannot be connected with more
than one sponsor.
The above logical model is showing all the entities and their relationship of Health Care System.
All entities are having their personnel information e.g. Sponsor entity is containing the
information regarding sponsors only, the Consumer entity is containing the information
regarding consumers only.
The logical model can be easily converted into the Relational Model. The relational model
depends upon the hardware and software. It depends upon the database software package in
which the relational database system will be developed. The Relational Database for the Health
Care System is developed in the MS Access RDBMS. MS Access database is very easy to access
and it contains all the features to make a suitable Relational Database. The MS Access provides
features to make forms and reports etc. that is very useful for the users and administrators also.
Conclusion
The report is summarizing the data models from scratch. The report is giving complete
information for all models- conceptual model, logical model and relational model.
The conceptual model is the actual condition of the scenario that is developed by the end users. It
shows the actual requirement of the information system.
The logical model represents the same reality of the conceptual model in the format of logical
model type e.g. object-oriented or relational etc. The report is showing the logical model in the
form of relational model.
The report is very informative about database models types and their uses. The actual scenario
can be easily understood by studying this report.

References
Guru99 (2016), Learn Database Normalization with the help of a case study, Retrieved from
http://www.guru99.com/database-normalization.html
Watt A. (n.d.), Chapter 11 Functional Dependencies, Retrieved from
https://serc.carleton.edu/introgeo/conceptmodels/index.html
MacKay B. (2018). What are Conceptual Models?. Retrieved from
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign
.aspx
Lucid Software Inc. (2015). ER Diagram Symbols and Meaning. Retrieved from
http://www.sparxsystems.com/enterprisearchitectuserguide/modelinglanguages/
dataflowdiagrams.html