Infromation system – access project
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
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
