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)
);
