Blood Donation System Case Study 2022

Running head: BLOOD DONATION SYSTEM
BLOOD DONATION SYSTEM
Name of the Student
Name of the University
Author Note

1
BLOOD DONATION SYSTEM
Table of Contents
Stage 1: Scenario and Conceptual Database Design..................................................................2
Task 1.1: Case Study:.............................................................................................................2
Task 1.2: Conceptual database design:..................................................................................2
Stage 2: Logical Database Design and SQL Querying..............................................................3
Task 2.1: Logical Database Design:.......................................................................................3
Task 2.2: Creating tables using Oracle DBMS......................................................................3
Task 2.3: Creating the four most useful indexes....................................................................8
Task 2.4: SQL Query writing.................................................................................................9
Bibliography:............................................................................................................................11

2
BLOOD DONATION SYSTEM
Stage 1: Scenario and Conceptual Database Design
Task 1.1: Case Study:
The National Blood bank wants to build its database for Blood donation. The old
system is not capable enough to hold a vast amount of information. New Blood donation
system is able to store every detail and it will help a blood seeker and management as well.
The new database is able to store every information of blood. Every blood bag can be
identified by their unique id number. Blood bag can be stored in blood banks and blood donor
can donate blood in the hospital. Every blood bank has its id. Only one manager is assigned
to a blood bank. Managers observe the workflow in blood banks. In the hospital, every donor
can donate blood and a blood seeker can find blood. Hospital stocks every blood bag. Many
employees can work under the hospital. The manager of the Blood bank is also an employee
of a national blood bank.
Task 1.2: Conceptual database design:
Figure1: Conceptual database design of National Blood Bank
(Source: Created by author)

3
BLOOD DONATION SYSTEM
Stage 2: Logical Database Design and SQL Querying
Task 2.1: Logical Database Design:
Figure 2: Logical Database design of National Blood Bank
(Source: Created by author)
Task 2.2: Creating tables using Oracle DBMS
Creating P2556926Doner Table:
CREATE TABLE P2556926Doner (
donerid int NOT NULL PRIMARY KEY,
name VARCHAR2(20),
age int NOT NULL,

4
BLOOD DONATION SYSTEM
contact_number int not null
);
Creating P2556926BloodBankManager Table:
CREATE TABLE P2556926BloodBankManager (
managerID NUMBER NOT NULL PRIMARY KEY,
Name VARCHAR2(20),
Email VARCHAR2(20),
Contact_number number,
salary NUMBER);
Creating P2556926Employee Table:
CREATE TABLE P2556926employee (
employeeID NUMBER NOT NULL PRIMARY KEY,
Name VARCHAR2(20),

5
BLOOD DONATION SYSTEM
Email VARCHAR2(20),
Address VARCHAR2(30),
contac_number NUMBER);
Creating P2556926BloodBank Table:
CREATE TABLE P2556926BloodBank (
BloodBankID NUMBER NOT NULL PRIMARY KEY,
Name varchar2(20),
Address varchar2(30),
ContactNO NUMBER,
managerID number references P2556926BloodBankManager
(managerID));
Creating P2556926Blood Table
CREATE TABLE P2556926Blood (

6
BLOOD DONATION SYSTEM
BloodID NUMBER NOT NULL PRIMARY KEY,
BloodGroup varchar2(10),
BloodBankID NUMBER REFERENCES P2556926BloodBank(BloodBankID),
ContactNO NUMBER,
DONERID number references P2556926doner
(donerID));
Creating P2556926Seeker Table
CREATE TABLE P2556926Seeker (
SeekerID NUMBER NOT NULL PRIMARY KEY,
Name varchar2(10),
BloodBankID NUMBER REFERENCES P2556926BloodBank(BloodBankID),
ContactNO NUMBER,
age number);
Creating P2556926Hospital Table

7
BLOOD DONATION SYSTEM
CREATE TABLE P2556926Hospital (
HospitalID NUMBER NOT NULL PRIMARY KEY,
Name varchar2(10),
address varchar2(20),
ContactNO NUMBER,
DonerID NUMBER REFERENCES P2556926doner(donerid),
SeekerID NUMBER REFERENCES P2556926Seeker(seekerID),
employeeid NUMBER REFERENCES P2556926employee(employeeid)
);
Creating P2556926Stock Table
CREATE TABLE P2556926stock (
stockid NUMBER NOT NULL PRIMARY KEY,
bloodID number REFERENCES P2556926bLOOD(BLOODID),
STATUS varchar2(20),
HOSPITALID NUMBER REFERENCES P2556926hospital(hospitalid)
);

8
BLOOD DONATION SYSTEM
Task 2.3: Creating the four most useful indexes
Index_Blood Index:
create unique index Index_Blood on P2556926blood (BloodGroup,BloodBankID,donerid);
Above codes can generate index_blood index. This index is able to fast retrieve values
from bloodgroup, bloodbankid and donerid columns. In emergency, national blood bank can
Retrieve blood information from this blood table. This is very helpful index for national
blood bank.
Index_Doner Index:
create unique index Index_Doner on P2556926doner (donerid,name,contact_number);
By using this codes blood bank can create Index_Doner index. This index will help
national blood bank to retrieve donor information very quickly.
Index_Seeker Index:
create unique index Index_Seeker on P2556926seeker (seekerid,name,contactno);
This index can be created by using above codes. National bank can get quickly blood
seeker information. This is most helpful index that can national blood bank use in emergency
situation.
Index_Hospital Index:
create unique index Index_Hospital on P2556926hospital (hospitalid,donerid,seekerid);
Above codes is able to create Index_Hospital index on hospital table. National blood
bank is able to get hospitalid, donerid and seekerid very quickly from hospital table.

9
BLOOD DONATION SYSTEM
Task 2.4: SQL Query writing
Query 1:
select * from P2556926BloodBankManager where managerid =5;
This is an example of select a particular table. By using this query national bank is
able to get all information about a specific manager.
Query 2:
select * from P2556926Doner inner join P2556926Seeker on P2556926Doner.donerid=
P2556926Seeker.seekerid;
This is an example of inner join of two tables. By using this query national blood
bank is able to get all information of blood donor and blood seeker.
Query 3:
select * from P2556926hospital full outer join P2556926employee on
P2556926hospital.hospitalid=P2556926employee.employeeid;

10
BLOOD DONATION SYSTEM
Above code is an example of outer join. This query is able get every information of
hospital and their employee. National blood bank can get every information of hospital and
employee.
Query 4:
select * from P2556926bloodbankmanager where managerid in
(select managerid from P2556926bloodbankmanager where salary >10000);
This is an example of sub query. By using this query national blood bank is able to
know the manager details who has salary greater than 10000.
Query 5:
select * from P2556926BloodBankManager order by salary ASC;

11
BLOOD DONATION SYSTEM
This is an example of sorting or ordering facilities. By using this sorting query blood
bank is can view every manager salary with their details in ascending order.
Query 6:
SELECT COUNT(seekerid) FROM P2556926seeker;
This is an example if count function. By using this query national bllod bank can view
the number of total bllod bag seeker.
Bibliography:
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.

12
BLOOD DONATION SYSTEM
Klochkov, Y., Klochkova, E., Antipova, O., Kiyatkina, E., Vasilieva, I. and Knyazkina, E.,
Watt, A. and Eng, N., 2018. Database design.