Difference between revisions of "Jnimmers Week 4"

From LMU BioDB 2019
Jump to navigation Jump to search
(Create page)
 
(Invoke template)
 
(19 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
=== LinkedIn Learning Tutorial for MS Access ===
+
==File Containing Microsoft Access FDA Database==
 +
*'''[[File:JnimmersFDAProducts.zip |FDA Database]]'''
 +
==Electronic Notebook==
 +
===Purpose===  
 +
The purpose of this investigation was to get a better understanding of how to operate the Microsoft Access Database by using data collected from the Federal Drug Agency to create my own database. Besides that, this assignment was meant to allow for a visualization of how complex databases, such as the FDA database, is formed and how relationships between tables can be formed to create a cohesive and understandable database to provide as much information as possible to those who access it. [[File:Jnimmers Certificate.png|thumb| Certificate of Completion from LinkedIn Learning ]]
 +
===Methods===
 +
'''Task 1'''
 +
* For this Task, I went to the  [[http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm | FDA Drug Information Website]] and downloaded the available drug data from that source. There is a link at the bottom of the page instructing you on how to download the .zip file.
 +
*From there, I found the files that contained relative data and extracted them from their .zip folder by finding a simple area to extract, right-clicking the folder, and selecting "Extract All" from the drop-down menu.
 +
'''Task 2'''
 +
*Once the Text documents had been downloaded, I went into Microsoft (MS) Access and, under the "External Data" tab, I selected the "Import Text File" option and found the files that needed to be imported from the FDA folder. Each file imported will create a new table. The list of these tables can be found on the left side of the screen in MS Access. Most formatting was done automatically by Access, however, make sure to check the box that says "First Row Contains Field Names", it will allow for the automatic formatting of your column names. This makes the inclusion of IDs easier because if you delete an ID row, it forces you to start the process of importing over so that your first row doesn't start as ID:2. [[File:JnimmersDatabaseinstruction1.png|thumb| Example of Access and highlighting of Text File Importing Button]]
 +
'''Task 3'''
 +
*In order to make sure that all Field Names and product names/ingredients are visible, hover your mouse over the right edge of the column you want to edit. A two sided arrow should appear,  indicating you can now edit the column length. Click and hold down at that point and drag you mouse left or right to adjust the size of your column in order to allow for all words to be properly viewed, including the field header.
 +
'''Task 4'''
 +
*Once my database was formatted correctly, I created relationships among my tables to show how each table corresponded with the other tables in the database. To do this,I clicked on the "Database Tools" tab and and selected "Relationships". This will open up a new page with a list of your current tables. From here, you can select all of your relevant tables by pressing Control on your keyboard while clicking on each individual table that you want to make a relationship for another table. The tables will pop up and the menu will disappear, giving you each table' field headers. From here, you want to connect tables by clicking and dragging on a field header from one box and overlapping it with another field header with the same name in a different table box. This pulls up a menu for you to confirm that you're making the right connection, if the field headers are identical then you can confirm the relationship and do the same process for the other fields that have relationships in separate tables. These relationships establish a connection between categories in one table and confirming that they're the same categories in a separate table. [[File:JnimmersDatabaseinstruction2.png|thumb| Example of Relationships Tab and Confirmation of Relationship Creation]].
 +
*From here, you should be done! Your tables are now relationship verified, formatted, and contain all necessary data provided by the FDA in a neat compilation. Be sure to Save your work under the "File" tab. Select "Save as", put it in a safe and simple place to find, name it appropriately, and do any last minute edits that you may like to spruce up your database and make it unique.
  
−
LMU provides free accounts to LinkedIn Learning (formerly Lynda.com).  You will take advantage of this resource by using the tutorial for learning Microsoft Access, the database software we will use in this class.
+
'''Creating a Query'''
−
* [https://its.lmu.edu/whatwedo/technologytraining/linkedinlearningatlmu/ This page] explains how to access LinkedIn Learning using your LMU credentials.
+
* To create a Query to draw relationships between sections of highlight a single field within your database, you can follow these steps.
−
* Complete the tutorial called "Learning Access 2016" by Adam Wilbert (Released on May 4, 2016).  It is 1 hour and 6 minutes long.  Be sure that you are selecting the right tutorial because there are several for MS Access.
+
*Under the "Create" Tab, click on the 'Query Wizard" option and select "Simple Query Wizard"
−
* When you are finished, download the certificate of completion, upload it to this wiki and link to it on your individual journal page.  Be sure to change the filename so that it is specific to you (e.g., add your last name or username to the filename) because there will be 14 of you uploading similar files.
+
*From there, you'll have the option to select which table you want to highlight, and within that table, you can select a field to highlight.
 +
*Once you have decided which field to highlight, click on it and press the single right-facing arrow to set it aside for the query.
 +
*Click "Next", confirm your settings, and click "Finish" to create a query of the field(s) that you selected for analysis.
 +
*And now you're done creating your first query! You can add more items to the query by repeating the "right-facing arrow" step for more than one field.
  
−
=== Preliminary Tasks ===
+
===Scientific Conclusion===
 +
*This project was given in order to learn the basic structure and function of the Microsoft Access program. It is meant to equip  me with the skill and knowledge of how to operate the system and sue it to create a database of my own using pre-made data fro the FDA. In terms of learning, I feel that I learned a lot from this program, and I think it can be a useful asset for anyone in the working world; it allows for organization at a level that can't be done with pen and paper, and it can store far more information than Excel, making it more optimal for a work or company setting, which can be useful for almost anyone.
  
−
==== Turning on File Extensions in Windows 10 ====
+
===Data and Files===
 +
*File:Jnimmers Certificate.png|thumb| Certificate of Completion from LinkedIn Learning
 +
*File:JnimmersFDAProducts.zip |FDA Database
 +
*File:Database File:JnimmersDatabaseinstruction1.png|thumb| Example of Access and highlighting of Text File Importing Button
 +
*File:Database File:JnimmersDatabaseinstruction2.png|thumb| Example of Relationships Tab and Confirmation of Relationship Creation
 +
*http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm | FDA Drug Information Website
  
−
* The Windows 10 operating systems defaults to hiding file extensions. To turn them back on, do the following: [[Image:FolderOptions.jpg|right|Folder Options window]]
+
===Acknowledgements===
−
*# Go to the Search menu (circle icon) "File Explorer Options" in the search field.  Select the result.
+
* I would like to thank Dr. Kam Dahlquist for her introduction of the Microsoft Access Software as well as instruction on how to access it and information on learning the database system as well
−
*# When the File Explorer Options window appears, click on the View tab.
+
* Thank you to LinkedIn Learning for assisting and teaching how to create a successful database [[https://www.linkedin.com/learning/me?u=42252777]]
−
*# Uncheck the box for "Hide extensions for known file types".
+
* Thank you to my homework partner [[User:Mavila9| Marcus Avila]]. We met in person and he assited me in formatting issues, learing to zip my file correctly and gave information on how to correctly update my database in Access.
−
*# 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.
+
"Except for what is noted above, this individual journal entry was completed by me and not copied from another source." [[User:Jnimmers|Jnimmers]] ([[User talk:Jnimmers|talk]]) 19:39, 25 September 2019 (PDT)
  
−
==== Setting the Download Options for FireFox and Chrome ====
+
=== References ===
 +
*Week 4 Assignment Page: LMU BioDB 2019. (2019). Week 4. Retrieved September 5th, 2019, from https://xmlpipedb.cs.lmu.edu/biodb/fall2019/index.php/Week_4
  
−
* For Mozilla FireFox (40.0.2 or higher)
+
{{Jnimmers}}
−
** Options set to default downloads to Desktop and to ask each time a file is downloaded
 
−
*** Go to the Options menu, General options
 
−
*** First, under the "Downloads" section, next to "Save files to" click the "Browse" button, select the T: drive, and click the OK button.
 
−
*** Second, click the radio button next to "Always ask me where to save files"
 
−
<!--:::[[Image:FireFox_Download_Settings.jpg]]-->
 
−
* For Google Chrome (Version 44.0.2403.157 m or higher)
 
−
** Options set to default downloads to Desktop and to ask each time a file is downloaded
 
−
*** Go to Settings page, click on link to "Show advanced settings"
 
−
*** First, under the "Downloads" section, click the "Change..." button, select the T: drive, and click the OK button.
 
−
*** Second, check the box next to "Ask where to save each file before downloading"
 
−
<!--:::[[Image:Chrome_Download_Settings.jpg]]-->
 
−
 
 
−
Note that for the computers in Seaver 120, anything saved to the Desktop, My Documents, Downloads, or any other folder will be deleted when the computers are restarted.  To make sure that your work will still be available to you, save your files to the T: drive.  ("T" stands for the "thawspace" of the Deep Freeze software that manages the deletions.)
 
−
 
 
−
==== Compressing and Decompressing Files with 7-Zip ====
 
−
 
 
−
* The 7-Zip file compression software has been installed on the computers in Seaver 120.  (If you want to install this software on your own computer, go to the [http://www.7-zip.org/ 7-zip Download page].)  To compress a single file or a group of files, do the following:
 
−
*# Select all of the files you want to zip together by clicking and dragging or control-clicking on the filenames.
 
−
*# Right-click on your selection.  In the context menu that appears, select the menu option: 7-Zip > Add to archive
 
−
*# Make sure there is a meaningful filename in the field under the word "Archive:".  If not, change it to something that is.  Typically you will want to include your name or initials, a short descriptor of what the file contains, and the date in year-month-day format (yyyy-mm-dd).
 
−
*# Select "zip" as the Archive format.
 
−
*# You do not need to change any of the other defaults.  Click OK.  The zip file will appear in the same folder as the files you compressed.
 
−
* To decompress files with 7-zip, right click on the file icon.  From the context menu that appears, select 7-zip > Extract Here.
 
−
** Note that Windows Explorer will allow you to view the contents of a zipped archive without actually decompressing them.  It may even seem like you can open them or otherwise manipulate them.  However, issues will arise from doing this, most commonly, not being able to save your changes.  Always make sure that you decompress (unzip) the files before using them.
 
−
 
 
−
=== 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.
 
−
** 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?
 
−
 
 
−
''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 (''activeingred'') is <code>ATROPINE</code>?
 
−
# In what ''form''s and ''dosage''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 (''sponsor applicant'' column) by <code>MERCK</code>?
 
−
# Which ''sponsor applicant'' 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?])
 
−
 
 
−
==== 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.
 
−
 
 
−
==== Acknowledgments ====
 
−
 
 
−
In this section, you need to acknowledge anyone who assisted you with your assignment, either in person, electronically, or even anonymously without their knowledge. 
 
−
# 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 (for example, via office hours), the TA, 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''''' (such as the methods of a protocol), that belongs in the References section where you will give a include a formal APA-formatted citation (see below).
 
−
# You '''must''' also include this statement unless otherwise noted: 
 
−
#* '''''"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.
 
−
 
 
−
==== References ====
 
−
 
 
−
*In this section, you need to provide properly formatted citations to any '''''content''''' that was not entirely of your own devising.  This includes, but is not limited to:
 
−
** methods
 
−
** data
 
−
** facts
 
−
** images
 
−
** documents, including the scientific literature
 
−
* Do not include extraneous references that you do not cite or use on your page.
 
−
* The references in this section should be accompanied by ''in text citations'' on your page that refer to these references.
 
−
* 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_Fall2019_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 2019. (2019). Week 1. Retrieved August 27, 2019, from https://xmlpipedb.cs.lmu.edu/biodb/fall2019/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.
 

Latest revision as of 18:54, 25 September 2019

File Containing Microsoft Access FDA Database

Electronic Notebook

Purpose

The purpose of this investigation was to get a better understanding of how to operate the Microsoft Access Database by using data collected from the Federal Drug Agency to create my own database. Besides that, this assignment was meant to allow for a visualization of how complex databases, such as the FDA database, is formed and how relationships between tables can be formed to create a cohesive and understandable database to provide as much information as possible to those who access it.

Certificate of Completion from LinkedIn Learning

Methods

Task 1

  • For this Task, I went to the [| FDA Drug Information Website] and downloaded the available drug data from that source. There is a link at the bottom of the page instructing you on how to download the .zip file.
  • From there, I found the files that contained relative data and extracted them from their .zip folder by finding a simple area to extract, right-clicking the folder, and selecting "Extract All" from the drop-down menu.

Task 2

  • Once the Text documents had been downloaded, I went into Microsoft (MS) Access and, under the "External Data" tab, I selected the "Import Text File" option and found the files that needed to be imported from the FDA folder. Each file imported will create a new table. The list of these tables can be found on the left side of the screen in MS Access. Most formatting was done automatically by Access, however, make sure to check the box that says "First Row Contains Field Names", it will allow for the automatic formatting of your column names. This makes the inclusion of IDs easier because if you delete an ID row, it forces you to start the process of importing over so that your first row doesn't start as ID:2.
    Example of Access and highlighting of Text File Importing Button

Task 3

  • In order to make sure that all Field Names and product names/ingredients are visible, hover your mouse over the right edge of the column you want to edit. A two sided arrow should appear, indicating you can now edit the column length. Click and hold down at that point and drag you mouse left or right to adjust the size of your column in order to allow for all words to be properly viewed, including the field header.

Task 4

  • Once my database was formatted correctly, I created relationships among my tables to show how each table corresponded with the other tables in the database. To do this,I clicked on the "Database Tools" tab and and selected "Relationships". This will open up a new page with a list of your current tables. From here, you can select all of your relevant tables by pressing Control on your keyboard while clicking on each individual table that you want to make a relationship for another table. The tables will pop up and the menu will disappear, giving you each table' field headers. From here, you want to connect tables by clicking and dragging on a field header from one box and overlapping it with another field header with the same name in a different table box. This pulls up a menu for you to confirm that you're making the right connection, if the field headers are identical then you can confirm the relationship and do the same process for the other fields that have relationships in separate tables. These relationships establish a connection between categories in one table and confirming that they're the same categories in a separate table.
    Example of Relationships Tab and Confirmation of Relationship Creation
    .
  • From here, you should be done! Your tables are now relationship verified, formatted, and contain all necessary data provided by the FDA in a neat compilation. Be sure to Save your work under the "File" tab. Select "Save as", put it in a safe and simple place to find, name it appropriately, and do any last minute edits that you may like to spruce up your database and make it unique.

Creating a Query

  • To create a Query to draw relationships between sections of highlight a single field within your database, you can follow these steps.
  • Under the "Create" Tab, click on the 'Query Wizard" option and select "Simple Query Wizard"
  • From there, you'll have the option to select which table you want to highlight, and within that table, you can select a field to highlight.
  • Once you have decided which field to highlight, click on it and press the single right-facing arrow to set it aside for the query.
  • Click "Next", confirm your settings, and click "Finish" to create a query of the field(s) that you selected for analysis.
  • And now you're done creating your first query! You can add more items to the query by repeating the "right-facing arrow" step for more than one field.

Scientific Conclusion

  • This project was given in order to learn the basic structure and function of the Microsoft Access program. It is meant to equip me with the skill and knowledge of how to operate the system and sue it to create a database of my own using pre-made data fro the FDA. In terms of learning, I feel that I learned a lot from this program, and I think it can be a useful asset for anyone in the working world; it allows for organization at a level that can't be done with pen and paper, and it can store far more information than Excel, making it more optimal for a work or company setting, which can be useful for almost anyone.

Data and Files

  • File:Jnimmers Certificate.png|thumb| Certificate of Completion from LinkedIn Learning
  • File:JnimmersFDAProducts.zip |FDA Database
  • File:Database File:JnimmersDatabaseinstruction1.png|thumb| Example of Access and highlighting of Text File Importing Button
  • File:Database File:JnimmersDatabaseinstruction2.png|thumb| Example of Relationships Tab and Confirmation of Relationship Creation
  • http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm | FDA Drug Information Website

Acknowledgements

  • I would like to thank Dr. Kam Dahlquist for her introduction of the Microsoft Access Software as well as instruction on how to access it and information on learning the database system as well
  • Thank you to LinkedIn Learning for assisting and teaching how to create a successful database [[1]]
  • Thank you to my homework partner Marcus Avila. We met in person and he assited me in formatting issues, learing to zip my file correctly and gave information on how to correctly update my database in Access.

"Except for what is noted above, this individual journal entry was completed by me and not copied from another source." Jnimmers (talk) 19:39, 25 September 2019 (PDT)

References


Biological Databases
Jnimmers
Assignment Table

Week Number Assignment Page Individual Journal Shared Journal
1 Week 1 Assignment Page N/A Week 2 Shared Journal
2 Week 2 Assignment Page Week 2 Individual Journal Week 2 Shared Journal
3 Week 3 Assignment Page CMR2/YOR093C Week 3 Week 3 Shared Journal
4 Week 4 Assignment Page Week 4 Individual Journal Week 4 Shared Journal
5 Week 5 Assignment Page CRISPRlnc Week 5 Week 5 Shared Journal
6 Week 6 Assignment Page Week 6 Individual Journal Week 6 Shared Journal
7 Week 7 Assignment Page Week 7 Individual Journal Week 7 Shared Journal
8 Week 8 Assignment Page Week 8 Individual Journal Week 8 Shared Journal
9 Week 9 Assignment Page Week 9 Individual Journal Week 9 Shared Journal
10 Week 10 Assignment Page Week 10 Individual Journal Week 10 Shared Journal
11 Week 11 Assignment Page Week 11 Individual Journal Sulfiknights Team Page
12/13 Week 12/13 Assignment Page Week 12/13 Individual Journal Sulfiknights Team Page
14 Week 14 Assignment Page Week 14 Individual Journal Week 14 Shared Journal
15 Week 15 Assignment Page Week 15 Individual Journal Week 15 Shared Journal
Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2019/index.php?title=Jnimmers_Week_4&oldid=3048"