I need only Professional people on Database, In this project, you will be asked to enhance the COMPANY schema that was given to you in class 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)$ Use the enhanced COMPANY database to write and test the following SQL queries: $(4$ x $1=4$ points$)3\mathrm{.}1$ Find the license number, model and make of all available vehicles $($those that are not assigned to any employees$).$ Hint: use the condition Assignee IS NULL in the where clause.Insert here the query as expressed in SQL and the output of the query. $3\mathrm{.}2$ Find the first name and last name of the employees who have been assigned vehicles.Insert here the query as expressed in SQL and the output of the query. $3\mathrm{.}3$ Find the first name and last name of the employees who have not been assigned vehicles.Insert here the query as expressed in SQL and the output of the query. $3\mathrm{.}4$ Find the license number, model, make, and year of all unavailable vehicles. Hint: use the condition Assignee IS NOT NULL in the where clause.Insert here the query as expressed in SQL and the output of the query$\mathrm{.}4)4\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. $4\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. $5)$ 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. $6)$ 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. $7)$ 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. $8)$ 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.