Exercise_5.md

Exercise-V

Consider the Book Lending system from the library- BOOKS, STUDENT, BORROWS. The students are allowed to borrow any number of books on a given date from the library. The details of the book should include ISBN, Title of the Book, author and publisher. All students need not compulsorily borrow books.

a) Mention the constrainst neatly.

CREATE TABLE books (
  isbn number(4) primary key,
  title varchar(30),
  author varchar(30)
);
  
CREATE TABLE student (
  sid number(4) primary key,
  name varchar(30),
  gender char(1)
);

CREATE TABLE borrows (
  sid number(4) references student(sid) on delete cascade,
  isbn number(4) references books(isbn) on delete cascade,
  issuedate date,
  primary key(sid,isbn)
);

INSERT ALL
INTO books VALUES (101, 'C Programming', 'Reema Thareja')
INTO books VALUES (102, 'Computer Networks', 'V S Bagad')
INTO books VALUES (103, 'OOPS with Java', 'Siddharth Santosh')
INTO books VALUES (104, 'Software Engineering', 'T Dayar')
INTO books VALUES (105, 'Python Programming', 'Sumita Arora')
INTO books VALUES (106, 'DBMS', 'Sumita Arora')
SELECT * FROM DUAL;

INSERT ALL 
INTO student VALUES (1, 'Ram', 'M')
INTO student VALUES (2, 'Sita', 'F')
INTO student VALUES (3, 'Shyam', 'M')
INTO student VALUES (4, 'Manish', 'M')
INTO student VALUES (5, 'Harish', 'M')
INTO student VALUES (6, 'Gopal', 'M')
SELECT * FROM DUAL;

INSERT ALL
INTO borrows VALUES (1,101,'1-2-22')
INTO borrows VALUES (1,102,'1-2-22')
INTO borrows VALUES (2,101,'1-3-22')
INTO borrows VALUES (2,103,'1-4-22')
INTO borrows VALUES (2,104,'1-3-22')
INTO borrows VALUES (3,102,'1-4-22')
INTO borrows VALUES (4,102,'1-5-22')
INTO borrows VALUES (5,105,'1-6-22')
INTO borrows VALUES (6,105,'1-7-22')
SELECT * FROM DUAL;

b) Design the ER diagram for the problem statement.

c) State the schema diagram for the ER diagram.

d) Create the tables, insert suitable tuples (min 6 each) and perform the following operations in SQL

1. Obtain the names of the student who has borrowed either book bearing ISBN ‘101’ or ISBN ‘105’.

SELECT sid, name 
FROM student 
WHERE sid IN ( SELECT sid 
             FROM borrows 
             WHERE isbn = 101 
             OR isbn = 105)

2. Obtain the Names of female students who have borrowed “OOPS with Java” books.

SELECT sid, name 
FROM student 
WHERE sid IN ( SELECT sid 
             FROM borrows 
             WHERE isbn IN ( SELECT isbn 
                           FROM books
                           WHERE title = 'OOPS with Java'))
AND gender = 'F';

3. Find the number of books borrowed by each student. Display the student details along with the number of books.

SELECT student.sid, name, gender, COUNT(borrows.isbn) as 'No. of Books Issued'
FROM student, borrows
WHERE student.sid = borrows.sid
GROUP BY student.sid;

4. List the books that begin with the letters “DB” and has never been borrowed by any students.

SELECT title
FROM books
WHERE title like 'DB%'
AND isbn NOT IN (SELECT DISTINCT isbn 
               FROM borrows);
             

e) Create the table, insert suitable tuples and perform the following operations using MongoDB

db.createCollection('Library')
db.Library.insertMany([
    {'ISBN': 101, 'Title' :'C Programming','Author' : 'Reema Thareja', 'SName' : 'Ram', 'SID':1 , 'Date' : '1-2-22'},
    {'ISBN': 101, 'Title' :'C Programming','Author' : 'Reema Thareja', 'SName' : 'Sita', 'SID':2 , 'Date' : '1-2-22'},
    {'ISBN': 101, 'Title' :'C Programming','Author' : 'Reema Thareja', 'SName' : 'Manish', 'SID':4 , 'Date' : '1-2-22'},
    {'ISBN': 102, 'Title' :'DBMS','Author' : 'Reema Thareja', 'SName' : 'Ram', 'SID':1 , 'Date' : '1-2-22'},
    {'ISBN': 103, 'Title' :'Python Programming','Author' : 'Sumita Arora', 'SName' : 'Sita', 'SID':2 , 'Date' : '1-2-22'},
])

1. Obtain the book details authored by “author_name”.

db.Library.distinct('Title',{ 'Author':'Reema Thareja'})

2. Obtain the Names of students who have borrowed “C Programming” books.

db.Library.find({'Title' :'C Programming'}, { _id : 0, 'SName' : 1}).pretty()

f)Write a PL/SQL procedure to print the first 8 Fibonacci numbers and a program to call the same.

1. Create the PL/SQL file - fib.sql using the command edit fib.sql

DECLARE
    a number := 0;
    b number := 1;
    c number := 0;
    counter number := 2;

PROCEDURE fibonacci is
	BEGIN
    	while (counter < 8)
        	loop
            c := a+b;
            dbms_output.put_line(c);
            a := b;
            b := c;
            counter := counter + 1;
        end loop;
    END;
BEGIN
  dbms_output.put_line('Fibonacci Series : ');
	dbms_output.put_line(a);
	dbms_output.put_line(b);
	fibonacci;
END;
/

2. Commands to execute the PL/SQL file -

set serveroutput on;
@fib.sql
Fibonacci Series : 
0
1
1
2
3
5
8
13