I designed a class diagram but I am not sure if it is correct. Below is the requirements for the diagram and attached is the diagram itself. Can someone help??
A clinic with three dentists and several dental hygienists needed a system to help administer patient records. This system does not keep any medical records. It only processes patient administration.
Each patient has a record with his/her name, date of birth, gender, date of first visit, and date of last visit. Patient records are grouped together under a household. A household has attributes such as name of head of household, address, and telephone number. Each household is also associated with an insurance carrier record. The insurance carrier record contains name of insurance company, address, billing contact person, and telephone number.
In the clinic, each dental staff person also has a record that tracks who works with a patient (dentist, dental hygienist, x-ray technician). Since the system focuses on patient administration records, only minimal information is kept about each dental staff person, such as name, address, and telephone number. Information is maintained about each office visit, such as date, insurance copay amount (amount paid by the patient), paid code, and amount actually paid. Each visit is for a single patient, but, of course, a patient will have many office visits in the system. During each visit, more than one dental staff person may be involved in the visit by doing a procedure. For example, the x-ray technician, dentist, and dental hygienist may all be involved on a single visit. In fact, some dentists are specialists in such things as crown work, and even multiple dentists may be involved with a patient. For each staff person does procedure in a visit combination (many-to-many) detailed information is kept about the procedure. This information includes type of procedure, description, tooth involved, the copay amount, the total charge, the amount paid, and the amount insurance denied.
Finally, the system also keeps track of invoices. There are two types of invoices: invoices to insurance companies and invoices to heads of household. Both types of invoices are fairly similar, listing each visit, the procedures involved, the patient copay amount, and the total due. Obviously, the totals for the insurance company are different from the patient amounts owed. Even though an invoice is a report (printed out), it also maintains some information such as date sent, total amount, amount already paid, amount due and also the total received, date received, and total denied. (Insurance companies do not always pay all they are billed.)
My interpretation of your requirements would be as follows(i have put CLASSES IN CAPITALS and relationships in brackets 1 to many represented as (1,*) etc )
1. CLINIC (OPTIONAL CONTROL CLASS) has STAFF (1,*)
ie, each clinic has one or more staff members
2. CLINIC has PATIENTS (1,*)
each clinin has 1 or more patients
3.STAFF works on PATIENT (*,*)
ie, 1 or many staff can work on 1 or many patients
4. PATIENT belongs to HOUSEHOLD (1,1)
ie. each patient belongs to only one household(assumption)
5.HOUSEHOLD has INSURANCE_POLICY (1,*)
(another assumption each household has 1 policy covering all)
6. PATIENT HAS INVOICE (1,*)
(assumed patient has 1 or more invoices associated with visit if insurance only cobvers part payment)
If you had to map this to a relational schema i would create a table for each class highlighted above and two further tables (with composite keys detailed) namely
STAFF_WORKS_ON_PATIENT (or PATIENT_TREATMENTS) using (staff_no,patient_no,date,invoice_id) as PK and PATIENT_INVOICES(patient_no,invoice_no,invoice_dat e)
You should be able to record and manipulate all you need from these classes.
Invoices can be identified by type (ie personal or commercial).
You can simplify the invoices by assuming you use the same invoice number for a course of treatment. ie. several visits may be required but invoice number is allocated on first visit and recorded against each treatment but billed only once on completion of visit.
For this you want to sum all costs from treatment table and group by invoice id.
in your diagram i don't see any need to split invoices into subclasses and certainly not subclasses of the patient class. If you want to split invoice into 2 subclasses then they should be subclasses of the INVOICE class. However you seem to be storing same data for both so no need to split up.
But I do need help with my SQL*Plus code - if anyone can help me out before the day is over with I would really appreciate it.
I had to create a stored program unit procedure named UPDATE_ENROLLMENT that receives a specific student ID and course section ID as input parameters named CURRENT_S_ID and CURRENT_C_SEC_ID. Then my procedure is supposed to insert the S_ID and C_SEC_ID values into the ENROLLMENT table in my database with the specification that the grade value is NULL. Then I had to write a command to execute the procedure from SQL*Plus command line, and pass input parameter values of 6 for S_ID and 10 for C_SEC_ID. so did I do it right?? the database I am using is attached - please help!
---create procedure
CREATE OR REPLACE PROCEDURE update_enrollment
(current_s_id IN NUMBER,
current_c_sec_id IN NUMBER
)
IS
BEGIN
--insert
UPDATE enrollment
SET grade = NULL
WHERE s_id = current_s_id AND
c_sec_id = current_c_sec_id;
END;
/
Reply With Quote
