Exercise 3 (constraints, 4 pts) 1.Write a CHECK constraint that expresses the fo
ID: 3721429 • Letter: E
Question
Exercise 3 (constraints, 4 pts)
1.Write a CHECK constraint that expresses the following condition on records in table Product in Hardware Database: “Maker H produces only printers”.
2.Write a CHECK constraint that expresses the following condition on table Schedule in University Database: “Each instructor occurring in this table teaches at most three classes per term”.
Product maker model type B 1004 pc B 1005 pc B 1006 pc C 1007 pc D 1008 pc D 1009 pc D 1010 pc E 1011 pc E 1012 pc E 1013 pc E 2001 laptop E 2002 laptop E 2003 laptop A 2004 laptop A 2005 laptop A 2006 laptop B 2007 laptop F 2008 laptop F 2009 laptop G 2010 laptop E 3001 printer E 3002 printer E 3003 printer D 3004 printer D 3005 printer H 3006 printer H 3007 printer
Explanation / Answer
If you need any clarifications, please give me comment...
CREATE ASSERTION produces_H_printers CHECK(
NOT EXISTS(
SELECT *
FROM Product
WHERE maker = 'H' AND type='printers'
)
);
CREATE ASSERTION teaches_per_tem CHECK(
NOT EXISTS(
SELECT *
FROM Schedule
GROUP BY Term
HAVING COUNT(*)<=3
)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.