CODEWITHSUNDEEP
sqlentity-relationship
ViktorG
ViktorG

Reputation: 515

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!

Upvotes: 2

Views: 13476

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Well, I don't know about "converting" an ER-diagram into SQL, but the current relationship can be modelled by simply having two tables:

create table profs ( pid int primary key, pname nvarchar(128), facid int );

create table facs ( fid int primary key, fname nvarchar(128), fpid int );

Apart from pname you can of course add all the attributes that describe a professor and the same applies for the faculty attributes in table facs. But the two columns facid in profs and fpid in facs describe adequately the relationships you laid out in your question.

As long, as one prof can belong to only one faculty at a time and the faculty can only have one head, you don't need another link-table connecting the two.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

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.

Upvotes: 1

Related Questions