STOCK

$\backslash$table$[[$STOCK$\_$ID$,$STOCK$\_$TYPE,STOCK$\_$MODEL,MANUFACTURER$],[12345,$Desktop,Proline K$100,$Acer$],[54321,$Laptop,J$55,$Mecer$],[78945,$Server,HP$9000,$Samsung$],[98754,$Laptop,Aspire $450,$HP$],[55311,$Notebook,L$920,$Lenovo$]$

CUSTOMER

$\backslash$table$[[$CUST$\_$ID$,$CUST$\_$FNAMECUST SNAME,CUST$\_$ADDRESS,CUST$\_$CONTACT,$],[$C$115,$Jeff,Willis,$3$ Main Road,$0821253659],[$C$116,$Andre,Watson,$13$ Cape Road,$0769658547],[$C$117,$Wallis,Smith,$3$ Mountain Road,$0863256574],[$C$118,$Alex,Hanson,$8$ Circle Road,$0762356587],[$C$119,$Bob,Bitterhout,$15$ Main Road,$0821235258],[$C$120,$Thando,Zolani,$88$ Summer Road,$0847541254],[$C$121,$Philip,Jackson,$3$ Long Road,$0745556658],[$C$122,$Sarah,Jones,$7$ Sea Road,$0814745745]]$

REPAIRS

$\backslash$table$[[$REPAIR$\_$ID$,$REPAIR$\_$WORK,REPAIR$\_$DATE,REPAIR$\_$HOURS$],[1,$HD defragmentation,$15/$JUL$/23,2],[2,\backslash$table$[[$RAM module replaced Tme taken to$],[$diagnose problem$]],18/$JUL$23,3],[3,$Battery Replaced,$19/$JUL$/23,2],[4,$OS Upgraded,$20/$JUL$/23,2],[5,$Faulty hard disk replaced,$25/$JUL$/23,3]]$

SALES

$\backslash$table$[[$SALES NUM,SALES$\_$DATE,SALES$\_$AMT,STOCK$\_$ID$,$CUST$\_$ID$,$REPAIR$\_$ID$],[101,271$JUL$/23,$R $1700,98754,$C$121,3],[102,20/$JUL$123,$R $1500,12345,$C$120,4],[103,23/$JUL$/23,$R $1650,55311,$C$119,5],[104,17/$JUL$/23,$R $1300,54321,$C$117,2],[105,19/$JUL$/23,$R $1900,12345,$C$122,1]]$

QUESTION $1$

CREATE AND POPULATE THE TAABLES

Question $2$

$($Marks: $5)$

TASK: Query Time taken to complete Repairs.

Using SQL$.$ only, display the combined customer name, the repar work, repair date and repair hours. In your query only display the results that had a repar time less than $3$ hours

Question $3$

$($Marks: $5)$

TASK: Query Sales Made

Using PL$/$SQL$,$ display the Customer ID$,$ Slock Type and the Sales Amount. In your query only display the results where the sales amount is less th

Question $4$

TASK: Query Calculate Commission

$($Marks: $15)$

IT Gear Dealer has a policy that states that, for any individual sale made, the employee who completed the repair work receives a certain commission.

Using PL$/$SQL$,$ display the Customer ID$,$ Stock ID$,$ Sales Number, the Repair ID$,$ the Sales Amount, and the commission payable which will be calculated as follows.

For an individual sale of R$999$ or less, apply a $5\%$ commission.

An individual sale between R$1000$ and R$1499,$ apply a $10\%$ commission

An individual sale of R$1500$ or more, apply a $15\%$ commission.

Question $5$

$($Marks: $5)$

TASK: Create View

Create a view called Repair$\_$View that will display the combined Customer Name, Stock ID$,$ Repair Date and the Repair Work done. In your query also include the statement to run the view.

Question $6$

$($Marks: $10)$

TASK: Create Trigger

Create a Trigger called Sales$\_$Entry that vill prevent a user from entering a sales amount that is less than zero. In your query include the code to test the trigger.

Question $7$

$($Marks: $15)$

TASK: Create Stored Procedure

Create a stored procedure called sp$\_$customer$\_$details that accepts sales and display the customer id$,$ customer first name, sumame and their contact number.

In your solution display the data for any custorner and provide the code to execute the procedure with relevant exception handling and relevant code comments.

Question $8$

$($Marks: $15)$

TASK: Create Function

Create a function called fn$\_$IT$\_$Gear that would be meaningful and relevant for this database. Your function should accept at least $1$ input parameter and display at least $4$ relevant pieces of information.

In your solution.

display the data and provide the code to execute the function.

use relevant exception handling and

provide relevant code comments

Question $10$

$($Marks: $10)$

TASK: Database Security Mini$-$ Presentation

At the moment, the management requires a mini presentation on how you would acheve data security. Provide at least five $(5)$ ways you would ensure database secunty$,$ referring to the case study in your answer