RDBMS example of many to many relationship

Q.).    book ( bno, bname, pubname, price)
 
         Author ( ano, aname)
 
         Relationships between book and author is many to many.

         Constraints:-primary key,
                                Aname and pubname should NOT NULL.


SQL> select * from book_1;

       BNO BNAME                PUBNAME                             PRICE
---------- -------------------- ------------------------------ ----------
       101 C++                           nirali                                150
       102 M A/C                      vision                                250
       103 RDBMS                    BPB                                   175
       104 data structre              BPB                                   165
       105 software engineering   nirali                                135

SQL> select * from author;

       ANO ANAME
---------- --------------------
      1201  mr.dewasi
      1202  mr.shiravi b
      1203  mr.pankaj
      1204  kanetkar
      1205  babulal

SQL> select * from bookauth;

    BNO        ANO
---------- ----------
       101       1201
       102       1202
       103       1203
       104       1204
       102       1204
       105       1205
       101       1203

7 rows selected.


************************************************************************
a). create or replace a PL/SQL procedure to display details of all books written by ‘kanetkar’.

SQL>
  1  create or replace procedure disa(t in varchar2)
  2  is cursor ca is select b.bno,b.bname,b.pubname,b.price from book_1 b,
  3     author a, bookauth ba where b.bno=ba.bno and a.ano=ba.ano and
  4     a.aname=t;
  5  begin
  6     for x in ca loop
  7        dbms_output.put_line(x.bno||' '||x.bname||' '||x.pubname||' '||x.price);
  8     end loop;
  9* end;
SQL> /

Procedure created.

SQL>
  1  declare
  2  begin
  3     disa('kanetkar');
  4* end;
  5  /
104 data structre  BPB 165
102 M A/C vision 250

PL/SQL procedure successfully completed.


************************************************************************

b). create or replace a trigger that restricts insertion or updation of books having price less than 0.

SQL>
  1  create or replace trigger ta1
  2  before insert or update
  3  on book_1
  4  for each row
  5  declare
  6    p book_1.price%type;
  7  begin
  8    p:=:new.price;
  9    if(p<0) then
 10      raise_application_error(-20089,'book price must be>0');
 11    end if;
 12* end;
SQL> /

Trigger created.

SQL> insert into book_1
  2  values(106,'SE','nirali',-45);
insert into book_1
            *
ERROR at line 1:
ORA-20089: book price must be>0
ORA-06512: at "SCOTT.TA1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TA1'


SQL> update book_1
  2  set price=-45
  3  where bno=101;
update book_1
       *
ERROR at line 1:
ORA-20089: book price must be>0
ORA-06512: at "SCOTT.TA1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TA1'

************************************************************************

RDBMS example of book and dept many to one relationship

Q.) Book( bno, bname, pudname, price)

        Department( dno, dname)

        Relationships between book and department is many to one.
   
        Constraints:-primary key,  Price should bo>0.

SQL> select * from department_1;
    DNO DNAME
---------- ------------------------------
       101 computer
       102 science
       103 arts
       104 MBA
       105 MSC
SQL> select * from books_1;
 BNO     BNAME   PUBNAME    PRICE  DNO
-------   -------------  ---------------   --------- --------
201      C++              nirali               150      101
1202    numerical     BPB                180      102
             method  
1203     RDBMS     vision               140      101


  BNO     BNAME   PUBNAME    PRICE  DNO
-------   -------------  ---------------   --------- --------

1204    data structure       nirali          145       104

1205     social sic         vision            120      105
1206       POM              BPB             130      103

6 rows selected.
************************ ** ** ******************************************

a).create or replace a PL/SQL function to return total expenditure on books of a given department.

SQL>
  1  create or replace function disb(x in varchar2)return number
  2  is
  3    c number(5);
  4  begin
  5    select sum(b.price) into c from department_1 d,books_1 b
  6    where d.dno=b.dno and d.dname=x;
  7    return(c);
  8* end;
SQL> /
Function created.

SQL>
  1  declare
  2     dn department_1.dname%type;
  3     t number(10);
  4  begin
  5     dn:='&dn';
  6     t:=disb(dn);
  7     dbms_output.put_line('total expenditure on books of a'||' '||dn||' '||'is:-'||t);
  8* end;
SQL> /
Enter value for dn: computer
old   5:    dn:='&dn';
new   5:    dn:='computer';
total expenditure on books of a computer is:-290

PL/SQL procedure successfully completed.


************************************************************************
************************************************************************

b). write a cursor to display details of all books brought for a ‘computer’ department.

SQL>
  1  declare
  2    cursor c8 is select b.bno,b.bname,b.pubname,b.price from department_1 d,
  3    books_1 b where d.dno=b.dno and d.dname='computer';
  4  begin
  5    for x in c8
  6    loop
  7      dbms_output.put_line(x.bno||' '||x.bname||' '||x.pubname||' '||x.price);
  8    end loop;
  9* end;
SQL> /
1201 C++ nirali 150
1203 RDBMS vision 140

PL/SQL procedure successfully completed.


************************************************************************