SQL Server Question

Please Use the Excel File Database from Here: https://drive.google.com/open?id=0B_OOaWV7RgsRWndmQjdhYndlZVk

See the ERD Diagram from Here: https://drive.google.com/open?id=0B_OOaWV7RgsRcE1TWEVpSGh1ME0

1: Using MS SQL Server, do the following: (15 points)

Download HR data dictionary, HR data, and HR ERD diagram.

using MS SQL Server Create HR database.

A. Using HR data spreadsheet,Write a script that drops foreign keys if exists, drops tables if exists, creates tables, inserts data into tables and creates foreign keys

Please note (Foreign key is not part of the create table but it is implemented using alter command

*/

/*

2: Using the employee table of the HR database you have created in step one: (17 points)

CREATE FOLLOWING STORE PROCEDURES AND FUNCTIONS, INCLUDE DROP STORE PROCEDURE OR USER DEFINED FUNCTION STATEMENT EACH STORE PROCEDURE OR USER DEFINED FUNCTION YOU CREATE.

*/

/*

A: Create a store procedure with parameters that inserts record (EmployeeId,LastName,FirstName,JobTitle,BirthDate,MaritalStatus,Gender,HireDate,ModifiedDate) into EMPLOYEE table

(Name of the store procedure: Employee_insert)(3 pts)

*/

/*

B: Create a store procedure with parameters that updates EMPLOYEE JobTitle using employeeId and JobTitle as parameters (Name of the store procedure: Employee_JobTitle_Update) (3 pts)

*/

/*

C: Create a store procedure with parameters that returns EMPLOYEE's record (EmployeeId, LastName,FirstName, JobTitle,BirthDate, MaritalStatus,Gender,HireDate,ModifiedDate) using employeeId (Name of the store procedure: Employee_Select) (3 pts)

*/

/*

D: Create a stored procedure with parameters that deletes EMPLOYEE record using employeeId as parameter (Name of the store procedure: Employee_Delete) (3 pts)

*/

/*

E: Create a stored procedure with parameters that returns employee's full name (LastName, FirstName) using employeeId as parameter (Name of the store procedure: Employee_fullname_Select) (3 pts)

*/

/*

F: Create a user defined funcation that returns employee age) using employeeid as parameter (Name of the user defined function: udf_employee_age) (5 pts)

*/