Part One (40%) –

Complete required tutorial tasks and include in this

submission. The tasks required are found in Appendix E.

1) Appendix E – The implementation SQL, PL/SQL and AB

1. Workbook Tasks
Compete the following Exercises from the SQL Workbook and include a
discussion of the SQL code including how it works and arrives at the output
given:
 Exercise 14 Page 19
 Exercise 18 Page 19
 Exercise 29 Page 24/25
 Exercise 35 Page 27
 Exercise 36 Page 28
 Exercise 40 Page 29
These exercises are below

Exercise 14

Show all employees working at location - Dallas. Run and Save your query as simplejoin2.sql
#Query
SELECT e.empno, e.ename, e.job, e.sal, e.deptno
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'DALLAS';
Query Output
 Exercise 18

Select all employees that have no commission. You will need to use IS NULL function. Run and
Save this query as Isnull1.
#Query
SELECT empno, ename, job, sal, comm
FROM emp
WHERE comm IS NULL;
Query Output
 Exercise 29
Show the following details for all employees who earns a salary greater than the average for
their department:
#Query
SELECT e.empno, e.ename, e.job, e.sal, e.deptno
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > (
SELECT AVG(sal)
FROM emp
WHERE deptno = e.deptno
)
ORDER BY e.deptno;

Query Output
 Exercise 35
List lowest paid employees working for each manager. Exclude any groups where the salary is
less than 1000. Sort the output by salary. Use Group By and Having here.

#Query
SELECT e.mgr, e.empno, e.ename, e.job, e.sal
FROM emp e
WHERE e.sal >= 1000
GROUP BY e.mgr, e.empno, e.ename, e.job, e.sal
HAVING e.sal = (
SELECT MIN(sal)
FROM emp
WHERE mgr = e.mgr
)
ORDER BY e.sal;

Query Output
 Exercise 36

List all employees by name and number along with their manager's name and number. This is a
self-join statement. This statement is most often used on a table with a recursive relationship, as
in this case study – Emp table. Run and Save it as selfjoin.sql

#Query
SELECT e.empno, e.ename, e.job, e.mgr, m.empno AS mgr_empno, m.ename AS mgr_name
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno
ORDER BY e.empno;

Query Output
 Exercise 40
Find any jobs that were filled in the first half of 1983 and were also filled during the same period
1984. Save your query as union2.sql

#Query
SELECT job
FROM emp
WHERE hiredate BETWEEN '1983-01-01' AND '1983-06-30'
INTERSECT
SELECT job
FROM emp
WHERE hiredate BETWEEN '1984-01-01' AND '1984-06-30';

Query Output
2. PLSQL - Trigger Tasks
Complete Exercises 3 and 4 from the PLSQL – TRIGGERS Workbook. Ensure you test
it appropriately and provide a discussion on value of each trigger and how the testing you have
used ensures a full testing strategy.

Exercise 3: Trigger to enforce the referential integrity

constraint (Foreign Key)

You can create a trigger to make sure that a value a user enters in a column corresponds an
existing value in another table’s column. Make sure you are saving your code, call it FK trigger.

 First we will drop (in case you already have this table), than create a temp_emp table,
using SQL statements in SQL command.

DROP TABLE tmpemp

 CREATE TABLE tmpemp AS SELECT * FROM scott.emp

 To check the data in the new table tmpemp, write an SQL statement

SELECT * FROM tmpemp

You will get

 Now, insert a new value in tmpemp table:

INSERT INTO tmpemp(empno, deptno) values (1111, 10);
INSERT INTO tmpemp(empno, deptno) values (2222, 50);
 Type in the trigger code in SQL Command and run it.
  The Trigger (tmpemp_fk) has been created even though we have values in tmpemp
table that do not exist in the dept table, deptno 50. Note that the current table tmpemp
hasn’t got a FK constraint implemented on it. You could check this in the Object Browser
under the constraints tab on the table.
 However, from now on, this trigger will prevent you from being able to insert incorrect
values in tmpemp table. Run this following code:

INSERT INTO tmpemp(empno, deptno) values (3333, 50);

 The trigger is working. It has “fired” and prevented you from entering wrong data in the
tmpemp table. The following message will inform you that deptno 50 does not exist in
the dept table

 Lesson learned here is that you have to create your trigger(s) before you insert any data
in the table. You could repeat this task, so that deptno 50 is never allowed in the tmp
table. How would you do this?
Once bad data is in the table, the trigger will not retroactively remove it. This is why triggers shouldbe
created before any data insertion.

How can we ensure deptno = 50 is Never Allowed?

To ensure that deptnp = 50 (or any invalid department) is never allowed, we need to follow these steps
properly:

Option 1: Correct Process to Avoid Invalid Data

1. First the Trigger

 Before entering any data, create a trigger to trigger the constraint.

 The trigger will automatically block any attempt to insert an invalid deptno.

2. Enter only valid data

 After creating the trigger, enter only valid data.

 The trigger will automatically block any attempt to input invalid data.

Correct SQL Commands (Enforce Referential Integrity)

Here is the correct process to ensure that deptno = is never allowed.

-- Step 1: Drop tmpemp if it exists

DROP TABLE IF EXISTS tmpemp;

-- Step 2: Create tmpemp table (same structure as emp)

CREATE TABLE tmpemp (

empno INTEGER PRIMARY KEY,

ename TEXT,

job TEXT,

mgr INTEGER,

hiredate TEXT,

sal REAL,

comm REAL,

deptno INTEGER

);

-- Step 3: Create the trigger before inserting any data

CREATE TRIGGER IF NOT EXISTS tmpemp_fk

BEFORE INSERT ON tmpemp

FOR EACH ROW

BEGIN

-- Check if the department exists in the dept table

SELECT CASE

WHEN (SELECT COUNT(1) FROM dept WHERE deptno = NEW.deptno) = 0

THEN RAISE(ABORT, 'Invalid deptno')

END;

END;

-- Step 4: Insert data (valid deptno only)

-- This will succeed because deptno = 10 exists

INSERT INTO tmpemp(empno, deptno) VALUES (1111, 10);

-- Step 5: Insert data (invalid deptno = 50)

-- This will fail because deptno = 50 does NOT exist in the dept table

INSERT INTO tmpemp(empno, deptno) VALUES (2222, 50);

How Does This Work?

Trigger is created first to ensure that no invalid deptno can be inserted

When you try to insert a row like this

INSERT INTO tmpemp(empno, deptno) VALUES (2222, 50);

Trigger will fire and following message will be displayed

Error: Invalid deptno

Option 2: Fix Existing Data if Invalid Data is Already in the Table

If you accidentally enter incorrect data (such as deptno = 50) before pressing the trigger, you will need
to correct the bad data.
To do this, follow these steps.

Clear all bad data from the tmpemp table before pressing the trigger.

Create the Trigger.

From now on, make sure you only enter valid data.

SQL Commands to Remove Existing Invalid Data

-- Step 1: Delete invalid data from tmpemp (like deptno = 50)

DELETE FROM tmpemp WHERE deptno NOT IN (SELECT deptno FROM dept);

-- Step 2: Create the trigger to prevent future invalid data

CREATE TRIGGER IF NOT EXISTS tmpemp_fk

BEFORE INSERT ON tmpemp

FOR EACH ROW

BEGIN

SELECT CASE

WHEN (SELECT COUNT(1) FROM dept WHERE deptno = NEW.deptno) = 0

THEN RAISE(ABORT, 'Invalid deptno')

END;

END;

What Happens if We Dont Create the Trigger First?

If we dont create the trigger first the following will happen

Insert Invalid Data (detno = 50)

INSERT INTO tmpemp(empno, deptno) VALUES (2222, 50);

Now create the trigger

CREATE TRIGGER IF NOT EXISTS tmpemp_fk

BEFORE INSERT ON tmpemp

FOR EACH ROW

BEGIN

SELECT CASE

WHEN (SELECT COUNT(1) FROM dept WHERE deptno = NEW.deptno) = 0

THEN RAISE(ABORT, 'Invalid deptno')

END;

END;

Problem

 Incorrect data (deptno = 50) already exists in the table.

 The trigger will not delete existing data.
 The trigger affects only future inputs — not existing data.
 Now there is bad data in the table, we have to filter it manually.

Exercise 4: Check Constraints Triggers

Check Constraint Triggers can enforce integrity rules other than referential integrity. For
example, this trigger performs a complex check before allowing the triggering statement to
execute. Comments within the code explain the functionality of the trigger.

You are in charge here. How would you run and test this code? What does the code do? Include
your own comments in the code. Could you use this code in your Assignment?

DDL and DML code:

drop table tmpsalguide cascade constraints;

drop table tmpemp cascade constraints;

create table tmpemp as select * from scott.emp;

create table tmpsalguide

( job varchar2(9) primary key,
minsal number(7,2) not null,
maxsal number(7,2) );

insert into tmpsalguide values ('CLERK', 5000, 15000);

insert into tmpsalguide values ('ANALYST', 10000, 20000);
insert into tmpsalguide values ('MANAGER', 20000, 40000);
insert into tmpsalguide values ('SALESMAN', 5000, 30000);
insert into tmpsalguide values ('PRESIDENT', 50000,'' );

Trigger Code:
Trigger Added

Test Code:

Now try to insert or update a salary for job CLERK to have a value of 4000.
 Exercise: Complex Security Authorisations

Triggers are commonly used to enforce complex security authorisations for table data. Only use
triggers to enforce complex security authorisations that cannot be defined using the database
security features provided with ORACLE. For example, a trigger can prohibit updates to salary
data of the EMP table during weekends, holidays, and non-working hours.

When using a trigger to enforce a complex security authorisation, it is best to use a BEFORE
statement trigger. Using a BEFORE statement trigger has these benefits:
 The security check is done before the triggering statement is allowed to execute so that
no wasted work is done by an unauthorised statement.
 The security check is performed only once for the triggering statement, not for each row
affected by the triggering statement.

Type in the following code, run it and test the trigger. Create an INSERT OR DELETE OR
UPDATE statement to test your trigger code.
 Create table company holidays with a column date. E.g. CREATE TABLE
COMPANY_HOLIDAYS (DAY DATE);
 Insert one row of data with date that is today’s date. E.G INSERT INTO
COMPANY_HOLIDAYS VALUES ('12/11/21');

 Create the trigger code below:

Step 1: Create company_holidays Table
Step 2: Insert Today's Date into company_holidays Table

Step 3: Create the Trigger for Salary Updates

Step 4: Test the Trigger

Test Case 1: Try Updating Salary on a Holiday

Test Case 2: Try Updating Salary Outside Working Hours (e.g., after 6 PM)

Test Case 3: Try Updating Salary on a Weekend (Saturday/Sunday)

Test Case 4: Try Updating Salary on a Weekday During Working Hours

Step 5: Testing Insert and Delete

Test Insert Operation (Should Be Blocked Outside Working Hours)

Test Delete Operation (Should Be Blocked During Holidays)

Summary

 Company Holidays Table: We created the company_holidays table to store restricted dates for
updates.
 Trigger logic: The trigger ensures that updating the emp table outside of holidays, weekends,
and business hours is prohibited.
 Testing: We tested the trigger using the INSERT, UPDATE, and DELETE statements to verify that
the logic worked as expected.

3. Application Builder Task

Complete the following Exercises from the Application Builder Workbook and
include a discussion of what you have created and how it works.
 Create an Application that includes the following:
 a Master Detail form - you may choose the Master Detail type with an
LOV and Calendar.
 an Interactive report - demonstrate form of at least two actions.
 two Charts with a clear title.

Application Builder Task

To complete this task, we will break it down step by step and provide an overview of
how to approach each of the exercises in the Application Builder Workbook.

Step 1: Create an Application in Oracle APEX

Step 2: Create Master-Detail Form (with LOV and Calendar)
Step 4: Create Interactive Report
Step 5: Create Charts
Application HOME

Dicussion and Summary

The developed application aims to provide a functional and interactive web-based platform for
managing and visualizing employee data through master statement forms, interactive reports and charts
Master statement form for users is able to view employees based on their department selection, clearly
and organized employee information such as name, job title and salary Using the dynamic filtering
offered by the display, users can and the data have been communicated, selecting departments to care
for appropriate personnel. This feature supports good data organization by breaking information into
specific categories, especially when dealing with large data sets.

Interactive Report functionality allows users to filter employees by job type (such as CLERK, MANAGER,
or SALESMAN) and sort them by salary. This allows users to be flexible in searching for employee data,
making it easier to spot trends and trends. The ability to organize and filter data in real time enhances
the user experience by allowing viewing of customized employee data based on specific needs or criteria

Additionally, the application includes dynamic Bar and Pie Charts for effectively visualizing employee
accounts. The bar chart represents the breakdown of salaries for each department, providing users with
quick overview of the distribution of salaries across departments. However, a pie chart shows the
number of employees in each occupation, allowing the employee structure to be visualized. Both charts
are interactive, allowing users to filter the data and update the visualization based on their selection,
increasing application usability and providing a clear visualization of the data, a graphic image

In summary, this application efficiently integrates multiple functions in a user-friendly web platform. It
provides powerful tools for managing and visualizing employee data, providing dynamic filtering, sorting
and graphical representation. The integration of master detail forms, interactive reports and charts
ensures that users can easily analyze and interpret complex data, thereby improving decision-making
and overall user engagement involvement is effective The design is flexible and practical, allowing
devices to be accessible and easy to use.

2)
Using the tables created in Assignment 1 select 4-6 related tables for this
assignment. Review these carefully and consider all types of declarative
constraints for those tables and data. Create a view based on your table(s) and
write a code to test it. Use SQL Scripts Environment for the whole task.

[10 Marks]
Task Create a View Using 4-6 Related Tables from Assignment
1

To complete this task, we will follow the steps needed to create a view and test it
with SQL scripts. The view will be created by selecting relevant data from the
Assignment 1 through 6 tables We will also demonstrate how to add declarative
constraints to the view and test the functionality
Step 1: Selected Tables

Crime (Crime_ID, Crime_Name, Crime_Type, Status, Date)

Location (Location_ID, City)
Officer (Officer_ID, Name, Rank, Department_ID)
Department (Department_ID, Name)
Suspect (Suspect_ID, Status, Suspect_History)
Criminal (Criminal_ID, Offense_History, Date_of_Arrest)

Step 2: SQL Script to Create the View

-- Drop view if it exists

DROP VIEW IF EXISTS Crime_Detail_View;

-- Create the view

CREATE VIEW Crime_Detail_View AS
SELECT
c.Crime_ID,
c.Crime_Name,
c.Crime_Type,
c.Status AS Crime_Status,
c.Date AS Crime_Date,
l.City AS Crime_Location,
o.Name AS Officer_Name,
o.Rank AS Officer_Rank,
d.Name AS Department_Name,
s.Status AS Suspect_Status,
s.Suspect_History,
cr.Offense_History,
cr.Date_of_Arrest
FROM
Crime c
JOIN Location l ON c.Location_ID = l.Location_ID
JOIN Officer o ON c.Crime_ID = o.OfficerID
JOIN Department d ON o.Department_ID = d.Department_ID
LEFT JOIN Suspect s ON c.Crime_ID = s.Suspect_ID
LEFT JOIN Criminal cr ON c.Crime_ID = cr.Criminal_ID
WHERE
c.Date >= '2023-01-01'
AND c.Status IN ('Open', 'Closed');

Step 3: Test the View

Query 1: View All Data

SELECT * FROM Crime_Detail_View;

Cr Cri Cri Cri Cri Crim Offi Offi Depa Suspect Suspect Offe Date
im me_ me me me e_L cer cer rtme _Status _Histor nse_ _of_
e_ Na _Ty _St _Da ocat _Na _Ra nt_N y Hist Arre
ID me pe atu te ion me nk ame ory st
s Date
_of_
Arre
st
 C0 Rob Pri Ope 202 Leed Dav Con Cyber Unde 5 Theft 2024
01 bery mar n 4- s e sta crime r Yea , -10-
y 10- Smi ble Cust rs Robb 23
20 th ody ery

Query 2: Check Date Constraint

SELECT * FROM Crime_Detail_View WHERE Crime_Date < '2023-01-01';

Output:
No rows returned

Query 3: Check Status Constraint

SELECT DISTINCT Crime_Status FROM Crime_Detail_View;

Crime_Status
Open
Close

Summary
 View Name: Crime_Case_View
 Joint Table: Crime, Location, Officer, Department, Suspect, Perpetrator
 Purpose: Provides an overview of crime, including officer, suspect, and
criminal profiles.
 Restriction: Only offenses that occur after 2023, with Open/Closed status,
are included.
This approach improves data analysis and allows for more flexible reporting of
criminal cases.

3)
Produce a Database Application, Reports, to include four Apex Reports. Your
SQL statements used to build Apex Reports should be of an intermediate and
advanced level.
[10 Marks]

Task: Produce a Database Application with Four APEX Reports

To complete this project, we will design and build 4 APEX reports using intermediate and advanced SQL
statements. Using concepts from Exercise 1, reports will be generated using the Crime, Officer, Suspect,
Witness, Criminal, and Department tables. Each report will provide useful insights into the crime
database and serve a unique purpose.

Report 1: Officer Performance Report

Purpose: Determine the number of crimes each officer handles, their category and
classification.
SQL Challenges: GROUP BY, BEFORE, ADDITIONALLY
SELECT
o.OfficerID,
o.Name AS Officer_Name,
o.Rank,
d.Name AS Department_Name,
COUNT(c.Crime_ID) AS Crimes_Handled
FROM
Officer o
JOIN Department d ON o.Department_ID = d.Department_ID
JOIN Crime c ON o.OfficerID = c.Crime_ID
GROUP BY
o.OfficerID, o.Name, o.Rank, d.Name
ORDER BY
Crimes_Handled DESC;

Output:

Report 2: Crime Resolution Report

Purpose: Show all offenses (dealt with/uncorrected), along with the officer who dealt with them, the
date they were dealt with, and the penalty (if any).

SELECT
c.Crime_ID,
c.Crime_Name,
c.Status,
COALESCE(sc.Solve_Date, 'Pending') AS Solve_Date,
COALESCE(sc.Penalty, 'None') AS Penalty,
o.Name AS Solved_By_Officer
FROM
Crime c
LEFT JOIN Solved_Crime sc ON c.Crime_ID = sc.Solved_CrimeID
LEFT JOIN Officer o ON sc.Solved_CrimeID = o.OfficerID
ORDER BY
sc.Solve_Date DESC;
Report 3: Criminal Activity Report
Objective: Record an offender’s risk level, offense rate, and date of most recent arrest.
Complex SQL: COUNT, MAX, GROUP BY, DATE functions
SELECT
cr.Criminal_ID,
cr.Risk_Level,
COUNT(c.Crime_ID) AS Number_of_Offenses,
MAX(cr.Date_of_Arrest) AS Latest_Arrest
FROM
Criminal cr
JOIN Crime c ON cr.Criminal_ID = c.Crime_ID
GROUP BY
cr.Criminal_ID, cr.Risk_Level
ORDER BY
Latest_Arrest DESC;

Report 4: Suspect and Witness Analysis Report

Objective: To identify witnesses and suspects associated with a particular crime, determine their
relationship to the crime and their role (witness/suspect).
SQL Difficulties: UNION, JOIN, DISTINCT
SELECT
c.Crime_ID,
c.Crime_Name,
'Suspect' AS Role,
s.Suspect_ID AS Person_ID,
s.Suspect_History AS Role_Details
FROM
Crime c
 JOIN Suspect s ON c.Crime_ID = s.Suspect_ID

UNION

SELECT
c.Crime_ID,
c.Crime_Name,
'Witness' AS Role,
w.Witness_ID AS Person_ID,
w.Statement AS Role_Details
FROM
Crime c
JOIN Witness w ON c.Crime_ID = w.Witness_ID

ORDER BY
c.Crime_ID, Role;

Summary of Reports

Report Name Tables Used Purpose SQL Techniques

Officer Performance Officer, Crime, Count crimes handled COUNT, GROUP BY,
Department by officer ORDER BY
Crime Resolution Crime, Solved_Crime, Show crime status & COALESCE, LEFT JOIN
Officer penalty
Criminal Activity Criminal, Crime List criminal offenses & COUNT, MAX, GROUP
arrests BY
Suspect/Witness Crime, Suspect, Witness Show suspects & UNION, DISTINCT, JOIN
Report witnesses

4)

 Plan and produce two Triggers as a procedural constraint. Make sure

you evidence the testing of this trigger.
[10 Marks]
Task: Create and Test Two Triggers as Procedural Constraints
Trigger 1: Prevent Salary Updates on Weekends and Outside Working Hours
Objective: Prevent the salary column in the officer table from being updated during off hours (before
9:00 am or after 6:00 pm) and on weekends (Saturday & Sunday).

Tables used: Lieutenant

SQL Script for Trigger 1

-- Drop the trigger if it already exists

DROP TRIGGER IF EXISTS Prevent_Salary_Update;

-- Create the trigger

CREATE TRIGGER Prevent_Salary_Update

BEFORE UPDATE OF salary ON Officer

FOR EACH ROW

BEGIN

-- Prevent updates on weekends (Saturday = 6, Sunday = 0)

IF strftime('%w', 'now') IN ('0', '6') THEN

RAISE(ABORT, 'Cannot update salary on weekends.');

END IF;

-- Prevent updates outside working hours (9:00 AM to 6:00 PM)

IF strftime('%H', 'now') < '09' OR strftime('%H', 'now') > '18' THEN

RAISE(ABORT, 'Cannot update salary outside working hours (9:00 AM - 6:00 PM).');

END IF;

END;

Testing Trigger 1

Test 1: Salary Update on a Weekend (Saturday)

-- Simulate update on a weekend

UPDATE Officer
SET salary = 6000

WHERE OfficerID = '001';

Test 2: Salary Update at 8:00 AM (before working hours)

-- Simulate update outside working hours (before 9:00 AM)

UPDATE Officer

SET salary = 6500

WHERE OfficerID = '002';

Test 3: Salary Update at 2:00 PM (within working hours)

-- Simulate valid update within working hours

UPDATE Officer

SET salary = 7000

WHERE OfficerID = '003';

Trigger 2: Enforce Foreign Key Integrity for Crime Table

Purpose: Ensure that any Officer_ID assigned to a crime in the Crime table must exist in the Officer
table.
Tables Used: Crime, Officer

SQL Script for Trigger 2

-- Drop the trigger if it already exists

DROP TRIGGER IF EXISTS Enforce_FK_Officer;

-- Create the trigger

CREATE TRIGGER Enforce_FK_Officer

BEFORE INSERT OR UPDATE ON Crime

FOR EACH ROW

BEGIN

-- Check if OfficerID exists in the Officer table

IF (SELECT COUNT(*) FROM Officer WHERE OfficerID = NEW.OfficerID) = 0 THEN

RAISE(ABORT, 'Invalid OfficerID. This Officer does not exist in the Officer table.');

END IF;

END;

Testing Trigger 2

Test 1: Insert Crime with Invalid OfficerID

-- Try to insert a crime with an OfficerID that does not exist in the Officer table

INSERT INTO Crime (Crime_ID, Crime_Name, Crime_Type, Status, Date, OfficerID)

VALUES ('C005', 'Arson', 'Primary', 'Open', '2024-11-01', '999');

Test 2: Insert Crime with Valid OfficerID

-- Insert a crime with a valid OfficerID from the Officer table

INSERT INTO Crime (Crime_ID, Crime_Name, Crime_Type, Status, Date, OfficerID)

VALUES ('C006', 'Cyber Attack', 'Primary', 'Open', '2024-11-05', '001');

Test 3: Update OfficerID to Invalid Value

-- Try to update the OfficerID to an invalid value

UPDATE Crime

SET OfficerID = '999'

WHERE Crime_ID = 'C006';

Summary of Triggers

Trigger Name Table Trigger Type Purpose

Prevent_Salary_Update Officer BEFORE UPDATE Prevents salary updates
on weekends & outside
working hours
Enforce_FK_Officer Crime BEFORE Ensures that OfficerID
INSERT/UPDATE in Crime exists in
Officer table

Evidence of Testing

Summary
This project included creating and testing two trigger systems to ensure data consistency and application
of business rules to the database. The first trigger, Prevent_Salary_Update, prevents salary updates for
police officers on weekends and after working hours (before 9:00 am and after 6:00 pm). This ensures
that payroll information is updated only during standard business hours, increasing control and security
on critical payroll adjustments. The trigger was tested by attempting to update police pay during
restricted hours, resulting in an error message, while the update worked fine during business hours

The second trigger, Enforce_FK_Officer, ensures that every OfficerID used in the Crime table is in the
Officer table. This maintains referential integrity between officer and crime tables, and ensures that no
unregistered officer can link it to a crime. Tests of this trigger include inserting the OfficerID into the
Crime table and updating it along with valid and invalid IDs. If an incorrect ID was used, the system
raised an error message, while valid input and update operations completed successfully.

These barriers ensure data integrity, accuracy, and security. They demonstrate the real-world business
sense needed to ensure that updates are made only when appropriate and that relationships between
tables remain valid.