1. Consider the following database Customer ID Policy ID First Last Address Phone Zip State Type Years Premium 123 999)- Matthew Baker Ludlow 111- 00884 TH Otm 114 Home 2 500 750 750 500 150 (999)- Stephanie Steiner Trench 112-12150 IY Tukum-; 2722 Ave 876 Home 4 259 Life 322 Car 17 999) 12 3Elizabeth Ramirez Candy 08977 PQ Alston 750 Ln 2342 Write down the outline for this database, as it is given to you. Explain in which normal form it is given to you. Normalize it to its 3rd normal form and do this by drawing bubble diagrams in every step, clearly specifying the nature of the dependencies. Write down the final database outline The field Years stores how many years have passed since a property type was insured. Assume that that you do have access to a full list of zip codes and that the premium depends exclusively on the type of policy (so, for instance, all home insurance policies cost the same). Use only the given column names (do not create new attributes). NOTE: For the following SQL questions, use the PracticeDatabase.accdb which is available on Blackboard Open the PracticeDatabase.accdb and save it as YourName_hw4 2. Create a query to select customers' first names, last names, and states who lives in states of New 3. 4. York, Massachusetts, Arizona, and Texas. Save your query as YourName Question2. Create a query to retrieve all columns from customers table for customers who do not live in Albany Save your query as YourName_Question3 Create a query to select product name, units in stock, and units on order from products table if units in stock is greater than 10 or units on order is greater than 10 for a product. Save your query as YourName Question4. 5. Write a query to create a customer report that will list last name, first name, city, and state of customers in the cities of Houston, Albany, and Phoenix from the states of Texas, New York, and Arizona respectively. Save your query as YourName Question5. NOTE: Your final submission for question 1 should be a single PDF file. For the rest of the questions, you are going to submit the database that you have saved your queries in (YourName_hw4.accdb) 1. Consider the following database Customer ID Policy ID First Last Address Phone Zip State Type Years Premium 123 999)- Matthew Baker Ludlow 111- 00884 TH Otm 114 Home 2 500 750 750 500 150 (999)- Stephanie Steiner Trench 112-12150 IY Tukum-; 2722 Ave 876 Home 4 259 Life 322 Car 17 999) 12 3Elizabeth Ramirez Candy 08977 PQ Alston 750 Ln 2342 Write down the outline for this database, as it is given to you. Explain in which normal form it is given to you. Normalize it to its 3rd normal form and do this by drawing bubble diagrams in every step, clearly specifying the nature of the dependencies. Write down the final database outline The field Years stores how many years have passed since a property type was insured. Assume that that you do have access to a full list of zip codes and that the premium depends exclusively on the type of policy (so, for instance, all home insurance policies cost the same). Use only the given column names (do not create new attributes). NOTE: For the following SQL questions, use the PracticeDatabase.accdb which is available on Blackboard Open the PracticeDatabase.accdb and save it as YourName_hw4 2. Create a query to select customers' first names, last names, and states who lives in states of New 3. 4. York, Massachusetts, Arizona, and Texas. Save your query as YourName Question2. Create a query to retrieve all columns from customers table for customers who do not live in Albany Save your query as YourName_Question3 Create a query to select product name, units in stock, and units on order from products table if units in stock is greater than 10 or units on order is greater than 10 for a product. Save your query as YourName Question4. 5. Write a query to create a customer report that will list last name, first name, city, and state of customers in the cities of Houston, Albany, and Phoenix from the states of Texas, New York, and Arizona respectively. Save your query as YourName Question5. NOTE: Your final submission for question 1 should be a single PDF file. For the rest of the questions, you are going to submit the database that you have saved your queries in (YourName_hw4.accdb)
