[phpXperts] Problem about database design

Hi All,

I am facing a problem about database design.

 

I have three tables named:

1. branch

2. dept

3. employee

 

At first I have to to create the branch table where branch_id is pk

 

Then I have to create the dept table in where branch_id will be fk and branch_id & dept_id will be composit pk.

 

Finally I have to create the employee table where branch_id & dept_id will be fk and branch_id, dept_id & employee_id will be composit pk.

 

Now I can successfully created the branch & dept table but not the employee table. Please anybody help me to construct the employee table? I've given the table structure and queries bellow:

 

Table Structures:

 

branch

branch_id

pk(branch_id)

 

dept

branch_id

dept_id

 

pk(branch_id, dept_id)

fk(branch_id)

 

employee

branch_id

dept_id

employee_id

 

pk(branch_id, dept_id, employee_id)

fk(branch_id, dept_id)

 

 

Table Creation Queries:

 

branch

CREATE TABLE branch(

branch_id      VARCHAR(10)           NOT NULL,

 

PRIMARY KEY( branch_id ) )

 

dept

CREATE TABLE dept(

branch_id      VARCHAR(10)           NOT NULL,

dept_id         VARCHAR(10)           NOT NULL,

 

PRIMARY KEY( branch_id, dept_id ),

 

FOREIGN KEY( branch_id )

REFERENCES( branch )

 

employee

CREATE TABLE EMPLOYEE(

branch_id      VARCHAR(10)           NOT NULL,

dept_id         VARCHAR(10)           NOT NULL,

employee_id   VARCHAR(10)           NOT NULL,

 

PRIMARY KEY( branch_id, dept_id, employee_id ),

 

FOREIGN KEY(---???---)

REFERENCES(---???---)

---

Unlimited freedom, unlimited storage. Get it now __._,_.___

Visit phpXperts website at www.phpxperts.net

Your email settings: Individual Email|Traditional
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___