Blood bank management system (3).pptx

blood bank management system using DBMS & SQL

1. 49- Lavanya Prasad 51- Shriya Raina 53- Aishwarya Salian

2. INDEX Introduction & Scope Problem Statement Objectives Requirement Analysis Relational Database Design ER Diagram Schema Tables SQL database SQL Queries Conclusion

3. INTRODUCTION The purpose of the blood bank management system (BBMS) is to simplify and automate the process of searching for blood in case of emergency and maintain the records of blood donors, recipients, blood donation programs and blood stocks in the bank. The proposed system gives the procedural approach of how to bridge the gap between Recipient, Donor, and Blood Banks. This Application will provide a common ground for all the three parties. SCOPE The system provides a high-end, efficient, highly available and scalable system to bridge the gap between the donors and the recipients and to reduce the efforts required to search for blood donors. From this system, there are several type of report that can be generated such as blood stock report, donor’s gender report and the total of blood donation according to months and year. The system also can give the information to the donor about blood analysis test result for each time the donor makes contribution. Hence, BBMS will make the blood bank stock more systematic and manageable.

4. Our blood bank database system , consists of all the attributes of the blood banks, donors and the recipients. It has several contents related to its real objectives ,the requirement analysis, data tables, its design and a whole planned ER diagram, further it explains the system by providing SQL queries & database. Our Blood Bank Management system takes care of all the information about the donors , hospital records , and check availability of blood stocks. All the orders done by hospitals for our recipients has been taken care of. Donors donate their blood ,which is being collected in various groups of Blood banks and all their personal information is kept for the registration. Receptionist deals with Registration process and works under blood banks to keep a track of all the donors, and available blood types. This study found out that this system puts an end to the fear caused during an emergency period and reduces the hassle of manual paper-based data entries. Blood Bank gives a platform to save life , in need of emergency, and also a chance to be helpful to someone. PROBLEM STATEMENT

5. The goals and objectives of the Blood Bank Management System are as follows: To provide a means for the blood bank to publicize and advertise blood donation programs. To allow the probable recipients to make search and match the volunteer donors, and make request for the blood. To provide an efficient donor and blood stock management functions to the blood bank by recording the donor and blood details. To improve the efficiency of blood stock management by alerting the blood bank staffs when the blood quantity is below it par level or when the blood stock has expired. To provide synchronized and centralized donor and blood stock database. To provide immediate storage and retrieval of data and information. OBJECTIVES

6. 1 . DONOR- Primary key- donor_id donor_id, name, sex, age, phno, address 2 . BLOOD- Primary key- code code, blood_group, cost 3 . HOSPITAL- Primary key- name name, address, phno RELATIONAL DATABASE DESIGN

7. 4. BLOODBANK- Primary key- bno bno, available_bloodtypes, orders 5. RECEPTIONIST- Primary key- emp_id emp_id, name, address, salary, phno

8. Donor - In donor, we have 5 attributes of donor_id, name , age, sex, phno & address; the primary key here is donor_id . Donor register for themselves and donate blood in our banks. They can contact various hospitals to get them connected to blood banks , or online blood banks could be found easily to donate and be a part of our society Blood bank - bno, available_bloodtypes, orders are the 3 attributes we have here, where as bno is our primary key. Blood bank is a link between recipients and the hospital. Donors share their blood here, hospitals give orders for blood needed in emergency and also prior if a particular blood type is not available . Hospital - name, address, phno of hospitals are the 3 attributes which is needed for them to get connected to blood banks in the city. n ame is given as a primary key as it gives the direct information of the hospital and the blood bank. Hospitals try to find blood but, not always they get at right time, they definitely keep a check on blood bank near their areas and supply to their banks. They even encourage people visiting there to donate their healthy blood and also make certain measures like blood donating camps & its awareness. REQUIREMENT ANALYSIS

9. bl ood - the 3 attributes are code, blood_group & cost here code is our primary key. Blood is the main content in this system, blood groups have been divided into each type. Hospitals also keep a track of all the blood in their banks and the orders of certain type is done by the means of code . We have tried to ensure that there is clear record of transactions between banks and the hospitals. Receptionist - 5 attributes of emp_id , name , address, salary, phno are mentioned here. All the employees of blood bank play a major role to connect with the hospitals in emergency and acts fast and decisively in that tense situation. They held various blood bank camps in remote areas and also maintain the hygiene , and security of such medical promises.

10. ER DIAGRAM-

11. SCHEMA TABLES DONOR Primary key – donor_id donor_id name sex age phno address 2. BLOOD Primary key – code code blood_group cost

12. 3. HOSPITAL Primary key – name name address phno 4. BLOODBANK Primary key – bno bno available_bloodtypes orders 5. RECEPTIONIST Primary key – emp_id emp_id name address salary phno

13. SQL QUERIES & DATABASE

14. 1.View the contents of DONOR 2. View the contents of BLOOD 3.V iew the contents of HOSPITAL

15. 4. Vi ew the contents of BLOODBANK 5. View the contents of RECEPTIONIST 6. Display the names of the hospitals in Mumbai 7. Update Bloodbank & add bloodtype AB where bno=116

16. 8. Alter table Donor and add a column disease 9. Display names of all the female donors who live in Pune 10. Display names of bloodbank employees whose salary is between 25k-30k 11. Display names of the hospitals in alphabetical order

17. 12. Display donors whose names do not start with an S 13. Count the no of hospitals in Mumbai 14. Display the total salary given to all bloodbank employees 15.Display the average salary.

18. 17. Display the minimum salary of an employee 18. Display the maximum salary of an employee 19. Count the no of male donors 20. Display all hospital names

19. 21. Display cities having hospitals. 22. Display donors in desc. order of their names 23. Count the no of bloodbanks which have all bloodtypes

20. 24. Display the bloodbank code which have all bloodtypes 25. Rename Donor to Donors

21. 26.Create view donor_contact and display it. 28. Create view bloodbank_details and display it. 27. Create and display view bloodbank_orders

22. 29. What is the average age of the donors 30. Alter table donor & drop the column diseases 31.Display bloodbank employee names whose salary<25k

23. 32. Perform full outer join on tables donors and blood. 33. Perform left outer join on bloodbank and blood

24. 34.Perform right outer join on tables bloodbank and blood 35.Perform NATURAL JOIN on donors and blood

25. CONCLUSION In conclusion, we have successfully implemented all the queries and made a perfectly working Blood Bank Management System (BBMS) Software used - Postgre SQL