database.txt

CREATE TABLE RECEPTION(
E_ID VARCHAR(54) PRIMARY KEY,
NAME VARCHAR(100),
EMAIL VARCHAR(100),
PASSWORD VARCHAR(100),
REGISTER_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE DONOR(
D_ID INT(3) NOT NULL AUTO_INCREMENT,
DNAME VARCHAR(50),
SEX VARCHAR(10),
AGE INT(3),
WEIGHT INT(3),
ADDRESS VARCHAR(150),
DISEASE VARCHAR(50),
DEMAIL VARCHAR(100),
DONOR_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT PK_2 PRIMARY KEY(D_ID)
);

CREATE TABLE BLOODBANK(
B_GROUP VARCHAR(4),
TOTAL_PACKETS INT(4),
CONSTRAINT PK_3 PRIMARY KEY(B_GROUP)
);

CREATE TABLE BLOOD(
B_CODE INT(4) NOT NULL AUTO_INCREMENT,
D_ID INT(3),
B_GROUP VARCHAR(4),
PACKETS INT(2),
CONSTRAINT PK_4 PRIMARY KEY(B_CODE),
CONSTRAINT FK_1 FOREIGN KEY(D_ID) REFERENCES DONOR(D_ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_2 FOREIGN KEY(B_GROUP) REFERENCES BLOODBANK(B_GROUP) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE CONTACT(
CONTACT_ID INT(3) NOT NULL AUTO_INCREMENT,
B_GROUP VARCHAR(4),
C_PACKETS INT(2),
F_NAME VARCHAR(50),
ADRESS VARCHAR(250),
CONSTRAINT PK_5 PRIMARY KEY(CONTACT_ID),
CONSTRAINT FK_3 FOREIGN KEY(B_GROUP) REFERENCES BLOODBANK(B_GROUP) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;

CREATE TABLE NOTIFICATIONS(
N_ID INT(3) NOT NULL AUTO_INCREMENT,
NB_GROUP VARCHAR(4),
N_PACKETS INT(2),
NF_NAME VARCHAR(50),
NADRESS VARCHAR(250),
CONSTRAINT PK_6 PRIMARY KEY(N_ID)
)ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;

TRIGGERS:

mysql> delimiter //
mysql> CREATE TRIGGER agecheck BEFORE INSERT ON DONOR FOR EACH ROW IF NEW.age < 21 THEN SET NEW.age = 0; END IF;//
mysql> delimiter ;