Problem integrity relationship Ms Access 2007 using sql query

[man6663]

Problem integrity relationship when create relationship foreign key between table Employee.EmpID to table Work_On and table Department.ManagerID to Employee.EmpID

the question below

EMPLOYEE (EmpID, EmpName, DoB, Address, Gender, Salary, SupervisorID, DeptNo)
DEPARTMENT (DeptNo, DeptName, ManagerID, ManagerStartDate)
DEPT_LOCATION (DeptNo, DeptLocation)
PROJECT (ProjNo, ProjName, ProjLocation, DeptNo)
WORKS_ON (EmpID, ProjNo, Hours)




[Note: Primary key is bold, Foreign key is italic and ManagerID is EmpID foreign key]

Real.zip

Please help me...2 days working to solve this issue

[June7]

If you set link between Employees and Work, there will be a circular relationship which can cause issues. http://www.codeproject.com/Articles/...atabase-Design

Why have DeptID in Project? The department info can be retrieved through link with employee in Work.

Why is DeptID foreign key in Dept_Location? Can each department have more than one location?

[man6663]

i cant change the field id...its my college assignment...so your mean that relationship cant be done...

[June7]

I guess do whatever your instructor expects, even if it results in circular reference. But you should be aware of issue in case you ever build db in real world.

What about my last question?

[orange]

Can you tell us the specifics of your assignment as per the Instructor?
You are showing us what you have done--which may or may not be aligned with the instructions.
If you have trouble/difficulty understanding the assignment, can you not clarify with your Instructor?

Why is there no Primary Key on Dept_Location Table and on Work_On Table?
I don't think your relationships are correct, but we don't know what the assignment as given by the Instructor is/was.
How did you get all those queries(append) without checking your tables and relationships?
Where and HOW is ManagerId assigned? Is a Manager also an Employee?

[man6663]

The question is like below


ASSIGNMENT QUESTION / SOALAN TUGASAN


PURPOSE
The purpose of this assignment is to test your skills in developing a simple database using Database Management System (DBMS) and to apply the knowledge that you have learnt on manipulating queries.

REQUIREMENT / ASSIGNMENT QUESTION
A relational schema of a database is provided below, which is about Employees, the departments they work in, and the Projects they are working on.

EMPLOYEE (EmpID, EmpName, DoB, Address, Gender, Salary, SupervisorID, DeptNo)
DEPARTMENT (DeptNo, DeptName, ManagerID, ManagerStartDate)
DEPT_LOCATION (DeptNo, DeptLocation)
PROJECT (ProjNo, ProjName, ProjLocation, DeptNo)
WORKS_ON (EmpID, ProjNo, Hours)

[Note: Primary key is bold, Foreign key is Italic . ManagerID is EmpID foreign key]

Use Microsoft Access to create the tables (using SQL) and enter sample data (using SQL or GUI with 5 records per table). Use suitable data type for each field and create the relationships between the tables. Then, write SQL queries that answer the questions below (one query per question) and run them:

1. Retrieve the names of employees in Finance Department who work more than 10 hours per week on the 'WangsaMaju2013' project.
2. For each project, list the project name and the total hours per week (by all employees) spent on that project.
3. Retrieve the names of employees who are not working on any project.
4. Find the names and addresses of employees who work on at least one project located in Wangsa Maju, whose department isn’t located in Wangsa Maju.
5. Find details of those employees whose salary is greater than the average salary for all employees in his/her department in ascending order of salary.

You should hand in a printout of:
1. Each query used to create the tables.
2. Each table which shows table name, fields and sample records.
3. The relationships between tables.
4. Each query in “SQL View”, so that the grader can see your SQL code.
5. The result of each query.

[orange]

Ok.
What is the significance with the underlined words?
In many places the PK is underlined, but not always.

When does this assignment have to be completed?
Here are some free videos you should watch/repeat watching as necessary.
There is also a link to Database Design Principles on that page. It is worth the time to read.
In addition there is a link to EntityRelationshipDiagramming on that page.

We are not here to do assignments for you, but members will assist with concepts and issues - provided you do your share of the work.
Good luck.

[June7]

Department and Dept_Location relationship still doesn't make sense to me. It is set to allow multiple locations for each department. How is location even relevant?

If accept that an employee never works for more than one department, don't see need for DeptID in Project table.

Unless this is intended to show that one department has responsibility for the project while allowing employees from various departments to contribute work effort. Another assumption?

[orange]

JUne7,

I agree with your thinking, but I also think the OP has to work through/ask about some of these.
Where are they getting the sample data from?
I'm thinking they have been studying or at least discussing the 'WangsaMaju2013' project - who work on at least one project located in Wangsa Maju, whose department isn’t located in Wangsa Maju.

Via google maps
Wangsa Maju Kuala Lumpur Federal Territory of Kuala Lumpur Malaysia