Consider the following database: Employee(emp-no, name, department, salary), Pro

Question

Consider the following database:

Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours)

a. Write one SELECT SQL query to list the numbers and names of all employees with a salary greater than 66 000 who are assigned to projects, the projects they are assigned to, and the corresponding hours worked. Your list should be sorted by employee name.

b. Define indexes on selected attributes to speed up your query, and justify your selections.

c. Write SQL queries to create the indexes you defined above.

Explanation / Answer

select employee.emp-no, employee.name, employee.salary, projAssigned.proj-no, projAssigned.worked-hours
from employee, projAssigned
where employee.emp-no = projAssigned.emp-no
and employee.salary > 66000
order by employee.name;

b)
To quickly search, we can define the index on Employee table on emp-no column, becuase that is unique. Similarly, In Projects table, there can be only one entry for one particular employee in a particular project. Hence we crete the index on two attributes, the combination of which is unique.

c)
CREATE UNIQUE INDEX employee_unique
ON employee(emp-no);
CREATE UNIQUE INDEX project_assigned_unique
ON ProjAssigned(emp-no, proj-no);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.