Planning
The selected business case is for health center patient reservation by Canadian Health Systems. The initial assessment of this case shows that it requires a database to improve the effectiveness of its work in the clinic reservations for patients in Dubai. Currently, Canadian Health Systems experiences significant challenges in the management of patient records because it uses separate files for patients, doctors, and pharmacy. The harmonization of these separate files necessitates the use of a relational database that allows patients, doctors, and pharmacy to share records in real-time. Other expected advantages of the system are that the database eliminates redundancy, assures accuracy, and eases accessibility of records. The implementation of the database is technically visible because there is a relationship between the data of patients, doctors, and pharmacy. Moreover, since the database is easy and simple to use, manage, and maintain, it does not require extensive training. Concerning economic feasibility, a relational database is affordable because it does not require a complex computing infrastructure. Canadian Health Systems requires a high-speed internet, computers, website, and database to implement the database system. Therefore, the initial assessment of the requirement of a database by Canadian Health Systems is not only necessary but also technically and economically feasible for effective storage and management of records.
Analysis
The analysis of the proposed database indicates that it must meet numerous user requirements, complement existing system evaluation, and comply with logical system design. Since the database is for clinic reservation, patients should understand how they should enter their details accurately into the system without introducing errors. In this view, the data entry form should contain brief information that guides patients on how to enter their details. Additionally, the data entry form for clinic preservation ought to be simple and easy to fill for patients to avoid confusion. As doctors are also users of the system, they should understand how to enter their details, diagnosis, and prescriptions. After examining their patients, doctors have important information that they have to share with pharmacists so that they can dispense appropriate drugs. The database also requires pharmacists to have relevant knowledge of entering data into the database. Pharmacists should define the type of medication, description, name, and costs and enter into the database for effective management of patients.
The evaluation of the existing depicts that Canadian Health Systems relies on emails shared between patients, doctors, and pharmacists. The comparison of the effectiveness, efficacy, and accuracy of the existing system and the proposed database allows evaluation functions. The expected evaluation is that the database would have a higher level of effectiveness, accuracy, and efficacy in the reservation of clinics and the management of patient data than the existing system of mails. To improve accuracy of data entry, the logical system design should specify data type, contain validation rules, and comprise input masks. The data type ought to indicate if data is number, currency, short text, long-text, or date. Validation rules ensures that users enter correct information by guiding them on how to put correct details. Input masks also help users to enter values in the correct format for the database to accept and process for storage.
Detailed Systems Design
The analysis of the business case of Canadian Health Systems reveals that it comprises three tables for patients, doctors, and pharmacy. The creation of the database shows that it must contain the three tables to allow the formation of entity relationships. The table of patients (Patients Data) has PatientID, PFirstName, PLastName, DOB (date of birth), and home address. The table doctors (Doctors’ Data) contain DoctorID, DFirstName, DLastName, PatientD, DOA (date of appointment), and specialization. The table of pharmacy (Pharmacy Data) holds PharmacistID, PatientID, MedicationType, MedicationName, Description, and Cost. The primary keys for patients’ table, doctors’ table, and pharmacy table are PatientID, DoctorsID, and PharmacistID. The foreign key, which is PatientID, is common in all the three tables, hence, allowing the creating of the entity relationships for the database. Figure 1 below shows an enitity relationship diagram of the proposed database for clinic reservation of patients in Dubai for Canadian Health Systems.
Comparison of the tables shows that detailed system design that includes your final DB proposal with all. tables and fields, entity relationship (ER) diagrams and a sample GUI for the database for phase 3 (6 Marks).
Implementation
In the creation of the database, the case proposes to use structured query language (SQL), which is a program that communicates with the database and allows storage, manipulation, and retrieval of data. To create database, the SQL code of CREATE DATABASE will be used to form the database named “Patient Reservation Database.”
- CREATE DATABASE Patient Reservation Database
The three tables of the proposed database will be created using the code CREATE TABLE. Moreover, records (fields) were inserted into the created tables using the code INSERT INTO. In the table of Patients’ Data, Patient ID, First Name, Last Name, date of birth, gender, and address were inserted. In the table of Doctors’ Data, Doctors ID, First Name, Last Name, date of appointment (DOA), and patient ID were inserted. Likewise, pharmacy ID, patient ID, medication type, medication name, description, and cost were inserted into the table of Pharmacy Data. The codes ALTER TABLE and ADD PRIMARY KEY were used to indicate primary keys in each of three tables created.
- CREATE TABLE Patients’ Data
- INSERT INTO Patients’ Data (PatientID, PFirstName, PLastName, DOB, Gender, HomeAddress)
- ALTER TABLE Patients’ Data ADD PRIMARY KEY PatientID
- CREATE TABLE Doctors’ Data
- INSERT INTO Doctors’ Data (DoctorID, DFirstName, DLastName, DOA, PatientID)
- ALTER TABLE Doctors’ Data ADD PRIMARY KEY DoctorID
- CREATE TABLE Pharmacy Data
- INSERT INTO Pharmacy Data (PharmacistID, PatientID, MedicationType, MedicationName, Description, Cost)
- ALTER TABLE Pharmacy Data ADD PRIMARY KEY PharmacistID
Tables and records can be deleted by using code DELETE FROM to specify the nature of deletion in the database. For example, to delete the table of Patient’s Data, the SQL code would be:
- DELETE FROM Patients’ Data
To delete a record of DOB from the table of Patients’ Data, the SQL code could be:
- DELETE FROM Patients’ Data WHERE DOB = 1980
In the retrieval of data from the database, the SQL code is SELECT FROM. For example, to retrieve DOB from the table of Patients’ Data, the SQL code is:
- SELECT DOB FROM Patients’ Data
Maintenance
The conversion strategy MySQL to eases the management of the database by the company. Since MySQL is not only simple but also easy to use, its conversion would enable the company to store, manage, and retrieve data effectively from its databases via the internet or cloud computing.