Enquire Access table design and define relationship.

[Paullhk]

Hi Experts,

I am doing my assignment for my database implementation subject. I am now encountered the relationships between the table Employee and the table Department.

Using SQL to create required tables, fields and relationships as belows:-

Table 1: EMPLOYEE (EmpID_PK, EmpName, DoB, Address, Gender, Salary, SupervisorID, DeptNo_FK)
Table 2: DEPARTMENT (DeptNo_PK, DeptName, ManagerID_FK, ManagerStartDate)
Table 3: DEPT_LOCATION (DeptNo_FK, DeptLocation)
Table 4: PROJECT (ProjNo_PK, ProjName, ProjLocation, DeptNo_FK)
Table 5: WORKS_ON (EmpID_FK, ProjNo_FK, Hours)

[Note: Primary key is _PK, Foreign key is _FK]

After designed.....I noticed that DeptNo_PK in table department is not linked to DeptNo_FK in table employee. (already defined the relationship by using SQL) So, I've no clue what's wrong with it. I am looking forward someone to assist me in this problem. Thank you.




Regards
Paul

[June7]

Why use SQL to create tables?

Delete the link and create correct one.

Beware circular relationships. Review http://www.codeproject.com/Articles/...atabase-Design

[Paullhk]

Lecturer wants to test our SQL skill, so i have to use SQL to create it.

[June7]

Sorry, don't know why Access did not create the link except maybe because there was already a link from the DeptNo PK. How does creating table with SQL control the PK/FK linking? Never had to do this.

[recyan]

@Paullhk,
Perhaps, if you spell out the role & relationship of each entity involved with the other entities, someone should be able to help out.
For eg.
1) How is dept related to deptlocation ?
2) What is the relation between an employee, dept, deptlocation, project,...?
.......

Thanks