The Dentist’s Office Database



The Dentist’s Office Database (20 points)

Designing a relational database

Preview

In this case, you will design a database for a dentist’s office. Your first task will be to brainstorm and sketch out the required fields and tables, and then determine how they relate to one another. Once this is done and has been approved of by your instructor, you will begin creating the database system from scratch. This creation process will involve the development of multiple tables, linking them correctly, creating queries to support needed forms and reports, and creating appropriate forms (including switchboards) and reports.

It will be important that the database can be “driven” by someone unfamiliar with databases or MS Access, thus, this project will also involve the creation of a user interface that will allow the user to use the database. Specifically, this will involve the use of “Switchboards,” and navigation command buttons placed on forms.

All the basic information you need to develop the database system has been provided, however, you may find that you need to customize certain aspects of it and be creative in your developmental process.

Preparation

o Review all previously assigned readings and video clips related to the development of relational databases.

Background

While at the dentist’s office, you overheard the receptionist complain about all the handwritten paperwork required to run the office. Being familiar with database design and MS Access, you mention to the receptionist that you may be able to help them develop a computerized database system that would alleviate part of their paperwork problem and result in a higher level of productivity. The receptionist has indicated that the owners may be interested in pursuing this option and will ask them to contact you.

Below is the letter you received from the owners requesting your assistance with the development of a database system that can facilitate the operation of the dental practice.

Step 1: Normalization

You first must brainstorm the required fields, assign field names, and determine primary keys and field data types, and then separate the fields into appropriate tables. Once the tables are created, they then must be linked correctly (Primary Key to Foreign Key).

Do not proceed to “Step 2” until you have confirmed with your instructor that your tables’ fields and table relationships are correct.

o Determine the required fields.

o Do not create a field if its value could be created by “calculated field”, i.e., you SHOULD NOT create a field called “Total_Dental_Ass_Pay” because this will be a calculated field that can use the “Check-in”, “Check-out” and “Dental_Ass_Pay” fields as the basis for the calculation.

o Determine data types and field properties for each of the fields (i.e., date fields, limit the size of the text fields (Zip Code does not need to be the default setting of 50 characters in length), currency, text, etc.).

o One of your tables should contain a logical (yes/no) field.

▪ Hint. Insurance (did you know that pressing the space bar is an easy way to check/uncheck a logical (yes/no) field.

o Remember you need fields to record the start and finish time for each appointment.

o Assign the identified fields to the appropriate tables. It is a good idea to start with your simple “lookup” tables first, i.e. “Patients”, “Procedures”, etc.

o Remember, proper table structure should minimize “data redundancy.” Look up this term if you don’t remember its meaning.

o Don’t forget to look for “determinants” to determine the tables needed (see previous video clips).

o Create your “transaction” tables after you create the “lookup” tables. You will likely need an “Appointments” table and an “Appointment_Details” table.

o Note. Refer back to the development of the simple business database table structure covered in previous modules. The “Appointments” form you will need is equivalent to the “Invoice” form and the “Appintment_Details” form you’ll need is equivalent to the “Invoice_Details” form.

o It is probably beneficial to sketch out the database design on paper, or use Excel, before actually creating it within the database application.

o Create and relate you tables in Access.

Before moving into the next Steps of this project, please confirm with you instructor that your fields, tables and table relationships are correctly established.

Step 2: Forms

Step 2A: Creating Forms for data entry

Create the necessary forms for entering data into the “Lookup” tables (Dentists, Dental Assistants, Patients and Procedures tables).

• These all represent forms that allow us to enter/edit data within the “lookup” tables.

• Use these forms to enter the following information into the database.

Enter data into each of the “Lookup” tables

Dentist Data:

Enter data for two dentists into the “Dentist” table. Use YOUR NAME as one of the dentist and make up the other one.

Dental Assistants:

Enter data for three dental assistants. You will need to make up the specific data.

Patients:

Enter the provided patient data.

o Half of the patients should NOT have dental insurance; you choose who they are.

Greg Hapless 10 15th Ave., Rochester NY 14566

Mary Morris 108 Main St., Rochester NY 14566

Joe Brady 008 Purnell, Newark NY 14899

Ellen Monk 009 Purnell, Newark NY 14899

Maria Ortez 16 E. 5th Ave., Potsdam NY 14666

Larry Goldstein 217 Oak Ave., Potsdam NY 14666

Patricia Buckley 27 DePue, Pensville NY 14665

YOUR NAME Address Zip Code

Fig: 4-1

Procedures:

Enter the following dental procedures and assign a retail price to each.

o Available Dental Procedures

• Cleaning

• Crown

• Filling

• Root canal

• X-ray

• Teeth Whitening.

Step 2B: Multi-table forms w/sub-forms

Create the necessary forms for entering data into the “Transaction” tables. Be patient with this one and refer back to previous video clips that covered the creation of an “Invoice” form for a simple business scenario for help.

• ***YOU SHOULD HAVE A PRINTOUT OF YOUR TABLE RELATIONSHIPS IN FRONT OF YOU BEFORE YOU DO THIS.***

o The transaction tables are the “Appointments” and “Appointments_Detail” tables; however, the forms built to populate these tables will also include fields from the “lookup” tables.

• i.e., There should be a “Patient_ID_F” (the F indicates it’s the foreign key) that is part of the “Appointments” table.

• This field should be included in the “Appointments” form, when the field is populated with data you will want other information pulled (“LookUp”) from the “Patients” table to appear. In other words, the user will select the “Patient_ID_F” value and then other fields such as the Patient_FN, Patient_LN, etc., should automatically populate within the form.

▪ Hint. The “Appointments” form can also be the same form used to check-out patients.

o The main transaction form will be the “Appointment” form; within this form will also be a subform (you can call it the “Appointment Details Subform,” that will be used to enter the procedures for each appointment.

• Don’t forget, you’ll need a “Quantity” field to indicate how many times the procedure was done.

o Test your form (your form should be a form with a sub-form) to ensure it works correctly.

• This form will include the use of multi-table forms and subforms.

o Once you have it working as you want, enter a number of appointments and appointment procedures.

o Make sure some patients have multiple procedures performed on them during a single appointment.

Form Summary

At this point, you should have forms created that allow the user to enter/edit data for Dentist, Dental Assistants, Patients, Procedures and Appointments (including the details of the appointments such as the procedures performed).

• Refine the functionality of the forms by adding appropriate drop down boxes, formatting and other navigation features.

• Protect/Disable the form fields that are not needed for data input. This would be mostly applicable to the Appointments and Appointments Details form.

Step 2B: Multi-table forms w/sub-forms (continued)

Sample “Appointments” form showing the main part of the form (multi-table form) and the included sub-form (which also happens to be a multi-table form). Your form should be similar in functionality but may look and operate a bit differently.

[pic]

Step 3: Generating Reports

Report 1

Create a report that displays the total pay for the dental assistants.

o Should be based on a parameter prompt query that prompts the user for a start & end date.

o Within the query, calculate pay per assistant per appointment

o Total_Pay: ([End_Time]-[Start_Time])*24*[HourlyRate]

o Use this query as the “Record Source” of Report 1.

o Display total pay for each Dental Assistant for each Day and include a total for all hours worked for each assistant.

o This will require you to set up grouping sections within the report:

▪ Group by Dental Assistant, then by Date

o Preview the report

o Refine the formatting of the report.

o The format of your report may look similar to the portion of the one that follows (actual dates and pay will vary)

[pic]

Report 2

Create an itemized bill for each customer who is NOT insured.

o Again, create a query to serve as the “record source” of the report.

o The itemized bill should include patient’s name, address, date of procedure, type of procedure, and cost of procedure.

o Total Cost for each patient should also show on the bill

o After your adjustments, the format of your report will look similar to the portion of the report that follows.

[pic]

Report 3

Generate a report of all patients who do not have insurance.

Additional Forms & Reports

Create additional Forms & Reports as you see necessary.

Step 4: The User Interface

• Once all the essential Forms and Reports are ready, its time to create an interface that ties all the objects together into a system that the user will use to operate the database.

• Your goal is to create a “user interface” that allows the user to interact with the database but does not allow them to change the database structure or programming. This means you want the user to be able to access forms and reports but nothing else (NO TABLES, QUIRIES or MACROS).

• This goal can be accomplished through the use of Switchboards and command buttons placed on individual forms.

o The command buttons activate “Macros”; they can do things like close one form and open another, among many other processes.

• Create a Switchboard from which the database can be operated.

• Make use of command buttons placed on individual forms to facilitate the operation of the database.

• The user should be able to start with your switchboard and using it as the starting point, operate the entire database.

Step 5: Final Refinement

• Set “Start-up” options so that when the database is opened the Switchboard (or home form) is automatically opened, and the “Navigation Pane” is NOT accessible to the user.

o This is a weak form of security but will keep the casual user from having access to the tables, queries and macros that facilitate the operation of the database.

▪ Remember. Your goal is to create a “user interface” that allows the user to interact with the database, but not to change the database structure or programming. This means you want the user to be able to access forms and reports but nothing else.

o The startup options can be over-ridden by holding down the “shift” key when opening a database file (thus the reason it is a very weak form of security).

• To set the start-up options:

1. Click the Microsoft Office Button (upper left corner).

2. Click the Access Options button in the bottom right corner of the menu.

3. Click the “Current Database” category on the left side of the Access Options dialog box.

4. Under Application Options section look for the option called "Display Form.” Select your form name from the combo box (keep in mind the Switchboard is a type of form).

5. Look for the “Display Navigation Pane” option and make sure it is NOT selected.

6. Click Ok to close the Access Options.

7. The next time you open the database your form will load on Startup and the Navigation Pane will not be displayed.

[pic]

Step 6: Test the Database:

• When the database file is opened, the switchboard should automatically be displayed.

• The “Database Window” should not be accessible (due to the start-up options).

• All forms should be accessible through the switchboard allowing the user to enter/edit any data necessary.

• All reports should be accessible through the switchboard allowing the user to view and print any of them.

• Practice entering data, review the report output, test your navigation features (drop down boxes, command buttons, etc.). DOES it all work?

Once you have ensured you have a functioning database system, please submit the completed file to your instructor.

-----------------------

Dear Database Systems Developer:

We would be delighted for you to assist us in setting up a computerized database billing system for our dental practice. Here is some basic information regarding how our office works.

Our office employs multiple dentists and dental assistants. For each appointment, we assign a single dentist and a single dental assistant. The Dentist is on a pay system that we do NOT need to integrate into the database system. The dental assistants, on the other hand, are paid hourly based on the length of time required for an appointment (no appointment – no pay) and we DO want to be able to track their pay.

Our patients are charged based on the individual procedures performed during each office visit, thus we have a list of procedures and their associated costs. Please note that sometimes procedures may be performed more then once during an office visit and thus we need a way to indicate multiple occurrences (for instance, there may be two fillings completed during a single visit).

We want the database system to allow our office staff to do several things, these include:

• Keep records related to Patients, Dentists, Dental Assistants and their pay information, Appointments and procedures performed, and procedures available from our office.

• We will need input forms to allow the entry of simple information such as that related to Patients, Dentists, Dental Assistants and available procedures.

• We will need a way to make appointments for our patients, and then be able to indicate the amount of time required for the appointment.

• When an appointment is concluded, we need a way to indicate the procedures performed on the patient during that specific appointment.

o i.e., Mrs. Young comes in to have a root canal. When she leaves, the office staff would check her out in the computer system by looking up the original appointment information and adding to it her check-out time (needed to calculate Dental Assistant pay), and the procedures performed (in this case, a root canal). Note that multiple and different procedures might be performed on a patient during one visit. For example, Mrs. Young might have three fillings and an x-ray during one visit.

We also need to track the number of hours the dental assistants work. Even though we charge the patient a set fee PER procedure, the dental assistant is paid by the hour. Therefore, we’ll need to use the start and finish times of each appointment to calculate the pay for each assistant. We would then like to generate a report based on a given time period (start date, end date), that would indicate the pay per each dental assistant.

Third, we want to generate monthly bills to send to our patients who do NOT have dental insurance. These bills should show the name and address of the patient, the procedures performed, the date of service, the cost for each procedure, and the total bill. We need to be able to indicate the start and end dates for this report.

You assistance with this project is greatly appreciated and will allow us to operate in a more efficient manner.

Sincerely,

Dental Office Ownership

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download