On SQL Managment studio

This lab is all about database normalization. Download, from DocShare, and run Week 6 Normalizing.sql to create the four databases, UNF (Unnormalized Form), FNF (First Normal Form), SNF (Second Normal Form), and TNF (Third Norma Form) used in this lab. Each of the following will be executed against all four databases. This will require a use and go statement between each query. All four queries should be able to run at one time. The goal of this lab is to show how the different normalization change how data is queried. Note: The same result set will be returned from each database. This is a good check to see that each query is correct.

Write a select statement that returns the project code, project name, and project manager.

List each Employees name, department, and department name.

List employee by project.

List each employee, project name, hourly rate, and department name.

the database is

Use master GO

-- -- Unnormalized Database --

CREATE DATABASE UNF; GO

USE UNF; GO

CREATE TABLE Unnormalized ( ProjectCode varchar(100), ProjectName varchar(100), ProjectManager varchar(255), ProjectBudget Decimal(10,2), EmployeeNumber varchar(10), EmployeeName varchar(100), DepartmentNumber varchar(10), DepartmentName varchar(100), HourlyRate Decimal(10,2) );

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC010', 'Reservation System', 'Mr. Jones', 120500.00, 'S100', 'John', 'D03', 'Database', 21.00)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC010', 'Reservation System', 'Mr. Jones', 120500.00, 'S101', 'George', 'D02', 'Testing', 16.50)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC010', 'Reservation System', 'Mr. Jones', 120500.00, 'S102', 'Bob', 'D01', 'IT', 22.00)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC011', 'HR System', 'Mrs. Smith', 500500.00, 'S103', 'Jack', 'D03', 'Database', 18.50)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC011', 'HR System', 'Mrs. Smith', 500500.00, 'S104', 'Jane', 'D02', 'Testing', 17.00)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC011', 'HR System', 'Mrs. Smith', 500500.00, 'S315', 'Dave', 'D01', 'IT', 23.50)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC012', 'Attendance System', 'Mr. Doe', 710700.00, 'S137', 'Sam', 'D03', 'Database', 21.50)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC012', 'Attendance System', 'Mr. Doe', 710700.00, 'S218', 'Neil', 'D02', 'Testing', 15.50)

INSERT INTO Unnormalized (ProjectCode, ProjectName, ProjectManager, ProjectBudget, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC012', 'Attendance System', 'Mr. Doe', 710700.00, 'S109', 'Hope', 'D01', 'IT', 20.50)

GO

-- -- First Normal Form -- -- 1 - Separate the repeating fields into new database tables along with the key from -- unnormalized database table. -- -- 2 - The primary key of new database tables may be a composite key --

CREATE DATABASE FNF; GO

USE FNF; GO

CREATE TABLE ProjectInfo ( ProjectCode varchar(100), ProjectName varchar(100), ProjectManager varchar(255), ProjectBudget Decimal(10,2) PRIMARY KEY (ProjectCode) );

CREATE TABLE ProjectStaff ( ProjectCode varchar(100), EmployeeNumber varchar(10), EmployeeName varchar(100), DepartmentNumber varchar(10), DepartmentName varchar(100), HourlyRate Decimal(10,2) PRIMARY KEY (ProjectCode, EmployeeNumber) FOREIGN KEY (ProjectCode) REFERENCES ProjectInfo );

GO

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC010', 'Reservation System', 'Mr. Jones', 120500.00);

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC011', 'HR System', 'Mrs. Smith', 500500.00);

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC012', 'Attendance System', 'Mr. Doe', 710700.00);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC010', 'S100', 'John', 'D03', 'Database', 21.00);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC010', 'S101', 'George', 'D02', 'Testing', 16.50);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC010', 'S102', 'Bob', 'D01', 'IT', 22.00);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC011', 'S103', 'Jack', 'D03', 'Database', 18.50);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC011', 'S104', 'Jane', 'D02', 'Testing', 17.00);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC011', 'S315', 'Dave', 'D01', 'IT', 23.50);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC012', 'S137', 'Sam', 'D03', 'Database', 21.50);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC012', 'S218', 'Neil', 'D02', 'Testing', 15.50);

INSERT INTO ProjectStaff (ProjectCode, EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName, HourlyRate) VALUES ('PC012', 'S109', 'Hope', 'D01', 'IT', 20.50);

GO

-- -- Second Normal Form -- -- 1 - First Normal Form Plus: -- -- 2 - Remove the partial dependencies(A type of functional dependency where a field -- is only functionally dependent on the part of primary key) of any non-key field. -- -- 3 - If field B depends on field A and vice versa. Also for a given value of B, we have -- only one possible value of A and vice versa, Then we put the field B in to new -- database table where B will be primary key and also marked as foreign key in -- parent table. --

CREATE DATABASE SNF; GO

USE SNF; GO

CREATE TABLE ProjectInfo ( ProjectCode varchar(100), ProjectName varchar(100), ProjectManager varchar(255), ProjectBudget Decimal(10,2) PRIMARY KEY (ProjectCode) );

CREATE TABLE EmployeeInfo ( EmployeeNumber varchar(10), EmployeeName varchar(100), DepartmentNumber varchar(10), DepartmentName varchar(100) PRIMARY KEY (EmployeeNumber)

);

CREATE TABLE ProjectRates ( ProjectCode varchar(100), EmployeeNumber varchar(10), HourlyRate Decimal(10,2) PRIMARY KEY (ProjectCode, EmployeeNumber) FOREIGN KEY (ProjectCode) REFERENCES ProjectInfo, FOREIGN KEY (EmployeeNumber) REFERENCES EmployeeInfo );

GO

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC010', 'Reservation System', 'Mr. Jones', 120500.00);

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC011', 'HR System', 'Mrs. Smith', 500500.00);

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC012', 'Attendance System', 'Mr. Doe', 710700.00);

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S100', 'John', 'D03', 'Database');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S101', 'George', 'D02', 'Testing');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S102', 'Bob', 'D01', 'IT');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S103', 'Jack', 'D03', 'Database');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S104', 'Jane', 'D02', 'Testing');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S315', 'Dave', 'D01', 'IT');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S137', 'Sam', 'D03', 'Database');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S218', 'Neil', 'D02', 'Testing');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber, DepartmentName) VALUES ('S109', 'Hope', 'D01', 'IT');

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC010', 'S100', 21.00);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC010', 'S101', 16.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC010', 'S102', 22.00);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC011', 'S103', 18.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC011', 'S104', 17.00);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC011', 'S315', 23.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC012', 'S137', 21.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC012', 'S218', 15.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC012', 'S109', 20.50);

GO

-- -- Third Normal Form -- -- 1 - Second Normal Form Plus: -- -- 2 - Remove the transitive dependecies(A type of functional dependency where a field -- is functionally dependent on the Field that is not the primary key.Hence -- its value is determined, indirectly by the primary key ) -- -- 3 - Make separate table for transitive dependent Field. --

CREATE DATABASE TNF; GO

USE TNF; GO

CREATE TABLE ProjectInfo ( ProjectCode varchar(100), ProjectName varchar(100), ProjectManager varchar(255), ProjectBudget Decimal(10,2) PRIMARY KEY (ProjectCode) );

CREATE TABLE DepartmentInfo ( DepartmentNumber varchar(10), DepartmentName varchar(100), PRIMARY KEY (DepartmentNumber) );

CREATE TABLE EmployeeInfo ( EmployeeNumber varchar(10), EmployeeName varchar(100), DepartmentNumber varchar(10) PRIMARY KEY (EmployeeNumber) FOREIGN KEY REFERENCES DepartmentInfo(DepartmentNumber) );

CREATE TABLE ProjectRates ( ProjectCode varchar(100), EmployeeNumber varchar(10), HourlyRate Decimal(10,2) PRIMARY KEY (ProjectCode, EmployeeNumber) FOREIGN KEY (ProjectCode) REFERENCES ProjectInfo, FOREIGN KEY (EmployeeNumber) REFERENCES EmployeeInfo );

GO

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC010', 'Reservation System', 'Mr. Jones', 120500.00);

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC011', 'HR System', 'Mrs. Smith', 500500.00);

INSERT INTO ProjectInfo (ProjectCode, ProjectName, ProjectManager, ProjectBudget) VALUES ('PC012', 'Attendance System', 'Mr. Doe', 710700.00);

INSERT INTO DepartmentInfo (DepartmentNumber, DepartmentName) VALUES ('D03', 'Database');

INSERT INTO DepartmentInfo (DepartmentNumber, DepartmentName) VALUES ('D02', 'Testing');

INSERT INTO DepartmentInfo (DepartmentNumber, DepartmentName) VALUES ('D01', 'IT');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S100', 'John', 'D03');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S101', 'George', 'D02');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S102', 'Bob', 'D01');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S103', 'Jack', 'D03');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S104', 'Jane', 'D02');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S315', 'Dave', 'D01');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S137', 'Sam', 'D03');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S218', 'Neil', 'D02');

INSERT INTO EmployeeInfo (EmployeeNumber, EmployeeName, DepartmentNumber) VALUES ('S109', 'Hope', 'D01');

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC010', 'S100', 21.00);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC010', 'S101', 16.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC010', 'S102', 22.00);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC011', 'S103', 18.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC011', 'S104', 17.00);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC011', 'S315', 23.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC012', 'S137', 21.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC012', 'S218', 15.50);

INSERT INTO ProjectRates (ProjectCode, EmployeeNumber, HourlyRate) VALUES ('PC012', 'S109', 20.50);