Database Management SQL
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, category, reasoning, and whether the prize is shared). Assume that the same person cannot receive multiple prizes in the same year.
2. (23.5 points) Import the data into APEX.
a. Write a CREATE TABLE SQL statement for each table. Select appropriate data
types, sizes, and constraints for each field. Dont forget to add primary and foreign key constraints. Provide screenshots of your SQL statements in your Word/PDF file. Note: Do not use the Table Design wizard or Data Workshop to generate the CREATE TABLE code.
b. Use the Data Workshop to move your data into APEX. You can use the copy and paste or file upload method. Provide screenshots of the first few rows of your populated tables from the Object Browser.
3. (13.5 points) Use the database youve created to answer the following questions with simple queries (each query uses data from only one table). Provide screenshots of your SQL code and results tables in your Word/PDF file.
a. Which countries have had the most female winners? For the top six countries with the most female laureates, return the country name and total winners. Make sure to rename any new, calculated fields.
b. Many Nobel Prize winners have advanced degrees and titles like Doctor or Professor, but which winners do not have any titles? Return their IDs, full names (formatted as one field), and birthdates. Make sure to rename any new, calculated fields.
c. Some Nobel Prizes are awarded to individuals, and some may be shared among multiple people. For all instances where a woman won the prize individually in a category other than Literature or Peace, return the year, category, and reasoning for the award.
