Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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 ? I have tried to give a picture of ER diagram above giving cardinality conditions, Now pls tell me , if there is any mistake in my SQL code.

share|improve this question
    
I can't see any ER diagram. Perhaps you can post that too (or a link to it)? –  ypercube Aug 7 '13 at 12:31
    
Why are some columns defined as NULL (for example the course.branch_id)? Is that on purpose or a mistake? And does a student applies for branch? It would make more sense if the applications were for Course, not Branch. –  ypercube Aug 7 '13 at 12:33
    
@ypercube, Is there any alternative to show ER diagram ? –  Rubbal Bhusri Aug 7 '13 at 12:39
    
I flagged the duplicate question at SO, for migration or deletion. Please don't cross-post. –  ypercube Aug 7 '13 at 12:42
    
There is a typo in the SQL for the branch table. It should be primary key not primary –  a_horse_with_no_name Aug 7 '13 at 13:23

1 Answer 1

up vote 1 down vote accepted

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)
);
share|improve this answer
    
Doesn't this condition (constraint stu_unq unique) establishes the 1: 1 relationship in Applicant table ? If not, then What should be done to establish 1 : 1 relationship between Applicant and student ? –  Rubbal Bhusri Aug 7 '13 at 13:05
    
Oh yes, you are right, I missed that. Please note the edited first paragraph in the answer. –  ypercube Aug 7 '13 at 13:06
    
I agree with ypercube. The applicant table only needs a single column (although I would have called it stu_id not app_id to document that it actually contains a student id). –  a_horse_with_no_name Aug 7 '13 at 13:26
    
@ypercube, What difference does it make, when we write : dept_id number references department(dept_id) and when we write : constraint Department_Has_Branches foreign key (dept_id) references department(dept_id) ? –  Rubbal Bhusri Aug 7 '13 at 13:39
    
The constraint gets to be named (by us.) Otherwise most DBMS will name the constraint by an arbitrary meaningless name. Constraint names appears in error messages - when (the constraints) are violated, so it's useful to have a meaningful name. Try this fiddle –  ypercube Aug 7 '13 at 13:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.