Write the following assertions. The database schema is from the \"PC\" example:

Question

Write the following assertions. The database schema is from the "PC" example:
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price

a) No manufacturer of PC's may also make laptops. 7.4. ASSERTIONS 331 Comparison of Constraints The following table lists the principal differences among attribute-based checks, tuple-based checks, and assertions Where Type of Constraint Declared Attribute With based CHECK attribute When Activated n insertion to relation or attribute update to Hold? Not subqueries Elemento Not i subqueries n insertion based CHECK relation scha to relation or tuple update On any change to Yes relation AssertionE Elemento database schema any mentioned b) A manufacturer of a PC must also make a laptop with at least as great a c) If a laptop has a larger main memory than a PC, then the laptop must d) If the relation Product mentions a model and its type, then this model processor speed. also have a higher price than the PC must appear in the relation appropriate to that type.

Explanation / Answer

If you have any doubts, please give me comment... I will help you...

-- a) No manufacturer of PCs may also make laptops
CREATE ASSERTION makeLaptops
CHECK (NOT EXISTS (
    SELECT maker
    FROM Product
    WHERE type = 'pc'
        AND maker IN(
            SELECT maker
            FROM Product
            WHERE type = 'laptop'
        )
));

--- b) A manufacturer of a PC also make a laptop with at least as great a processor speed.
CREATE ASSERTION greatProcSpeed
CHECK(NOT EXISTS(
    SELECT maker
    FROM Product P1 NATURAL JOIN PC
    WHERE speed > ALL(
        SELECT speed
        FROM Product P2 NATURAL JOIN Laptop
        WHERE P2.maker = P1.maker
    )
));

--c) If a laptop has a large main memory than a PC, then the laptop must also has a higher price than the PC.

CREATE ASSERTION checkHigher
CHECK(NOT EXISTS(
    SELECT model
    FROM Laptop, PC
    WHERE Laptop.ram > PC.ram AND Laptop.price < PC.price
));

--d) If the relation Product mentions a model and its type then this model must appear in the relation appropriate to that type.
CREATE ASSERTION mustAppear
CHECK( NOT EXISTS(
    SELECT model
    FROM Product
    WHERE (type=='pc' AND model NOT IN (SELECT model FROM PC))
    OR (type == 'laptop' AND model NOT IN (SELECT model FROM Laptop))
    OR (type == 'printer' AND model NOT IN (SELECT model FROM printer))
));

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.