Please help on mySQL Stored Procedures! Schema is given above. Consider the foll
ID: 3577388 • Letter: P
Question
Please help on mySQL Stored Procedures! Schema is given above.
Consider the following relational schema and corresponding sample data: Product model, type, maker) 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 forcign keys to Product.model. Referential integrity constraints are enforced through CASCADE for DELETE and SET NULL for UPDATE, respectively mode 100001 1000022.1161024 1000033.2 32 8192 1000042.7 64 8192 1000052.532 4096 1000062.2164096 3.2 324096 model type 100001 pc 100002 pc 100003 pc 100004 pc 100005 pc 100006 pc 200001 laptop P4 200002 laptop P5 200003 laptop Pl 200004 laptop P3 200005 laptop P3 200006 laptop P2 200007 laptop P4 200008 laptop P1 200009 laptop Pl 200010 laptop PI 30000 printer P6 300002 printer P6 300003 printer P2 300004 printer P3 300005 printer P3 model 100001D3 100002 D 100004 D 100005 D 200003 DI 200003 D2 200003 D3 200004P3 200008P3 200009P3 200010 Di 200010D2 er name price P2 P2 630 550 1000 800 980 P2 hd screen model spee 200001 2000022.116 1024 2000033.2 32512 3.2 16 4096 13 880 620 2000042.7 2000052.5 8 4096 82048 82048 570 2000062.2164096 2000072.2 200007 P3 200007P2 890 13 2000083.2324096 200009 2.8 2000103.2 32512 200004 D3 200004P1 200004 P2 300004 D3 300002DI 300002D3 300001 P3 13 180 model color type 300001 T 300002 T 300003 F 300004 F 300005 F lascr laser aser laserExplanation / Answer
Create table distributor(foreign key (model ) references product(model) int , name varchar , price int)
Create table pc(model int foreign key(model) references product(model), speed float, ram int , hd int)
Create table laptop(model int (model) references product(model), speed float, ram int ,hd int, screen int)
Crete table printer(model int (model) references product(model) , color char ,type char)
Procedure Merger Acquistion( Maker b in char ) is
maker a out char;
Begin
Maker a = maker b;
End;
Begin
Merger Acquistion(Select maker from product);
End;
(b)HolidaySale(maker,type ,discount)
Procedure HolidaySale(price in char , type in char) is
discount out char
Begin
Discount=price – discount percentage;
Dbms_output.put_line(“doiscount price is “||+discount);
end
begin
HolidaySale( select d.price from product p, distributor d where p.model=d.model,select p.type frpm product p)
end
d)insertlaptopdistributors(model,speed,ram,hd,screen,maker,distributor,price)
Procedure insertlaptopdistributors(,speed in int ,ram in int ,hd in int ,screen in int ,maker in char ,distributor in int ,price in int) is
Begin
Insert into table laptop values(model,speed,ram,hd.screen);
end
begin
insertlaptopdistributors( select p.model,p.speed,p.ram,p.hd,p.screen from laptop p );
end
create table product(model int primary key not null, type string , maker varchar)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.