数据库作业代写/database作业代写：本次作业为CS代写中的数据库代写内容，涉及数据库设计、sql语句编写等内容
Project Info
Submission
Submit a single “.sql” file to the Project 3 drop box.
You may submit multiple times to the dropbox. Only the most recent “.sql” file submission will be graded.
This is an individual assignment. Significant overlap between submissions from two or more students may be flagged for plagiarism.
Grading
The project is graded out of 20 total marks. Each requirement has an associated grade in its description.
Each requirement will be graded independently, though most requirements are at least partially dependent on the successful completion of previous steps.
Initialization
This project must run against the database created by “Project3_InitialDB.sql” in the Project 3 folder.
Additional Info
Please submit all work in a single file and use comments to clarify where your work for a requirement begins and ends.
For example:
/* REQUIREMENT 1 */
SELECT *
FROM dbo.JustAnExample;
/* REQUIREMENT 2 */
SELECT *
FROM dbo.AlsoAnExample;
You are encouraged to test your code before submitting. Your script must execute without error against the database created by “Project3_InitialDB.sql”.
Requirements
Requirement 1 – User Defined Types ( 2 Marks )
Create a new user defined table type called dbo.DepartmentType, to be used with dbo.Departments. The type should include columns for DepartmentName and DepartmentDesc. It can exclude DepartmentID.
Requirement 2 – Basic Stored Procedure ( 1 Mark )
Create a stored procedure to insert into dbo.Departments.
The procedure should accept a parameter using dbo.DepartmentType. This parameter should be used to insert into the dbo.Department table.
Requirement 3 – Basic Procedure Execution ( 1 Mark )
Write a script that will execute the procedure created in requirement 2. Create the following three departments:
DepartmentName DepartmentDesc
QA Quality Assurance
Engineering Systems design and development
Support Product support
Requirement 3 – Scalar Function ( 2 Mark )
Create a function to get an employee ID by first name and last name.
The function should accept two parameters – one to be used to reference FirstName and one to reference LastName. Both parameters should have appropriate data types for this task.
The return type should be appropriate for returning EmployeeID.
The function should return the EmployeeID of the employee that is found. If it is not found, the function should return NULL.
Requirement 4 – Intermediate Stored Procedure ( 6 Marks )
Create a stored procedure that will insert a record into dbo.Employees.
The procedure should accept the following parameters:
· DepartmentID
· EmployeeFirstName
· EmployeeLastName
· Salary
· ManagerFirstName
· ManagerLastName
The Salary parameter should be optional. If not specified, it should default to 30000.
The procedure should use the function created in requirement 3 to look up the manager’s employee ID by first name and last name. If the function returns null, a new manager should be created.
The procedure should insert a new employee, using the DepartmentID, EmployeeFirstName, EmployeeLastName, and Salary parameters. For the ManagerEmployeeID column, it should use the ID that was either found or created in the step above.
Any new managers created by this procedure should not be committed to the database if the insert for the employee fails.
Requirement 5 – Window Function ( 4 Marks )
Write a window function that will rank employees by department, based on descending salary (i.e. highest salary should be #1). The query should also get the name and salary of the person with the next highest salary in that department. Include the FirstName, LastName, and Salary columns in your select.
Requirement 6 – Recursive CTE ( 4 Marks )
Write a recursive CTE that will get employees by their manager. Include the following columns:
· Employee FirstName
· Employee LastName
· Department ID
· Manager FirstName
· Manager LastName
Also include a column called “Level” that will increment for each level in the tree. This means that an employee with no manager would be level 1, employees of level 1 employees would be level 2, employees of level 2 employees would be level 3, et cetera.