Jayawant Shikshan Prasarak Mandal

JAYAWANTRAO SAWANT COLLEGE OF ENGINEERING,

HADAPSAR, PUNE

Department of Computer Engineering

Lab Manual
DATABASE MANAGEMENT SYSTEMS LAB

S.E. Computer Engineering

Academic Year: 2025-2026

 Savitribai Phule Pune University
Jayawant ShikshaPrasarak Mandal’s

JAYAWANTRAO SAWANT COLLEGE OF ENGINEERING,

HADAPSAR, PUNE

Department of Computer Engineering

Lab Manual

Second Year Engineering

Semester-IV

DATABASE MANAGEMENT SYSTEMS LAB

Subject Code: PCC-209- COM: Database Management Laboratory

Prepared By : Prof. R. V. Shinde.

: Prof. [Link].
Savitribai Phule Pune University
Savitribai Phule Pune University
Savitribai Phule Pune University
Savitribai Phule Pune University
Savitribai Phule Pune University
 Savitribai Phule Pune University
Assignment No 1
Title: ER Modeling and Normalization:
Case Study and ER Diagram
Develop a case study and design its Entity-Relationship (ER) Diagram. Convert the ER model into a
relational model.

Objective: To understand the concept of ER model and convert into relational tables.

Software Required: ERD plus online tool.

Theory:

What is ER Diagram?

The Entity Relational Model is a model for identifying entities to be represented in the database and
representation of how those entities are related. The ER data model specifies an enterprise schema that
represents the overall logical structure of a database graphically.

The Entity Relationship Diagram explains the relationship among the entities present in the
database. ER models are used to model real-world objects like a person, a car, or a company and the
relation between these real-world objects. In short, ER Diagram is the structural format of the database.

Why Use ER Diagrams In DBMS?

● ER diagrams are used to represent the E-R model in a database, which makes them easy to be
converted into relations (tables).
● ER diagrams provide the purpose of real-world modeling of objects which makes them intently useful.
● ER diagrams require no technical knowledge and no hardware support.
● These diagrams are very easy to understand and easy to create even for a naive user.
● It gives a standard solution for visualizing the data logically.

Symbols Used in ER Model:

ER Model is used to model the logical view of the system from a data perspective which consists of
these symbols:

● Rectangle: Rectangles represent Entities in ER Model.

● Ellipse: Ellipses represent Attributes in ER Model.
● Diamond: Diamonds represent Relationships among Entities.
● Line: Lines represent attributes to entities and entity sets with other relationship types.
● Double Ellipse: Double Ellipses represent Multi-Valued Attributes.
● Double Rectangle: Double Rectangle represents a Weak Entity.
 Savitribai Phule Pune University

❖ Entity :
An Entity may be an object with a physical existence – a particular person, car, house, or employee –
or it may be an object with a conceptual existence – a company, a job, or a university course.

1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not depend on other
Entity in the Schema. It has a primary key, that helps in identifying it uniquely, and it is represented by
a rectangle. These are called Strong Entity Types.

2. Weak Entity
An Entity type has a key attribute that uniquely identifies each entity in the entity set. But some entity
type exists for which key attributes can’t be defined. These are called Weak Entity types.

❖ Attributes :
Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age,
Address, and Mobile_No are the attributes that define entity type Student.

1. Key Attribute
The attribute which uniquely identifies each entity in the entity set is called the key attribute. For
example, Roll_No will be unique for each student.

2. Composite Attribute
An attribute composed of many other attributes is called a composite attribute. For example, the
Address attribute of the student Entity type consists of Street, City, State, and Country.
 Savitribai Phule Pune University
than one for a given student).

4. Derived Attribute
An attribute that can be derived from other attributes of the entity type is known as a derived attribute.
e.g.; Age (can be derived from DOB).

Relationship:
A Relationship represents the association between entity types.

How to Draw ER Diagram?

● The very first step is Identifying all the Entities, and place them in a Rectangle, and labeling them
accordingly.
● The next step is to identify the relationship between them and pace them accordingly using the
Diamond, and make sure that, Relationships are not connected to each other.
● Attach attributes to the entities properly.
● Remove redundant entities and relationships.
● Add proper colors to highlight the data present in the database.

E-R
Model Bus

● BusNo
● Source
● Destination
● CoachType

SCHEMA
 Savitribai Phule Pune University
TicketTicketNo
● DOJ
● Address
● ContactNo
● BusNo
● SeatNo
● Source
● Destination

●
SCHEMA

Passenger
● PassportID
● TicketNo
● Name
● ContactNo
● Age
● Sex
● Address
 Savitribai Phule Pune University

Reservation
● PNRNo
● DOJ
● No_of_seats
● Address
● ContactNo
● BusNo
● SeatNo

SCHEMA

Cancellation
● PNRNo
● DOJ
● SeatNo
● ContactNo
● Status

SCHEMA
 Savitribai Phule Pune University
CONCEPT DESIGN WITH E-R MODEL
 Savitribai Phule Pune University
★ To Represent all the entities (Strong, Weak) in tabular fashion. Represent relationships in a tabular fashion.

Bus:

ColumnName Datatype Constraints Type of Attributes

BusNo Varchar(10) Primary key Single-value

Source Varchar(20) Single-value

Destination Varchar(20) Simple

CoachType Varchar(10) Simple

Ticket:

ColumnName Datatype Constraints Type of Attributes

TicketNo Varchar(20) Primary Key Single-valued

DOJ Date Single-valued

Address Varchar(20) Composite

ContactNo Integer Multi-valued

BusNo Varchar(10) Foreign Key Single-valued

SeatNo Integer Simple

Source Varchar(10) Simple

Destination Varchar(10) Simple

Passenger:

Type of Attributes
ColumnName Datatype Constraints
PassportID Varchar(15) Primary Key Single-valued

TicketNo Varchar(20) Foreign Key Single-valued

Name Varchar(20) Composite

ContactNo Varchar(20) Multi-valued

Age Integer Single-valued

Sex character Simple

Address Varchar(20) Composite

Reservation:
 Savitribai Phule Pune University

ColumnName Datatype Constraints Type of Attributes

PNRNo Varchar(20) Primary Key Single-valued

DOJ date Single-valued

No_of_Seats Integer Simple

Address Varchar(20) Composite

ContactNo Varchar(10) Multi-valued

BusNo Varchar(10) Foreign Key Single-valued

SeatNo Integer Simple

Cancellation:

ColumnName Datatype Constraints Type of Attributes

PNRNo Varchar(10) Primary Key Single-valued

DOJ date Single-valued

SeatNo Integer Simple

ContactNo Varchar(15) Multi-valued

Status Varchar(10) Simple

Conclusion: Here we understood the concept of ER model and relational model representation from ER.
 Savitribai Phule Pune University
Assignment No 2
Title: SQL Queries:
Write and execute SQL Data Definition Language (DDL) commands such as CREATE, ALTER, DROP,
RENAME, and TRUNCATE to define and modify tables. Insert data into the tables and apply appropriate
integrity constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK

Objective: Understand the concept of DDL & DML Commands and its operations with Operators, functions.

Software Required: MYSQL Workbench

Theory:
What is SQL?

SQL stands for Structured Query Language. SQL is used to communicate with a database. According to
ANSI (American National Standards Institute), it is the standard language for relational database
management systems. SQL statements are used to perform tasks such as update data on a database, or
retrieve data from a database. Some common relational database management systems that use SQL are:
Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL,
most of them also have their own additional proprietary extensions that are usually only used on their
system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create",
and "Drop" can be used to accomplish almost everything that one needs to do with a database.

SQL Languages:

There is three types of commands are used in database that is :-

1) DDL (Data Definition Language)
2) DML (Data Manipulation Language)
3) DCL (Data Control Language)

DDL (Data Definition Language) :-

i) It Is Stands For Data Definition Language
ii) It is used for manipulate the data.
iii) DDL statements are used to define the database structure or schema.

Operations performs on DDL are:-

1. Create operation:-

a) Create database :- The CREATE DATABASE statement is used to create a new SQL
database.
Syntax:- CREATE DATABASE databasename;
b) Create Table :- It is used to Create a Table.
Syntax:- CREATE TABLE table_name ( column1 datatype, column2 datatype, column3
datatype);

2. Alter Table :- The ALTER TABLE statement is used to add, delete, or modify columns in an existing
table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table
and alters the structure of the database
 Savitribai Phule Pune University

a) ALTER TABLE - ADD Column

To add a column in a table, use the following syntax :-ALTER TABLE table_name
ADD column_name datatype;

b) ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax:-
ALTER TABLE table_name
DROP COLUMN column_name;

c) ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax :-
LTER TABLE table_name
ALTER COLUMN column_name datatype;

3. Drop Table :- The DROP TABLE statement is used to drop an existing table in a database.

Syntax:- DROP TABLE table_name;

4. Truncate Table :- The TRUNCATE TABLE statement is used to delete the data inside a table, but
not the table itself.

Syntax:- TRUNCATE TABLE table_name;

5. Rename Table :- It is used to rename an object . It is used for give another name to the
table. Syntax :- Rename old_table_name to New_table_name ;
SQL Objects :-

1) Table: A table is a collection of related data held in a structure format within a database it consists
of column and row a table is a set of data elements using a model of vertical column and horizontal
rows the cell being the init where a row and column insert .

2) View: In SQL, a view is a virtual table based on the result-set of an SQL statement.A view
contains rows and columns, just like a real table. The fields in a view are fields from one or more
real tables in the database.

a) CREATE VIEW:

Syntax :- CREATE VIEW view_name AS SELECT column1, column2, ...

FROM table_name WHERE condition;
b) SQL CREATE OR REPLACE VIEW :

Syntax:- CREATE OR REPLACE VIEW view_name AS SELECT column1, column2,

FROM table_name WHERE condition;

c) SQL Dropping a View: You can delete a view with the DROP
VIEW command. Syntax:- DROP VIEW view_name;
 Savitribai Phule Pune University
3) Index: Indexes are used to retrieve data from the database very fast. The users cannot see the indexes,
they are just used to speed up searches/queries.

a) CREATE INDEX :- Creates an index on a table. Duplicate values

are allowed: Syntax:- CREATE INDEX index_name
ON table_name (column1, column2, ...);

b) CREATE UNIQUE INDEX :-Creates a unique index on a table. Duplicate values are
not allowed: Syntax:- CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

4) Sequence :- Auto-increment allows a unique number to be generated automatically when a new record is
inserted into a table. Often this is the primary key field that we would like to be created automatically every
time a new record is inserted.

Syntax :- Create table < table_name > (variable_name datatype primary key auto increment ,
variable_name data type);

5) INSERT

Used to add new records (rows) into a table.

Syntax
INSERT INTO table_name VALUES (value1, value2, ...);
or
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example
INSERT INTO Student VALUES (101, 'Amit', 'amit@[Link]', 20);

6)Integrity Constraints

a. NOT NULL

Ensures that a column cannot have NULL values.

Example
Name VARCHAR(50) NOT NULL

b. UNIQUE

Ensures all values in a column are different.

Example
Email VARCHAR(50) UNIQUE

c. PRIMARY KEY

Uniquely identifies each record and cannot be NULL.

Example
StudentID INT PRIMARY KEY

d. FOREIGN KEY
 Savitribai Phule Pune University

Establishes a relationship between two tables by referencing the primary key of another table.
Syntax
FOREIGN KEY (column) REFERENCES parent_table(column)
Example
CREATE TABLE Enrollment (
EnrollID INT PRIMARY KEY,
StudentID INT,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

e. CHECK

Ensures values in a column satisfy a specified condition.

Example
Age INT CHECK (Age >= 18)

Conclusion: - Here we understood the DDL Commands and its operations with Integrity Constraints.
 Savitribai Phule Pune University
Assignment No 3
Title: SQL Queries for Data Manipulation, Access Control, and Transaction
Management
SQL Queries for Data Manipulation, Access Control, and Transactions Design and run SQL
queries to demonstrate the following:
a) Data Manipulation (DML): Use SQL statements to INSERT, UPDATE, and DELETE
records. Apply arithmetic, logical, set operators, pattern matching, and string functions.
b) Access Control (DCL): Use GRANT, REVOKE, and ROLE commands to manage user
access.
c) Transaction Control (TCL): Apply START TRANSACTION, COMMIT, ROLLBACK,
and SAVEPOINT commands to manage transactions.

Objective: Understand the concept of DML & DCL, TCL Commands and its operations.

1. Data Manipulation Language (DML): Modifying and querying database records

using arithmetic, logical, and string operations.
2. Data Control Language (DCL): Managing database security and user permissions.
3. Transaction Control Language (TCL): Managing the integrity of database
transactions.

Software Required

MySQL Workbench (or MySQL Command Line Client)

Theory

Structured Query Language (SQL) is divided into several sub-languages based on the nature
of the operations performed. This lab focuses on manipulating data content, securing access
to that data, and ensuring data consistency during complex operations.

1. Data Manipulation Language (DML)

DML commands are used to manage data within schema objects.

 INSERT: Adds new rows to a table.

 UPDATE: Modifies existing data within a table.
 DELETE: Removes rows from a table.
 Operators & Functions:
o Arithmetic: +, -, *, / used for numeric calculations.
o Logical: AND, OR, NOT used to filter criteria.
o Set Operators: UNION, UNION ALL combine result sets from multiple
SELECT statements.
o Pattern Matching: The LIKE operator is used with wildcards (% for multiple
characters, _ for single character) to find patterns.
o String Functions: Functions like CONCAT(), UPPER(), and SUBSTR()
 Savitribai Phule Pune University
manipulate string data.

2. Data Control Language (DCL)

DCL commands control access to data within the database.

 GRANT: Gives user access privileges to a database or specific objects.

 REVOKE: Withdraws access privileges given with the GRANT command.
 Roles: Named collections of privileges that can be assigned to users to simplify
permission management.

4. Transaction Control Language (TCL)

TCL commands manage the changes made by DML statements. They ensure the ACID
properties (Atomicity, Consistency, Isolation, Durability) of the database.

 START TRANSACTION: Begins a new transaction. Disables autocommit mode.

 COMMIT: Permanently saves the changes made during the current transaction.
 ROLLBACK: Undoes changes made during the current transaction, reverting the
database to its previous state.
 SAVEPOINT: Creates a point within a transaction to which you can later roll back.

Pre-requisite: Create a database and a sample table for testing.

CREATE DATABASE LabSession;

USE LabSession;

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
Part A: Data Manipulation (DML)

1. Inserting and Updating Records

-- INSERT: Add records

INSERT INTO Employees VALUES (101, 'Alice Smith', 'HR', 50000);
INSERT INTO Employees VALUES (102, 'Bob Jones', 'IT', 60000);
INSERT INTO Employees VALUES (103, 'Charlie Brown', 'IT', 62000);
INSERT INTO Employees VALUES (104, 'David White', 'Finance', 55000);

-- UPDATE: Apply Arithmetic Operator (Give a 10% raise to IT dept)

UPDATE Employees
SET Salary = Salary * 1.10
 Savitribai Phule Pune University
WHERE Department = 'IT';

2. Logical Operators and Pattern Matching

-- SELECT: Using Logical Operators (AND, OR)

SELECT * FROM Employees
WHERE Department = 'IT' AND Salary > 65000;

-- PATTERN MATCHING: Find names starting with 'A'

SELECT * FROM Employees
WHERE Name LIKE 'A%';

3. String Functions and Set Operators

-- STRING FUNCTIONS: Convert Name to uppercase and extract first 3 letters of Dept
SELECT UPPER(Name) as EmployeeName, SUBSTR(Department, 1, 3) as DeptCode
FROM Employees;

-- SET OPERATOR (UNION): Combine results from two queries

SELECT Name FROM Employees WHERE Department = 'HR'
UNION
SELECT Name FROM Employees WHERE Department = 'Finance';

4. Deleting Records

-- DELETE: Remove employee with ID 104

DELETE FROM Employees WHERE EmpID = 104;

Part B: Access Control (DCL)

1. Creating a User and a Role (Note: Run these as a root/admin user)

-- Create a new user

CREATE USER 'lab_user'@'localhost' IDENTIFIED BY 'password123';

-- Create a role
CREATE ROLE 'app_developer';

2. Granting Privileges

-- Grant specific access to the Role

GRANT SELECT, INSERT ON [Link] TO 'app_developer';

-- Assign the Role to the User

GRANT 'app_developer' TO 'lab_user'@'localhost';
 Savitribai Phule Pune University
-- Activate the role (often required in MySQL 8.0+)
SET DEFAULT ROLE ALL TO 'lab_user'@'localhost';

3. Revoking Privileges

-- Revoke INSERT permission from the Role

REVOKE INSERT ON [Link] FROM 'app_developer';

-- Verify grants
SHOW GRANTS FOR 'lab_user'@'localhost';

Part C: Transaction Control (TCL)

Scenario: We will insert a new record, set a savepoint, delete a record mistakenly, and then
recover the deleted record using rollback.

-- 1. Start the Transaction

START TRANSACTION;

-- 2. Perform a DML operation (Insert new employee)

INSERT INTO Employees VALUES (105, 'Eve Black', 'Marketing', 48000);

-- 3. Set a Savepoint (Safety net)

SAVEPOINT Savepoint_Before_Delete;

-- 4. Perform a risky operation (Accidentally delete everyone in HR)

DELETE FROM Employees WHERE Department = 'HR';

-- Verify current state (HR is gone, Eve is present)

SELECT * FROM Employees;

-- 5. Rollback to Savepoint (Undo the deletion, keep the insertion)

ROLLBACK TO Savepoint_Before_Delete;

-- Verify state (HR is back, Eve is still there)

SELECT * FROM Employees;

-- 6. Commit the final changes to the database

COMMIT;

Conclusion

In this lab, we successfully implemented the essential SQL commands required for database
management.
 Savitribai Phule Pune University
1. DML: We manipulated table data using INSERT, UPDATE, and DELETE, and
queried it using arithmetic logic and string functions.
2. DCL: We secured the database by creating users/roles and strictly controlling their
permissions using GRANT and REVOKE.
3. TCL: We maintained data integrity by grouping operations into transactions, utilizing
SAVEPOINT and ROLLBACK to recover from errors before finalizing changes with
COMMIT.
 Savitribai Phule Pune University
Assignment No 4

Title: Aggregate Functions and Grouping

Use aggregate functions along with GROUP BY and HAVING clauses to retrieve
summarized data from the database.
Objective

To understand and implement advanced data retrieval techniques that summarize large
datasets.

1. Apply Aggregate Functions to perform calculations on multiple rows.

2. Use the GROUP BY clause to arrange identical data into groups.
3. Use the HAVING clause to filter these groups based on specific conditions.

Software Required

 MySQL Workbench (or MySQL Command Line Client)

Theory

In database management, raw data often needs to be summarized to provide meaningful

insights (e.g., "What is the total revenue?" rather than "Show me every single transaction").

1. Aggregate Functions

These functions take a collection of values (an entire column or a group of rows) and return a
single summary value.

 COUNT(): Returns the number of rows.

 SUM(): Returns the total sum of a numeric column.
 AVG(): Returns the average value of a numeric column.
 MIN(): Returns the smallest value.
 MAX(): Returns the largest value.

2. GROUP BY Clause

The GROUP BY statement groups rows that have the same values into summary rows. It is
almost always used with aggregate functions.

 Syntax: SELECT column_name, AGG_FUNC(column) FROM table GROUP BY

column_name;
 Logic: "For every unique value in column X, calculate the aggregate of column Y."

3. HAVING Clause

The HAVING clause is used to filter groups after the GROUP BY operation has occurred.
 Savitribai Phule Pune University
 Difference from WHERE:
o WHERE filters individual rows before grouping.
o HAVING filters groups after grouping (because WHERE cannot work with
aggregate functions like SUM or COUNT).

Practical Demonstration (Lab Exercises)

Pre-requisite: Create a dataset suitable for grouping. We will use a Sales_Data table.

CREATE DATABASE LabSessionAgg;

USE LabSessionAgg;

CREATE TABLE Sales_Data (

SaleID INT PRIMARY KEY,
Region VARCHAR(50),
Product VARCHAR(50),
SaleAmount DECIMAL(10, 2)
);

-- Insert dummy data

INSERT INTO Sales_Data VALUES
(1, 'North', 'Laptop', 500.00),
(2, 'South', 'Tablet', 300.00),
(3, 'North', 'Mouse', 20.00),
(4, 'East', 'Laptop', 550.00),
(5, 'North', 'Laptop', 500.00),
(6, 'South', 'Laptop', 600.00),
(7, 'East', 'Mouse', 25.00),
(8, 'West', 'Tablet', 320.00),
(9, 'North', 'Tablet', 310.00);
Part A: Basic Aggregate Functions

1. Calculate Overall Statistics We use aggregates on the entire table without grouping to get
global totals.

-- Count total number of sales transactions

SELECT COUNT(*) AS TotalTransactions FROM Sales_Data;

-- Calculate total revenue across the company

SELECT SUM(SaleAmount) AS TotalRevenue FROM Sales_Data;

-- Find the most expensive and cheapest sale

SELECT MAX(SaleAmount) AS MaxSale, MIN(SaleAmount) AS MinSale
FROM Sales_Data;
Part B: The GROUP BY Clause
 Savitribai Phule Pune University
2. Grouping by a Single Column We want to see the total sales volume generated by each
Region.

-- Calculate Total Sales per Region

SELECT Region, SUM(SaleAmount) AS RegionalRevenue
FROM Sales_Data
GROUP BY Region;

Output will show one row for North, South, East, and West with their respective sums.

3. Grouping by Multiple Columns We want to see how specific products perform in

specific regions.

-- Count how many of each Product were sold in each Region

SELECT Region, Product, COUNT(*) AS UnitsSold
FROM Sales_Data
GROUP BY Region, Product;
Part C: The HAVING Clause

4. Filtering Groups (WHERE vs HAVING) We want to identify "High Value" regions

only.

 Scenario: Find regions where the total revenue exceeds $800.

SELECT Region, SUM(SaleAmount) AS RegionalRevenue

FROM Sales_Data
GROUP BY Region
HAVING SUM(SaleAmount) > 800;

Note: We use HAVING because we are filtering based on the result of SUM(), which is an
aggregate.

5. Combining WHERE, GROUP BY, and HAVING

 Scenario: Calculate the average sale amount for 'Laptop' products only, grouped by
region, but only show regions where that average is greater than $500.

SELECT Region, AVG(SaleAmount) AS AvgLaptopPrice

FROM Sales_Data
WHERE Product = 'Laptop' -- 1. Filter rows: Keep only Laptops
GROUP BY Region -- 2. Group: Group remaining rows by Region
HAVING AVG(SaleAmount) > 500; -- 3. Filter groups: Check aggregate condition

Conclusion

Thus we demonstrated how to transform raw data into summarized information:

 Savitribai Phule Pune University
1. Aggregate Functions allowed us to calculate totals, averages, and extremes (SUM,
AVG, MAX) across the dataset.
2. The GROUP BY clause allowed us to categorize this data, enabling analysis per
sector (e.g., Revenue per Region).
3. The HAVING clause provided the ability to filter these summaries, effectively acting
as a "conditional filter for groups" that complements the standard WHERE clause.
 Savitribai Phule Pune University
Assignment No 5
Title: JOIN Operations and Views
Perform various types of JOIN operations to extract meaningful relationships between
tables. Create and manage different database views.
Objective:

To understand how to combine data from multiple tables and create virtual tables for
simplified access.

1. JOIN Operations: Retrieve data from two or more tables based on a related column
between them.
2. Database Views: Create, query, and manage virtual tables (Views) to abstract
complex queries and enhance security.

Software Required

 MySQL Workbench (or MySQL Command Line Client)

Theory

Relational databases are designed to store data in separate tables to reduce redundancy. To
retrieve meaningful information, these tables must be linked.

1. JOIN Operations

A JOIN clause is used to combine rows from two or more tables, based on a related column
between them (usually a Primary Key and Foreign Key).

 INNER JOIN: Returns records that have matching values in both tables.
 LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table. (Returns NULL if no match is found).
 RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table.
 CROSS JOIN: Returns the Cartesian product (all combinations of rows) between
tables.

2. Database Views

A View is a virtual table based on the result-set of an SQL statement.

 Virtual: It contains rows and columns like a real table, but the fields are from one or
more real tables in the database.
 Abstraction: Views simplify complex queries (e.g., complex Joins) by hiding the
complexity from the user.
 Savitribai Phule Pune University
 Security: Views can restrict access to specific columns in a table while hiding others
(like salaries or passwords).

Practical Demonstration

Pre-requisite: Create two related tables: Customers and Orders.

CREATE DATABASE LabSessionJoins;

USE LabSessionJoins;

-- Table 1: Customers
CREATE TABLE Customers (
CustID INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
);

-- Table 2: Orders
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustID INT,
Amount DECIMAL(10, 2),
FOREIGN KEY (CustID) REFERENCES Customers(CustID)
);

-- Insert Data
-- Note: 'Charlie' makes no orders. 'OrderID 103' is assigned to CustID 99 (orphan record for
demo).
INSERT INTO Customers VALUES (1, 'Alice', 'New York'), (2, 'Bob', 'London'), (3,
'Charlie', 'Paris');
INSERT INTO Orders VALUES (101, 1, 500.00), (102, 2, 300.00), (103, 99, 150.00);

Part A: JOIN Operations

1. INNER JOIN Retrieve a list of customers who have actually placed an order, along with
their order details.

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

FROM Customers
INNER JOIN Orders ON [Link] = [Link];

Observation: Charlie is excluded because he has no matching ID in the Orders table.

2. LEFT JOIN Retrieve a list of all customers, showing order details if they exist, or NULL
if they don't.
 Savitribai Phule Pune University
SELECT [Link], [Link]
FROM Customers
LEFT JOIN Orders ON [Link] = [Link];

Observation: Charlie appears in the list with 'NULL' for OrderID.

3. RIGHT JOIN Retrieve a list of all orders, showing the customer name if it exists.

SELECT [Link], [Link]

FROM Customers
RIGHT JOIN Orders ON [Link] = [Link];

Observation: Order 103 appears even though Customer 99 does not exist in our Customers
table (returns NULL for Name).

4. CROSS JOIN Create a combination of every customer with every order (Cartesian
Product).

SELECT [Link], [Link]

FROM Customers
CROSS JOIN Orders;

Part B: Managing Views

1. Creating a View Create a view named Customer_Order_Summary that hides the IDs and
only shows readable names and amounts for fulfilled orders.

CREATE VIEW Customer_Order_Summary AS

SELECT [Link], [Link], [Link]
FROM Customers
JOIN Orders ON [Link] = [Link];

2. Querying a View Access the data just like a regular table.

SELECT * FROM Customer_Order_Summary;

-- Filter results from the view

SELECT * FROM Customer_Order_Summary WHERE Amount > 400;

3. Updating a View (Simple View) If a view maps directly to a table without aggregates or
groups, it can be updated. (Note: This updates the underlying Customers table).

CREATE VIEW City_View AS

SELECT CustID, Name, City FROM Customers;

UPDATE City_View
 Savitribai Phule Pune University
SET City = 'Tokyo'
WHERE Name = 'Alice';

4. Dropping a View Remove the view when it is no longer needed.

DROP VIEW Customer_Order_Summary;

Conclusion

Thus, we explored how to relate data across the database:

1. Joins: We utilized INNER JOIN to find intersections, LEFT JOIN to prioritize the
main entity (Customers), and RIGHT JOIN to prioritize the transactional entity
(Orders). This allowed us to generate reports that handle missing or mismatched data
gracefully.
2. Views: We created a View to act as a saved query. This demonstrated how to
encapsulate complex join logic into a simple, reusable object
(Customer_Order_Summary), improving both query simplicity and data security.
 Savitribai Phule Pune University
Assignment No 6
Title: Subqueries
Write and execute subqueries to retrieve data from one table based on results from another.
Objective:

To understand and implement the concept of Subqueries (Nested Queries) in SQL.

1. Single-Row Subqueries: Retrieve data based on a single value returned by an inner

query.
2. Multi-Row Subqueries: Retrieve data based on a list of values returned by an inner
query.
3. Correlated Subqueries: Execute a subquery that depends on the outer query for its
values.

Software Required

 MySQL Workbench (or MySQL Command Line Client)

Theory

A Subquery (also known as a nested query or inner query) is a query nested inside another
SQL query. It is used to return data that will be used in the main query as a condition to
further restrict the data to be retrieved.

1. Types of Subqueries

 Single-Row Subquery: Returns zero or one row to the outer SQL statement. It is
typically used with comparison operators like =, >, <, >=, <=, or <>.
 Multi-Row Subquery: Returns more than one row to the outer SQL statement. It is
used with multiple-value operators such as IN, ANY, or ALL.
 Correlated Subquery: A subquery that uses values from the outer query. The
subquery is executed once for each row processed by the outer query.

2. Execution Flow

In a standard (non-correlated) subquery, the inner query executes first, and its result is
passed to the outer query.

 Structure:

SELECT column_name
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name
WHERE condition);
 Savitribai Phule Pune University
Practical

Pre-requisite: Create Employees and Departments tables.

CREATE DATABASE LabSessionSub;

USE LabSessionSub;

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10, 2),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Insert Data
INSERT INTO Departments VALUES (1, 'IT'), (2, 'HR'), (3, 'Sales');
INSERT INTO Employees VALUES
(101, 'Alice', 90000, 1),
(102, 'Bob', 40000, 2),
(103, 'Charlie', 85000, 1),
(104, 'David', 45000, 2),
(105, 'Eve', 70000, 3);
Part A: Single-Row Subqueries

1. Using Comparison Operators Retrieve details of employees who earn more than 'David'.

SELECT * FROM Employees

WHERE Salary > (SELECT Salary FROM Employees WHERE Name = 'David');

Logic: The inner query finds David's salary (45000). The outer query finds everyone earning
> 45000.

2. Using Aggregate Functions Find employees who earn more than the average salary of the
entire company.

SELECT Name, Salary

FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Part B: Multi-Row Subqueries
 Savitribai Phule Pune University
3. Using the IN Operator Find employees who work in the 'IT' or 'Sales' departments
(identified by name, not ID).

SELECT Name
FROM Employees
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE DeptName IN ('IT',
'Sales'));

Logic: The inner query returns a list (1, 3). The outer query matches any DeptID present in
that list.

4. Using ANY/ALL Operators Find employees who earn more than all employees in the HR
department.

SELECT Name, Salary

FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE DeptID = 2);

Logic: Finds employees whose salary is strictly higher than the highest salary in Dept 2 (HR).

Part C: Correlated Subqueries

5. Dependent Subquery Find employees who earn more than the average salary of their own
department.

SELECT Name, Salary, DeptID

FROM Employees e1
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees e2
WHERE [Link] = [Link]
);

Logic: For every row processed in the outer query (e1), the inner query calculates the average
for that specific department ID.

Conclusion

In this experiment, we successfully implemented subqueries to perform dynamic data

retrieval.
 Savitribai Phule Pune University
Assignment No 7
Title: Implementation of Stored Procedures, Functions using Cursors for Database Logic
Objective:

To understand and implement server-side database programming concepts.

1. Stored Procedures & Functions: Encapsulate and reuse SQL logic.

2. Cursors: Implement row-by-row processing mechanisms to handle complex logic
that cannot be achieved with standard set-based SQL operations.

Software Required:

 MySQL Workbench (or MySQL Command Line Client)

Theory:

Standard SQL statements work on sets of data (all rows at once), but sometimes applications
require procedural logic to process data row-by-row or to encapsulate complex business
rules.

1. Stored Procedures and Functions

 Stored Procedure: A precompiled collection of SQL statements stored in the

database. It can accept input parameters (IN), return output parameters (OUT), and
perform actions like modifying tables.
 Stored Function: Similar to a procedure but must return a single value. It is typically
used for calculations and can be embedded within standard SQL statements (like
SELECT).

2. Cursors

A Cursor is a database object used to retrieve, manipulate, and traverse a result set one row
at a time.

 Declare: Define the cursor and the query it will use.

 Open: Execute the query and store the result set in memory.
 Fetch: Retrieve the current row into variables.
 Close: Release the memory associated with the cursor.

Practical Demonstration

Pre-requisite: Create a sample table Staff.

CREATE DATABASE LabSessionProc;

USE LabSessionProc;
 Savitribai Phule Pune University
CREATE TABLE Staff (
StaffID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10, 2),
Bonus DECIMAL(10, 2) DEFAULT 0
);

INSERT INTO Staff (StaffID, Name, Salary) VALUES

(1, 'John', 40000), (2, 'Jane', 60000), (3, 'Jim', 35000);
Part A: Stored Procedures

1. Creating a Procedure Create a procedure to update a staff member's salary based on their
ID.

DELIMITER //

CREATE PROCEDURE UpdateSalary(IN s_id INT, IN new_salary DECIMAL(10,2))

BEGIN
UPDATE Staff
SET Salary = new_salary
WHERE StaffID = s_id;
END //

DELIMITER ;

2. Calling a Procedure

CALL UpdateSalary(1, 45000);

SELECT * FROM Staff;

Part B: Stored Functions

3. Creating a Function Create a function that calculates the annual tax (assuming 10%) for a
given salary.

DELIMITER //

CREATE FUNCTION CalculateTax(salary DECIMAL(10,2))

RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE tax DECIMAL(10,2);
SET tax = salary * 0.10;
RETURN tax;
END //
 Savitribai Phule Pune University

DELIMITER ;

4. Using a Function

SELECT Name, Salary, CalculateTax(Salary) AS EstimatedTax FROM Staff;

Part C: Cursors

5. Implementing a Cursor Create a procedure that iterates through the Staff table. If the
salary is below 50,000, give a 10% bonus; otherwise, give a 5% bonus.

DELIMITER //

CREATE PROCEDURE ProcessBonuses()

BEGIN
-- 1. Declare Variables
DECLARE done INT DEFAULT 0;
DECLARE s_id INT;
DECLARE s_salary DECIMAL(10,2);

-- 2. Declare Cursor
DECLARE cur CURSOR FOR SELECT StaffID, Salary FROM Staff;

-- 3. Declare Handler for End of File

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- 4. Open Cursor
OPEN cur;

read_loop: LOOP
-- 5. Fetch Row
FETCH cur INTO s_id, s_salary;

-- Check if loop is finished

IF done THEN
LEAVE read_loop;
END IF;

-- 6. Logic Per Row

IF s_salary < 50000 THEN
UPDATE Staff SET Bonus = s_salary * 0.10 WHERE StaffID = s_id;
ELSE
UPDATE Staff SET Bonus = s_salary * 0.05 WHERE StaffID = s_id;
END IF;
 Savitribai Phule Pune University
END LOOP;

-- 7. Close Cursor
CLOSE cur;
END //

DELIMITER ;

6. Executing the Cursor Procedure

CALL ProcessBonuses();
SELECT * FROM Staff;

Conclusion

We successfully implemented stored procedures and functions to encapsulate reusable

database logic, enhancing code modularity and security.
 Savitribai Phule Pune University
Assignment No 8
Title: Database Triggers.
Implement and test triggers to maintain data integrity in database.
Objective:
 To understand the concept of database triggers as stored procedures.
 To implement BEFORE and AFTER triggers to enforce business
rules.
 To test triggers for INSERT, UPDATE, and DELETE operations.
 To use NEW and OLD references to maintain data integrity.

Software Required:

 MySQL Workbench (or MySQL Command Line Client)

Theory:

A Trigger is a named database object (a set of SQL statements) that is stored in the database
and automatically invoked ("fired") by the database engine when a specific event occurs on a
table.

1. Trigger Timing

 BEFORE: Executes the logic before the modification is applied to the database.
Useful for validation or formatting data.
 AFTER: Executes the logic after the modification is successfully applied. Useful for
logging, auditing, or cascading changes to other tables.

2. Trigger Events

 INSERT: Fires when a new row is added.

 UPDATE: Fires when an existing row is modified.
 DELETE: Fires when a row is removed.

3. Key Keywords

 NEW: Refers to the new row being inserted or the new version of an updated row.
 OLD: Refers to the row being deleted or the original version of a row before an
update.

Practical Demonstration (Lab Exercises)

Pre-requisite: Create a main table (Products) and an audit table (Product_Audit).

CREATE DATABASE LabSessionTriggers;

 Savitribai Phule Pune University
USE LabSessionTriggers;

CREATE TABLE Products (

ProdID INT PRIMARY KEY,
ProdName VARCHAR(50),
Price DECIMAL(10, 2),
Stock INT
);

CREATE TABLE Product_Audit (

AuditID INT AUTO_INCREMENT PRIMARY KEY,
ProdID INT,
ActionType VARCHAR(20),
OldPrice DECIMAL(10, 2),
NewPrice DECIMAL(10, 2),
ChangeDate DATETIME
);

Part A: BEFORE Trigger (Data Validation)

1. Create the Trigger Ensure that no product can be inserted with a negative stock value. If a
user tries to insert a negative number, force it to 0.

DELIMITER //

CREATE TRIGGER Before_Product_Insert

BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
IF [Link] < 0 THEN
SET [Link] = 0;
END IF;
END //

DELIMITER ;

2. Test the Trigger

INSERT INTO Products VALUES (101, 'Keyboard', 500.00, -5);

-- Check result: Stock should be 0, not -5

SELECT * FROM Products;

Part B: AFTER Trigger (Audit Logging)

 Savitribai Phule Pune University
3. Create the Trigger Keep a history of price changes. Whenever a product's price is
updated, log the old and new prices into the Product_Audit table.

DELIMITER //

CREATE TRIGGER After_Product_Update

AFTER UPDATE ON Products
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO Product_Audit (ProdID, ActionType, OldPrice, NewPrice, ChangeDate)
VALUES ([Link], 'PRICE UPDATE', [Link], [Link], NOW());
END IF;
END //

DELIMITER ;

4. Test the Trigger

-- Update the price of Keyboard

UPDATE Products SET Price = 550.00 WHERE ProdID = 101;

-- Check the Audit Table

SELECT * FROM Product_Audit;

Part C: Preventing Deletion (Safety Trigger)

5. Create the Trigger Prevent the deletion of any product that still has stock remaining.

DELIMITER //

CREATE TRIGGER Before_Product_Delete

BEFORE DELETE ON Products
FOR EACH ROW
BEGIN
IF [Link] > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: Cannot delete product with existing stock.';
END IF;
END //

DELIMITER ;

6. Test the Trigger

-- This should fail because stock is 0 (from previous step, unless updated)
 Savitribai Phule Pune University
-- Let's update stock first to test the error
UPDATE Products SET Stock = 10 WHERE ProdID = 101;

-- Try to delete
DELETE FROM Products WHERE ProdID = 101;
-- Output: Error Code: 1644. Error: Cannot delete product with existing stock.

Conclusion

We successfully demonstrated that triggers are a vital mechanism for enforcing business rules
and maintaining data integrity directly at the database layer.
 Savitribai Phule Pune University
Assignment No 9
Title: CRUD Operations using MongoDB
Design and implement basic Create, Read, Update, and Delete (CRUD) operations using
MongoDB. Use the save method and logical operators where necessary.
Objective:

To understand and implement the fundamental operations of a NoSQL database.

1. CRUD Operations: Create, Read, Update, and Delete documents within a collection.
2. Operators: Apply logical ($and, $or) and comparison ($gt, $lt) operators to filter
data.
3. Methods: Utilize insert, save, update, and remove methods for data manipulation.

Software Required:

 MongoDB Shell (mongosh) or MongoDB Compass

Theory:

MongoDB is a NoSQL, document-oriented database. Unlike relational databases (SQL) that

use tables and rows, MongoDB uses Collections and Documents.

 Document: Data is stored in BSON (Binary JSON) format, which consists of field-
value pairs (e.g., {"name": "Alice", "age": 25}).
 CRUD Syntax:
o Create: insertOne(), insertMany(), or save().
o Read: find() returns documents matching a query.
o Update: updateOne(), updateMany(), or save() (replaces existing document).
o Delete: deleteOne(), deleteMany(), or remove().

Practical Demonstration

Pre-requisite: Launch the MongoDB shell and create a new database.

// Switch to (or create) a new database

use LabSessionMongo

Part A: Create Operations (Insert & Save)

1. Using insertMany() Add multiple student records to the students collection.

[Link]([
{ "_id": 1, "name": "Alice", "course": "CSE", "marks": 85 },
{ "_id": 2, "name": "Bob", "course": "ECE", "marks": 60 },
{ "_id": 3, "name": "Charlie", "course": "CSE", "marks": 92 },
{ "_id": 4, "name": "David", "course": "MECH", "marks": 75 }
 Savitribai Phule Pune University
]);

2. Using the save() method The save() method acts as an "Upsert":

 If the document contains an _id that already exists, it updates/replaces the document.
 If the _id is new or missing, it inserts a new document.

// Scenario: Insert a new student (Acts as Insert)

[Link]({ "_id": 5, "name": "Eve", "course": "IT", "marks": 88 });

Part B: Read Operations (Logical & Comparison Operators)

3. Basic Selection Retrieve all students in the 'CSE' course.

[Link]({ "course": "CSE" });

4. Using Comparison Operators ($gt) Find students with marks greater than 80.

[Link]({ "marks": { $gt: 80 } });

5. Using Logical Operators ($and, $or) Find students who are in 'CSE' AND have marks
greater than 90, OR are in 'IT'.

[Link]({
$or: [
{ $and: [ { "course": "CSE" }, { "marks": { $gt: 90 } } ] },
{ "course": "IT" }
]
});

Part C: Update Operations

6. Using updateOne() with $set Update Bob's marks to 65. The $set operator modifies only
the specific field without overwriting the whole document.

[Link](
{ "name": "Bob" },
{ $set: { "marks": 65 } }
);

7. Using save() to Update Replace David's entire document with new data.

// Scenario: David changes course to Civil.

// Note: We must include the same _id (4) to update the existing record.
[Link]({ "_id": 4, "name": "David", "course": "CIVIL", "marks": 75 });
 Savitribai Phule Pune University
Part D: Delete Operations

8. Deleting Documents Remove the student named 'Bob' from the collection.

[Link]({ "name": "Bob" });

// Verify the final state of the collection

[Link]();

Conclusion

We successfully transitioned from relational schemas to a flexible, document-based structure

by implementing CRUD operations in MongoDB using JSON-like syntax.
 Savitribai Phule Pune University
Assignment No 10
Title: Aggregation and Indexing in MongoDB
Design and execute MongoDB queries using aggregation and indexing techniques with
suitable examples.
Objective:
 Understand the concept and application of the MongoDB aggregation framework.
 Learn about different types of indexes in MongoDB (single-field, compound, etc.).
 Gain hands-on experience using aggregation stages like $match , $group , $sort ,
and $limit .
 Practice creating and testing indexes to optimize query performance using
the explain() command.

Software Required

 MongoDB Shell (mongosh) or MongoDB Compass

Theory

As databases grow, simple CRUD operations are insufficient for data analysis and
performance.

1. Aggregation Framework

The aggregation framework models data processing as a Pipeline. Documents enter a multi-
stage pipeline that transforms the documents into aggregated results.

 Pipeline: A series of stages (operations) performed on the data.

 Common Stages:
o $match: Filters documents (similar to SQL WHERE).
o $group: Groups documents by a specified key (similar to SQL GROUP BY).
o $sort: Sorts the documents.
o $project: Selects or reshapes specific fields.
o $sum, $avg, $max: Accumulators used within the $group stage.

2. Indexing

Indexes are special data structures that store a small portion of the collection's data in an
easy-to-traverse form.

 Without Index: MongoDB must perform a collection scan (scan every document) to
select those that match the query.
 With Index: MongoDB limits the inspection to those documents identified by the
index (Index Scan).
 Savitribai Phule Pune University
 Trade-off: Indexes speed up Read operations but slightly slow down Write
operations (insert/update).

Practical Demonstration

Pre-requisite: Create a collection orders with sample data for analysis.

use LabSessionAggIndex

[Link]([
{ _id: 1, product: "Laptop", category: "Electronics", price: 1000, quantity: 5 },
{ _id: 2, product: "Mouse", category: "Electronics", price: 50, quantity: 20 },
{ _id: 3, product: "Chair", category: "Furniture", price: 150, quantity: 10 },
{ _id: 4, product: "Table", category: "Furniture", price: 300, quantity: 5 },
{ _id: 5, product: "Phone", category: "Electronics", price: 800, quantity: 8 },
{ _id: 6, product: "Monitor", category: "Electronics", price: 200, quantity: 10 }
]);

Part A: Aggregation Pipeline

1. Single Stage Aggregation ($match) Filter documents to find only 'Electronics'.

(Functionally similar to find(), but start of a pipeline).

[Link]([
{ $match: { category: "Electronics" } }
]);

2. Multi-Stage Pipeline ($match + $group + $sum) Calculate the total revenue (price *
quantity) for the 'Electronics' category.

 Stage 1: Filter for Electronics.

 Stage 2: Group all remaining documents together (_id: null means one single group)
and sum the revenue.

[Link]([
{ $match: { category: "Electronics" } },
{
$group: {
_id: "$category",
TotalRevenue: { $sum: { $multiply: ["$price", "$quantity"] } }
}
}
]);

3. Grouping and Sorting ($group + $sort) Count how many products exist in each category
and sort by count descending.
 Savitribai Phule Pune University
[Link]([
{
$group: {
_id: "$category",
ProductCount: { $sum: 1 }
}
},
{ $sort: { ProductCount: -1 } }
]);

Part B: Indexing and Performance

4. Analyzing Query Performance (Before Indexing) Use explain("executionStats") to see

how MongoDB searches for data. We search for "Chair".

[Link]({ product: "Chair" }).explain("executionStats");

Observation: Look for totalDocsExamined. If it equals the total number of documents in the
collection (6), it performed a COLLSCAN (Collection Scan), which is inefficient for large
datasets.

5. Creating an Index Create an ascending index on the product field.

[Link]({ product: 1 });

6. Analyzing Query Performance (After Indexing) Run the exact same explain query
again.

[Link]({ product: "Chair" }).explain("executionStats");

Observation: 1. totalDocsExamined should now be 1 (only the matching document). 2. The

stage should change from COLLSCAN to IXSCAN (Index Scan), proving the performance
improvement.

7. Viewing and Dropping Indexes

// List all indexes

[Link]();

// Drop the index

[Link]("product_1");

Conclusion

We effectively utilized the Aggregation Framework to process raw data into meaningful
statistical summaries using multi-stage pipelines.
 Savitribai Phule Pune University
Assignment No. 11
Title: Mini Project

End-to-End Development of a Database Application using SDLC

Objective:

To synthesize all learned database concepts into a functional software application.

1. Application Development: Design and build a full-stack application with a Front

End (user interface) and a Back End (database).
2. SDLC Implementation: Follow standard Software Engineering practices
(Requirements, Design, Implementation, Testing).
3. Database Design: Apply ER modeling and Normalization to ensure an efficient
database structure.

Software Required

 Front End: Python (Flask/Django), Java, PHP, or [Link] (Student's choice).

 Back End: MySQL Workbench or MongoDB Compass.
 Tools: IDE (VS Code/IntelliJ), Diagramming Tool ([Link]/StarUML).

Theory and Methodology (Project Phases)

This mini-project follows the Software Development Life Cycle (SDLC). The final report
must document the following phases in detail.

Phase 1: Requirement Analysis (SRS)

Before coding, the problem must be defined.

 Problem Statement: Clearly define what the system does (e.g., "Library
Management System").
 Functional Requirements: What features must the system have? (e.g., "Login",
"Add Book", "Issue Book").
 Non-Functional Requirements: Performance, Security, Reliability.

Phase 2: Conceptual & Logical Design

This is the blueprint of the database.

 ER Diagram: Draw the Entity-Relationship diagram identifying Entities (tables),

Attributes (columns), and Relationships (1:1, 1:M, M:N).
 Normalization: specific how the tables are structured to satisfy 1NF, 2NF, and 3NF
to reduce redundancy.
 Savitribai Phule Pune University
 Schema Design: Define Primary Keys, Foreign Keys, and Constraints.

Phase 3: Implementation (Coding)

Develop the application by connecting the interface to the database.

 Front End: Create Forms and GUIs for user interaction.

 Back End Connectivity:
o For SQL: Use connectors like mysql-connector-python or JDBC.
 For MongoDB: Use drivers like PyMongo or Mongoose.
 CRUD Integration: Ensure the app can Create, Read, Update, and Delete records via
the GUI.

Phase 4: Testing

Validate that the application works as intended.

 Manual Testing: Manually execute test cases (e.g., "Enter invalid password ->
System should deny access").
 Validation: Check if constraints (like unique emails or positive prices) are enforced
by the database.