Converting an ER diagram to SQL code

Question

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 jus 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 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 to the ER diagram ?

Answer 1

Your ER diagram depicts a conceptual model of the subject matter. This is a good thing.

For future reference, there are two intermediate steps between an conceptual model and an SQL create script. They are logical design and physical design.

Logical design changes the conceptual model into a logical model, and adds some features. The logical model is relational (in most cases). One added feature is foreign keys. This is where you normalize, if you choose to normalize.

Physical design changes the logical model into a physical model, and adds some features. The physical model is expressed in SQL terms; like tables, rows, and columns. It is DBMS specific. It adds features like indexes and many DBMS specific features such as tablespace mapping and others. At this stage you consider the volume of data, the anticipated traffic, and throughput consideraions.

Finally, you convert the physical model into a create script.

For very small problems, these stages can be collapsed into one stage, as you appear to have done. For very big projects, it's better to keep them separate.