ER diagram

[khurram7x]

I tried hard to understand myself but I think I really need kindof help understanding the entity-relationship concept.

I've been advised in one of the post earlier that there should be only one path to each table in ER process flow diagram and if there are more than one paths then design is not correct, and now I understand that this is certainly correct but I continuously see cases in training books and video that show more than one path to a table.

I'm attaching one similar diagram of this case where 'User' and 'Course' are connected via study table, but it is also connected via 'Teaches' table and it seems valid as well because someone has to teach the course. And there are other paths via 'Lecture' and 'Watches' table as well which looks quite applicable.

I'll be thankful if someone could explain how to make it work inside the actual database when we're creating real ER diagrams please. I tried to do so once and I horribly messed it up.

 

[Ranman256]

Just to clarify, you have tUsers and tCourses, but you also need tUserCourses:
With the fields
[userID]
[coursID]

Now I only draw relationships to NEEDED tables. Parent-child.
tUsers-tUserCourses. Or tUsers-tUserPhones
(Not because the UserID is in the table)

Click the Relationship button, grab the tUsers.UserID and drag it ,and drop it on the tUserCourses.UserID. Double-click the line, and set ENFORCE integrity. Check cascade update, but DO NOT check cascade Delete. The delete can erase records.

 

[stopher]

I've been advised in one of the post earlier that there should be only one path to each table in ER process flow diagram and if there are more than one paths then design is not correct.

This is just not true. However, sometimes people do get carried away and put in more relationships than there should be. So when we see a loop in an ER diagram, we do have to take special care that it is valid.

I'm attaching one similar diagram of this case where 'User' and 'Course' are connected via study table, but it is also connected via 'Teaches' table and it seems valid as well because someone has to teach the course. And there are other paths via 'Lecture' and 'Watches' table as well which looks quite applicable.

Seems reasonable. But do note that the diamonds are "relationships" not tables. The squares are "Entities". ER diagrams are conceptual models of a real world scenario. They are not models of a subsequent database structure (which would include tables).

I'll be thankful if someone could explain how to make it work inside the actual database when we're creating real ER diagrams please. I tried to do so once and I horribly messed it up.

In terms of the loop you describe (studies and teaches), lets consider the "studies" relationship. This is a many to many relationship. This is typically resolved in a relational database using what is often called a junction table. This junction table would take it's key from both user and course. Google junction tables might to see how they are implemented.

Now consider the "teaches" relationship: This is one to many i.e. each course has a specific teacher. So you would typically handle this in a relational database by having the course table related directly to the user table via a foreign key (in the course table).

So you can see the actual structure of you relational database will look different to the ER diagram albeit perform exactly the function of the ER diagram.

hth

 

[khurram7x]

Seems reasonable. But do note that the diamonds are "relationships" not tables.

In terms of the loop you describe (studies and teaches), lets consider the "studies" relationship. This is a many to many relationship. This is typically resolved in a relational database using what is often called a junction table. This junction table would take it's key from both user and course. Google junction tables might to see how they are implemented.

Now consider the "teaches" relationship: This is one to many i.e. each course has a specific teacher. So you would typically handle this in a relational database by having the course table related directly to the user table via a foreign key (in the course table).

So you can see the actual structure of you relational database will look different to the ER diagram albeit perform exactly the function of the ER diagram.
hth

Thanks for clearing up things on relationship diagram.

Based on what is advised here, i've created a relationship diagram.

This is first time i'm using multiple paths, does it look eligible?? I mean it isn't is providing any redundant information, or any other reason for it not being acceptable?

Thank you.

 

[MarkK]

To me, there is a distinction to be drawn between some theoretical construct, and an actual system that you will use for a purpose. For instance, you posted a jpg with a diagram, and you seem to want to know if it is "eligible," but eligible for what?

When we make a diagram or write a system we are modelling a reality, and the test is whether our model has fidelity, or whether our model is accurate. Have we captured the essential structure of the thing we tried to model for the purpose that we intend? Maybe you take a photograph of your friend, and it is a well composed photo and correctly exposed, but it doesn't capture the essence of your friend's personality, so is it a good photo?

So, you can write a structure that abides by all the rules, but doesn't model the reality you want it to.

The jpg you posted solves no problem. There is no data in any of the tables, so there is no purpose. The relationships are possible, but not essential, so there is not enough information to conclude anything. What is your purpose? Your design can only be judged on whether is satisfies your purpose.

 

[khurram7x]

This is just not true. However, sometimes people do get carried away and put in more relationships than there should be. So when we see a loop in an ER diagram, we do have to take special care that it is valid.

...The relationships are possible, but not essential...

I was only looking for the answer where such relationships as in last attachment are possible in a database, WHEN THE SITUATION ARISES.
'Stopher' understood when I'm looking for and I found the answer in first half of his advice. You've also answered, so i've got my answer.

When we make a diagram or write a system we are modelling a reality, and the test is whether our model has fidelity, or whether our model is accurate.

I'm at the moment working on design thing and getting some help from a book too. I'm getting concepts clear and understand what you're saying. I'll keep you advise in real world scenarios.

Thanks