Wednesday, August 7, 2013


An example of converting ER diagram to Tables

Here we've an image showing an 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)

No comments:

Post a Comment