Transcript Database Management Systems

Database Management
Systems
Pierce College
Part B
Module 6:
Database Development
Lecture 1: Introduction to Microsoft Access 2010
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
2
Introduction Links
• Database design basics for Access 2010
- (20 minutes)
http://office.microsoft.com/en-us/access-help/database-design-basics-HA010341617.aspx?CTT=1
• Make the switch to Access 2010 – great as overview (20 minutes)
http://office.microsoft.com/en-us/access-help/make-the-switch-to-access-2010-RZ101791922.aspx?CTT=3
• **Getting started with Access 2010 (includes video) (40 min)
http://office.microsoft.com/en-us/access-help/getting-started-with-access-2010-HA010341722.aspx?CTT=3
• Demo: Bring your business data together in an Access database (6
minutes video – 20 min for text version)
http://office.microsoft.com/en-us/access-help/demo-bring-your-business-data-together-in-an-access-database-HA010255203.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
3
Other Reference Links
• Structure of a database Access 2007 text version (20 minutes)
http://office.microsoft.com/en-us/access-help/learn-the-structure-of-an-access-database-HA001213954.aspx?CTT=3
• Access 2007 reserved words and symbols (20 minutes)
http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx?CTT=1
• Access 2010 specifications (20 minutes)
http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx?CTT=1
• Access Glossary (5 minutes scan)
http://office.microsoft.com/en-us/access-help/access-glossary-HA010218202.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
4
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/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
5
Module 6:
Database Development
Lecture 2: Microsoft Access 2010 Structure and Navigation
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
6
Create Database & Navigation
• **Access 2010 Database Tasks (15 min Read; 1 hr Study)
http://office.microsoft.com/en-us/access-help/access-2010-database-tasks-HA101829991.aspx?CTT=5&origin=HA010341722
• Create an Access database (15 min Read; 1 hr Study/do)
http://office.microsoft.com/en-us/access-help/create-an-access-database-HA010341576.aspx?CTT=1
• Guide to the Access 2010 user interface (15 min Read; 1 hr Study/do)
http://office.microsoft.com/en-us/access-help/guide-to-the-access-2010-user-interface-HA010341735.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
7
Module 6:
Database Development
Lecture 3: Microsoft Access 2010 Tables
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
8
Create Tables
• Introduction to tables (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-tables-HA010341584.aspx?CTT=1
• Create tables for a new database (40 minutes)
(online training - this feeds to “Design the tables for a new database” series link on right side)
http://office.microsoft.com/en-us/access-help/create-tables-for-a-new-database-RZ101772997.aspx
• Online training series of 9 videos with practices, quizzes, and Quick Reference
Cards: (40-50 minutes each)
Use “Design the tables for a new database” (online series) only for this lecture module. The others will be referenced in subsequent slides.
http://office.microsoft.com/en-us/access-help/design-the-tables-for-a-new-database-RZ101772996.aspx
• Introduction to data types and field properties (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-data-types-and-field-properties-HA010341783.aspx
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
9
Working with relationships
• “Create relationships for a new database” (online series) (40-50 minutes)
http://office.microsoft.com/en-us/access-help/create-relationships-for-a-new-database-RZ101772998.aspx
• Article: Guide to table relationships (20 minutes)
http://office.microsoft.com/en-us/access-help/guide-to-table-relationships-HA010120534.aspx?CTT=1
• Microsoft video on database relationships using Access 2007 (7 minutes)
http://office.microsoft.com/video.aspx?assetid=ES010277683&vwidth=884&vheight=540&CTT=11&Origin=HA010254901
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
10
Working with data
• Restrict data input by using a validation rule (20 minutes)
http://office.microsoft.com/en-us/access-help/restrict-data-input-by-using-a-validation-rule-HA010341586.aspx
• Add a calculated field to a table (20 minutes)
http://office.microsoft.com/en-us/access-help/add-a-calculated-field-to-a-table-HA101820564.aspx?CTT=1
• Introduction to importing and exporting data (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-importing-and-exporting-data-HA101790599.aspx?CTT=1
• Video: Import data to an Access database (6 minutes)
http://office.microsoft.com/en-us/access-help/video-import-data-to-an-access-database-VA101815333.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
11
Module 6:
Database Development
Lecture 4: Microsoft Access 2010 Queries
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
12
Create queries links
• Introduction to queries (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-queries-HA010341786.aspx?CTT=1
• Create queries for a new database (online series) (40 minutes)
http://office.microsoft.com/en-us/access-help/create-queries-for-a-new-database-RZ101772999.aspx?CTT=1
• Video: Calculate values in a query (3 minutes)
http://office.microsoft.com/en-us/access-help/video-calculate-values-in-a-query-VA101822639.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
13
Create expressions links
• Video: Create an expression (6 minutes)
•
http://office.microsoft.com/en-us/access-help/video-create-an-expression-VA101812615.aspx?CTT=3
• Article: Create an expression (20 minutes)
•
http://office.microsoft.com/en-us/access-help/create-an-expression-HA010341590.aspx?CTT=1
• Article: Use the expression builder (20 minutes)
•
http://office.microsoft.com/en-us/access-help/use-the-expression-builder-HA101812460.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
14
Module 6:
Database Development
Lecture 5: Microsoft Access 2010 Forms
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
15
Create forms links
• Article: Introduction to forms (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-forms-HA010343724.aspx?CTT=1
• Create a form by using the Form tool (20 minutes)
http://office.microsoft.com/en-us/access-help/create-a-form-by-using-the-form-tool-HA010341567.aspx?CTT=3
• Create a form by using the Form Wizard (20 minutes)
http://office.microsoft.com/en-us/access-help/create-a-form-by-using-the-form-wizard-HA010341568.aspx?CTT=3
• Create forms for a new database (online series) (40 minutes)
http://office.microsoft.com/en-us/access-help/create-forms-for-a-new-database-RZ101773001.aspx
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
16
More forms links
• Video: Set the record source for a form or report (3.5 minutes)
http://office.microsoft.com/en-us/access-help/video-set-the-record-source-for-a-form-or-report-VA101814106.aspx?CTT=1
• Create a tabbed form (20 minutes)
http://office.microsoft.com/en-us/access-help/create-a-tabbed-form-HA010341583.aspx?CTT=3
• Video: Pivot your data in Access (6.5 minutes)
http://office.microsoft.com/en-us/access-help/video-pivot-your-data-in-access-VA101842926.aspx?CTT=1
• Create PivotTable or PivotChart in a desktop database (20 minutes)
http://office.microsoft.com/en-us/access-help/create-pivottable-or-pivotchart-views-in-a-desktop-database-HA101901543.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
17
Module 6:
Database Development
Lecture 6: Microsoft Access 2010 Reports
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
18
Create reports links
• Introduction to reports (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-reports-HA010343725.aspx?CTT=3
• Create a simple report (20 minutes)
http://office.microsoft.com/en-us/access-help/create-a-simple-report-HA010341579.aspx?CTT=3
• Create a grouped or summary report (20 minutes)
http://office.microsoft.com/en-us/access-help/create-a-grouped-or-summary-report-HA010341571.aspx?CTT=3
• Video: Set the record source for a form or report (review from
forms) (3.5 minutes)
http://office.microsoft.com/en-us/access-help/video-set-the-record-source-for-a-form-or-report-VA101814106.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
19
More reports links
• Create reports for a new database (online series) (40 minutes)
http://office.microsoft.com/en-us/access-help/create-reports-for-a-new-database-RZ101773004.aspx?CTT=1
• Video: Use conditional formatting on reports (5 minutes)
http://office.microsoft.com/en-us/access-help/video-use-conditional-formatting-on-reports-VA101814119.aspx?CTT=1
• Video: Highlight data on forms by using conditional formatting (5.5
minutes)
http://office.microsoft.com/en-us/access-help/video-highlight-data-on-forms-by-using-conditional-formatting-VA102438087.aspx?CTT=3
• Display comparative data visually with data bars (20 minutes)
http://office.microsoft.com/en-us/access-help/display-comparative-data-visually-with-data-bars-HA101851088.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
20
Module 6:
Database Development
Lecture 7: Microsoft Access 2010 Database Administration & Security
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
21
Access Security and Administration
• Introduction to Access 2010 security (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-access-2010-security-HA010341741.aspx
• Create strong passwords (20 minutes)
http://www.microsoft.com/security/online-privacy/passwords-create.aspx
• Compact and repair a database (20 minutes)
http://office.microsoft.com/en-us/access-help/compact-and-repair-a-database-HA010341740.aspx?CTT=1
• Archive Access data (20 minutes)
http://office.microsoft.com/en-us/access-help/archive-access-data-HA010288259.aspx?CTT=3
• Protect your data with backup and restore processes (20 minutes)
http://office.microsoft.com/en-us/access-help/protect-your-data-with-backup-and-restore-processes-HA010341510.aspx?CTT=1
• Use sandbox mode in Access 2010 (20 minutes)
http://office.microsoft.com/en-us/access-help/use-sandbox-mode-in-access-2010-HA010342092.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
22
Module 6:
Database Development
Lecture 8: Microsoft Access 2010 Advanced Topics
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
23
Access Templates
• Using Templates in Access 2010 (video) (2 minutes)
http://office.microsoft.com/en-us/videos/video-get-started-using-templates-in-access-2010-VA101944638.aspx?CTT=3
• Microsoft templates (20 minutes)
http://office.microsoft.com/en-us/templates/results.aspx?qu=access&av=zac
• Introduction to the Access 2010 templates (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-the-access-2010-templates-HA010341734.aspx?CTT=1
• Video: Modify the Access 2010 templates (3.5 minutes)
http://office.microsoft.com/en-us/access-help/video-modify-the-access-2010-templates-VA101794442.aspx?CTT=3
• Build an Access database to share on the Web (20 minutes)
http://office.microsoft.com/en-us/access-help/build-an-access-database-to-share-on-the-web-HA010356866.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
24
Access Programming and Macros
• Create a data macro (20 minutes)
http://office.microsoft.com/en-us/access-help/create-a-data-macro-HA010378170.aspx?CTT=1
• Video: Create a user interface (UI) macro (4 minutes)
http://office.microsoft.com/en-us/access-help/video-create-a-user-interface-ui-macro-VA101814109.aspx?CTT=3
• Video: Introduction to the Macro Builder (3.5 minutes)
http://office.microsoft.com/en-us/access-help/video-introduction-to-the-macro-builder-VA100393510.aspx?CTT=3
• Introduction to Access programming (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-access-programming-HA010341717.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
25
Advanced Access Links
• Ten quick tips to help work more efficiently in Access 2010 – Video (3.5 minutes)
http://office.microsoft.com/en-us/access-help/video-ten-quick-tips-to-help-work-more-efficiently-in-access-2010-VA101831095.aspx?CTT=1
• Create dynamic reports in Access using linked tables – Article (20 minutes)
http://office.microsoft.com/en-us/support/create-dynamic-reports-in-access-using-linked-tables-HA001042820.aspx?CTT=1
[Note: this is a previous version of Access but the content is still valid.]
• Import or link to data in an Excel workbook – Article (20 minutes)
http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-an-excel-workbook-HA010341760.aspx?CTT=1
• Split an Access database – Article (20 minutes)
http://office.microsoft.com/en-us/access-help/split-an-access-database-HA102749583.aspx?CTT=1
• Ways to share an Access database – Article (20 minutes)
http://office.microsoft.com/en-us/access-help/ways-to-share-an-access-database-HA010342110.aspx?CTT=1
• Create a navigation form – Video (5 minutes)
http://office.microsoft.com/en-us/access-help/video-create-a-navigation-form-VA101812613.aspx?CTT=3
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
26
Advanced Access Links – (cont.)
• Access 2010 Custom Themes – Article (20 minutes)
http://blogs.office.com/b/microsoft-access/archive/2009/09/25/access-2010-custom-themes.aspx
• Improve the appearance of your Access forms and reports with
Office Themes – Video (8.5 minutes)
http://office.microsoft.com/en-us/access-help/video-improve-the-appearance-of-your-access-forms-andreports-with-office-themes-VA101815213.aspx?CTT=1
• Database Tasks using Access 2010 – Article (20 minutes)
[NOTE: this is an overview of many Access features. Could be for introduction, or better for overall
review.]
http://office.microsoft.com/en-us/access-help/access-2010-database-tasksHA101829991.aspx?CTT=5&origin=HA010341722
• Add or change a lookup column (20 minutes)
http://office.microsoft.com/en-us/access-help/add-or-change-a-lookup-column-HA010163773.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
27
Transitioning to Access 2013
• Discontinued features in Access 2013 (10 minutes)
http://office.microsoft.com/en-us/access-help/discontinued-features-and-modified-functionality-in-access-2013-HA102749226.aspx?CTT=1
• Access 2013 Quick Start Guide (6 page reference sheet) (20 minutes)
http://office.microsoft.com/en-us/support/access-2013-quick-start-guide-HA103673689.aspx?CTT=1
• Switching to Access 2013 (20 minutes)
http://office.microsoft.com/enus/support/results.aspx?htags=htgprgntxO15Training&queryid=dbeb016e%2D76d2%2D4041%2Dbd62%2D87210c054359&avg=zac
• Make the switch to Access 2013 – online training (20 minutes)
http://office.microsoft.com/en-us/support/make-the-switch-to-access-2013-RZ102923802.aspx?CTT=1
• Training courses for Access 2013 – Reference List (5 minutes)
http://office.microsoft.com/en-us/access-help/training-courses-for-access-2013-HA104030993.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
28
Module 6:
Database Development
Lecture 9: Microsoft Access 2010 SQL
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
29
Structured Query Language (SQL)
“When you want to retrieve data from a database, you ask for the data
by using Structured Query Language, or SQL.
SQL is a computer language that closely resembles English that
database programs understand.
Knowing SQL is important because every query in Microsoft Access
uses SQL.
Understanding how SQL works can help create better queries, and can
make it easier for you to fix a query when it is not returning the results
that you want.”
http://office.microsoft.com/en-us/access-help/introduction-to-access-sql-HA010341468.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
30
Access SQL (Structured Query Language)
• Introduction to Access SQL (20 minutes)
http://office.microsoft.com/en-us/access-help/introduction-to-access-sql-HA010341468.aspx?CTT=1
• Access SQL: basic concepts, vocabulary, and syntax (20 minutes)
http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx
• Comparison of Microsoft Access SQL and ANSI SQL (20 minutes)
http://msdn.microsoft.com/en-us/library/office/bb208890(v=office.12).aspx
• Microsoft Access SQL Reference (5 minutes)
http://msdn.microsoft.com/en-us/library/office/ff841692(v=office.14).aspx
• Access 2010 Development (20 minutes)
http://msdn.microsoft.com/en-us/library/office/ff604965(v=office.14).aspx
• Get help for built-in functions, properties, macro actions, and SQL keywords (20 minutes)
http://office.microsoft.com/en-us/access-help/get-help-for-built-in-functions-properties-macro-actions-and-sql-keywords-HA010357048.aspx?CTT=1
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
31
Module 7:
Database Security
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
32
Module 7:
Database Security
Lecture 1: CAHIMS 7.1 Privacy & Security Policies & Compliance
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
33
Module 7:
Database Security
Lecture 2: CAHIMS 7.3 Data and Systems Security Management
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
34
Module 7:
Database Security
Lecture 3: CAHIMS 7.4 Disaster Recovery and Business Continuity
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
35
Module 8:
Testing and Training
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
36
Module 8:
Testing and Training
Lecture 1: CAHIMS 6.1 Testing Methodology and Planning
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
37
Module 8:
Testing and Training
Lecture 2: CAHIMS 6.2 Testing Implementation and Documentation
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
38
Module 8:
Testing and Training
Lecture 1: CAHIMS 5.2 End User Training and Support
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
39
Module 9:
Capstone Project
Applying Documentation and Development Skills in Access
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
40
Nurse Rotation Database
•
Access database design and development
•
Team projects
•
Peer review by other teams
•
Documentation requirements:
1.
2.
3.
4.
5.
6.
Combined Requirements document
(Business, Functional, Technical)
Data Dictionary
Training Plan & Users Guide
Test Plan
Combined Governance, Policies and
Procedures document
Security should be addressed in
numbers 1, 4, and 5
4/16/2013 5:00 PM
Pierce College - CIS260 Database Management Systems
41
Nursing Rotation Scenario
• It is the first week in your new healthcare database career/(internship???). The IT Manager comes to
you (all the new interns??? If team work is desired) and says “I think I have just the project for you to
get your feet wet. It will be a chance for you to use your Access skills and to work with some of our
healthcare staff. Our house supervisor wants a way to track nursing staff work rotations, make shift
changes, and to easily print weekly and monthly reports.
• Up until now, it has just been tracked in a spreadsheet, but it has limitations. Since we already have
the software, we believe using Access will quickly give them the functionality they need at this time
without additional software costs. Talk to House Supervisor about the requirements. I estimate it will
take you about two weeks (class time). Be sure to document everything so it can be supported by our
team.”
• After talking to House Supervisor, you find that there are 3 shifts a day: 7am-3pm, 3pm-11pm, and
11pm-7am. The following information on each nurse is also needed in the system for the House
Supervisor:
•
•
•
4/16/2013 5:00 PM
•
Nurse name
•
Employee ID
•
Shift
•
Work contact phone and email
•
Unit/floor
•
Home contact phone and email
•
PatientID
•
Day
•
Shift Supervisor
Whether it is a federal holiday or not; ideally they only work one major holiday a calendar year.
After you think about it, you ask a few more questions and also found out that a nurse isn’t supposed
to work two shifts back to back unless in an emergency, nor more than 36 hours a week.
Also, the nurse should work the same unit/floor for the week if possible, and should watch the same
patient if he/she is still on the floor for the nurse’s next shift for “patient continuity”.
Pierce College - CIS260 Database Management Systems
42