• Q1:
• Q2:
General Instructions
1. SQL aggregate operators: An aggregate is an operation in SQL (e.g., MIN, AVG, etc.) for combining numerical values.
2. Renaming operator: A relational algebra operator that allows us to assign names to relations and their attributes.
3. Outer join: A variant of natural join in which tuples from one relation that do not match any tuples of the other relation can still appear in the query result, with missing attribute values padded with NULLs.
4. Identifying relationship set: A relationship set that helps define a weak entity set in an ER diagram.
5. Assertion: A powerful SQL construct to define arbitrary constraints in a database.
• Q3:
• Q4:
• Q5:
SELECT M.manager_ssn, E2.name, AVG(E1.salary)
FROM Manages M, Employees E1, Employees E2
WHERE M.employee_ssn=E1.ssn AND M.manager_ssn=E2.ssn
GROUP BY M.manager_ssn, E2.name
• Q6:
ρ(M1(employee ssn → m1 employee ssn, manager ssn → m1 manager ssn), Manages)
ρ(M2(employee ssn → m2 employee ssn, manager ssn → m2 manager ssn), Manages)
ρ((1 → ssn),
Πm1 employee ssn(M1 Im1 employee ssn<>m1 manager ssn∧m1 manager ssn=m2 employee ssn∧m1 employee ssn=m2 manager ssn
M2)
• Q7: Q7.1: NO; Q7.2: YES; Q7.3: YES; Q7.4: YES; Q7.5: YES
Q3.1: YES; Q3.2: NO; Q3.3: NO
Problem 1:
A spurious key constraint of Students into the Enrolled relationship set is introduced because of the PRIMARY KEY of StudentsEnrolled
Problem 2:
Descriptive attribute semester of Enrolled is missing
Problem 3:
cid in StudentsEnrolled could have “dangling” values because it is not a FOREIGN KEY into Courses
Problem 4:
Total participation constraint of Students in Enrolled is not modelled
1