VISVESVARAYA TECHNOLOGICAL

UNIVERSITY
“JnanaSangama”, Belgaum -590014, Karnataka.

LAB REPORT
on

Database Management Systems (23CS3PCDBM)

Submitted by
Mahmad Shahabaz (1BM25CS475-T)

in partial fulfillment for the award of the degree of

BACHELOR OF ENGINEERING
in
COMPUTER SCIENCE AND ENGINEERING

B.M.S. COLLEGE OF ENGINEERING

(Autonomous Institution under VTU)
BENGALURU-560019
Sep-2025 to Jan-2026
1
 B. M. S. College of Engineering,
Bull Temple Road, Bangalore 560019
(Affiliated To Visvesvaraya Technological University, Belgaum)
Department of Computer Science and Engineering

CERTIFICATE

This is to certify that the Lab work entitled “Database Management Systems (23CS3PCDBM)”
carried out by Mahmad Shahabaz (1BM25CS475-T), who is bonafide student of B. M. S.
College of Engineering. It is in partial fulfillment for the award of Bachelor of Engineering in
Computer Science and Engineering of the Visvesvaraya Technological University, Belgaum
during the year 2022. The Lab report has been approved as it satisfies the academic requirements
in respect of a Database Management Systems (23CS3PCDBM) work prescribed for the said
degree.

Amruta B Mam Dr. Kavitha Sooda

Lab faculty Incharge Professor & HOD
Department of CSE, BMSCE Department of CSE, BMSCE

2
 Index
Sl. Date Experiment Title Page No.
No.
1 28-09-25 Insurance Database

2 10-10-25 More Queries on Insurance Database

3 17-10-25 Bank Database

4 24-10-25 More Queries on Bank Database

5 31-10-25 Employee Database

6 7-11-25 More Queries on Employee Database

7 14-11-25 Supplier Database

8 14-11-25 More Queries on Supplier

9 21-11-25 NOSQL Installation in Cloud

10 28-11-25 NO SQL - Student Database

11 5-12-25 NO SQL - Customer Database

12 12-12-25 NO SQL – Restaurant Database

13 12-12-25 LeetCode Practice Problem I

14 12-12-25 LeetCode Practice Problem II

15 12-12-25 LeetCode Practice Problem III

3
 Experiment 1: Insurance Database

Specification of Insurance Database Application

The insurance database must maintain information about drivers, the cars they own, the accidents
reported, and the participation of each driver and car in those accidents. Each driver in the system is
uniquely identified by a driver ID, along with their name and address, and each car is uniquely
identified by its registration number together with details such as model and manufacturing year. The
system must allow storing ownership information that links a driver to one or more cars, while also
allowing a car to be linked to one or more drivers if shared ownership occurs; duplicate ownership
records for the same driver and car must not exist. Accident information must be stored using a
unique report number assigned to each accident, along with the date on which the accident occurred
and the location where it happened. Every accident reported in the system must have at least one
participating driver and car, and this participation is recorded by linking the driver, the involved car,
and the accident report together with the corresponding damage amount for that particular
involvement. A participation record must reference an existing driver, an existing car, and an existing
accident, and no two participation entries may repeat the same combination of driver, car, and
accident report. The database must ensure that damage amounts are non-negative, accident dates are
valid calendar dates, and car manufacturing years fall within reasonable limits. It must also preserve
referential integrity so that ownership or participation entries cannot exist without valid driver, car,
and accident information already present in the system. Deletion policies must prevent removal of
drivers or cars that appear in past accident participation records unless historical consistency is
preserved through controlled deletion rules or archival mechanisms. The system should maintain
accurate links between drivers, cars, and accidents at all times, ensuring reliable retrieval of
ownership histories, accident histories, and damage information for administrative, legal, and
insurance-related purposes.

4
Entity Relationship Diagram

5
Schema Diagram

- PERSON (driver_id: String, name: String, address: String)

- CAR (reg_num: String, model: String, year: int)
- ACCIDENT (report_num: int, accident_date: date, location: String)
- OWNS (driver_id: String, reg_num: String)
- PARTICIPATED (driver_id: String,reg_num: String, report_num: int, damage_amount:
int)
6
 - Create the above tables by properly specifying the primary keys and the foreign keys. -
Enter at least five tuples for each relation

Create database
create database test;
use test;

Create table

create table person(

driver_id varchar(10) primary key,
name varchar(20),
adress varchar(30));

create table car(

reg_num varchar(10) primary key,
model varchar(10),
year int);

create table accident(

report_num int primary key,
accident_date date,
location varchar(20) );

create table owns(driver_id varchar(10),

reg_num varchar(10),
primary key(driver_id, reg_num),
foreign key(driver_id) references person(driver_id),
foreign key(reg_num) references car(reg_num));
7
create table participated(driver_id varchar(10), reg_num varchar(10), report_num int,
damage_amount int,
primary key(driver_id, reg_num, report_num),
foreign key(driver_id) references person(driver_id), foreign key(reg_num) references car(reg_num),
foreign key(report_num) references accident(report_num));

Structure of the table

desc person;

desc

accident;

desc participated;

desc

participated;

desc owns;

8
Inserting Values to the table

insert into person values("A01","richard","srinivas nagar"),

("A02","pradeep","rajaji nagar"),

("A03","smith","ashok nagar"),

("A04","venu","nk colony"),

("A05","john","hanuman nagar");

select * from person;

insert into car values("KA052250","indica",1990),

("KA052251","toyota",1957),

("KA052252","honda",1998),

("KA052253","audi",2008), ("KA052254","lambo",2005);

select * from car;

9
insert into owns values("A01","KA052250"),

("A02","KA052251"),

("A03","KA052252"),

("A04","KA052253"),

("A05","KA052254");

select * from owns;

INSERT INTO accident VALUES(11, '2003-01-01', 'Mysore Road'), (12,

'2004-02-02', 'South end Circle'),

(13, '2003-01-21', 'Bull temple Road'),

(14, '2008-02-17', 'Mysore Road'),

(15, '2005-03-04', 'Kanakpura Road');
select*from accident;

INSERT INTO participated VALUES('A01', 'KA052250', 11, 10000), ('A02',

10
'KA052251', 12, 20000),

('A03', 'KA052252', 13, 25000),

('A04', 'KA052253', 14, 30000),

('A05', 'KA052254', 15, 50000);

select*from participated;

Queries :
update participated set damage_amount=25000 where report_num=12; insert into

accident(report_num, accident_date, location)

values (16,'2008-03-15','Domlur');

select * from car order by year asc;

select count(*) CNT from car c,participated p where c.reg_num=p.reg_num and model="audi";

select count( driver_id) CNT from participated a,accident b where a.report_num=b.report_num and

year(b.accident_date)=2003;
11
select * from participated order by damage_amount desc;

Experiment 2: More Queries on Insurance Database

Queries
i. Display the entire CAR relation in the ascending order of manufacturing year.
SELECT * FROM car ORDER BY manuf_year;

ii. Find the number of accidents in which cars belonging to a specific model (example 'Lancer')
were involved.
SELECT COUNT(*)
FROM participated p
JOIN car c ON p.reg_num = c.reg_num
WHERE [Link] = 'Lancer';

12
 iii. Find the total number of people who owned cars that involved in accidents in 2008.
SELECT COUNT(DISTINCT o.driver_id)
FROM owns o
JOIN participated p ON o.reg_num = p.reg_num
JOIN accident a ON p.report_num = a.report_num
WHERE YEAR(a.accident_date) = 2008;

iv. List the entire participated relation in the Descending Order of Damage Amount.
SELECT * FROM participated ORDER BY damage_amount DESC;

v. Find the Average Damage Amount. vi. Delete the tuple whose Damage Amount is below the
Average Damage Amount
SELECT AVG(damage_amount) FROM participated;

vi. List the name of drivers whose Damage is Greater than the Average Damage Amount.
SELECT DISTINCT [Link]
FROM person pe
JOIN participated pa ON pe.driver_id = pa.driver_id
WHERE pa.damage_amount >
(SELECT AVG(damage_amount) FROM participated);

13
vii. Find Maximum Damage Amount.
SELECT MAX(damage_amount) FROM participated;

Experiment 3: BANKING ENTERPRISE DATABASE

Specification of Banking Enterprise Database

The banking system must store information about branches, bank accounts, customers, deposit relationships,
and loans so that branch details (identified by branch name together with city and total assets) are linked to
accounts and loans, each account (identified by an account number) records the branch it belongs to and the
current balance, customers are recorded with their name, street and city, and a depositor relationship associates
a customer with an account; loans are recorded by a unique loan number together with the branch name that
issued the loan and the loan amount. Account numbers and loan numbers must be unique identifiers, branch
names are used to associate accounts and loans to a branch, and customer names (as modeled) are used to
identify customers referenced by depositor entries; every depositor entry must reference an existing customer
and an existing account so that ownership and access relationships are always valid, and duplicate depositor
records linking the same customer and account are disallowed. The system must maintain referential integrity
so accounts cannot reference a non-existent branch, depositor rows cannot reference missing customers or
accounts, and loans must reference an existing branch; deletion of a branch, account, or customer that is
referenced by dependent records should be controlled (either disallowed or handled by archival/controlled
reassignment) to preserve historical transaction and loan consistency. Numeric and temporal constraints must

14
be enforced: account balances should be constrained to valid values (for example non-negative where
overdraft is not allowed), branch assets and loan amounts must be non-negative and within specified business
limits, and updates to balance or loan amounts should be auditable. Cardinality rules implied by the schema
are enforced: a branch may host many accounts and issue many loans, an account belongs to exactly one
branch, a customer may be linked to many accounts through depositor relationships, and an account may have
many depositors if joint accounts are permitted by policy. Implementation must prevent orphaned records,
ensure uniqueness where required, and rely on application logic or database-level triggers to enforce complex
rules such as cascading effects on deletion, business rules about allowed balance operations or overdrafts, and
any required validation when transferring accounts between branches or when converting a customer’s
identifying details; the database should thus reliably support queries for branch-wise account lists, customer
account ownership, account balances, and loan portfolios while preserving historical and referential integrity
for auditing and regulatory reporting.

Entity Relationship Diagram

15
Schema Diagram

16
 ● Branch (branch-name: String, branch-city: String, assets: real)
● BankAccount(accno: int, branch-name: String, balance: real)
● BankCustomer (customer-name: String, customer-street: String, customer-city:
String)
● Depositer(customer-name: String, accno: int)
● LOAN (loan-number: int, branch-name: String, amount: real)
● Create the above tables by properly specifying the primary keys and the foreign
keys. - Enter at least five tuples for each relation
Create database
create database Bank_db;
use Bank_db;

Create table
17
create table branch(

b_name varchar(40) primary key,

b_city varchar(40),

assets int);

create table Bankaccount (

accno int primary key,

b_name varchar(45),

balance int,

foreign key (b_name) references Branch (b_name));

create table BankCustomer (

cus_name varchar(45) primary key,

cus_street varchar(45),

city varchar(45));

create table depositor (

cus_name varchar(45),

accno int ,

primary key(cus_name,accno),

foreign key (accno) references BankAccount (accno), foreign key(cus_name)

references bankcustomer(cus_name));

create table loan(

loan_no int primary key,

b_name varchar(45),

amt int,

foreign key (b_name) references branch (b_name));

Structure of the table

18
desc branch;

desc bankaccount;

desc BankCustomer;

DESC depositor;

desc loan;

Inserting Values to the table

INSERT INTO Branch VALUES

('SBI_Chamrajpet', 'Blr', 500000),

('SBI_ResidencyRoad', 'Blr', 100000),

('SBI_ShivajiRoad', 'Bombay', 200000),

('SBI_ParlimentRoad', 'Delhi', 100000),

('SBI_Jantarmantar', 'Delhi', 200000); select * from branch;

19
INSERT INTO BankCustomer VALUES ('Avinash',
'Bull_Temple_Road', 'Bangalore'), ('Dinesh', 'Bannerghatta_Road',
'Bangalore'), ('Mohan', 'NationalCollege_Road', 'Bangalore'), ('Nikil',
'Akbar_Road', 'Delhi'),

('Ravi', 'Prithviraj_Road', 'Delhi');

select * from bankcustomer;

INSERT INTO BankAccount VALUES

(1, 'SBI_Chamrajpet', 2000),

(2, 'SBI_ResidencyRoad', 5000),

(3, 'SBI_ShivajiRoad', 6000),

(4, 'SBI_ParlimentRoad', 9000),

(5, 'SBI_Jantarmantar', 8000),

(6, 'SBI_ShivajiRoad', 4000),

(8, 'SBI_ResidencyRoad', 4000),

(9, 'SBI_ParlimentRoad', 3000),

(10, 'SBI_ResidencyRoad', 5000), (11,
'SBI_Jantarmantar', 2000); select * from bankaccount;

20
INSERT INTO depositor VALUES ('Avinash', 1),('Dinesh',
2),('Nikil', 4), ('Ravi', 5),('Avinash', 8),

('Nikil', 9),('Dinesh', 10),('Nikil', 11); select * from

depositor;

INSERT INTO loan VALUES

(1, 'SBI_Chamrajpet', 1000), (2,

'SBI_ResidencyRoad', 2000), (3, 'SBI_ShivajiRoad',
3000), (4, 'SBI_ParlimentRoad', 3000), (5,
'SBI_Jantarmantar', 5000); select * from loan;

Queries :
iii. Display the branch name and assets from all branches in lakhs of rupees and rename the assets
column to 'assets in lakhs'.
21
select b_name as 'Branch_Name',assets/100000.0 as 'assets_in_lakhs' from branch;

iv. Find all the customers who have at least two accounts at the same branch (ex.
SBI_ResidencyRoad).

select d.cus_name,b.b_name,count(*) as num_accounts

from depositor d, bankaccount b
where [Link] = [Link]
group by d.cus_name,b.b_name
having count(*)>=2;

v. Create a view which gives each branch the sum of the amount of all the loans at the branch.
CREATE VIEW Summary AS

SELECT b_name,SUM(amt) AS total_loan_amount

FROM loan

GROUP BY b_name;

select * from summary;

22
 Experiment 4: More Queries on Bank Database

Queries
i. Find all the customers who have an account at all the branches located in a specific city (Ex.
Delhi).
select d.cus_name

from depositor d,bankaccount b

where [Link]=[Link] and b_name in (select b_name from branch where b_city='Delhi') group by
d.cus_name;

ii. Find all customers who have both an account and a loan at the Bangalore branch

SELECT DISTINCT d.cus_name

FROM depositor d,bankaccount b,loan l

where [Link] = [Link] and b.b_name = l.b_name

and b.b_name IN (

SELECT b_name FROM branch WHERE b_city = 'Blr'

);

iv. Find the names of all branches that have greater assets than all branches located in
Bangalore.

SELECT b_name

23
FROM branch

WHERE assets > ALL (

SELECT assets

FROM branch

WHERE b_city = 'Blr'

);

v. Demonstrate how you delete all account tuples at every branch located in a specific city (Ex.
Bombay).

DELETE FROM BankAccount

WHERE b_name IN (
SELECT b_name
FROM branch
WHERE b_city = 'Bombay'
);

vi. Update the Balance of all accounts by 5%

SET SQL_SAFE_UPDATES = 0;

UPDATE BankAccount
SET balance = balance + (balance * 0.05);

SET SQL_SAFE_UPDATES = 1;

Experiment 5 : EMPLOYEE DATABASE

24
Specification of employee Database

The employee database must record each employee’s identifying number, name, manager reference, hire date,
salary, and department affiliation while also tracking departmental details, project assignments (including the
role an employee plays on a project), and any incentive payments given to employees. Every employee is
represented by a unique employee number and has a hire date and salary that must be valid; the manager field
is a self-referencing link that must, if present, point to an existing employee and must never create a circular
management chain or reference the employee themself. Departments are identified by a unique department
number and include a department name and location; every department referenced by an employee or by other
structures must exist in the department table, and departments may contain zero or many employees. Projects
are recorded with a unique project number, project name and project location; employees may be assigned to
multiple projects and each project may have many employees, with each assignment carrying the employee’s
job role for that project — duplicate assignments of the same employee to the same project are disallowed.
Incentive payments are recorded with the employee reference, the incentive date and the incentive amount; an
incentive entry must reference an existing employee and incentive amounts must be non-negative and dated on
or after the employee’s hire date. Referential integrity must be enforced so that employee records cannot
reference non-existent departments, projects, or managers, and assignment and incentive records cannot exist
without corresponding employee, project, or department records as appropriate. Salary, incentive amounts, and
any monetary fields must be constrained to valid numeric ranges and hire/ incentive dates must be valid
calendar dates (and typically not future-dated unless business rules permit). Deletion and update policies must
preserve historical consistency: deleting an employee who appears as a manager, as a project assignee, or in
incentive records should be prevented or should be handled via controlled archival, reassignment, or soft-
delete flags rather than hard deletion to preserve audit trails; similarly, changing a department or project
identifier must either be disallowed if it would orphan historical records or handled by introducing immutable
surrogate keys. Business rules include preventing circular manager chains, ensuring an employee’s manager (if
specified) cannot be the employee themself, disallowing duplicate project-assignments, requiring that
incentive dates fall within the employee’s employment window, and optionally requiring at least one project
assignment or at least one incentive record depending on policy for reporting. Implementation should use
primary-key and foreign-key constraints for identity and linkage, unique constraints to prevent duplicate
assignments, check constraints for monetary and date ranges, and application logic or triggers for complex
temporal or graph constraints (like cycle detection in management relationships and enforcing non-overlap or
other schedule-related rules if assignments gain temporal attributes later). The system must therefore reliably
support queries such as employee reporting lines, department staffing lists, project rosters with job roles,
incentive payment histories, salary analyses, and audit reports while maintaining data integrity, preventing
inconsistent deletions, and preserving a complete historical record for HR and compliance needs.
25
Entity Relationship Diagram

26
Schema Diagram

● DEPT (deptno: int, dname: String, dloc: String)

● EMPLOYEE (empno: int, ename: String, mgr_no: int, hiredate: date, sal: real,
deptno: int)
● PROJECT (pno: int, ploc: String, pname: String)
● ASSIGNED_TO (empno: int, pno: int, job_role: String)
● INCENTIVES (empno: int, incendate: date, incenamt: real)
Create database
create database Emp_db;
use Emp_db;

Create table
CREATE TABLE DEPT (

DEPTNO INT PRIMARY KEY,

DNAME VARCHAR(20),

DLOC VARCHAR(20)

);

CREATE TABLE EMPLOYEE (

EMPNO INT PRIMARY KEY,

ENAME VARCHAR(20),

27
 MGR_NO INT,

HIREDATE DATE,

SAL INT,

DEPTNO INT,

FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

);

CREATE TABLE PROJECT (

PNO INT PRIMARY KEY,

PLOC VARCHAR(50),

PNAME VARCHAR(50)

);

CREATE TABLE ASSIGNED_TO (

EMPNO INT,

PNO INT,

JOB_ROLE VARCHAR(50),

FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE(EMPNO),

FOREIGN KEY (PNO) REFERENCES PROJECT(PNO)

);

CREATE TABLE INCENTIVES (

EMPNO INT,

INCENDATE DATE,

INCENAMT DECIMAL(10,2),

FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE(EMPNO)

);

Structure of the table

desc dept;

d
esc employee;
28
desc projectr;

DESC assigned_tor;

desc incentives;

Inserting Values to the table

INSERT INTO DEPT VALUES

(1, 'HR', 'nkl'),
(2, 'IT', 'Blr'),
(3, 'Fin', 'Hyd'),
(4, 'Market', 'yadgir'),
(5, 'ORG', 'gulbarga'),
(6, 'Admin', 'Pune');

29
SELECT * FROM DEPT;

INSERT INTO EMPLOYEE VALUES

(101, 'panda', 0, '2020-01-15', 50500, 1),
(102, 'cutie', 101, '2021-03-12', 67000, 2),
(103, 'shahabaz', 101, '2021-06-25', 55000, 3),
(104, 'parvez', 102, '2022-01-09', 45000, 2),
(105, 'muskan', 103, '2022-11-18', 60000, 4),
(106, 'yaar', 101, '2023-02-10', 58000, 5);
SELECT * FROM EMPLOYEE;

INSERT INTO PROJECT VALUES

(10, 'nkl', 'tech'),
(20, 'Blr', 'gogappa'),
(30, 'Hyd', 'hack'),
(40, 'yadgir', 'sharmili'),
(50, 'gulbarga', 'naraj'),
(60, 'Pune', 'khafa');
SELECT * FROM PROJECT;

30
INSERT INTO ASSIGNED_TO VALUES
(101, 10, 'Leader'),
(102, 10, 'Developer'),
(103, 20, 'Analyst'),
(104, 30, 'Tester'),
(105, 40, 'Designer'),
(106, 50, 'Co-ordinator'),
(103, 60, 'Engineer');
SELECT * FROM ASSIGNED_TO;

INSERT INTO INCENTIVES VALUES

(101, '2023-12-31', 5000),
(102, '2024-01-10', 3000),
(104, '2024-05-20', 2000),
(105, '2024-07-30', 2500),
(106, '2024-09-10', 3500);
SELECT * FROM INCENTIVES;

31
Queries :
iii. Retrieve the employee numbers of all employees who work on project located in
Bengaluru, Hyderabad, or Mysuru

SELECT DISTINCT [Link]

FROM ASSIGNED_TO a
JOIN PROJECT p ON [Link] = [Link]
WHERE [Link] IN ('Blr','Hyd','Mysuru');

iv. Get Employee ID’s of those employees who didn’t receive incentives

SELECT [Link]
FROM EMPLOYEE e
LEFT JOIN INCENTIVES i ON [Link] = [Link]
WHERE [Link] IS NULL;

v. Write a SQL query to find the employees name, number, dept, job_role, department
location and project location who are working for a project location same as his/her
department location.

SELECT [Link], [Link], [Link], a.JOB_ROLE,

[Link] AS Dept_Location, [Link] AS Project_Location
FROM EMPLOYEE e
JOIN DEPT d ON [Link] = [Link]
JOIN ASSIGNED_TO a ON [Link] = [Link]
JOIN PROJECT p ON [Link] = [Link]
WHERE [Link] = [Link];

32
Experiment 6: More Queries on Employee Database

Queries
i. List the name of the managers with the maximum employees

SELECT [Link]
FROM EMPLOYEE m
JOIN EMPLOYEE e ON [Link] = e.MGR_NO
GROUP BY [Link], [Link]
HAVING COUNT(*) = (
SELECT MAX(cnt)
FROM (
SELECT COUNT(*) cnt
FROM EMPLOYEE
WHERE MGR_NO IS NOT NULL
GROUP BY MGR_NO
)x
);

ii. Display those managers name whose salary is more than average salary of his employee.

SELECT [Link]
FROM EMPLOYEE m
WHERE [Link] > (
SELECT AVG([Link])
FROM EMPLOYEE e
WHERE e.MGR_NO = [Link]
);

33
iii. Find the name of the second top level managers of each department.

SELECT DISTINCT [Link], [Link]

FROM EMPLOYEE e
JOIN DEPT d ON [Link] = [Link]
WHERE e.MGR_NO IN (
SELECT EMPNO
FROM EMPLOYEE
WHERE MGR_NO IS NULL
);

iv. Find the employee details who got second maximum incentive in January 2019.

SELECT e.*
FROM EMPLOYEE e
JOIN INCENTIVES i ON [Link] = [Link]
WHERE MONTH([Link])=1
AND YEAR([Link])=2019
AND [Link] = (
SELECT MAX(INCENAMT)
FROM INCENTIVES
WHERE INCENAMT < (
SELECT MAX(INCENAMT)
FROM INCENTIVES
WHERE MONTH(INCENDATE)=1 AND YEAR(INCENDATE)=2019
)
);

v. Display those employees who are working in the same department where his manager is
working.
SELECT e.*
FROM EMPLOYEE e
JOIN EMPLOYEE m ON e.MGR_NO = [Link]
WHERE [Link] = [Link];

34
Experiment 7 : SUPPLIER DATABASE

Specification of Supplier Database

The supplier database must store information about suppliers, the parts they provide, and the prices at
which each part is offered so that purchasing, analysis, and reporting can be done accurately. Each supplier is
uniquely identified by a supplier ID and is recorded with a name and the city in which the supplier is located;
each part is uniquely identified by a part ID and includes a part name and a color. The system must maintain
a catalog that links suppliers to the parts they supply and records the cost at which a given supplier sells a
given part. Every catalog entry must reference an existing supplier and an existing part, and there must be no
duplicate entries for the same combination of supplier and part, so that at most one current price record
exists per supplier–part pair. Costs must be valid numeric values and strictly non-negative, and business rules
may specify upper limits or currency formats that must be enforced consistently. The data model must
support the possibility that a supplier can provide many different parts, that a part can be supplied by many
different suppliers, and that some suppliers or parts may temporarily have no catalog entries if they are
inactive or not currently traded. Referential integrity must be enforced so that a supplier or part cannot be
deleted while still referenced in the catalog unless such deletion is handled by controlled archival or cascade
rules that preserve historical price information; in general, historical catalog data should not be lost, as it may
be required for audits or trend analysis. The system should allow queries such as “find all suppliers for a given
part,” “list all parts provided by a given supplier,” “retrieve the cheapest supplier for each part,” and “analyze
supplier coverage by city,” and must therefore guarantee that identifiers are unique, relationships between
suppliers, parts, and catalog entries are consistent, and price information is accurate and reliably maintained
over time.

35
Entity Relationship Diagram

36
Schema Diagram

● Supplier (sid: int, sname: String, city: String)

● Parts (pid: int, pname: String, color: String)
● Catalog (sid: int, pid: int, cost: int)
Create database
create database SUP ;
use SUP;

Create table
CREATE TABLE Supplier (
sid INT PRIMARY KEY,
sname VARCHAR(20),
city VARCHAR(20)
);

CREATE TABLE Parts (

pid INT PRIMARY KEY,
pname VARCHAR(20),
color VARCHAR(20)
);
37
CREATE TABLE Catalog (
sid INT,
pid INT,
cost int,
PRIMARY KEY (sid, pid),
FOREIGN KEY (sid) REFERENCES Supplier(sid), FOREIGN KEY (pid)
REFERENCES Parts(pid) );

Structure of the table

desc supplier;

desc
parts;

desc catalog;

Inserting Values to the table

INSERT INTO Supplier VALUES
(1, 'Big mom', 'Bangalore'),
(2, 'Kaido', 'Chennai'),
(3, 'Shanks', 'Delhi');
select * from supplier;

38
INSERT INTO Parts VALUES
(101, 'bankai', 'Red'),
(102, 'shikai', 'Green'),
(103, 'quincy', 'Red'),
(104, 'espada', 'Blue');
select * from parts;

INSERT INTO Catalog VALUES (1, 101, 50),

(1, 102, 40),
(2, 101, 45),
(2, 103, 60),
(3, 102, 38),
(3, 104, 70);
select * from catalog;

39
Queries :
[Link] the pnames of parts for which there is some supplier.

SELECT DISTINCT [Link] FROM Parts p

JOIN Catalog c ON [Link] = [Link];

ii. Find the snames of suppliers who supply every part.

SELECT [Link]
FROM Supplier s
JOIN Catalog c ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT(*) FROM Parts);

v. Find the snames of suppliers who supply every red part.

40
SELECT [Link]
FROM Supplier s
JOIN Catalog c ON [Link] = [Link]
JOIN Parts p ON [Link] = [Link]
WHERE [Link] = 'Red'
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = (
SELECT COUNT(*) FROM Parts WHERE color = 'Red' );

vi. Find the pnames of parts supplied by Acme Widget Suppliers and by no one else.

SELECT [Link]
FROM Parts p
JOIN Catalog c ON [Link] = [Link]
JOIN Supplier s ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = 1
AND MAX([Link]) = 'Acme Widget Suppliers';

vii. Find the sids of suppliers who charge more for some part than the average cost of that
part (averaged over all the suppliers who supply that part).

SELECT DISTINCT [Link]

FROM Catalog c1
WHERE [Link] > (
SELECT AVG([Link])
FROM Catalog c2
WHERE [Link] = [Link]
);

viii. For each part, find the sname of the supplier who charges the most for that part.

SELECT [Link], [Link] FROM Parts p

JOIN Catalog c ON [Link] = [Link] JOIN Supplier s ON [Link] =
[Link] WHERE [Link] = (
41
 SELECT MAX([Link])
FROM Catalog c2
WHERE [Link] = [Link]
);

Experiment 8:NOSQL INSTALLATION IN CLOUD

MongoDB Installation – Summary Notes

Supported

● 64-bit systems only

● x86_64, arm64, ppc64le, s390x

● Windows 11 / Server 2022

● Ubuntu 20.04, 22.04, 24.04

● RHEL / Rocky / Alma 8 & 9

● macOS 12+

▶ Ubuntu (Community Edition)

sudo apt update

sudo apt install -y mongodb-org

42
sudo systemctl start mongod

sudo systemctl enable mongod

▶ Windows

1. Download MongoDB Community Server (.msi)

2. Run installer

3. Select Install MongoDB as a Service

4.
Finish MongoDB runs automatically

43
Experiment 9: NOSQL STUDENT DATABASE

Create database and collection

use student;
switched to db student
[Link]("student_details");

Inserting Values to the collection

{ Rollno: 10, Name: "shahabaz", Age: 20, ContactNo:
9900454575, EmailId: "shahabaz@[Link]" },
{ Rollno: 11, Name: "cutie", Age: 21, ContactNo:
746575123, EmailId: "cutie@[Link]" },
{ Rollno: 12, Name: "riyaz", Age: 22, ContactNo:
7406000473, EmailId: "riyaz@[Link]" },
{ Rollno: 13, Name: "panda", Age: 20, ContactNo:
94670004, EmailId: "panda@[Link]" },
{ Rollno: 14, Name: "muskan", Age: 23, ContactNo:
9954758505, EmailId: "munsakn@[Link]" }
44
]);

Structure of collection:

45
Queries :
46
i. Write query to update Email-Id of a student with rollno 10
db.student_details.updateOne(
{ Rollno: 10 },
{ $set: { EmailId: "newmail@[Link]" } }
)

iv. Replace the student name from “ABC” to “FEM” of rollno 11.

db.student_details.updateOne(
{ Rollno: 11 },
{ $set: { Name: "FEM" } }
)

vi. Drop the table.

db.student_details.drop()

Experiment 10 : NOSQL CUSTOMER DATABASE

47
Create database and collection

Inserting Values to the collection

[Link]([
{ Cust_id: 1, Acc_Bal: 1000, Acc_Type: "C" },
{ Cust_id: 1, Acc_Bal: 2000, Acc_Type: "C" },
{ Cust_id: 2, Acc_Bal: 200, Acc_Type: "C" },
{ Cust_id: 2, Acc_Bal: 5000, Acc_Type: "D" },
{ Cust_id: 3, Acc_Bal: 1500, Acc_Type: "D" },
{ Cust_id: 3, Acc_Bal: 800, Acc_Type: "C" },
{ Cust_id: 4, Acc_Bal: 2500, Acc_Type: "C" }
])

Structure of collection:

48
Queries :
49
i. Write a query to display those records whose total account balance is greater than 1200 of
account type ‘C’ for each customer_id.

[Link]([
{ $match: { Acc_Type: "C" } },
{ $group: { _id: "$Cust_id", Total_Balance: { $sum: "$Acc_Bal" } } },
{ $match: { Total_Balance: { $gt: 1200 } } }
])

[Link] Minimum and Maximum account balance for each customer_id.

[Link]([
{
$group: {
_id: "$Cust_id",
Min_Balance: { $min: "$Acc_Bal" },
Max_Balance: { $max: "$Acc_Bal" }
}
}
])

50
vi. Drop the table.
[Link]()

Experiment 11: NOSQL RESTAURANT DATABASE

51
Create database and collection

Inserting Values to the collection

[Link]([
{
restaurant_id: "R101",
name: "Spicy Dragon",
town: "Bangalore",
cuisine: "Chinese",
grades: [ { score: 12 }, { score: 8 } ] },
{
restaurant_id: "R102",
name: "Burger Maharaja",
town: "Delhi",
cuisine: "Fast Food",
grades: [ { score: 9 }, { score: 7 } ] },
{
restaurant_id: "R103",
name: "Curry Kingdom",
town: "Mumbai",
cuisine: "Indian",
grades: [ { score: 15 }, { score: 14 } ] },
{
restaurant_id: "R104",
name: "Pasta Palace",
town: "Bangalore",
cuisine: "Italian",
grades: [ { score: 6 }, { score: 10 } ] },
{
restaurant_id: "R105",
name: "Dosa Junction",
town: "Chennai",
cuisine: "South Indian",
grades: [ { score: 11 }, { score: 9 } ] }
])
Structure of collection:

52
Queries :
53
i. Write a MongoDB query to arrange the name of the restaurants in descending along with all
the columns.

[Link]().sort({ name: -1 })

[Link] a MongoDB query to find the restaurant Id, name, town and cuisine for those
restaurants which achieved a score which is not more than 10.

[Link](
{ "[Link]": { $lte: 10 } },
{ _id: 0, restaurant_id: 1, name: 1, borough: 1, cuisine: 1 }
)

vi. Write a MongoDB query to find the average score for each restaurant.
[Link]([
54
 { $unwind: "$grades" },
{
$group: {
_id: "$restaurant_id",
name: { $first: "$name" },
avgScore: { $avg: "$[Link]" }
}
}
])

Experiment 11 : LeetCode Practice -I

55
181 – Employees Earning More Than Their Managers
Q) Find employees whose salary > their manager’s salary.

Experiment 12 : LEETCODE PRACTICE

56
1075 – Project Employees I
Q) For each project, find the average experience (years) of employees working on it.

Table: Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+

Table: Employee
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+

Experiment 13 : LEETCODE PRACTICE

57
1148 – Article Views I
Q)Find authors who viewed their own articles.

Table: Views
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+

58