Exercise_2.md

Exercise-II

Consider the relations: PART, SUPPLIER and SUPPLY. The Supplier relation holds information about suppliers. The attributes SID, SNAME, SADDR describes the supplier. The Part relation holds the attributes such as PID, PNAME and PCOLOR. The Shipment relation holds information about shipments that include SID and PID attributes identifying the supplier of the shipment and the part shipped, respectively. The Shipment relation should contain information on the number of parts shipped.

a) Mention the constrainst neatly.

CREATE TABLE part (
  pid number(10) primary key,
  pname varchar(30),
  pcolor varchar(30)
);
  
CREATE TABLE supplier (
  sid number(4) primary key,
  sname varchar(30),
  saddr varchar(30)
);

CREATE TABLE supply (
  pid number(10) references part(pid) on delete cascade,
  sid number(4) references supplier(sid) on delete cascade,
  quantity number(3),
  primary key(pid,sid)
);

INSERT ALL
INTO part VALUES (101, 'Bolts', 'Red')
INTO part VALUES (102, 'Nuts', 'Red')
INTO part VALUES (103, 'Plugs', 'Red')
INTO part VALUES (104, 'Bolts', 'Green')
INTO part VALUES (105, 'Nuts', 'Green')
INTO part VALUES (106, 'Plugs', 'Green')
INTO part VALUES (107, 'Bolts', 'Blue')
INTO part VALUES (108, 'Nuts', 'Blue')
INTO part VALUES (109, 'Plugs', 'Blue')
SELECT * FROM DUAL;

INSERT ALL 
INTO supplier VALUES (1, 'Ram', 'Bangalore')
INTO supplier VALUES (2, 'Shyam', 'Delhi')
INTO supplier VALUES (3, 'Harish', 'Bombay')
INTO supplier VALUES (4, 'Manish', 'Chennai')
SELECT * FROM DUAL;

INSERT ALL
INTO supply VALUES (107,4,10)
INTO supply VALUES (109,4,15)
INTO supply VALUES (102,1,3)
INTO supply VALUES (109,3,6)
INTO supply VALUES (104,3,3)
INTO supply VALUES (103,2,5)
INTO supply VALUES (109,1,10)
INTO supply VALUES (106,2,10)
INTO supply VALUES (107,2,5)
INTO supply VALUES (108,3,10)
INTO supply VALUES (106,3,5)
INTO supply VALUES (109,2,3)
INTO supply VALUES (105,1,5)
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 details of parts supplied by supplier #SNAME.

SELECT *
FROM part 
WHERE pid IN (SELECT pid
              FROM supply
              WHERE sid IN ( SELECT sid
                            FROM supplier
                            WHERE sname = 'Manish'));

2. Obtain the Names of suppliers who supply #PNAME.

SELECT *
FROM supplier 
WHERE sid IN (SELECT sid
              FROM supply
              WHERE pid IN ( SELECT pid
                            FROM part
                            WHERE pname = 'Plugs'));

3. Delete the parts which are in #PCOLOR.

DELETe FROM part
WHERE pcolor = 'Green';

SELECT * 
FROM PART;

4. List the suppliers who supplies exactly two parts.

SELECT sname
FROM supplier
WHERE sid IN (SELECT sid
             FROM supply
             GROUP BY sid
             HAVING COUNT(pid) = 2);
             

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

db.createCollection('warehouse')
db.warehouse.insertMany([
    {'PNo': 123, 'PName' :'Bolts','Color' : 'Black', 'SName' : 'Ram', 'SNo':1111 , 'Address' : 'Mumbai'},
    {'PNo': 321, 'PName' :'Chain','Color' : 'Blue', 'SName' : 'Shyam', 'SNo':1115 , 'Address' : 'Bangalore'},
    {'PNo': 124, 'PName' :'Chain','Color' : 'Blue', 'SName' : 'Raju', 'SNo':5111 , 'Address' : 'Chennai'},
    {'PNo': 312, 'PName' :'Wheels','Color' : 'Green', 'SName' : 'Kaju', 'SNo':4511 , 'Address' : 'Pune'},
    {'PNo': 122, 'PName' :'Nuts','Color' : 'White', 'SName' : 'Manish', 'SNo':3111 , 'Address' : 'Delhi'}
])

1. Update the details of parts for a given part identifier: #PID.

db.warehouse.update({'PNo':122, {$set : {'Color':'Black'}},{multi:true}})
db.warehouse.find().pretty()

2. Display all suppliers who supply the part with part identifier: #PID.

db.warehouse.find({'PNo':122}).pretty()

f)Write a PL/SQL program to copy the contents of the Supply table to another table for maintaining records for specific part number.

1. Create a table Shipment with same schema as supply

Note : We add an always false condition 1=2 here to avoid copying rows from the supply table. We only copy the schema.

CREATE TABLE shipment AS
(SELECT *
FROM supply
WHERE 1=2);

2. Create the PL/SQL file - copy.sql using the command edit copy.sql

DECLARE
    cursor c1 is select * from supply;
    v_rec supply%rowtype;
BEGIN
    open c1;
    loop
    fetch c1 into v_rec;
    exit when c1%notfound;
    insert into shipment values(v_rec.pid,v_rec.sid,v_rec.quantity);
    where v_rec.pid in (102,103,107)
    end loop;
    close c1;
END;
/

3. Commands to execute the PL/SQL file -

set serveroutput on;
@copy.sql
plsql execution completed successfully

SELECT *
FROM shipment;