1

I have some confusion / difficulties trying to draw and understand an ERD diagram, below are the question:

“Students enrol at colleges which offer courses taught by teachers. When the students enrol they must register on the courses they wish to study. The college will then assign a tutor, who is a teacher, to the student.”

Would this be the right diagram?

http://imgur.com/a/Z8Tkl

Should college be an entity or attribute of the student entity?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
postsrc
  • 732
  • 3
  • 9
  • 26

2 Answers2

1

Should college be an entity or attribute of the student entity?

The question is: "Can a college exists without a first student attending it?".

Yes, in order for a student to attend a college that college must exists; therefore college is an independent entity.

The next the question is: should a college be an attribute in the student entity, or do you need a separate relation (table, entity) {STUDENT_ID, COLLEGE_ID} ?

That depends on your "business rules" (constraints).

[1] If the rule (constraint) is:

[c1] Each student can attend only one college; for each college it is possible that more than one student attends that college.

Then add a COLLEGE_ID to the student entity.

[2] If the rule (constraint) is:

[c2] Each student can attend more than one college; for each college it is possible that more than one student attends that college.

Than you need to add a dependent entity {STUDENT_ID, COLLEGE_ID}

To sort-out teacher, course, student details, see this SO example.

Your original model has few extra weaknesses like:

  1. Can a college offer a course without a student signing up for the course first?

  2. Can a teacher teach at more than one college?

  3. Can a teacher teach more than one course?

  4. Can more than one teacher teach a course?

  5. How to make sure that if a student takes a course and a teacher tutors that student; than that teacher also teaches that course?

As you work through this you will add more entities.

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • In ER, attributes aren't just columns but mappings from entity or relationship sets to value sets. If `College` is an independent entity set, a `COLLEGE_ID` column in the `Student` table wouldn't be seen as an attribute but part of the relationship represented by `STUDENT_ID, COLLEGE_ID`. Also, _dependent entity_ isn't a term in the ER model - we would call it a relationship. The idea that tables represent entities and FK constraints represent relationships comes from the network data model, not the ER model. – reaanb Mar 23 '17 at 16:19
  • [The Entity-Relationship Model: Toward a Unified View of Data](http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.123.1085) – reaanb Mar 23 '17 at 19:26
1

Your diagram is not a proper ER diagram. While it can be useful to leave out attributes to focus on entities and relationships, you should at least use a shape to represent relationship sets so that ternary and higher relationships aren't excluded by the notation. In Chen's notation, relationships are indicated by diamond shapes.

College is best handled as an independent entity set, since it's associated with multiple other entity sets (Student, Course, possibly Teacher as well). When something is the subject of relationships and attributes, it should be an entity set. In ER, attributes are mappings from entity sets (or relationship sets) to value sets, i.e. they should be used for labels and measurements.

Your choice of relationships look reasonable, though I suggest you consider associating Teacher with College directly. Depending on how you handle Course (can colleges can offer the same course or do each offer its own instance?) and Student (can students enroll at more than one college, can courses exist without any students enrolled?), it may not be possible to derive a relationship between Teacher and College.

reaanb
  • 9,806
  • 2
  • 23
  • 37