Week 4

This page is under construction.

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

Note that there is an interim deadline of Tuesday, September 17, at 2:00pm Pacific time to complete the LinkedIn Learning tutorial and upload your completion certificate to your individual journal page.

Overview

The purpose of this assignment is:

• To gain experience working directly with a relational database using the Microsoft Access software
• 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 4" (i.e., this is the text to place between the square brackets when you link to this page).
• Create the following set of links. (HINT: These links should all be in your personal template that you created for the Week 1 Assignment; you should then simply invoke your template on each new journal entry.)
  • Link to your journal entry from your user page.
  • Link back from your journal entry to your user page.
  • Link to this assignment from your journal entry.
  • Add the "Journal Entry" category to the end of your wiki page.

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:

• Aby and Michael
• Christina and David
• DeLisa and Naomi
• Emma and Iliana
• Ivy and Jonar
• Joey and Marcus
• Kaitlyn and Mihir

LinkedIn Learning Tutorial for MS Access

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.

• This page explains how to access LinkedIn Learning using your LMU credentials.
• 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.
• 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.

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 uncompress the files.
2. Define appropriate tables for the Application and Product entities.
3. Import the data files into a Microsoft Access database you will create.
4. Answer the questions below.

Preliminary Tasks

Turning on File Extensions in Windows 7

• The Windows 10 operating systems defaults to hiding file extensions. To turn them back on, do the following:
  
  1. Go to the Start menu and select "Control Panel".
  2. In the window that appears, search for "Folder Options" in the search field in the upper right hand corner.
  3. Click on "Folder Options" in the main window.
  4. When the Folder Options window appears, click on the View tab.
  5. Uncheck the box for "Hide extensions for known file types".
  6. 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 (40.0.2 or higher)
  • 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"
• 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"

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 7-zip Download page.) To compress a single file or a group of files, do the following:
  1. Select all of the files you want to zip together by clicking and dragging or control-clicking on the filenames.
  2. Right-click on your selection. In the context menu that appears, select the menu option: 7-Zip > Add to archive
  3. 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).
  4. Select "zip" as the Archive format.
  5. 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.

Questions to Answer

1. Provide the DDL (create table) statements that you used for your application and product tables.
2. Provide the sed command sequences that you used to convert the raw text files into sequences of SQL insert statements.
3. Using the command line, how can you determine the number of records in each file? Provide the command.
4. Using SQL, how can you determine the number of records in the table corresponding to the file? Provide the SQL select statement.
5. In your database, are these numbers the same or different? Explain why you think so.

   For the remaining questions, supply (a) the answer to the question and (b) the SQL statement that you used to answer the question.

6. What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?
7. What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?
8. In what forms and dosages can the drug product named BENADRYL be administered?
9. Which drug products have a name ending in ESTROL?
10. 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.)
11. 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.)
12. What are the names of the drug products that are sponsored (sponsor applicant column) by MERCK?
13. Which sponsor applicant companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE?

Shared Journal Assignment

• Store your journal entry in the shared Class Journal Week 4 page. If this page does not exist yet, go ahead and create it (congratulations on getting in first :) )
• Link to your journal entry from your user page.
• Link back from the journal entry to your user page.
  • NOTE: you can easily fulfill the links part of these instructions by adding them to your template and using the template on 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

• Blog post by LMU librarian Aisha Conner-Gaten, Lynda Becomes LinkedIn Learning, posted on August 1, 2019.

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—Spotify music, Fitbit exercise tracking, Twitter tweets, IRS tax returns, etc.—might look when stored on a computer?
2. Section 5.2 half-jokingly says that, by building a bookstore, you actually built the death of bookstores. You just built a drug database—have you actually built the death of pharmacies? What do you think of this analogy?
3. Section 5.3 says that the Java language can “talk to a database.” But you just “talked” to a database in this assignment. Why do you think you would need a programming language to do the talking?