Blood Bank Management System PDF

Preview

Summary

Project consisting of the development of a blood bank management system using SQL...

Description

Blood Bank Management System

Contents 1.

Problem Description.................................................................................................. 1

2.

Conceptual Model ..................................................................................................... 2 2.1.

Relational Entity Diagram ................................................................................. 2

3.

Logic Model .............................................................................................................. 2

4.

Normalization ............................................................................................................ 3

5.

Data Dictionary ......................................................................................................... 5

6.

List of Queries ......................................................................................................... 10 6.1.

Relational Algebra ........................................................................................... 10

6.2.

SQL Queries .................................................................................................... 12

7.

Triggers and Stored Procedures .............................................................................. 13

8.

Database Creation ................................................................................................... 22

1. Problem Description A Blood Bank is a place where blood is collected and stored for use by others who need it due to health emergencies or a lack of blood. Therefore, it is necessary to develop a data management system to minimize the cost and time of manual labor to acess blood bank information.    

 

   

The donor will be the person who donates the blood; it will contain information such as: name, age, gender, contact, blood group, address, disease; The patient will be the person who will receive the blood; this will have the following attributes: iD, name, gender, blood group, contact, address; The blood bank is managed by a manager, who has a name, an iD and location; The donor can only make the donation once a month; at each donation, the donated blood will be analysed by a clinical analyst, to find out if he suffers from any blood-borne disease or not. If you have a disease and you are younger than 18 or older than 60, will not be able to donate; An iD and blood group will be tagged (to the blood); Donors and patients are registered by a registration team. A registration team can register multiple donors and patients, however, a donor or patient can only be registered by one team member; Both registry team and clinical analyst have with attributes: iD and name; Hospitals and patients order blood from the blood bank through the manager, as this is the one who handles the blood bags; Hospital will have the following attributes: iD, name, location; The date of registration for each patient and donor is important to the context.

1

2. Conceptual Model 2.1.

Relational Entity Diagram

3. Logic Model Manager (idManager, nameM, idBloodBank, nameBloodBank, locationBloodBank) Registration_Team(idRT, nameRT, idBloodBank) 2

Technical_Analyst (idTA, nameTA, idBloodBank) TechnicalAnalyst_Blood ( idTA, idBlood, bloodGroupBlood ) Blood (idBlood, bloodGroupBlood, idManager) Blood_Patient ( idBlood, bloodGroupBlood, idPatient ) Patient (idPatient, nameP, gender, bloodGroup, contact, address, idManager, idRT, dateRegisters, bloodGroupRequests, amountOfBloodRequests) Donor (idDonor, nameD, gender, bloodGroup, contact, address, age, disease, idRT, idBlood, bloodGroupBlood, dateRegisters) Hospital (idHospital, nameH, amountOfBloodRequests)

location,

idManager,

bloodGroupRequests,

4. Normalization  Manager Table Manager (idManager, nameM, idBloodBank, nameBloodBank, locationBloodBank) 1NF Manager (idManager, nameM, surnameG) BloodBank (idManager, idBloodBank, name, avenue_street, number, neighborhood)

2NF / 3NF Manager (idManager, nameM, surnameG) Manager_BloodBank(idManager, idBloodBank) BloodBank (idBloodBank, nameBB, avenue_street, number, neighborhood)

 Registration_Team Table Registration_Team(idRT, nameRT, idBloodBank) 1NF / 2NF / 3NF Registration_Team(idRT, nameRT, idBloodBank)

 Technical_Analyst Table Technical_Analyst (idTA, nameTA, idBloodBank)

3

1NF / 2NF / 3NF Technical_Analyst (idTA, nameTA, idBloodBank)



TechnicalAnalyst_Blood Table

TechnicalAnalyst_Blood ( idTA, idBlood, bloodGroupBlood ) 1NF / 2NF / 3NF TechnicalAnalyst_Blood ( idTA, idBlood, bloodGroupBlood )

 Blood Table Blood (idBlood, bloodGroupBlood, idManager) 1NF / 2NF / 3NF Blood (idBlood, bloodGroupBlood, idManager)

 Blood_Patient Table Blood_Patient ( idBlood, bloodGroupBlood, idPatient ) 1NF / 2NF / 3NF Blood_Patient ( idBlood, bloodGroupBlood, idPatient )

 Patient Table Patient (idPatient, nameP, gender, bloodGroup, contact, address, idManager, idRT, dateRegisters, bloodGroupRequests, amountOfBloodRequests) 1NF Patient (idPatient, nameP, surnameP, gender, bloodGroup, contact, avenue_street, number, neighborhood, idManager, idRT, dateRegisters) Requests (idPatient, bloodGroupRequests, amountOfBloodRequests)

2NF / 3NF Patient (idPatient, nameP, surnameP, gender, bloodGroup, contact, avenue_street, number, neighborhood, idManager, idRT, dateRegisters) Patient_Requests (idPatient, idRequests) Requests (idRequests, bloodGroupRequests, amountOfBloodRequests)

4

 Donor Table Donor (idDonor, nameD, gender, bloodGroup, contact, address, age, disease, idRT, idBlood, bloodGroupBlood, dateRegisters) 1NF / 2NF / 3NF Donor (idDonor, nameD, gender, bloodGroup, contact, avenue_street, number, neighborhood, age, disease, idRT, idBlood, bloodGroupBlood, dateRegisters)

 Hospital Table Hospital (idHospital, nameH, amountOfBloodRequests)

location,

idManager,

bloodGroupRequests,

1NF Hospital (idHospital, nameH, avenue_street, number, neighborhood, idManager) Requests (idHospital, bloodGroupRequests, amountOfBloodRequests)

2NF / 3NF Hospital (idHospital, nomeH, avenue_street, number, neighborhood, idManager) Hospital_Requests (idHospital, idRequests) Requests (idRequests, bloodGroupRequests, amountOfBloodRequests)

5. Data Dictionary Table Description Comments

Manager Will contain manager information Fields

Name

Description

Type

Size

idManager nameM surnameM

Manager Code Manager’s First Name Manager’s Surname

SMALLINT VARCHAR VARCHAR

4 30 30

Table Description Comments

Comments (PK / FK ) PK NOT NULL NOT NULL

Manager_BloodBank Will contain Blood Bank and Manager iD information Fields

Name

Description

Type

Size

idManager idBloodBank

Manager Code Blood Bank Code

SMALLINT SMALLINT

4 4

Comments (PK / FK ) PK PK 5

Table Description Comments

BloodBank Will contain Blood Bank information Fields

Name

Description

Type

Size

idBloodBank nameBB

Blood Bank Code Blood Bank Name Avenue or Street where the Blood Bank is located Blood Bank building number Neighborhood where the Blood Bank is located

SMALLINT VARCHAR

4 30

Comments (PK / FK ) PK NOT NULL

VARCHAR

30

NOT NULL

INTEGER

4

NOT NULL

VARCHAR

30

avenue_street number neighborhood

Table Description Comments

Registration_Team Will contain Registration Team information Fields

Name idRT nameRT surnameRT idBloodBank Table Description Comments

Description Registration Team member code Team Member’s First Name Team Member’s Surname Blood Bank Code

Type

Size

Comments (PK / FK )

INTEGER

10

PK

VARCHAR

30

NOT NULL

VARCHAR

30

NOT NULL

SMALLINT

4

FK

Type

Size

Comments (PK / FK )

INTEGER

10

PK

VARCHAR

30

NOT NULL

VARCHAR

30

NOT NULL

SMALLINT

4

FK

Technical_Analyst Will contain Technical Analyst information Fields

Name idTA nameTA surnameTA idBloodBank

Description Technical Analyst Code Technical Analyst First Name Technical Analyst Surname Blood Bank Code

6

Table Description

Technical Analyst_ Blood Will contain information of the Blood Code and Blood Group of the blood analyzed by the Technical Analyst

Comments Fields Name idTA idBlood bloodGroup

Table Description Comments

Type

Size

Comments (PK / FK )

INTEGER

10

PK

INTEGER

10

PK

VARCHAR

3

PK

Tipo

Size

Comments (PK / FK )

INTEGER

10

PK

VARCHAR

3

PK

SMALLINT

4

FK

Description Technical Analyst Code Blood Code Blood Group of blood analyzed

Blood Will contain information on donated blood Type

Name idBlood bloodGroup idManager

Table Description Comments

Description Donated Blood Code Blood Group of donated blood Manager Code

Blood_Patient Will contain information on the blood received by a patient Fields

Name idBlood bloodGroup

idPatient

Table Description Comments

Description Donated Blood Code Blood Group of blood received by a Patient Code of the Patient who reciveid the blood

Type

Size

Comments (PK / FK )

INTEGER

10

PK

VARCHAR

3

PK

INTEGER

10

PK

Size

Comments (PK / FK )

Patient Will contain Patient information Fields

Name

Description

Type

7

idPatient nameP surnameP

Patient Code Patient’s First Name Patient’s Surname

INTEGER VARCHAR VARCHAR

10 30 30

gender

Patient’s Gender

CHAR

1

VARCHAR

3

NOT NULL

VARCHAR

9

NOT NULL

VARCHAR

30

NOT NULL

INTEGER

4

NOT NULL

VARCHAR

30

NOT NULL

INTEGER

10

FK

SMALLINT

4

FK

bloodGroup contacto avenue_street number neighborhood idRT idManager dateRegisters

Patient’s Blood Group Patient Contact Patient’s Avenue or Street House Number Neighborhood where the Patient lives Registration Team member code Manager Code Date the patient was registered

DATE

PK NOT NULL NOT NULL DEFAULT ‘M’

NOT NULL

Patient_Requests Table Description Will contain information on patients who requested blood Comments Fields Name

Description

Type

Size

idPatient idRequests

Patient Code Blood Request Code

INTEGER INTEGER

10 10

Table Description Comments

Comments (PK / FK ) PK PK

Donor Will contain Donor information Fields Comments (PK / FK ) PK NOT NULL NOT NULL DEFAULT ‘M’

Name

Description

Type

Size

idDonor nameD surnameD

Donor Code Donor First Name Donor Surname

INTEGER VARCHAR VARCHAR

10 30 30

gender

Donor Gender

CHAR

1

VARCHAR

3

NOT NULL

VARCHAR

9

NOT NULL

VARCHAR

30

NOT NULL

INTEGER

4

NOT NULL

bloodGroup contacto avenue_street number

Donor Blood Group Donor Contact Donor Avenue or Street House Number

8

Neighborhood where the Donor lives age Donor Age Indicates whether the donor has any disease or not. disease Have disease = 1 There is no disease =0 Donated Blood idBlood Code Blood Group of bloodGroupBlood donated blood Registration Team idRT member code Date the donor was dateRegisters registered neighborhood

Table Description Comments

VARCHAR

30

NOT NULL

INTEGER

3

NOT NULL

INTEGER

1

DEFAULT 0

INTEGER

10

NULL

VARCHAR

3

NULL

INTEGER

10

FK

DATE

NOT NULL

Hospital Will contain Hospital information Fields

Name

Description

Type

Size

idHospital nameH

Hospital Code Hospital Nome Avenue or Street where the Hospital is located Hospital building number Neighborhood where the hospital is located Manager Code

INTEGER VARCHAR

10 30

Comments (PK / FK ) PK NOT NULL

VARCHAR

30

NOT NULL

INTEGER

4

NOT NULL

VARCHAR

30

NOT NULL

SMALLINT

4

FK

avenue_street number neighborhood idManager

Hospital_Requests Table Description Will contain information from hospitals that requested blood Comments Fields Comments Name Description Type Size (PK / FK ) idHospital Hospital Code INTEGER 10 PK idRequests Blood Request Code INTEGER 10 PK

9

Table Description Comments

Requests Will contain information on blood requests made Fields

Name

Description

Type

Size

idRequests

Request Code Required Blood Group Amount of Blood requested (number of bags)

INTEGER

10

Comments (PK / FK ) PK

VARCHAR

3

NOT NULL

INTEGER

10

NOT NULL

bloodGroup amountBlood

6. List of Queries 1. List the names of the donors next to their address, who belong to the AB+ blood group and are female. 2. List the names of tegistered donors who donated blood aged 18-28 years, and older male and female donors who also donated blood. 3. List the registered donors who donated blood and who belong to the blood group considered universal donor, as well as the patients who belong to the blood group considered universal recipient. 4. List the names of workers who work in the same blood bank as technical analyst ‘Junaid’. 5. List registered donors who cannot donate (who have some illness). 6. The percentage of male donors without any disease, where the percentage of donors without any disease is approximately 69.23%. 7. List the hospital and patient with the highest number of requested bags.

6.1.

Relational Algebra

1. Result < - πidDonor, nameD, surnameD, gender, bloodGroup, number, neighborhood, avenue_streets

(σgender=' F' (σbloodGroup ='AB+' (σidBlood

!=NULL (σbloodGroupBlood !=NULL

(donor)))))

2. A < - πidDonor, nameD, surnameD, gender, age (σage ≥ 18 (σage ≤ 28 (σdisease=0 (donor)))) B < - πidDonor, nameD, surnameD, gender, age (σgender = 'M' (σdisease=0 (donor))) C < - πidDonor, nameD, surnameD, gender, age (σgender = 'F' (σdisease=0 (donor))) 10

Result < - A U B U C

3. A < - πidDonor, nameD, surnameD, bloodGroup (σbloodGroup=' O-' (σdisease=0 (donor))) B < - πidPatient, nameP, surnameP, bloodGroup (σbloodGroup =' AB+' (patient)) Result < - A U B

4. A...