Access
Project
IS31
2
—
Fall
2015
Last Updated:
10
/
09
/20
1
5
David Liu, Ph.D. © 20
1
5
page
1
of
4
IS312 Access Project
This is an academic assignment: Report all unethical conduct to david.liu@csun.edu
Submission Instruction:
1.
Turn in your
project
anytime on box.com
, but
definitely
before
lab ends on the
due date
.
2.
The file naming conventi
on is
“
L
ast
–
name
,
F
irst
–
name
.
accdb
”
. Pay attention to the
capi
talization
, coma and blank space
, e.g.
“
Doe
,
John
.
accdb
”
3.
An image of the
R
elationship
D
iagram should
automatically po
p
–
up when the database
initially
opens
. The image
of the R
elationship
D
iagram
must
also include
the
following
four
pieces of
identifying
information
:
3.1.
Name
(
i.e. Last Name, First Name
)
3.2.
SID
3.3.
E
–
mail
3.4.
Profile picture
4.
If
the file you turn
ed
in has
a virus, you
will
get an automatic zero for this project.
5.
Projects that are submitted late or via e
–
mail will
NOT
be accepted
.
6.
If you did more than which the assignment has required (e.g. query, form,
VBA, etc.),
describe what you did in a .TXT file (using Notepad) for extra credit consideration. Use the
same naming convention (i.e. Last Name, First Name) and turn it in with your .accdb file.
Note:
A
dditional
updates
,
clarifications
and requirements
m
ay be
given
dur
ing classes and/or
the
labs
.
Hence, it is your responsibility to
be cognizant of any additions and to
incorporate
the
m in your
assignment
.
Learning objectives
:
1.
B
e self
–
sufficient in
acquiring knowledge
about
the usage of
Access
by using the built
–
in Help
(?) function in
Access
and by searching YouTube and Google
when
necessary
.
2.
To transform written requirements into a well thought out database design.
3.
Be able to a
nalyze data requirements and model the
m in
a relational database m
anagement
system, i.e. Access
.
4.
Us
e
the relationships window in Access
to create a R
elationship
D
iagram
.
5.
Be able to properly configure/constraint fields by setting data types and field properties
.
6.
Be able to
represent many
–
to
–
many relationships
with one
–
to
–
many relationships
.
7.
Be able to normalize a
database
design so that it has minimum
data redundancy
.
Note: There should be only one relationship between
each pair
of
tables.
In addition, there
is
NO
need
to populate the database with data.
O
ptimization of
database
performance is
not
a
criterion
for
the
design in this
assignment
.
Narrative d
escription of
the
“
medical p
ractice
”
dat
a
base
assignment
:
Design a database for a small medical practice
with
several
physicians
.
The purpose of the
database is to support the administrative functions such as
billing
, scheduling and
patient
tracking
.
The medical
practice has multiple offices
; hence,
physicians may
be scheduled
to be
at
different and/or multiple locations
.
However,
each physician has be assign a primary location.
Any patient m
ay see any physician, and, over
time,
a
patient
may
see
different
physicians. A
patient may complain about
multiple
symptoms (or
ailments
)
during a single appointment
.
A
unique
ailment
should
o
nly
be
listed once
in a single
appointment.
However, a single ailment
(e.g.
bronchitis)
can span multiple appointments. In addition
,
a
patient
may
see only
one physician per
appointment.
Since t
he
medical
practice
prefers to use
a
standard
set of
terminology/
phraseology
for
describing
all
the alimen
ts
, a
table
should be created
in the
database
to
keep
track of
all the
possible
ailment
s and their associated
attributes
(
e.g.
description
of the ailment
,
recommended
treatment,
charges
for that treatmen
t
and so on)
.
D
uring an
appointment
, the attending physician
can
treat
multiple ailments.
Access
Project
IS31
2
—
Fall
2015
Last Updated:
10
/
09
/20
1
5
David Liu, Ph.D. © 20
1
5
page
2
of
4
IS312 Access Project
This is an academic assignment: Report all unethical conduct to david.liu@csun.edu
Your solution should incl
ude separate tables for patients
,
physicians
,
appointments
and ailment
s.
For each table
, analyze
what should be the
appropriate
and necessary
fields
in order to satisfy
the purpose of the database design
. Points will be deducted when essential fields are missing
from the table. Point will also be
deducted
when unnecessary
necessary fields are
included
, e.g.
if you have foreign key
s in a table that are not linked, then it is unnecessary
.
The solution that
the
professor is
looking for
has
a pure j
unction table
—
one that
is used to
depict
a many
–
to
–
many
relationship from two one
–
to
–
many relationships
.
A
primary
key
with multiple fields
is needed
in
the pure junction table
to ensure that there are no duplicate diagnostic codes for the same
appointment.
The final results for the assignment include
a database that contains
only
th
e
require
d
tables
(i.e.
5 of them)
and
a
data model
shown
in the form of
a
R
elationship
s
D
iagram.
Be sure that all the
fields are appropriately typed
and their properties configured/constrained
.
D
o not enter any data
into the tables
.
Recommended steps to f
ollowing
:
1.
Start a new database from the Blank database
template in
Access 2013
, i.e. do not start
from any other template
s
or existing databases.
2.
You are to
initially
c
reate the following 4 tables:
Patients
Physicians
Appointments
Ailments
3.
General notes regarding database design
Decide what fields are
appropriate for which tables.
Keep in mind the purpose of the database
design
, i.e.
to support the
administrative functions such as
billing, scheduling and
patient tracking
for
the
medical practice
.
It would be helpful for you to
search
for open source
ap
plications for these functions to serve as a reference on what fields are
necessary.
Always work in the Design View when you are assignment field names and field
properties, i.e. do not work in the Datasheet View
.
Do NOT enter data into the tables, i.e.
just define the field properties of the
tables
.
4.
Patient table design
What kind of
patient
information does the medical practice need
in order to do
assure that it gets paid?
For example: contact information, employment
information, insurance information, e
tc.
For each piece of information that
is needed in the database design, it should
become
a field that you need to specify
.
It is important to have the appropriate level of
granularity
in the design of
the
fields. For example, a patient’s name is not just
one field
, it
should be at least first
name, last name, etc. Likewise,
an
address is not just one field, but
it should be
separated into
street, city, state, zip, etc.
For each field, be sure to assign the appropriate
Data Type
to ensure data
integrity and
the
support
of
downstream
manipulation/extraction of the data.
Also for each field, be sure to assign the appropriate
Field Properties
to
constraint the value
s
it
should hold
and to facilitate formatting
of the content
.
What is the unique identifier for a patient? Assign that field to be the Primary Key
for this table.
5.
Repeat
the process in
4
for the remaining 3 tables
Sample design
considerations
for the Physician table: what kind of
physician
information would an insur
ance company want when
it
process a claim for
Access
Project
IS31
2
—
Fall
2015
Last Updated:
10
/
09
/20
1
5
David Liu, Ph.D. © 20
1
5
page
3
of
4
IS312 Access Project
This is an academic assignment: Report all unethical conduct to david.liu@csun.edu
reimbursement?
What kind of physician information
would the
other stakeholders
(e.g. the patient and
the medical practice
itself) like to have on the doctor
s
?
There is no need to create tables for office locat
ions and insurance companies.
Sample design
considerations
for the Appointments table: who is schedule
d
to
meet? When? Where? Why?
Sample design
considerations
for the Ailments table
: what is the problem?
Is it a
symptom, an ailment or pathology? Be clear
about the
difference between the
values that a field holds
(i.e. the content) versus the field itself (i.e. the container).
For example,
the field
“
ailment
description”
can hold
explanatory
value such as
“migraine headache”,
“torn
right
meniscus”, “
subdural hematoma”, etc.
T
hose
descriptive
values
should
not be individual fields.
BTW, t
here are more hints
about the fields of the Ailment table
in the narrative description
section
on page 1
of this document
.
6.
Prepare
the R
elationship
canvas
Open the Rel
ationship canvas
by clicking the Relationship button on the
P
opulate it with the 4 tables
by right
–
clicking on the
blank
Relationship canvas
and select the desired option in the context menu.
Enlarge the window for each table
so that mos
t (if not all) the fields are visible
without scrolling.
Position
the tables so that
they do not obscure each other.
Common mistakes to avoid
:
a)
If Access complains that someone else is working on the table(s) or
relationship
when you attempt to make modifications
, it
is
because
you
have more than one
associated
object open
ed
with modification(s).
Hence, t
he
best remedy in this situation
is to Save and/or Close those
other objects before you continue.
b)
If Access complains when
you try to link two fields together,
a
frequent
mistake is that
the two fields are of different Data Types, e.g. a
Number field cannot be linked to a Text field. BTW, an AutoNumber
field can be linked to a Number field. However, you should never link
two A
utoNumber fields together.
c)
If Access does not let you assign a field to be the primary key,
the
cause
may be
that you have populated the table with
records and the
in the
values
in
that field
are not unique.
Hence, by definition,
that field
cannot be the p
rimary key. The
best remedy in this situation
is to
delete all the records in the table.
7.
Define
r
elationships
Recall that you need to
associate
two one
–
to
–
many relationships in order to
depict
a many
–
to
–
many relationship.
There is a many
–
to
–
many
relationship
between
patients and physicians.
The Appointment tabl
e brings together the Patients t
able and the Physicians
table.
There is a many
–
to
–
many relationship between (a patient’s) appointment and
his/her
ailments.
A
5
th
table
is needed and it serve
s
the following purpose
s
:
a)
The 5
th
table
brings together the Appointments table and Ailments
table.
b)
Since the 5
th
table is
a
pure junction table, the only two fields in it are
the primary keys of the
Appointments table and Ailments table
.
c)
The 5
th
table
ensu
res that
each unique ailment should only be listed
once per appointment.
You do this by creating a key that is a
c
ombination of
the
two fields
in it
.
H
ighlight
the
two fields,
then right
–
Access
Project
IS31
2
—
Fall
2015
Last Updated:
10
/
09
/20
1
5
David Liu, Ph.D. © 20
1
5
page
4
of
4
IS312 Access Project
This is an academic assignment: Report all unethical conduct to david.liu@csun.edu
click
and select the desired option in the context menu.
If done
properly, you
should see a key symbol
by
each of the
two
fields.
There is only one relationship between each set of tables.
At least one side of
each
relationship is a primary key.
There
should be
no foreign keys in any of the tables
which
are not
use to
link
to
another table
.
Open up the tables so that most (if not all) the fields are visible without scrolling.
Do
not
cross your relationships i
n th
e Relationships D
iagram.
Show the relationship type (e.g. one
–
one, one
–
to many, etc.) by right
–
clicking on a
relationship
line
and check the Enforce Referential Integrity checkbox.
8.
Create
a report
When you are all done
with the above 7 steps
and are satisfied with your
database design
, then
c
reate
a report from the Relationship D
iagram
.
There is a
button in the Design tab titled Relationship R
eport that automatically creates
a
report from the Relationship Diagram.
This report is a static image snap shot of the Relationship Diagram. Hence
, if you
make
subsequent
changes to
the
database
an
d/or the Relationship Diagram
,
changes
will not be reflected in the report
automatically. Therefore,
create the
report
toward the end
of the assignment when everything is finalized
. Otherwise
,
you will need to
re
–
snap an
image
from the Relationship Diagram
,
and
redo all
the
embellishments for
the report
.
Open up the report in Design View.
Add in the
3
pieces of identifying information (
described
at the top of page 1
of
this document
)
on
the report.
Apply
some rendering features to
embellish
the report
so th
at it is more
appealing.
Save
your report
and
give it a
nam
e
.
9.
Create a macro to launch
(i.e. OpenReport)
the report
by the name
.
As an additional
challenge,
you
can
add an additional action to the macro so that the
first
action
of the
macro
is to
open a form that contains the 3 pieces of identifying information and
the
second
action
of the macro is to
open the report with
image of
the Relationship D
iagram.
10.
In order for this macro to be automatically invoked by Access at the opening of
your
databa
se,
the macro
needs to
have
a
certain
reserve
d
name.
Go
s
earch the internet to
find out what that
name
is
and named your macro accordingly
.
In addition to the demonstrating your ability to follow verbal and written instructions, you are
graded on
the
following
:
1.
Overall table design
2.
Completeness
of the
field specification
s
, i.e. right fields in the right table
s
3.
Thoroughness
of the
field specification
s
, i.e. data types and
details in the
field properties
4.
Assignment of primary keys
5.
Assignment of relation
ships
6.
Use of
a
macro
Final n
ote
s
:
1.
Make sure every
view
is positioned properly for
clear presentation when it is open
.
2.
Quality and presentation counts, e.g. don’t cross lines in the
Relationship D
iagram or obscure
any of the
info
rmation
.
3.
This is not a team
project and plagiarism will not be tolerated.
4.
Do
not
e
–
mail your project because
the CSUN faculty mail
–
sever
will not
pass it through
.