I need help with this I need this in SQL and please, I need to also see what this looks like in an SQL query with Microsoft SQL Server please show me what this looks like. Thank you very much for your help!

In this lab you will normalize an unnormalized table to the third normal form (3NF) using the bottom-up approach learned in class and the textbook. You will also create a database to store the tables created and provide an Entity Relationship Diagram.

Verify that all of the tables are complete with the appropriate fields, types, lengths, and any default values, keys, and constraints (e.g. FK).

The following diagram is an unnormalized table of a flat-file (Excel) containing the entities and attributes for this assignment.

MAKE SURE document your work and your commands work before you pass them into the document. Do not use the GUI interface to alter these actions.

Instructions

You are to complete the following actions. For each question below paste in print screens of your normalization process (tables in appropriate normal forms) and query window showing the command and the functioning output.

1. First Normal Form (1NF)

In this step, transform the table of unnormalized data into the first normal form (1NF).

The rule is: remove any repeating attributes to a new table. The process is as follows:

• Identify repeating attributes.
• Remove these repeating attributes to a new table together with a copy of the key from the UNF table.
• Assign a key to the new table (and underline it). The key from the original unnormalized table always becomes part of the key of the new table. A compound key is created. The value for this key must be unique for each entity occurrence.

YOUR TABLES IN 1NF:

2. Second Normal Form (2NF)

The next step is to transform the data in first normal form (1NF) into second normal form (2NF). The rule is: remove any non-key attributes that only depend on part of the table key to a new table. Ignore tables with (a) a simple key or (b) with no non-key attributes (these go straight to 2NF with no conversion). The process is as follows:

1. Take each non-key attribute in turn and ask the question: is this attribute dependent on one part of the key?
   • If yes, remove attribute to new table with a copy of the part of the key it is dependent upon. The key it is dependent upon becomes the key in the new table. Underline the key in this new table.
   • If no, check against other part of the key and repeat above process
   • If still no (i.e. not dependent on either part of key), keep attribute in current table.

YOUR TABLES IN 2NF:

3. Third Normal Form (3NF)

The next step is to transform the data in the second normal form (2NF) into the third normal form (3NF). The rule is: remove to a new table any non-key attributes that are more dependent on other non-key attributes than the table key. Ignore tables with zero or only one non-key attribute (these go straight to 3NF with no conversion). The process is as follows:

• If a non-key attribute is more dependent on another non-key attribute than the table key:
  • Move the dependent attribute, together with a copy of the non-key attribute upon which it is dependent, to a new table.
  • Make the non-key attribute, upon which it is dependent, the key in the new table. Underline the key in this new table.
  • Leave the non-key attribute, upon which it is dependent, in the original table and mark it a foreign key (*).

YOUR TABLES IN 3NF:

4. Complete the following SQL commands.

1. Create a database called NormalizeDB.
2. Create all the tables you have normalized. Youre not required to enter the data, but just the table structures and their relationships.

YOUR COMMANDS WERE:

5. Verify that all of the tables were created correctly with the appropriate constraints and relationships. Create an ERD showing all tables and their relationships.

YOUR VERIFICATIONS:

Project Manager M Phillips Project Budget 24500 Employee Employee No. Name S10001 A Smith Department No. L004 Department Name IT Hourly Rate 22.00 M Phillips 24500 S10030 L Jones LO23 Pensions 18.50 M Phillips 24500 S21010 P Lewis L004 IT 21.00 H Martin 17400 S10010 B Jones L004 IT 21.75 Project Project Title Code PC010 Pensions System PC010 Pensions System PC010 Pensions System PC045 Salaries System PC045 Salaries System PC045 Salaries System PC045 Salaries System PC064 HR System PC064 HR System PC064 HR System PC010 Pensions System H Martin 17400 S10001 A Smith L004 IT 18.00 H Martin 17400 S31002 T Gilbert LO28 Database 25.50 H Martin 17400 S13210 W Richards L008 Salary 17.00 K Lewis K Lewis K Lewis M Phillips 12250 12250 12250 24500 S31002 S21010 S10034 S10001 T Gilbert P Lewis B James A Smith L028 L004 L009 L004 Database IT HR IT 23.25 17.50 16.50 22.00 S10030 S21010 S10010 L Jones P Lewis B Jones LO23 L004 L004 Pensions IT IT 18.50 21.00 21.75 PC045 H Martin 17400 Salaries System S10001 S31002 S13210 S31002 S21010 S10034 A Smith T Gilbert W Richards T Gilbert P Lewis B James HR System L004 LO28 LO08 L028 L004 L009 IT Database Salary Database IT HR 18.00 25.50 17.00 23.25 17.50 16.50 PC064 K Lewis 12250