Create a procedure into the CAP database using CURSORS that receives a DNI as parameter and delete
the entry in the METGE table for that DNI.
Consider that a doctor may work with multiple nurses and also a doctor may attend multiple patients, so take
into account referential integrity because the row for the doctor may be linked with rows in other tables
through the foreign keys that should be deleted first..
The use of CURSOR is mandatory, any solution which doesn't make use of it won't be valid and will have a
qualification of zero points.DROP DATABASE IF EXISTS CAP;
CREATE DATABASE CAP;
USE CAP;

CREATE TABLE MALALTIAMEDICACIO(
nomMalaltia VARCHAR(30) PRIMARY KEY,
simptomes VARCHAR(30),
codiMedicacio INT,
nomMedicacio VARCHAR(30)
) ENGINE=INNODB;

CREATE TABLE PATEIX (
DNIPacient CHAR(9),
nomMalaltia VARCHAR(30),
gravetat VARCHAR(30),
PRIMARY KEY(DNIPacient, nomMalaltia)
) ENGINE=INNODB;

CREATE TABLE PACIENT (
DNIPacient CHAR(9) PRIMARY KEY,
nomPacient VARCHAR(30),
DNIMetge CHAR(9)
) ENGINE=INNODB;

CREATE TABLE METGE (
DNIMetge CHAR(9) PRIMARY KEY,
nomMetge VARCHAR(30)
) ENGINE=INNODB;

CREATE TABLE INFERMER (
DNIInfermer CHAR(9) PRIMARY KEY,
nomInfermer VARCHAR(30),
DNIMetge CHAR(9)
) ENGINE=INNODB;

ALTER TABLE PATEIX ADD CONSTRAINT fk_PATEIX_nomMalaltia FOREIGN KEY
(nomMalaltia) REFERENCES MALALTIAMEDICACIO(nomMalaltia),
ADD CONSTRAINT fk_PATEIX_DNIPacient FOREIGN KEY (DNIPacient)
REFERENCES PACIENT(DNIPacient);
ALTER TABLE PACIENT ADD CONSTRAINT fk_PACIENT_DNIMetge FOREIGN KEY
(DNIMetge) REFERENCES METGE(DNIMetge);
ALTER TABLE INFERMER ADD CONSTRAINT fk_INFERMER_DNIMetge FOREIGN KEY
(DNIMetge) REFERENCES METGE(DNIMetge);

INSERT INTO MALALTIAMEDICACIO VALUES ("Angines","mal de coll",38564,"antibiotic");
INSERT INTO MALALTIAMEDICACIO VALUES ("Al.lergia al pol.len","estrenuts",5436,"antihistaminic");
INSERT INTO METGE VALUES ("32445346P","NAME_1");
INSERT INTO METGE VALUES ("45634756X","NAME_2");
INSERT INTO INFERMER VALUES ("45433234K","Marta NAME_3","45634756X");
INSERT INTO PACIENT VALUES ("34564564E","Sra. NAME_4","32445346P");
INSERT INTO PACIENT VALUES ("45648352T","Sr. NAME_5 NAME_6","32445346P");
INSERT INTO PACIENT VALUES ("34997867Y","Sra. NAME_7 NAME_8","45634756X");
INSERT INTO PATEIX VALUES ("45648352T","Angines","lleugeres");
INSERT INTO PATEIX VALUES ("45648352T","Al.lergia al pol.len","greu");
INSERT INTO PATEIX VALUES ("34997867Y","Angines","greus");

SHOW TABLES;
EXPLAIN MALALTIAMEDICACIO;
SELECT * FROM MALALTIAMEDICACIO;
EXPLAIN PATEIX;
SELECT * FROM PATEIX;
EXPLAIN PACIENT;
S