Search code examples
database-designerd

How can I create an entity relationship model?

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?

Solution

  • 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.