EPI 218: Database Management Systems for Clinical Research (Summer 2017)
Section outline
-
-
for Clinical Research
EPI 218 Summer 2017 (1 units).
Course Director: Michael A. Kohn, MD, MPP.
Associate Professor, Department of Epidemiology and Biostatistics -
Due 9/14/2017 by 23:59.
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 several realistic test records.
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 databases must include at least one on-screen data collection form.
4. Access databases should be normalized. REDCap databases should use the "Longitudinal" feature. (Events must be defined and instruments designated.)
5. Extra credit will be awarded if your database includes a report, such as the "NIH Report" created in Lab 3.
6. Extra credit will also be awarded if you provide a budget with your data collection and management plan.
-
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.
-
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 one-page protocol that we are asking from you as Part B of your Final Project. The Access database is included in this zip file.
-
-
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.
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 -
Computer Lab: Excel: pivot tables
Creating an Excel pivot table to summarize the data.
Faculty: Michael Kohn, Josh Senyak, et al.
Location: Mission Hall 1400-
Upload LastName_Lab1_IJSExport.xlsx and LastName_Lab1Worksheet.doc by clicking on the "Lab 1" link above. The link won't work unless you are logged into the CLE syllabus site.
-
Lecture: Tables and relationships, normalization. On-screen forms.
Multi-table relational database and data normalization. Primary and foreign keys. One-to-Many, Many-to-Many, and One-to-One relationships. On-screen forms.
Faculty: Michael Kohn
Location: Mission Hall 1400
-
Computer Lab: Access: creating tables, relationships, and data entry forms
Creating a table in design or “data dictionary” view. Field names, types, and descriptions; validation rules, primary and foreign keys, lookup tables, and relationships between tables. Creating data entry forms/subforms.
Faculty: Michael Kohn, Josh Senyak, et al.
Location: Mission Hall 1400-
Upload LastNameLab2.accdb by clicking on the "Lab 2" link above. The link won't work unless you are logged into the CLE syllabus site.
-
Lecture: Queries
More on normalized relational databases. Views/Select Queries; Action Queries: Update, Append, Delete; On-screen forms.
Faculty: Michael Kohn
Location: Mission Hall 1400
-
Computer Lab: Filtering by form, queries, reports, and importing data
Filtering by form. Writing select queries and reports for monitoring study progress. Importing data and appending and updating records.
Faculty: Michael Kohn, Josh Senyak, et al.
Location: Mission Hall 1400-
Upload Lab 3 files by clicking on the "Lab 3" link above.
The link won't work unless you are logged in.
The files should include should include the following:
“LastnameCountsByRace1.rtf” made prior to importing the new 2012-13 records
“LastnameCountsByRace2.rtf” made after importing and the new records
Your completed Lab 3 worksheet.
-
This is a compressed (zip) file to improve download speeds. You should extract it prior to using it.
-
Lecture: Wrap-Up of Relational Database Concepts
Execution and Results of the JIFee Study
Faculty: Josh Senyak and Michael Kohn
Location: Mission Hall 1400
-
Computer Lab: Creating queries, calculating derived fields, and exporting to Stata
Faculty: Michael Kohn, Josh Senyak, et al
Location: Mission Hall 1400-
-
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.
-
Lecture: Web-based data entry forms: REDCap
Faculty: Jennifer Creasman
Location: Mission Hall 1400
-
Computer Lab: REDCap: basic functions, advantages and limitations
Faculty: Michael Kohn, Jennifer Creasman, et al (no Josh)
Location: Mission Hall 1400-
Please share your Lab 5 REDCap project with your section leader. You do not need to upload a file.
The deadline for sharing your project is 8/31 at 23:59.
-
Lecture: Platform Choice; Eight Questions to Address in Your Data Management Plan.
Faculty: Josh Senyak / Michael Kohn
Location: Mission Hall 1400
-
Computer Lab: REDCap: monitoring, reporting and exporting to Stata
Faculty: Michael Kohn, Josh Senyak, et al
Location: Mission Hall 1400-
Upload your Lab 6 worksheet file by clicking on the "Lab 6" link above.
The link won't work unless you are logged in.
The file should include your completed Lab 6 Worksheet, including your two Stata tables, 1) all included subjects by race and sex and 2) included Latino subjects by race and sex.
-
Lecture: Research Database Demonstrations
Faculty: Michael Kohn, Josh Senyak
Location: Mission Hall 1400
-
Computer Lab: Final project consultation
Faculty: Michael Kohn, Josh Senyak, et al.
Location: Mission Hall 1400-
The link for uploading your final project is near the top of this syllabus page.
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 several realistic test records.
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 databases must include at least one on-screen data collection form.
4. Access databases should be normalized. REDCap databases should use the "Longitudinal" feature. (Events must be defined and instruments designated.)
5. Extra credit will be awarded if your database includes a report, such as the "NIH Report" created in Lab 2.
6. Extra credit will also be awarded if you provide a budget with your data collection and management plan.
-
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 one-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 Date format: Tuesday, July 30, 2013; 1:15 PM - 2:45 PM
<Activity> < optional: insert title>
Lecture narrative description hereActivity options are: Lecture, Small Group Discussion Section, Computer Lab, Journal Club, and Office Hours. Lectures will always have titles but other activities typically won’t. For online activities, add (Times and dates for online sessions may differ)
Faculty: (examples: Mark Pletcher; Mark Pletcher and Jeff Martin)
Location: (e.g., China Basin 6702; or, for small groups: Martin CB 6702, Cheng CB 6601, Jones CB 5740, and Smith CB 5601 ) -
Example Date format: Tuesday, July 30, 2013; 1:15 PM - 2:45 PM
<Activity> < optional: insert title>
Lecture narrative description hereActivity options are: Lecture, Small Group Discussion Section, Computer Lab, Journal Club, and Office Hours. Lectures will always have titles but other activities typically won’t. For online activities, add (Times and dates for online sessions may differ)
Faculty: (examples: Mark Pletcher; Mark Pletcher and Jeff Martin)
Location: (e.g., China Basin 6702; or, for small groups: Martin CB 6702, Cheng CB 6601, Jones CB 5740, and Smith CB 5601 ) -
Example Date format: Tuesday, July 30, 2013; 1:15 PM - 2:45 PM
<Activity> < optional: insert title>
Lecture narrative description hereActivity options are: Lecture, Small Group Discussion Section, Computer Lab, Journal Club, and Office Hours. Lectures will always have titles but other activities typically won’t. For online activities, add (Times and dates for online sessions may differ)
Faculty: (examples: Mark Pletcher; Mark Pletcher and Jeff Martin)
Location: (e.g., China Basin 6702; or, for small groups: Martin CB 6702, Cheng CB 6601, Jones CB 5740, and Smith CB 5601 )