0

enter image description here

Thats the ERD.

I'm studying for an upcoming test and having trouble getting my head around converting this ERD into dependency diagrams. This is from a previous exam that the Lecturer told us to study just btw so he said it will be pretty similar to this.

We have to -

  • Convert the ERD into a dependency diagram.
  • And then, convert that diagram into a 3NF model, with no transitive or partial dependencies.

Any help would be much appreciated as I'm struggling to get my head around it and need to pass this test :/

Thanks in advance!

Improve this question
3
  • I'm not going to give you the answer, but rather point you in the right direction. Your task here is to identify the different entities in this diagram and then figure out which fields belong to which entity (table). The aim is to make sure that no properties appear in duplicate. For example, in the current setup, the patient have have different phone numbers for different appointments. Hope that helps you.
    – Daniel Hutmacher
    Commented Aug 19, 2014 at 9:01
  • When I learned the ER model, it did NOT include FKs. What your professor is teaching you is to use ERDs as a depiction of a relational design. That is, in fact, the mainstream today, but it is not the classical way.
    – Walter Mitty
    Commented Aug 19, 2014 at 11:06
  • Why are images of text, code and mathematical expressions discouraged?](meta.stackexchange.com/q/320052/266284)
    – philipxy
    Commented Jan 11, 2023 at 10:02

1 Answer 1

Reset to default
2

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do:

1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited and can be defined. 2- Identify natural keys of objects and their properties,... 3- Identify relationships of objects. The step help you answer some questions:

  • What actions?
  • Who took place actions?
  • Where actions are happened?
  • When actions happened

4-If we have many-many relationship, we separate them into an individual table. Otherwise, we use foreign keys or not.

5- Verify our diagram matching which NF.

Now, we come back your ERD and we will convert it to dependency diagram.

Step 1- identify objects: Here, we can understand that we want to manage list of appointments of doctors and patients. So, we have 2 objects Doctor and Patient

Step 2- identify natural keys/properties of objects: you listed out above.

Step 3- identify relationships of objects: As you know, a patient can have zero or many appointments with doctors and a doctor can have zero or many appointments with patients. So, we have a many-many relationship.

Step 4- Because we have a many-many relationship, we create a table here.

Finally, we have a diagram

Doctor(Doc_FEmpID,Doc_Fname,Doc_FCellPhone) Patient(Pat_PatientID,Pat_PatientName,Pat_Phone,Pat_Address,Pat_City) Appointment(App_Num,Doc_FEmpID,Pat_PatientID,App_Date,App_Time)

Step 5- verify the diagram again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.