I need only Professional experts on Database, In this project, you will be asked to enhance the COMPANY schema below by adding a new table called COMPANY$\_$VEHICLE and linking it to the EMPLOYEE table. Using the enhanced schema, you will then be asked to write some SQL queries, views, triggers, and stored procedures. To complete this project, please follow these steps:

$1)$Use the CREATE TABLE command to create the COMPANY$\_$VEHICLE table. This table will contain the following attributes $($underlined attribute$($s$)$ denotes primary key$)$:$(2$ points$)$

Attribute Data Type

LicenseNumber VARCHAR$(15)$

Model VARCHAR$(20)$

Make VARCHAR$(20)$

Year CHAR$(2)$

Assignee CHAR$(9)$: foreign key corresponding to the Ssn primary key in EMPLOYEE

Insert here the CREATE TABLE command you used to create the above table.

$2)$Use INSERT INTO command to fill the table as shown below:$(2$ points$)$

$$LicenseNumber Model Make Year Assignee

TEXAS ABC$-739$ Mustang Ford $02333445555$

TEXAS RSK$-629$ XJS Jaguar $05888665555$

TEXAS TQR$-515$ Sonata Hyundai $07987654321$

TEXAS OPU$-010$ Camry Toyota $04$ null

TEXAS FGT$-111$ Cavalier Chevrolet $08$ null

TEXAS YHF$-235$ Elantra Hyundai $09$ null

TEXAS TQY$-689$ Avalon Toyota $06$ null

$$Insert here INSERT INTO commands you used to populate the table.

$3\mathrm{.}1$ Create a view called V$1$ that, for each employee who was assigned a vehicle, will show the employee first name, middle initial, last name, the model, make, and year of the vehicle assigned to him$/$her$.(1\mathrm{.}5$ point$)$

Insert here the CREATE VIEW command you used to create the above view and show the output of this view.

$3\mathrm{.}2$ Create another view V$2$ that will show the social security number of each employee, $$number of projects he$/$she works on and the total number of hours he spent on all the projects ordered by the social security number in ascending order. That is$,$ the view should show the following information: $(1\mathrm{.}5$ point$)$

Social Security Number # of Projects Total Hours

$=========================================$

$123456789240$

$333445555440$

$453453453240$

$666884444140$

$8886655551$

$987654321235$

$987987987240$

$999887777240$

$$Insert here the CREATE VIEW command you used to create the above view and show the output of this view.

$4)$Mr$.$ Ahmad V Jabbar has quit the company. Use the DELETE command to delete his records from the WORKS$\_$ON and EMPLOYEE tables. $(0\mathrm{.}5$ point$)$

$$Insert here the DELETE command you used to delete the records.$$

$5)$Mr$.$ James E Borg has changed his address and received a $10\%$ salary increase. Use the UPDATE command to change his address to $11$ Thunder Rd$.,$ Houston TX$$ and increase his salary by $10\%.(1$ point$)$

$$Insert here the UPDATE command you used to update the address and salary.

$6)$Use the ALTER TABLE command to add a CHECK constraint to the EMPLOYEE table called salary$\_$check to restrict the salary so that it is in the range $20000$ to $100000.$ Test the constraint by updating the salary of Mr$.$ James E$.$ Borg to $101000$ using the UPDATE command. $(1$ point$)$

$$Insert here the ALTER TABLE command you used to create the above CHECK constraint. Also, the UPDATE command you used to test this constraint and a screen shot showing the error message that will be displayed.

$7)$Write a row$-$level trigger that will increase the salary of every employee that is switched to department $1$ by $10\%.$ Test it by switching the employee Joyce English from department $5($her current department$)$ to department $1.(2$ points$)$

Insert here the CREATE OR REPLACE TRIGGER command you used to create the above trigger.

$8)$Write a stored procedure called Vehicle$\_$Details$($LN IN VARCHAR$)$ that has one input parameter LN $($which stores the license number$)$ as indicated and when called, will display details about the vehicle including model, make, year, and the manager who is currently using it$.$ The following is an example of how to call the procedure and the expected output:$(2\mathrm{.}5$ points$)$

$$EXEC$$ Vehicle$\_$Details$(\text{'}$TEXAS ABC$-739\text{'})$;$$

First Name$=$Franklin

Last Name$=$Wong

Model$=$Mustang$$

Make$=$Ford$$

Year$=02$

Insert here the CREATE OR REPLACE PROCEDURE command you used to create the above procedure. Insert here a sample run of the procedure as shown above.

$9)$Write a function called Proj$\_$Count$($Ssn char$)$ that takes as input the social security number of some employee, and returns the number of projects the employee works on$.$ Then, write an anonymous block to test this function by using for example the social security number $333445555.$ In this case, the anonymous block should display the following message:

This employee works on $4$ projects$$

Submit the function and the anonymous block. Also submit a screen shot of testing the function as indicated above.