Section outline

  • Collapse all Expand all


    • Data Collection and Management Systems 

      for Clinical Research


      EPI 218 Summer 2018 (1 units).
      Course Director: Michael A. Kohn, MD, MPP.
      Associate Professor, Department of Epidemiology and Biostatistics
    • News forum
    • Discussion Forum
    • Introductory Material
    • Welcome to Database Management for Clinical Research File
    • MyResearch - Mac Instructions for Installation.pdf File
    • MyResearch Windows 10 Installation Instructions URL
    • Epi 218 Naming/Coding Conventions File
      These guidelines for naming fields and coding responses are mostly arbitrary, but if we all follow them, it will be easier to share our data with one another.
    • Final Project
    • Final Project Instructions with FAQs (Updated 9/10/2018) File

      Due 9/20/2018 by 23:59.

      See detailed requirements and FAQs in the Word document above.

      Part A: Data Collection System

      1. We prefer that your database be populated with real data (de-identified if appropriate), but at least make sure to test your data collection system by entering at least 5 realistic test records in each instrument

      2. Make sure your field types are appropriate (e.g. if you are collecting a date, you should be using a date field type).

      3. All data collection systems must include at least two on-screen data collection forms.

      4. Access databases should have at least one one-to-many relationship and a form/subform as in Lab 4.  REDCap projects should use at least two instruments having a one-to-many relationship, one of which is a repeatable event or repeatable instrument as in Labs 2 and 3. ("Longitudinal data collection" is NOT required.)

      Part B: Data collection and management plan

      Make sure to outline how you will meet reporting requirements such as creating an NIH Report (see Lab 5).

      Extra credit will also be awarded if you provide a budget with your data collection and management plan.


    • Final Project (due 23:59 on 9/20/2018) Assignment

      Upload Parts A and B of your final project by clicking on "Final Project" above.

      You need to be logged on to the CLE website in order to use this feature.

      If you are using REDCap for your final project, instead of uploading Part A, make your section leader a user with administrative privileges.

    • Example Final Projects
    • HPV Cytology Back-End Access Database File

      The HPV Cytology Final Project used a REDCap data collection system.  Your section leader will share the HPV Cytology REDCap project with you.  This Access database is an example of a back-end database that you would need to enable storage, management, querying, reporting, and analysis of the data collected via REDCap.  If your final project uses a REDCap data collection system, you do not need to turn in a back-end database to complete Part A; just share your REDCap project with your section leader.  In your Part B write-up, you should describe what you will use to store, manage, query, report on, and analyze your data.  REDCap does not have these capabilities.  A relational database like Access is best, but a set of Stata .dta files is acceptable.  While an Excel workbook might have been a reasonable choice before you took this course, you should have learned that an Excel workbook is not an acceptable back-end repository for research data.

    • HPV Cytology Final Project Part B File
    • Long Infant Jaundice Study Protocol and Access Database File

      This long version of the Infant Jaundice Study Protocol  represents a modification of the original R01  proposal and is not an example of the  kind of two-page protocol that we are  asking from you as Part B  of your Final Project.    The  Access database is included in this zip file.

    • Example Final Project Part B -- VA ICD Database File
    • Example Final Project Part A -- CT Syndesmosis Study File
    • Example Final Project Part B -- CT Syndesmosis Study File

  • Thursday, August 2, 2018; 8:30 AM - 9:20 AM

    Lecture: Data Tables--Rows=Records, Columns=Fields; Data Dictionaries=Metadata; Excel--a great tool, but not for data entry, storage, and management

    Going from data collection forms to data tables in which rows correspond to records/entities and columns correspond to fields/attributes. The concept of a data dictionary or "meta-data".  What is great about Excel and why you can't use it for data entry, storage, and management.  Data table structure -- Wide ("short-fat") vs. Long ("tall-skinny").

    IMPORTANT: The course labs require a MyResearch and REDCap account from UCSF Academic Research Systems (ARS).  We will submit the initial account request for all registered students in EPI 218. You should receive an email from ARS (or DocuSign) asking you to fill out an online attestation form in order to activate your MyResearch and REDCap account.  See the "Prospectus" memo below.  

    Faculty:  Michael Kohn

    Location:      Mission Hall 1400

    • Prospectus:

    • Session Slides:

    • EPI218Lecture1.ppt File

    • Session Audio/Video Recording (Access restricted to registered students):

    • Required Reading:

      Chapter 16 -- "Data Management" in Designing Clinical Research (Hulley et al), 4th Edition

    • Assignment:

      Work through the Chapter 16 ("Data Management") Exercises starting on page 301 of Designing Clinical Research (Hulley et al), 4th Edition.

      These are not to turn in (especially since the answers are in the book).

       

  • Thursday, August 2, 2018; 9:30 AM - 10:30 AM

    Computer Lab: Excel: Good, Bad, and Ugly

    Early warning: This lab is due by midnight next Thursday August 9, 2018.  Lab 2 is due the following Thursday, and Lab 3 is not due for 3 weeks, but Lab 3 may take more time than Labs 1 and 2.  Even if you are able to do Labs 1 and 2 on your own, Lab 3 may require help from a TA or someone else who has completed it successfully.  The best way to get this help is to attend the in-person lab sessions and start early to see if you have trouble.

    Limitations of using Excel for research data entry and for sorting and querying data.  Data table structure -- Wide ("short-fat") vs. Long ("tall-skinny").

    Faculty:  Michael Kohn, Josh Senyak, et al.
     
    Location:      Mission Hall 1400

    • Optional Reading:

    • Assignment:

      Complete Lab 1 on Excel pivot tables.

    • Lab 1 Instructions File
    • MyResearch - Mac Instructions File
    • MyResearch Windows 10 Installation URL

    • Assignment Due Date: Complete Lab 1 Quiz and upload your Lab 1 Excel xlsx file, LASTNAME_Epi218ExcelLab1.xlsx, below by 8/09/2018 at midnight.

    • Lab 1 Quiz
    • Lab 1 Upload Assignment

      Upload LastName_Epi218Lab1Data.xlsx by clicking on the "Lab 1" link above.  The link won't work unless you are logged into the CLE syllabus site.

    • Datasets:

    • Epi218ExcelLab1Data.xlsx File

  • Thursday, August 9, 2018; 8:30 AM - 9:20 AM

    Lecture: REDCap: Browser-based data collection

    REDCap features; advantages/limitations; the relational limits of REDCap; instruments, events and repeating events; other features. We may cover coding conventions as well.

    Faculty:  Josh Senyak

    Location:  Mission Hall 1400

  • Thursday, August 9, 2018; 9:30 AM - 10:30 AM

    Computer Lab: REDCap -- Creating a Web-Based Research Data Collection System

    Early warning: This lab (Lab 2) is due by midnight next Thursday, 8/16/2018.   Lab 3 is not due for 2 weeks, but you may require help from a TA or someone else who has completed it successfully.  The best way to get this help is to attend the in-person lab sessions and start early to see if you have trouble.

    The Lab 2 Instructions are in a Word doc posted below.   The lab covers the following:

    A.   Create a new REDCap project.

    B.    Create a data collection instrument for subject-specific data

    C.    Create a data collection instrument for exam-specific data

    D.   Define 4 events per subject and designate the Subject form for the first event (Birth), and the Exam form for the 3 the Exam events.

    E.    Use the forms to enter data for one subject (with 3 exams)

    F.    Piping

    G.   Create calculated fields.

    H.   Add new users to the REDCap project.

    Faculty:  Michael Kohn, Josh Senyak, et al.
    Location:      Mission Hall 1400

    • Assignment:

    • Lab 2 Instructions File
    • Lab 2 Data Collection Forms File

    • Assignment Due Date:  Make sure that you have shared your REDCap project with your TA by 8/16/2018 at midnight.

  • Thursday, August 16, 2018; 8:30 AM - 9:20 AM

    Lecture: Monitoring Data Collection, Checking for Outliers, Reporting, Exporting

    More on REDCap: Stats and Graphs, Reports, Exports.  Excel Pivot tables

    Faculty: Michael Kohn

    Location:  Mission Hall 1400

    • Session Audio/Video Recording (Access restricted to registered students):

    • Session Slides:

    • Lecture 3 File

  • Thursday, August 16, 2018; 9:30 AM - 10:30 AM

    Computer Lab: REDCap -- Monitoring, Reporting, Exporting

    Note: This lab is the longest and most challenging in the course. You may need assistance from a TA or someone else who has successfully completed it.  The best way to get this assistance is to attend the scheduled lab session on Thursday morning.

    The Lab 3 instructions are in a Word doc posted below.  The lab covers the following:

    A.   Create the REDCap project by importing 2 csv files containing the data dictionary (metadata) and create events.

    B.    Designate Instruments and Import Data

    C.   Record a subject’s outcome.

    D.   Find selected records.

    E.    Find an outlier.

    F.    Identify erroneous data.

    G.   Export data.

    H.   Compare Neuro Exam Results

    I.     Compare Mean IQs

    J.     (Optional) Analyze data in Stata

    Faculty:  Michael Kohn, Josh Senyak, et al.

    Location:      Mission Hall 1400

    • Required Reading:

    • Optional Reading:

    • Assignment:

    • Lab 3 Instructions (NOTE: Section I (eye) clarified on 8/17.) File

      See forum post for more about Section I (eye).

    • Assignment Due Date: Make sure to complete the online CLE quiz prior to y 8/23/2018 at midnight.

    • Lab 3 Quiz

      Unless otherwise noted, you will have 3 attempts to answer each question.

    • Datasets:

    • Lab3IJS_DataDictionary.csv File
    • Lab3IJS_DATA.csv File

  • Thursday, August 23, 2018; 8:30 AM - 9:20 AM

    Lecture: The Relational Model and SQL

    Primary Key; Foreign Key; One-to-Many relationships; Entity-Relationship Diagrams; Normalization; SQL. 

    Faculty: Michael Kohn

    Location: Mission Hall 1400

  • Thursday, August 23, 2018; 9:30 AM - 10:30 AM

    Computer Lab:  First Access/SQL Lab

    Creating Tables, Relationships, Primary Key, Foreign Key, and Data Entry Forms

    Faculty: Michael Kohn, Josh Senyak, et al

    Location: Mission Hall 1400

    • Assignment: Lab 4 on creating tables and relationships is due 8/30/2018.

    • Lab 4 Instructions File
    • Lab 4 Assignment

      Upload Lab 4 on the "Lab 4" link above.

      You must be logged in to use the link.

    • Datasets:

  • Thursday, August 30, 2018; 8:30 AM - 9:20 AM

    Lecture: More on normalized relational databases.  Select Queries; Action Queries: Update, Append, Delete. 

    Faculty: Michael Kohn

    Location: Mission Hall 1400

    • Session Slides:

    • Lecture 5 File
    • InfantJaundiceStudyForLecture5_NOT_for_Lab_5 File

    • Session Audio/Video Recording (Access restricted to registered students):

    • Resources:

  • Thursday, August 30, 2018; 9:30 AM - 10:30 AM

    Computer Lab:

    Second Access SQL Lab

    Filtering by Form,

    Queries, and Reports

    Faculty: Michael Kohn
    Location:     Mission Hall 1400

  • Thursday, September 6, 2018; 8:30 AM - 9:20 AM

    Lecture:

    Wrap-Up of Relational Database Concepts ; Data Management Plan

    Faculty: Josh Senyak

    Location:  Mission Hall 1400

  • Thursday, September 6, 2018; 9:30 AM - 10:30 AM

    Computer Lab:  

    Creating SQL Queries to Summarize Results and Export to Stata (or another statistical package)

    Faculty: Michael Kohn, Josh Senyak, et al

    Location:     Mission Hall 1400

    • Assignment:

    • Lab 6 Instructions File
    • InfantJaundiceDatabaseComplete.accdb File

      To shorten download times, we compressed this file into a "zip" archive.  Please extract the Access file "InfantJaundiceDatabaseComplete.accdb" from the zip archive before launching it.

    • Newman et al. NEJM. Hyperbili Outcomes File

    • Assignment Due Date:  Lab 6 is due at 23:59 on 9/13/2018.  You will need to complete the Lab 6 quiz.  See the lab instructions for details.

    • Lab 6 Quiz

    • Datasets:

  • Thursday, September 13, 2018; 8:30 AM - 9:20 AM

    Lecture: Research Database Demonstrations

    Faculty: Michael Kohn, Josh Senyak

    Location:  Mission Hall 1400

    • Session Slides:

    • HPV Cytology PPT File

    • Session Audio/Video Recording (Access restricted to registered students):

    • Required Reading:

  • Thursday, September 13, 2018; 9:30 AM - 10:30 AM

    Computer Lab: Final project consultation

    Faculty:  Michael Kohn, Josh Senyak, et al.
     
    Location:      Mission Hall 1400

    • Final Project Due Date: EPI 218 final project is due by midnight on 9/20/2018.

      Instructions for the Final Project, examples, and the submission link are at the top of this syllabus.