CQ Implementing System

Question: Discuss about the CQ Implementing System. Answer: Introduction CQ is implementing a system for its medical center. This document illustrates how the system is model based on the case study provided and then a logical design of the system is derived from the ERD. All the relations in the logical design are in 3NF. Entity Relationship Diagram The following is the relationship diagram for the CQMC system. Business rules and assumptions For CQMC to be operational, the following business rules are followed. A customer visits a general medical practitioner and is referred to a CQMC specialist using a referral letter. The customer visits or calls CQMC to make an appointment. The customer does not need to have the referral letter from the general medical practitioner to make an appointment if they go to the center. When the patient makes an appointment, their details are captured in the system. These details include, the name, gender, age and address. After making an appointment a consultation is created and the patient is informed the date. If the patient has the referral letter, details are recorded. These details include the date of the referral, and details of general medical practitioner who are identified by their unique number. The patient visits CQMC on the day of his/her consultation and presents the referral letter from the general medical practitioner if he had not presented it during the day of making an appointment. The specialist attending to the patient gets detail of the consultation from the referral letter from where the specialist conducts procedures and tests on the patient. These tests and procedures have to be recorded. Based on the results the specialist may prescribe multiple prescriptions to the patient. The prescriptions are given after the specialist has checked the history of the patient for cases of allergic reactions to certain drugs. The prescribed medications are recorded in the system. After treatment, the patient makes payments. The patient pays a consultation fee of $120. The other payments are based on the services provided by the specialist. There is a standard price for every service as shown in the table below. Type Fees/Charges Biopsy $75 Excision $175 Laser $250 Other additional charges on the patient are for the medication if any were prescribed. The specialist may offer a discount to the patient. If a patient would like to know the qualifications of the specialist they can get the details from the system. From the case study a number of assumptions were formulated while creating the ERD. These assumptions are justified in the points below; A patient can only visit CQMC with a referral letter from a general medical practitioner. This is rule only applies during the consultation day patients can make appointments without a referral letter but for the to see a specialist they have to present their referral letter. The general medical practitioners who refer patients to CQMC are known and their details exist in the CQMC system thus to identify them a practitioner number is used. This is why a practitioner number is used to identify a referral letter from a patient during a consultation. Tests and procedures done on a patient are solely based on the referral letter presented by the patient. This is because the referral letter always acts as the guide to the specialist If the patient is visiting CQMC for the first time and no details of their history in the system, the specialist has to ask from the patient without referring to the system from where they can update the system. This is because the specialist cannot refer to the system for the patient history if the patient is on first time visit. The specialist decides who gets a discount during payment. This is to enable discounts to be attributed to a specific specialist based on the specialist ID. This will give CQMC accountability over issuance of discounts. Payment for the test and procedures is done based on the table shown above. There are no other prices for those services. The patient gets medication at CQMC for the diagnosis so payment is inclusive of the medication. This helps in totaling the total payment the patient owes CQMC. Justifications. For the business rules and assumptions to work in a real-world application all the rules must work as defined for the system to work correctly. For referrals, every customer must present a referral letter. This makes it easy to create consultations. For discounts, a specialist is responsible for the discounts they give. This ultimately makes everyone responsible on how they issue the discounts as they will be accountable. For the general medical practitioners, it makes much more sense for CQMC to have their details so that a referral letter can be identified based on the practitioners unique ID. All the rules are justified as they facilitate to a correct logical design of the system Logical design From the entity relationship diagram the following relations are gotten. Patient (patientID, name, age, gender, address, phone,email) Appointments (appointmentID, patientID, date, type) foreign key (patientID) references patient (patientID) generalPractioner (GPID, name, phone, address, postalCode) patientAllergyHistory (allergyID, patientID, allergyName) foreign key (patientID) references patient (patientID) patientSurgeryHistory (surgeryID, patientID, name, dateConducted) foreign key (patientID) references patient (patientID) patientHistoryMedications (medicationID, patientID, name, amount) foreign key (patientID) references patient (patientID) consultations (consultationID, patientID, referralID, date, specialistID) foreign key (patientID) references patient (patientID), foreign key (referralID) references referrals (referralID), foreign key (specialistID) references specialists (specialistID) referrals (referralID,GPID,date,patientID) foreign key (GPID) references generalPractioner (GPID), foreign key (patientID) references patient (patientID) specialist (specialistID, name, phone, age, address, postalCode, experienceYrs) patientTests (tID, testID, patientID, consultationID, specialistID) foreign key (testID) references tests (testID), foreign key (patientID) references patient (patientID), foreign key (consultationID) references consultations (consultationID), foreign key (specialistID) references specialist (specialistID) patientProcedures (pID, procedureID, patientID, consultationID, specialistID) foreign key (procedureID) references procedures (procedureID), foreign key (patientID) references patient (patientID), foreign key (consultationID) references consultations (consultationID), foreign key (specialistID) references specialist (specialistID) tests (testID, name) procedures (procedureID,name) payments (paymentID, consultationID, amount,type, date) foreign key (consultationID) references consultations (consultationID) discounts (discountID, amount, paymentID), foreign key (paymentID) references payments (paymentID) specialistQualifications (qID, details, specialistID) foreign key (specialistID) references specialist (specialistID) All the relations are in 3NF because theyre already in 2NF and all non-key attributes are dependent on the key attribute for every relation.