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.