SQL Assignment - Soln to Q2, Q3

SQL Assignment - Soln to Q2, Q3 - 1) i) There is total...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1) i) There is total participation from 'Technician' entity to 'Expertise' relation. ii) Covering constraints: There may be employees who are neither Technicians nor Traffic Controllers; their details are not accounted for in the database. iii) Overlapping Constraint: If there are employees who are both Technicians and Traffic Controllers, we need to make sure that their details in the two tables (technician and traffic controller) are equivalent to each other. Conditions or assertions maybe required to enforce such compatibility.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2.1) SELECT S.snames FROM Suppliers S WHERE NOT EXISTS (SELECT P.pid FROM Parts P WHERE NOT EXISTS (SELECT C.pid FROM Catalog C WHERE C.pid = P.pid AND C.sid = S.sid)) 2.2) SELECT DISTINCT C.sid FROM Catalog C WHERE C.cost > (SELECT AVG(C1.cost) FROM Catalog C1 WHERE C1.pid = C.pid) 2.3) SELECT S.sname FROM Suppliers S, Parts P, Catalog C WHERE S.sid = C.sid AND C.pid = P.pid AND C.cost = (SELECT MAX(C1.cost) FROM Catalog C1 WHERE C1.pid = P.pid) 2.4) SELECT C.sid
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 5

SQL Assignment - Soln to Q2, Q3 - 1) i) There is total...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online