i2a8RdEc

1/*	1. Drop one referential constraint and
2define it anew with the CASCADE option. */
3
4ALTER TABLE Ward
5DROP FOREIGN KEY HID,
6ADD FOREIGN KEY(HID) REFERENCES Hospital(HID)
7ON DELETE CASCADE;
8
9
10/*	2. Add a trigger to your database. 
11If needed, feel free to define an additional table 
12so that the trigger can update that table. */
13
14DROP TABLE IF EXISTS EmployeeArchives;
15CREATE TABLE EmployeeArchives(
16	EID char(6) NOT NULL,
17	fName varchar(20) NOT NULL,
18    MI char(1),
19    lName varchar(20) NOT NULL,
20	duty varchar(20),
21	dateRemoved DATE NOT NULL
22);
23
24DELIMITER $$
25
26CREATE TRIGGER beforeDeleteEmployees
27BEFORE DELETE
28ON Employee FOR EACH ROW
29BEGIN
30	INSERT INTO EmployeeArchives(EID, fName, MI, lName, duty, dateRemoved)
31	VALUES(OLD.EID, OLD.fName, OLD.MI, OLD.lName, OLD.duty, NOW());
32
33END $$
34
35DELIMITER ;
36
37/*	3. Write a typical SQL query -- a query which is expected to be frequently
38executed by the employees of the HN. */
39
40-- getting name of patient and diagnoses
41
42SELECT patientDID.registrNumber, patientDID.fName, patientDID.MI, patientDID.lName, d.dName
43FROM 
44(SELECT Patient.registrNumber, Patient.fName, Patient.MI, Patient.lName, Treatment.DID
45FROM Patient, Treatment
46WHERE Treatment.patientRegistrNumber = Patient.registrNumber) AS patientDID
47JOIN Diagnosis d
48    ON patientDID.DID = d.DID;
49
50
51/*	4. Add an index to the database 
52designed to speed up the execution of the query from 3.*/
53
54CREATE INDEX idxPatientRegistrNumber
55ON Patient(registrNumber);
56
57CREATE INDEX idxTreatmentRegistrNumber
58ON Treatment(patientRegistrNumber);