HealthOne Project

Medical

Group #9 (BLOCKCHAIN TECHNOLOGY)

Integrates

Josue Bonilla
Marvin Guillen
Nicol Zuniga

1
Database Design Oracle
 Project Case:

You are the owner of a small consulting company specializing in database development.
for the medical sector. It has just obtained the award of a contract for the development of a model of
data in a database application system for a mid-sized health insurance company
medium that allows for tracking of medical claims and includes information from
patient, provider information (doctor), information about the consultations the patient visits
to their doctor, as well as the medications prescribed to the patient.

Some data is necessary, such as the patient's name, address, phone number, the
email address, etc., as well as who the primary care physician of each patient is,
insurance ID number and the name of the insurance company. Information about is also required
each doctor, such as their specialty and the hospitals they are affiliated with, as well as their phone number,
address, etc. Regarding the hospitals themselves, we will need to know where they are located and the
contact information

On this occasion, it will also be necessary to register the medications in this particular database.
prescribed to each patient by a healthcare provider to determine if it is
the claim is admissible, including basic information about the prescribed medication to ensure
that it is not incompatible with other prescriptions of the patient. The name must be known,
purpose/use and the possible side effects of each medication.

Finally, the database will be used to track trends and for some
extrapolative models based on accumulated data. The database will only be available for consultation in English
at first, although the goal is for it to be available in several languages.

2
Database Design Oracle
Paso 1: Determinación de Entidades, Atributos, UID
(Sections 2 and 3)

Based on the business case described above, you must identify the database requirements and,
continuation, create a conceptual data model that meets these needs.

1. Check what type of information a medical database can contain. Read articles and
check online what challenges the registration of this information poses.

2. Gather medical information to better understand the topic. Look for the type of information that
You will need to follow up.

3. Develop a list of business needs, rules, and assumptions according to the case,
research and the objectives.

4. Confeccione una lista de posibles entdades, incluidos sus atributos, las opciones del atributo y un posible
UID for each entity.

5. Create an Entity Relationship Diagram (ERD) that meets these objectives and needs. (Note: Create
only the entities. The relationships will be added in step 3)

3
Database Design Oracle
 DEVELOPMENT

Item #1 Project Challenges.

1. The organizational culture of companies in the face of a possible change in an information system
for the insurance company.
2. The content of the processes that each of the financial institutions has for management
Internal insurance claims are part of your confidential information and cannot be
revealed to the general public.

4
Database Design Oracle
Section #2:

Information and examples of formats were sought to follow up on the data with which they work.
different insurance companies and what types of products they offer, in order to have better guidance.
time to carry out and identify what type of entities can be obtained to create the database
htps://www.fi[Link]/seguros/productos-y-servicios/seguro-personas/seguro-medico-hospitalario/
ht[Link]
htps://[Link]/pan-american-life-seguro-de-salud-honduras-2
ht[Link]
htps://[Link]/personas/vida-y-ahorro/objetyou-sure/
ht[Link]

5
Database Design Oracle
Item #3:

Object

Develop a data model in a database application system for a company.

medium-sized health insurance.

Specific Objectives

Develop an ERD (Entity Relationship Diagram) for an insurance company

doctors.
Develop a diagram of the physical model of the ERD (Entity Relationship Diagram) for a company.
health insurance.
Understand the specific terminology used by insurance companies to manage their information.

Research

Based on the forms provided by the instructions that are dedicated to these activities of
offering insurance services, we analyze that companies enter the data of patients and their claims
with forms assigned for this function.
The information provided for a follow-up depends on the type of complaint made by the person or
client with the forms provided by the institution to document a complaint about a
sure.

Business Needs

Insurance companies must adapt to the unique needs of each client to fit the market.
existing of the insurances.
Improve existing products or services and if they do not exist, design and develop this product or service.
Attract new customers.

Business rules

Insurance companies cannot assign an insurance policy to a client who does not have a
fixed monthly income.
Patients must have insurance coverage before they can create a record.
Doctors can be providers for the insurance company.
Doctors can have several specialties completed.
Doctors can be assigned to several hospitals; there is no exclusivity.
Hospitals have information about their location and contact information.
The medical claims submitted by patients will be followed up.
Claims may not have assigned medications, they are optional.
Claims must be verified to determine if they are admissible, to prevent the patient from receiving
a prohibited medication.
The entered information will be assigned to a country in order to define its language.
The information about the patients' consultations.

6
Database Design Oracle
Business assumptions
• Expand internationally.
• Provide medical advice to clients.
• Optimize the technology channels for clients for better healthcare.
• Generation of advertising for insurance companies to attract new clients by offering our
services.
Structural Business Rules
• Every doctor must be assigned to a hospital.
• All medical consultations will be provided by an assigned doctor.
• The medical staff assigned to a consultation must have their license to practice their profession.
• Los proveedores de consultas médicas pueden ser proveedores de bienes y servicios para la compañía
insurance company.
• Each patient must be attended to through a consultation.
Business Rules by Procedure
• The patient must attend the consultation, where the assigned doctor will provide them with a signed prescription to be able to
make the claim to the insurance company.
• A patient can be referred to another doctor, from the primary care physician to a specialist.
Programmable Business Rules

7
Database Design Oracle
 8
Database Design Oracle
Item #4:

SUPPLIER HOSPITAL DOCTOR

#*supplierId #*hospitalId #*doctorId
*nombreProveedor *nombreHospital doctorName
address address phone
telephone email email
email telephone address
*idEspecialidad
*hospitalId

PATIENT
#*patientId
*nombrePaciente MEDICATION CLAIM
address #*idMedication #*claimId
telephone *medicationName *idPaciente
email functionality *idProveedor
*idSeguro sideEffects *idMedico
*fechaVencimiento *consultationId
Medication ID

INQUIRY
#*consultationId
*idPaciente
*idProveedor
*idMedico
consultation date
the disease
Observations

9
Database Design Oracle
 10
Database Design Oracle
Clause #5:

Step 2: Supertypes and Subtypes (Section 4, Lesson 1)

As indicated in the case, we must follow up on the consultations in which the patient visits their doctor. Some
patient consultations are related to a new condition/disease, others are follow-up consultations of a
existing diagnosis and others are consultations or routine check-ups of "patients in good health". We would like to be able to make a
Follow-up of the consultation that is conducted each time, so that we can store specific information about the consultation.
example:
From each new consultation for condition/disease, an initial diagnosis will be stored.
In follow-up consultations, it is necessary to monitor the patient's status in relation to the diagnosis.
3. In routine check-ups, we must track vital information about the patient, such as blood pressure.
current, the height and weight.

Modify the ERD with a supertype/subtype structure within the entity Consult.

Add Entity Others

11
Database Design Oracle
Step 3: Relationships (Section 5, Lesson 1, 2, 3)

When creating the entities, you should consider beforehand what relationships should be established between them. Create the relationships.
among these entities, including the options and the cardinality of the relationship.

1. Write the ERD of each of the relationships.

Each doctor can attend to one or more patients. (Transferable)

Each patient must be attended to by a single doctor.

Each doctor can work in several hospitals (Transferibles)

Each hospital must hire one or more doctors.

Each Doctor can provide one or more Consultations. Transferable

Each consultation must be provided by a single doctor.

Each Consultation must belong to a Claim (Non-Transferable)

Each Claim must have an Inquiry

Each Patient can make one or several Claims (Non-Transferable)

Each claim must be made by one or more patients.

Each Supplier can supply one or several Medications (Transferable)

Each medication must have at least one supplier.

12
Database Design Oracle
Each Prescription can contain multiple Medications (Non-Transferable)
Each medication must be assigned to a prescription.

Each patient can have a consultation. (Non-Transferable)

An appointment must be assigned to a patient.

Check if a prescription needs to be renewed.(Non-Transferable)

Each recipe can be received from a consultation.

Each doctor can have one or more specialties. (Non-Transferable)

Each specialty must have a doctor.

Each claim can have medications. (Transferable)

Each medication delivered must be assigned to a claim.

2. Some relationships will be transferable and others non-transferable (be sure to show this aspect in the ERD). For example, a
Once a prescription has been issued for a patient, it cannot be transferred to another patient.

3. The possible types of relationships are: one-to-one, one-to-many, and many-to-many.

13
Database Design Oracle
 Entities Relationship Optionality Cardinality
Medical-Hospital To work It can be M:M
Doctor-Patient Attend Can 1:M
Medical-Consultation To provide Can 1:M
Inquiry-Complaint Must Belong 1:1
Patients-Claim Perform Can 1:M
Supplier-Medicines Supply Can M:M
Prescription-Medicaments Assigned Can M:M

4. Any many-to-many relationship must be resolved. For example, each doctor can be associated with several hospitals and each
A hospital can have several associated doctors. We must ensure that this many-to-many relationship is resolved, so that
that allows us to track which doctors are affiliated with which hospitals.

Entities Relationship Optionality Cardinality

Medical-Hospital To work It may be M:M
Supplier-Medicines Supply Can M:M
Prescription-Medications Assigned Can M:M

14
Database Design Oracle
Add the entities that were not in step 1.4.

15
Database Design Oracle
Normalization
Step 4: Normalization (Section 6, Lesson 2, 3, 4)

PRESCRIPTION
#*Id_Prescripcion
* Fecha_Prescripcion
Dose
Duration
Refillable

Relationship

Each PATIENT can be given one or more PRESCRIPTIONS.

Each RECIPE OR PRESCRIPTION must be for a single PATIENT.

Each DOCTOR can prescribe one or more PRESCRIPTIONS.

Each RECIPE OR PRESCRIPTION must be prescribed by a single DOCTOR.

Recipe 16
Database Design Oracle
 Prescription
#*Id_Prescripcion
* Fecha_Prescripcion
Dose
Duration
Refillable

Step 5: Arches (Section 7, Lesson 1)

17
Database Design Oracle
 PRESCRIPTION
#*Id_Prescripcion
* Fecha_Prescripcion
Dosage
Duration

FILLABLE NOT REPLACEABLE

*Refillable number oReason
Fillable size
Observation

Paso 6: Relationships
Recursives (Section 7, Lesson 2)

18
Database Design Oracle
Each patient can be the holder of one or more insurance policies.
And each insurance must have a patient as the holder

Steps to Change Normalization

19
Database Design Oracle
Creation of Secure Entity
Remove the Id_country from the entities
Modify entity Recipe by Prescription

Change doctor-specialty relationship

Each doctor can have only one specialty
Each specialty can have one or more specialties.

The intersection prescription-entity change the name of the recipe

Step #7 create a historical data entity for Intersection called (Primary Doctor History)

#*Fecha_Inicio
Final Date
reason for living

Add the new entities to step #4

20
Database Design Oracle