foreign keys - Converting an ER diagram to SQL code -

this er diagram, tables have made in sql code implementing constraints. made tables , tried implementing relationship via foreign keys, jus wanted confirm, whether these tables 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 number, 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 er diagram ?

your er diagram depicts conceptual model of subject matter. thing.

for future reference, there 2 intermediate steps between conceptual model , sql create script. logical design , physical design.

logical design changes conceptual model logical model, , adds features. logical model relational (in cases). 1 added feature foreign keys. normalize, if choose normalize.

physical design changes logical model physical model, , adds features. physical model expressed in sql terms; tables, rows, , columns. dbms specific. adds features indexes , many dbms specific features such tablespace mapping , others. @ stage consider volume of data, anticipated traffic, , throughput consideraions.

finally, convert physical model create script.

for small problems, these stages can collapsed 1 stage, appear have done. big projects, it's better keep them separate.