Personal GitHub

1 An E-R diagram can be viewed as a graph. What do the following mean in terms of the structure of an enterprise schema?

1.1 The graph is disconnected.

A disconnected graph means that there are pairs of entity sets that are unrelated to each other. Thus, we we have disconnected graph, it might be better for us to design separate databases to make each as a connected graph.

1.2 The graph has a cycle.

A cyclic graph means that there are same two entities being connected by more than one path. In other words, not all of the paths between every pair of entity sets are unique.

2 Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted (Hint: use draw.io to draw the diagram with relationship sets).

3 We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets?

  • To avoid the inconsistencies caused by duplicating the key of the strong entity.

  • Weak entities reflect the logical structure of an entity being dependent on another entity.

  • Weak entities can be deleted automatically when their strong entity is deleted.

  • Weak entities can be stored physically with their strong entities.

4 SQL exercise:

4.1 Consider the employee database as follows, where the primary keys are underlined. Give an expression in SQL for each of the following queries. (Hint: use from employee as e, works as w, company as c, manages as m)

The ER diagram is as follows:

4.1.1 Find ID and name of each employee who lives in the same city as the location of the company for which the employee works.

select e.ID as employee_ID, 
        e.person_name as employee_name
        e.city,
        w.company_name
from employee e
inner join works w
on e.ID = w.ID
inner join company c
on w.company_name = c.company_name
where e.city = c.city

4.1.2 Find ID and name of each employee who lives in the same city and on the same street as does her or his manager.

select e.ID as employee_ID, 
        e.person_name as employee_name,
        e.street,
        e.city,
        m3.manager_id,
        m3.manager_name
from employee e2
inner join manages m2
on e2.ID = m2.ID
left join (select m1.manager_id,
                  e1.person_name as manager_name,
                  e1.street as manager_street,
                  e1.city as manager_city
            from employee e1
            inner join manages m1
            on e1.ID = m1.manager_id) as m3
on m2.manager_id = m3.manager_id
where e2.city = m3.manager_city and e2.street = m3.manager_street

4.1.3 Find ID and name of each employee who earns more than the average salary of all employees of her or his company.

select e2.ID as employee_ID, 
        e2.person_name as employee_name
        w2.salary
        w2.company_name
from employee e2
inner join works w2
on e2.ID = w2.ID
left join (select avg(w.salary) as avg_salary,
           w.company_name
           from works w
           inner join employee e
           on w.ID = e.ID
           groupby(company_name)) as comAvgSalary
on w2.company_name = comAvgSalary.company_name
where w2.salary > comAvgSalary.avg_salary

4.2 Consider the following SQL query that seeks to find a list of titles of all courses taught in Spring 2017 along with the name of the instructor.

select name, title 
from instructor 
natural join teaches 
natural join section 
natural join course 
where semester = 'Spring' and year = 2017

What is wrong with this query? (Hint: check book website)

The query doesn’t have the conditions for all natural joins. The right query is as follows:

select name, title 
from instructor i
natural join teaches t
on i.ID = t.ID
natural join section s
on t.course_id = s.course_id and t.sec_id = s.sec_id and t.semester = s.semester and t.year = s.year
natural join course c
on s.course_id = c.course_id
where s.semester = 'Spring' and s.year = 2017