You have been asked to develop a database system for a country doctor’s office. As you might expect, the doctor in question is well trained in the medical profession, but has very little knowledge or experience in developing database systems. Using the concepts covered in this course, you must design and build a system to run the doctor’s office. This project has three phases.
Phase 1
This office has three doctors that serve approximately 100 patients. As a country doctor’s office they are equipped to provide a wide variety of services to their patients. Since they are located so far from the nearest hospital they handle more than you might expect from a primary care physician. Minor surgeries, broken bones, and cuts requiring stitches are routinely performed in the office.
For this phase of the project, you must develop an Entity Relationship Diagram (ERD) for the system. Entity types for this system include:
Doctors,
Patients,
Services (i.e. physical exam, surgery),
Supplies used (i.e. gauze, tape, needles),
Suppliers (provide supplies),
Appointments
Be sure to design in the ability to record the cost to the patient. The purpose of this phase is to communicate with the doctor how you envision the relationships in the data generated by the office. Remember, the doctor is not a technical expert, so a simple way of communicating the design of the system is valuable.
Phase 2
Once the ERD is complete, convert the diagram into tables with the associated fields. Identify primary keys for each table, data types, etc. Also, determine the relationships between tables and the fields that make up those relationships. The purpose of this phase is to communicate between the system designers and the programmers (you) as to the layout of the system.
Phase 3
Using Microsoft Access, build your database. Be sure all the relationships work and all fields have appropriate data types. Input some sample data and run the following queries:
Generate a list of patients who have appointments for today.
Determine which patients most frequently come to the office.
Determine which service is most widely used (i.e. surgery).
Which suppliers provide the most commonly used supplies.
Generate a schedule of appointments for each doctor for the week.
Please provide me the SQL Code for each Phase
