CONVERTING ER DIAGRAMS TO RELATIONAL DATABASE

An example of converting ER diagram to Tables

Here we've an image showing an ER-diagram :

ER-diagram

Our target is to implement this ER-diagram into SQL code, make tables and establish relationships between tables, satisfying the ER-diagram.

Below given are the CREATE TABLE statements, which are used to implement the above given ER-diagram.

1) Department table :

create table department

 (dept_id number not null primary key, 

  dept_name varchar2(15) not null

 );

2) Branch table

create table branch

 (branch_id varchar2(5) not null primary key, 

  electives varchar2(10),

  dept_id number not null,

  constraint Department_Has_Branches

    foreign key (dept_id)

    references department(dept_id)

 );

3) Course table:

create table course

 (course_id number not null primary key,

  course_name varchar2(10) not null,

  branch_id varchar2(5) not null,

  constraint Branch_Offers_Courses

    foreign key (branch_id)

    references branch(branch_id)

 );

4) Student table:

create table student

 (stud_id number not null primary key, 

  stud_name varchar2(30) not null,

  branch_id varchar2(5) not null,

  constraint Student_BelongsTo_Branch

    foreign key (branch_id)

    references branch(branch_id)

 );

5) Applicant table:

create table applicant

 (app_id number not null primary key,constraint Student_SelectedAs_Applicant foreign key (app_id) to Student table

references student(stud_id)

);

6) applicant_AppliesFor_branch Table :

create table applicant_AppliesFor_branch

 (app_id number not null, 

  branch_id varchar2(5) not null,

  primary key (app_id, branch_id),

  constraint Student_AppliesFor_Branch

    foreign key (app_id)

    references applicant(app_id),

  constraint Branch_AppliedBy_Student

    foreign key (branch_id)

    references branch(branch_id)

);