Blood-Bank-Management-System-DB/BBMS.sql at master · sakibreza/Blood-Bank-Management-System-DB · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
-- drop tables

drop table Donate;
drop table BloodBank;
drop table Donar;
drop table Patient;


-- create tables

create table Patient(
    patient_id integer,
    patient_name varchar(20) not null,
    blood_group varchar(4),
    disease varchar(20),
    patient_address varchar(20),
    patient_contact_no varchar(20),
    primary  key (patient_id)
);

create table Donar(
    donar_id integer,
    donar_name varchar(20) not null,
    blood_group varchar(4),
    donar_contact_no varchar(20),
    donar_address varchar(20),
    disease varchar(20),
    primary key (donar_id)
);

create table BloodBank(
    blood_bank_id integer,
    blood_bank_name varchar(20),
    blood_bank_address varchar(20),
    blood_bank_contact_no varchar(20),
    primary key (blood_bank_id)
);

create table Donate(
    donate_id integer,
    donar_id integer,
    date_of_donation date,
    blood_bank_id integer,
    flag integer default 1,
    primary key(donate_id),
    foreign key (donar_id) references Donar(donar_id),
    foreign key (blood_bank_id) references BloodBank(blood_bank_id)
);

-- data insertion of patient

insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1001,'PA','A+','N/A','Dhaka','01234');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1002,'PB','B+','N/A','Khulna','04234');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1003,'PC','A+','N/A','Khulna','01634');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1004,'PD','B+','N/A','Dhaka','01294');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1005,'PE','A+','N/A','Dhaka','01434');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1006,'PF','A+','N/A','Dhaka','01234');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1007,'PG','B+','N/A','Khulna','04234');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1008,'PH','A+','N/A','Khulna','01634');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1009,'PI','B+','N/A','Dhaka','01294');
insert into Patient (patient_id,patient_name,blood_group,disease,patient_address,patient_contact_no)
            values(1010,'PJ','A+','N/A','Dhaka','01434');


-- data insertion of donar

insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(101,'DA','A+','0134','Dhaka');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(102,'DB','A+','0134','Dhaka');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(103,'DC','B+','0134','Khulna');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(104,'DD','A+','0134','Khulna');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(105,'DE','B+','0134','Dhaka');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(106,'DF','A+','0134','Chittagong');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(107,'DG','A+','0134','Dhaka');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(108,'DH','B+','0134','Khulna');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(109,'DI','A+','0134','Chittagong');
insert into Donar (donar_id,donar_name,blood_group,donar_contact_no,donar_address)
            values(110,'DJ','B+','0134','Dhaka');


-- data insertion of blood bank

insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(1,'BBA','Dhaka','911');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(2,'BBB','Khulna','912');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(3,'BBC','Dhaka','913');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(4,'BBD','Khulna','914');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(5,'BBE','Dhaka','915');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(6,'BBF','Khulna','916');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(7,'BBG','Dhaka','917');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(8,'BBH','Khulna','918');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(9,'BBI','Dhaka','919');
insert into BloodBank (blood_bank_id,blood_bank_name,blood_bank_address,blood_bank_contact_no)
            values(10,'BBJ','Khulna','920');

-- data insertion of donation

insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10001,101,TO_DATE('28/02/2018', 'DD/MM/YYYY'),1);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10002,104,TO_DATE('27/02/2018', 'DD/MM/YYYY'),2);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10003,102,TO_DATE('26/02/2018', 'DD/MM/YYYY'),1);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10004,106,TO_DATE('25/04/2018', 'DD/MM/YYYY'),2);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10005,105,TO_DATE('24/02/2018', 'DD/MM/YYYY'),1);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10006,106,TO_DATE('28/02/2018', 'DD/MM/YYYY'),1);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10007,107,TO_DATE('27/02/2018', 'DD/MM/YYYY'),2);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10008,108,TO_DATE('26/02/2018', 'DD/MM/YYYY'),1);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10009,109,TO_DATE('25/02/2018', 'DD/MM/YYYY'),2);
insert into Donate (donate_id,donar_id,date_of_donation,blood_bank_id)
            values(10010,110,TO_DATE('24/02/2018', 'DD/MM/YYYY'),1);
commit;