Converting ER diagram to tables and relationships in sql

foreign key

Department : Branch is 1 : M

Branch : Course is 1 : M

Branch : Student is 1 : M

Branch : Applicant is M : N

Applicant : Student is 1 : 1

These are the conditions prescribed in the ER diagram.

This is the ER diagram, for which tables have to be made in SQL code implementing all the constraints. I made tables and tried implementing all the relationship via foreign keys, i just wanted to confirm, whether these tables are correct or not.

1) Department table:

create table department(dpet_id number primary key, dept_name varchar2(15)
not null);

2) Branch table:

create table branch(branch_id varchar2(5) primary key, electives varchar2(10),
dept_id number references department(dept_id));

3) Course table:

create table course(course_id number primary key, course_name varchar2(10)
not null,branch_id varchar2(5) references branch(branch_id));

4) Student table:

create table student(stud_id number primary key, stud_name varchar2(30) not null,
branch_id varchar2(5) references branch(branch_id);

5) Applicant table:

create table applicant(app_id number primary key, stud_id number constraint fk
references student(stud_id) constraint stu_unq unique);

6) Applicant_branch table:

create table applicant_branch(app_id number references applicant(app_id),
branch_id varchar2(5) references branch(branch_id));

Do these tables conform to the ER diagram ?

I have tried to give a picture of ER diagram above giving cardinality conditions. Now please tell me if there is any mistake in my SQL code.

Best Answer

The Student - Applicant relationship is 1:1 and your implementation is correct. An alternative is to remove the app_id (or the stud_id) from the Applicant table and to use the (one and only) column both as a Primary Key and for the Foreign Key constraint (see the code below).

I assumed from your code that a Student can be selected as an Applicant. If it's the other way around (some of the Applicants are selected as Students), the foreign key constraint has to be reversed.

You also have several columns (that are used in the foreign key constraints) defined as NULL. Not sure how the diagram is to be interpreted. I guess that these should be NOT NULL. Otherwise, the course.branch_id for example being null means that you may have a course that is not related to any branch at all.

I would also name the (FK) constraints, and since this is homework, I'd name them according to the provided diagram.

It's also not bad to add the NOT NULL to primary key columns as well. It changes nothing in the created tables - until you decide to change the primary key and you forget to add the not null constraint:

create table department
 (dept_id number not null primary key, 
  dept_name varchar2(15) not null
 );

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

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

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

create table applicant
 (app_id number not null primary key,      -- stud_id removed           
  constraint Student_SelectedAs_Applicant
    foreign key (app_id)                   -- app_id used in the FK to Student table
    references student(stud_id)
);

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

For Oracle, see:

Display all names of constraints for a table in Oracle SQL

Related Question