Deliverable 5
Stephen Mcelhose and Jeff Morris
Object-Oriented Data Model
Aid-Station operations occur daily. Patients (mostly from 1000+ people in battalion, but some emergency) come in. They are checked in at front desk with SSN, Name, Company (A,B,C,D,E, Support) or whole Battalion name if from outside battalion, and there complaint. Then they get seen by a medic where standard paperwork is generated, and then signed off or seen further by a provider (Dr. or PA) after receiving care. They are then checked out, where any care given, disposition (type of illness/injury) and profiles (prescribed duty limitations) are recorded as well as check out time. The patient's medical records then need to be updated with standard paper work generated for each soldier (DA 600's), usually a separate process complicated by fact that tracking of medical records is a mess.
Medicines passed out could also be tracked at time of check out. Medicines are usually just ordered in bulk through different process, but sometimes run out because of poor tracking. Medicines passed out are recorded during Aid-Station operations.
Aid-Station would needs access to the following information:
- Daily and Weekly report on how many patients he has seen from each company.
Classification of illness/injury
Number/type of profiles
- What medic and/or provider saw a given patient?
- How many patients from a given company were seen?
- Most common illness or injury
- Number of profiles for a given time period?
- Where is the medical record located for a given patient?
- When was a medical record checked out given a patient?
- What medications are running low in supplies?
It is imperative that the database solution be efficient, flexible and scalable, as the Military may encounter huge increases in numbers down the road. It also needs to be secure to keep confidential information out of harm's way, allowing only medics and patients to see the required information. This will also help optimize the process of inventory control, as the administrator of the system can easily check at all times when there is short supply of a medicine, especially if they notice an increase in a particular illness and/or injury.
One of the main issues with the current solution is it was not used on a day-to-day basis. The current administrator on a given day would us their own modification of tracking methods, which created unnecessary duplication of work. Furthermore, the development of this database will provide a standard process to be used daily, eliminating the duplication of effort.
Page 1 of 35
Deliverable 5
Stephen Mcelhose and Jeff Morris
Database Analysis
Database Analysis allows the developer and the client to develop and discuss the business rules to make an efficient database model, which allows for an optimized solution that appropriately solves the problem that exists. It is imperative that you discuss any limitations that need to be placed on the database, as to keep data integrity in mind throughout the whole process. Lastly, you must discuss response time and the physical data model needs such as security concerns.
Business Rules
Business rules are what defines how a process is properly performed. It is the job of the analyst to work with the client and determine what policies, calculations, regulations and other constraints exists within the process. This also covers the topics of data security and/or specific user requirements.
Response TimeThe Aid-Station is an imperative part of operations for the Army. Therefore, reports, queries and any other process need to happen in an efficient, and timely manner. It is necessary to access a patient's medical information also instantly, so queries should not take more than 2 or 3 seconds.
Conceptual Data ModelBased on the needs of an Aid-Station, and object oriented data model was developed after analysis of the business requirements. The first step was to identification all the objects involved and then how they relate to each other.Identification of Object TypesBased on the problem described above, below are the listed objects needed to solve the problem:
Patient with attributes:PatientID, SSN, FirstName, MiddleIntial, LastName, BirthDate and Company
Medic with attributes: MedicID,Name and Title
Provider with attributes: ProviderID, Name and Title
Disposition with attributes: DispositionID, Name and Description
DispositionCode with attributes: CodeID, Name and Description
Profile with attributes: ProfileID, Name, and Description
Medicine with attributes: MedicineID, Name, Price Quantity and MinQuantity
Visit with attributes: VisitID, CheckInTime and CheckOutTime
PatientProfile with attributes: PatientID, ProfileID, StartDate and EndDate
PatientMedicine with attributes: PatientMedicineID, PatientID, MedicineID, DosageGiven, StartDate and EndDate
Identification of RelationshipsAfter careful analysis of the business rules, the following relationships exists between the object types:
There is a many to many relationship between Profile and Patient
There is a many to many relationship between Patient and Medicine
There is a one to many relationship between Patient and Visit
There is a one to many relationship between Provider and Visit
There is a one to many relationship between Disposition and Visit
There is a one to many relationship between DispositionCode and Disposition
There is a one to many relationship between Medic and Visit
OODM
Logical Database Design
The purpose here is to develop a relational data model based upon the Object-Oriented Data Model (OODM) developed during the Conceptual Data Base Design.
We followed the conversion rules to translate the OODM into an equivalent relational data model.
Initial Relational Data Model
Please note that a primary key is indicated by underling the attribute(s). A foreign key is shown in italics.
Patient (PatientID, SSN, FirstName, MiddleInitial, LastName, BirthDate, Company)
Profile (ProfileID, Name, Description)
PatientProfile (PatientID, ProfileID, StartDate, EndDate)
Medicine (MedicineID, Name, Price, Quantity, MinQuantity)
PatientMedicine (PatientMedicineID, PatientID, MedicineID, DosageGiven, StartDate, EndDate)
Provider ( ProviderID, Name, Title)
Medic (MedicID, Name, Title)
Disposition (DispositionID, CodeID, Name, Description)
DispositionCode (CodeID, Name, Description)
Visit (VisitID, PatientID, ProviderID, MedicID, DispositionID, CheckInTime, CheckOutTime)
Definition of Attributes
Here we define the meaning of each attributes as well as the relations.
We have ten relations in the relational model.
Patient
The relation Patient is used to store information about Patients. Each row (n-tuple or simply tuple) in the relation corresponds to one patient in the real world.
PatientID. This attribute acts as a primary key for Patient. It contains a unique value for each patient. It is defined as an integer type data. It can take any value between 1 and 99,999.
SSN. This attribute is used to store a patient’s Social Security Number. This value should be protected from unauthorized access. It contains an alphanumeric value that is encrypted.
FirstName. This attribute is used to store a patient’s First Name. It contains a variable length alphanumeric value.
MiddleInitial. This attribute is used to store a patient’s middle initial. It contains an alphanumeric value.
LastName. This attribute is used to store a patient’s last name. It contains a variable length alphanumeric value.
BirthDate. This attribute is used to store a patient’s birth date. It contains a date.
Company. This attribute is used to store a patient’s company affiliation. It contains alphanumeric value.
Profile
The relation Profile is used to store the basic information about prescribed limitations, or a profile.
ProfileID. This attribute acts as a primary key for Profile. It contains a unique value for each profile, and is of integer value.
Name. This attribute is used to store a name for each profile. It contains variable alphanumeric value.
Description. This attribute is used to store descriptive information about the prescribed limitations. It contains alphanumeric data.
PatientProfile
The PatientProfile relation stores information about which Patients have what profiles. It has a row for each combination of a Profile and Patient. The primary key of this relation contains two attributes—PatientID and ProfileID.
PatientID. This attribute acts as a part of a combination primary key and foreign key. Its description is the same as above.
ProfileID. This attribute acts as a part of a combination primary key and foreign key. Its description is the same as above.
StartDate. This attribute is used to store information on the starting date of a profile for a given patient. It contains a date value.
EndDate. This attribute is used to store information on the ending date of a profile for a given patient. It contains a date value.
Medicine
The medicine relation stores information about Medicines.
MedicineID. This attribute acts as a primary key for medicine. It contains a unique value for each medicine. It is an integer value.
Name. This attribute is used to store the name of a medicine. It is an alphanumeric value.
Price. This attribute is used to store the price information for a medicine, for purposes of ordering medicines. It is a currency value.
Quantity. This attribute is used to store the current quantity of medicine on hand. It is an integer value.
MinQuantity. This attribute is used to store a minimum quantity of medicine that should be on handIts primary function is in re-ordering. It is an integer value.
Page 1 of 35
Deliverable 5
Stephen Mcelhose and Jeff Morris
PatientMedicine
The PatientMedicine relation stores information on what Patients have been issued what Medicines. It has a row for each combination of a patient and medicine.
PatientMedicineID. This attribute acts as a primary key. It contains a unique value for each issuance of medicine to a given patient.
PatientID. This attribute acts as a foreign key. Its description is the same as above.
MedicineID. This attribute acts as a foreign key. Its description is the same as above.
DosageGiven. This attribute is used to store the quantity of medicine distributed to a given patient. It is an integer value.
StartDate. This attribute is used to store the start date for a medicine regiment. It is a date value.
EndDate. This attribute is used to store the end date for a medicine regiment. It is a date value.
Provider
The Provider relation stores information on medical providers. It has a row for each provider.
ProviderID. This attribute acts as a primary key. It contains a unique value for each provider. It contains an integer value.
Name. This attribute is used to store the full name of a provider. It contains a variable alphanumeric value.
Title. This attribute is used to store a provider’s title, for example Dr. or PA. It contains a variable alphanumeric value.
Medic
The Medic relation stores information on medics. There is a row for each medic.
MedicID. This attribute acts as a primary key. It contains a unique value for each medic. It contains an integer value.
Name. This attribute is used to store the full name of a medic. It contains variable alphanumeric value.
Title. This attribute is used to store a medic’s title, for example civilian nurse, private, sergeant etc. It contains variable alphanumeric value.
Disposition
The Disposition relation stores information on dispositions which are basically diagnosis. Each disposition is associated with a disposition code.
DispositionID. This attribute acts as a primary key. It contains a unique value for each disposition. It contains an integer value.
CodeID. This attribute acts as a foreign key. See DispositionCode.CodeID for description.
Name. This attribute is used to descriptively name a disposition. It contains variable alphanumeric value.
Description. This attribute is used to provide a short readable description of a disposition. It contains variable alphanumeric value.
Page 1 of 35
Deliverable 5
Stephen Mcelhose and Jeff Morris
DispositionCode
The DispositionCode relation stores information on disposition codes, which are standard classifications for dispositions.
CodeID. This attribute acts as a primary key for DispositionCode. It contains a unique value for each DispositionCode. It is an integer value.
Name. This attribute stores a standardized name for the classification. It contains an alphanumeric value.
Description. This attribute stores a short description, describing the classification. It contains a variable alphanumeric value.
Visit
The Visit relation stores information on visits. There is a row for each visit, and each visit is associated with a given patient, provider, medic, and disposition.
VisitID. This attribute acts as a primary key for a Visit. It contains a unique value for each visit. It contains an integer value.
PatientID. This attribute acts as a foreign key. The description is the same as above.
ProviderID. This attribute acts as a foreign key. The description is the same as above.
MedicID. This attribute acts as a foreign key. The description is the same as above.
DispositionID. This attribute acts as a foreign key. The description is the same as above.
CheckInTime. This attribute is used to store the time a patient checks into visit. It is a datetime value.
CheckOutTime. This attribute is used to store the time a patient checks out of visit. It is a datetime value.
Now, we go through the normalization process to make sure that each relation is in 3NF.
Normalization of Relations
A relation is in 1NF if it has no non-atomic attributes. Each attribute holds only one value for each row. A relation is in 2NF if there is no partial dependency between the primary key and the non-key attributes of the relation. A relation is in 3NF if there is no transitive dependency between the primary key and the non-key attributes of the relation.
Patient (PatientID, SSN, FirstName, MiddleInitial, LastName, BirthDate, Company)
This relation is in 1NF. There is only one primary key and all other attributes are completely dependent upon this value, so no partial dependencies exists, making this relation in 2NF. No non-key attributes are dependent on each other, therefore no mutual dependencies exists. Clearly, your BirthDate does not determine your FirstName, MiddleInitial or LastName. Also, people can have duplicate the BirthDate, FirstName, etc. Therefore, no transitive dependencies exists between the primary key, PatientID, and non-key attributes, making this a 3NF relation.
Profile (ProfileID, Name, Description)
Each attribute of the Profile relation is atomic in nature. Name and description are not dependent upon each other and can be found by providing the ProfileID. Therefore, it is in 1NF. Applying the logic from Patient, we prove that this is also in 2NF and 3NF.
PatientProfile (PatientID, ProfileID, StartDate, EndDate)
Each attribute of the PatientProfile relation is atomic in nature. The StartDate and EndDate are completely dependent on PatientID and a ProfileID; however, they are not dependent on each other.Therefore, this relation is in 1NF, 2NF and 3NF.
Medicine (MedicineID, Name, Price, Quantity, MinQuantity)
Each attribute of the Medicine relation is atomic in nature. It can be assumed that there is no relationship between the Medicine's Name, Price, Quantity or the MinQuantity; therefore, they are not depending on each other. Also, all non-key attributes are depending on the MedicineID. Therefore, this relation is in 1NF, 2NF and 3NF.
PatientMedicine (PatientMedicineID, PatientID, MedicineID, DosageGiven, StartDate, EndDate)
Each attribute of the PatientMedicine relation is atomic in nature. Because you cannot determine the DosageGiven based on the StartDate or the EndDate, no non-key attribute is depending on each other. However, you can discover this information based on the primary and foreign keys PatientID and MedicineID.Therefore, this relation is in 1NF, 2NF and 3NF.
Provider ( ProviderID, Name, Title)
Each attribute of the Provider relation is atomic in nature. There is no correlation between the Provider's Name and their Title, so the two non-key attributes are not dependent upon each other. However, you can find out this information by providing the primary key, ProviderID.Therefore, this relation is in 1NF, 2NF and 3NF.
Medic (MedicID, Name, Title)
Each attribute of the Medic relation is atomic in nature. There is no correlation between the Medic's Name and their Title, so the two non-key attributes are not dependent upon each other. However, you can find out this information by providing the primary key, MedicID. Therefore, it is in 1NF. Therefore, this relation is in 1NF, 2NF and 3NF.
Disposition (DispositionID, CodeID, Name, Description)
Each attribute of the Disposition relation is atomic in nature. There is no correlation between the Disposition Name or Description, so the two non-key attributes are not dependent upon each other. These two non-key attributes also do not depending on any foreign key or primary key. However, you can find out this information by providing the primary key, DispositionID. Therefore, it is in 1NF. Therefore, this relation is in 1NF, 2NF and 3NF.
DispositionCode (CodeID, Name, Description)
Each attribute of the Medic relation is atomic in nature. There is no correlation between the Disposition Code Name or Description, so the two non-key attributes are not dependent upon each other. However, you can find out this information by providing the primary key, CodeID. Therefore, it is in 1NF. Therefore, this relation is in 1NF, 2NF and 3NF.
Visit (VisitID, PatientID, ProviderID, MedicID, DispositionID, CheckInTime, CheckOutTime)
Each attribute of the Medic relation is atomic in nature. There is no correlation between the CheckInTime or CheckOutTime, so the two non-key attributes are not dependent upon each other. These two non-key attributes also do not depending on any foreign key or primary key. However, you can find out this information by providing the primary key, VisitID. Therefore, it is in 1NF. Therefore, this relation is in 1NF, 2NF and 3NF.
The final list of normalized relations is given below.
Final Relational Data Model
Please note that a primary key is indicated by underling the attribute(s). A foreign key is shown in italics. Below is the final relational data model:
Patient (PatientID, SSN, FirstName, MiddleInitial, LastName, BirthDate, Company)
Profile (ProfileID, Name, Description)
PatientProfile (PatientID, ProfileID, StartDate, EndDate)
Medicine (MedicineID, Name, Price, Quantity, MinQuantity)
PatientMedicine (PatientMedicineID, PatientID, MedicineID, DosageGiven, StartDate, EndDate)
Provider ( ProviderID, Name, Title)
Medic (MedicID, Name, Title)
Disposition (DispositionID, CodeID, Name, Description)