Blood-Bank-Management-System/BloodBankManagementSystem.sql at main · dhruv7504/Blood-Bank-Management-System · 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
-- Create Database
CREATE DATABASE BloodBankManagementSystem;
USE BloodBankManagementSystem;

-- ============================================
-- Table: BloodType
-- ============================================
CREATE TABLE BloodType (
    Blood_Type_ID VARCHAR(10) PRIMARY KEY,
    Name          VARCHAR(100) NOT NULL
);

-- Display BloodType Table Structure
DESC BloodType;

-- Display All Records from BloodType Table
SELECT * FROM BloodType;

-- ============================================
-- Table: Hospital
-- (New table to store hospital details)
-- ============================================
CREATE TABLE Hospital (
    Hospital_ID    VARCHAR(10) PRIMARY KEY,
    Name           VARCHAR(100) NOT NULL,
    Address        VARCHAR(255),
    Contact_Number VARCHAR(15) NOT NULL
);

-- Display Hospital Table Structure
DESC Hospital;

-- Display All Records from Hospital Table
SELECT * FROM Hospital;

-- ============================================
-- Table: Donor
-- ============================================
CREATE TABLE Donor (
    Donor_ID        VARCHAR(10) PRIMARY KEY,
    Name            VARCHAR(100) NOT NULL,
    Contact_Number  VARCHAR(15) NOT NULL,
    Blood_Type_ID   VARCHAR(10),
    Donor_Card_ID   VARCHAR(10),
    FOREIGN KEY (Blood_Type_ID) REFERENCES BloodType(Blood_Type_ID),
    CHECK (Age >= 18) -- Ensures only 18+ donors can be added
);

-- Display Donor Table Structure
DESC Donor;

-- Display All Records from Donor Table
SELECT * FROM Donor;

-- ============================================
-- Table: DonationCard
-- ============================================
CREATE TABLE DonationCard (
    Card_ID  VARCHAR(10) PRIMARY KEY,
    Donor_ID VARCHAR(10),
    FOREIGN KEY (Donor_ID) REFERENCES Donor(Donor_ID)
);

-- Display DonationCard Table Structure
DESC DonationCard;

-- Display All Records from DonationCard Table
SELECT * FROM DonationCard;

-- ============================================
-- Table: DonorTransactions
-- (Now includes Hospital_ID to indicate the hospital where the donation took place)
-- ============================================
CREATE TABLE DonorTransactions (
    Donor_Trans_ID        VARCHAR(10) PRIMARY KEY,
    Donor_ID              VARCHAR(10),
    Donation_Confirmation VARCHAR(50),
    Health_Condition      VARCHAR(50),
    Date                  VARCHAR(15),
    Hospital_ID           VARCHAR(10),  -- New column for hospital reference
    FOREIGN KEY (Donor_ID)   REFERENCES Donor(Donor_ID),
    FOREIGN KEY (Hospital_ID) REFERENCES Hospital(Hospital_ID)
);

-- Display DonorTransactions Table Structure
DESC DonorTransactions;

-- Display All Records from DonorTransactions Table
SELECT * FROM DonorTransactions;

-- ============================================
-- Table: Recipient
-- ============================================
CREATE TABLE Recipient (
    Recipient_ID   VARCHAR(10) PRIMARY KEY,
    Name           VARCHAR(100) NOT NULL,
    Contact_Number VARCHAR(15) NOT NULL,
    Blood_Type_ID  VARCHAR(10),
    Donor_ID       VARCHAR(10),
    FOREIGN KEY (Blood_Type_ID) REFERENCES BloodType(Blood_Type_ID),
    FOREIGN KEY (Donor_ID) REFERENCES Donor(Donor_ID)
);

-- Display Recipient Table Structure
DESC Recipient;

-- Display All Records from Recipient Table
SELECT * FROM Recipient;

-- ============================================
-- Table: RecipientTransactions
-- (Now includes Hospital_ID to record the hospital where the transfusion/transaction occurred)
-- ============================================
CREATE TABLE RecipientTransactions (
    Recipient_Trans_ID VARCHAR(10) PRIMARY KEY,
    Recipient_ID       VARCHAR(10),
    Recipient_Request  VARCHAR(255),
    Date               VARCHAR(15),
    Donor_Card_ID      VARCHAR(10),
    Blood_Type_ID      VARCHAR(10),
    Hospital_ID        VARCHAR(10),  -- New column for hospital reference
    FOREIGN KEY (Recipient_ID)   REFERENCES Recipient(Recipient_ID),
    FOREIGN KEY (Donor_Card_ID)    REFERENCES DonationCard(Card_ID),
    FOREIGN KEY (Blood_Type_ID)    REFERENCES BloodType(Blood_Type_ID),
    FOREIGN KEY (Hospital_ID)      REFERENCES Hospital(Hospital_ID)
);

-- Display RecipientTransactions Table Structure
DESC RecipientTransactions;

-- Display All Records from RecipientTransactions Table
SELECT * FROM RecipientTransactions;