Data Management

1
Data Management Database Design
Richard Harris
HarrisRichardM_at_uams.edu
501-257-5878 GCRC Informatics Core Director
2
Data Management - Outline

• Goal level set on what data and database and
  database management system means to us
• Outline
• Terms
• Relational Database
• Data Management

3
For note takers
Determine all relevant data to be
collected. Create a Data Dictionary
(Metadata). De-Identify data. Backup data
regularly.
4
Data Definition

• What is data?
• Representation of facts or concepts in a manner
  suitable for communication, interpretation, or
  processing by humans or by automatic means
• Something used as a basis for calculating or
  measuring (Websters Dictionary)

5
Database Definition

• What is a database?
• A database is a collection of data organized in a
  manner that allows access, retrieval, and use of
  that data.
• Research subject data stacked in paper forms
  could be considered a database.
• Databases vary from the very simple to the very
  complex

6
Classic Database Example
Classic Database Example
File Cabinet
Drawer Folder / File Piece of paper Piece of
Information
Database
Table
Record
Field
7
Excel Example
8
Excel as a Database

• An electronic spreadsheet can be considered a
  database.
• Most spreadsheets are not

Relational
9
Relational Database
10
Relational Database
Lab Table keySubjectID/Lab
Visit Table keySubjectID/Visit
More efficient compared to one spreadsheet!
11
Importance of Relationships

• Subject Table each subject has one entry
• Visit Table - entry for each visit
• LabResults Table entry for each lab test
• Meds Table- entry for each medication

12
Table Data
Table structures presented as Flat files
13
Access Form
14
Relational Database vs Spreadsheet

• Unlike a spreadsheet a relational database can
  help insure that
• visit data not collected without subjects basic
  (or root) data being present.
• duplicate data not entered for the same subject.
• data not redundantly entered.

15
Advantages of Relational Databases

• You define the relationships among tables.
• Each piece of information is stored in one place.
• Reduces error since changes to information occur
  in only one place.
• Easy to revise the structure of the data base.

16
Database Management System (DBMS) Software

• Excel (?)
• Small Database Software
• Microsoft Access, FoxPro
• Midsize Database Software
• Microsoft SQL Server, mySQL, PostgreSQL
• Large (user base/data set) Software
• Oracle, DB2, Sybase

17
Data Design

• Data Dictionary (Code Book, Metadata)
• What is the purpose of your protocol
• Can you explain your hypothesis in numerical
  terms
• List of fields/data points to collect
• Subject Name, demographic information
• Diagnosis, medication, lab results
• Relationship of data collected

18
Data Collection - Design

• Collection forms
• Charts / nursing flow sheets
• Visit notes
• Questionnaires surveys
• Lab results
• Who will collect the data?
• You
• Administrative assistant
• Research assistant
• More than one person

19
Data Management - Design

• Who will help you analyze the data?
• Statistics important for proving or disproving a
  hypothesis
• How will your data relate to other research?
• Other researchers in the field
• Your previous and future data sets

20
Data Management - Design

• What statistics will you provide?
• Will the data management system interface with
  other software?

21
Data Structure Considerations

• Organize data with rows as records, columns as
  fields
• Use short, meaningful field names with no spaces
  and no special characters (_at_!)
• Store a 4 digit year, not 2
• Determine data type
• Will I want to derive averages, sums, StdDev?
  (One, 1, 1)
• Contemplate subject or sample ID numbering
  scheme.
• Store identifying data in a different place but
  linked. (HIPAA)
• Make users pick from a list when possible for
  more uniform data entry
• DO NOT put first and last name in same field!
  Separate!

22
Fix Me!
23
What changed?
24
Record Key

• Primary Key
• Uniquely identifies each record in table
• Could be auto generated by DBMS

25
Data Management - Develop

• Now What???

26
Data Management - Implement

• Time to test your protocol in action with a few
  subjects
• Have you thought of everything that needs to be
  collected?
• Can the data be managed any better?
• Setup database (prototype)

27
Data Management Manage

• Management of the actual data
• Where will you store the data?
• How will the data be backed up?
• Who has access?

28
Data Management - Manage

• Size of your data set, in terms of
• Number of subjects
• Number of data points
• Disk Space
• Timeframe of storage
• Interfacing scope
• Number of data entry personnel
• Number of staff accessing subject records
• Number of researchers accessing data
• Accessing data from multiple locations

29
Database Security

• Who needs access to data
• Network access security
• Server security
• Database password - same rights
• User ID and password - individual rights
• HIPAA Considerations

30
Data Management - Retrieve

• What good is your data if you cant use it?
• Databases are designed to allow for information
  storage and retrieval
• So are file cabinets!

31
Database Terms

• Tables - where information is stored, consists of
  rows (records) and columns (fields)
• Forms - data are entered into forms to make data
  entry easier and more accurate
• Queries - a question to the database to locate
  specific information
• Reports - summarizes and displays information
• Modules - program instructions

32
Backup

• Storage / Backup Security
• GCRC offers
• Redundant hard drive system
• Tape BackUp
• UAMS Server Support Team

33
What can the GCRC do for you?

• Database Design Consultation
• Database Storage on Server
• Backup ?
• Data Translation Assistance
• Questions?