The Relational Model Employee ID, FName, LName, Salary, jobTitle, officeNum, emp

Question

The Relational Model Employee ID, FName, LName, Salary, jobTitle, officeNum, empRank, supervisorID) That is one design, other designs for the ISA relationship are possible... Equipment Type (ID, Desc, model, instructions) Equipment (Serialft, TypeID, PurchaseYear, Lastlnspection, roomNum) Room (Num, occupied flag) RoomService (roomNum service Room Access (roomNum EmplD) Patient (SSN, FirstName, LastName, Address, TelNum) Doctor (LD, gender, specialty, LastName, FirstName) Admission (Num, AdmissionDate, LeaveDate, TotalPayment, InsurancePayment, Patient SSN, FutureVisit) Examine (Doctor ID, AdmissionNum, comment) Stayln(AdmissionNum, RoomNum, startDate, endDate)

Explanation / Answer

A.

PL/SQL TRIGGER

CREATE TRIGGER mri_check
   BEFORE
   INSERT OR UPDATE ON Equipment
   FOR EACH ROW
BEGIN
   IF :NEW.PurchaseYear IS NULL AND :NEW.PurchaseYear <2005 THEN
       raise_application_error(-20015, "You cannot insert or update this row");
   END IF;
END;

Here is an additional Mysql Implementation

DELIMITER $$
CREATE TRIGGER mri_insert_check
   BEFORE
   INSERT ON Equipment
   FOR EACH ROW
BEGIN
   IF NEW.PurchaseYear IS NULL AND NEW.PurchaseYear <2005 THEN
       signal sqlstate '45000'; /* Raise some exception to prevent insertion */
   END IF;
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER mri_update_check BEFORE UPDATE ON Equipment
   FOR EACH ROW
BEGIN
   IF NEW.PurchaseYear IS NULL AND NEW.PurchaseYear <2005 THEN
       signal sqlstate '45000'; /* Raise some exception to prevent updation */
   END IF;
END $$
DELIMITER ;

B.

CREATE TRIGGER patient_history_trigger
   AFTER
   INSERT ON Admission,
   FOR EACH ROW
DELCARE
DocName varchar2(100)
BEGIN
   SELECT LastName INTO DocName
   FROM Doctor AS Doc, Examine AS Ex, Admission AS Adm
   WHERE Doc.ID = Ex.Doctor_ID AND Ex.AdmissionNum = Admission.Num
   AND Admission.Num = :NEW.Num;
   dbms_output.put(DocName);
END;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.