MySQL Library Management System

| December 6, 2022 | MySQL, Projects | 1 Comment

MySQL ka upyog kar library Management software kaise banaye, Library ko aasani se kaise manage kare, trigger ke upyog se books stock kaise manage kare.

इस आर्टिकल मे हमने एक Library की वर्किंग को MySQL Database मे design किया हैं, यह बहुत ही छोटा सा प्रोजेक्ट हैं इसमे हमने एक Project मे जो भी होना चाहिए सभी point को जोड़ा हैं, यदि आप इस Project को अच्छे से समझ जाते हैं तो आप निश्चय ही खुद से कोई भी Project बना पाएंगे । इस प्रोजेक्ट को हमने कुल 7 भागों मे विभाजित किया है । और प्रत्येक भाग का एक Video भी बनाया हैं ताकि आप और अच्छे से समझ सके ।

इस Project को इस तरह से Design किया गया हैं की आप Front end पर बहुत ही कम Coding कर भी आसानी से Project बना सकते हैं । इस Project मे हमने Entry Level Check के लिए Triggers का भी उपयोग किया हैं जिससे की Data की Accuracy बनी रहती हैं । साथ मे कुछ अन्य Trigger का उपयोग कर हमने Data को भी इस तरह से Manage किया हैं ताकि आपको Report बनाने के लिए बड़ी बड़ी SQL लिखने की आवश्यकता न हो।

इस Project मे आपको किस तरह से Calculation लिखना हैं, किस तरह से Validation लगाना हैं । एक Table के data को दूसरी टेबल मे किस तरह से Update करना हैं । Table मे data Insert होने से पहले कोई बदलाव आदि करने हो तो उन्हे कैसे किया जाता हैं आदि सभी प्रकार के कार्य इस प्रोजेक्ट मे किए गए हैं । चलिए शुरू करते हैं ।

Project Overview

यह एक Library Management Software हैं। इसमे हमारे पास कुछ Books हैं जिनको की हम Members को Issue करते हैं , कुछ समय बाद Member के द्वारा जब book को दे दिया जाता हैं तब उस Book को Receive कर लिया जाता हैं। System के द्वारा देने और प्राप्त होने की Date के बिच के Difference निकाल कर प्रति दिन का जो भी Rent हो उससे दिनों की सख्या मे गुणा कर राशि Calculate की जाती हैं ।

इस प्रोजेक्ट मे Book का Stock Manage हो जाता हैं । और साथ मे कितनी Book Member के पास हैं उनकी जानकारी हमए प्राप्त हो जाती हैं । साथ जब भी आप इस Project को शुरू करे तब का Stock Entry के लिए Opening का भी Provision किया गया हैं। इसके साथ जब Book Stock मे नहीं होगी तब आप Book Issue नहीं कर पाएंगे इस तरह के Check भी लगाए गए हैं । इन सात भाग मे अपने इस प्रोजेक्ट को Distribute किया है ।

1. Table Structure For Library Management System

1.1 Book

इस Table मे Book की Entry होगी, इसमे हमने Book का नाम और Opening , Issue, Receive, Closing के ही कॉलम लिए हैं । आप अपनी Requirement के अनुसार इसमे ISBN , Location Book Price आदि और भी जो कॉलम लेना चाहे ले सकते हैं। Implement करते समय हमने बाद मे इसमे Rate का कॉलम भी जोड़ा हैं । 

CREATE TABLE `mst_book` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `book_nm` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 `op` int(5) NOT NULL,
 `iss` int(5) NOT NULL,
 `rec` int(5) NOT NULL,
 `cl` int(5) NOT NULL,
 `rate` decimal(9,2) NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

आपको इसमे book_nm , op, rate मे ही एंट्री Front end से करवाना हैं । iss, rec, cl आदि column की वैल्यू Trigger के द्वारा Update होगी।

1.2 Member

आप जिन्हे भी Book Issue करना चाहते हैं चाहे वह Student हो, Teacher हो या Staff हो , आप सभी के Entry इस Table मे कर सकते हैं । इसमे मेने id के बाद केवल Member Name का ही Column लिया हैं । आप इसमे Mobile Number, Member Type, Address, City आदि की जानकारी भी ले सकते हैं । 

CREATE TABLE `mst_member` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `mem_nm` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

1.3 Issue / Receive

इस एक ही Table से हमने Book Issue और Receive करने का कार्य किया हैं ।

  • Primary Key : इस Table मे id को Primary किया लिया हैं और Auto Increment सेट किया हैं ।
  • Foreign Key : इस टेबल मे book_id और mem_id को Foreign key लिया हैं ।

Foreign Key Constrain को समझने के लिए क्लिक करे । 

CREATE TABLE `trn_issue` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `tr_dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `book_id` int(10) NOT NULL,
 `mem_id` int(10) NOT NULL,
 `rec_flag` int(1) NOT NULL DEFAULT '0',
 `rec_dt` datetime NOT NULL,
 `no_days` int(5) NOT NULL DEFAULT '0',
 `rate` decimal(9,2) NOT NULL DEFAULT '0.00',
 `amt` decimal(9,2) NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`id`),
 KEY `cont_book` (`book_id`),
 KEY `cont_mem` (`mem_id`),
 CONSTRAINT `cont_book` FOREIGN KEY (`book_id`) REFERENCES `mst_book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `cont_mem` FOREIGN KEY (`mem_id`) REFERENCES `mst_member` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Related Video : MySQL Table Structure For Library Management System (Part 1/7)

2. Before Triggers for Library Management System

Closing calculate करने के लिए हमने Before Insert और Before Update Trigger लिखे हैं । यह दोनों ही Calculation Book Table पर होंगे ।

जाने MySQL Triggers के बारे मे


CREATE TRIGGER `mst_book_bi` BEFORE INSERT ON `mst_book`
 FOR EACH ROW BEGIN

    SET new.cl = new.op - new.iss + new.rec; 

END

#---------------------

CREATE TRIGGER `mst_book_bu` BEFORE UPDATE ON `mst_book`
 FOR EACH ROW BEGIN

    SET new.cl = new.op - new.iss + new.rec; 

END

Related Video : MySQL Before Triggers for Library Management System (Part 2/7)

3. After Trigger for Library Management System

Book Table मे iss यानि की Issue और rec यानि की Receive को Update करने का कार्य After Insert, After Update और After Delete Trigger से किया गया हैं यह तीनों ही Trigger Issue वाली table पर लिखे गए हैं । 

CREATE TRIGGER `trn_issue_ai` AFTER INSERT ON `trn_issue`
 FOR EACH ROW BEGIN

UPDATE mst_book SET iss = iss + 1 WHERE id = new.book_id;
UPDATE mst_book SET rec = rec + new.rec_flag WHERE id = new.book_id;

END

#---------------------

CREATE TRIGGER `trn_issue_au` AFTER UPDATE ON `trn_issue`
 FOR EACH ROW BEGIN

UPDATE mst_book SET iss = iss - 1 WHERE id = old.book_id;
UPDATE mst_book SET rec = rec - old.rec_flag WHERE id = old.book_id;

UPDATE mst_book SET iss = iss + 1 WHERE id = new.book_id;
UPDATE mst_book SET rec = rec + new.rec_flag WHERE id = new.book_id;

END

#---------------------

CREATE TRIGGER `trn_issue_ad` AFTER DELETE ON `trn_issue`
 FOR EACH ROW BEGIN

UPDATE mst_book SET iss = iss - 1 WHERE id = old.book_id;
UPDATE mst_book SET rec = rec - old.rec_flag WHERE id = old.book_id;

END

Related Video : MySQL After Triggers for Library Management System (Part 3/7)

4. Validation for Library Management System

जितनी बुक हमारे पास स्टॉक मे है उससे ज्यादा इशू नहीं होना चाहिए , या कहे की यदि किसी Book का Closing Stock 0 हो तब Book Issue नहीं होना चाहिए । तब हम Signal SQLSTATE ‘45000’ का उपयोग कर Entry Time Check लगा कर Insert को रोक सकते हैं।

यह कार्य Before Insert पर ही किया जाता हैं ।

साथ मे जो Book Master मे जो rate हैं उस Transaction table मे अपडेट करने का कार्य भी इसी Trigger से करवाया गया हैं । 

CREATE TRIGGER `trn_issue_bi` BEFORE INSERT ON `trn_issue`
 FOR EACH ROW BEGIN

DECLARE li_cl integer DEFAULT 0;
DECLARE ld_rate decimal(9,2) DEFAULT 0;

SELECT cl INTO li_cl
FROM mst_book WHERE id = new.book_id;

#For Stock 0 Message 
IF (li_cl < 1) THEN
	SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Book Not in Stock';
END IF;


#For Set Book Rate From Master 
SELECT rate into ld_rate
FROM `mst_book` where id = new.book_id;

set new.rate = ld_rate; 


END

Related Video : MySQL Validation for Library Management System (Part 4/7)

5. Old & New Keyword for Library Management System

जब भी Update करने पर किसी कॉलम को Update करने पर उसके Update करने के पहले की value और bad की वैल्यू को Compare कर कोई भी operation करना हो तब हम इस प्रकार से Trigger लिखते हैं । इस तरह के Trigger Before Update पर लिखे जाते हैं ।

  • इसमे जब जब Rec Flag पहले 0 हो और बाद मे 1 हो तो यह मन जाता हैं की बुक Receive की गई हैं ।
  • और जब पहले 1 और बाद मे 0 हो तो यह माना जाता हैं की आपने जो receive की entry की थी उसे पास Reverse कर रहे है या आप Edit कर Receive की Entry हटाना चाहते हैं । 
CREATE TRIGGER `trn_issue_bu` BEFORE UPDATE ON `trn_issue`
 FOR EACH ROW BEGIN

IF (old.rec_flag = 0 and new.rec_flag = 1) THEN
	SET new.rec_dt = now();
        set new.no_days = datediff(new.rec_dt, new.tr_dt) + 1;
	set new.amt = new.no_days * new.rate;
ELSEIF (old.rec_flag = 1 and new.rec_flag = 0) THEN
	SET new.rec_dt = '0000-00-00';
END IF;

END

Related Video : 86. MySQL OLD & NEW Keywords for Library Management System (Part 5/7)

6. Calculation for Library Management System

हमे इस प्रोजेक्ट मे तीन Calculation Issue वाली टेबल पर करना हैं

  • 1. No of Day : इसमे हमे Issue Date और Receive Date के बिच के Difference को no_days वाले कॉलम मे Update करना हैं ।
  • 2. Rate Pick from Master : हमे Book Issue करते समय Book Master से Rate को लाकर Issue Table मे Store करना हैं ।
  • 3. Find Amount : अब जो हम No of Days और Rate लाए हैं दोनों का गुना कर हमे राशि Calculate करना हैं ।

Rate Pick करने के लिए हमने Issue Table पर Before Insert Trigger मे निम्न Code लिखा था जो की आपको Validation वाले पॉइंट मे मिल जाएगा। इसमे केवल उतना सा code ही लिखा गया है । 

SELECT rate into ld_rate
FROM `mst_book` where id = new.book_id;

set new.rate = ld_rate;

No Of day और Amount Calculate करने के लिए हमने 5 वे पॉइंट मे Before Update Trigger जो की Issue table पर लिखा गया हैं उसमे निम्न Code लिखा हैं । 

 set new.no_days = datediff(new.rec_dt, new.tr_dt) + 1;
 set new.amt = new.no_days * new.rate;

इसमे पहली Line से Day Calculate किए हैं और दूसरी लाइन से Amount Calculate किया हैं ।

Related Video : MySQL Calculations for Library Management System (Part 6/7)

7. Report SQL for Library Management System

Report के लिए कुछ छोटी छोटी SQL लिखी गई हैं । SQL Execute करने से पहले का Data इस प्रकार हैं ।

Q 1. कितनी Books हमारे पास Stock मे हैं ?

SELECT * FROM `mst_book` WHERE cl > 0 ORDER BY `id` ASC

Q 2. कितनी Books ऐसी हैं जिनका Stock हमारे पास नहीं हैं ?

SELECT * FROM `mst_book` WHERE cl = 0 ORDER BY `id` ASC

Q 3. कितनी ऐसी Books हैं जिनको हमने Issue किया है । 

SELECT * FROM `mst_book` WHERE iss > 0 ORDER BY `id` ASC

Q 4. कितनी ऐसी Books हैं जिनको हमने Issue किया है , Book के नाम और Member के नाम भी बताना हैं । 

SELECT a.tr_dt, b.book_nm, c.mem_nm 
FROM `trn_issue` a 
left join mst_book b on a.book_id = b.id 
left join mst_member c on a.mem_id = c.id 
WHERE a.rec_flag = 0
ORDER BY a.tr_dt

Q . कितनी ऐसी Books हैं जिनको हमने Issue किया है जिनको 7 दिन से ज्यादा का समय हो गया हो?

इस SQL को 05-12-2022 को Execute किया हैं उस अनुसार दिन Calculate हुवे हैं । 

SELECT a.tr_dt, b.book_nm, c.mem_nm , ( datediff(now(), tr_dt) + 1 ) as no_days 
FROM `trn_issue` a 
left join mst_book b on a.book_id = b.id 
left join mst_member c on a.mem_id = c.id 
WHERE a.rec_flag = 0 and ( datediff(now(), tr_dt) + 1 ) > 7 
ORDER BY a.tr_dt

Related Video : MySQL Report SQL for Library Management System (Part 7/7)

इस Article से Related आपकी कोई भी Query हो तो आप निसंकोच Comment करे, यह Article कैसा लगा बताना न भूले , मिलते हैं Next Article मे , Thanks For Reading ।

सीखे VIDEO के माध्यम से

  1. सरल भाषा मे Programming सीखने के लिए Imagination Code Channel को Subscribe करे।
  2. MySQL Telegram Channel से जुड़े और पाए Article & Video प्रतिदीन
  3. MySQL Beginner & Advance Playlist in Hindi
  4. MySQL Overview in One Video

हमारे अन्य आर्टिकल

Related Posts

One Comment

Add a Comment

Your email address will not be published. Required fields are marked *