Stocks Management System

Project Report
Submitted by
Devansh Gupta [RA2311026010260]
Arnav Anand [RA2311026010259]
Under the guidance of
Dr. G. Dinesh
Assistant Professor, Department of Computational Intelligence
In partial satisfaction of the requirements for the degree of

BACHELOR OF TECHNOLOGY
In
COMPUTER SCIENCE AND ENGINEERING
With Specialization in Artificial Intelligence and Machine Learning

Department of Computational Intelligence

College Of Engineering and Technology
SRM Institute of Science and Technology
KATTANKULATHUR – 603203

May 2025
i
 ABSTRACT

This project aims to develop a Stock Management System. A Stock Management

System can be used by stock exchanges, brokers, investors, and trading platforms
to maintain and manage stock-related data efficiently. The system can be used to
track the performance of stocks, manage buyer portfolios, and calculate profits
and dividends in real-time.

Our system helps in maintaining structured and relational data for markets,
brokers, and buyers, ensuring accurate tracking of stock purchases and sales. The
current system relies heavily on manual record-keeping or fragmented software
solutions, which may lead to errors or delays in decision-making. The proposed
system aims to eliminate these challenges by providing an automated database-
driven backend supported with triggers, cursors, and views for real-time updates
and data consistency.

The intention of proposing such a system is to offer a reliable and scalable

solution that ensures smooth operations in stock transactions and portfolio
management, ultimately contributing to a better trading experience for all
stakeholders.

Keywords: HTML, DBMS, PYTHON, JAVASCRIPT

ii
 TABLE OF CONTENTS

ABSTRACT ii
TABLE OF CONTENTS iii
LIST OF FIGURES vi
LIST OF TABLES viii

CH NO. TITLE PAGE NO.

1 PROBLEM UNDERSTANDING 1
1.1 Problem Statement 1
1.1.1 Problem Description 1
1.2 ER Diagram for Stock Management System 2
1.2.1 Stock Entity 3
1.2.2 Broker Platform Entity 3
1.2.3 Buyer Entity 4
1.2.4 Keys in ER Diagram 5

2 RELATION – SCHEMA AND CREATION OF TABLES 6

2.1 Relationships 6
2.2 Cardinality 6
2.3 Tables Used in the schema 7
2.3.1 Markets Table 7
2.3.2 Stocks Table 8
2.3.3 Types Table 9
2.3.4 BrokerPlatform Table 9
2.3.5 Information Table 10

iii
 2.3.6 Buyer Table 11
2.3.7 Portfolio Table 12
2.3.8 Dividend Table 12
2.3.9 Buys Table 13
2.3.10 Update Table 14
2.3.11 Has Table 15

3. COMPLEX QUERIES 16
3.1 Views 16
3.2 Triggers 17
3.3 Cursors 19

4. PITFALLS IN STOCK MANAGEMENT SYSTEM 21

4.1 Redundancy 21
4.2 Update Anomalies 21
4.3 Violation of 3NF 21
4.4 Increased Storage 21
4.5 Data Integrity Risk 22
4.6 Inefficient Querries 22

5. DEPENDENCIES AND NORMALIZTION IN STOCK 23

MANAGEMENT SYSTEM
5.1 Dependencies in Stock Management System 23
5.1.1 Markets Table 23
5.1.2 Stocks Table 23
5.1.3 Types Table 23
5.1.4 BrokerPlatform Table 23
5.1.5 Information Table 24
5.1.6 Buyer Table 24
5.1.7 Portfolio Table 24

iv
 5.1.8 Dividend Table 24
5.1.9 Buys Table 25
5.1.10 Update Table 25
5.1.11 Has Table 25
5.2 Normalisation in Stock Management System 25
5.2.1 Markets Table 25
5.2.2 Stocks Table 25
5.2.3 Types Table 26
5.2.4 BrokerPlatform Table 26
5.2.5 Information Table 26
5.2.6 Buyer Table 26
5.2.7 Portfolio Table 27
5.2.8 Dividend Table 27
5.2.9 Buys Table 28
5.2.10 Update Table 28
5.2.11 Has Table 28

6. CONCURRENCY AND RECOVERY MECHANISMS 29

6.1 Calculate Total Investments 29
6.2 Distribute Dividends 31
6.3 Applying total commissions 34
6.4 Update Stock Price after Transaction 37
6.5 Update Profit Loss after Purchase 38
6.6 Delete Associate Data on Stock Selection 40

7. CODE 42
7.1 Dashboard.py 42
7.2 Db_config.py 45
7.3 Main.py 46
7.4 Portfolio.py 52

v
8. OUTPUT SCREENSHOT 64
8.1 Broker 64
8.2 Buyer 68

9. RESULTS AND DISCUSSION 71

9.1 Database Integration 71
9.2 Functionality Highlights 71
9.3 Discussion 72
9.4 Security Measures 73
9.5 Data Integrity 73
9.6 User Interface Design 73
9.7 Challenges and Limitations 74
9.8 Future Enhancements 75

10. CONCLUSION 76

Online Course Certificate 77

NPTEL Hall Ticket 77
NPTEL Result Page 79
Scaler 80

vi
 List of Figures

Figure No. Name Page No.

1.1 Entity Relationship Model of Stock Management

2
System
2.1 Database Schema For Stock Management System 7
2.2 Table ‘Market’ as defined in MySQL 8

2.3 Table ‘Stocks’ as defined in MySQL 8

2.4 Table ‘Types’ as defined in MySQL 9

2.5 Table ‘BrokerPlatform’ as defined in MySQL 10

2.6 Table ‘Information’ as defined in MySQL 11

2.7 Table ‘Buyer’ as defined in MySQL 11

2.8 Table ‘Portfolio’ as defined in MySQL 12

2.9 Table ‘Dividend as defined in MySQL 13

2.10 Table ‘Buys’ as defined in MySQL 13

2.11 Table ‘Updates’ as defined in MySQL 14

2.12 Table ‘Has’ as defined in MySQL 15

8.1 Login/Signup Page of Stock Management System 64

8.2 Login Confirmation of Broker 65

8.3 Dashboard Page for Broker 65

8.4 Portfolios of All the Buyers 66

vii
8.5 Transactions of All the Buyers 66

8.6 Broker Actions for a Broker 67

8.7 Stock Management for Broker 67

8.8 Login Confirmation for Buyer 68

8.9 Dashboard page for Buyer 68

8.10 Portfolio Page for Buyer 69

8.11 Stocks page for Buyer 69

8.12 Buy/Sell Stocks Page for Buyer 70

8.13 Transaction Page for Buyer 70

viii
 List of Tables

Table No. Title Page No.

2.3.1 Market 7
2.3.2 Stocks 8
2.3.3 Types 9
2.3.4 BrokerPlatform 9
2.3.5 Information 10
2.3.6 Buyer 11
2.3.7 Portfolio 12
2.3.8 Dividend 12
2.3.9 Buys 13
2.3.10 Updates 14
2.3.11 Has 15

ix
 CHAPTER 1
PROBLEM UNDERSTANDING

1.1 Problem Statement:

Design and implement a comprehensive database management system to support

and manage operations related to stock trading, brokerage platforms, and buyer
portfolios. The system should facilitate the interaction between buyers, broker
platforms, and stocks, ensuring accurate tracking of investments, account details,
transactions, and regulatory compliance.

1.1.1 Problem Description:

In today’s financial environment, individuals invest in stocks through various
broker platforms. Each platform provides access to different stocks, which buyers
can purchase and manage through demat accounts. The proposed system models
this ecosystem by incorporating:

• Stocks: Captures attributes like stock name, price, types

(Intraday/Delivery), quantity, and other details.
• Broker Platforms: Maintain information such as platform name, ID,
website, and the ability to update stock-related information.
• Buyers: Include details like name, email, mobile number, Aadhar ID,
PAN, capital, KYC status, linked bank account, and demat account details.
• Portfolios: Allow buyers to manage stock holdings, including purchase
price, quantity owned, and profit/loss.
• Transactions: Record stock purchases made by buyers, maintaining
relationships between buyers, stocks, and platforms.
• Regulatory & Compliance Info: Ensure KYC and bank details are linked
and validated.

1
1.2 ER Diagram for Stock Management System

Fig 1.1 Entity Relationship Model of Stock Management System

Entities and Attributes

1.2.1 Stock Entity :

• Stock Name: The name of the stock (e.g., Apple Inc., Tesla Inc.).

• Stock ID: A unique identifier for each stock (e.g., a numerical code).

• Price: The current market price of the stock.

• Broker ID: The stock is held by which broker.

• Types: Could represent different types of stocks (e.g., common stock,

preferred stock).

Þ Quality: May refer to the quality of the stock (e.g., blue-chip, penny
stock), or it could be a placeholder for other relevant information.

§ Intraday: Indicates whether the stock is traded within a single

day (intraday) or for longer periods (delivery).

2
 § Delivery: Indicates whether the stock is for delivery (physical
or electronic transfer) or for intraday trading.

Þ Quantity: The total number of shares available.

§ Units: The unit of measurement for the stock (e.g., shares).

§ Lots: The number of shares in a single lot (e.g., a lot of 100

shares).

1.2.2 Broker Platform Entity

• Platform ID: A unique identifier for each broker.

• Websites: The website or online presence of the broker, one broker can
have more than one website.

• Name: The name of the broker or brokerage firm.

• Information: It stores the information about broker and has further sub
attributes.

Þ Broker ID: Identifies the broker

Þ Stock ID: Indicates which stock the broker is having.

Þ Buyer ID: Who has bought stocks from broker.

Þ IPO ID: Uniquely identifies the broker

• Commission: It is the charge or cut which is charged by broker for trading.

3
1.2.3 Buyer Entity

• Buyer ID: A unique identifier for each buyer.

• Name: The full name of the buyer.

• Mobile Number: The buyer's mobile phone number.

• Email: The buyer's email address.

• Aadhar ID: The buyer's Aadhar card number (unique identification

number in India).

• Demate Acc: The buyer's demat account number (used for holding
securities electronically).

Þ Linked Bank Ac: The bank account linked to the buyer's demat
account for fund transfers.

Þ KYC status: Indicates whether the buyer has completed the Know
Your Customer (KYC) verification process.

Þ Profit/Loss: The overall profit or loss incurred by the buyer in their

investments.

• Pan Number: The buyer's Permanent Account Number (used for tax
purposes).

• Capital: The available capital or funds in the buyer's account for investing.

• Portfolio ID: A unique identifier for each portfolio created by the buyer.

Þ Stock Id: Uniquely Identifies the Stock

Þ Stock Name: Name of the stock you have

Þ Purchase Price: At what price you purchased the stock

Þ Quantity: How many stocks you are having

4
1.2.4 Keys in Our ER Diagram

• Stock:

Þ Candidate Keys: (Stock Name, Stock ID)

Þ Primary Key: Stock ID

• Broker:

Þ Candidate Keys: (Broker ID, Broker Platform), (Broker ID,

Websites)

Þ Primary Key: Broker ID

• Buyer:

Þ Candidate Keys: (Buyer ID, Aadhar ID), (Buyer ID, Demate Acc)

Þ Primary Key: Buyer ID

5
 CHAPTER 2
RELATION – SCHEMA AND CREATION OF TABLES

2.1 Relationships :

• Has: Broker Platform has details of buyer, who has purchased stocks from
which broker. Similarly, we can say buyer also has some information about
the broker.
• Updates: Broker Platform updates the stocks table, adding and selling the
stocks.
• Buys: The buyer buys stocks from the Stocks table thus providing a buying
relationship.

2.2 Cardinalities :

• Buyer To Stocks: It has one to many cardinalities as one buyer can have
multiple stocks.
• Buyer To Portfolio ID: It has one to one cardinality as one buyer can have
one portfolio accounting all the stocks.
• Buyer To Broker Platform: It has one to many cardinalities as one buyer
can buy stocks from multiple brokers and multiple platforms.
• Broker Platform To Stocks: It has Many to Many cardinalities as many
platforms may have same or different stocks.
• Broker Platform To Information: It has one to many cardinalities as it
can hold information of many brokers.

6
 Fig 2.1 Database Schema for Stock Management System

2.3 Tables used in the project :

2.3.1. Market Table

Schema:
• Market(MarketId [PK], Type, Country)
• This table stores details about stock markets.
• No foreign keys here; it's a base entity.
• CREATE TABLE Market ( MarketId INT PRIMARY KEY,
Type VARCHAR(50), Country VARCHAR(50) );
7
 Fig 2.2: Table ‘Market’ as defined in MySQL

2.3.2. Stocks Table

Schema:

Stocks(StockId [PK], StockName, Price, BrokerId, MarketId [FK →

Market(MarketId)].

• Relation: Many stocks can be listed on one market, so

MarketId is a foreign key.
• A broker is associated with each stock (BrokerId) but is not
directly linked via FK to BrokerPlatform. (Can be added as
FK if needed.)
• CREATE TABLE Stocks ( StockId INT PRIMARY KEY,
StockName VARCHAR(100), Price DECIMAL(10, 2),
BrokerId INT, MarketId INT, FOREIGN KEY (MarketId)
REFERENCES Market(MarketId) );

Fig 2.3 : Table ‘Stocks’ as defined in MySQL

8
2.3.3. Types Table

Schema:
Types(TypeId [PK], StockId [FK → Stocks(StockId)], Indicator,
Delivery, Quality, Lots, Quantity, Units)
• Relation: One stock can have multiple type attributes like
delivery, lots, and quality.
• StockId used as FK to link with Stocks table (1:M
relationship).
• CREATE TABLE Types ( TypeId INT PRIMARY KEY
AUTO_INCREMENT, StockId INT, Indicator
VARCHAR(50), Delivery VARCHAR(50), Quality
VARCHAR(50), Lots INT, Quantity INT, Units
VARCHAR(20), FOREIGN KEY (StockId) REFERENCES
Stocks(StockId) );

Fig 2.4 : Table ‘Types’ as defined in MySQL

2.3.4. BrokerPlatform Table

Schema:
BrokerPlatform(PlatformId [PK], Name, MobileNumber, Website,
Commission)
• Contains data about trading platforms.
• No FKs; it is a master entity.

9
 • CREATE TABLE BrokerPlatform ( PlatformId INT PRIMARY
KEY, Name VARCHAR(100), MobileNumber VARCHAR(15),
Website VARCHAR(100), Commission DECIMAL(5, 2) );

Fig 2.5 : Table ‘BrokerPlatform’ as defined in MySQL

2.3.5. Information Table

Schema:
Information(InfoId [PK], BrokerId [FK →
BrokerPlatform(PlatformId)], StockId [FK → Stocks(StockId)],
BuyerId)
• Relation: Shows which brokers deal in which stocks for which
buyers.
• Many-to-one from Information → BrokerPlatform
• Many-to-one from Information → Stocks
• CREATE TABLE Information ( InfoId INT PRIMARY KEY
AUTO_INCREMENT, BrokerId INT, StockId INT, BuyerId
INT, FOREIGN KEY (BrokerId) REFERENCES
BrokerPlatform(PlatformId), FOREIGN KEY (StockId)
REFERENCES Stocks(StockId) );

10
 Fig 2.6 : Table ‘Information’ as defined in MySQL

2.3.6. Buyer Table

Schema:
Buyer(BuyerId [PK], Name, Email, MobileNumber, AadharId,
Capital, KYCStatus, PANNo, LinkedBankAccount, DematAccount,
ProfitLoss)
• Core table for investors.
• No foreign keys directly, but referenced in many other tables.
• CREATE TABLE Buyer ( BuyerId INT PRIMARY KEY,
Name VARCHAR(100), Email VARCHAR(100),
MobileNumber VARCHAR(15), AadharId VARCHAR(20),
Capital DECIMAL(15, 2), KYCStatus VARCHAR(20),
PANNo VARCHAR(20), LinkedBankAccount
VARCHAR(50), DematAccount VARCHAR(50), ProfitLoss
DECIMAL(15, 2) );

Fig 2.7 : Table ‘Buyer’ as defined in MySQL

11
2.3.7. Portfolio Table

Schema:
Portfolio(PortfolioId [PK], BuyerId [FK → Buyer(BuyerId)],
StockId [FK → Stocks(StockId)], StockName, Quantity,
PurchasePrice)
• Relation: One buyer can hold multiple stocks (1:M), hence
BuyerId is a FK.
• Also links to Stocks for the related stock.
• CREATE TABLE Portfolio ( PortfolioId INT PRIMARY KEY
AUTO_INCREMENT, BuyerId INT, StockId INT, StockName
VARCHAR(100), Quantity INT, PurchasePrice DECIMAL(10,
2), FOREIGN KEY (BuyerId) REFERENCES Buyer(BuyerId),
FOREIGN KEY (StockId) REFERENCES Stocks(StockId) );

Fig 2.8 : Table ‘Portfolio’ as defined in MySQL

2.3.8. Dividend Table

Schema:
Dividend(DividendId [PK], BuyerId [FK → Buyer(BuyerId)], Date,
Amount)
• Relation: A buyer can receive multiple dividends (1:M), so
BuyerId is a FK.
• CREATE TABLE Dividend ( DividendId INT PRIMARY KEY
AUTO_INCREMENT, BuyerId INT, Date DATE, Amount
DECIMAL(10, 2), FOREIGN KEY (BuyerId) REFERENCES
Buyer(BuyerId) );
12
 Fig 2.9 : Table ‘Dividend as defined in MySQL

2.3.9. Buys Table

Schema:
Buys(BuyerId [PK, FK → Buyer(BuyerId)], StockId [PK, FK →
Stocks(StockId)])
• Represents many-to-many relationship between Buyer and
Stocks.
• Composite PK with both as FKs.
• CREATE TABLE Buys ( BuyerId INT, StockId INT,
PRIMARY KEY (BuyerId, StockId), FOREIGN KEY
(BuyerId) REFERENCES Buyer(BuyerId), FOREIGN KEY
(StockId) REFERENCES Stocks(StockId) );

Fig 2.10 : Table ‘Buys’ as defined in MySQL

13
2.3.10. Updates Table

Schema:
Updates(StockId [PK, FK → Stocks(StockId)], PlatformId [PK, FK
→ BrokerPlatform(PlatformId)])
• Relation: A stock can be available on multiple platforms and
vice versa.
• Many-to-many relationship.
• CREATE TABLE Updates ( StockId INT, PlatformId INT,
PRIMARY KEY (StockId, PlatformId), FOREIGN KEY
(StockId) REFERENCES Stocks(StockId), FOREIGN KEY
(PlatformId) REFERENCES BrokerPlatform(PlatformId) );

Fig 2.11 : Table ‘Updates’ as defined in MySQL

14
2.3.11. Has Table

Schema:
Has(BuyerId [PK, FK → Buyer(BuyerId)], MobileNumber [PK])
• Relation: A buyer can have multiple contact numbers.
• Composite primary key, MobileNumber is not a FK but part of
uniqueness.
• CREATE TABLE Has ( BuyerId INT, MobileNumber
VARCHAR(15), PRIMARY KEY (BuyerId, MobileNumber),
FOREIGN KEY (BuyerId) REFERENCES Buyer(BuyerId) );

Fig 2.12 : Table ‘Has’ as defined in MySQL

15
 Chapter 3
COMPLEX QUERIES

3.1 Views:
1. Create a view showing all buyers with stock details where the
quantity bought is greater than 100.

CREATE VIEW HighVolumeBuyers AS

SELECT Buyer.Name, Stocks.StockName, Portfolio.Quantity,
Portfolio.PurchasePrice
FROM Buyer
JOIN Portfolio ON Buyer.BuyerId = Portfolio.BuyerId
JOIN Stocks ON Portfolio.StockId = Stocks.StockId
WHERE Portfolio.Quantity > 100;

-- To display the view

SELECT * FROM HighVolumeBuyers;

2. Create a view showing broker platforms with their total number of

stocks listed.

CREATE VIEW PlatformStockCount AS

SELECT BrokerPlatform.Name, COUNT(Stocks.StockId) AS
TotalStocks
FROM BrokerPlatform
JOIN Stocks ON BrokerPlatform.PlatformId = Stocks.BrokerId
GROUP BY BrokerPlatform.Name;
SELECT * FROM PlatformStockCount;

16
 3. View showing each stock with average purchase price and total
quantity held across all buyers.

CREATE VIEW StockOverview AS

SELECT Stocks.StockName, AVG(Portfolio.PurchasePrice) AS
AvgPrice, SUM(Portfolio.Quantity) AS TotalHeld
FROM Stocks JOIN Portfolio ON Stocks.StockId = Portfolio.StockId
GROUP BY Stocks.StockName;

3.2 Triggers:
1. Trigger to update ProfitLoss in Buyer table after inserting into
Portfolio.

DELIMITER //

CREATE TRIGGER UpdateProfitLoss AFTER INSERT ON Portfolio

FOR EACH ROW

BEGIN

UPDATE Buyer

SET ProfitLoss = ProfitLoss - (NEW.Quantity * NEW.PurchasePrice)

WHERE BuyerId = NEW.BuyerId;

END //

DELIMITER ;

17
2. Trigger to insert a record into Dividend table when a buyer’s
capital exceeds 10,00,000.

DELIMITER //

CREATE TRIGGER CapitalBonus AFTER UPDATE ON Buyer

FOR EACH ROW

BEGIN

IF NEW.Capital > 1000000 THEN

INSERT INTO Dividend (BuyerId, Date, Amount)

VALUES (NEW.BuyerId, CURDATE(), 500.00);

END IF;

END //

DELIMITER ;

18
3.3 Cursors:
1. Cursor to calculate and display total capital of each buyer.

DELIMITER $$
CREATE PROCEDURE ShowBuyerCapital()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE b_name VARCHAR(100);
DECLARE b_capital DECIMAL(15,2);
DECLARE cur CURSOR FOR SELECT Name, Capital FROM Buyer;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =
TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO b_name, b_capital;
IF done THEN
LEAVE read_loop;
END IF;
SELECT CONCAT('Buyer: ', b_name, ' has Capital: ', b_capital) AS
Info;
END LOOP;
CLOSE cur;
END $$
DELIMITER ;

-- To call:
CALL ShowBuyerCapital();

19
2. Cursor to update stock prices by increasing them by 5% if price <
500.

DELIMITER $$
CREATE PROCEDURE IncreaseStockPrices()
BEGIN
DECLARE s_id INT;
DECLARE s_price DECIMAL(10,2);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT StockId, Price FROM Stocks
WHERE Price < 500;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =
TRUE;

OPEN cur;
price_loop: LOOP
FETCH cur INTO s_id, s_price;
IF done THEN
LEAVE price_loop;
END IF;
UPDATE Stocks SET Price = s_price * 1.05 WHERE StockId =
s_id;
END LOOP;
CLOSE cur;
END $$
DELIMITER ;

-- To execute:
CALL IncreaseStockPrices();

20
 Chapter 4

PITFALLS IN STOCK MANAGEMENT SYSTEM

4.1 Redundancy
StockName is already stored in the Stocks table, so adding it again to
Portfolio, Information, or other related tables results in data duplication.
For every entry referring to the same StockId, the StockName will be
repeated.

4.2 Update Anomalies

If a StockName changes (e.g., due to a rebranding or merger), you have to
update it in all places it's duplicated. Missing one update will lead to
inconsistent data.

4.3 Violation of 3NF

StockName is dependent on StockId, not on the primary key of tables like
Portfolio or Information.

This creates a transitive dependency — violating Third Normal Form

(3NF).

4.4 Increased Storage

Repeating the StockName across many tables increases the size of the
database unnecessarily — especially problematic at scale (thousands of
records).

21
4.5 Data Integrity Risk
If StockName is manually entered or inserted alongside StockId, there’s a
risk of mismatch — incorrect StockName being associated with a valid
StockId.

4.6 Inefficient Queries

Searching or filtering by StockName in multiple tables (instead of joining
with Stocks) adds complexity and inefficiency in your SQL queries.

22
 Chapter 5
DEPENDENCIES AND NORMALISATION IN STOCK
MANAGEMENT SYSTEM

5.1 DEPENDANCY IN STOCK MANAGEMENT SYSTEM

5.1.1 Market Table

Attributes: MarketId, Type, Country
Functional Dependency:
MarketId → Type, Country

5.1.2 Stocks Table

Attributes: StockId, StockName, Price, BrokerId, MarketId
Functional Dependency:
StockId → StockName, Price, BrokerId, MarketId

5.1.3 Types Table

Attributes: TypeId, StockId, Indicator, Delivery, Quality, Lots,
Quantity, Units
Functional Dependency:
TypeId → StockId, Indicator, Delivery, Quality, Lots, Quantity,
Units

5.1.4 BrokerPlatform Table

Attributes: PlatformId, Name, MobileNumber, Website,
Commission
Functional Dependency:
PlatformId → Name, MobileNumber, Website, Commission

23
5.1.5 Information Table
Attributes: InfoId, BrokerId, StockId, BuyerId
Functional Dependency:
InfoId → BrokerId, StockId, BuyerId

5.1.6 Buyer Table

Attributes: BuyerId, Name, Email, MobileNumber, AadharId,
Capital, KYCStatus, PANNo, LinkedBankAccount, DematAccount,
ProfitLoss
Functional Dependency:
BuyerId → Name, Email, MobileNumber, AadharId, Capital,
KYCStatus, PANNo, LinkedBankAccount, DematAccount,
ProfitLoss

5.1.7 Portfolio Table

Attributes: PortfolioId, BuyerId, StockId, StockName, Quantity,
PurchasePrice
Functional Dependencies:
PortfolioId → BuyerId, StockId, StockName, Quantity,
PurchasePrice
StockId → StockName (This is a transitive dependency)

5.1.8 Dividend Table

Attributes: DividendId, BuyerId, Date, Amount
Functional Dependency:
DividendId → BuyerId, Date, Amount

24
 5.1.9 Buys Table
Attributes: BuyerId, StockId
Functional Dependency:
BuyerId + StockId → ∅ (no other attributes)

5.1.10 Updates Table

Attributes: StockId, PlatformId
Functional Dependency:
StockId + PlatformId → ∅

5.1.11 Has Table

Attributes: BuyerId, MobileNumber
Functional Dependency:
BuyerId + MobileNumber → ∅

5.2 NORMALISATION IN STOCK MANAGEMENT SYSTEM

5.2.1 Market Table

Attributes: (MarketId, Type, Country)
1NF: All attributes are atomic.
2NF: Fully functionally dependent on MarketId.
3NF: No transitive dependencies. Already in 3NF.

5.2.2 Stocks Table

Attributes: (StockId, StockName, Price, BrokerId, MarketId)
1NF: Atomic values.
2NF: All non-key attributes depend on StockId.
3NF: No transitive dependencies. Already in 3NF.

25
5.2.3 Types Table
Attributes: (TypeId, StockId, Indicator, Delivery, Quality, Lots,
Quantity, Units)
1NF: Atomic attributes.
2NF: Full functional dependency on TypeId.
3NF: No transitive dependencies. In 3NF.

5.2.4 BrokerPlatform Table

Attributes: (PlatformId, Name, MobileNumber, Website,
Commission)
1NF: All values are atomic.
2NF: Full dependency on PlatformId.
3NF: No transitive dependencies. In 3NF.

5.2.5 Information Table

Attributes: (InfoId, BrokerId, StockId, BuyerId)
1NF: Atomic attributes.
2NF: All attributes fully dependent on InfoId.
3NF: No transitive dependencies.In 3NF.

5.2.6 Buyer Table

Attributes: (BuyerId, Name, Email, MobileNumber, AadharId,
Capital, KYCStatus, PANNo, LinkedBankAccount, DematAccount,
ProfitLoss)
1NF: Atomic values.
2NF: All fields are fully functionally dependent on BuyerId.
3NF: No transitive dependencies. Already in 3NF.

26
5.2.7 Portfolio Table
Attributes: (PortfolioId, BuyerId, StockId, StockName, Quantity,
PurchasePrice)
First Normal Form (1NF):
All attributes are atomic and contain only single values.
1NF is satisfied.
Second Normal Form (2NF):
Primary Key: PortfolioId (assumed to uniquely identify each
portfolio entry)
In the original table, StockName was dependent only on StockId, not
on the full primary key PortfolioId.
This was a partial dependency, violating 2NF.
After removing StockName, all attributes (BuyerId, StockId,
Quantity, PurchasePrice) are fully dependent on PortfolioId.
2NF is now satisfied.
Third Normal Form (3NF):
In the updated table, there are no transitive dependencies.
All non-key attributes depend only on the primary key.
Thus, 3NF is also satisfied.

5.2.8 Dividend Table

Attributes: (DividendId, BuyerId, Date, Amount)
1NF: Atomic.
2NF: Fully dependent on DividendId.
3NF: No transitive dependencies. In 3NF.

27
5.2.9 Buys Table
Attributes: (BuyerId, StockId)
Composite primary key.
1NF, 2NF, 3NF: Each row uniquely identified and no redundancy.
In 3NF.

5.2.10 Updates Table

Attributes: (StockId, PlatformId)
Composite primary key.
1NF, 2NF, 3NF: No partial or transitive dependencies. In 3NF.

5.2.11 Has Table

Attributes: (BuyerId, MobileNumber)
Composite key.
1NF: Atomic.
2NF/3NF: No transitive dependencies. In 3NF.

28
 Chapter 6
CONCURRENCY AND RECOVERY MECHANISMS
6.1 CalculateTotalInvestment()

DELIMITER //

CREATE PROCEDURE CalculateTotalInvestment()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE buyerId INT;

DECLARE totalInvestment DECIMAL(15,2);

DECLARE deadlock_occurred BOOLEAN DEFAULT FALSE;

-- Declare handler for deadlock

DECLARE CONTINUE HANDLER FOR 1213 SET

deadlock_occurred = TRUE;

-- Use a cursor with FOR UPDATE to lock rows

DECLARE buyerCursor CURSOR FOR

SELECT BuyerId FROM Buyer FOR UPDATE;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =

1;

-- Retry loop for deadlock handling

retry_loop: LOOP

SET deadlock_occurred = FALSE;

START TRANSACTION;

29
 OPEN buyerCursor;

read_loop: LOOP

FETCH buyerCursor INTO buyerId;

IF done THEN

LEAVE read_loop;

END IF;

-- Calculate total investment with lock

SELECT SUM(Quantity * PurchasePrice) INTO totalInvestment

FROM Portfolio

WHERE BuyerId = buyerId

FOR UPDATE;

IF totalInvestment IS NULL THEN

SET totalInvestment = 0;

END IF;

UPDATE Buyer

SET Capital = totalInvestment

WHERE BuyerId = buyerId;

END LOOP;

30
 CLOSE buyerCursor;

IF deadlock_occurred THEN

ROLLBACK;

ITERATE retry_loop;

ELSE

COMMIT;

LEAVE retry_loop;

END IF;

END LOOP;

END //

DELIMITER ;

6.2 Distribute Dividends

DELIMITER //

CREATE PROCEDURE DistributeDividends()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE buyerId INT;

DECLARE totalHolding DECIMAL(15,2);

DECLARE dividendAmount DECIMAL(10,2);

31
 DECLARE deadlock_occurred BOOLEAN DEFAULT FALSE;

-- Deadlock handler

DECLARE CONTINUE HANDLER FOR 1213 SET

deadlock_occurred = TRUE;

-- Cursor with FOR UPDATE

DECLARE buyerCursor CURSOR FOR

SELECT DISTINCT BuyerId FROM Portfolio FOR UPDATE;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =

1;

-- Retry mechanism

retry_loop: LOOP

SET deadlock_occurred = FALSE;

START TRANSACTION;

OPEN buyerCursor;

read_loop: LOOP

FETCH buyerCursor INTO buyerId;

IF done THEN

LEAVE read_loop;

END IF;

-- Lock portfolio rows for this buyer

32
 SELECT SUM(Quantity * PurchasePrice) INTO totalHolding

FROM Portfolio

WHERE BuyerId = buyerId

FOR UPDATE;

IF totalHolding IS NULL THEN

SET totalHolding = 0;

END IF;

SET dividendAmount = totalHolding * 0.02;

-- Insert dividend with lock

INSERT INTO Dividend (BuyerId, Date, Amount)

VALUES (buyerId, CURDATE(), dividendAmount);

END LOOP;

CLOSE buyerCursor;

IF deadlock_occurred THEN

ROLLBACK;

ITERATE retry_loop;

ELSE

COMMIT;

33
 LEAVE retry_loop;

END IF;

END LOOP;

END //

DELIMITER;

6.3 Apply Commission To Stock Price

DELIMITER //

CREATE PROCEDURE ApplyCommissionToStockPrices()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE stockId INT;

DECLARE platformId INT;

DECLARE stockPrice DECIMAL(10,2);

DECLARE commissionRate DECIMAL(5,2);

DECLARE deadlock_occurred BOOLEAN DEFAULT FALSE;

-- Deadlock handler

DECLARE CONTINUE HANDLER FOR 1213 SET

deadlock_occurred = TRUE

-- Cursor with FOR UPDATE

DECLARE stockCursor CURSOR FOR

SELECT StockId, PlatformId FROM Updates FOR UPDATE;

34
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =
1;

-- Retry mechanism

retry_loop: LOOP

SET deadlock_occurred = FALSE;

START TRANSACTION;

OPEN stockCursor;

read_loop: LOOP

FETCH stockCursor INTO stockId, platformId;

IF done THEN

LEAVE read_loop;

END IF;

-- Lock broker platform row

SELECT Commission INTO commissionRate

FROM BrokerPlatform

WHERE PlatformId = platformId

FOR UPDATE;

-- Lock stock row

SELECT Price INTO stockPrice

FROM Stocks

WHERE StockId = stockId

35
 FOR UPDATE

UPDATE Stocks

SET Price = stockPrice * (1 + commissionRate / 100)

WHERE StockId = stockId;

END LOOP;

CLOSE stockCursor;

IF deadlock_occurred THEN

ROLLBACK;

ITERATE retry_loop;

ELSE

COMMIT;

LEAVE retry_loop;

END IF;

END LOOP;

END //

DELIMITER ;

36
6.4 UpdateStockPriceAfterTransaction

DELIMITER //

CREATE TRIGGER UpdateStockPriceAfterTransaction

AFTER INSERT ON Information

FOR EACH ROW

BEGIN

DECLARE newPrice DECIMAL(10,2);

DECLARE deadlock_occurred BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR 1213 SET

deadlock_occurred = TRUE;

retry_loop: LOOP

SET deadlock_occurred = FALSE;

START TRANSACTION;

SELECT Price INTO newPrice

FROM Stocks

WHERE StockId = NEW.StockId

FOR UPDATE;

UPDATE Stocks

SET Price = newPrice * 1.05

WHERE StockId = NEW.StockId;

37
 IF deadlock_occurred THEN

ROLLBACK;

ITERATE retry_loop;

ELSE

COMMIT;

LEAVE retry_loop;

END IF;

END LOOP;

END //

DELIMITER;

6.5 UpdateProfitLossAfterPurchase

DELIMITER //

CREATE TRIGGER UpdateProfitLossAfterPurchase

AFTER INSERT ON Portfolio

FOR EACH ROW

BEGIN

DECLARE totalInvestment DECIMAL(15,2);

DECLARE deadlock_occurred BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR 1213 SET

deadlock_occurred = TRUE;

retry_loop: LOOP

38
 SET deadlock_occurred = FALSE;

START TRANSACTION;

SELECT SUM(Quantity * PurchasePrice) INTO totalInvestment

FROM Portfolio

WHERE BuyerId = NEW.BuyerId

FOR UPDATE;

UPDATE Buyer

SET ProfitLoss = totalInvestment - Buyer.Capital

WHERE BuyerId = NEW.BuyerId;

IF deadlock_occurred THEN

ROLLBACK;

ITERATE retry_loop;

ELSE

COMMIT;

LEAVE retry_loop;

END IF;

END LOOP;

END //

DELIMITER ;

39
6.6 DeleteAssociateDataOnStockDeletion

DELIMITER //

CREATE TRIGGER DeleteAssociatedDataOnStockDeletion

AFTER DELETE ON Stocks

FOR EACH ROW

BEGIN

DECLARE deadlock_occurred BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR 1213 SET

deadlock_occurred = TRUE;

retry_loop: LOOP

SET deadlock_occurred = FALSE;

START TRANSACTION;

DELETE FROM Portfolio

WHERE StockId = OLD.StockId;

DELETE FROM Buys

WHERE StockId = OLD.StockId;

DELETE FROM Information

WHERE StockId = OLD.StockId;

IF deadlock_occurred THEN

ROLLBACK;
40
 ITERATE retry_loop;

ELSE

COMMIT;

LEAVE retry_loop;

END IF;

END LOOP;

END //

DELIMITER ;

41
 Chapter 7
CODE

7.1 Dashboard.py

import ttkbootstrap as tb

from ttkbootstrap.constants import *

from stocks import open_stock_window

from portfolio import open_portfolio_window

from broker_actions import open_broker_actions_window

def open_dashboard(username, role):

root = tb.Window(themename="darkly")

root.title(f"Stock Management Dashboard ({role})")

root.geometry("500x350")

tb.Label(root, text=f"Welcome {username} ({role})",

font=("Helvetica", 16)).pack(pady=10)

btn_frame = tb.Frame(root)

btn_frame.pack(pady=20)

if role == "Buyer":

# Buyer buttons

tb.Button(btn_frame, text="View Portfolio",

command=lambda: open_portfolio_window(username),

bootstyle=PRIMARY).grid(row=0, column=0, padx=10,

pady=5)

42
 tb.Button(btn_frame, text="View Stocks",

command=lambda: open_stock_window(username, "view"),

bootstyle=PRIMARY).grid(row=0, column=1, padx=10,

pady=5)

tb.Button(btn_frame, text="View Transactions",

command=lambda: open_portfolio_window(username,
"transactions"),

bootstyle=INFO).grid(row=1, column=0, padx=10, pady=5)

tb.Button(btn_frame, text="Buy Stocks",

command=lambda: open_stock_window(username, "view"),

bootstyle=SUCCESS).grid(row=1, column=1, padx=10,

pady=5)

tb.Button(btn_frame, text="Sell Stocks",

command=lambda: open_stock_window(username, "view"),

bootstyle=DANGER).grid(row=2, column=0, columnspan=2,

pady=5)

else:

# Broker buttons

tb.Button(btn_frame, text="Manage Stocks",

command=lambda: open_stock_window(username, "manage"),

bootstyle=SUCCESS).grid(row=0, column=0, padx=10,

pady=5)

tb.Button(btn_frame, text="Broker Actions",

43
 command=lambda: open_broker_actions_window(username),

bootstyle=WARNING).grid(row=0, column=1, padx=10,

pady=5)

tb.Button(btn_frame, text="View All Portfolios",

command=lambda: open_portfolio_window("all", "portfolio"),

bootstyle=INFO).grid(row=1, column=0, padx=10, pady=5)

tb.Button(btn_frame, text="View All Transactions",

command=lambda: open_portfolio_window("all",
"transactions"),

bootstyle=INFO).grid(row=1, column=1, padx=10, pady=5)

tb.Button(root, text="Logout", command=root.destroy,

bootstyle=DANGER).pack(pady=20)

root.mainloop()

44
7.2 Db_config.py

import mysql.connector

from mysql.connector import Error

def get_connection():

try:

return mysql.connector.connect(

host="localhost",

user="root",

password="Red_Spidy",

database="Stock_Management"

except Error as e:

print(f"Error connecting to MySQL Platform: {e}")

return None

45
7.3 Main.py

### db_config.py

import mysql.connector

def get_connection():

return mysql.connector.connect(

host="localhost",

user="root",

password="Red_Spidy",

database="Stock_Management"

### login.py

import tkinter as tk

from tkinter import messagebox

import ttkbootstrap as ttk

from db_config import get_connection

from dashboard import open_dashboard

def login_window():

root = ttk.Window(themename="darkly")

root.title("Login")

46
 root.geometry("300x250")

ttk.Label(root, text="Username").pack(pady=5)

user_entry = ttk.Entry(root)

user_entry.pack()

ttk.Label(root, text="Password").pack(pady=5)

pass_entry = ttk.Entry(root, show="*")

pass_entry.pack()

def login():

conn = get_connection()

cursor = conn.cursor()

cursor.execute("SELECT * FROM users WHERE username=%s

AND password=%s",

(user_entry.get(), pass_entry.get()))

result = cursor.fetchone()

if result:

root.destroy()

open_dashboard(user_entry.get())

else:

messagebox.showerror("Login Failed", "Invalid credentials")

47
 ttk.Button(root, text="Login", command=login,
bootstyle="success").pack(pady=15)

root.mainloop()

### dashboard.py

import tkinter as tk

import ttkbootstrap as ttk

from buyers import open_buyer_window

from stocks import open_stock_window

from portfolio import open_portfolio_window

def open_dashboard(username):

root = ttk.Window(themename="cyborg")

root.title("Stock Management Dashboard")

root.geometry("500x300")

ttk.Label(root, text=f"Welcome {username}", font=("Helvetica",

16)).pack(pady=10)

ttk.Button(root, text="Manage Buyers",

command=open_buyer_window).pack(pady=5)

ttk.Button(root, text="Manage Stocks",

command=open_stock_window).pack(pady=5)

ttk.Button(root, text="View Portfolio",

command=open_portfolio_window).pack(pady=5)
48
 root.mainloop()

### buyers.py

import tkinter as tk

import ttkbootstrap as ttk

from db_config import get_connection

def open_buyer_window():

win = ttk.Toplevel()

win.title("Buyers")

win.geometry("500x400")

conn = get_connection()

cursor = conn.cursor()

cursor.execute("SELECT * FROM Buyer")

records = cursor.fetchall()1

ttk.Label(win, text="Buyers List", font=("Arial", 14)).pack(pady=10)

for rec in records:

ttk.Label(win, text=f"{rec[1]} | ₹{rec[5]}").pack()

conn.close()

49
### stocks.py

import tkinter as tk

import ttkbootstrap as ttk

from db_config import get_connection

def open_stock_window():

win = ttk.Toplevel()

win.title("Stocks")

win.geometry("500x400")

conn = get_connection()

cursor = conn.cursor()

cursor.execute("SELECT * FROM Stocks")

records = cursor.fetchall()

ttk.Label(win, text="Stocks List", font=("Arial", 14)).pack(pady=10)

for rec in records:

ttk.Label(win, text=f"{rec[1]} | ₹{rec[2]}").pack()

conn.close()

### portfolio.py

import tkinter as tk

50
import ttkbootstrap as ttk

from db_config import get_connection

def open_portfolio_window():

win = ttk.Toplevel()

win.title("Portfolio")

win.geometry("500x400")

conn = get_connection()

cursor = conn.cursor()

cursor.execute("SELECT Portfolio.BuyerId, Buyer.Name,

Portfolio.StockId, Portfolio.Quantity, Portfolio.PurchasePrice FROM
Portfolio JOIN Buyer ON Portfolio.BuyerId = Buyer.BuyerId")

records = cursor.fetchall()

ttk.Label(win, text="Portfolio List", font=("Arial", 14)).pack(pady=10)

for rec in records:

ttk.Label(win, text=f"{rec[1]} owns {rec[3]} of Stock {rec[2]} at

₹{rec[4]}").pack()

conn.close()

### main.py

from login import login

if __name__ == "__main__":

login()

51
7.4 Portfolio.py

import ttkbootstrap as tb

from ttkbootstrap.constants import *

from db_config import get_connection

from tkinter import messagebox

import mysql.connector

def open_portfolio_window(username, view="portfolio"):

try:

conn = get_connection()

cursor = conn.cursor()

win = tb.Toplevel()

win.title(

f"{'All Portfolios' if username == 'all' else username}'s {'Portfolio'

if view == 'portfolio' else 'Transactions'}")

win.geometry("1100x700")

# Summary frame

summary_frame = tb.Frame(win)

summary_frame.pack(fill="x", padx=10, pady=10)

52
 if username == "all": # Broker view

# Get summary stats

cursor.execute("SELECT COUNT(DISTINCT BuyerId) FROM

Portfolio")

investor_count = cursor.fetchone()[0]

cursor.execute("SELECT SUM(Quantity * PurchasePrice) FROM

Portfolio")

total_investment = cursor.fetchone()[0] or 0

cursor.execute(

"SELECT SUM(Quantity * (s.Price - p.PurchasePrice)) FROM

Portfolio p JOIN Stocks s ON p.StockId = s.StockId")

total_pl = cursor.fetchone()[0] or 0

cursor.execute("SELECT SUM(Amount) FROM Dividend")

total_dividends = cursor.fetchone()[0] or 0

tb.Label(summary_frame, text=f"Investors: {investor_count}",

font=("Helvetica", 10)).pack(side="left",

padx=10)

tb.Label(summary_frame, text=f"Total Investment:

₹{total_investment:,.2f}", font=("Helvetica", 10)).pack(
53
 side="left", padx=10)

tb.Label(summary_frame, text=f"Total P/L: ₹{total_pl:,.2f}",

font=("Helvetica", 10), foreground="green" if total_pl >= 0

else "red").pack(side="left", padx=10)

tb.Label(summary_frame, text=f"Total Dividends:

₹{total_dividends:,.2f}", font=("Helvetica", 10)).pack(

side="left", padx=10)

if view == "portfolio":

cursor.execute("""

SELECT b.Name, s.StockId, s.StockName, p.Quantity,

p.PurchasePrice, s.Price,

(p.Quantity * p.PurchasePrice) AS Investment,

(p.Quantity * (s.Price - p.PurchasePrice)) AS ProfitLoss

FROM Portfolio p

JOIN Buyer b ON p.BuyerId = b.BuyerId

JOIN Stocks s ON p.StockId = s.StockId

ORDER BY b.Name, s.StockId

""")

tree = tb.Treeview(win, columns=("buyer", "id", "stock", "qty",

"buy", "current", "invest", "profit"),

54
 show="headings")

tree.heading("buyer", text="Buyer")

tree.heading("id", text="Stock ID")

tree.heading("stock", text="Stock")

tree.heading("qty", text="Qty")

tree.heading("buy", text="Buy Price")

tree.heading("current", text="Current Price")

tree.heading("invest", text="Investment")

tree.heading("profit", text="P/L")

tree.column("buyer", width=120)

tree.column("id", width=80)

tree.column("stock", width=150)

tree.column("qty", width=60)

tree.column("buy", width=100)

tree.column("current", width=100)

tree.column("invest", width=100)

tree.column("profit", width=100)

else: # Transactions view

cursor.execute("""

55
 SELECT t.TransactionId, b.Name, s.StockId, s.StockName,

t.TransactionType, t.Quantity, t.Price,

(t.Quantity * t.Price) AS Amount,

t.TransactionDate

FROM Transactions t

JOIN Buyer b ON t.BuyerId = b.BuyerId

JOIN Stocks s ON t.StockId = s.StockId

ORDER BY t.TransactionDate DESC

LIMIT 100

""")

tree = tb.Treeview(win, columns=("id", "buyer", "stockid",

"stock", "type", "qty", "price", "amount",

"date"), show="headings")

tree.heading("id", text="Txn ID")

tree.heading("buyer", text="Buyer")

tree.heading("stockid", text="Stock ID")

tree.heading("stock", text="Stock")

tree.heading("type", text="Type")

tree.heading("qty", text="Qty")

tree.heading("price", text="Price")

tree.heading("amount", text="Amount")
56
 tree.heading("date", text="Date")

tree.column("id", width=60)

tree.column("buyer", width=100)

tree.column("stockid", width=70)

tree.column("stock", width=120)

tree.column("type", width=70)

tree.column("qty", width=60)

tree.column("price", width=80)

tree.column("amount", width=90)

tree.column("date", width=120)

else: # Buyer view

cursor.execute("SELECT BuyerId FROM Buyer WHERE Name =

%s", (username,))

buyer_result = cursor.fetchone()

if buyer_result is None:

messagebox.showerror("Error", "Buyer record not found")

return

buyer_id = buyer_result[0]

# Get buyer stats

cursor.execute("""
57
 SELECT SUM(p.Quantity * p.PurchasePrice),

SUM(p.Quantity * (s.Price - p.PurchasePrice)),

SUM(d.Amount),

b.Capital

FROM Portfolio p

JOIN Stocks s ON p.StockId = s.StockId

LEFT JOIN Dividend d ON d.BuyerId = p.BuyerId

JOIN Buyer b ON p.BuyerId = b.BuyerId

WHERE p.BuyerId = %s

""", (buyer_id,))

investment, profit_loss, dividends, capital = cursor.fetchone()

investment = investment or 0

profit_loss = profit_loss or 0

dividends = dividends or 0

capital = capital or 0

tb.Label(summary_frame, text=f"Total Investment:

₹{investment:,.2f}", font=("Helvetica", 10)).pack(

side="left", padx=10)

tb.Label(summary_frame, text=f"Profit/Loss: ₹{profit_loss:,.2f}",

font=("Helvetica", 10), foreground="green" if profit_loss >=

0 else "red").pack(side="left",
padx=10)

58
 tb.Label(summary_frame, text=f"Dividends: ₹{dividends:,.2f}",
font=("Helvetica", 10)).pack(side="left",

padx=10)

tb.Label(summary_frame, text=f"Available Capital:

₹{capital:,.2f}", font=("Helvetica", 10)).pack(

side="left", padx=10)

if view == "portfolio":

cursor.execute("""

SELECT s.StockId, s.StockName, p.Quantity,

p.PurchasePrice, s.Price,

(p.Quantity * p.PurchasePrice) AS Investment,

(p.Quantity * (s.Price - p.PurchasePrice)) AS ProfitLoss

FROM Portfolio p

JOIN Stocks s ON p.StockId = s.StockId

WHERE p.BuyerId = %s

ORDER BY s.StockId

""", (buyer_id,))

tree = tb.Treeview(win, columns=("id", "stock", "qty", "buy",

"current", "invest", "profit"),

show="headings")

59
 tree.heading("id", text="Stock ID")

tree.heading("stock", text="Stock")

tree.heading("qty", text="Qty")

tree.heading("buy", text="Buy Price")

tree.heading("current", text="Current Price")

tree.heading("invest", text="Investment")

tree.heading("profit", text="P/L")

tree.column("id", width=80)

tree.column("stock", width=150)

tree.column("qty", width=60)

tree.column("buy", width=100)

tree.column("current", width=100)

tree.column("invest", width=100)

tree.column("profit", width=100)

else: # Transactions view

cursor.execute("""

SELECT t.TransactionId, s.StockId, s.StockName,

t.TransactionType, t.Quantity, t.Price,

60
 (t.Quantity * t.Price) AS Amount,

t.TransactionDate

FROM Transactions t

JOIN Stocks s ON t.StockId = s.StockId

WHERE t.BuyerId = %s

ORDER BY t.TransactionDate DESC

""", (buyer_id,))

tree = tb.Treeview(win, columns=("id", "stockid", "stock",

"type", "qty", "price", "amount", "date"),

show="headings")

tree.heading("id", text="Txn ID")

tree.heading("stockid", text="Stock ID")

tree.heading("stock", text="Stock")

tree.heading("type", text="Type")

tree.heading("qty", text="Qty")

tree.heading("price", text="Price")

tree.heading("amount", text="Amount")

tree.heading("date", text="Date")

tree.column("id", width=60)

tree.column("stockid", width=70)

tree.column("stock", width=120)
61
 tree.column("type", width=70)

tree.column("qty", width=60)

tree.column("price", width=80)

tree.column("amount", width=90)

tree.column("date", width=120)

# Insert data into treeview

for row in cursor.fetchall():

tree.insert("", "end", values=row)

# Add scrollbar

scrollbar = tb.Scrollbar(win, orient="vertical", command=tree.yview)

scrollbar.pack(side="right", fill="y")

tree.configure(yscrollcommand=scrollbar.set)

tree.pack(fill="both", expand=True, padx=10, pady=10)

# View toggle button

if username != "all": # Only show toggle for individual buyers

if view == "portfolio":

tb.Button(win, text="View Transactions",

command=lambda: [win.destroy(),
open_portfolio_window(username, "transactions")],
62
 bootstyle=INFO).pack(pady=10)

else:

tb.Button(win, text="View Portfolio",

command=lambda: [win.destroy(),
open_portfolio_window(username, "portfolio")],

bootstyle=INFO).pack(pady=10)

tb.Button(win, text="Close", command=win.destroy,

bootstyle=DANGER).pack(pady=10)

except mysql.connector.Error as err:

messagebox.showerror("Database Error", str(err))

finally:

if 'cursor' in locals():

cursor.close()

if 'conn' in locals():

conn.close()

63
 Chapter 8
OUTPUT SCREENSHOTS

8.1 Broker

Fig 8.1 Login/Signup Page of Stock Management System

64
Fig. 8.2 Login Confirmation of Broker

Fig 8.3 Dashboard Page for Broker

65
 Fig. 8.4 Portfolios of All the Buyers

Fig. 8.5 Transactions of All the Buyers

66
 Fig. 8.6 Broker Actions for a Broker

Fig. 8.7 Stock Management for Broker

67
8.2 Buyer

Fig. 8.8 Login Confirmation for Buyer

Fig. 8.9 Dashboard page for Buyer

68
Fig. 8.10 Portfolio Page for Buyer

Fig. 8.11 Stocks page for Buyer

69
Fig. 8.12 Buy/Sell Stocks Page for Buyer

Fig. 8.13 Transaction Page for Buyer

70
 Chapter 9
RESULTS AND DISCUSSIONS

9.1 Database Integration

The stock management system integrates seamlessly with a MySQL database,

ensuring efficient storage and retrieval of financial and trading data. The Python-
based backend with ttkbootstrap UI facilitates smooth communication between
the frontend and database, enabling real-time updates and transactions. This
integration supports scalability, security, and reliability in handling stock market
operations with high concurrency requirements.

9.2 Functionality Highlights

• User Registration & Authentication: Secure role-based authentication for

both buyers and brokers with proper KYC verification processes.
• Stock Trading: Comprehensive buy/sell functionality with real-time price
updates and portfolio tracking.
• Portfolio Management: Detailed view of investments including profit/loss
calculations and dividend distributions.
• Broker Operations: Commission management, stock price updates, and
market oversight capabilities.
• Transaction Processing: Robust order execution system with concurrency
control to prevent race conditions.
• Dividend Distribution: Automated dividend calculations and payments
based on portfolio holdings.
• Reporting & Analytics: Comprehensive views of market data, investor
portfolios, and transaction histories.

71
9.3 Discussion

The stock management system successfully meets project requirements by

providing a complete solution for stock trading, portfolio management, and
broker operations. The system implements several key innovations:

• Concurrency Control: Implemented through:

Þ Row-level locking with SELECT ... FOR UPDATE
Þ Transaction isolation in critical operations
Þ Deadlock detection and automatic retry mechanisms
Þ Consistent lock ordering to prevent circular waits
• Data Integrity: Ensured through:
Þ Atomic transactions for all financial operations
Þ Referential integrity constraints in the database schema
Þ Proper error handling and rollback procedures
Þ Input validation at both UI and database levels
• Performance Optimization: Achieved via:
Þ Efficient cursor-based operations for bulk processing
Þ Proper indexing of frequently queried tables
Þ Minimized lock duration through short transactions
Þ Caching of frequently accessed data where appropriate

The user-friendly interface ensures both investors and brokers can navigate and
interact with the system efficiently. The integration with MySQL ensures data
integrity and supports real-time updates for all market operations.

72
9.4 Security Measures

• Role-Based Access Control: Strict separation between buyer and broker

functionality
• Data Encryption: Sensitive financial data encrypted both in transit and at
rest
• Secure Authentication: Password hashing and session management
• Audit Logging: Comprehensive tracking of all financial transactions
• Input Sanitization: Protection against SQL injection and other attacks

9.5 Data Integrity

• Transaction Management: ACID-compliant operations for all financial

transactions
• Referential Integrity: Proper foreign key constraints in database design
• Validation Checks: Business logic validation before database operations
• Recovery Mechanisms: Automatic retry for failed transactions with
deadlock detection
• Backup Procedures: Regular database backups to prevent data loss

9.6 User Interface Design

• Responsive Layout: Adapts to different screen sizes

• Intuitive Navigation: Clear separation between different functional areas
• Data Visualization: Effective presentation of portfolio performance and
market data
• Accessibility: Keyboard navigation and screen reader compatibility
• Real-time Updates: Immediate feedback on trade executions and price
changes
73
9.7 Challenges and Limitations

• High Concurrency Requirements:

Þ Handling simultaneous trade requests during market volatility
Þ Maintaining performance under heavy load
• Data Consistency:
Þ Ensuring accurate portfolio valuations during rapid price changes
Þ Preventing race conditions in order execution
• Regulatory Compliance:
Þ Meeting financial reporting requirements
Þ Maintaining proper audit trails
• System Scalability:
Þ Supporting growing numbers of users and transactions
Þ Handling increasing data volumes efficiently
• Integration Challenges:
Þ Connecting with market data feeds
Þ Interfacing with banking/payment systems

74
9.8 Future Enhancements

• Advanced Analytics:
Þ Predictive modeling for stock performance
Þ Portfolio optimization suggestions
Þ Risk assessment tools
• Mobile Integration:
Þ Native mobile apps for trading on the go
Þ Push notifications for price alerts and order execution
• AI Features:
Þ Intelligent order routing
Þ Fraud detection algorithms
Þ Personalized investment recommendations
• Blockchain Integration:
Þ Secure transaction recording
Þ Smart contracts for dividend distributions
Þ Tokenization of assets
• Extended Market Coverage:
Þ Support for additional asset classes (bonds, derivatives)
Þ International market integration
Þ Cryptocurrency trading capabilities
• Enhanced Risk Management:
Þ Real-time exposure monitoring
Þ Automated margin calls
Þ Circuit breaker implementations
• Social Trading Features:
Þ Investor community platform
Þ Copy trading functionality
Þ Performance sharing and benchmarking

75
 Chapter 10
CONCLUSION

The stock management system provides a robust platform for securities trading
and portfolio management, with particular attention to concurrency control and
data integrity in financial operations. The system architecture successfully
addresses the challenges of high-volume transaction processing while
maintaining data consistency and system responsiveness.

Future development will focus on expanding functionality through advanced

analytics, mobile access, and AI-driven features while maintaining the system's
core strengths in reliability and security. The modular design allows for
incremental addition of new features without disrupting existing operations.

By continuing to focus on performance optimization, user experience, and

regulatory compliance, the system is well-positioned to evolve with the changing
needs of the financial markets and its participants. The implemented concurrency
control mechanisms provide a solid foundation for scaling the system to handle
increasing transaction volumes while maintaining data integrity.

76
 Online Course Certificate

NPTEL Hall Ticket

77
78
NPTEL Result Page

79
Scaler

80