Transcript Database Management Systems OUR Hospital Training Case

Database Management
Systems
Classroom Case Study
O.U.R. Hospital Employee Training
Scenario 1:
O.U.R. Hospital Employee Training Database
You’ve been tasked to create an Access database to be
used for scheduling and tracking training courses for your
hospital’s employees.
Along with HIPAA training for new employees, and
CPR/1st Aid certification and re-certification courses for
all employees, you also need to schedule training on the
new Electronic Medical Records (EMR) system that is
being implemented. While all employees will be trained,
they don’t all take the same EMR training modules.
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
2
Gather Requirements
• Identify the Problem Statement and Deliverables
• Identify the Stakeholders
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
3
Governance, Policies, and Procedures
O.U.R. Hospital Employee Training Database
• Governance
• Policies
Why?
Governance
What?
Policies
How?
Procedures
• Procedures
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
4
Data Dictionary
Based on the O.U.R. Hospital Training Database scenario, what data will
we need to collect in our database?
1.
2.
3.
4.
5.
List each data element that is known at this time
Define each data element
Determine the data type
Determine the field constraints and messages
Determine the data validation requirements
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
5
Conceptual Design
Based on the O.U.R. Hospital Training Database scenario, use Microsoft
VISIO to :
1. Diagram the tables
2. Add fields to the tables and data types
3. Add primary keys
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
6
ER Diagram
Based on the O.U.R. Hospital Training Database scenario, expand the
Microsoft VISIO diagram to :
1. Add relationships between tables and label with relationship verbs.
2. Normalize to 3NF
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
7
Physical Design
Based on the O.U.R. Hospital Training Database scenario, expand the
Microsoft VISIO diagram to :
1. Resolve many-to-many relationships
2. Add Cardinality
3. Add Optionality
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
8
Database Administration
• Based on the O.U.R. Hospital Training Database scenario, develop an
administration plan for our database.
•
•
•
•
•
What is the backup plan and schedule?
Who is responsible?
Who is responsible for recovery if needed?
Who is responsible and when will the backup/recovery process be tested?
What determines or when will the database be compacted/repaired?
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
9
Security Plan
Based on the O.U.R. Hospital Training Database scenario, develop a
security plan for our database.
1. Who gets access to the database? Who approves their access? Who
sets up their access?
2. Where does the database reside?
3. Will a password be required or will the location be password
protected? How frequently will the password change?
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
10
Testing Plan
Based on the O.U.R. Hospital Training Database scenario, create a test
plan to confirm all parts are working as required.
• Discuss:
•
•
•
•
Who should determine the test requirements?
Who should manage the test plan?
Who should perform the tests?
Who signs off that the tests were successful?
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
11
Training Plan
Based on the O.U.R. Hospital Training Database scenario, create a
training plan for the Access users.
• Why is a Training Plan needed?
• What should be included? Is a training/users manual needed?
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
12
Build Database
• Using the Physical Model diagram and the Data Dictionary, build the
Microsoft Access tables.
• Include data validation
• Include user messages
• Create minimum 10 records per table
• Build the table relationships
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
13
Queries
• Build queries to answer the following questions:
•
•
•
•
Which courses are offered this week, sorted by weekday?
Who is enrolled in today’s classes?
Who still needs HIPAA training?
Who is ready for CPR/First Aid recertification?
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
14
Forms
• Training administrative assistants need forms for enrolling the
employees, or to look up information when an employee calls with
questions.
• The instructors are responsible for scheduling the training courses
depending on their availability (not during department meetings or
their vacations) and with some time between sections to allow them
to reset the classroom. They need a form for scheduling.
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
15
Reports
• Training Management:
• Needs to report to upper management the training progress. They need
to see numbers of courses and sections offered and number of students
served by month along with the number of students still needing that
training.
• Training Instructors:
• Need to see their weekly training schedule at a glance with course
names, dates/times and room locations. They would like this grouped
by work day, then by course name and section.
• Need their class rosters for the day they teach sorted by employee last
name, then first name along with the employee’s department (to
customize examples when possible). They would also like the date/time
and room location, and course name listed at the top of the page.
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
16
Navigation Forms
Create a Navigation Form for ease of use by the Training department.
• Training Management wants to be able to quickly find and print the
reports showing employee training is progressing as required.
• Training Instructors want to find their class rosters, dates/times and
room locations.
• Training administrative assistants want to get to their forms for
enrolling the employees, or to look up information when an
employee calls with questions.
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
17
PivotTables & Charts
• Training Management would like a Pivot Chart to accompany their
progress report.
4/15/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
18