(a) A common requirement for the Hospital data is for details about male patients to be provided. (0) Give the definition of a view named male_patient which includes the columns named patient_id, patient_name, height, weight, staff_no and ward_no, and which can be used to retrieve data for just male patients. (ii) Give an INSERT statement using this view for a male patient with patient identifier p99, name Malakite, height 180.2 cm. weight 69.0 kg, under the care of a doctor with the staff number 110, and on ward number w2. Execute this statement, and then execute a query to display all the rows of this view. Explain why the inserted row is not displayed (iii) There are two approaches to solving the problem described in part (ii). We could 1. revise the view definition to prevent the view updating the base table, or 2. revise the base table definition to enable the view to update the base table appropriately. Describe how each approach could be effected. (b) Give a database procedure named transfer patient that executes an UPDATE statement for the patient table to effect the transfer of patient from one ward to another, only if the given values will not cause it to fail. This procedure should have two input parameters, the identifier of the patient and the number of the ward the patient is being transferred to. Ignoring data type conflicts, if the given values would cause an UPDATE statement to fail, an output parameter should return a text string that identifies the cause of the failure. If the UPDATE statement would be successful it should be executed. (a) A common requirement for the Hospital data is for details about male patients to be provided. (0) Give the definition of a view named male_patient which includes the columns named patient_id, patient_name, height, weight, staff_no and ward_no, and which can be used to retrieve data for just male patients. (ii) Give an INSERT statement using this view for a male patient with patient identifier p99, name Malakite, height 180.2 cm. weight 69.0 kg, under the care of a doctor with the staff number 110, and on ward number w2. Execute this statement, and then execute a query to display all the rows of this view. Explain why the inserted row is not displayed (iii) There are two approaches to solving the problem described in part (ii). We could 1. revise the view definition to prevent the view updating the base table, or 2. revise the base table definition to enable the view to update the base table appropriately. Describe how each approach could be effected. (b) Give a database procedure named transfer patient that executes an UPDATE statement for the patient table to effect the transfer of patient from one ward to another, only if the given values will not cause it to fail. This procedure should have two input parameters, the identifier of the patient and the number of the ward the patient is being transferred to. Ignoring data type conflicts, if the given values would cause an UPDATE statement to fail, an output parameter should return a text string that identifies the cause of the failure. If the UPDATE statement would be successful it should be executed
