Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Help with ER Diagram

    Hi, i need to create a ER diagram for a clinic. Below is my case study:


    Travel Jabs Clinic Database

    A company called Travel Jabs provides private travel immunisation throughout the UK. This service is provided through various clinics located in the main cities of the UK. The Director of Travel Jabs is concerned that there is a lack of communication within the company and particularly in the sharing of information and resources across the various clinics. To resolve this problem the Director has requested the creation of a centralised database system to assist in the more effective and efficient running of the practice. The Director has provided the following description of the current system.

    System Overview:

    Travel Clinics
    Travel Jabs has many travel clinics located in the main cities of the UK. The details of each clinic include the clinic number, clinic address (consisting of the street, city, county, and postcode), and the telephone and fax numbers. Each clinic has a Manager and a number of staff (for example, nurses, secretaries, cleaners).

    Staff
    The details stored on each member of staff include the staff number, name (first and last), address (street, city, county, and postcode), home telephone number, date of birth, gender, job description, national insurance number and current annual salary.

    Patients
    When a patient first contacts a clinic of Travel Jabs, the details of the patient are recorded, which include a patient number, (first name and last name), address (street, city, county, and postcode), date of birth, gender and home telephone number.

    Vaccines
    Travel Jabs provides various vaccines at a standard rate across all clinics. The details of each vaccine include a vaccine number, a full description of the treatment and the cost.

    Invoices
    Each patient is invoiced for the vaccine(s) and the details recorded on the invoice include the invoice number, invoice date, patient number, patient name and full address. The invoice provides the cost for each vaccine and the total cost. Additional data is also recorded on the payment of the invoice, including the date the invoice was paid and the method of payment (for example, cheque, cash, credit card). Invoices are issued on the same day as the vaccines are administered.
    The invoice does not state the member of staff who administers the vaccine(s) but this information should be stored in the database.

    Appointments
    Every patient must make an appointment. The details of an appointment are recorded and include an appointment number, patient number, patient name (first name and last name), home telephone number and the appointment date and time.


    I have attached the ER diagram i have created. Can someone please let me know if I have done it correct or if i need to change anything.
    Attached Thumbnails Attached Thumbnails erdiagram.jpg  

  2. #2
    Join Date
    Feb 2012
    Posts
    63
    It looks pretty decent.

    You excluded most of the foreign key attributes even though you showed the relationships. You have patientNo in Invoice but don't show the relationship. You have some denormalized info like patientName and patientAddress in Invoice, but this is fine to record historical information. However, I see no reason to duplicate the patient's info in Appointment. Your many-to-many relationship will end up being converted into a juntion table between a pair of one-to-many relationships, but I'm fine with many-to-many in an ERD.

    Based on your case study, there's more to be done. You haven't implemented the clinic manager relationship. You should probably record the cost of each vaccine on the invoice for the same reason you record the patient's name and address on it. Also, when these assignments list types (like job descriptions or payment methods), they usually want you to normalize that and make separate tables defining those types.

  3. #3
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by reaanb View Post
    It looks pretty decent.

    You excluded most of the foreign key attributes even though you showed the relationships. You have patientNo in Invoice but don't show the relationship. You have some denormalized info like patientName and patientAddress in Invoice, but this is fine to record historical information. However, I see no reason to duplicate the patient's info in Appointment. Your many-to-many relationship will end up being converted into a juntion table between a pair of one-to-many relationships, but I'm fine with many-to-many in an ERD.

    Based on your case study, there's more to be done. You haven't implemented the clinic manager relationship. You should probably record the cost of each vaccine on the invoice for the same reason you record the patient's name and address on it. Also, when these assignments list types (like job descriptions or payment methods), they usually want you to normalize that and make separate tables defining those types.
    do i have to state what attributes are foreign keys when using UML notation

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    793
    Quote Originally Posted by NFS View Post
    do i have to state what attributes are foreign keys when using UML notation
    I believe what reaanb was pointing out is that you need an fk from parent to child table which creates the relationship.

    For example there is no fk in vaccine which indicates the prescription. Can there be more than one vaccine per prescription? Or none?

    Travel Clinics
    Travel Jabs has many travel clinics located in the main cities of the UK. The details of each clinic include the clinic number, clinic address (consisting of the street, city, county, and postcode), and the telephone and fax numbers. Each clinic has a Manager and a number of staff (for example, nurses, secretaries, cleaners).

    Staff
    The details stored on each member of staff include the staff number, name (first and last), address (street, city, county, and postcode), home telephone number, date of birth, gender, job description, national insurance number and current annual salary.
    Staff are not (physically) associated with clinic so an fk on clinic table is needed if staff can be associated with one-many clinics. How would you join these two tables without an fk? If you go by post code that may not be a good candidate because post code for staff may be entered incorrectly. Second, the staff table is personal information. Once you enforce the fk then staff associated with a specific clinic is easy enough to identify.

    Another thing to consider is a patient can possible go to more than one clinic. How do you associate this relationship?

    How are staff and appointment related? How would you retrieve this information?

    Good luck.
    Last edited by corncrowe; 03-31-12 at 10:16.

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    793
    I should point out that you can add a relationship either by physical (constraint) or logical. Consider whether or not a business rule is required and enforced. For example: every appointment much be assigned to a staff. If true then the fk constraint would enforce this business rule. If false then adding the staffno to appointment table without the constraint means that is not enforced and an appointment may not have any staff associated.

    -- just some thoughts

    Here is a good link for database design linky

  6. #6
    Join Date
    Mar 2012
    Posts
    5
    I have created a new ER diagram...hopefully this ones is more accurate than the previous one. Any changes i should make or anything i should add??
    Attached Thumbnails Attached Thumbnails erdiagram.jpg  

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    793
    Quote Originally Posted by NFS View Post
    I have created a new ER diagram...hopefully this ones is more accurate than the previous one. Any changes i should make or anything i should add??
    I think you are much closer. What should this flow look like:

    Is it clinic has --> patients --> who make appointments --> and are seen by staff --> who prescribe vaccine --> which is billed to patient

    I don't believe staff make appointments. And another thing is how do you relate which date a patient visited? I see an appointment table and patient, but how can you tie back the patient to a particular visit?

    Looks much better.
    Last edited by corncrowe; 03-31-12 at 12:48.

  8. #8
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by corncrowe View Post
    I think you are much closer. What should this flow look like:

    Is it clinic has --> patients --> who make appointments --> and are seen by staff --> who prescribe vaccine --> which is billed to patient

    I don't believe staff make appointments. And another thing is how do you relate which date a patient visited? I see an appointment table and patient, but how can you tie back the patient to a particular visit?

    Looks much better.
    I believe the flow is:

    clinic has --> staff & manager --> who store details of patient and make appointment when patients contacts clinic --> patient is then prescribed vaccine ---> invoiced to patient.

    Can you tell me what relationships are missing and what foreign keys need to be added to what table.

    Regarding which date a patient visited, the appointment table has the patientNo as a foreign key so you know what appointment corresponds to which patient. Right?

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    793
    Quote Originally Posted by NFS View Post
    I believe the flow is:

    clinic has --> staff & manager --> who store details of patient and make appointment when patients contacts clinic --> patient is then prescribed vaccine ---> invoiced to patient.

    I tend to agree. I believe the clinic-->staff-->appointment-->patient relationship are much better.


    Can you tell me what relationships are missing and what foreign keys need to be added to what table.

    patient-->prescription looks ok because you should be able to retrieve when a prescription was given by date and go up the relationships to arrive at appointment date <-- staff <-- clinic

    Good job!


    Regarding which date a patient visited, the appointment table has the patientNo as a foreign key so you know what appointment corresponds to which patient. Right?

    That is fine since you can retrieve a patient history <-- appointment by date <-- staff who administered vaccine at which clinic <-- clinic
    I would look at the invoices again and ask yourself how to provide the details and totals? Do you need details and a summary? Do you require more normalized tables? How would you resolve the cost and totals in the same table?

    I would also look at how scalable is this model? Would it break if a staff member moved to a new clinic?
    Last edited by corncrowe; 03-31-12 at 16:07.

  10. #10
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    793
    Assumptions 2: A patient can go to any clinic and be seen by any doctor. The doctor can likewise can be associated with any clinic and see patients from any clinic. The patient and doctor may have previously seen each other at any of the clinics. This is the patient entity. The prescription and invoice entities should reflect this relationship.

  11. #11
    Join Date
    Feb 2012
    Posts
    63
    It looks better with the foreign keys displayed. However, the rest of my comments still stand. Your added assumptions box don't add to the diagram - though you claim that many-to-many will be broken up, you misrepresent your many-to-many relationship. Prescription is not the junction table between vaccines and doctors (prescription doesn't reference a doctor). The many-to-many relationship is still between vaccines and prescriptions. I don't think it's a problem having a many-to-many, but some educators will require you to show it as it will be implemented.

    If I seem critical, I apologize. Really, this is a good attempt and you've done much right; it's only because the diagram is close to a perfect answer that I'm able to easily pick out the individual inconsistencies.

  12. #12
    Join Date
    Mar 2012
    Posts
    5
    I have edited the ER diagram again so hopefully it "satisfies the above case study" better.

    I have tried to normalise the diagram so I have removed the patients name and address attributes in the invoice table. I have also removed the attributes patients name, patient number, and patient telephone number from the appointment table. Hopefully the ER diagram is now normalized, so can someone please check this for me.

    I have also created a seperate table called Payment for the attributes that regard to the invoice being paid. However I do not know exactly how to establish the relationship between Payment and Invoice table???

    Also is the Patient and Invoice table supposed to be linked?? Is it correct that I have removed the attributes from invoice and appointment to normalize the diagram?? Are my relationships between tables correct?? Is this ER diagram able to "satisfy the case study"??

    Any help to correct any mistakes in the diagram would be greatly appreciated.
    Attached Thumbnails Attached Thumbnails erdiagram.jpg  
    Last edited by NFS; 04-03-12 at 22:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •