FACULTY OF LAW AND
BUSINESS
Peter Faber Business School, North Sydney
SEMESTER 1, 2019
DATA 200: Data and Information Management
Assignment Three (Individual): e-Portfolio (45%)
Due: before 11.55pm 26/05/19
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:
Specifications:
1) No of minimum records of each entity:
Patients 15
Receptionists 05
Nurses 10
Doctors 10
Medication 10
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
ehind 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.
) 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 3:
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 co
ect one.
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,
ut 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
eceptionists 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,
ut 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.
Ru
ic C – Assessment Task 3 (e-Portfolio) (marks out of 45)
ILO
s
Criteria NN (points) PA (points) CR (points) DI (points) HD (points)
LO
4, 5,
6
Reflection report
capturing critical
learning points of
data and
information
management
throughout the
semester
Reflection report
largely repeats the
textbook or do not
show the ability for
eflective 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
e some e
ors in
eferences (6.4)
Reflection report
shows a fair level of
eflective learning,
capture most of
critical learning
points, properly
eferenced (7.4)
Reflection report
shows a good
level of reflective
learning, capture
all critical learning
points, properly
eferenced (8.4)
Reflection report
shows that the
learning materials
are well analysed
and deeply
eflected on, all
critical learning
points are
comprehensively
captured, properly
eferenced (10)
LO
4, 5,
6
Implementation of
all the entities,
keys, field names
and properties in
the database
Database contains
less than half of
co
ectly identified
entities and keys of
the case study (4.9)
Database contains
about half of
co
ectly identified
entities and keys of
the case study (6.4)
Database contains
more than half
co
ectly identified
entities and keys of
the case study (7.4)
Database contains
most of the
co
ectly identified
entities and keys
of the case study
(8.4)
Database contains
all the co
ectly
identified entities,
keys, field names
and properties of
the case study
(10)
LO
4, 5,
6
Implementation of
elationships in the
database
Database contains
less than half relevant
and co
ectly
interpreted
elationships. (2.9)
Database contains
about half of
elevant and
co
ectly interpreted
elationships. (3.4)
Database contains
more than half
elevant and
co
ectly interpreted
elationships. (4.4)
Database
contains most of
the relevant and
co
ectly
interpreted
elationships. (5.4)
Database contains
all the relevant
and co
ectly
interpreted
elationships. (7)
LO
4, 5,
6
Implementation of
Normalisation (1st
Norm, 2nd Norm,
3rd Norm)
Normalisation is not
applied. (0)
Only 1st norm is
applied co
ectly
(4.4).
Only 1st norm and 2
norm is applied
co
ectly (5.4).
Most of the three
norms are applied
co
ectly (6.4).
All three
normalisations are
applied co
ectly
(8).
LO
4, 5,
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
(10).
DATA200: Data and Information Management
Group Assessment
Submitted by:
Roja Khadka (S XXXXXXXXXX)
Sujan Ghimire (S XXXXXXXXXX)
Submitted to: Mr. Shakir Karim
Part A:-
Figure 1: Entity relationship diagram of the emergency room
Figure 1 above depicts the entity relationship diagram (ERD) of the emergency room. The ERD has multiple entities with their attributes listed under them and illustrates the relationship between each entities using the crows feet notation. Below we have explained the kind of relationship between the entities.
Entities
Relationship
Docto
Personal Details
One to one relationship
Doctor, patient, receptionist or nurse can have only one personal details.
Patient
Receptionist
Nurse
Personal Details
Docto
One tomany relationship
Personal details table can have details of one or many doctors, patient, receptionist or nurse.
Patient
Receptionist
Nurse
Docto
Patient
Zero to many relationship
A doctor diagnosis zero or many patients in a shift.
A nurse can supervise zero to many beds that have patients assigned to them in a shift.
A receptionist can admit zero to many patients in a shift.
Nurse
Receptionist
Patient
Docto
One to one relationship
A patient is looked by only one case and triage doctor.
A patient is admitted by only one receptionist.
Receptionist
Patient
Nurse
One to many relationship
A patient can be supervised by one or many nurses since shift changes in a day which means different nurse has to be assigned to a bed/patient each time shift of a nurse ends.
Shift
Docto
One to many relationship
A shift has one or many doctor, nurse or receptionist since the case study states