Hi
I have a question i hope to get some help and advice.
I have a case study which is this.

"The Get Well Hospital (GWH) is considering creating a database to keep track of all aspects of the hospital.

GWH wants to store the information about employees, patients, wards, beds, and treatments in the database.

Each employee has a NHS Number, name, and salary. For some types of employees, additional information should be recorded. For a doctor, the highest degree obtained and where and when the degree is obtained should be recorded. For a nurse, the level of skill (1 to 5) should be recorded.

Each patient has a NHS Number, a name, an address (street, city, county, and postcode), and several contacts (name and phone number). The patient’s disease should also be recorded.

GWH has several wards with different functions. Each ward has a number, name, main function, and number of beds.

Each ward has several beds that may contain patients. Beds are numbered within the ward. Two wards could both have a bed 123. The patient will be placed in a bed in a ward relevant to his/her disease.

Several nurses work in each ward and one of them is in charge of the ward. The nurse in charge of a ward must have a level of at least 4. There must be at least the same number of nurses as the number of beds in any ward.

A Patient is admitted to the hospital on the advice of the consultant doctor.

The patient will be treated by a number of doctors with various treatments. Each treatment will begin at a specified date and place, and will be associated with a fee. A unique treatment number is assigned to each treatment. A doctor cannot treat more than 20 patients on any single day. Every treatment has a result that is stored as medical history for the patient."







I have been try to design the ER diagrame for this case study but I'm not sure if it's correct.
Here it is what I have done.
Task A

Develop a top-down design for the above database system in the form of an entity-relationship diagram. State any assumptions made.

Task B

Produce relations from the E-R model
Tables: Employees, Patients, Wards, Beds, Treatments


Employee: NHS_Employee_Number (Unique), Name, Salary Position, Doctor_Degree_Level, Doctor_Degree_Location, Doctor_Degree_Date, Nurse_Skill_Level (1-5), Ward_Number, Bed_Number, Nurse_Ward_Status


Assumption: There must be a method of associating the Employee (Nurse) to the Ward and Bed to locate them in the system



Patients: NHS_Patient_Number (Unique), Name, Address_Street, Address_City, Address_County, Address_Postcode, Contact1_Name, Contact1_Number, Contact2_Name, Contact2_Number, Contact3_Name, Contact3_Number, Patient_Disease, Consultant_Doctor_Reference




Ward: Ward_Number (Unique), Ward_Name, Ward_Function, Ward_Bed_Count




Assumption: Bed numbering in different wards overlaps (e.g. each ward may have a bed 1, 2, …) the Ward Number must be applied to the Bed Table to remove the possibility of duplication


Bed: ID (Unique), Ward_Number, Bed Number


Assumption: Database constraints or programming would be used to limit the number of patients treated by a doctor to no more than 20 in a day.

Assumption: There must be a method of associating the Patient to the Ward and Bed to locate them in the system.

Assumption: The patient could be moved from ward to ward if the disease requires multiple different treatments, or the patients condition changes

Treatment: Treatment_Number (Unique), NHS_Patient_Number, Ward_Number, Bed Number, Treatment_Type, Treatment_Date, Treatment_Duration, Treatment_Location, Treatment_Result, Treatment_Fee, NHS_Employee_Number




Task C

Identify the attributes required for the system, and proceed to fully normalise these to 3NF thereby developing a bottom up design using the normalisation process

I would be inclined to make the following changes


Employee: NHS_Employee_Number (Unique), Name, Salary,Doctor: NHS_Employee_Number (Unique), Degree_Level, Degree_LocationNurse: NHS_Employee_Number (Unique), Ward_Number, Bed_Number, Skill_Level(1-5), Status

Treatment: Treatment_Number (Unique), NHS_Patient_Number, Condition_Number, Bed Number, Treatment_Date, Treatment_Duration, Trestment_Result


Condition: Condition_Number (Unique), Ward_Number, Resource_Number, Treatment, FeeResource: Resource_Number (Unique), NHS_Employee_Number, Location


Assumption: Specific conditions would require specific treatments by certain specialists with an associated locations e.g. different operating theatre.

I would not be inclined to change the other tables (Patients, Ward, Bed)

Compare the results of the above and modify the set of relations if necessary. Explain any changes that have been made.

The Principle Reasons for changing the Employee Table and the Treatment Table, are to reduce the complexity of each table, remove unused field to make better use of storage space and make the database and associated software more flexible

Task E

Use Access to create the final set of tables. You should ensure that the D.D.L. statements used enforce all primary key, foreign key and domain constraints. A full listing of the scripts used to create and populate your sample tables are required

Task F

f) The following SQL DML queries are required:

i) For a specific patient the full details should be listed. The patient's NHS Number should be prompted for at run time.
ii) For each doctor all qualifications should be listed, sorted by doctor's surname and first name.
iii) A count of beds not allocated to a patient by ward.
iv) A report consisting of the ward details, the nurse in charge and the nurses working in that ward.
v) A report for a week detailing summary information regarding the number of patients seen by the doctor per day to ensure that the 20 patient rule is not exceeded.

I'm not sure about what I have done is correct and complet.
I'm not so confident how to do the normalisation of attributs with no data in in order to know where the repeating groups are starting.

thanks

This is exactly what i am donig i take it you go to newman and are in the joint honours group....

Well how are you distingusting between doctors and nurses only via Employeers what about using a union there and making it an EER diagram?

other then that how have you got on with doing the database in access?

bfn

Here is my version of an EER diagram for this given casestudy.

How does a union work in Access?

Bfn

could some check this for me please as i am sure that some thing is wrong as the fields that are the same in another table as the one i am inputting data in does not update as i think it should do.

You know, I got my degree in computer science without posting my homework to the Internet and asking everybody else to do it for me...

Sundial, for once I agree with you.

Once.

This person appears to be on a management track.

blindman

i hate to tell you you are wrong about me i am not sure about the other person this is task which has stumped the whole group we are only looking for suggestions as to how to go about doing it thats all