Problem $1(20$ points$)$

Create a MidT.sql script for SQL Server to create an exam$1$en database containing two

tables described below:

"Departments" table with columns: "DeptId" INT NOT NULL and

"Name" VARCHAR $(50)$ NOT NULL;

"MgrId" INT

"Employees" table with columns: "EmpId" INT NOT NULL,

"Name" VARCHAR $(50)$ NOT NULL,

"Age" INT NOT NULL,

"Salary" DECIMAL $(10,2)$ NOT NULL,

"DeptId" INT NOT NULL;

The primary key of the "Departments" table is "DeptId" and the primary key of the

"Employees" table is "EmpId".

In addition, the attribute "DeptId" in the "Employees" table is a foreign key that

references the attribute "DeptId" of the "Departments" table. This foreign key must not

allow us to delete a department if there are employees assigned to that department.

However, updates in Department table $($DeptId$)$ must be propagated to the Employees

table.

The attribute "MgrId" in the "Departments" table is a foreign key that references the

attribute "EmpId" of the "Employees" table. This foreign key must not allow us to delete

an employee if this employee is the manager of a department. This foreign key must

also prevent updating the "EmpId" of a department manager.

Your MidT.sql script must also insert the following data $($at a minimum$)$:

Coll$$ge LaSalle $4$ of $4$

Department $(1,$ 'Marketing', $3)$;

Department $(2,$ 'Accounting', $1)$;

Department $(3,$ 'Finance', $1)$;

Department $(4,\text{'}$IT$\text{'},14)$;

Employees $(1,$ 'Mary', $27,90,000,3)$;

Employees $(3,$ 'John', $32,90,000,1)$;

Employees $(7,$ 'Brian', $28,80,000,2)$;

Employees $(14,$ 'Anne', $28,95,000,4)$;

Employees $(32,$ 'James', $29,85,000,1)$;

Run your script to create this database and its tables.

Problem $2(70$ points$)$

Create a $3-$tiers C#$,$ Windows Form, ADO.NET application with two options at the

main menu of the window for:

$1.$ Showing a DataGridView adapted for the "Employees" table, which allows you to see

the data in the table, add new rows, modify rows and delete them $($deleting several

rows$).$ Use SQLAdaper.

$2.$ Showing a DataGridView adapted for the "Departments" table, which allows you to

see the data in the table, add new rows, modify rows and delete them $($deleting several

rows$).$ Use SQLAdaper.

Note: To correctly handle the deletion of multiple rows in the case of restriction by the

foreign key, the same foreign key must be defined between the DataTables. Therefore,

both DataTables must be loaded in memory before using any of these two tables for the

first time.

Problem $3(10$ points$)$

Include in your solution two business rules:

$1.$ no employee can be less than $18$ years old nor older than $120$ years.

$2.$ no employee can have a salary lower than or equal to Can$$15,000\mathrm{.}00.$

In case of violation of these rules, messages must be specific and the insertion or

update must be rejected.

Create a Word or PDF file with the screen shots showing that your solution executes

correctly.