FARM MANAGEMENT SYSTEM

PROJECT REPORT

Submitted by

Aaditva Chauhan – RA2211047010141

Bhavay Sehgal -RA2211047010146

Under the Guidance of

Dr. Beaulah Jeyavathana R

Assistant Professor, Department of Computing Technologies

In partial satisfaction of the requirements for the degree of

BACHELOR OF TECHNOLOGY
in
COMPUTER SCIENCE ENGINEERING

DEPARTMENT OF COMPUTING TECHNOLOGIES

COLLEGE OF ENGINEERING AND TECHNOLOGY

SRM INSTITUTE OF SCIENCE AND TECHNOLOGY

KATTANKULATHUR – 603203

MAY 2024
 ABSTRACT

The project aims to disrupt the agricultural commerce

landscape by introducing a direct online sales platform
for farmers, effectively eliminating the need for
intermediaries. Through our website, farmers can
register themselves and showcase their produce to a
broad audience, completely bypassing the traditional
distribution channels. This direct-to-consumer
approach allows farmers to retain more of their profits
and provides consumers with access to fresher and
more diverse produce. By facilitating transactions
between farmers and consumers, we aim to enhance
transparency and efficiency in the agricultural supply
chain. This initiative promotes fair trade practices by
ensuring that farmers receive fair compensation for
their goods while offering consumers high-quality
products at competitive prices. Moreover, our platform
seeks to empower farmers economically by giving
them greater control over their sales and marketing
strategies. By cutting out middlemen, farmers can
negotiate directly with consumers and establish fair
market prices for their produce. This not only benefits
farmers financially but also fosters a sense of
autonomy and independence in their agricultural
businesses. Overall, our goal is to create a more
equitable and sustainable agricultural market that
benefits both producers and consumers. By leveraging
technology to connect farmers with end consumers, we
aim to create a win-win situation that drives positive
change in the agricultural industry.
iii
 TABLE OF CONTENTS

ABSTRACT iii

Problem Statement 1
Chapter Chapter Name Page No
No

1. Problem understanding, Identification of Entity and 2

Relationships, Construction of DB using ER Model for the
project

2. Design of Relational Schemas, Creation of Database Tables for 4

the project.

3. Complex queries based on the concepts of constraints, sets, 9

joins, views, Triggers and Cursors.

4. Analyzing the pitfalls, identifying the dependencies, and 22

applying normalizations

5. Implementation of concurrency control and recovery 28

mechanisms

6. Code for the project 30

7. Result and Discussion (Screen shots of the implementation 39

with front end.

8. Attach the Real Time project certificate / Online course 41

certificate
 Problem Statement
Our project aims to revolutionize agricultural commerce by establishing an online platform that
connects farmers directly with consumers, eliminating the need for intermediaries. Traditional
agricultural supply chains often involve multiple middlemen, leading to increased costs for
consumers and decreased profits for farmers. Our platform seeks to address this inefficiency by
providing a digital marketplace where farmers can showcase their produce and sell it directly to
consumers.
Through our platform, farmers can create profiles and list their products, including details such as
crop type, quantity, and pricing. Consumers, on the other hand, can browse through a wide variety
of fresh produce, view detailed product information, and make purchases online. By facilitating
direct transactions, we aim to streamline the agricultural supply chain, reduce costs, and increase
profitability for farmers.
One of the key features of our platform is transparency. We provide consumers with information
about the farmers behind the products, including their farming practices, location, and
certifications. This transparency fosters trust and allows consumers to make informed decisions
about their purchases.
Additionally, our platform promotes sustainability by encouraging environmentally friendly
farming practices and supporting small-scale farmers. By enabling farmers to sell directly to
consumers, we reduce the need for extensive transportation and storage, leading to lower carbon
emissions and fresher produce.
Our project also aims to empower farmers economically by providing them with access to a larger
market and fairer prices for their products. By cutting out middlemen, farmers can retain a larger
portion of their profits, leading to increased financial stability and improved livelihoods.
Overall, our platform seeks to transform agricultural commerce by promoting transparency,
sustainability, and economic empowerment. Through the power of technology, we aim to create a
more equitable and efficient agricultural market that benefits both farmers and consumers alike.

1
 Chapter-1
Problem Understanding, Identification of Entity and Relationships,
Construction of DB using ER Model for the project
1.1 Existing System
In the current agricultural landscape, farmers often rely on traditional supply chains
characterized by middlemen and intermediaries for selling their produce. However, this
system comes with several disadvantages. Farmers face challenges such as reduced
profits due to middlemen taking a significant portion of the revenue, limited market
access, price fluctuations, lack of transparency, high post-harvest losses, and
socioeconomic impacts like hindrances to rural development. These shortcomings
highlight the need for innovative solutions that enable farmers to bypass intermediaries
and directly connect with consumers. Such solutions would enhance transparency,
efficiency, and profitability in agricultural markets while empowering farmers to
achieve fair prices for their produce and fostering sustainable rural livelihoods.

1.2 Identification of Entity and Relationship

There are 4 entities in the Farm Management System, they are:
User Entity:

It has name, mobile number, e-mail and address of the user. Mobile Number is
the Primary Key.

Farmer Entity:

It has name, mobile number and e-mail of the farmer. Mobile Number is the
Primary Key.

Login Entity:

It has id, role id, username and password. Id is the Primary Key.

Crop Entity:

It has id, name, amount and price. Id is the Primary Key

Has:
Has relation is creating relationship between User, Login and Farmer entity.
Crop_Trade:
Manages relation is creating relationship between crop, user and farmer entity.

2
Fig 1: ER Diagram of Farm Management System

3
 Chapter-2
Design of Relational Schemas, Creation of Database Tables for the project
2.1 Schema
Login(Login_ID int, Login_username char(50), Login_password char(50))
Customer(Customer_ID int, Customer_Name char(50),Customer_Mobile int,
Customer_Email char(50), Customer_Address char(100))
Crop(Crop_ID int, Crop_name char(50), Crop_amount float, Crop_price float)
Farmer(Farmer_ID int, Farmer_Name char(50), Farmer_Mobile int, Farmer_E-mail
char(50)) Order(Order_ID int, Order_Date int, Crop_Name int, Purchase_amount float)

Figure 2 Schema Diagram for Farm Management System

2.2 Relation Tables

DDL Commands and Results

Create Database Farm;

USE Farm;

4
CREATE TABLE Login (

Login_username varchar(50) unique NOT NULL,

Login_password varchar(50) NOT NULL,

Customer_ID varchar(5) unique,

Farmer_ID varchar(5) unique

);

CREATE TABLE Customer (

Customer_ID varchar(5) unique PRIMARY KEY ,

Customer_Name char(50) NOT NULL,

Customer_Mobile varchar(10) unique NOT NULL,

Customer_Email varchar(50) unique NOT NULL,

Customer_Address varchar(100) NOT NULL,

FOREIGN KEY(Customer_ID) REFERENCES Login(Customer_ID)

);

CREATE TABLE Farmer (

Farmer_ID varchar(5) unique PRIMARY KEY,

Farmer_Name CHAR(50) NOT NULL,

Farmer_Mobile varchar(10) unique NOT NULL,

Farmer_Email varchar(50) unique NOT NULL,

Crop_ID varchar(5) unique,

FOREIGN KEY(Farmer_ID) references Login(Farmer_ID)

);

CREATE TABLE Crop (

Crop_ID INT PRIMARY KEY,

5
 Farmer_ID varchar(5),

Crop_name CHAR(50),

Crop_amount varchar(25),

Crop_price varchar(25),

FOREIGN KEY(Farmer_ID) references Farmer(Farmer_ID)

);

CREATE TABLE order_table (

Order_ID INT PRIMARY KEY,

Crop_ID INT,

Customer_ID varchar(5),

Purchase_amount FLOAT,

order_date date,

FOREIGN KEY(Crop_ID) references Crop(Crop_ID),

FOREIGN KEY(Customer_ID) references Customer(Customer_ID)

);

6
 Figure 3 Create a Tables using DDL Commands for Farm Management System
DML Commands (INSERT) and Results

INSERT INTO `farm`.`Login` (`Login_username`, `Login_password`, `Customer_ID`)

VALUES ('1', 'Vansh', 'password_1', '1026');

INSERT INTO `farm`.`Login` (`Login_username`, `Login_password`, `Farmer_ID`)

VALUES ('2', 'Krishna', 'password_2', '1045');

INSERT INTO `farm`.`Login` (`Login_username`, `Login_password`, `Customer_ID`)

VALUES ('3', 'Yaksh', 'password_3', '1035');

INSERT INTO `farm`.`Login` (`Login_username`, `Login_password`, `Customer_ID`)

VALUES ('4', 'Diva', 'password_4', '1034');

INSERT INTO `farm`.`Login` (`Login_username`, `Login_password`, `Farmer_ID`)

VALUES ('5', 'Samyak', 'password_5', '1095');

INSERT INTO `farm`.`Customer` (`Customer_ID`, `Customer_Name`, `Customer_Mobile`,

`Customer_Email`, `Customer_Address`) VALUES ('1026', 'Vansh V', '7010526173',
'vanshv@gmail.com', 'Srm nagar, chennai');

INSERT INTO `farm`.`Customer` (`Customer_ID`, `Customer_Name`, `Customer_Mobile`,

`Customer_Email`, `Customer_Address`) VALUES ('1035', 'Yakshkumar Vijaykumar',
'9875642233', 'yakshv@gmail.com', '1 street, tambaram');

INSERT INTO `farm`.`Customer` (`Customer_ID`, `Customer_Name`, `Customer_Mobile`,

`Customer_Email`, `Customer_Address`) VALUES ('1034', 'Diva Alpeshkumar',
'9874555200', 'divaa@gmail.com', '2nd avenue, anna nagar')

INSERT INTO `farm`.`Farmer` (`Farmer_ID`, `Farmer_Name`, `Farmer_Mobile`,

`Farmer_Email`) VALUES ('1045', 'Krishna Wadhwani', '7012023654',
'krishnaw@gmail.com');

INSERT INTO `farm`.`Farmer` (`Farmer_ID`, `Farmer_Name`, `Farmer_Mobile`,

`Farmer_Email`) VALUES ('1095', 'Samyak Tripathi', '8694563210',
'samayakt@gmail.com');

INSERT INTO `farm`.`Crop` (`Crop_ID`, `Farmer_ID`, `Crop_name`, `Crop_amount`,

`Crop_price`) VALUES ('45', '1045', 'Rice', '1000kg', 'Rs 100 per kg');

INSERT INTO `farm`.`Crop` (`Crop_ID`, `Farmer_ID`, `Crop_name`, `Crop_amount`,

`Crop_price`) VALUES ('44', '1045', 'Wheat', '500 kg', 'Rs 150 per kg');

INSERT INTO `farm`.`Crop` (`Crop_ID`, `Farmer_ID`, `Crop_name`, `Crop_amount`,

`Crop_price`) VALUES ('41', '1045', 'Millet', '60 kg', 'Rs 60 per kg');

INSERT INTO `farm`.`Crop` (`Crop_ID`, `Farmer_ID`, `Crop_name`, `Crop_amount`,

`Crop_price`) VALUES ('35', '1095', 'Ragi ', '80 kg', 'Rs 250 per kg');

7
INSERT INTO `farm`.`Crop` (`Crop_ID`, `Farmer_ID`, `Crop_name`, `Crop_amount`,
`Crop_price`) VALUES ('32', '1095', 'Jawar', '50 kg', 'Rs 40 per kg');
INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,
`Purchase_amount`, `order_date`) VALUES ('10', '45', '1034', '1000', '2024-02-04');

INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,

`Purchase_amount`, `order_date`) VALUES ('26', '32', '1035', '400', '2024-02-05');

INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,

`Purchase_amount`, `order_date`) VALUES ('12', '35', '1035', '2500', '2024-02-07');

INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,

`Purchase_amount`, `order_date`) VALUES ('15', '44', '1026', '1500', '2024-02-07');

INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,

`Purchase_amount`, `order_date`) VALUES ('20', '41', '1026', '600', '2024-02-08');

8
 Figure 4 Inserting values into Tables using DML Commands for Farm Management System

Chapter-3
Complex queries based on the concepts of constraints, sets, joins, views, Triggers
and Cursors
3.1 Constraint
Query to find farmers who have sold the most amount of a specific crop:
SELECT Farmer_Name FROM Farmer
WHERE Farmer_ID = (
SELECT Farmer_ID FROM Crop
WHERE Crop_name = 'Wheat'
ORDER BY Crop_amount
DESC LIMIT 1
);

Query to find customers who have made the largest purchase

SELECT Customer_Name FROM Customer
WHERE Customer_ID = ( SELECT Customer_ID
FROM order_table
ORDER BY Purchase_amount DESC LIMIT 1
);

Query to find customers who have made purchases on the most recent date:
SELECT Customer_Name FROM Customer WHERE
Customer_ID = ( SELECT Customer_ID FROM order_table
WHERE order_date = ( SELECT MAX(order_date) FROM
order_table) LIMIT 1
);

Query to find crops with the highest total sales amount

SELECT Crop_name FROM Crop WHERE Crop_ID = (
SELECT Crop_ID FROM (SELECT Crop_ID,
SUM(Purchase_amount) AS total_sales FROM order_table
GROUP BY Crop_ID
ORDER BY total_sales DESC LIMIT 1

9
 ) AS max_total_sales
);

Query to add a rating constraint to the order table

SELECT Customer_Name FROM Customer
WHERE Customer_ID IN ( SELECT o.Customer_ID FROM
order_table o WHERE o.Crop_ID IN (
SELECT Crop_ID FROM (
SELECT Crop_ID, COUNT(*) AS num_purchases
FROM order_table
GROUP BY Crop_ID
HAVING num_purchases > 1
) AS multiple_purchases
))
;

Sets
Query to retrieve the total purchase amount for each customer
SELECT c.Customer_Name, SUM(ot.Purchase_amount) AS
3.2 Total_Purchase_Amount FROM Customer c LEFT JOIN
order_table ot ON c.Customer_ID = ot.Customer_ID GROUP
BY c.Customer_Name;

Query to find the customers who have purchased at least one crop and have not
purchased any crops with a price greater than Rs 200 per kg.
SELECT c.Customer_Name, SUM(ot.Purchase_amount) AS
Total_Purchase_Amount FROM Customer c LEFT JOIN
order_table ot ON c.Customer_ID = ot.Customer_ID GROUP
BY c.Customer_Name;

11
 Query to get the Order details with customer name, crop purchased and order
date
SELECT c.Customer_Name, o.Order_ID, cr.Crop_name AS
Crop_Purchased, o.order_date FROM Customer c INNER
JOIN order_table o ON c.Customer_ID = o.Customer_ID
INNER JOIN Crop cr ON o.Crop_ID = cr.Crop_ID;

Query to combine the names of the crops and farmers along with their
quantities and types
SELECT Crop_name AS Name, Crop_amount AS Quantity, 'Crop' AS
Type FROM Crop
UNION
SELECT Farmer_Name AS Name, NULL AS Quantity, 'Farmer'
AS Type FROM Farmer;

Query to find the customers who have purchased either "Rice" or "Wheat"
crops, but not both.
SELECT c.Customer_Name, GROUP_CONCAT(DISTINCT
cr.Crop_name) AS Purchased_Crops FROM Customer c JOIN
order_table ot ON c.Customer_ID = ot.Customer_ID
JOIN Crop cr ON ot.Crop_ID = cr.Crop_ID
WHERE cr.Crop_name IN ('Rice', 'Wheat')
GROUP BY c.Customer_Name
HAVING COUNT(DISTINCT cr.Crop_name) = 1;

3.3 Join
Query to find the total purchase amount for each crop
SELECT c.Crop_name, SUM(ot.Purchase_amount) AS
Total_Purchase_Amount FROM Crop cr LEFT JOIN
order_table ot ON cr.Crop_ID = ot.Crop_ID
GROUP BY cr.Crop_name;
 Query to retrieve the customers who have purchased crops from more than one farmer.
SELECT c.Customer_Name, GROUP_CONCAT
(DISTINCT f.Farmer_Name) AS Purchased_From_Farmers
FROM Customer c JOIN order_table ot ON
c.Customer_ID = ot.Customer_ID
JOIN Crop cr ON ot.Crop_ID = cr.Crop_ID
JOIN Farmer f ON cr.Farmer_ID = f.Farmer_ID
GROUP BY c.Customer_Name
HAVING COUNT(DISTINCT cr.Farmer_ID) >= 1;

Query to find the customers who have not made any purchases.
SELECT c.Customer_Name FROM Customer c
LEFT JOIN order_table ot ON
c.Customer_ID = ot.Customer_ID
 WHERE ot.Customer_ID IS NULL;
Query to retrieve the crop details along with the farmer's name for each order
SELECT ot.Order_ID, cr.Crop_name, cr.Crop_amount,
cr.Crop_price, f.Farmer_Name FROM order_table ot JOIN
Crop cr ON ot.Crop_ID = cr.Crop_ID
JOIN Farmer f ON cr.Farmer_ID = f.Farmer_ID;


Query to retrieve the number of orders placed for each crop SELECT
c.Customer_Name, SUM(ot.Purchase_amount)
AS Total_Purchase_Amount FROM Customer c
LEFT JOIN order_table ot ON c.Customer_ID = ot.Customer_ID
GROUP BY c.Customer_Name;

13
3.4 Views
Query to create a view table to display customer details along with their total
purchase amount.
CREATE VIEW Customer_Total_Purchase AS SELECT
c.Customer_Name, SUM(ot.Purchase_amount) AS
Total_Purchase_Amount FROM Customer c
LEFT JOIN order_table ot ON c.Customer_ID = ot.Customer_ID
GROUP BY c.Customer_Name;

Query to create a view table to display crop and farmer details

CREATE VIEW Crop_Farmer_Details AS SELECT cr.Crop_name,
cr.Crop_amount, cr.Crop_price,
f.Farmer_Name, f.Farmer_Mobile, f.Farmer_Email
FROM Crop cr
JOIN Farmer f ON cr.Farmer_ID = f.Farmer_ID;

Query to create a view table to display orders along with customer details
CREATE VIEW Crop_Farmer_Details AS SELECT
o.Order_ID, c.Customer_Name, cr.Crop_name, o.Purchase_amount,
o.order_date FROM order_table o
JOIN Customer c ON o.Customer_ID = c.Customer_ID
JOIN Crop cr ON o.Crop_ID = cr.Crop_ID;

Query to create a view table to display orders along with customer details
CREATE VIEW Customers_No_Purchases AS SELECT
c.Customer_Name FROM Customer c LEFT JOIN order_table ot ON
c.Customer_ID =
ot.Customer_ID
WHERE ot.Customer_ID IS NULL;
 Query to create a view table to display orders along with customer details
CREATE VIEW Crop_Sales_View AS SELECT
c.Crop_ID, c.Crop_name, SUM(o.Purchase_amount) AS
Total_Sales_Amount FROM Crop c
LEFT JOIN order_table o ON c.Crop_ID = o.Crop_ID
GROUP BY c.Crop_ID, c.Crop_name;

3.5 Trigger
The trigger insert_new_customer automatically inserts a new customer record
into the Customer table whenever a new row is inserted into the Login table
DELIMITER //

CREATE TRIGGER insert_new_customer AFTER INSERT ON

Login FOR EACH ROW
BEGIN
IF (NEW.Customer_ID IS NOT NULL) THEN
INSERT INTO Customer (Customer_ID, Customer_Name,
Customer_Mobile, Customer_Email, Customer_Address)
VALUES (NEW.Customer_ID, 'New Customer',
'1234567890', 'newcustomer@email.com', '1234 Main St');
END
IF;
END;
//
DELIMITER ;
INSERT INTO Login (Login_username, Login_password,
Customer_ID) VALUES ('Ayush', 'password@6', '1070');

The trigger updates the crop amount when an order is placed

DELIMITER //
CREATE TRIGGER reduce_crop_amount
AFTER INSERT ON order_table
FOR EACH

15
 ROW BEGIN
UPDATE Crop
SET Crop_amount = Crop_amount - NEW.Purchase_amount
WHERE Crop_ID = NEW.Crop_ID;
END;
//
DELIMITER ;
Query to invoke trigger:
INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,
`Purchase_amount`, `order_date`)
VALUES ('50', '45', '1035', 40, '2024-02-19');
SELECT * FROM crop;

The trigger sends an error message when the value being purchased is higher than
the available crop quantity
DELIMITER //
CREATE TRIGGER update_crop_amount
AFTER INSERT ON order_table
FOR EACH
ROW BEGIN
DECLARE remaining_amount INT;
SELECT Crop_amount - NEW.Purchase_amount INTO remaining_amount
FROM Crop WHERE Crop_ID = NEW.Crop_ID;
UPDATE Crop SET Crop_amount = remaining_amount WHERE
Crop_ID = NEW.Crop_ID;
ELSE
SIGNAL SQLSTATE '45000';
END IF;
END;
DELIMITER
;
Query to invoke trigger:
INSERT INTO `farm`.`order_table` (`Order_ID`, `Crop_ID`, `Customer_ID`,
`Purchase_amount`, `order_date`)
VALUES ('52', '32', '1026', 60 , '2024-02-19');

16
The trigger helps in preventing deletion on the order table
DELIMITER //
CREATE TRIGGER prevent_deletion
BEFORE DELETE ON order_table
FOR EACH ROW
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Deleting rows from the order_table is
not allowed'; END; // DELIMITER ;

 The trigger helps in preventing deletion on the order table

DELIMITER //
CREATE TRIGGER
update_total_sales_amount AFTER INSERT
ON order_table
FOR EACH
ROW BEGIN
DECLARE total_sales DECIMAL(10,2);
SELECT SUM(Purchase_amount)
INTO total_sales
FROM order_table
WHERE Crop_ID = NEW.Crop_ID;
UPDATE Crop
SET Total_Sales_Amount =
total_sales WHERE Crop_ID
= NEW.Crop_ID;
END;
//

DELIMITER

17
3.6 Cursor:
Query to create a cursor to fetch all the farmers with a Crop amount of less
than 100 kg
DELIMITER //
CREATE PROCEDURE
fetch_farmer_crops() BEGIN
DECLARE done INT DEFAULT FALSE; DECLARE
farmer_id_var VARCHAR(5); DECLARE crop_name_var
CHAR(50); DECLARE crop_amount_var
VARCHAR(25); DECLARE farmer_cursor CURSOR
FOR
SELECT F.Farmer_ID, C.Crop_name,
C.Crop_amount FROM Farmer F
JOIN Crop C ON F.Farmer_ID =
C.Farmer_ID WHERE C.Crop_amount =
100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =
TRUE;
OPEN
farmer_cursor;
farmer_loop: LOOP
FETCH farmer_cursor INTO farmer_id_var, crop_name_var,
crop_amount_var; IF done THEN
LEAVE
farmer_loop; END IF;
-- Do something with fetched data (e.g., print or
process) SELECT farmer_id_var, crop_name_var,
crop_amount_var;
END LOOP;
CLOSE
farmer_cursor; END;
//
DELIMITER ;
CALL fetch_farmer_crops();

Query to create a cursor to fetch all crops available in the inventory

DELIMITER //
CREATE PROCEDURE fetch_inventory_crops() BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE crop_id_var INT; DECLARE crop_name_var CHAR(50);
DECLARE crop_amount_var
VARCHAR(25); DECLARE
crop_price_var VARCHAR(25);
DECLARE
inventory_crops_cursor
CURSOR FOR
18
 SELECT Crop_ID, Crop_name, Crop_amount,
Crop_price FROM Crop;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =
TRUE;
OPEN
inventory_crops_cursor;
inventory_loop: LOOP
FETCH inventory_crops_cursor INTO crop_id_var, crop_name_var,
crop_amount_var, crop_price_var;
IF done THEN
LEAVE
inventory_loop; END IF;
SELECT crop_id_var, crop_name_var, crop_amount_var,
crop_price_var; END LOOP;
CLOSE
inventory_crops_cursor; END;
DELIMITER ;

 Query to create a cursor to fetch all orders placed by a specific customer

DELIMITER //
CREATE PROCEDURE fetch_customer_orders(IN customer_id_param
VARCHAR(5)) BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE order_id_var INT;
DECLARE crop_name_var CHAR(50);
DECLARE purchase_amount_var
FLOAT; DECLARE order_date_var
DATE;
DECLARE customer_orders_cursor CURSOR FOR
 SELECT o.Order_ID, c.Crop_name, o.Purchase_amount, o.order_date
FROM order_table o
JOIN Crop c ON o.Crop_ID = c.Crop_ID
WHERE o.Customer_ID =
customer_id_param;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN
customer_orders_cursor;
orders_loop: LOOP
FETCH customer_orders_cursor INTO order_id_var, crop_name_var,
purchase_amount_var, order_date_var;
IF done THEN
LEAVE
orders_loop; END IF;
SELECT order_id_var, crop_name_var, purchase_amount_var, order_date_var;
END LOOP;
CLOSE
customer_orders_cursor; END;
DELIMITER
;
CALL fetch_customer_orders('1026');

Query to create a cursor all customers who have placed a order

DELIMITER //
CREATE PROCEDURE
fetch_customers_with_orders() BEGIN
 DECLARE done INT DEFAULT FALSE;
DECLARE customer_id_var VARCHAR(5);
DECLARE customer_name_var CHAR(50);
DECLARE customer_mobile_var
VARCHAR(10); DECLARE customer_email_var
VARCHAR(50);
DECLARE customer_address_var
VARCHAR(100);
DECLARE customers_with_orders_cursor CURSOR FOR

19

20
 SELECT c.Customer_ID, c.Customer_Name,
c.Customer_Mobile, c.Customer_Email, c.Customer_Address
FROM Customer c
JOIN order_table o ON c.Customer_ID =
o.Customer_ID GROUP BY c.Customer_ID;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN
customers_with_orders_cursor;
customers_loop: LOOP
FETCH customers_with_orders_cursor INTO customer_id_var,
customer_name_var, customer_mobile_var, customer_email_var, customer_address_var;
IF done THEN
LEAVE
customers_loop; END IF;
-- Do something with fetched data (e.g., print or process)
SELECT customer_id_var, customer_name_var,
customer_mobile_var, customer_email_var, customer_address_var;
END LOOP;
CLOSE
customers_with_orders_cursor; END;
//

DELIMITER

 Query to create a cursor to fetch all orders placed for a specific crop
DELIMITER //
CREATE PROCEDURE fetch_orders_for_crop(IN
crop_id_param INT) BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE order_id_var INT;
DECLARE customer_id_var
VARCHAR(5); DECLARE
purchase_amount_var FLOAT;
20
 DECLARE order_date_var DATE;
DECLARE orders_for_crop_cursor CURSOR FOR
SELECT Order_ID, Customer_ID, Purchase_amount,
order_date FROM order_table
WHERE Crop_ID = crop_id_param;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN
orders_for_crop_cursor;
orders_loop: LOOP
FETCH orders_for_crop_cursor INTO order_id_var,
customer_id_var, purchase_amount_var, order_date_var;
IF done THEN
LEAVE
orders_loop; END IF;
SELECT order_id_var, customer_id_var, purchase_amount_var, order_date_var;
END LOOP;
CLOSE
orders_for_crop_cursor; END;
//
DELIMITER ;
CALL fetch_orders_for_crop(4);
 21

Chapter-4
Analyzing the pitfalls, identifying the dependencies, and applying normalizations
4.1 Login Table

Pitfalls:
a) Redundancy:
The Login table may contain redundant data if multiple users have the same login
credentials.
b) Inconsistency:
Inconsistencies may arise if the same Customer_ID or Farmer_ID is associated
with different login credentials.
c) Inefficiency:
Storing both customer and farmer login information in the same table can lead to
inefficiency.
d) Complexity:
Managing both customer and farmer login information in the same table can
increase complexity and make maintenance more difficult.
Dependencies:
The Customer_ID and Farmer_ID attributes determine the login credentials
(Login_username and Login_password), but the login information does not determine
any other attributes in the table.
Normalization:
No specific normalization changes are required for dependency reasons

4.2 Customer Table:

22
 Pitfalls:
e) Redundancy:
Redundant data may occur if multiple customers have the same contact information.
f) Inconsistency:
Inconsistencies may arise if the same customer has multiple entries with different
contact information.
g) Inefficiency:
Storing non-atomic values like Customer_Address can lead to inefficiency.
Dependencies:
There are no partial or transitive dependencies present in the Customer table.
Normalization:
No specific normalization changes are required for dependency reasons

Farmer Table:

4.3

Pitfalls:
h) Redundancy:
Redundant data may occur if multiple customers have the same contact information.
i) Inconsistency:
Inconsistencies may arise if the same farmer has multiple entries with different contact
information.
j) Inefficiency:
Non-atomic values like Farmer_Email can lead to inefficiency.
Dependencies:
There are no partial or transitive dependencies present in the Customer table.
Normalization:

23
 No specific normalization changes are required for dependency reasons

5.2 Crop Table:

Pitfalls:
a) Redundancy:
Redundant data may occur if multiple customers have the same contact
information.
b) Inconsistency:
Inconsistencies may arise if the same farmer has multiple entries with different
contact information.
c) Inefficiency:
Non-atomic values like Farmer_Email can lead to inefficiency.
Dependencies:
There are no partial or transitive dependencies present in the Customer table.
Normalization:
a) First Normalization Form (1NF):
Original Table:

CREATE TABLE Crop ( Crop_ID

INT PRIMARY KEY,

Crop_name CHAR(50),

Crop_amount FLOAT,

Crop_price FLOAT,

Order_ID INT

);

Normalized Crop Table:

CREATE TABLE Crop_Detail (

24
 Crop_Detail_ID INT PRIMARY KEY,

Crop_ID INT,

Crop_amount VARCHAR(25),

Crop_price VARCHAR(25),

FOREIGN KEY (Crop_ID) REFERENCES Crop(Crop_ID)

);

b) Third Normalization Form (3NF):

Normalized Crop Table:

CREATE TABLE Crop_Details (

Crop_ID INT PRIMARY KEY,

Crop_name CHAR(50),

Crop_price DECIMAL(10, 2)

);

CREATE TABLE Crop_Amount (

Crop_ID INT,

Farmer_ID VARCHAR(5),

Crop_amount VARCHAR(25),

FOREIGN KEY (Crop_ID) REFERENCES Crop_Details(Crop_ID),

FOREIGN KEY (Farmer_ID) REFERENCES Farmer(Farmer_ID)

);

25
4.4
Order Table:

Pitfalls:
d) Redundancy:
Redundant data may occur if multiple orders with the same attributes are entered into
the table.
e) Inconsistency:
Inconsistencies may arise if the same order has multiple entries with different attribute
values.
f) Inefficiency:
Storing non-atomic values like Purchase_amount can lead to inefficiency.
Dependencies:
There are no partial or transitive dependencies present in the Customer table.
Normalization:
Fifth Normalization Form (5NF):
Original Table:

CREATE TABLE order_table ( Order_ID

INT PRIMARY KEY,

Crop_ID INT, Customer_ID varchar(5),

Purchase_amount

FLOAT, order_date date,

FOREIGN KEY(Crop_ID) references Crop(Crop_ID),

FOREIGN KEY(Customer_ID) references Customer(Customer_ID)

);

Normalized Order Table:

CREATE TABLE Crop (

Crop_ID INT PRIMARY KEY, Farmer_ID varchar(5), Crop_name CHAR(50),

 FOREIGN KEY(Farmer_ID) references Farmer(Farmer_ID)

);

CREATE TABLE order_table (

Order_ID INT PRIMARY KEY,

Crop_ID INT, Customer_ID

varchar(5), Purchase_amount

FLOAT, order_date date,

FOREIGN KEY(Crop_ID) references Crop(Crop_ID), FOREIGN

KEY(Customer_ID) references Customer(Customer_ID)

);

select * from order_table;);

27
 Chapter-5
Implementation of concurrency control and recovery mechanisms
5.1 Commit, Rollback and Savepoint code
-- Start a transaction
START TRANSACTION;

-- Insert a new customer

INSERT INTO Customer (Customer_ID, Customer_Name, Customer_Mobile,
Customer_Email, Customer_Address)
VALUES ('1050', 'John Doe', '1234567890', 'john.doe@example.com', '123 Main
St');

-- Update the crop amount

UPDATE Crop SET Crop_amount = '900kg' WHERE Crop_ID = 45;

-- Savepoint
SAVEPOINT before_commit;

-- Commit the transaction

COMMIT;

-- Rollback to savepoint (undo the changes made after the savepoint)

ROLLBACK TO before_commit;

-- Rollback the entire transaction (undo all changes)

ROLLBACK;

5.2 ACID

Atomicity: The code utilizes transactions to ensure that a series of operations either
all succeed (commit) or all fail (rollback). For example, when inserting new data
into tables or updating existing records, these operations are grouped within
transactions. If any part of the transaction fails, all changes made by the transaction
are rolled back, preserving atomicity.

32
Consistency: Database constraints such as foreign key constraints and unique
constraints ensure data consistency by enforcing rules about the relationships between
tables and the uniqueness of data values. These constraints help maintain the integrity of
the data and prevent inconsistencies.
Isolation: Transactions are executed in isolation from each other, meaning that
changes made by one transaction are not visible to other transactions until the
changes are committed. This isolation prevents interference between concurrent
transactions and maintains data integrity.

Durability: Once a transaction is committed, the changes made by the transaction

are permanently saved in the database even in the event of a system failure. This
durability is ensured by the database system's logging and recovery mechanisms,
which maintain a record of committed transactions to recover data in case of failure.

Overall, by using transactions and database constraints, the code adheres to the
principles of ACID to ensure data integrity, consistency, and reliability.

33
 Chapter-6
Code for the Project
from flask import Flask,render_template,request,session,redirect,url_for,flash
from flask_sqlalchemy import SQLAlchemy from flask_login import
UserMixin
from werkzeug.security import generate_password_hash,check_password_hash
from flask_login import login_user,logout_user,login_manager,LoginManager from
flask_login import login_required,current_user

# MY db connection
local_server= True app =
Flask(_name_)
app.secret_key='harshithbhaskar
'

# this is for getting unique user access

login_manager=LoginManager(app) login_manager.login_view='login'

@login_manager.user_loader def
load_user(user_id):
return User.query.get(int(user_id))

#
app.config['SQLALCHEMY_DATABASE_URL']='mysql://username:password@localhost/
databas_table_name'
app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:@localhost/farmers'
db=SQLAlchemy(app)

34
# here we will create db models that is tables
class Test(db.Model):
id=db.Column(db.Integer,primary_key=True)
name=db.Column(db.String(100))

class Farming(db.Model):
fid=db.Column(db.Integer,primary_key=True)
farmingtype=db.Column(db.String(100))

class Addagroproducts(db.Model):
username=db.Column(db.String(50))
email=db.Column(db.String(50))
pid=db.Column(db.Integer,primary_key=True)
productname=db.Column(db.String(100))
productdesc=db.Column(db.String(300))
price=db.Column(db.Integer)

class Trig(db.Model):
id=db.Column(db.Integer,primary_key=True)
fid=db.Column(db.String(100))
action=db.Column(db.String(100))
timestamp=db.Column(db.String(100))

class User(UserMixin,db.Model):
id=db.Column(db.Integer,primary_key=True)
username=db.Column(db.String(50))

35
email=db.Column(db.String(50),unique=True)
password=db.Column(db.String(1000))

class Register(db.Model):
rid=db.Column(db.Integer,primary_key=True)
farmername=db.Column(db.String(50))
adharnumber=db.Column(db.String(50))
age=db.Column(db.Integer)
gender=db.Column(db.String(50))
phonenumber=db.Column(db.String(50))
address=db.Column(db.String(50))
farming=db.Column(db.String(50))

@app.route('/')
def index():
return render_template('index.html')

@app.route('/
farmerdetails')
@login_required def
farmerdetails():
# query=db.engine.execute(f"SELECT * FROM register")
query=Register.query.all()
return render_template('farmerdetails.html',query=query)

@app.route('/agroproducts')
def agroproducts():

36
 # query=db.engine.execute(f"SELECT * FROM addagroproducts")
query=Addagroproducts.query.all()
return render_template('agroproducts.html',query=query)
@app.route('/addagroproduct',methods=['POST','GET'])
@login_required def
addagroproduct(): if
request.method=="POST":
username=request.form.get('username')
email=request.form.get('email')
productname=request.form.get('productname')
productdesc=request.form.get('productdesc')
price=request.form.get('price')

products=Addagroproducts(username=username,email=email,productname=productname,pro
ductdesc=productdesc,price=price) db.session.add(products) db.session.commit()
flash("Product Added","info") return redirect('/agroproducts')

return render_template('addagroproducts.html')

@app.route('/
triggers')
@login_required def
triggers():
# query=db.engine.execute(f"SELECT * FROM trig")
query=Trig.query.all()
return render_template('triggers.html',query=query)

@app.route('/addfarming',methods=['POST','GET'])
@login_required def
addfarming(): if
request.method=="POST":

37
farmingtype=request.form.get('
farming')

query=Farming.query.filter_by(farmingtype=farmingtype).first()
if query:
flash("Farming Type Already Exist","warning")
return redirect('/addfarming')
dep=Farming(farmingtype=farmingtype)
db.session.add(dep) db.session.commit()
flash("Farming Addes","success") return
render_template('farming.html')

@app.route("/delete/<string:rid>",methods=['POST','GET'])
@login_required
def delete(rid):
# db.engine.execute(f"DELETE FROM register WHERE register.rid={rid}")
post=Register.query.filter_by(rid=rid).first() db.session.delete(post)
db.session.commit()
flash("Slot Deleted Successful","warning")
return redirect('/farmerdetails')

@app.route("/edit/<string:rid>",methods=['POST','GET'])
@login_required
def edit(rid):
# farming=db.engine.execute("SELECT * FROM farming")
if request.method=="POST":
farmername=request.form.get('farmername')
adharnumber=request.form.get('adharnumber')
age=request.form.get('age') gender=request.form.get('gender')
phonenumber=request.form.get('phonenumber')

38
 address=request.form.get('address')
farmingtype=request.form.get('farmingtype')
# query=db.engine.execute(f"UPDATE register SET
farmername='{farmername}',adharnumber='{adharnumber}',age='{age}',gender='{gender}',p
honenumber='{phonenumber}',address='{address}',farming='{farmingtype}'")
post=Register.query.filter_by(rid=rid).first() print(post.farmername)
post.farmername=farmername post.adharnumber=adharnumber post.age=age
post.gender=gender post.phonenumber=phonenumber post.address=address
post.farming=farmingtype db.session.commit()
flash("Slot is Updates","success")
return redirect('/farmerdetails')
posts=Register.query.filter_by(rid=rid).first()
farming=Farming.query.all()
return render_template('edit.html',posts=posts,farming=farming)

@app.route('/
signup',methods=['POST','GET']) def signup():
if request.method == "POST":
username=request.form.get('username')
email=request.form.get('email')
password=request.form.get('password')
print(username,email,password)
user=User.query.filter_by(email=email).first()
if user:
flash("Email Already Exist","warning")
return render_template('/signup.html')
# encpassword=generate_password_hash(password)

# new_user=db.engine.execute(f"INSERT INTO user (username,email,password)

VALUES ('{username}','{email}','{encpassword}')")

39
 # this is method 2 to save data in db

newuser=User(username=username,email=email,password=password)
db.session.add(newuser) db.session.commit()
flash("Signup Succes Please Login","success")
return render_template('login.html')

return render_template('signup.html')

@app.route('/login',methods=['POST','GET']) def
login():
if request.method == "POST":
email=request.form.get('email')
password=request.form.get('password')
user=User.query.filter_by(email=email).first()

if user and user.password == password:

login_user(user)
flash("Login Success","primary")
return redirect(url_for('index')) else:
flash("invalid credentials","warning")
return render_template('login.html')

return render_template('login.html')

@app.route('/
logout')
@login_required def
logout():
logout_user()

40
 flash("Logout SuccessFul","warning")
return redirect(url_for('login'))

@app.route('/register',methods=['POST','GET'])
@login_required
def register():
farming=Farming.query.all()
if request.method=="POST":
farmername=request.form.get('farmername')
adharnumber=request.form.get('adharnumber')
age=request.form.get('age')
gender=request.form.get('gender')
phonenumber=request.form.get('phonenumber')
address=request.form.get('address')
farmingtype=request.form.get('farmingtype')

query=Register(farmername=farmername,adharnumber=adharnumber,age=age,gender=gende
r,phonenumber=phonenumber,address=address,farming=farmingtype)
db.session.add(query) db.session.commit()
# query=db.engine.execute(f"INSERT INTO register
(farmername,adharnumber,age,gender,phonenumber,address,farming) VALUES
('{farmername}','{adharnumber}','{age}','{gender}','{phonenumber}','{address}','{farmingtyp
e}')")
# flash("Your Record Has Been Saved","success")
return redirect('/farmerdetails') return
render_template('farmer.html',farming=farming)

@app.route('/
test') def test():
try:
Test.query.all() return 'My
database is Connected' except:
return 'My db is not Connected' app.run(debug=True)

41
 Chapter-7
Results and Discussions

42
43
44
 Chapter-8
Online Course Certificate 1. Vansh V

2. Krishna Wadhwani

45
46