GEIT 3341: Database Term Project Due Date: Due Date: On or Before Thursday May 12, 2022 To be done individually or in te

Post by **answerhappygod** » Fri May 20, 2022 12:06 pm

: Geit 3341 Database Term Project Due Date Due Date On Or Before Thursday May 12 2022 To Be Done Individually Or In Te 1 (40.93 KiB) Viewed 38 times

: Geit 3341 Database Term Project Due Date Due Date On Or Before Thursday May 12 2022 To Be Done Individually Or In Te 2 (45.37 KiB) Viewed 38 times

: Geit 3341 Database Term Project Due Date Due Date On Or Before Thursday May 12 2022 To Be Done Individually Or In Te 3 (30.74 KiB) Viewed 38 times

: Geit 3341 Database Term Project Due Date Due Date On Or Before Thursday May 12 2022 To Be Done Individually Or In Te 4 (54.56 KiB) Viewed 38 times

: Geit 3341 Database Term Project Due Date Due Date On Or Before Thursday May 12 2022 To Be Done Individually Or In Te 5 (16.22 KiB) Viewed 38 times

GEIT 3341: Database Term Project Due Date: Due Date: On or Before Thursday May 12, 2022 To be done individually or in teams of no more than 3 per team Student Section 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 undertined attribute(s) denotes primary key): 2 points) Data Type Attribute LicenseNumber VARCHAR(15) Model Make VARCHAR(20) VARCHAR(20) Year Assignee CHAR(2) 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) 1

Model Year Make Ford 02 05 LicenseNumber TEXAS ABC-739 TEXAS RSK-629 TEXAS TOR-515 TEXAS OPU-010 TEXAS FGT-111 TEXAS YHF-235 TEXAS TQY-689 Mustang XJS Sonata Camry Cavalier Assignee 333445555 888665555 987654321 null 07 04 Jaguar Hyundai Toyota Chevrolet Hyundai Toyota 08 09 06 null null Elantra Avalon 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: (4x1.4 points) 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 15 NULL in the where clause. Insert here the query as expressed in SQL and the output of the query. 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. 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. 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. 4) 2

4.1 Create a view called V1 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. (2 point) Insert here the CREATE VIEW command you used to create the above view and show the output of this view. 4.2 Create another view V2 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. That is the view should show the following information: (2 point) Social Security Number of Projects Total Hours 2 40.0 1 40.0 2 40.0 4 40.0 123456789 666884444 453453453 333445555 999887777 987987987 987654321 888665555 2 40.0 2 40.0 35.0 null Insert here the CREATE VIEW command you used to create the above view and show the output of this view. 3

5) Mr. Ahmad Jabbar has quit the company. Use the DELETE command to delete his records from the WORKS_ON and EMPLOYEE tables. (0.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. 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.5 points) Insert here the CREATE OR REPLACE TRIGGER command you used to create the above trigger 9) 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: (3 points)

EXEC Vehicle_Details(TEXAS ABC-739): 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. 5