Dbms Darshan Report

Database Management Systems Lab Report
Submi(ed for the par2al fulfillment of Bachelor of Engineering
By
DARSHAN .S. (1SI21CS036)
Department of Computer Science and Engineering
(Program Accredited by NBA)
Siddaganga Ins6tute of Technology, Tumakuru – 572103

(An autonomous ins2tu2on affiliated to VTU, Belagavi, Approved by AICTE, New Delhi,
Accredited by NAAC with 'A++' grade & ISO 9001:2015 Cer2fied)
2023-2024
1
LAB PROGRAM-1
Suppose a movie studio has several film fields. A crew
must be designated by a given studio as crew 1,crew 2
and so on. However other studios might use the same
designa>on for crews so the a@ribute key number is
not a key for crews. Movie studio holds the
informa>on like name branch and several loca>ons.
each crew holds informa>on like sector and strength.
ER-DIAGRAM
2
create table movie_studio
(sname varchar(25),
branch varchar(25),
primary key(sname));
desc movie_studio;
create table crew
(sname varchar(25),
crew_no int,
sector int,
strength int,
primary key(sname,crew_no),
foreign key(sname) references movie_studio(sname));
desc crew;
create table studio_loca2on
(sname varchar(25),
loca2on varchar(25),
primary key(sname,loca2on),
foreign key(sname) references movie_studio(sname));
desc studio_loca2on;
3
Inser1ng values into the table
insert into movie_studio values
-> ('Gayatri','b1');
mysql> insert into movie_studio values
-> ('Maruthi','b2');
-> ('Krishna','b3');
('ionax','b4');
('Narthaki','b5');
-> ('Rockline','b6');
mysql> select * from movie_studio;
insert into crew values
4
('Gayatri',1,2,20);
mysql> insert into crew values
-> ('Maruthi',5,4,100);
mysql> insert into crew values
-> ('ionax',3,6,40);
mysql> select * from crew;
insert into studio_loca2on values
-> ('Gayatri','BH road');
mysql> insert into studio_loca2on values
-> ('Maruthi','MG road');
-> ('ionax','JP Nagar');
Query OK, 1 row affected (0.01 sec)
-> ('Krishna','Vijay Nagar');
-> ('Rockline','RR Nagar');
-> ('Narthaki','Jaya Nagar');
5
Queries lab-program -1
1)List all the movie_studios name which does not use a single crew
select sname from movie_studio
-> where sname not in
-> (select sname from crew);
2)Reterieve the movie studio which uses highest strength crew

mysql> select sname from crew
-> where strength>=ALL
-> (select strength from crew);
6
Write a procedure retrieve all crews used by specific studio.
DELIMITER //
CREATE PROCEDURE display_crew(IN sname VARCHAR(25))
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE crew_no_var INT;
DECLARE strength_var INT;
DECLARE sector_var VARCHAR(25);
DECLARE c CURSOR FOR
SELECT c1.crew_no,c1.strength,c1.sector
FROM crew c1
JOIN movie_studio m ON c1.sname=m.sname
WHERE m.sname=sname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN c;
read_loop:LOOP
FETCH c INTO crew_no_var,strength_var,sector_var;
IF done THEN
LEAVE read_loop;
END IF;
SELECT CONCAT(crew_no_var,' ',strength_var,' ',sector_var) AS result;
END LOOP;
CLOSE c;
END //
DELIMITER ;
7
Write a before insert trigger to check maximum number of crews to any
studio is limited to 5.
DELIMITER //
CREATE TRIGGER max_crew
BEFORE INSERT ON crew
FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(*) INTO cnt FROM crew
WHERE sname = NEW.sname;
IF cnt > 5 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'MAX_reached';
END IF;
END;
//
DELIMITER ;
8
9
Lab program -2
The production company is organised into
different studios. We store each studio's name
branch and location. Every studio must own
atleast one movie. We store each movie's title,
sensor number and year of production. Star
may act in any number of movies and we store
each actor's name and address.
ER-DIAGRAM
10
create table studios
-> (name varchar(25),branch varchar(25),loca2on varchar(25),primary key(name));
Query OK, 0 rows affected (0.03 sec)
desc studios;
mysql> create table movie
-> (2tle varchar(25),
-> sensor_no int,
-> yop int,
-> primary key(2tle));
desc movie;
mysql> create table actor
-> (aname varchar(25),
-> address varchar(25),
-> primary key(aname));
desc actor;
11
mysql> create table owns
-> (name varchar(25),
-> 2tle varchar(25),
-> primary key(name,2tle),
-> foreign key(name) references studios(name),
-> foreign key(2tle) references movie(2tle));
desc owns;
mysql> create table acts
-> (aname varchar(25),
-> 2tle varchar(25),
-> primary key(aname,2tle),
-> foreign key(aname) references actor(aname));
desc acts;
12
insert into studios values
-> ('maruthi','b1','MG Road');
mysql> insert into studios values
-> ('gayatri','b2','BH Road');
-> ('ionax','b3','RR Nagar');
-> ('krishna','b4','Jayanagar');
-> ('narthaki','b5','Vijay Nagar');
mysql> select * from studios;
13
mysql> insert into movie values
-> ('xyz',1,2002);
-> ('kantara',2,2005);
-> ('katera',3,2007);
-> ('kushi',4,2008);
-> ('leo',5,2012);
-> ('vikram',6,2015);
-> ('varisu',7,2017);
mysql> select * from movie;
14
mysql> insert into actor values
-> ('DBoss','JayNagar');
-> ('Yash','GandiNagar');
-> ('Sudeep','Dollars colony');
-> ('Thalaphathy','JP Nagar');
-> ('Vijay Devarkonda','Manjunath nagar');
-> ('Ramya','RT Nagar');
15
-> ('Rashmika','Gokula Extension');
-> ('Radika','saraswa2 puram');
-> ('Druva Sarja','Maruthi Nagar');
select * from actor;
mysql> insert into owns values
-> ('gayatri','xyz');
-> ('maruthi','xyz');
16
-> ('ionax','xyz');
-> ('krishna','xyz');
-> ('krishna','leo');
-> ('gayatri','vikram');
-> ('maruthi','varisu');
-> ('maruthi','katera');
-> ('krishna','katera');
-> ('maruthi','kantara');
17
-> ('ionax','kantara');
-> ('ionax','kushi');
mysql> select * from owns;
mysql> insert into acts values
-> ('DBoss','katera');
-> ('DBoss','xyz');
-> ('Ramya','xyz');
18
-> ('Sudeep','xyz');
-> ('Yash','xyz');
-> ('Rashmika','xyz');
-> ('Druva Sarja','xyz');
-> ('Druva Sarja','leo');
-> ('Rashmika','leo');
mysql> select * from acts;
19
Queries for lab program-2
1)list all the studios of the movie ‘xyz’
select s.name,s.branch,s.loca2on
-> from movie m,owns o,studios s
-> where m.2tle='xyz' and
-> m.2tle=o.2tle and
-> o.name=s.name;
20
2)list all the actors acted in the movie ‘xyz’
select a.aname
-> from acts a,movie m
-> where m.2tle='xyz' and
-> a.2tle=m.2tle;
Write a procedure to list all movies produced during the specific year.
DELIMITER //
CREATE PROCEDURE pr2(IN s INT)
BEGIN
DECLARE v_2tle VARCHAR(25);
DECLARE cur_done INT DEFAULT FALSE;
DECLARE cur_2tle CURSOR FOR
SELECT 2tle
FROM movie
WHERE yop = s; -- Use the correct column name: yop
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_done = TRUE;
OPEN cur_2tle;
read_loop: LOOP
FETCH cur_2tle INTO v_2tle;
IF cur_done THEN
LEAVE read_loop;
END IF;
SELECT v_2tle;
21
END LOOP;
CLOSE cur_2tle;
END //
DELIMITER ;
Write a deleBon trigger, does not allow to deleBng current year movies.
DELIMITER //
CREATE TRIGGER tr2
BEFORE DELETE ON movie1
FOR EACH ROW
BEGIN
DECLARE cur_year INT;
SET cur_year = YEAR(NOW());
IF OLD.year = cur_year THEN
SET MESSAGE_TEXT = 'Cannot delete';
END IF;
END //
DELIMITER ;
22
23
Lab-Program-3
A produc)on company is organised into different
studios. We store each studio's name, branch
and loca)on. Studio own any number of Cartoon
serials. We store cartoon studio's )tle, sensor
number and year of produc)on. Star may do
voices in any number of Cartoon serials. We store
each actor's name and address.
ER-DIAGRAM
24
create table cartoon studio
(sname varchar(25),
branch varchar(25),
loca2on varchar(25),
primary key(sname));
desc cartoon_studio;
create table cartoons
(c2tle varchar(25),
25
sensor_no int,
yop int,
primary key(c2tle));
desc cartoons;
create table actors
(aname varchar(25),
address varchar(25),
primary key(aname));
desc actors;
create table owning
(sname varchar(25),
c2tle varchar(25),
foreign key(sname) references cartoon_studio(sname),
foreign key(c2tle) references cartoons(c2tle),
primary key(sname,c2tle));
26
desc owning;
create table ac2ng
(aname varchar(25),
c2tle varchar(25),
foreign key(aname) references actors(aname),
foreign key(c2tle) references cartoons(c2tle),
primary key(aname,c2tle));
desc ac2ng;
insert into cartoon_studio values
('Gayatri','cnland','france');
('Maruthi','pogoland','India');
('ionax','Large Corporate Anima2on','America');
select * from cartoon_studio;
27
('Narthaki','Small contractor','Japan');
('Krishna','Small contractor','Japan');
Inser6ng values into cartoons table

insert into cartoons values
('Tom and jerry',15,2000);
('chota bheem',11,2003);
('roll no 21',13,2007);
('Doora',18,2014);
('Mickeymouse',12,2005);
('Balkrishna',17,2008);
28
InserBng values into actors table
insert into actors values
('JohyDep','london');
('james','USA');
('Tom kenny','England');
insert into ac2ng values
('chota bheem','JohyDep');
('chota bheem','james');
29
('chota bheem','Tom Kenny');
('Doora','Tom Kenny');
('Doora','james');
('','james');
('Balkrishna','james');
Inser2ng values into owning
insert into owning values
('Gayatri','chota bheem');
('ionax','Mickeymouse');
('Krishna','Doora');
('Maruthi','roll no 21');
('Narthaki','Tom and jerry');
30
Queries
1) Find the total number of actors do voice in cartoon serial xyz.
select count(*)
-> from ac2ng
-> group by c2tle
-> having c2tle='chotabheem';
2) Reterieve name of studio and cartoon serial’s 6tle in which

star abc voiced.
select s.sname,s.loca2on,c.c2tle
-> from ac2ng a, cartoons c,owning o,cartoon_studio s
-> where a.aname='james' AND
-> a.c2tle=c.c2tle AND
31
-> o.sname=s.sname;
Write a procedure to list all Cartoon-Serials produced during the specific

year.
DELIMITER //
CREATE PROCEDURE pr03(IN s INT)
BEGIN
DECLARE v_title VARCHAR(25);
DECLARE cur_done BOOLEAN DEFAULT FALSE;
DECLARE cur_title CURSOR FOR
SELECT title
FROM CARTOON
WHERE yop = s;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_done = TRUE;
OPEN cur_title;
read_loop:LOOP
FETCH cur_title INTO v_title;
32
IF cur_done THEN
LEAVE read_loop;
END IF;
SELECT v_title;
END LOOP;
CLOSE cur_title;
END//
DELIMITER ;
Write a deleBon trigger, does not allow to deleBng current year Cartoon-
Serials.
DELIMITER //
CREATE TRIGGER tr3
BEFORE DELETE ON CARTOON
FOR EACH ROW
BEGIN
DECLARE cur_year INT;
SET cur_year = YEAR(NOW());
IF OLD.yop = cur_year THEN
SET MESSAGE_TEXT='Cannot Delete';
END IF;
END//
DELIMITER ;
33
34
LAB-PROGRAM-4
Car marke)ng company wants to keep track of
marketed cars and their owners. Each car must
be associated with a single owner and the owner
may have any number of cars.We store car's
register number,model and colour. owner's
name, address and SSN. We also store date of
purchase of each car.
ER-DIAGRAM
35
create table car
(rno int,
model varchar(25),
color varchar(20),
ssn int,
dop date,
primary key(rno));
desc car;
create table owners
(ssn int,
oname varchar(25),
address varchar(30),
primary key(ssn));
mysql> alter table car add foreign key(ssn) references owners(ssn);
Records: 0 Duplicates: 0 Warnings: 0
desc owners;
36
mysql> insert into owners values
-> (64521,'Nandu','RR Nagar');
-> (98154,'Bunty','JP Nagar');
-> (67246,'Chinnu','JayaNagar');
-> (90163,'Rocky','JayaNagar');
-> (85412,'Pinky','RamNagar');
37
-> (49162,'Charu','Gokula Extension');
-> (85123,'Ramya','Vijaya Nagar');
-> (93265,'Navya','CSI Layout');
mysql> select * from owners;
mysql> insert into car values
-> (81563,'Lamborgini','Black',64521,'2011-11-11');
-> (38915,'Range Rovers','White',64521,'2012-04-09');
-> (19742,'BMW','Silver',64521,'2013-01-06');
38
-> (27890,'Audi','White',64521,'2015-09-02');
-> (83167,'Ferrari F8','Black',64521,'2018-07-10');
-> (92356,'Innova','White',64521,'2003-06-05');
-> (76903,'Brezza','White',49162,'2004-08-12');
-> (18543,'Boleno','Black',49162,'2019-03-09');
-> (38236,'Bolero','Black',67246,'2016-06-07');
-> (94512,'Jaguar','White',85123,'2008-02-12');
-> (23546,'Toyota','White',85412,'2014-08-09');
39
-> (93451,'Jeep','White',85412,'2010-09-06');
-> (12890,'Ciaz','Black',90163,'2011-02-09');
-> (29045,'Maruthi 800','Black',93265,'2016-08-01');
-> (32189,'Honda','White',98154,'2013-02-04');
-> (95162,'Mercedes Benz','White',64521,'2003-11-03');
-> (67125,'Rolls-Royce','White',64521,'2000-03-03');
mysql> select * from car;
40
Queries for lab program-4
1)Find a person who owns highest number of cars
mysql> select o.oname,c.ssn,count(*)
-> from car c,owners o
-> where c.ssn=o.ssn
-> group by o.oname,c.ssn
-> having count(c.ssn)>=ALL
-> (select count(c1.ssn)
-> from car c1
-> group by c1.ssn);
41
2)Reterieve person’s and car’s informa6on purchased on the day
‘2011-11-11’
mysql> select o.oname,o.ssn,o.address,c.rno,c.model,c.color
-> from owners o,car c
-> where c.dop='2011-11-11' AND
-> c.ssn=o.ssn;
Write a procedure to list all cars and owner informaBon purchased during the
specific year.
DELIMITER //
CREATE PROCEDURE pr4(IN pur_date DATE)
BEGIN
DECLARE v_name VARCHAR(25);
DECLARE v_ssn VARCHAR(25);
DECLARE v_address VARCHAR(25);
DECLARE v_rgno VARCHAR(25);
42
DECLARE v_model VARCHAR(25);
DECLARE v_color VARCHAR(25);
DECLARE done INT DEFAULT FALSE; -- Declara2on of done variable
DECLARE cur_owner CURSOR FOR
SELECT o.oname, o.ssn, o.address
FROM owners o, car c1
WHERE c1.ssn = o.ssn AND c1.dop = pur_date;
DECLARE cur_car CURSOR FOR
SELECT c1.rno, c1.model, c1.color
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;
OPEN cur_owner;
SELECT 'OWNER DETAILS';
read_loop_owner: LOOP
FETCH cur_owner INTO v_name, v_ssn, v_address;
IF done THEN
LEAVE read_loop_owner;
END IF;
SELECT CONCAT(v_name, ' ', v_ssn, ' ', v_address);
END LOOP;
CLOSE cur_owner;
SET done = FALSE;
OPEN cur_car;
SELECT 'CAR DETAILS';
read_loop_car: LOOP
FETCH cur_car INTO v_rgno, v_model, v_color;
IF done THEN
LEAVE read_loop_car;
END IF;
43
SELECT CONCAT(v_rgno, ' ', v_model, ' ', v_color);
END LOOP;
CLOSE cur_car;
END //
DELIMITER ;
Write a inserBon trigger to check date of purchase must be less

than current date (must use system date)
DELIMITER //
CREATE TRIGGER tr4
BEFORE INSERT ON car
FOR EACH ROW
BEGIN
DECLARE cur_date DATE;
SET cur_date = NOW();
44
IF NEW.dop > cur_date THEN
SET MESSAGE_TEXT = 'Incorrect date';
END IF;
END //
DELIMITER ;
45
LAB-PROGRAM-5
The puppy pet Shop wants to keep track of dogs

and their owners. The person can buy maximum
3 pet dogs. We store person's name, SSN and
address and dog's name, date of purchase and
gender. The owner of the pets dogs will be
iden)fied by SSN, since the dog's names are not
dis)nct.
ER-DIAGRAM
46
create table dog_owner
-> (ssn int,
-> owner_name varchar(25),
-> address varchar(25),
-> primary key(ssn));
mysql> desc dog_owner;
mysql> create table dog
-> (ssn int,
-> dname varchar(25),
-> dop date,
-> primary key(ssn,dname),
-> foreign key(ssn) references dog_owner(ssn));
mysql> desc dog;
47
mysql> insert into dog_owner values
-> (32581,'Nandu','RR Nagar');
-> (27845,'xyz','JP Nagar');
-> (89542,'Sachu','JayaNagar');
-> (51984,'Radika','Vijaya Nagar');
-> (65340,'Ramya','CSI Layout');
-> (54219,'Vamika','Bhadravathi');
mysql> select * from dog_owner;
48
mysql> insert into dog values
-> (32581,'Bunty','2017-03-02');
-> (89542,'Chinnu','2016-02-03');
-> (32581,'Zoozoo','2013-12-11');
-> (32581,'Pinky','2012-05-08');
-> (89542,'Charlie','2005-01-10');
-> (27845,'cooper','2011-10-12');
-> (27845,'twinkle','2010-10-10');
49
-> (27845,'teddy','2018-08-04');
mysql> select * from dog;
Queries for lab-program-5

1)List all persons who not owned a single pet
mysql> select o.owner_name
-> from dog_owner o
-> where not exists
-> (select *
-> from dog d
-> where o.ssn=d.ssn);
50
2)List all the pets owned by a person ‘xyz’
mysql> select d.dname,d.ssn,d.dop
-> from dog d,dog_owner o
-> where o.owner_name='xyz' AND
-> o.ssn=d.ssn;
Write a procedure to list all dogs and owner details purchased on the
specific date.
DELIMITER //
CREATE PROCEDURE pr4(IN pur_date DATE)
BEGIN
DECLARE v_name VARCHAR(25);
DECLARE v_ssn VARCHAR(25);
DECLARE v_address VARCHAR(25);
DECLARE v_rgno VARCHAR(25);
DECLARE v_model VARCHAR(25);
DECLARE v_color VARCHAR(25);
DECLARE done INT DEFAULT FALSE; -- Declara2on of done variable
DECLARE cur_owner CURSOR FOR
SELECT o.oname, o.ssn, o.address
DECLARE cur_car CURSOR FOR
51
SELECT c1.rno, c1.model, c1.color
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET done = FALSE;
OPEN cur_owner;
SELECT 'OWNER DETAILS';
read_loop_owner: LOOP
FETCH cur_owner INTO v_name, v_ssn, v_address;
IF done THEN
LEAVE read_loop_owner;
END IF;
SELECT CONCAT(v_name, ' ', v_ssn, ' ', v_address);
END LOOP;
CLOSE cur_owner;
SET done = FALSE;
OPEN cur_car;
SELECT 'CAR DETAILS';
read_loop_car: LOOP
FETCH cur_car INTO v_rgno, v_model, v_color;
IF done THEN
LEAVE read_loop_car;
END IF;
SELECT CONCAT(v_rgno, ' ', v_model, ' ', v_color);
END LOOP;
CLOSE cur_car;
END //
DELIMITER ;
52
Write a trigger to check the constraint that the person can buy maximum
three pet dogs
DELIMITER //
CREATE TRIGGER tr5
BEFORE INSERT ON dog
FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(*) INTO cnt
FROM dog_owner o, dog d
WHERE o.ssn = d.ssn AND o.ssn = NEW.ssn;
IF cnt > 3 THEN
53
SET MESSAGE_TEXT = 'Capacity of the owner crossed';
END IF;
END //
DELIMITER ;
54

Dbms Darshan Report

Uploaded by

Copyright:

Available Formats

Dbms Darshan Report

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Dbms Darshan Report

Uploaded by

Copyright:

Available Formats

Database Management Systems Lab Report

Submi(ed for the par2al fulﬁllment of Bachelor of Engineering

DARSHAN .S. (1SI21CS036)

Department of Computer Science and Engineering

(Program Accredited by NBA)

Siddaganga Ins6tute of Technology, Tumakuru – 572103

Accredited by NAAC with 'A++' grade & ISO 9001:2015 Cer2ﬁed)

create table crew

foreign key(sname) references movie_studio(sname));

create table studio_loca2on

foreign key(sname) references movie_studio(sname));

mysql> insert into movie_studio values

mysql> insert into movie_studio values

mysql> insert into movie_studio values

mysql> insert into movie_studio values

mysql> insert into movie_studio values

mysql> select * from movie_studio;

insert into crew values

mysql> insert into crew values

mysql> insert into crew values

mysql> select * from crew;

insert into studio_loca2on values

-> ('Gayatri','BH road');

mysql> insert into studio_loca2on values

-> ('Maruthi','MG road');

mysql> insert into studio_loca2on values

-> ('ionax','JP Nagar');

Query OK, 1 row aﬀected (0.01 sec)

mysql> insert into studio_loca2on values

-> ('Krishna','Vijay Nagar');

mysql> insert into studio_loca2on values

-> ('Rockline','RR Nagar');

mysql> insert into studio_loca2on values

-> ('Narthaki','Jaya Nagar');

-> where sname not in

-> (select sname from crew);

2)Reterieve the movie studio which uses highest strength crew

-> where strength>=ALL

-> (select strength from crew);

CREATE PROCEDURE display_crew(IN sname VARCHAR(25))

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE crew_no_var INT;

DECLARE strength_var INT;

DECLARE sector_var VARCHAR(25);

DECLARE c CURSOR FOR

JOIN movie_studio m ON c1.sname=m.sname

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

FETCH c INTO crew_no_var,strength_var,sector_var;

SELECT CONCAT(crew_no_var,' ',strength_var,' ',sector_var) AS result;

CREATE TRIGGER max_crew

BEFORE INSERT ON crew

FOR EACH ROW

DECLARE cnt INT;

SELECT COUNT(*) INTO cnt FROM crew

WHERE sname = NEW.sname;

IF cnt > 5 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'MAX_reached';