Show understanding of how software tools found within a DBMS are used in practice

8.2 Database Management Systems (DBMS)

Objective

Show understanding of how software tools found within a DBMS are used in practice.

Overview of DBMS Software Tools

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Query Language (SQL)
• Transaction Management
• Indexing
• Views
• Stored Procedures & Functions
• Triggers
• Backup and Recovery Utilities
• Security and Access Control
• Reporting and Business Intelligence Tools

1. Data Definition Language (DDL)

DDL commands define the structure of database objects. Typical statements include CREATE, ALTER and DROP.

Example – creating a table for employee records:

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name \cdot ARCHAR(50),
Department \cdot ARCHAR(30),
Salary DECIMAL(10,2)
);

2. Data Manipulation Language (DML)

DML is used to insert, update, delete and retrieve data. The most common DML statements are INSERT, UPDATE, DELETE and SELECT.

Example – adding a new employee and giving a raise:

INSERT INTO Employee (EmpID, Name, Department, Salary)
VALUES (101, 'Alice Brown', 'Finance', 48000.00);
UPDATE Employee
SET Salary = Salary * 1.05
WHERE EmpID = 101;

3. Query Language (SQL)

SQL combines DML and query capabilities. It allows complex retrieval using WHERE, JOIN, GROUP BY, HA \cdot ING and ORDER BY.

Example – list finance staff earning more than $50,000, ordered by salary descending:

SELECT Name, Salary
FROM Employee
WHERE Department = 'Finance' AND Salary > 50000
ORDER BY Salary DESC;

4. Transaction Management

Transactions ensure a group of operations are treated as a single logical unit. The ACID properties guarantee reliability.

Typical commands:

• BEGIN TRANSACTION
• COMMIT – makes changes permanent
• ROLLBACK – undoes all changes in the current transaction

Example – transferring $2,000 from one account to another:

BEGIN TRANSACTION;
UPDATE Account
SET Balance = Balance - 2000
WHERE AccNo = 'A123';
UPDATE Account
SET Balance = Balance + 2000
WHERE AccNo = 'B456';
COMMIT;

5. Indexing

Indexes speed up data retrieval by providing a fast lookup structure, usually a B‑tree.

Example – creating an index on the Salary column:

CREATE INDEX idx_salary ON Employee (Salary);

Effect: a query such as SELECT * FROM Employee WHERE Salary > 60000 can be satisfied by scanning the index rather than the whole table.

6. Views

A view is a virtual table defined by a query. It can simplify complex queries, enforce security, and present data in a user‑friendly format.

Example – a view showing only employee names and departments:

CREATE \cdot IEW EmpDept AS
SELECT Name, Department
FROM Employee;

Users with limited privileges can be granted access to EmpDept without seeing salaries.

7. Stored Procedures & Functions

These are pre‑compiled SQL blocks stored in the DBMS. They encapsulate business logic, reduce network traffic and improve performance.

Example – a procedure that raises salaries for a given department by a percentage:

CREATE PROCEDURE RaiseSalary (
IN dept \cdot ARCHAR(30),
IN pct DECIMAL(4,2)
)
BEGIN
UPDATE Employee
SET Salary = Salary * (1 + pct/100)
WHERE Department = dept;
END;

8. Triggers

Triggers automatically execute in response to specific data‑modification events (INSERT, UPDATE, DELETE). They enforce integrity rules or audit changes.

Example – an audit trigger that records every salary change:

CREATE TRIGGER trgsalaryaudit
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
BEGIN
INSERT INTO SalaryAudit (EmpID, OldSalary, NewSalary, ChangeDate)
VALUES (OLD.EmpID, OLD.Salary, NEW.Salary, CURRENT_TIMESTAMP);
END;

9. Backup and Recovery Utilities

Regular backups protect against data loss. DBMS tools typically offer:

• Full backups – a complete copy of the database.
• Incremental/differential backups – only changes since the last backup.
• Point‑in‑time recovery – restoring the database to a specific moment.

Example command (MySQL syntax) for a logical backup:

mysqldump -u admin -p --single-transaction mydb > mydb_backup.sql

10. Security and Access Control

DBMSs manage user authentication and authorisation through roles, privileges and row‑level security.

Example – creating a role for HR staff and granting limited access:

CREATE ROLE hr_role;
GRANT SELECT, INSERT ON Employee TO hr_role;
GRANT EXECUTE ON PROCEDURE RaiseSalary TO hr_role;

Then assign the role to a user:

GRANT hrrole TO aliceuser;

11. Reporting and Business Intelligence Tools

Many DBMSs include built‑in reporting utilities or integrate with external BI platforms. They allow users to generate formatted reports, charts and dashboards directly from query results.

Typical workflow:

1. Define a parameterised SQL query (e.g., monthly sales per region).
2. Design a report template (header, footers, grouping).
3. Schedule the report to run automatically and distribute via email or web portal.

Practical Example: End‑to‑End Use of DBMS Tools

Consider a small retail business that needs to maintain product inventory, process sales, and generate monthly sales reports.

CREATE TABLE Product (ProdID INT PRIMARY KEY, Name \cdot ARCHAR(100), Price DECIMAL(8,2), Stock INT);

INSERT INTO Product \cdot ALUES (2001, 'Wireless Mouse', 25.99, 150);

BEGIN TRANSACTION;
INSERT INTO Sales (SaleID, ProdID, Qty, SaleDate) VALUES (5001, 2001, 3, CURDATE());
UPDATE Product SET Stock = Stock - 3 WHERE ProdID = 2001;
COMMIT;

CREATE INDEX idxsalesdate ON Sales (SaleDate);

CREATE ROLE sales_clerk;
GRANT SELECT, INSERT ON Sales TO sales_clerk;
GRANT SELECT ON Product TO sales_clerk;
GRANT salesclerk TO bobuser;

CREATE PROCEDURE MonthlySales (IN month INT, IN year INT)
BEGIN
SELECT p.Name, SUM(s.Qty) AS UnitsSold, SUM(s.Qty * p.Price) AS Revenue
FROM Sales s JOIN Product p ON s.ProdID = p.ProdID
WHERE MONTH(s.SaleDate)=month AND YEAR(s.SaleDate)=year
GROUP BY p.Name;
END;

Relational Algebra Illustration

Before translating to SQL, many designers reason using relational algebra. For example, to find employees in the Finance department earning more than $50,000:

\$\$

\sigma_{Department='Finance' \cdot Salary>50000}(Employee)

\$\$

The equivalent SQL query is shown earlier in the “Query Language” section.

Summary

The tools provided by a DBMS—DDL, DML, SQL, transaction control, indexing, views, stored procedures, triggers, backup/recovery, security, and reporting—work together to create robust, efficient, and secure data‑centric applications. Mastery of these tools enables developers to design databases that meet real‑world business requirements while maintaining data integrity and performance.