Assignment 4: Conceptual Database Modeling and SQL Query Behavior

Disclaimer: Parts of this assignment were created with the assistance of AI-based tools used for conceptual clarification and guidance. The analysis, responses, and final work represent my own reasoning and understanding.

  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)

    • According to the textbook, a weak entity set is one whose existence is dependent on another entity set, called its identifying entity set. A strong entity set is one that doesn’t rely on another entity set, specifically being independent.

    • One example of a weak entity set is from the geeksforgeeks website, where ‘Payment’ is the weak entity, making it dependent to other keys. The link to the website is pasted below the diagram.

      https://www.geeksforgeeks.org/dbms/weak-entity-set-in-er-diagrams/

  2. I designed an E-R diagram for keeping track of the scoring statistics of my favorite soccer sports team FC Bayern Munich. I store the matches played, the scores in each match, the players in each match, and individual player scoring statistics for each match.

  1. SQL exercise:

    a. 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;
    course_id semester year sec_id avg (tot_cred)
    CS-101 Fall 2017 1 65
    CS-190 Spring 2017 2 43
    CS-347 Fall 2017 1 67

    • This query uses the “natural join” construct to list of courses sections offered in 2017 where at least two students were enrolled with the average total credits of them.

    • Natural join matches tuples with the same values for all common attributes, and retains only one copy of each column.

    • Below, I’ve pasted the original code without the “natural join” query where it gives me the same result, where ‘takes natural join student’ is equal to ‘takes join student on takes.ID = student.ID’

    select t.course_id, t.semester, t.year, t.sec_id, avg (tot_cred)
from takes t
join student s on t.ID = s.ID
where t.year = 2017
group by t.course_id, t.semester, t.year, t.sec_id
having count(t.ID) >= 2;
    course_id semester year sec_id avg (tot_cred)
    CS-101 Fall 2017 1 65
    CS-190 Spring 2017 2 43
    CS-347 Fall 2017 1 67

    b. 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 ID
from student natural left outer join takes
where course_id is null;
    ID
    70557

Citation Reference:

GeeksforGeeks. n.d. Weak Entity Set in ER Diagrams. GeeksforGeeks. Accessed February 25, 2026. https://www.geeksforgeeks.org/dbms/weak-entity-set-in-er-diagrams/