For this homework, you need to submit three files:
o Cleaned and processed Excel (.xlsx) file
o APEX-encoded SQL script (.sql) with your commands and queries to answer each question. (Note: use the “Export” option under SQL Scripts.) Use SQL comments (--) to mark question number in your script.
o Word or PDF file with screenshots of the code and output for each question
• Your file names should contain your last name and the assignment name (e.g., “lastname_homework3.xlsx”, “lastname_homework3.sql”, “lastname_homework3.docx”)
• All files should be attached in one submission:
o If you are using a cloud service (e.g., Google Drive, OneDrive), to save your files, you may need to download the files and submit the local copies (stored on your computer) to ICON
o If you need to re-submit your assignment, make sure to include all files
Download the “nobel.xlsx” file from ICON. This file contains 54 rows tracking information about
female winners of the Nobel Prize (https://www.nobelprize.org/). This data is sourced from
Wikipedia (https://en.wikipedia.org/wiki/List_of_female_Nobel_laureates). The data contains the
following fields:
1. (13 points) Clean and normalize the data. Submit your processed Excel file with a sheet for each relation/table. You do not need to submit screenshots of the Excel data.
a. The raw data contains a multivalued field (Country) that must be resolved before loading the data into APEX
b. The raw data contains a composite field (Laureate) that must be resolved before loading the data into APEX. Hint: Every laureate has a first name. Title, middle name, and last name are optional.
c. Break up the raw data into 3 tables. LAUREATE records information about each
woman who has won a Nobel Prize, including ID, name (title, first, middle, last),
birthdate, and death date. COUNTRY tracks the ID and country for each
laureate. AWARD contains information about the award itself (year, recipient ID,
Get Free Quote!
407 Experts Online