Assignment Three (Individual): e-Portfolio (45%)
Due: before 11pm 28/5/18
The main objective of assignment task 3 is to convert the logical-level design to a physical database using Microsoft Access software. You may change your logical design submitted in assignment task 2, depending on the feedback and/or due to some implementation issues. During the physical design stage, it is quite common to iterate between design and implementation stages.
Your e-portfolio should contain two elements.
1. ACCESS database as a file
2. Technical report presented as a journal entry (Not a WORD document)
(A) Access database:
1) No of minimum records of each entity:
You may increase above (not decrease). You may also have additional tables. Assign any number of records as you wish for such tables.
2) Perform normalisation
3) Create relevant relationships
4) Create 5 queries (One query using 1 table, 4 queries using 2 or more tables) 5) Make any business rules or assumptions that are necessary.
(B) Technical report: as a journal entry of e-Portfolio (900 – 1000 words).
This technical report should contain the following elements.
a) Reflection – (700 words)
You need to reflect on the major learnings of this subject and how you have linked them to your assignments. It may be quite possible that you may have a new design. This is quite normal in design and implementation processes. You need to explain the reasons behind the new change. You also need to explain the additional requirements that you would like to add in future designs, how would your database be used by multi-users and any other relevant features.
b) E-R diagram (old) and E-R diagram (New)
It is quite possible that the ER diagram you produced in assignment 2 may have changed. Therefore, include both old and new ER diagrams.
Note that new changes are a result of students making changes in their old ER diagram, individually. Therefore, include both old and new ER diagrams.
c) Queries: List and describe your queries in a table as shown below. Note: if you list name of query as qry_doctor, in your Access database, there should be a query called qry_doctor. This consistency must be maintained.
Name of query implemented at A3 Query Description
Example: qry_doctor List first name, last name, email address and mobile number of all doctors.
d) Business rules and/or assumptions
e) References – Links to your discussion forum posts in weeks 7-11, and any other sources
f) Note: The length of (a), (b), (c) and (d) should be 900 – 1000 words (max)
Submission of DATA200 Assessment task 2:
Submit your assignment via the Submission link available in Assessment section of Leo. When clicked on the submission link, the system will display all the e-portfolios that you have. Select the correct one.
Help on any query about this assignment: It is important to clarify any questions that you may have about the assignment before submission. Please email Tamara. firstname.lastname@example.org
Case Study – Emergency Room
In our Emergency Room (ER), we have three distinct types of workers: receptionists, nurses, and doctors. Any of the workers can in fact be a patient. Each person in the proposed system, be it a patient or a worker has a last, a first, possibly a middle name, and one or more addresses. An address consists of a country, province, city, street and street number. Each person can have none or more email addresses, none or more telephone numbers.
The workers work in ER in shifts. A shift consists of start and end time. The shifts do not overlap, but they are consecutive, i.e. there is a shift on at any given time and day. We are assuming that the model we are creating (and eventually the database we will design) covers some extended period of time. Each worker will thus be assigned to many shifts in that period. Exactly two receptionists are assigned to each shift, a group of two or more nurses is assigned to each shift, a group of two or more doctors is assigned to each shift, and one of the doctors assigned to a shift is the shift’s triage doctor.
When a patient comes to ER, it happens during a particular shift. The patient is admitted by a particular receptionist, is seen by the triage doctor of the shift. The patient may be send home, prescribed some medication by the triage doctor and send home, or is staying in ER – in which case the patient is assigned a bed and case doctors (one of the doctors on each shift best qualified for the particular problem of the patient). Each bed is supervised by a single nurse during a shift, but a nurse may supervise many beds, or none at all. The case doctor(s) may prescribe a medication that is administered to the patient by a single nurse in each shift for the duration of the patient taking the medicine. Each medication has a name, and for each patient there may be a different dosage and different number of times a day to take it.
s Criteria NN (points) PA (points) CR (points) (points) HD (points)
6 Reflection report capturing critical learning points of
data and information
throughout the semester Reflection report largely repeats the textbook or do not show the ability for reflective learning, do not capture critical learning points, not properly referenced
(4.9) Reflection report shows an average
level of reflective learning, capture some critical learning points, may be some errors in references (6.4) Reflection report shows a fair level of reflective learning, capture most of critical learning points, properly referenced (7.4) Reflection report shows a good
level of reflective learning, capture all critical learning points, properly referenced (8.4) Reflection report shows that the learning materials are well analysed and deeply reflected on, all critical learning points are comprehensively captured, properly referenced (10)
6 Implementation of all the entities, keys, field names and properties in the database Database contains less than half of correctly identified entities and keys of the case study (4.9) Database contains about half of correctly identified entities and keys of the case study (6.4) Database contains more than half correctly identified entities and keys of the case study (7.4) Database contains most of the correctly identified entities and keys of the case study
(8.4) Database contains all the correctly identified entities, keys, field names and properties of the case study
6 Implementation of
relationships in the database
Database contains less than half relevant and correctly interpreted relationships. (4.9) Database contains about half of relevant and correctly interpreted relationships. (6.4) Database contains more than half relevant and correctly interpreted relationships. (7.4) Database contains most of the relevant and
correctly interpreted relationships. (8.4) Database contains all the relevant and correctly interpreted relationships. (10)
6 Implementation of
Norm, 2nd Norm,
3rd Norm) Normalisation is not applied. (0) Only 1st norm is applied correctly (6.4). Only 1st norm and 2 norm is applied correctly (7.4). Most of the three norms are applied correctly (8.4). All three normalisations are applied correctly (10).
6 Query Design Queries are not implemented. (0) Some queries are implemented using one table (6.4). Some relevant and meaningful queries are implemented using one table (7.4). Many relevant and meaningful queries are implemented using many tables (8.4). Several relevant and meaningful queries are implemented using many tables and AND, OR, NOT
Rubric C – Assessment Task 3 (e-Portfolio) (marks out of 50)