Question

Question 3 refers to the relational database with a schema described below:

The hospital database contains information about the treatments of patients performed by the doctors. The database also contains information on the prescriptions ordered by the doctors. The schemas of relational tables, the meanings of attributes and specifications of primary, candidate, and foreign keys are given below.

HOSPITAL HospitalCd Name Address Estate PostalCode EstablishedDate	Hospital Code Name of the hospital Address of the hospital The estate where the hospital is located The postal code of the address The date the hospital was established
Primary key = HospitalCd
DOCTOR DoctorId Name Citizenship WorkFor	The identification number of the doctor Name of the doctor The citizenship of the doctor The hospital the doctor is working for
Primary key = DoctorId Foreign key = WorkFor references HOSPITAL (HospitalCd)
PATIENT NRIC Name DateOfBirth Sex Address Estate PostalCode	The NRIC of the patient The name of the patient The birth date of the patient The gender of the patient The residential address of the patient The estate where the patient lives The postal code of the address
Primary key = NRIC
TREATMENT DoctorId NRIC TreatmentDate Description	The doctor identification number The NRIC of the patient The treatment date Description of the treatment
Primary key = (DoctorId, NRIC, TreatmentDate) Foreign key = (DoctorId) references DOCTOR(DoctorId) Foreign key = (NRIC) references PATIENT(NRIC)
PRESCRIPTION DoctorId NRIC TreatmentDate ItemNum Drug Dosage	The doctor identification number The NRIC of the patient The treatment date The item number of the prescription The drug prescribed by the doctor The dosage prescribed
Primary key = (DoctorId, NRIC, TreatmentDate, ItemNum) Foreign key = (DoctorId, NRIC, TreatmentDate) references TREATMENT(DoctorId, NRIC, TreatmentDate) Implement a stored PL/SQL function PATIENT(DoctorId) that finds the NRIC of all patients who received three or more treatments from the same doctor (DoctorId). The function should return a string of NRICs separated with one or more spaces. Assuming the identifier of the doctor (DoctorId) treating the patients is passed to the function as a value of parameter.                                                                                             (6.0 marks) Implement a stored PL/SQL procedure that adds to the database information about a new doctor. The information about the new doctor should be passed into the procedure through the values of parameters. The procedure must check the validity of hospital code before the verification of a referential integrity constraint. A hospital code is a sequence of characters that starts from a letter 'H' and is followed by two digits. Hint: You may use pattern matching ‘like’ and ‘_’ (underscore) of SQL to achieve your verification of valid hospital code.                         (6.0 marks)

Answer 1

--//SQL FUNCTION//

--//WHICH WILL RETURN NRIC'S OF PATIENT WHO HAVE 3 OR MORE TREATMENTS FROM SAME DOCTOR this function will accept one doctor id as an parameter to return NRIC's based on this id//

GO

CREATE FUNCTION PATIENT(@DoctorId NVARCHAR)
RETURNS TABLE   
AS
RETURN SELECT PATIENT.NRIC from PATIENT
inner join TREATMENT on patient.NRIC=TREATMENT.NRIC
           inner join DOCTOR on TREATMENT.DoctorId=@DoctorId
           where (select COUNT(NRIC) from PATIENT)>=3

--//SQL PROCEDURE TO INSERT VALUES INTO DOCOTOR TABLE IF HOSPITAL CODE PATTEREN IS (H__) means if first letter of code is H and then code have two more digits then new record will be inserted into DOCOTR table.//

GO

CREATE PROCEDURE NewDoctor(@DoctorId nvarchar(200), @Name nvarchar(200),
@Citizenship nvarchar(200), @WorkFor nvarchar(200))
AS
BEGIN
IF(@WorkFor like('H__'))
BEGIN
INSERT INTO DOCTOR VALUES(@DoctorId, @Name, @Citizenship, @WorkFor)
END
ELSE
BEGIN
SELECT 'insertion falied due to code patteren!'
END
END

--//COMMENT DOWN FOR ANY QUERIES...
--//HIT A THUMBS UP IF YOU DO LIKE IT!!!