CSI2132-Database I Solved 

Develop a Dental Clinic Management System (DCMS) for a dental centre with clinics in major cities across Canada (e.g., Toothworks dental clinics). This will provide an efficient and a fast way for the dental centre to manage appointments and related activities. In addition, a DCMS will enable the dental centre practitioners to track records, minimize data loss, provide privacy & security of records, enable quick report generation, implement updates, eliminate redundant paper work, and save time. Below are a few minimal requirements you need to consider. Note that they can be extended to fit your selected dental centre.

Users: All users, e.g., admin, dentist, etc. must be authenticated before using the system. 

Patient: The patient details such as the address (house number, street, city, and province), patient’s name (first, middle, and last name), gender, insurance, SSN, email address, date of birth, phone numbers, etc. are required. Note that a patient can also be an employee (e.g., dentist, hygienist or receptionist). A patient should be able to book more than one procedure. To register a patient, the patient must be 15 years old or above. Otherwise, someone else (a parent or a responsible party) should be responsible for the patient. Each responsible party should be a registered user. A responsible party or parent is not required to be a patient.  

Records/Patient Charts: Details of the treatment rendered must be recorded in the patient’s chart progress notes by an employee.

Employees: Every employee (e.g., dentist, hygienist, receptionist, etc.) needs to be identified by some basic information, e.g., given names, address, role, employee type, SSN, and salary.

Branches: The dental clinic enterprise is organized into branches, represented by the city in which the clinics are located. Each branch details need to be captured by the database. For each clinic in each city, the employees are managed by a branch manager, who is also an employee. Each branch can have many dentists and hygienists. Each branch cannot have more than two receptionists.

Appointment: Appointment bookings must be captured by the system after a patient is registered. The appointment should indicate the patient, dentist, date, start time, end time, appointment type, status (no show, cancelled, completed, unscheduled), and room assigned. Each patient and dentist may have zero or more appointments.  

Appointment Procedure: An appointment can have multiple appointment procedures. The information captured also includes the patient, date, procedure code (most dental operations used standard codes), procedure type (scaling, fluoride, removal, etc.), description, tooth involved, amount of procedure (e.g., 2 fluoride). Each procedure to be completed will be added to an invoice. Each procedure can have a charge apportioned the patient, the portion for the insurance charge as well as the total charge.  

Treatment: After diagnosis and the result is known by both dentist and patient, an appropriate treatment is provided based on the patient’s condition. Information that needs to be captured are appointment type, treatment type, medication, symptoms, tooth, comments, etc.

Fee Charge: The information required are the fee identifier, procedure, fee code, and charge. Fees are charged for ALL procedures provided at the clinics. Patients who cancel an appointment within 24 hours notice or do not show up for an appointment should be penalized. The fee code for cancellations and no show is 94303, and a charge of $14 is added to the patient’s account. 

Invoice: An invoice will be required to bill the patient for dental services. Information can include the date of issue, complete contact information, patient charge, insurance charge, total fee charge, discount, penalty, patient insurance (if any), etc. Many appointments can be added to an invoice. Fee charged for employee services are 50% of the professional fee. The invoice may be partly billed to the patient, and the remaining value sent to the insurance company.  

Patient Billing/Payment: Each patient billing will have information related to a patient’s visit or an appointment. Each patient’s bill has a unique identifier for each patient. A bill can include multiple appointment procedures for each appointment. Payment made for each bill include a patient portion, insurance portion and total amount. When an insurance claim is made, each payment should capture the payment type and the unique claim identifier. Patients are expected to pay for services on the day the service is completed. Payments can be made through cash, debit card, Amex, Visa or Mastercard payment types. Multiple payment type can be used to pay for the invoice. An employee can pay for the procedure of many patients.

Insurance Claim: Patients can submit electronic insurance claim, which should be applied to the cost of the treatment. 

Reviews: The dental clinic enterprise also needs to keep track of the reviews from the patients. Information that needs to be stored include professionalism of employees, communication, cleanliness and value.

Project Requirements: 

E-R Model   
Construct an E-R diagram representing the conceptual design of the database. Since there are many variations of the original version (which represented relationships as diamonds), you can use other forms you prefer, e.g., IDEFIX, IE CROW’s foot model, etc. At a minimum, you must include all the entities and relationship sets implied. They should not be assumed as completely identified under the project requirements. Be sure to identify the primary keys, relationship cardinalities, etc.

Relational Model  
After constructing your E-R Diagram, you need to translate it into the relational database design. Define the necessary constraints that will ensure the correctness of the database to be created according to your Relational model. These are primary keys, referential integrity constraints, domain constraints and user-defined constraints. Implement the relations in the DBMS, e.g., PostgreSQL, and make sure that you create indices and constraints as appropriate. Whenever you discover flaws that require changes to your E-R Diagram, make sure these changes are captured in your relational model.

This part of the DBMS is very important. Write some queries to perform the following (Note, you can showcase other information that your system can generate):

1.       Show the list dentists in each branch.
2.       Add new patients
3.       Check upcoming appointment with the dentist
4.       Set a new appointment
5.       Add a new employer
6.       Check the types of procedures available

Interfaces:  

Several users will need to make use of the database and each will require special application during access.  
•       Receptionist UI: They need to be able to add patient information, edit patient information, set patient appointments.  
•       Dentists/Hygienist UI: They will need to be able to retrieve the records of appointed patients easily. They will need to track the patient’s data, e.g., check medical history before administering new procedures.
•       Patient UI: They will need access to their records, e.g., medical history, upcoming appointments or schedule with the dentists