Translating relationship attributes from ER diagram into SQL

Question

Currently trying to get to grips with SQL for the first time, so I am working through a few problems. Here is a sample database spec:

Students (name, gender, course) do projects(title). Each project has two supervisors (name, gender, department). All students do a project but not all projects get taken. More than one student can do the same project. Students meet one of their supervisors regular and these meetings are recorded (date, time, student, supervisor, notes).

So far I've got an ER diagram drawn up which I think is correct:

I can get the basics (e.g. creating a Student table etc) but I'm having trouble getting my head around how to represent the relationships, specifically the meetings relationship, and how to represent it and its attributes in SQL. Should I instead create a 'meetings' entity?

Solution

Yes, you should create a Meeting entity to represent the many to many relationship between Student and Supervisor. In it you can relate to those tables using foreign keys that correspond to the those respective tables. In SQL it may look something like this:

Create table Meeting {
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
supervisor_id INT NOT NULL,
//rest of the fields...   
FOREIGN KEY (student_id) REFERENCES Student(id)
FOREIGN KEY (supervisor_id) REFERENCES Supervisor(id)
}

You would also do the same thing for the Supervise between Project and Supervisor. Also you could use something called a composite key on your Meeting table, I guess it comes down to personal preference, I usually do it this way when representing many to many relationships. I'm not saying this is the syntax you will use, that depends on your database, this was just an example to point you in the right direction. Hope it helps.

Also for your diagram (I'm just guessing this is for a class) you might want to look into software such as visio or visual paradigm to create your ER diagram. While most people will be able to understand your current diagram, that's not correct modeling.

For fun I made a diagram based on your tables:

You would want an entity between Supervisor and Project if they are a many to many relationship. This is called an associative entity. I labeled mine SupervisorProject just so they are a little more clear.

Edit Overlooked the fact that Student and project was a many to one, fixed that, sorry.

Comments

[Basil Bourque]

For general purpose drawing app for Mac or iOS that can handle ERDs, look at OmniGraffle.

[Cohagen]

Great - could you expand a little more on the merits of making Supervise an entity?