Slip 16 - C) Consider the following entities and their relationships. Game (game_name, no_of_players, coach_name) Player (pid, pname, address, club_name) Relation between Game and Player is Many to Many. Constraint: Primary key, no_of_players should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a procedure which will display games details having number of players more than 5. 2) Write a trigger which will fire before insert or update on Game having no_of_players less than or equal to zero. (Raise user defined exception and give appropriate message)

Solution:

1) Write a procedure which will display games details having number of players more than 5. 

SQL> set serveroutput on 

SQL> create or replace procedure p3(n in varchar)as cursor c3 is select gname,coach_name from game2,player,gp where game2.gid=gp.gid and player.pid=gp.pid and no_of_players > 5; 

c c3 %rowtype; 

begin 

open c3;

dbms_output.put_line('gname'||"||'coach_na me'); 

Loop 

Fetch c3 into c; 

exit when c3 %notfound; 

if(c.gname=n)then 

dbms_output.put_line(c.gname||"||c.coach_n ame); 

end if; 

end loop; 

close c3; 

end; 

/ 

Procedure created. 

begin 

p3('football'); 

end;

2) Write a trigger which will fire before insert or update on Game having no_of_players less than or equal to zero. (Raise user defined exception and give appropriate message)

SQL> set serveroutput on 

SQL> create or replace trigger t5 before insert or update on game2 

for each row 

begin 

if (:new.no_of_players<=0)then 

raise_application_error (-20001,'no_of_players>'); 

end if; 

end; 

 /

Trigger created.