Reference no: EM133719780

Normalization Homework

You will use the ViewsSeqHWStart.sql file. This script creates two tables and inserts some data. After running this script, do the following (IN ORDER!). As always, put your name on the HW and number each problem (commented out). The deliverable for this part of the HW is a .sql file.

--1
Create a view called instrument_info_view, that contains all fields in the instrument table.

--2
Add a new instrument to the above view: washboard, $0.22, "For those with less talent only".

--3
Query the view, displaying all fields but restricting the results to those instruments costing less than $400. Sort by cost, from highest to lowest.

--4
Create another view called band_instrument_view, that includes the following fields: instrument name, band member first and last name and length of time in the band.

--5
Modify the above view (#4) to also include the instrument description field and proficiency rating (as well as the previous fields-: instrument name, band member first and last name, and length of time in band). The view should have the same name. Make sure for this view that users are not allowed to make any changes to the underlying tables.

--6
Query this view, including first and last name, instrument, and proficiency rating.

--7
Create an index (instrument_inst_name_idx) on the instrument name field.

--8
Create a query, using the group function COUNT (*) for all instrument names that have an ‘a' anywhere in the name. Run this query, then answer the following question:

Using the Explain Plan, does the query use the index or the table in doing the query? W hat is the cost?

Do the following:

1. List all fields that are totally dependent on the PK fields (PlayerID and Year):

2. List all fields that have partial dependence (on one or the other PK fields), and tell which of the PK fields it is dependent on:

3. List all fields that have transitive dependence, and tell which field it is dependent on:

4. Change the DB from 1NF to 2NF. Write the tables on separate lines, with PKs first (indicate by PK), and other fields to the side (indicate by FK the foreign key fields). I've put the tables (and some fields) in to help. All fields must be accounted for.