• Q2. E/R Diagrams: Q2.1: YES; Q2.2: YES; Q2.3: NO
• Q3. Mapping of E/R Diagrams to SQL:
Problem 1:
Primary key of entity set Sponsors is lost
Problem 2:
Unlike in the E/R diagram, each Account can participate at most once in Owns
Problem 3:
Unlike in the E/R diagram, each Student can participate at most once in Owns
Problem 4:
The key participation of Students and Sponsors into Owns (denoted by the arrow into Accounts) is not modeled
Problem 5:
A spurious participation constraint of Students in Owns is introduced: now every Student must be associated with an Account and a (potentially NULL) Sponsor
Problem 6:
A spurious participation constraint of Sponsors in Owns is introduced: now every Sponsor must be associated with an Account and a Student
Problem 7:
An instance of (ternary relationship set) Owns might involve just a Student and an Account with a NULL Sponsor, which is not legal in the E/R diagram
• Q4. Relational Algebra Query: R ASJ S = R − ΠA,B(R I S)
• Q5. SQL Query:
SELECT S.sid, S.sname
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.semester=’F20’
GROUP BY S.sid, S.sname
HAVING COUNT(*)>=5 AND
S.sid IN (SELECT E2.sid
FROM Enrolled E2
WHERE E2.semester=’F19’
GROUP BY E2.sid
HAVING AVG(E2.grade)>=3.8) AND
UNIQUE (SELECT E3.cid
FROM Enrolled E3
WHERE E3.sid=S.sid);
• Q6. Equivalence of Queries: Q6.1: NO; Q6.2: YES; Q6.3: NO; Q6.4: NO; Q6.5: YES
1