Search code examples
sqlentity-relationship

Convert ER-Diagram to SQL

I have trouble with converting an ER-Diagram into valid SQL code.

Look at the following ER-Diagram

enter image description here

How can I translate this ER-Diagram into SQL code? I Have trouble specifying the relations. Would it make sense to create a third table just for the relations? 

CREATE TABLE Faculty (
    Fac.-Nr INTEGER NOT NULL,
    PRIMARY KEY (  Fac.-Nr)
);

CREATE TABLE Prof (
    Name VARCHAR(255) NOT NULL,
    PRIMARY KEY (Name)
);

I think that I have to work with constraints but I don't know how to properly convert the relations.

I want to be able to insert the following information:

|Name |belongs to | leads|

|Smith | Fac10 | -|

|Becker | Fac10 | Fac10|

|John | Fac10 | -|

I'm very new to SQL so please be patient with me :)

Help would be greatly appreciated!

Solution

  • Yes you should create a third table to hold the relation which will point to both table primary key as FOREIGN KEY constraint. Something like

    create table ProfFaculti (
name VARCHAR(255) NOT NULL,
Nr INTEGER NOT NULL,
primary key(name,Nr),
foreign key(name) references Prof(name), 
foreign key(Nr) references Faculti(Nr))

    You should actually have some ID column in both table which should be the primary key and should have FK on those columns.