VISVESVARAYA TECHNOLOGICAL

UNIVERSITY
“JnanaSangama”, Belgaum – 590014, Karnataka

LAB REPORT
On

Database Management Systems (23CS3PCDBM)

Submitted by
Shriram S Patil (1BM24CS278)

in partial fulfilment 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
 B. M. S COLLEGE OF ENGINEERING
Bull Temple Road, Bengaluru - 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 Shriram S Patil (1BM24CS278), who is bonafide
student of B. M. S. College of Engineering. It is in partial fulfilment for the award of
Bachelor of Engineering in Computer Science and Engineering of the Visvesvaraya
Technological University, Belgaum during the year 2025-26. 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.

Saritha A. N Dr. Kavitha Sooda

Assistant Professor Professor & HOD
Department of CSE, BMSCE Department of CSE, BMSCE
 INDEX
Sl.
Date Experiment Title Page No.
No.
1 26/09/2025 Insurance Database 1

2 10/10/2025 More Queries on Insurance Database 7

3 17/10/2025 Bank Database 11

4 24/10/2025 More Queries on Bank Database 17

5 31/10/2025 Employee Database 20

6 07/11/2025 More Queries on Employee Database 26

7 14/11/2025 Supplier Database 30

8 21/11/2025 More Queries on Supplier Database 35

9 21/11/2025 NoSQL Installation in Cloud (MongoDB Atlas) 37

10 28/11/2025 NoSQL – Student Database 38

11 05/12/2025 NoSQL – Customer Database 41

12 12/12/2025 NoSQL – Restaurant Database 44

13 12/12/2025 LeetCode Practice – I 51

14 12/12/2025 LeetCode Practice – II 52

15 12/12/2025 LeetCode Practice – III 54

 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.

1
Entity – Relationship Diagram:

2
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)
1. Create the above tables by properly specifying the primary keys and the foreign keys.
2. Enter at least five tuples for each relation.

Create Database:
CREATE DATABASE insurance;
USE insurance;

Create Table:
CREATE TABLE person (
driver_id VARCHAR(10) PRIMARY KEY,
name VARCHAR(50),
address VARCHAR(100)
);

CREATE TABLE car (

reg_num VARCHAR(10) PRIMARY KEY,
model VARCHAR(20),
year INT );
CREATE TABLE accident (
3
 report_num INT PRIMARY KEY,
accident_date DATE,
location VARCHAR(100)
);

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)
);

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 Tables:

desc person;

desc car;

desc accident;

desc owns;

4
 desc participated;

Inserting Values into the Tables:

INSERT INTO person (driver_id, name, address) VALUES
('A01', 'Richard', 'Srinivas nagar'),
('A02', 'Pradeep', 'Rajaji nagar'),
('A03', 'Smith', 'Ashok nagar'),
('A04', 'Venu', 'N R Colony'),
('A05', 'Jhon', 'Hanumanth nagar');

INSERT INTO car (reg_num, model, year) VALUES

('KA052250', 'Indica', 1990),
('KA031181', 'Lancer', 1957),
('KA095477', 'Toyota', 1998),
('KA053408', 'Honda', 2008),
('KA041702', 'Audi', 2005);

INSERT INTO accident (report_num, accident_date, location) VALUES

5
(11, '2019-01-24', 'Mysore Road'),
(12, '2019-02-04', 'South End Circle'),
(13, '2019-01-21', 'Bull temple Road'),
(14, '2019-02-17', 'Mysore Road'),
(15, '2019-03-04', 'Kanakpura Road');

INSERT INTO owns (driver_id, reg_num) VALUES

('A01', 'KA052250'),
('A02', 'KA053408'),
('A03', 'KA031181'),
('A04', 'KA095477'),
('A05', 'KA041702');

INSERT INTO participated (driver_id, reg_num, report_num, damage_amount)

VALUES
('A01', 'KA052250', 11, 10000),
('A02', 'KA053408', 12, 50000),
('A03', 'KA095477', 13, 25000),
('A04', 'KA031181', 14, 5000),
('A05', 'KA041702', 15, 5000);

6
Queries:
1. Display all accidents.

SELECT * FROM accident;

2. Display driver id who did the accident damage greater than or equal to Rs.25000.

SELECT driver_id FROM participated

WHERE damage_amount >= 25000;

3. List each driver with the cars they own.

SELECT person.driver_id, [Link], owns.reg_num, [Link]

FROM person, owns, car
WHERE person.driver_id = owns.driver_id AND car.reg_num = owns.reg_num;

7
4. Show all accidents, drivers involved and damage amount.

SELECT person.driver_id, [Link], [Link], participated.damage_amount

FROM person, accident, participated
WHERE person.driver_id = participated.driver_id AND accident.report_num =
participated.report_num;

5. Find the total damage amount per accident report

SELECT accident.report_num, participated.driver_id, participated.damage_amount

FROM accident, participated
WHERE accident.report_num = participated.report_num;

6. Find the total number of drivers in more than 1 accident.

SELECT [Link], person.driver_id

FROM person, accident, participated
WHERE person.driver_id = participated.driver_id AND participated.report_num =
accident.report_num
GROUP BY [Link], person.driver_id
HAVING COUNT(participated.report_num) > 1;

7. Find cars owned by drivers but have not been involved in any accident

SELECT car.reg_num

8
 FROM car
WHERE car.reg_num NOT IN (SELECT participated.reg_num FROM participated);

8. Find the average damage amount per driver.

SELECT driver_id, AVG(damage_amount)

FROM participated
GROUP BY driver_id;

9. Find the date of the most recent accident.

SELECT MAX(accident_date) as LatestDate

FROM accident;

10. Update the damage amount of a driver to 25000.

UPDATE participated
SET damage_amount = 25000
WHERE driver_id = ‘A05’;
SELECT * FROM participated;

9
11. Find drivers who caused maximum damage amount.

SELECT driver_id, damage_amount

FROM participated
WHERE damage_amount = (SELECT MAX(damage_amount) FROM participated);

12. Create a view summarizing accidents with participants count and total damage.

CREATE VIEW AccSum AS

SELECT accident.report_num, accident.accident_date, [Link],
COUNT(participated.driver_id) as total_drivers,
SUM(participated.damage_amount) as total_damage
FROM accident, participated
WHERE accident.report_num = participated.report_num
GROUP BY accident.report_num, accident.accident_date, [Link];
SELECT * FROM AccSum;

10
 Experiment 2: Bank Database

Specification of Bank Database Application:

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 modelled) 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 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.

11
Entity – Relationship Diagram:

12
Schema Diagram:

 Branch (branch-name: String, branch-city: String, assets: real)

 BankAccount(accno: int, branch-name: String, balance: real)
 BankCustomer (customername: String, customer-street: String, customercity: String)
 Depositer(customer-name: String, accno: int)
 Loan (loan-number: int, branch-name: String, amount: real)

1. Create the above tables by properly specifying the primary keys and the foreign keys.
2. Enter at least five tuples for each relation.

Create Database:
CREATE DATABASE bank;
USE bank;

Create Tables:
CREATE TABLE branch (
branch_name VARCHAR(20),
branch_city VARCHAR(20),
assets INT,
PRIMARY KEY (branch_name)
);

13
 CREATE TABLE bankaccount (
acc_no INT,
branch_name VARCHAR(20),
balance INT,
PRIMARY KEY (acc_no),
FOREIGN KEY (branch_name) REFERENCES branch(branch_name) );
CREATE TABLE bankcustomer (
customer_name VARCHAR(20),
customer_street VARCHAR(20),
customer_city VARCHAR(20),
PRIMARY KEY (customer_name)
);

CREATE TABLE depositer (

customer_name VARCHAR(20),
acc_no INT,
PRIMARY KEY (acc_no),
FOREIGN KEY (customer_name) REFERENCES bankcustomer (customer_name));

CREATE TABLE loan (

loan_number INT,
branch_name VARCHAR(20),
amount INT,
PRIMARY KEY (loan_number),
FOREIGN KEY (branch_name) REFERENCES branch(branch_name)
);

Structure of the Tables:

desc branch;

desc bankaccount;

desc bankcustomer;

14
 desc depositer;

desc loan;

Inserting Values into the Tables:

INSERT INTO branch (branch_name, branch_city, assets INT) VALUES
('SBI_Chamarajpet', 'Bangalore', 50000),
('SBI_Residency Road', 'Bangalore', 10000),
('SBI_Shivaji Road', 'Bangalore', 20000),
('SBI_Parliament Road', 'Delhi', 10000),
('SBI_Jantarmantar', 'Delhi', 20000);

INSERT INTO bankaccount (acc_no, branch_name, balance) VALUES

(1, 'SBI_Chamarajpet', 2000),
(2, 'SBI_Residency Road', 5000),
(3, 'SBI_Shivaji Road', 6000),
(4, 'SBI_Parliament Road', 9000),
(5, 'SBI_Jantarmantar', 8000);

INSERT INTO bankcustomer (customer_name, customer_street, customer_city)

15
VALUES
('Avinash', 'Bull temple road', 'Bangalore'),
('Dinesh', 'Bannerghatta road', 'Bangalore'),
('Mohan', 'NationalCollege road', 'Bangalore'),
('Nikhil', 'Akbar road', 'Delhi'),
('Ravi', 'Prithviraj road', 'Delhi');

INSERT INTO depositer (customer_name, acc_no) VALUES

('Avinash', 1),
('Dinesh', 2),
('Nikhil', 4),
('Ravi', 5),
('Nikhil', 9);

INSERT INTO loan (loan_number, branch_name, amount) VALUES

(1, 'SBI_Chamarajpet', 10000),
(2, 'SBI_Residency Road', 2000),
(3, 'SBI_Shivaji Road', 3000),
(4, 'SBI_Parliament Road', 4000),
(5, 'SBI_Jantarmantar', 5000);

16
Queries:
1. Display the branch name and assets from all branches in lakhs of rupees and rename the
assets column to 'assets in lakhs'.

SELECT branch_name, assets/100000 AS "Assets in Lakhs"

FROM branch;

2. Find all the customers who have at least two accounts at the same branch.

SELECT depositer.customer_name, bankaccount.branch_name

FROM depositer, bankaccount
WHERE depositer.acc_no = bankaccount.acc_no
GROUP BY depositer.customer_name, bankaccount.branch_name
HAVING COUNT (depositer.acc_no) >= 2;

3. Create a view which gives each branch the sum of the amount of all the loans at the
branch.

CREATE VIEW LoanSummary AS

SELECT branch_name, SUM(amount) AS Total_Amount
FROM loan
GROUP BY branch_name;
SELECT * FROM LoanSummary;

17
4. Find customers who have an account at all branches located in a specific city.

SELECT bankcustomer.customer_name, branch.branch_name

FROM bankcustomer, branch
WHERE bankcustomer.customer_city=branch.branch_city and branch.branch_name
= ALL (SELECT branch_name FROM branch WHERE branch_city="Delhi");

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

SELECT depositer.customer_name
FROM depositer, bankaccount, loan, branch
WHERE depositer.acc_no=bankaccount.acc_no AND
bankaccount.branch_name=branch.branch_name
AND branch.branch_name=loan.branch_name AND
branch.branch_city="Bangalore";

6. Find all branches that have greater assets than every branch in Bangalore.

SELECT branch_name
FROM branch
WHERE assets > ALL(SELECT assets FROM branch WHERE
branch_city="Bangalore");

7. Delete all account tuples at every branch located at a specific city.

18
 DELETE FROM bankaccount
WHERE bankaccount.branch_name IN (SELECT branch_name FROM branch
WHERE branch_city="Delhi");
SELECT * FROM bankaccount;

8. Update balance of all accounts by 5%.

UPDATE bankaccount
SET balance=balance+balance*0.05;
SELECT * FROM bankaccount;

19
 Experiment 3: Employee Database

Specification of Employee Database Application:

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

20
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.

Entity – Relationship Diagram:

21
Schema Diagram:

 EMPLOYEE (empno: int, ename: String, mgr_no: int, hiredate: Date, sal: int, deptno:
int)

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

 PROJECT (pno: int, ploc: String, pname: String)

 ASSIGNED_TO (empno: int, pno: int, job_role: String)

 INCENTIVES (empno: int, incentive_date: Date, incentive_amount: int)

1. Create the above tables by properly specifying the primary keys and the foreign keys.
2. Enter at least five tuples for each relation.

Create Database:
CREATE DATABASE employee;
USE employee;

Create Table:
CREATE TABLE project (
pno int PRIMARY KEY,
ploc varchar(20),
pname varchar(20)
);

CREATE TABLE dept (

dept_no int PRIMARY KEY,
dname varchar(20),
dloc varchar(20)
);

22
 CREATE TABLE employee (
empno int PRIMARY KEY,
ename varchar(20),
mgr_no int,
hiredate date,
sal int,
dept_no int,
FOREIGN KEY(dept_no) REFERENCES DEPT(dept_no)
);

CREATE TABLE assigned_to (

empno int,
pno int,
job_role varchar(20),
FOREIGN KEY (empno) REFERENCES EMPLOYEE(empno),
FOREIGN KEY (pno) REFERENCES PROJECT(pno)
);

CREATE TABLE incentives (

empno int,
incentive_date date PRIMARY KEY,
incentive_amt decimal(10,2),
FOREIGN KEY (empno) REFERENCES EMPLOYEE(empno)
);

Structure of the Tables:

desc project;

desc dept;

desc assigned_to;

23
 desc employee;

desc incentives;

Inserting Values into the Tables:

INSERT INTO project (pno, ploc, pname) VALUES
(1, 'Bengaluru', 'Alpha'),
(2, 'Bengaluru', 'Beta'),
(3, 'Mysore', 'Gamma'),
(4, 'Hyderabad', 'Delta'),
(5, 'Hyderabad', 'Epsilon');

INSERT INTO dept (dept_no, dname, dloc) VALUES

(10, 'Information systems', 'Bengaluru'),
(20, 'Backend Support', 'Mysore'),
(30, 'AI', 'Bengaluru'),
(40, 'Cybersecurity', 'Hyderabad'),
(50, 'Blockchain', 'Bengaluru');

24
INSERT INTO employee (empno, ename, mgr_no, hiredate, sal, dept_no) VALUES
(101, 'Raju', 101, '2013-03-04', 200000, 10),
(102, 'Ravi', 101, '2014-04-09', 95000, 10),
(103, 'Prasad', 101, '2015-07-09', 68000, 20),
(104, 'Mahesh', 101, '2016-02-04', 70400, 30),
(105, 'Ram', 101, '2015-10-10', 55300, 40);

INSERT INTO assigned_to (empno ,Pno ,job_role) VALUES

(101, 1, 'Manager'),
(102, 2, 'Engineer'),
(103, 3, 'Developer'),
(104, 4, 'Tester'),
(105, 5, 'Full stack developer');

INSERT INTO incentives (empno, incentive_date, incentive_amt) VALUES

(101, '2019-01-01', 22300.50),
(102, '2019-02-01', 3050.50),
(103, '2019-03-01', 3500.40),
(104, '2019-04-01', 4080.40),
(105, '2019-05-01', 520.50);

25
Queries:
1. Retrieve the employee numbers of all employees who work on project located in
Bengaluru.

SELECT [Link]
FROM assigned_to a, employee e, project p
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = 'Bengaluru';

2. Get Employee IDs of those employees who didn’t receive incentives.

SELECT [Link]
FROM employee e
WHERE [Link] NOT IN (SELECT [Link] FROM incentives i);

3. 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 DISTINCT [Link], [Link] ,a.job_role,d.Dept_no,[Link]

FROM employee e , dept d , project p , assigned_to a
WHERE e.Dept_no=d.Dept_no AND [Link] = [Link] AND [Link] = [Link] AND
[Link]=[Link];

26
4. List employees with their department name and location.

SELECT [Link], [Link], [Link], [Link]

FROM employee e, dept d
WHERE e.dept_no = d.dept_no;

5. Employees who are not assigned to any project, project-wise headcount.

SELECT [Link], [Link], COUNT([Link]) AS head_count

FROM project p, assigned_to a
WHERE [Link] =[Link]
GROUP BY [Link], [Link];

6. Department wise average and max salary.

SELECT d.dept_no ,[Link] , AVG([Link]) AS avg_salary , MAX([Link]) AS

max_salary
FROM dept d , employee e
WHERE e.dept_no=d.dept_no
GROUP BY d.dept_no ,[Link];

27
7. Total incentives earned by each employee (lifetime).

Select [Link] , SUM(i.incentive_amt) AS total_incentives

FROM employee e , incentives i
WHERE [Link] = [Link]
GROUP BY [Link];

8. Employees working on a given project (project name :Alpha Project).

SELECT [Link] , [Link] , [Link]

FROM employee e , assigned_to a , project p
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = 'Alpha';

9. Departments with no employees.

SELECT d.dept_no , [Link]

FROM dept d WHERE
d.Dept_no NOT IN (SELECT dept_no FROM employee);

10. Managers and their direct report counts (MGR_NO references EMPNO).

SELECT [Link] AS manager_id, [Link] AS manager_name, COUNT([Link])

AS direct_report_count FROM employee e, employee m
where [Link] = m.mgr_no
GROUP BY [Link], [Link];

11. Employees assigned to more than one project.

28
SELECT [Link] , [Link]
FROM assigned_to a , employee e
WHERE [Link]= [Link]
GROUP BY [Link]
HAVING COUNT([Link]) > 1;

29
 Experiment 4: Supplier Database

Specification of Supplier Database Application:

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 colour. 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 “analyse 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.

30
Entity – Relationship Diagram:

31
Schema Diagram:

 SUPPLIER (sid: int, sname: String, city: String)

 PARTS (pid: int, pname: String, color: String)

 CATALOG (sid: int, pid: int, cost: real)

1. Create the above tables by properly specifying the primary keys and the foreign keys.
2. Enter at least five tuples for each relation.

Create Database:
CREATE DATABASE supplier;
USE supplier;

Create Table:
CREATE TABLE supplier (
sid INT PRIMARY KEY,
sname VARCHAR(100),
city VARCHAR(50)
);

CREATE TABLE parts (

pid INT PRIMARY KEY,
pname VARCHAR(50),
color VARCHAR(20)
);

CREATE TABLE catalog (

sid INT,
pid INT,
cost DECIMAL(10,2),
PRIMARY KEY (sid, pid),
FOREIGN KEY (sid) REFERENCES SUPPLIER(sid),
FOREIGN KEY (pid) REFERENCES PARTS(pid)

32
 );

Structure of the Tables:

desc supplier;

desc parts;

desc catalog;

Inserting Values into the Tables:

INSERT INTO supplier (sid, sname, city) VALUES
(1, 'Acme Widget Supplier', 'Bengaluru'),
(2, 'Global industries', 'Chennai'),
(3, 'National Parts Co', 'Delhi'),
(4, 'Reliable Supplies', 'Mumbai');

INSERT INTO parts (pid, pname, color) VALUES

(101, 'Bolt', 'Red'),
(102, 'Nut', 'Green'),
(103, 'Screw', 'Red'),
(104, 'Washer', 'Blue'),
(105, 'Cog', 'Red');

33
INSERT INTO catalog (sid, pid, cost) VALUES
(1, 103, 10.00),
(1, 101, 12.50),
(2, 101, 11.00),
(2, 104, 9.50),
(3, 102, 7.75),
(3, 105, 15.00),
(4, 103, 8.00),
(4, 104, 9.75);

34
Queries:
1. Find the pnames of parts for which there is some supplier.

SELECT [Link]
FROM parts p, supplier s, catalog c
WHERE [Link]=[Link] AND [Link]=[Link]
GROUP BY [Link];

2. Find snames of suppliers who supply every part.

SELECT [Link]
FROM supplier s, catalog c
WHERE [Link]=[Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link])=(SELECT COUNT(pid) FROM catalog);

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

SELECT [Link]
FROM supplier s,catalog c, parts p
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link]= 'Red'
GROUP BY [Link];

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

SELECT [Link]
FROM parts p, catalog c, supplier s
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link]= 'Acme Widget Supplier';

5. 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 [Link]
FROM supplier s, parts p, catalog c
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link] > (SELECT AVG([Link]) FROM
catalog c WHERE [Link]=[Link] GROUP BY [Link]);

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

SELECT [Link], [Link]

FROM supplier s,catalog c, parts p
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link] = (SELECT MAX([Link]) FROM
catalog c WHERE [Link]=[Link] GROUP BY [Link]);

36
 Experiment 5: NoSQL Cloud Installation
(MongoDB Atlas)
Aim:
To install and configure a NoSQL database in the cloud using MongoDB Atlas and perform
basic database operations.

Software Requirements:
 Web browser (Chrome / Firefox)
 Internet connection
 MongoDB Atlas account

Procedure:
1. Open a web browser and visit [Link]
2. Click on Sign Up and create a MongoDB Atlas account using email or Google login.
3. After successful login, create a new project.
4. Click on Build a Database and choose the Free Tier (M0) cluster.
5. Select the cloud provider and region (preferably nearest region).
6. Create the cluster and wait for deployment to complete.
7. Configure Database Access by creating a database user with username and password.
8. Configure Network Access by allowing access from the current IP address (or
[Link]/0).
9. Click Connect and obtain the MongoDB connection string.
10. Use MongoDB Compass or MongoDB Shell to connect to the cloud database using
the provided connection string.
11. Create a database and collections to perform operations such as insert, find, update,
and delete.

Result:
MongoDB Atlas cloud database was successfully created and connected. Basic NoSQL
operations were performed on the cloud-hosted database.

Conclusion:
Thus, a NoSQL database was successfully installed and configured in the cloud using
MongoDB Atlas, demonstrating cloud-based database deployment and access.

37
 Experiment 6: NoSQL – Student Database

Specification of NoSQL – Student Database Application:

The NoSQL student database must store and manage student information using a document-
oriented data model in MongoDB. Each student record is uniquely identified by a roll number
and includes attributes such as age, contact number, and email ID. The database must support
insertion of appropriate student records and allow modification of existing data, including
updating a student’s email ID based on roll number and replacing a student name with a new
value for a specified roll number. It should also support administrative operations such as
exporting the student collection to the local file system for backup or data transfer purposes
and importing data from external CSV files into MongoDB collections. Additionally, the
system must allow safe deletion of collections when required. The database should ensure
flexible schema handling, efficient document updates, and reliable data persistence while
demonstrating fundamental NoSQL operations such as insert, update, replace, export, import,
and drop.

Create Database:

Insert Appropriate Values:

Insert at least 4 documents each with RollNo, Name, Age, ContactNo and EmailID attributes.

38
Queries:
1. Write a query to update EmailID of a student with RollNo 10.

2. Replace the student’s name from “DEF” to “FEM” of RollNo 11.

3. Export the created table into Local File System.

Or
a) Open your Collections Table.
b) Click on Export Data -> Export Full Collection.
c) Export File Type → CSV.
d) A dialog box will open. Choose file destination and click save.

39
4. Drop the table.

5. Import a given a CSV dataset from your Local File System into MongoDB collection.
a) Open your collections table.
b) Click Add Data → Import JSON or CSV file.
c) Select previously saved CSV file ([Link]).
d) Click Import.

40
 Experiment 7: NoSQL – Customer Database

Specification of NoSQL – Customer Database Application:

The NoSQL customer database is designed to store and manage customer account
information using a document-based data model in MongoDB. Each customer record is
uniquely identified by a customer ID and includes attributes such as account balance and
account type. The database must support insertion of multiple customer records and enable
querying of customers whose total account balance exceeds a specified value for a given
account type. It should also allow aggregation operations to determine the minimum and
maximum account balances for each customer. Additionally, the system must support
administrative operations such as exporting the customer collection to the local file system
for backup purposes, importing customer data from external CSV files, and safely dropping
collections when required. The database should demonstrate efficient data storage, flexible
querying, and basic aggregation capabilities of NoSQL databases.

Create Database:

Insert Appropriate Values:

Insert at least 4 documents each with Cust_id, Acc_Bal, Acc_Type attributes.

41
Queries:
1. Write a query to display those records whose total account balance is greater than 1200 of
account type ‘Z’ for each Cust_id.

2. Determine Minimum and Maximum account balance for each customer_id.

42
3. Export the created table into Local File System.

Or
e) Open your Collections Table.
f) Click on Export Data → Export Full Collection.
g) Export File Type → CSV.
h) A dialog box will open. Choose file destination and click save.

4. Drop the table.

5. Import a given a CSV dataset from your Local File System into MongoDB collection.
a) Open your collections table.
b) Click Add Data → Import JSON or CSV file.
c) Select previously saved CSV file ([Link]).
d) Click Import.

43
 Experiment 8: NoSQL – Restaurant Database

Specification of NoSQL – Restaurant Database Application:

The NoSQL restaurant database is designed to store and manage restaurant-related
information using a document-oriented data model in MongoDB. Each restaurant document
contains details such as restaurant ID, name, location information, cuisine type, and
inspection scores. The database must support retrieval of all restaurant records and allow
sorting of restaurant data based on specified attributes. It should enable selective querying to
extract specific fields such as restaurant ID, name, town, and cuisine for restaurants meeting
given score criteria. Additionally, the system must support aggregation operations to compute
the average inspection score for each restaurant and perform pattern-based queries to identify
restaurants located in areas with specific zip code formats. The database demonstrates
flexible querying, sorting, filtering, and aggregation capabilities of MongoDB for real-world
data analysis.

Create Database:

Insert Appropriate Values:

Insert at least 4 documents each with Restaurant_id, Name, Cuisine, Address (Town,
Zipcode), Score attributes.

44
45
Queries:
1. Write a MongoDB query to display all the documents in the collection restaurants.

46
2. Write a MongoDB query to arrange the name of the restaurants in descending along with
all the columns.

47
3. Write 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.

48
4. Write a MongoDB query to find the average score for each restaurant.

49
5. Write a MongoDB query to find the name and address of the restaurants that have a zip
code that starts with '10'.

50
 Experiment 9: LeetCode Practice - I

Problem – 570. Managers with at least 5 direct reports:

Query:
Write a solution to find managers with at least five direct reports.

SELECT [Link]
FROM Employee e, Employee m
WHERE [Link] = [Link]
GROUP BY [Link]
HAVING COUNT([Link]) >= 5;

Input Output

51
 Experiment 10: LeetCode Practice - II

Problem – 585. Investments in 2016:

Query:
Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all
policyholders who:
 Have the same tiv_2015 value as one or more other policyholders, and
 Are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute
pairs must be unique).
Round tiv_2016 to two decimal places.

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016

FROM Insurance
WHERE (lat, lon) IN (
SELECT lat, lon
FROM Insurance
GROUP BY lat, lon
HAVING COUNT(*) = 1
)

52
 AND tiv_2015 IN (
SELECT tiv_2015
FROM Insurance
GROUP BY tiv_2015
HAVING COUNT(*) > 1
);

Input Output

Output is 70 because the location constraint is checked irrespective of tiv_2015. The pid 3
and pid 5 have same lat, lat pair – 3,3. Hence pid 3 is excluded from the output. Final output
will only tiv_2016 from pid 1, 2 and 4 = 10 + 20 + 40 = 70.

53
 Experiment 11: LeetCode Practice - III

Problem – 180. Consecutive Numbers:

Query:
Find all numbers that appear at least three times consecutively.
SELECT DISTINCT [Link] AS ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link] – 1 AND
[Link] = [Link] - 1;

54
Input
Output

55