1. Create and submit the ERD that you are using as the basis of your implementation. (10 marks) 2. A one page explanation of the changes you have made to the ERD you submitted in Assignment 01. (5...


1. Create and submit the ERD that you are using as the basis of your implementation.
(10 marks)


2. A one page explanation of the changes you have made to the ERD you submitted in Assignment 01.
(5 marks)


3. Create a
data dictionary
that lists
at least
each of the tables, their columns, domains and any other constraints that apply.
(10 marks)


4. Implement the database in Oracle SQLPlus on sphinx.murdoch.edu.au
(20 marks)


a. All tables should be created as per your ERD; the marker will check your ERD against your tables.


b. All
entity and referential integrity constraints
should be created and appropriately named.


c. All columns should be of an appropriate domain/size and be set as required or not as appropriate.


d. All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and support the transactions and views listed below.


e.
SELECT, UPDATE and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark all or part of your assignment.


5. Provide all the SQL statements that are required for the following transactions to be executed
(15 marks):


a. Create a record for a new staff member with the following details: Moira Samuels, 49 School Road, Bedford, W.A., 6052. She is female, her date of birth is 30 May 1990, and her telephone number is 0150-456-3357. Her current position is Charge Nurse in Ward 11. Her current salary is $68760 and she is working 37.5 hours per week on a Permanent basis. She is paid monthly. She has a BSc in Nursing from Curtain University which was awarded to her on 12th
July 2010. She also has a Post Graduate Diploma in Geriatrics from Enid Blyton University which as awarded on 22nd
July 2012. Prior to starting at FSH, she worked at Eastern Hospital as a Charge Nurse between 23rd
January 2011 and 1st
May 2011. From 1st
June 2011 until 23rd
Feb 2012, she worked at Eastern Hospital as a Charge Nurse.


b. Register a new patient (Anne Phelps) as per the details in Figure 1 below.


c. Allocate the staff to Ward 11 as per Figure 2 below. You do not need to include the statements required to create the records of the staff named in the Figure.


6. Provide VIEWS for the following (Views should be named as ViewA, ViewB etc)
(20 marks):


a. VIEW A: Staff currently allocated to each ward, grouped by ward.


b. VIEW B: List of all patients referred to the hospital in the current month.


c. VIEW C: List of all patients referred to Ward 11 in the last 12 months, including the date they were Placed and the date they left, and the bed they occupied.


d. VIEW D: List of local doctors (including address, contact details and provider number) who have referred patients in the last 6 months, and the number of patients they have referred.


7. Provide an explanation (and any code you have used) as to how you would implement (or, if you were not able to successfully implement, how you would have implemented) the following constraints
(20 marks):


a. A Patient can only be allocated to a ward if there are beds available.


b. Only Staff in the role of Personnel Officer are permitted to make updates to staff records. When a staff record is updated, the username of the user responsible must be recorded.

Mar 29, 2021
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here