CSI2132 Project
The goal of this project is to enable you to gain experience in the design of an enterprise relational database system as well as its operation. The application domain under consideration is a dental centre. Dental practice management is a challenging job, especially for Dentists who are already busy caring for patients. Hence, it is essential to have the right system to manage the day-to-day operations of clinics. As part of a new information system development project for a dental centre with clinics in major cities across Canada, you are required to develop a Dental Clinic Management System (DCMS) to automate how information is managed. The application description and minimum requirements are presented in the next section. Please note that extensions to the minimal requirements stated are highly encouraged. You can study any dental centre of your choice to get some domain knowledge. Ensure that all the entities, attributes, relationships and constraints needed to design the database application are properly identified. This project should be carried out by a group of at least 4 (+ 1) students to ensure project completion before the submission deadlines.
Application Description:
Dental healthcare professionals are required by law to produce and maintain adequate dental records or dental charts, containing the necessary details of patients and dental history. Dental care services are, in general, communities with dental practitioners (e.g., dentists, hygienists), patients and systems that need constant monitoring and communication with each other. For most dental practices, the availability of a system to manage employee profiles, patient details, treatments, appointments, billings, reviews, etc. is very important to support dental operations.
In this project, you are required to 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
unscheduled),
and room assigned. Each patient and dentist may have zero or more appointments.
(no show, cancelled, completed,
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.
Populate Relations
Your database needs to be populated with data after creating it. You are required to generate sample data for your tables. Please note that adding enough data will make your queries interesting.
Queries:
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):
- Show the list dentists in each branch.
- Add new patients
- Check upcoming appointment with the dentist
- Set a new appointment
- Add a new employee
- 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.
Project Deliverables:
1st Deliverable (due February 21st 23:30):
Please submit a report that includes the following:
- (20%) E-R diagram.
- (10%) Relational schema (please do not print out your data).
- (10%) The constraints that you have defined. Please include a brief justification for each constraint.
2nd Deliverable (due April 6th 23:30):
Please submit a .zip file that includes the following:
1. (30%) a.
b.
Please upload your deliverables on Brightspace by the due dates stated at the beginning of this document. Each group should submit a 10-minutes presentation of the application on Brightspace by 8th of April 2020 23:30.
A report that includes the following:
The technologies used (e.g., DBMS & programming language) used for the implementation of your application.
A list with the DDLs used to create your database
Specific steps to guide someone to install your application.
A recorded presentation of the implemented application
The code for implementing the user interfaces, and SQL code that supports all the functionalities in your application
c.
- (20%)
- (10%)
