Section outline

  • Collapse all Expand all

  • Thursday, July 22, 2021

    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 assignments require an RAE (formerly 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 RAE (MyResearch) and REDCap account.

    Faculty:  Michael Kohn

    • Lecture Slides:

    • DBMSLecture1_2021.ppt File

      The first segment is the course introduction by Josh Senyak, and the second segment is an introduction to the Infant Jaundice Study and to data management in Excel by Michael Kohn.  We recommend that you re-watch these videos at 1.6x or 2x playback speed.

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

    • Lecture 1 Segment 1 -- Introduction to the Epi 218 (20:11) Josh Senyak Media Resource
      Not available unless: You belong to Registered Students Only
    • Lecture 1 Segment 2 -- Infant Jaundice Study, Data Management in Excel (28:23) Michael Kohn Media Resource
      Not available unless: You belong to Registered Students Only

    • Required Reading:

      Chapter 19 -- "Data Management" in Designing Clinical Research (Browner et al), 5th Edition

    • Chapter 19 REVISED FINAL 2021.0507 for posting File

    • Assignment 0 to be completed prior to lab for Assignment 1 on 7/22/2021:

      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).

      Do not confuse this with Assignment 1, which is due on 7/29/2021.

    • Assignment Due Date: Complete Assignment 1 Quiz and upload your Assignment 1 Excel xlsx file, LASTNAME_Epi218ExcelLab1.xlsx, below by 7/29/21 at 11:59pm.

    • Assignment 1 Instructions. Assignment due 7/29/2021. File
    • Assignment 1 Quiz
      Not available unless: You belong to a group in Registered Students Only
    • Assignment 1 Upload

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

    • Datasets:

    • Epi218ExcelLab1Data.xlsx File

  • Thursday, July 22, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

    Computer Lab: Excel: Good, Bad, and Ugly

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak
     
    Location:      MH-1400 and online

    The lab sessions do not include any formal didactics or new material.

    The course material is covered in the lecture videos and the documents posted to the syllabus.  However, most students like to work through the assignments in the lab sessions so they can get help from the TAs and fellow students.  

    The course assignments have complete, detailed instructions.  They have been tested and debugged over the years, but we do make changes.  They require doing an online quiz and/or uploading a file to this CLE syllabus site.

    The lab sessions are completely optional.   Although it's fine to attend both, we envisioned people attending one or the other, either the 8:30 am or the 6 pm lab.  Only the 8:30 am lab will be available in-person.

    Again, students can complete the assignments without ever attending a lab session.

    In addition to completing the 6 assignments on time, you will turn in a final project consisting of a data collection system that you have developed yourself and a data collection and management plan.

  • Thursday, July 29, 2021

    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

    • Lecture Slides:

    • REDCap: Browser-based data collection File

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

    • Lecture 2 Video (58:28) Media Resource
      Not available unless: You belong to a group in Registered Students Only

    • Important note: Be sure to watch this lecture recording before beginning Assignment 2.

      Warning: The Final Project is due 9/9/21. The due date given in the pre-recorded lecture is not correct.

      Other notes:

      The lecture slides have been updated. Use the current PowerPoint slides in the link just above.

      Time mark 39:40: REDCap's "longitudinal" feature can be useful if you know exactly how many events to expect in your study. If different subjects will have different numbers of events, repeating instruments or repeating events may be a better choice.

      If you're curious about a much more full-featured version of REDCap (e.g. full clinical trials), have a look at REDCap Cloud.


    • Required Reading:

    • Coding and Naming Conventions File

    • Optional Reading:

    • Using the REDCap Shared Library of forms File

    • Assignment:

    • Assignment 2 Instructions File
    • Assignment 2 Data Collection Forms File
    • Assignment 2 -- REDCap. This is a placeholder for grading only. Please share your REDCAP project with your assigned TA, as described in the instructions.
    • TA Assignments File

    • Assignment Due Date:  Make sure that you have shared your REDCap project with your TA by 8/5/21 at 11:59PM.

  • Thursday, July 29, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

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

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak, Pallavi Chaudhary, Matthew Durstenfeld, Hannah Hoban, Sujata Mathur, Josephine Urbina

     
    Location:      MH1400 and online

  • Thursday, August 5, 2021

    REDCap Reporting and data exchange

    More on REDCap: Reporting, exporting, and exchanging data with other systems

    Faculty:  Josh Senyak

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

    • REQUIRED: Watch Lecture 3 Recording: REDCap Reporting and data exchange (46:50) Media Resource
      Not available unless: You belong to a group in Registered Students Only
    • Lecture 3 Outline File
    • Lecture 3 Addendum -- REDCap Data Entry Audits (3:11) Media Resource
      Not available unless: You belong to Registered Students Only
    • [OPTIONAL: The 2019 version of Lecture 3. Contains additional information on interrater reliability] URL

      This is a different version of Lecture 3, given in August 2019.  We cannot edit this file.  If we could, we would cut evrything before 18:40 and everything after 50:00.  There is information that you might find useful between 18:40 and 50:00.

      Not available unless: You belong to Registered Students Only

    • Assignment:

    • Assignment 3 Instructions File
    • For use in Assignment 3: Assignment3IJS_DataDictionary.csv File
    • Dataset for Assignment 3: Assignment3IJS_DATA.csv File
    • Assignment 3 Quiz

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

    • Assignment Due Date: Make sure to complete the online CLE quiz prior to  8/12/21 at 11:59 PM.

  • Thursday, August 5, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

    Computer Lab: REDCap -- Monitoring, Reporting, Exporting

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak, Pallavi Chaudhary, Matthew Durstenfeld, Hannah Hoban, Sujata Mathur, Josephine Urbina
     
    Location:      Online only

  • Thursday, August 12, 2021

    The Relational Model

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

    Faculty:  Michael Kohn

    • Lecture Slides:

    • Lecture 4 -- Relational Databases and Normalization File

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

    • Epi 218 2021 Lecture 4 Segment 1 -- Intro / Data Dictionaries and Data Types (15:44) Media Resource
      Not available unless: You belong to Registered Students Only
    • Epi 218 2021 Lecture 4 Segment 2 -- Normalization, One-to-Many Relationships, Primary/Foreign Keys (15:41) Media Resource
      Not available unless: You belong to Registered Students Only
    • Epi 218 2021 Lecture 4 Segment 3 -- Exporting to Stata (8:20) Media Resource
      Not available unless: You belong to Registered Students Only

    • Required Reading:

    • Assignment:

    • Assignment 4 Instructions File
    • Assignment 4 Upload

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

      You must be logged in to use the link.

    • Assignment Due Date: 8/19/21 at 11:59PM

  • Thursday, August 12, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

    Computer Lab: First Access/SQL Lab

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak, Pallavi Chaudhary, Matthew Durstenfeld, Hannah Hoban, Sujata Mathur, Josephine Urbina
     
    Location:      Online only

  • Thursday, August 19, 2021

    More on normalized relational databases

    Faculty:  Michael Kohn

    • Lecture Slides:

    • Lecture 5 File

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

    • Lecture 5 Segment 1 -- Front End vs Back End, Splitting (22:21) Media Resource
      Not available unless: You belong to Registered Students Only
    • Lecture 5 Segment 2 -- Advice About Final Project (10:00) Media Resource
      Not available unless: You belong to Registered Students Only
    • Lecture 5 Segment 3 -- Relational Model, Normalization (10:32) Media Resource
      Not available unless: You belong to Registered Students Only

    • Resources:

    • Assignment:

    • Assignment 5 Instructions File
    • InfantJaundiceStudyLab5.accdb File
    • Baby2012-13.xlsx File
    • Assignment 5 Quiz

    • Assignment Due Date:  By 11:59pm on 8/26/21.

    • Datasets:

  • Thursday, August 19, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

    Computer Lab: Second Access SQL Lab, Filtering by Form, Queries, and Reports

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak, Pallavi Chaudhary, Matthew Durstenfeld, Hannah Hoban, Sujata Mathur, Josephine Urbina
     
    Location:      Online only

  • Thursday, August 26, 2021

    Wrap-Up of Relational Database Concepts ; Data Management Plan

    Faculty:  Josh Senyak

    • Lecture Slides:

    • Lecture 6 - Wrap-up of Relational Database Concepts File
    • Database management plan File

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

    • Lecture 6 - Part A: Wrap-up of Relational Database Concepts Media Resource
      Not available unless: You belong to Registered Students Only
    • Lecture 6 - Part B: Final Project: Database Management Plan Media Resource
      Not available unless: You belong to Registered Students Only

    • Optional Reading:

    • Newman et al. NEJM. Hyperbili Outcomes File

    • Assignment:

    • Assignment 6 Instructions File
    • IJSDatabaseComplete.accdb File
    • Assignment 6 Quiz

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

    • Optional Assignment 7 Using R:

    • Assignment 7 Instructions (Optional, Extra Credit, Using R) File

      In this optional, extra credit assignment you will run some simple queries and analysis using R, a powerful and flexible statistical analysis package. For the sake of familiarity, we will repeat some of the same analysis that we did in Assignment 6. Have your Assignment 6 answers on hand for comparison.  This assignment includes 2 quiz questions.  The quiz is worth 5 extra credit points (25% of a regular assignment, extra credit).


    • Assignment 7 Quiz (Optional Extra Credit Using R)

      Assignment 7 repeats many of the tasks in Assignment 6 using R instead of Access and Stata.  If you do it, you will learn a bit of R and see that SQL statements can be run from within R.  The Assignment 7 quiz is completely optional and worth up to 5 extra crdit points.

  • Thursday, August 26, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

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

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak, Pallavi Chaudhary, Matthew Durstenfeld, Hannah Hoban, Sujata Mathur, Josephine Urbina
     
    Location:      MH-1400 and online


  • Thursday, September 2, 2021

    Research Database Demonstrations

    Faculty:  Michael Kohn, Josh Senyak and Elizabeth Black

    • Lecture Slides:

    • FLiPCUP -- PPT File

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

    • Final Project Demo 1 (13:22)-- Michael Kohn, FLiPCUP Media Resource
      Not available unless: You belong to Registered Students Only

    • Required Reading:

    • REDWrap 1.11 conversion utility File

      This utility converts REDCap data exports to normalized Access data tables. (This utility is still in development; use caution and judgment in confirming that data is fully and correctly converted!)

      More information: http://www.quicksilverconsulting.com/redwrap/

    • Final Project:

    • Final Project Instructions with FAQs (Updated 9/1/2021 File

      Due 9/9/2021 by 11:59pm.

      See detailed requirements and FAQs in the Word document above.

    • Final Project (due 23:59 on 9/9/2021) 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.

    • Examples of Final Project:

    • FLiPCUP - XML file to create the REDCap project

      The FLiPCUP Study uses REDCap to collect data and an Access back-end database for storage, management, querying, reporting, and analysis of the data collected via REDCap. To review the FLiPCUP REDCap project, download the XML file here and use it to create a new REDCap project (see instructions, available below).

    • FLiPCUP Back-End Access Database File
    • Instructions: How to create the FLiPCUP REDCap project from XML file
    • FLiPCUP 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 A -- CT Syndesmosis Study File
    • Example Final Project Part B -- CT Syndesmosis Study File

  • Thursday, September 2, 2021; 8:30 AM - 10:00 AM (Additional Session at 6:00 PM to 7:30 PM)

    Computer Lab: Final project consultation

    Students have access to course faculty for questions on current or prior curriculum, assignments and software implementation. For the session from 8:30 AM to 10:00 AM, separate in-person (at Mission Hall) and online labs will be held simultaneously, and students may attend either format from week to week. The 6:00 PM to 7:30 PM session will be held online.

    Faculty:  Michael Kohn, Josh Senyak, Pallavi Chaudhary, Matthew Durstenfeld, Hannah Hoban, Sujata Mathur, Josephine Urbina
     
    Location:      Online only