EPPS 6354 Assignment 5

Question 1:

If the graph is disconnected it means that there are entities that are completely unrelated to other entities by relationships. This can be solved by making sure that all foreign keys and relationships are identified.

If the graph is a circle, it means everything has at least two relationships, and that from one entity, you can find data in all other entities. This may suggest redundancy in database design.

---

Question 3:

Having weak entity sets can remove redundancy and simplify relationships. For example, it may be more simple to have an entity be a weak entity than add an attribute for the relationship connecting it.

---

Question 4:

4a)

-- for 4i
select e.ID, e.person_name
from employee as e
join works as w on e.ID = w.ID
join company as c on w.company_name = c.company_name
where e.city = c.city;
-- for 4ii
select e.ID, e.person_name
from employee as e
join manages as m on e.ID = m.ID
join employee as mgr on m.manager_id = mgr.ID
where e.street = mgr.street
and e.city = mgr.city;
-- for 4iii
-- for 4i
select e.ID, e.person_name
from employee as e
join works as w on e.ID = w.ID
join (
    select company_name, avg(salary) as avg_salary
    from works
    group by company_name
) as avg_company
on w.company_name = avg_company.company_name
where w.salary > avg_company.avg_salary;

One reason to avoid using multiple natural joins like in the example is that it will join on multiple columns. This can mean that information that is not meant to be joined on is being joined on.