Conversion of statements to E-R Diagram

database-design

EDIT: PLEASE DON'T CONSIDER ARROWS 🙂

statements are

"A teacher can teach many courses. A student
can enrol in many courses. A course may
be a part of one or many programmes. A
teacher can be mentor of many students,
however a student can have only one
mentor."

Having confusion is Mentor an entity or not

I have drawn two E-R diagrams with

E-R diagrams with Mentor entity

and without mentor

E-R diagrams without Mentor entity

this diagram can be accessed here

Best Answer

is Mentor of is a relationship between a teacher and a student. From your diagram I conclude that a course can be only be enroled by one student. I is not explicitely stated but I think that a course can be enroled by many students. Also I think that a course can be part of more then one program. Finally I get the following diagram:

enter image description here

The diagram is created with dia and the source can be found here.

In the diagram all relationships are defined mandatatory. So the the diagram should be refined in respect to this. For example it seems to be wrong that each teacher is mentor of at least one student.

Related Solutions

Database Design – Managing Many to Many Relationships

Generally a many-to-many relationship is done with a junction table, like so:

Student (id, name, date of birth, department) 

Course (id, name)

Student_Course (student_id, course_id)

You can either create an artificial key on the Student_Course table or create a primary key or unique index with both fields. student_id is a foreign key to student.id and course_id is a foreign key to course.id.

Note that the junction table need not be exactly two fields. You can include fields such as date_added or date_dropped so you could see when students have added and dropped courses.

Mysql – School database design

Homework questions like this will generally not elicit answers with code. Stack Exchange is not a place where you can come to get others to do your homework for you. However you have only asked for pointers of where to look, so to that end, I will inform you that what you are looking for is called a many-to-many relationship, and requires a third table that maps table 1 to table 2. Usually, this third table just has two columns, each containing the primary key of one of the main tables, and the primary key of this table covers both columns.

Read up:

Best Answer

Related Solutions

Database Design – Managing Many to Many Relationships

Mysql – School database design

Related Question