Difference between revisions of "Week 8"

From LMU BioDB 2024
Jump to navigation Jump to search
(→‎Homework Partners: added homework partners (same as week 2))
(→‎Electronic Lab Notebook and Questions to Answer: updated column names to reflect updated FDA database)
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
{{Under Construction}}
 
−
 
 
'''This journal entry is due on Thursday, March 14, at 12:01am Pacific time.'''
 
'''This journal entry is due on Thursday, March 14, at 12:01am Pacific time.'''
  
Line 60: Line 58:
 
** Click the "Extract" button.
 
** Click the "Extract" button.
  
−
=== Electronic Lab Notebook ===
+
=== The FDA Drug Database ===
 +
 
 +
The United States Food and Drug Administration (FDA) provides, as a matter of public record, the full data set for its approved drugs at this website: http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm
 +
 
 +
The site includes both a link to the downloadable files (compressed in .zip format) and the schema (“entity relationship diagram”) for those files.
 +
 
 +
Using these files, what you have learned in the LinkedIn Learning tutorial, and additional information found in this wiki and on the aforementioned FDA website, do the following:
 +
 
 +
# Download and decompress the files.
 +
# Import the data files into a Microsoft Access database you will create.
 +
# Configure the fields in your tables to match the schema provided by the FDA (keys, datatypes).
 +
# Set the relationships between your tables to match the schema provided by the FDA.
 +
# Answer the questions below.
 +
 
 +
=== Electronic Lab Notebook and Questions to Answer ===
 +
 
 +
Complete your electronic notebook  on your individual journal page. Your notebook entry should contain:
 +
* The '''purpose''':  what was the purpose of your investigations?
 +
* Your '''methods''': what did you actually do?  Give a step by step account for Tasks 1-4 listed above.
 +
** There should be enough detail provided so that you or another person could re-do it based solely on your notebook.
 +
** You may copy protocol instructions on your page and modify them as to what '''''you''''' actually did, as long as you provide appropriate attribution in your acknowledgments section.
 +
** Take advantage of the electronic nature of the notebook by providing screenshots, links to web pages, links to data, etc.
 +
* Your '''results''': the answers to the questions below, plus any other results you gathered.
 +
** Usually it makes sense to embed your answers to the questions/results in the "methods" in the order in which you obtained them.
 +
* A '''scientific conclusion''':  what was your main finding for today's project?  Did you fulfill the purpose?  Why or why not?
 +
 
 +
'''[https://support.office.com/en-us/article/access-sql-basic-concepts-vocabulary-and-syntax-444d0303-cde1-424e-9a74-e8dc3e460671 Access SQL: basic concepts, vocabulary, and syntax]'''
 +
 
 +
For your electronic lab notebook this week, do the following:
 +
# Upload your zipped Microsoft Access FDA database to the wiki and link to it on your individual journal page.
 +
# Write up the methods you used to create the database, load the tables, and write your first query.
 +
 
 +
''For the questions below, supply (a) the answer to the question and (b) the query that you used to answer the question.''
 +
# What are the names of the drug products that are administered in the ''form'' <code>INJECTABLE;INTRAVENOUS, SUBCUTANEOUS</code>?
 +
# What are the names of the drug products whose active ingredient (''activeingredient'') is <code>ATROPINE</code>?
 +
# In what ''form''s and ''strength''s can the drug product named <code>BENADRYL</code> be administered?
 +
# Which drug products have a name ''ending'' in <code>ESTROL</code>?
 +
# Produce a table listing all of the known values for the ''therapeutic_potential'' column in the ''application'' table and how many ''application'' records there are of each. (''Side note:'' The ''therapeutic_potential'' codes are explained in the ''ReviewClass_Lookup.txt'' file, in case you’re interested.)
 +
# Produce a table listing all of the known values for the ''chemical_type'' column in the ''application'' table and how many ''application'' records there are of each. (''Side note:'' The ''chemical_type'' codes are explained in the ''ChemTypeLookup.txt'' file, in case you’re interested.)
 +
# What are the names of the drug products that are sponsored (''sponsorname'' column) by <code>MERCK</code>?
 +
# Which ''sponsorname'' companies have the text <code>LABS</code> in their names and have products whose active ingredients (''activeingred'') include ''both'' <code>ASPIRIN</code> and <code>CAFFEINE</code>?
 +
# Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database ([https://www.accessdata.fda.gov/scripts/cder/daf/index.cfm Drugs@FDA?])
  
−
Because of the nature of the assignment this week, you will have four sections of the electronic notebook:
+
==== Data and Files ====
  
−
# The '''purpose''':  what is the purpose of this assignment?
+
* References to data and files should be made within the methods and results section of your notebook.
−
# A ''brief'' '''methods/results''' section that summarizes what you did for the tutorial.  Upload your completion certificate here.
+
* In addition to these inline links, create a "Data and Files" section of your notebook to make a list of the files generated in this exercise.  In particular, you will need to zip and upload a compressed version of your MS Access FDA drug database.  Make sure that your filename contains your last name or username to distinguish it from databases created by other students in the class.
−
# A '''conclusion''' section that mirrors your purpose.
 
−
# The '''Acknowledgments''' section.
 
−
#* You '''''must''''' acknowledge your homework partner or team members with whom you worked, giving details of the nature of the collaboration.  You should include when and how you met and what content you worked on together.  An appropriate statement could be (but is not limited to) the following:
 
−
#** I worked with my homework partner (give name and link name to their user page) in class.  We met face-to-face one time outside of class.  We texted/e-mailed/chatted online three times.  We worked on the <details> portion of the assignment together.
 
−
#* Acknowledge anyone else you worked with who was not your assigned partner.  This could be Dr. Dahlquist, other students in the class, or even other students or faculty outside of the class.
 
−
#* If you copied <code>wiki syntax</code> or a particular ''style'' from another wiki page, acknowledge that here.  Provide the user name of the original page, if possible, and provide a link to the page from which you copied the syntax or style. 
 
−
#* If you need to reference '''''content''''', include the formal citation in your References section (see below).
 
−
#* You '''must''' also include this statement:  
 
−
#** '''''"Except for what is noted above, this individual journal entry was completed by me and not copied from another source."'''''
 
−
#* Sign your Acknowledgments section with your wiki signature.
 
−
# The '''References''' section. In this section, you need to provide properly formatted citations to any '''''content''''' that was not entirely of your own devising.   
 
−
#* Do not include citations/references to sources that you did not use.
 
−
#* You should include a reference to this week's assignment page.
 
−
#* The references should be formatted according to the [https://owl.purdue.edu/owl/research_and_citation/using_research/documents/20180719CitationChart.pdf APA guidelines].
 
−
#* For more detailed guidelines, please see the document [[Media:BIOL367_Spring2024_GuidelinesforLiteratureCitations.pdf | Guidelines for Literature Citations in a Scientific Paper]] that you were given on the first day of class.
 
−
#* Cite the appropriate weekly Assignment page.  For example, the citation for the [[Week 1]] Assignment page is: LMU BioDB 2024. (2024). Week 1. Retrieved August 27, 2019, from https://xmlpipedb.cs.lmu.edu/biodb/spring2024/index.php/Week_1
 
−
#* Even if you have nothing else to cite, the References section must be present and include the citation to the appropriate weekly assignment page.
 
  
 
== Shared Journal Assignment ==
 
== Shared Journal Assignment ==
Line 94: Line 116:
 
=== Read ===
 
=== Read ===
  
−
<!--
 
 
* Return to [http://www.bloomberg.com/graphics/2015-paul-ford-what-is-code/ “What is Code?”], now looking at these sections within Section 5, “The Time You Attended the E-mail Address Validation Meeting:”
 
* Return to [http://www.bloomberg.com/graphics/2015-paul-ford-what-is-code/ “What is Code?”], now looking at these sections within Section 5, “The Time You Attended the E-mail Address Validation Meeting:”
 
:5.1 What is the Relationship Between Code and Data?
 
:5.1 What is the Relationship Between Code and Data?
 
:5.2 Where Does Data Live?
 
:5.2 Where Does Data Live?
 
:5.3 The Language of White Collars
 
:5.3 The Language of White Collars
−
-->
 
  
 
=== Reflect ===
 
=== Reflect ===
  
−
<!--
 
 
# Based on what you have seen of the FDA database, do you feel that you have a better understanding of how the data mentioned in section 5.1 of ''What is Code?''—Spotify music, Fitbit exercise tracking, Twitter tweets, IRS tax returns, etc.—might look when stored on a computer?
 
# Based on what you have seen of the FDA database, do you feel that you have a better understanding of how the data mentioned in section 5.1 of ''What is Code?''—Spotify music, Fitbit exercise tracking, Twitter tweets, IRS tax returns, etc.—might look when stored on a computer?
−
-->
+
# Which part of this assignment made the most sense to you?
 +
# Which part of this assignment do you feel least confident about? 
 +
# What would you like to know more about?
  
 
[[Category:Assignment]]
 
[[Category:Assignment]]

Latest revision as of 14:51, 13 March 2024

This journal entry is due on Thursday, March 14, at 12:01am Pacific time.

Overview

The purpose of this assignment is:

  • To experience a real-world example of how raw data sets can be distributed and formatted
  • To get some practice with database queries using the graphical interface of MS Access

Individual Journal Assignment

  • Store this journal entry as "username Week 8" (i.e., this is the text to place between the square brackets when you link to this page).
  • Invoke your personal template that you created for the Week 1 Assignment on your individual journal entry page. Your template should provide the following set of navigation links:
    • Link to your user page.
    • Links to the weekly Assignment pages.
    • Links to your weekly Individual Journal entry pages.
    • Links to the weekly Class Journal pages.
    • The category "Journal Entry".

Homework Partners

You will work in groups of two for this week's assignment. Please sit next to your partner in class. You will be expected to consult with your partner, in order to complete the assignment. However, unless otherwise stated, each partner must submit his or her own work as the individual journal entry (direct copies of each other's work is not allowed). Homework partners for this week are:

  • Hailey & Andrew
  • Katie & Charlotte
  • Natalija & Dean

Preliminary Tasks

Turning on File Extensions in Windows 10

  • The Windows 10 operating systems defaults to hiding file extensions. To turn them back on, do the following:
    Folder Options window
    1. Go to the Search menu (circle icon) "File Explorer Options" in the search field. Select the result.
    2. When the File Explorer Options window appears, click on the View tab.
    3. Uncheck the box for "Hide extensions for known file types".
    4. Click the OK button.
  • The computers in Seaver 120 are are set to erase all custom user settings and restore the defaults once they have been restarted, so if you have done this previously, you might have to do it again.

Setting the Download Options for FireFox and Chrome

  • For Mozilla FireFox (122.0.1 or higher)
    • For this class we want to set the default download folder to "Desktop" and have Firefox ask each time a file is downloaded
      • Go to the Settings menu, General.
      • First, under the Files and Applications > Downloads section, next to "Save files to" click the "Browse" button, select the Desktop, and click the OK button.
      • Second, check the box next to "Always ask you where to save files"
  • For Google Chrome (Version 121.0.6167.162 or higher)
    • For this class we want to set the default download folder to "Desktop" and have Chrome ask each time a file is downloaded
    • Options set to default downloads to Desktop and to ask each time a file is downloaded
      • Go to Settings page, click on the "Downloads" menu item on the left hand side.
      • First, under the "Location" section, click the "Change" button, select the Desktop, and click the OK button.
      • Second, move the slider "Ask where to save each file before downloading" to the right to turn it on.

Decompressing Files in Windows

  • Files with the .zip extension are compressed files that need to be decompressed before using them.
  • Right-click on the file icon to open the context menu.
    • Select "Extract all".
    • Click the "Extract" button.

The FDA Drug Database

The United States Food and Drug Administration (FDA) provides, as a matter of public record, the full data set for its approved drugs at this website: http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm

The site includes both a link to the downloadable files (compressed in .zip format) and the schema (“entity relationship diagram”) for those files.

Using these files, what you have learned in the LinkedIn Learning tutorial, and additional information found in this wiki and on the aforementioned FDA website, do the following:

  1. Download and decompress the files.
  2. Import the data files into a Microsoft Access database you will create.
  3. Configure the fields in your tables to match the schema provided by the FDA (keys, datatypes).
  4. Set the relationships between your tables to match the schema provided by the FDA.
  5. Answer the questions below.

Electronic Lab Notebook and Questions to Answer

Complete your electronic notebook on your individual journal page. Your notebook entry should contain:

  • The purpose: what was the purpose of your investigations?
  • Your methods: what did you actually do? Give a step by step account for Tasks 1-4 listed above.
    • There should be enough detail provided so that you or another person could re-do it based solely on your notebook.
    • You may copy protocol instructions on your page and modify them as to what you actually did, as long as you provide appropriate attribution in your acknowledgments section.
    • Take advantage of the electronic nature of the notebook by providing screenshots, links to web pages, links to data, etc.
  • Your results: the answers to the questions below, plus any other results you gathered.
    • Usually it makes sense to embed your answers to the questions/results in the "methods" in the order in which you obtained them.
  • A scientific conclusion: what was your main finding for today's project? Did you fulfill the purpose? Why or why not?

Access SQL: basic concepts, vocabulary, and syntax

For your electronic lab notebook this week, do the following:

  1. Upload your zipped Microsoft Access FDA database to the wiki and link to it on your individual journal page.
  2. Write up the methods you used to create the database, load the tables, and write your first query.

For the questions below, supply (a) the answer to the question and (b) the query that you used to answer the question.

  1. What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?
  2. What are the names of the drug products whose active ingredient (activeingredient) is ATROPINE?
  3. In what forms and strengths can the drug product named BENADRYL be administered?
  4. Which drug products have a name ending in ESTROL?
  5. Produce a table listing all of the known values for the therapeutic_potential column in the application table and how many application records there are of each. (Side note: The therapeutic_potential codes are explained in the ReviewClass_Lookup.txt file, in case you’re interested.)
  6. Produce a table listing all of the known values for the chemical_type column in the application table and how many application records there are of each. (Side note: The chemical_type codes are explained in the ChemTypeLookup.txt file, in case you’re interested.)
  7. What are the names of the drug products that are sponsored (sponsorname column) by MERCK?
  8. Which sponsorname companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE?
  9. Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database (Drugs@FDA?)

Data and Files

  • References to data and files should be made within the methods and results section of your notebook.
  • In addition to these inline links, create a "Data and Files" section of your notebook to make a list of the files generated in this exercise. In particular, you will need to zip and upload a compressed version of your MS Access FDA drug database. Make sure that your filename contains your last name or username to distinguish it from databases created by other students in the class.

Shared Journal Assignment

  • Store your journal entry in the shared Class Journal Week 8 page. If this page does not exist yet, go ahead and create it (congratulations on getting in first :) )
  • Link to the Class Journal page from your user page (should be part of your template).
  • Link back from the Class Journal page to your user page.
  • Sign your portion of the journal with the standard wiki signature shortcut (~~~~).
  • Add the "Journal Entry" and "Shared" categories to the end of the wiki page (if someone has not already done so).

Read

  • Return to “What is Code?”, now looking at these sections within Section 5, “The Time You Attended the E-mail Address Validation Meeting:”
5.1 What is the Relationship Between Code and Data?
5.2 Where Does Data Live?
5.3 The Language of White Collars

Reflect

  1. Based on what you have seen of the FDA database, do you feel that you have a better understanding of how the data mentioned in section 5.1 of What is Code?—Spotify music, Fitbit exercise tracking, Twitter tweets, IRS tax returns, etc.—might look when stored on a computer?
  2. Which part of this assignment made the most sense to you?
  3. Which part of this assignment do you feel least confident about?
  4. What would you like to know more about?
Retrieved from "https://xmlpipedb.lmucs.org/biodb/spring2024/index.php?title=Week_8&oldid=2032"