You are now ready to write the Data Definition Language (DDL) that will be the p
You are now ready to write the Data Definition Language (DDL) that will be the physical implementation of your database on the Oracle SQL Developer. This includes writing CREATE TABLE statements for each entity of your ERD. Be sure to follow the familiar patterns below for data types, NULL values, and propagating foreign keys under the following rubric for this assignment:
Use variable character data type (“VARCHAR”) as opposed to character in most cases where alpha-numeric values are the norm. Character data type is only appropriate if each value throughout the column will be the same length (such as postal code).
Use INTEGER for all primary key and foreign key columns.
Any column that will have “math”—such as AVG( ) or SUM( ))—will most likely be a numeric or decimal data type. Avoid having INTEGER for any column that will need precise fractions.
Make most columns NOT NULL (we want to avoid allowing “blanks” or empty values into your database). Usually, only description columns are optional.
Code REPLACE INTO statements (five rows) for each table.
What to deliver for this assignment?
A single word document with screen shots that captures:
1. the CREATE TABLE statements for all tables. (show all data types, PKs, and FKs). You might also include ALTER TABLE commands if any.
2. the REPLACE INTO statements for data population. Entities and Attributes
Case study: General description
The Wellness Clinic is a facility providing medical care in a rural area of the country. Its professional staff consists of five medical doctors (physicians), two nurse-practitioners who provide non-acute care and can prescribe medication, two registered nurses, two midwives who provide pre-natal care and supervise delivery except in cases with complications, a pharmacist, and a medical technician. The non-professional staff members include an office administrator, a receptionist, and a bookkeeper who works part time. The clinic serves several thousand patients, each of whom may visit the clinic any number of times per year, both for preventative care such as checkups or immunizations, and for treatment of illness. Its facilities consist of a waiting room with a reception desk, an administrative office, a nurses’ station, ten examining rooms with adjoining consultation rooms, a small operating room, a birthing room, a recovery room, a pharmacy, and a small laboratory.
Basic Operations
The clinic has regular hours of operation weekdays, Saturday mornings, and two evenings per week. Normally two physicians, one nurse practitioner, one registered nurse, and one midwife are in the clinic during regular hours. In addition, the physicians and nurse-practitioners rotate responsibility for covering emergency calls 24 hours per day, 7 days a week. At the end of each day, the receptionist sets up call forwarding so that emergency calls are automatically directed to the telephone number of the person providing emergency coverage. When the clinic opens in the morning, the call forwarding is halted. Two of the physicians are surgeons who perform routine surgery not requiring general anesthesia at the clinic one morning a week, assisted by a registered nurse. Others have specialties in pediatrics and internal medicine. However, all of the physicians can provide general and acute care for any of the patients. Patients who require major surgery or other hospital care must go to a hospital located outside the immediate area served by the clinic. The clinic physicians do not normally visit their patients who are in the hospital, instead leaving their care to the hospital staff with whom they communicate during the hospitalization. However, the clinic provides both pre- and post-hospital care for the patients.
Hours of operation are divided into scheduled appointments and unscheduled hours, which are open for walk-ins. Patients usually schedule checkups and immunizations well in advance. Patients suffering from chronic or acute illness can usually schedule appointments promptly, or they may come in during the unscheduled hours. The administrator is responsible for setting up all schedules, and for keeping records updated. Prior to the beginning of each month, the administrator makes up complete coverage schedules for all professional and support staff. The bookkeeper is responsible for doing all billing and recording payments. The receptionist is responsible for making patient appointments, for handling traffic, and for making the patient’s medical records available during the visit. The nurse prepares the patient, takes medical history, performs some medical routines or tests, takes samples for lab tests, updates the medical record, and assists the practitioner (the physician, nurse practitioner, or midwife) during the visit. The practitioner examines the patient, administers medical treatment, can perform some tests, can also take samples for lab tests, and writes prescriptions for medications or orders for additional lab tests during a visit. Each visit results in one or more diagnoses, which the practitioner adds to the patient’s medical record, along with any comments or observations. Prescriptions can be filled at the clinic’s pharmacy or they can be sent to another pharmacy at the patient’s request. Some laboratory tests are performed at the clinic by the medical technician, using samples taken by one of the professionals. More specialized tests are performed at an outside medical laboratory. Whenever possible, specimens, such as blood samples, are taken at the clinic by one of the professionals there and sent to the laboratory. If the lab test requires the presence of the patient and equipment that is not available at the clinic, the patient is sent to the outside laboratory for the test, and results are sent back to the clinic.
Medical care is provided for all patients, regardless of their ability to pay. Bills are generated based on the services provided, not on the payment method. Private patients who can afford to pay out of pocket can do so at the time of service or be billed at the end of each month. Those who have medical insurance provide information about their insurance policies, and the insurance companies are billed. Usually in that case patients pay a small amount of co-insurance (co-pay), which is determined by the type of policy they hold, at the time of the visit. Those who cannot afford to pay normally have government-provided health care, for which they have a government-issued medical card. They pay nothing and the clinic is reimbursed by the government for the entire cost of the visit, including any lab tests performed and medications dispensed there. A small number of indigent patients who do not have health coverage are treated and the cost is absorbed by the clinic until they qualify for government-provided coverage.
Information Needs
Currently all information about patients and their care is kept manually, and billing information is kept on a spreadsheet. Physicians use computer or telephone communications to provide information to the hospital and receive information about patients who need hospital care. The clinic has computer access to hospital records for its patients, as well as on-line systems provided by insurance companies and the government for third-party billing. It needs a database that keeps track of all the patient-related activities of the clinic and to provide information about billing and payments. The database will not keep track of medical supplies, plant maintenance, or payroll information.
The following forms or reports are needed.
Patient Intake Form This form is filled out by patients prior to or during their initial visits. It contains contact information, medical history, list of current medications, and insurance information. The data is updated for each visit.
Weekly Coverage Schedule This schedule lists the daily hours and the professional and non-professional staff who are scheduled to be in the clinic at specific times each day of the week. It also lists the name and telephone number of the person who is covering for emergencies during all hours each week. (Recall that the administrator provides the coverage information each month.)
Daily Master Schedule This is a master schedule for all practitioners for each day. It should list each of the practitioners who are in that day, with all appointments scheduled for them. Most appointments are allocated 10 minutes each, so each hour has 6 timeslots. However, some appointments are given more than one timeslot, depending on the nature of the care needed. Each professional has hours dedicated to walk-ins during which no pre-scheduled appointments are made. As walk-ins sign in for care, the receptionist assigns them to a practitioner (a physician, nurse-practitioner, or midwife) and adds the patient’s name to the schedule. The registered nurses do not have appointments scheduled, and are available to assist the practitioners with visits, or to administer tests or take samples on an unscheduled basis. The lab technician also does not have an appointment schedule.
Individual Practitioner’s Daily Schedule Each of the practitioners should receive a printed copy of his or her own schedule for any day he or she is in the clinic. Appointments list the patient’s name and the reason given for the visit. The copy is updated manually by the receptionist as visits for walk-ins are conducted.
Provider’s Statement for Insurance Forms This is a pre-printed form that is used as a receipt primarily for insurance purposes. It lists the clinic name, address, and telephone number, along with the names and tax identification numbers of all the practitioners on the staff. It also lists all the types of visits, the procedures that can be performed, with a code for each, and some blank lines for “other”, along with a line for entering the fee for each. It also has a list of the common diagnoses and codes, with a few blank lines for “other”. At the bottom are lines for Total Charge, Amount Paid, and Balance Due. The provider uses this form during a visit, to record visit type, procedures performed, and diagnosis. When the patient checks out after the visit, the receptionist checks off a box showing which practitioner provided services, fills in the fee for each service using a fee schedule, calculates the total, and writes in the amount paid, if any, and the Balance Due. One copy is kept by the clinic and another is given to the patient. The same information is submitted electronically to the private or government insurer.
Patient Monthly Statement Any patient who has an unpaid balance receives a statement that is compiled at the end of each month, listing all the services provided that month, any payments received, and the balance due.
Precription Label and Receipt This form consists of two parts. The top part is gummed and used as a label for the container in which medication is dispensed. The label shows the Rx Number, Doctor Name, Patient Name, Patient Address, Directions, Drug Name, Form, Strength, Quantity, Pharmacist’s Name, Date Filled, Original Date, and Number of Refills Remaining. The bottom part repeats the information on the label, and also lists the total price of the medication, the amount covered by insurance or the government, and the balance due from the patient, as well as more information about the drug, complete directions for use, and warnings about possible side effects and drug interactions. The receipt can be used for submitting claims for insurance coverage. This information is also submitted electronically to the private or government insurer.
Daily Laboratory Log This log is used to record all lab tests performed each day.
Operating Room Schedule This schedule provides information about all scheduled surgeries for the day.
Operating Room Log This records information about the surgeries actually performed on a given day, including identification of the patient, surgeon, and nurse, and notations and observations about the surgery.
Daily Delivery Room Log This records information about all the deliveries performed each day.
Recovery Room Log This report records information about the use of the recovery room, including the patient’s name, attending practitioner, bed, date in, time in, date out, time out, and signature of the practitioner who signs the patient out. A nurse records the times and results of any medical checks performed while the patient is in recovery.
Monthly Activity Report This is an internal report summarizing the clinic’s activity each month. It shows such items as the number of visits conducted by each provider, the number of surgeries performed, the number of deliveries, the number of lab tests broken down by type, the number of prescriptions dispensed, the average time per visit, and so on
PatientPatientID (PK)
PractitionerPractitionerID (PK)
PractitionerType
PractitionerName
AdministratorAdminID (PK)
AdminDetails
ReceptionistReceptionistID (PK)
ReceptionistDetails
BookkeeperBookkeeperID (PK)
BookkeeperDetails
Medical TechnicianTechnicianID (PK)
TechnicianDetails
PharmacistPharmacistID (PK)
PharmacistDetails
Medical RecordRecordID (PK)
PatientID (FK)
AppointmentAppointmentID (PK)
PatientID (FK)
VisitVisitID (PK)
PatientID (FK)
PrescriptionPrescriptionID (PK)
VisitID (FK)
PatientID (FK)
Laboratory TestLabTestID (PK)
VisitID (FK)
SurgerySurgeryID (PK)
DeliveryDeliveryID (PK)
Recovery RoomRoomID (PK)
BillingBillingID (PK)
VisitID (FK)
PatientID (FK)
…
Insurance CompanyInsuranceID (PK)
GovernmentGovernmentID (PK)
Relationships:
Patient-Appointment: One-to-Many
Practitioner-Appointment: One-to-Many
Practitioner-Visit: One-to-Many
Patient-Visit: One-to-Many
Visit-Medical Record: One-to-One
Visit-Prescription: One-to-Many
Visit-Laboratory Test: One-to-Many
Visit-Billing: One-to-One
Patient-Billing: One-to-Many
Prescription-Billing: One-to-One
Pharmacy-Prescription: One-to-Many
Surgery-Practitioner: Many-to-Many
Surgery-Patient: Many-to-One
Delivery-Practitioner: Many-to-One
Delivery-Patient: Many-to-One
Relationships:
Staff – Coverage Schedule (One to Many).
Patient – Appointment (One to Many).
Patient – Patient Monthly Statement (One to Many).
Practitioner – Provider’s Statement (One to Many).
Professional Staff – Appointment (One to Many).
Operation of Visits:
1.Appointment Scheduling: The first step involves a patient calling in to schedule a visit.
2.Conducting Visits: The visits are conducted by professional staff and each visit is associated to a patient and a staff member.
3.Room Allocation: Visits may use one or more rooms for consultations, examinations, or procedures.
4.Referrals: Visits may result in referrals to other healthcare providers, recorded within the Visit table or a separate Referral table.
5.Charges and Payments: Visits incur charges for services provided, recorded in tables such as Billing or Payments, including details of services rendered, costs, and payment status.
6.Prescriptions and Diagnostics: A visit may result in prescriptions for drugs or lab work, which are recorded in the Prescription table together with any diagnostic procedures and tests carried out during the appointment.
Submission Type : Word document & oracle code no plagarism