Need help on Part B and D! Consider the following relational schcma and correspo

Question

Need help on Part B and D!

Consider the following relational schcma and corresponding sample data: Product (model. type, ma Distributor (model, name price PC (model, speed, ram, hd) Laptop (model, speed, ram, hd, screen) Printer (model, color, type) The keys are underlined. Distributor.model, PC.model, Laptop.model, and Printer. model are all foreign keys to Product model. Rcforential integrity constraints arc cnforccd through CASCADE for DELETE and SET NULL for UPDATE respectively. model speed ram had 100001 3.2 32 1096 100002 2.1 16 1024 model model maker type name price 100003 3.2 32 8192 100001 D3 100001 P1 680 100004 2.7 64 8192 100002 pc P2 100002 D3 630 100005 32 4096 100003 pc P2 100004 D3 780 100006 2.2 16 1096 P3 100005 D3 100004 pe 550 200003 DI P1 1000 100005 model speed ram had screen 100006 pc P2 200003 D2 800 200001 laptop PA 200003 D3 980 200001 3.2 16 1096 15 200002 laptop P5 200004 P3 680 200002 2.1 16 1024 13 200003 laptop Pl 3.2 200008 P3 880 200003 512 17 200004 laptop P3 200009 P3 620 200004 2.7 256 15 200005 200005 laptop P3 200010 DI 2.5 8 4096 15 200006 laptop P2 200010 D2 2.2 570 200006 16 17 200007 P3 200007 laptop P4 930 20000 2.2 8 2048 17 200008 laptop P1 200007 P2 890 200008 3.2 32 4096 13 200009 laptop Pl 200004 D3 680 200009 2.8 8 2048 15 200010 200010 laptop Pl 200004 P1 580 3.2 32 12 13 300001 printer Po 200001 P2 610 300002 print cr P6 300004 D3 180 model color type 300003 printer P2 300002 D1 280 300001 T laser 300004 print cr P3 300002 D3 200 300002 T laser 300005 printer P3 300001 P3 80 300003 F ink-jet 300004 F laser 300005 F laser

Explanation / Answer

Answer Number B)-

delimiter //<in MySql>

create trigger my_trigger<name_of_the_trigger>

before UPDATE on distributer <name_of_the_table _here you can place table name distributer>

for each row

begin

set @P1= (SELECT PRICENAME FROM distributer <name_of_the_table> WHERE max(PRICE)-min(price));

    if(@P1<200)

<here_you_replace your code which is used for update the column of table those satisfied the condition>

    then set @price=@price + 20;

    end if;

    end //

Answer Number D)-

delimiter ////<in MySql>

create trigger my_trigger_1<name_of_the_trigger>

after delete on printer<name_of_the_table>

for each row

begin

delete from printer where model=<you_should_pass the value_here>;

delete from product where product.model= printer.model;

delete from product where distributer.model= printer.model;

end //

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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