Que7

Q. 10)    Consider the following Entities and Relationships                [30 Marks]
Sailor (sid, sname, age)
Boats (bid, bname, color)
Relation between Sailer and Boats is Many to Many with day as descriptive attribute.
Constraint: Primary key, age should be > 0.
Create a Database in 3NF & write queries for following.
•    Display details of all boats sailed by sailor ‘Ram’.
•    Display Sailor names working on blue boat.
•    Count number of boats sailed by each sailor.
•    Find the name of sailor who sailed the boat on both Tuesday & Friday.
•    Display details of the boats which is sailed maximum times on Sundays.

Ans.
create table sailor(
sid int primary key,
sname varchar(10),
age int check(age>0)
);
create table boats(
bid int primary key,
bname varchar(10),
color varchar(10)
);
create table sailor_boats(
sid int references sailor(sid),
bid int references boats(bid),
day varchar(10),
primary key(sid,bid)
);
insert into sailor values(501,'Ram',25);
insert into sailor values(502,'Sam',25);
insert into sailor values(503,'Gansam',25);
insert into sailor values(504,'Rima',25);

insert into boats values(601,'Godavari','blue');
insert into boats values(602,'Andaman','blue');
insert into boats values(603,'Albert','Yellow');
insert into boats values(604,'Areil','Weight');
insert into boats values(605,'Arizona','Black');

insert into sailor_boats values(501,601,'Monday');
insert into sailor_boats values(502,602,'Friday');
insert into sailor_boats values(503,603,'Sunday');
insert into sailor_boats values(504,605,'Tueday');
insert into sailor_boats values(501,604,'Sunday');
select *from sailor;
select *from boats;
select *from sailor_boats;

1]
select bid from sailor_boats  where sid=50;
2]selet
***************************************************
Q. 11)Consider the following Entities and Relationships                [30 Marks]
Supplier (sid, sname, addr)
Parts (pid, pname, pdesc)
Relation between Supplier and Parts is Many to Many with cost as descriptive attribute.
Constraint: Primary key, cost should be > 0.
Create a Database in 3NF & write queries for following.
•    Display Supplier details from 'Mumbai' city.
•    Update cost by 25 % for all parts supplied by supplier ‘Mr. Pawar’.
•    Display all parts supplied by each supplier.
•    Display details of parts which are supplied at maximum price by each supplier.
•    Display all suppliers who supply part ‘wheel’ and also display its cost.
Ans.
create table supplier(
sid int primary key,
sname varchar(10),
addr varchar(10)
);
create table parts(
pid int primary key,
pname varchar2(10),
pdesc varchar(10)
);
create table sup_parts(
sid int references supplier(sid),
pid int references parts(pid),
cost int,
primary key(sid,pid)
);
insert into supplier values(101,'Ram','Pune');
insert into supplier values(102,'Sam','Mumbai');
insert into supplier values(103,'Gamsam','Satara');
insert into supplier values(104,'Balaji','Pune');
insert into supplier values(105,'Tata','Nashik');

insert into parts values(1,'Wheel','Car');
insert into parts values(2,'Handl','Bike');
insert into parts values(3,'Stand','Bike');
insert into parts values(4,'Breks','Bus');

insert into  sup_parts values(101,1,100);
insert into  sup_parts values(102,2,200);
insert into  sup_parts values(103,3,300);
insert into  sup_parts values(104,4,400);

select *from supplier;
select *from parts;
select *from sup_parts;