Task for the access database "Fee-based polyclinic".
Description of the subject area
You are the head of the paid polyclinic planning service. Your task is to monitor the financial performance of the clinic. The clinic employs doctors of various specialties who have different qualifications. Every day patients come to the polyclinic. All patients undergo mandatory registration, in which standard personal data (name, surname, patronymic, year of birth) are entered in the database. Each patient can go to the clinic several times, in need of various medical care. All appeals of patients are fixed, at the same time the diagnosis is established, the cost of treatment is determined, the date of treatment is remembered.
Doctors (Doctor´s code, Family name, First name, Patronymic, Specialty, Category).
Patients (Patient´s code, Family name, First name, Patronymic, Year of birth).
Appeals (Treatment Code, Doctor´s Code, Patient Code, Date of Treatment, Diagnosis, Treatment Cost).
The development of the formulation of the problem
As a result of the operation of the database, it was found out that when you go to a polyclinic, the patient is examined and treated by different specialists. The total cost of treatment depends on the cost of those consultations and procedures that are assigned to the patient. In addition, discounts are provided for certain categories of citizens. Add changes to the table structure that take into account these facts, and modify existing queries. Add new queries.