Personal GitHub

1 Explain the difference between a weak and a strong entity set. Use an example other than the one in Chapter 6 to illustrate. (Consult Ch. 6, 6.5.3)

Weak entity set is existence dependent since it could not identify its existence without an identifying entity set. A weak entity set does not have primary key. Instead, it has a partial discriminator key.

Strong entity is not dependent on any other entity in the schema. As strong entity will always have a primary key.

For instance, at UTD, we have a course table/relation which is strong entity. We also have a course_offering including the semester, year and section_number, which is a weak entity and we need to include course_number attribute as a foreign key referring course table to help course_offering to identify its existence.

2 Design an E-R diagram for keeping track of the scoring statistics of your favorite sports team.

You should store the matches played, the scores in each match, the players in each match, and individual player scoring statistics for each match. Summary statistics should be modeled as derived attributes with an explanation as to how they are computed. (Consult: https://www.dbbook.com/db7/practice-exer-dir/PDF-dir/6s.pdf)

3 SQL exercise

3.1 Consider the query

select course_id, semester, year, sec_id, avg (tot_cred) 
from takes 
natural join student 
where year = 2017 
group by course_id, semester, year, sec_id 
having count (ID) >= 2;

3.1.1 Explain why appending natural join section in the from clause would not change the result. (Consult Ch. 4, 4.1.1)

The natural join of takes and student is calculated first, and the new relation contains the attributes (ID, name, dept_name, tot_cred, course_id, sec_id, semester, year, grade). Since course_id, sec_id, semester, year are foreign keys of the new relation referencing section relation with a many to one relationship, the second natural join will not affect the result of the first natural join result. Thus, appending natural join section in the from clause would not change the result.

3.1.2 Test the results:

3.2 Write an SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no subqueries and no set operations (use an outer join). (Consult Ch. 4, 4.1.3)

select S.ID as student_ID
from student S
left outer join takes T
on S.ID = T.ID
where T.course_id is null

3.3 Consider the following database, write a query to find the ID of each employee with no manager. Note that an employee may simply have no manager listed or may have a null manager(use natural left outer join). (Consult Ch. 4, 4.1.3)

select E.ID as employee_ID
from employee E
natural left outer join manges M
on E.ID = ID 
where E.manager_id is null or count(E.manager_id) = 0