Transcript data types

Database Structure
Basics
Pam Kennedy
Analyst, McKing Consulting
Regional Training Workshop on Influenza Data Management
Phnom Penh, Cambodia
July 27 – August 2, 2013
National Center for Immunization & Respiratory Diseases
Influenza Division
Course Objectives
Section 1
•
Section 2
Designing a System




•
Data Entry
Function
Structure
Data Types
Data Relationships
Questions
 Data Standards
 Forms
• Controls
• Control Validation
 Data Security
Designing a System
Function
• Function of a database is to facilitate the analysis of
large amounts of data quickly and efficiently
• Effective databases result from upfront planning
before the first data table is built
 Who has data /who is collecting data
 What data is being collected/needs to be collected
 Why is the data needed
 When is data collected
 Where is data collected
Designing a System
Structure
Database structure
 How many data tables
are needed?
 Who needs access to
the files?
 How will data be entered?  Will data be used in
calculations?
 How will data be stored?
 Will regular reports be
 What level of
needed?
data/database security is
needed?
 Is there a need to
export data?
Designing a System
Structure
• Database structure
 Excel is a ‘flat’ file system
 A "flat file" is a plain text or mixed text which usually contains
one record per line
 There are no structural relationships between the records.
 ACCESS is a ‘relational’ system
 A relational database matches data by using common
characteristics found within the data set. The resulting groups
of data are organized and are much easier for many people
to understand.
http://en.wikipedia.org/wiki/Flat_file_database
http://en.wikipedia.org/wiki/Relational_database
Designing a System
Structure
• Excel and ACCESS both store data in a ‘column/row’
format
• Excel
 A ‘worksheet’ is the data storage tool
 Excel workbook is a collection of worksheets
 Excel worksheets are ‘flat files’
• ACCESS
 A ‘table’ is the data storage tool (equivalent to an Excel ‘worksheet’)
 Tables relate to other tables forming the basis for data analysis
 Tables group like elements together

Patient information – name, address, phone, Date of Birth,

Site information - site name, site contact info, type of site
Designing a System
Structure
• Worksheets/Tables store data within the database
 Excel Worksheet
 ACCESS Table
Fields
Record
Field Value
Designing a System
Data Types
• There are different elements of data:
 Names, Dates, Cost information, Notes
• Data type is defined during database construction
• Excel and ACCESS both have default data types
 Excel – ‘general’
 ACCESS – ‘text’
• Data type determines how data can be used in
calculations
 Important during analysis or data summary
• Commonly used data types:
 Excel – general, date, currency
 ACCESS – text, number, date
Designing a System
Data Types
• Shared data types – Excel and ACCESS




Text - for use storing names, short titles, country
Number - for use storing numerical data
Date/Time - for use storing dates and can capture time
Currency - for use storing costs, price, etc.
• ACCESS Specific Data Types
 Memo - text field without character limit – for use storing notes
 In Excel – there is no character limit in any one cell
 Yes/No - creates check box field in a table (yes = box checked)
 AutoNumber – field that assigns an incremental number to each
record
Designing a System
Data Types
• Excel
Designing a System
Data Types
• ACCESS
Designing a System
Data Relationships
• ACCESS Specific
• What is a relational database ?
 System that links data by using common characteristics
 System that stores data in logical manner making analysis easier
 Provide more efficient means of dealing with duplicated data
• An ACCESS database is composed of the following
elements:
 Tables – Store data
 Queries – Aid in sorting, summing, calculating data
 Forms – Aid data input
Designing a System
Data Relationships
• Data relationships are critical
• Relationships should be set up as the database
is built
• Questions to consider:
 How do the fields in different tables relate?
 Is there a common factor in all the tables?
• This is the unique field that will apply across all data
 Consider data to be collected and how it will be used
 Consider data types – relationships link data of same type
Designing a System
Data Relationships
• Key types of relationships
 One to One – A unique record in Table 1 matches an exact
record in Table 2
 One to Many – A unique record in Table 1 matches to several
records in Table 2
SARI Data
Table 1
Lab Data
Table 2
Patient ID
Date of
specimen
collection
Date of Visit
Type of
sample
collected
Age
Sex
Results
Patient ID
Example of Data Relationship
Example of Data Relationship
Data Entry
•
Standards
•
Forms
 Form functions
 Controls
• Drop down
• Check boxes
• Data Validation
 Queries
Data Security
Data Entry
Standards
• Why standardize?
 Create common understanding
 Ease comparison of data year to year
 Increase efficiency for analyzing
 Know where data is
 Know what people are asking for
 Everyone enters data in same way
 Ease comparison of data from group to group
 Example
 Dates
http://en.wikipedia.org/wiki/Football
Data Entry
Standards
• Dates
12/2/2013
2/12/2013
• Football
http://en.wikipedia.org/wiki/Football
Data Entry
Standards
• Standards
 Definition of data types during design phase ensures
standardization of data




Numbers stored as numbers
Currency stored in specified currency format
Dates stored uniformly ‘Yes’ stored as ‘Yes’ not ‘Y’
 ‘Controls’ can be used to assist in maintaining data
standards
 Limit data entry choices to defined terms
 Excel and ACCESS have similar ‘controls’ available
Data Entry
Forms
• Building a ‘data entry form’




Identify data fields
Determine structure
Determine data field relationship (ACCESS)
Determine data type for each data field
 Dates = date type
 Cost = currency
 Determine common standard responses
 “Yes” will by stored as ‘Yes’
 Choices will be ‘Yes’, ‘No’, ‘NA’
 Date format = DDMMYY or DDMMYYYY
• Most of these decisions should be made during
database structure design
Data Entry
Forms
• Excel
Data Entry
Forms
• ACCESS
Data Entry
Forms
• Queries
 Allow you to view, change, and analyze data in different
ways
 Several types :




•
Select
Parameter
Crosstab
Action
A select query is the most common type of query
 Retrieve data from one or more tables
 Group records and calculate sums, counts, averages, and
other types of totals
Stefano TEMPIA
Microsoft Access Tutorial – Lecture 2
Data Entry
Forms
• Select query
Stefano TEMPIA
Microsoft Access Tutorial – Lecture 2
Data Entry
Data Security
• Why security
 Ensure data is maintained according to set standards
 Ensure access to data is controlled
 Ensure accuracy of data
• Security levels
 Excel
 Workbook
 Worksheet
 ACCESS
 Database
 Record
Designing a System
Database Basics
•
Questions???
Designing a System
Database Basics
• Final Thoughts
 The database should meet your current needs and
any future needs you can anticipate
 Build a structure that works for you and provides what
you need
 Be prepared to change and adapt as your data needs
change
Keep it SIMPLE!
THANK YOU
For more information please contact Centers for Disease Control and
Prevention
1600 Clifton Road NE, Atlanta, GA 30333
Telephone, 1-800-CDC-INFO (232-4636)/TTY: 1-888-232-6348
E-mail: [email protected] Web: www.cdc.gov
The findings and conclusions in this report are those of the authors and do not necessarily represent the official
position of the Centers for Disease Control and Prevention.
National Center for Immunization & Respiratory Diseases
Influenza Division
Designing a System
Database Basics
• EXERCISE
Setting up tables using national surveillance forms
Setting up data entry controls
Entering data using controls