Assignment 5: E-R Diagrams, Weak Entity Sets, and SQL Exercises

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?

a) The graph is disconnected.

• This indicates that there are separate parts of the diagram that don’t have a direct relationship between the tables, which could also mean that some entities don’t have connected relationships to others. Overall, this could lead parts of the schema be isolated from the rest of the other relationships.

b) The graph has a cycle.

• A cycle in the graph happens when an entity is indirectly related to types of relationships, which in the case of an E-R diagram could result in redundancy and logical issues in the schema.

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?

• Weak entity sets are meant to show accurate dependency relationships. Although it can be converted to a strong entity set by adding appropriate attributes, a weak entity is not able to exist independently with another entity.

4. SQL exercise:

a) Consider the following database:

i. 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 employee. ID, employee person_name 
from employee e 
join works w on e.ID-w.ID 
join company c on w.company_name - c.company_name 
where c.city = ecity

ii. 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 employee.ID, employee.person_name 
from employee e 
join manages m on e. ID - m.ID 
where e.city = m.city and  e.street - m.street

iii. Find ID and name of each employee who earns more than the average salary of all

employees of her or his company.

select employee.ID, employee.person_name 
from employee e 
join manages m on e. ID - m.ID 
where e.city = m.city and e.street - m.street

b) 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?

• The problem in this query is how the natural join matches columns automatically with the same name between the tables.

• Additionally, if those rows don’t exactly match, they will disappear.

• Since the where semester = ‘Spring’ and year = 2017 were applied after the natural joins, they have removed the teaches, section, and course rows.