Indicate how you would express the integrity constraints listed below in SQL ass
ID: 3799146 • Letter: I
Question
Indicate how you would express the integrity constraints listed below in SQL assuming the following schema modeling the inventory of a computer store:
Product (maker, model, type)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
For this schema, the Product relation gives the manufacturer, model number and type (PC, laptop or printer) of various products. You may assume for convenience that model numbers are unique over all manufacturers and product types. The PC relation gives for each model number that is a PC the speed (of the processor in Ghz), the amount of RAM (in GB), the size of the hard disk (in TB) and the price. The Laptop relation is similar, except that the screen size (in inches) is also shown. The Printer relation records for each printer model whether the printer produces color output (true, if so), the process type (laser or ink-jet) and the price.
a) When updating the price of a PC, check that there is no lower priced PC with the same speed
b) No manufacturer of a PC may also make laptops
c) A manufacturer of a PC must also make a laptop with at least as great a processor speed.
d) Every Printer model number should appear in Product.
e) If a laptop has more main memory than a PC, then it must also be more expensive than a PC
Explanation / Answer
Cosider below table as example:
Product_type:
product_type_id product_type
1 PC
2 Laptop
3 Printer
Process_type:
process_type_id process_type
1 laser
2 ink-jet
personal_computer:
Model speed RAM HD Price
WX78KS011 3.60 132 15.6 35000
WX78KS012 2.60 120 22.5 30500
WX78KS013 4.60 140 20.5 45500
WX78KS014 3.60 200 18.7 45500
laptop:
Model speed RAM HD Screen Price
WX78LS011 3.60 130 15.6 15 30000
WX78LS012 2.60 110 22.5 22 28500
WX78LS013 4.60 145 20.5 20 45600
WX78LS014 3.60 250 18.7 18 46500
printer:
Model color Type(process_type) Price
PS011 true 1 3500
PS012 false 0 2580
PS013 true 2 4050
PS014 true 1 3850
Product:
Maker(Manufacturers) Model Type(product_type)
1 WX78KS011 1 //pc
2 WX78KS012 1 //pc
2 WX78LS011 2 //lap
4 WX78LS012 2 //lap
5 PS011 3 //printer
2 PS012 3 //printer
1 WX78KS013 1 //pc
4 WX78LS013 2 //lap
5 PS013 3 //printer
5 PS014 3 //printer
2 WX78LS014 2 //lap
1 WX78KS014 1 //pc
Relation
Manufacturers 1 = pc, Manufacturers 2 = pc,laptop, printer, Manufacturers 4 = laptop, Manufacturers 5 = printer.
a) When updating the price of a PC, check that there is no lower priced PC with the same speed
you can use trigger method for above case, it will triggered when updating PC price,
CREATE TRIGGER triggerPricePC ON personal_computer
FOR UPDATE
AS
BEGIN
declare @pc_price int;
declare @pc_speed int;
declare @check_price_cnt int;
declare @model_num varchar(200);
select @pc_speed =speed from personal_computer where price = @pc_price
select @check_price_cnt=Count(*) from personal_computer where price < @pc_price and speed = @pc_speed
if(@check_price_cnt>1 )
BEGIN
Print 'Price already exists for this speed of PC';
END
else
BEGIN
UPDATE personal_computer SET price=@pc_price WHERE Model =@model_num
Commit
END
END
b) No manufacturer of a PC may also make laptops
That is the manufacturer does not make laptop, but they can make PC and printer
SELECT maker, model, type FROM product WHERE type = 1 AND maker != (SELECT * FROM product WHERE type=2);
sample output:
Maker Model Type
1 WX78KS011 1
1 WX78KS013 1
1 WX78KS014 1
c) A manufacturer of a PC must also make a laptop with at least as great a processor speed.
SELECT p.maker, p.model, p.type
FROM product p
INNER JOIN personal_computer pc ON pc.model = p.model
INNER JOIN laptop lp ON lp.model = p.model
WHERE p.Type = 1 AND (p.Type = 2 AND lp.speed > pc.speed);
sample output:
Maker Model Type
2 WX78LS011 2
Here maker 2 is make both PC and Laptop. And WX78LS011(3.60 - speed) has greater speed of WX78KS012(2.60 - speed) PC
d) Every Printer model number should appear in Product.
Which means need to get printer information.
SELECT p.maker, p.model, p.type, pr.color, pr.Type, pr.price
FROM product p
INNER JOIN printer pr ON pr.model = p.model
WHERE p.Type = 3;
sample output:
Maker Model Type color type price
2 PS012 3 false 0 2580
5 PS011 3 true 1 3500
5 PS013 3 true 2 4050
5 PS014 3 true 1 3850
e) If a laptop has more main memory than a PC, then it must also be more expensive than a PC
the laptop has more memory than PC, and price also greater that PC
SELECT p.maker, p.model, p.type, lp.ram, lp.price
FROM product p
INNER JOIN personal_computer pc ON pc.model = p.model
INNER JOIN laptop lp ON lp.model = p.model
WHERE p.Type = 2 AND lp.price > pc.price AND lp.ram > pc.ram;
WX78LS013 4.60 145 20.5 20 45600
WX78LS014 3.60 250 18.7 18 46500
sample output:
Maker Model Type ram price
4 WX78LS013 2 145 45600
2 WX78LS014 2 250 46500