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 the software tools provided by a DBMS are used in practice.

1. Why a DBMS is needed – limitations of file‑based systems

2. Core DBMS Features that Address Those Limitations

• Data dictionary & logical schema – stores metadata (tables, columns, constraints, relationships). Enables data independence.
• Integrity constraints – PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.
• Security – authentication (password, LDAP, Kerberos), authorisation (roles, privileges, row‑level policies).
• Transaction management – ACID properties:
  • Atomicity – all or nothing.
  • Consistency – constraints remain valid.
  • Isolation – concurrent transactions do not interfere (see isolation levels).
  • Durability – committed changes survive failures.
• Concurrency control – lock‑based (shared/exclusive) and Multi‑Version Concurrency Control (MVCC).
• Backup & recovery – logical (SQL dump) vs. physical (file‑system copy) backups; incremental & differential options.

3. Developer Interface & Query Processor

Most DBMSs ship with a graphical or command‑line IDE (e.g. MySQL Workbench, pgAdmin, Oracle SQL Developer). Typical tasks:

• Write, test and debug SQL statements.
• Explore the data dictionary (tables, columns, constraints).
• Run scripts and batch jobs.
• View execution plans with EXPLAIN (or equivalent).

Query‑processor pipeline (simplified)

Parser  →  Optimiser  →  Executor
   |          |            |
Syntax   Choose best   Perform I/O,
checking   access path  apply operators,
resolve    (indexes,   return rows
names)     joins)       

4. Data Definition Language (DDL)

DDL Checklist

• CREATE SCHEMA – group related objects.
• CREATE TABLE – define columns, data types, and constraints.
• ALTER TABLE – add/modify/drop columns, constraints, indexes.
• DROP TABLE / SCHEMA – remove objects.
• CREATE INDEX – improve search performance.

Example – Creating a table with full integrity

CREATE SCHEMA Retail AUTHORIZATION dbo;

CREATE TABLE Retail.Employee (
    EmpID      INT PRIMARY KEY,
    Name       VARCHAR(50) NOT NULL,
    DeptID     INT NOT NULL,
    Salary     DECIMAL(10,2) CHECK (Salary >= 0),
    CONSTRAINT fk_dept FOREIGN KEY (DeptID)
        REFERENCES Retail.Department(DeptID)
);

Other common DDL commands

• ALTER TABLE Employee ADD HireDate DATE;
• DROP INDEX idx_salary ON Employee;
• CREATE UNIQUE INDEX uq_emp_name ON Employee(Name);

5. Data Manipulation Language (DML)

Basic DML

INSERT INTO Retail.Employee (EmpID, Name, DeptID, Salary)
VALUES (101, 'Alice Brown', 3, 48000.00);

UPDATE Retail.Employee
SET Salary = Salary * 1.05
WHERE EmpID = 101;

DELETE FROM Retail.Employee
WHERE EmpID = 101;

SELECT – retrieval with all major clauses

SELECT e.Name,
       d.DeptName,
       e.Salary,
       COUNT(s.SaleID) AS SalesCount,
       SUM(s.Amount)   AS TotalSales
FROM   Retail.Employee e
JOIN   Retail.Department d ON e.DeptID = d.DeptID
LEFT   JOIN Retail.Sales s   ON e.EmpID = s.EmpID
WHERE  e.Salary > 50000
GROUP BY e.Name, d.DeptName, e.Salary
HAVING COUNT(s.SaleID) > 0
ORDER BY TotalSales DESC;

JOIN types (quick reference)

6. Transaction Management & Isolation Levels

-- Example of a money transfer (atomic operation)
BEGIN TRANSACTION;

UPDATE Retail.Account
SET Balance = Balance - 2000
WHERE AccNo = 'A123';

UPDATE Retail.Account
SET Balance = Balance + 2000
WHERE AccNo = 'B456';

COMMIT;   -- or ROLLBACK on error

Isolation levels (SQL standard)

• READ UNCOMMITTED – dirty reads possible.
• READ COMMITTED – prevents dirty reads (default in many DBMSs).
• REPEATABLE READ – prevents non‑repeatable reads; may allow phantom rows.
• SERIALIZABLE – full isolation; behaves as if transactions run sequentially.

Lost‑update scenario (illustration)

T1: READ Salary = 50 000
T2: READ Salary = 50 000
T1: UPDATE Salary = 55 000   (5 % raise)
T2: UPDATE Salary = 52 500   (5 % raise)   ← overwrites T1’s change

Using SELECT … FOR UPDATE or a higher isolation level prevents this.

7. Indexing

• Non‑clustered (secondary) index – separate structure, points to rows.
• Clustered index – determines the physical order of rows; a table can have only one.

Creating indexes

-- non‑clustered index on Salary
CREATE INDEX idx_emp_salary ON Retail.Employee(Salary);

-- clustered index on primary key (often created automatically)
CREATE CLUSTERED INDEX pk_emp ON Retail.Employee(EmpID);

Using EXPLAIN to verify index usage (MySQL example)

EXPLAIN SELECT * FROM Retail.Employee WHERE Salary > 60000;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | Employee | NULL       | range| idx_emp_salary| idx_emp_salary | 4 | NULL |  120 |   30.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------+

“type = range” and the presence of key = idx_emp_salary confirm the index is being used.

8. Views

• Virtual tables defined by a SELECT statement – no data stored (unless it’s a materialised view, which is optional in some DBMSs).
• Useful for:
  • Simplifying complex queries.
  • Encapsulating business logic.
  • Security – expose only required columns/rows.

Example – Secure view for HR

CREATE VIEW Retail.EmpDept AS
SELECT EmpID, Name, DeptID
FROM Retail.Employee;

Granting SELECT on EmpDept lets HR staff see names and departments without seeing salaries.

9. Stored Procedures & Functions

Procedure (performs actions, no return value)

CREATE PROCEDURE Retail.RaiseSalary (
    IN pDeptID INT,
    IN pPct    DECIMAL(5,2)
)
BEGIN
    UPDATE Retail.Employee
    SET Salary = Salary * (1 + pPct/100)
    WHERE DeptID = pDeptID;
END;

Function (returns a value, can be used in expressions)

CREATE FUNCTION Retail.NetSalary (pEmpID INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE base DECIMAL(10,2);
    SELECT Salary INTO base FROM Retail.Employee WHERE EmpID = pEmpID;
    RETURN base * 0.80;   -- example: 20% tax deduction
END;

10. Triggers

• Automatic code that fires in response to DML events.
• Can be defined BEFORE or AFTER the event, and for INSERT, UPDATE, or DELETE.
• Typical uses: auditing, cascading updates, enforcing complex business rules.

Example – Auditing salary changes (AFTER UPDATE)

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

11. Backup and Recovery Utilities

Backup types

• Logical backup – export of SQL statements (e.g., mysqldump, pg_dump).
• Physical backup – copy of data files, transaction logs, and control files.
• Full – entire database.
• Incremental – only changes since the last backup (logical: binlogs; physical: changed blocks).
• Differential – all changes since the last full backup.

Example commands (MySQL)

# Logical full backup
mysqldump -u admin -p --single-transaction Retail > Retail_full.sql

# Start a new binary log for incremental backup
mysqladmin -u admin -p flush-logs

# Copy the newly created binlog files (e.g., mysql-bin.000002) to backup storage

Recovery scenario

1. Restore the latest full backup.
2. Apply the most recent differential backup (if any).
3. Replay incremental logs up to the desired point‑in‑time.

12. Security and Access Control

Layers of security

• Authentication – verifies identity (password, LDAP, Kerberos, certificates).
• Authorisation – grants privileges via roles, object‑level rights, and optional row‑level security.

Role‑based example

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

Row‑level security (PostgreSQL syntax)

CREATE POLICY hr_salary_policy ON Retail.Employee
FOR SELECT
USING (DeptID = (SELECT DeptID FROM Retail.Department WHERE DeptName = CURRENT_USER));

ALTER TABLE Retail.Employee ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON Retail.Employee TO hr_role;

13. Reporting and Business Intelligence (BI) Tools

1. Define a parameterised query (e.g., monthly sales per region).
2. Design a report template – headings, grouping, charts, totals.
3. Schedule execution – daily/weekly jobs that run the query, render the report, and distribute via email or web portal.

Many DBMSs ship with basic reporting (MySQL Report, SQL Server Reporting Services) and integrate with external BI platforms such as Power BI, Tableau, or Qlik.

14. End‑to‑End Practical Example – Small Retail Business

CREATE TABLE Retail.Product (
    ProdID INT PRIMARY KEY,
    Name   VARCHAR(100) NOT NULL,
    Price  DECIMAL(8,2) CHECK (Price >= 0),
    Stock  INT CHECK (Stock >= 0)
);

INSERT INTO Retail.Product (ProdID, Name, Price, Stock)
VALUES (2001, 'Wireless Mouse', 25.99, 150);

BEGIN TRANSACTION;
INSERT INTO Retail.Sales (SaleID, ProdID, Qty, SaleDate)
VALUES (5001, 2001, 3, CURDATE());

UPDATE Retail.Product
SET Stock = Stock - 3
WHERE ProdID = 2001;
COMMIT;

CREATE INDEX idx_sales_date ON Retail.Sales (SaleDate);

CREATE ROLE sales_clerk;
GRANT SELECT, INSERT ON Retail.Sales TO sales_clerk;
GRANT SELECT ON Retail.Product TO sales_clerk;
GRANT sales_clerk TO bob_user;

/* optional row‑level policy */
CREATE POLICY sales_region_policy ON Retail.Sales
FOR SELECT USING (Region = CURRENT_USER);
ALTER TABLE Retail.Sales ENABLE ROW LEVEL SECURITY;

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

15. Relational Algebra Illustration

Designers often reason with relational algebra before writing SQL.

The equivalent SQL statement (shown earlier) is:

SELECT Name, Salary
FROM Retail.Employee
WHERE Dept = 'Finance' AND Salary > 50000;

16. Summary

The software tools supplied by a DBMS – data dictionary, DDL, DML, SQL query language, transaction control (ACID), concurrency mechanisms, indexing (clustered & non‑clustered), views, stored procedures, functions, triggers, backup/recovery utilities, layered security (authentication, authorisation, row‑level policies), and reporting/BI integration – work together to create robust, efficient, and secure data‑centric applications. Mastery of these tools enables students to design databases that meet real‑world business requirements while preserving data integrity, performance, and security.