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.
Develop a top-down design for the above database system in the form of an entity-relationship diagram. State any assumptions made.
Produce relations from the E-R model
Tables: Employees, Patients, Wards, Beds, Treatments
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
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
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.
Originally posted by blindman
Sundial, for once I agree with you.
This person appears to be on a management track.
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