How do I normalize this from where it is in 1 normal form to 2 normal form, then from 2 normal form to 3 normal form? I am looking for explanation and code examples to better understand the reason for the conversion and to see the code needed so I can see a model of how to do it.
HERE IS THE STARTER CODE:
drop table if exists xyz_consulting

go

create table xyz_consulting

(

project_id int not null,

project_name varchar(50) not null,

employee_id int not null,

employee_name varchar(50) not null,

rate_category char(1) not null,

rate_amount money not null,

billable_hours int not null,

total_billed money not null,

constraint pk_xyz_consulting primary key(project_id, employee_id)

)

insert into xyz_consulting values

(1023, 'Madagascar travel site', 11, 'Carol Ling', 'A', 60.00, 5, 300.00 ),

(1023, 'Madagascar travel site', 12, 'Chip Atooth', 'B', 50.00, 10, 500.00 ),

(1023, 'Madagascar travel site', 16, 'Charlie Horse', 'C', 40.00, 2, 80.00),

(1056, 'Online estate agency', 11, 'Carol Ling', 'D', 90.00, 5, 450.00 ),

(1056, 'Online estate agency', 17, 'Avi Maria', 'B', 50.00, 2, 100.00 ),

(1099, 'Open travel network', 11, 'Carol Ling', 'A', 60.00, 6, 360.00 ),

(1099, 'Open travel network', 12, 'Chip Atooth', 'C', 40.00, 8, 320.00 ),

(1099, 'Open travel network', 14, 'Arnie Hurtz', 'D', 90.00, 3, 270.00 )

GO

select * from xyz_consulting order by employee_id
I think I understand that this is in 1 normal form because there are no columns with multiple values.

 

Transcribed Image Text:

5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 insert into xyz_consulting values (1023, 'Madagascar travel site', 11, 'Carol Ling', 'A', (1023, 'Madagascar travel site', 12, 'Chip Atooth', 'B', (1023, 'Madagascar travel site', 16, 'Charlie Horse', 23 (1056, 'Online estate agency', 11, 'Carol Ling', 'D', 24 (1056, 'Online estate agency', 17, 'Avi Maria', 'B', 25 (1099, 'Open travel network', 11, 'Carol Ling', 'A', 26 (1099, 'Open travel network', 12, 'Chip Atooth', 'C', (1099, 'Open travel network', 14, 'Arnie Hurtz', 'D', 27 28 GO 29 select from xyz_consulting order by employee_id Results Messages drop table if exists xyz_consulting go create table xyz_consulting ( 1 2 3 4 5 6 7 8 project_id int not null, project_name varchar (50) not null, employee_id int not null, 1023 1056 1099 1099 1023 1099 1023 1056 employee_name varchar(50) not null, rate_category char(1) not null, rate_amount money not null, billable_hours int not null, total_billed money not null, constraint pk_xyz_consulting primary key (project_id, employee_id) project_id project_name Madagascar travel site Online estate agency Open travel network Open travel network Madagascar travel site Open travel network Madagascar travel site Online estate agency employee_id 11 11 11 12 12 14 16 17 60.00, 50.00, 'C', 90.00, 50.00, 60.00, 40.00, 90.00, employee_name 40.00, 5, 2, 6, 8, 3, Carol Ling Carol Ling Carol Ling Chip Atooth Chip Atooth Arnie Hurtz Charlie Horse Avi Maria 5, 300.00), 10, 500.00), 80.00), 2, 450.00), 100.00), 360.00), 320.00), 270.00 ) Vrate_category A D A C B D C B Vrate_amount 60.00 90.00 60.00 40.00 50.00 90.00 40.00 50.00 billable_hours 5 5 6 8 10 3 2 2 total billed 300.00 450.00 360.00 320.00 500.00 270.00 80.00 100.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 insert into xyz_consulting values (1023, 'Madagascar travel site', 11, 'Carol Ling', 'A', (1023, 'Madagascar travel site', 12, 'Chip Atooth', 'B', (1023, 'Madagascar travel site', 16, 'Charlie Horse', 23 (1056, 'Online estate agency', 11, 'Carol Ling', 'D', 24 (1056, 'Online estate agency', 17, 'Avi Maria', 'B', 25 (1099, 'Open travel network', 11, 'Carol Ling', 'A', 26 (1099, 'Open travel network', 12, 'Chip Atooth', 'C', (1099, 'Open travel network', 14, 'Arnie Hurtz', 'D', 27 28 GO 29 select from xyz_consulting order by employee_id Results Messages drop table if exists xyz_consulting go create table xyz_consulting ( 1 2 3 4 5 6 7 8 project_id int not null, project_name varchar (50) not null, employee_id int not null, 1023 1056 1099 1099 1023 1099 1023 1056 employee_name varchar(50) not null, rate_category char(1) not null, rate_amount money not null, billable_hours int not null, total_billed money not null, constraint pk_xyz_consulting primary key (project_id, employee_id) project_id project_name Madagascar travel site Online estate agency Open travel network Open travel network Madagascar travel site Open travel network Madagascar travel site Online estate agency employee_id 11 11 11 12 12 14 16 17 60.00, 50.00, 'C', 90.00, 50.00, 60.00, 40.00, 90.00, employee_name 40.00, 5, 2, 6, 8, 3, Carol Ling Carol Ling Carol Ling Chip Atooth Chip Atooth Arnie Hurtz Charlie Horse Avi Maria 5, 300.00), 10, 500.00), 80.00), 2, 450.00), 100.00), 360.00), 320.00), 270.00 ) Vrate_category A D A C B D C B Vrate_amount 60.00 90.00 60.00 40.00 50.00 90.00 40.00 50.00 billable_hours 5 5 6 8 10 3 2 2 total billed 300.00 450.00 360.00 320.00 500.00 270.00 80.00 100.00

Answer rating: 100% (QA)

Based on the image provided we can see a table xyzconsulting that consists of multiple attributes including projectid projectname employeeid employeename ratecategory rateamount billablehours and tota... View the full answer