Unit 5: Security, Authorization, and Advanced Topics

Database Security, Authorization & Advanced Topics

Database Security

Database Security means protecting data from unauthorized access, misuse, or corruption.
Just like you lock your house to prevent thieves, databases also need digital locks.

Main Goals of Database Security

1. Confidentiality – Only authorized users can access data.
2. Integrity – Data should remain accurate and consistent.
3. Availability – Authorized users can access data when needed.

Key Security Components

Concept	Meaning	Example
Authentication	Verifying the identity of a user	Username & password
Authorization	Giving specific rights to users	Admin can modify, user can only view
Privileges	Permissions to perform actions	SELECT, INSERT, DELETE privileges

SQL Example:

CREATE USER jay IDENTIFIED BY '12345';
GRANT SELECT, INSERT ON Employee TO jay;

Common Security Threats

Threat	Description
SQL Injection	Hacker inserts malicious SQL code
Unauthorized Access	Access without permission
Data Theft or Leakage	Stealing confidential information
Virus or Malware	Damaging database files
Human Error	Accidental data deletion or update

Auditing, Access Controls, and Violation Handling

Auditing

Auditing means tracking and recording all database activities — who accessed what, when, and how.
It helps in detecting misuse or data breaches.

Think of it as a CCTV camera for your database.

Example:

• Who deleted a record?
• Who changed salary data last night?

---

Access Control

Access control defines who can do what in a database.

Type	Description	Example
Discretionary Access Control (DAC)	Access given by the data owner	HR allows only Managers to view payroll
Mandatory Access Control (MAC)	Access based on security level	Confidential vs. Public data
Role-Based Access Control (RBAC)	Access based on user’s job role	Admin, Accountant, Employee

Violation Handling

When unauthorized access or suspicious activity is detected, the system:

1. Logs the event
2. Alerts the admin
3. May temporarily lock the user or session

Data Integrity and Constraints

Data Integrity means maintaining accuracy, consistency, and reliability of data.
The system should never store wrong, duplicate, or incomplete data.

Types of Integrity:

Type	Meaning	Example
Entity Integrity	Each record must have a unique ID	Primary Key: EmpID
Referential Integrity	Foreign key must match existing record	Every DeptID must exist in Department table
Domain Integrity	Values must be valid and within range	Salary cannot be negative
User-defined Integrity	Business rules applied	Employee age ≥ 18

Constraints Used:

Constraint	Description
PRIMARY KEY – unique & not null	EmpID INT PRIMARY KEY
FOREIGN KEY – links tables	DeptID INT REFERENCES Department(DeptID)
NOT NULL – no empty value	Name VARCHAR(50) NOT NULL
CHECK – condition must be true	CHECK (Salary > 10000)
UNIQUE – no duplicate values	Email UNIQUE

Distributed Databases and Client-Server Architecture

Distributed Database

A distributed database is stored across multiple computers or locations, but acts as one single database. Example: A bank has branches in many cities. Each branch stores local customer data but can still access data from other branches when needed.

Advantages:

• Faster local access
• Data sharing across locations
• Improved reliability (if one server fails, others continue)

Challenges:

• Synchronizing data between servers
• Handling network failures

Client-Server Architecture

This model divides work between:

1. Client (Front-end) → User interface or application
2. Server (Back-end) → Database and processing

Example:

• You open a banking app (client)
• App requests account details from the bank database (server)

Benefits:

• Centralized control
• Easier management
• Supports multiple users at once

Introduction to Data Warehousing, OLAP, OLTP & Data Mining

Data Warehousing

A data warehouse is a large, centralized storage system used for storing historical data from different sources.
It helps in business analysis and decision-making.

Feature	Description
Purpose	Long-term storage for analysis
Data Type	Historical, summarized
Users	Managers, analysts
Example	Sales data of last 10 years

OLAP (Online Analytical Processing)

OLAP systems are used for analyzing large amounts of data from the data warehouse.

Feature	Description
Focus	Data analysis & reporting
Operations	Roll-up, drill-down, slice, dice
Example	“Show total sales by region and year.”

Used in business intelligence dashboards and decision support systems.

OLTP (Online Transaction Processing)

OLTP systems are used for day-to-day transactions like inserting, updating, or deleting data.

Feature	Description
Focus	Real-time transactions
Data Type	Current, detailed
Example	ATM withdrawals, online bookings, sales billing

Difference between OLTP and OLAP

Basis	OLTP	OLAP
Purpose	Daily operations	Data analysis
Data	Real-time	Historical
Users	Clerks, employees	Managers, analysts
Example	Bank transaction	Yearly profit report
Query Type	Simple, frequent	Complex, summary-based

Data Mining

Data Mining means extracting useful patterns, trends, or insights from large datasets.

Example:

• Predicting which product customers will buy next
• Identifying fraudulent credit card transactions
• Customer segmentation based on buying behavior

Common Data Mining Techniques

Technique	Purpose
Classification	Categorize data (e.g., high or low risk)
Clustering	Group similar data (e.g., customer groups)
Association	Find relationships (e.g., “People who buy bread also buy butter”)
Regression	Predict future values (e.g., sales forecast)

Summary Table

Topic	Key Idea	Example
Authentication	Verifies user identity	Login credentials
Authorization	Grants user rights	Admin vs. user access
Privileges	Database permissions	SELECT, UPDATE
Auditing	Tracks user activity	Who updated records
Access Control	Restricts user actions	Role-based permissions
Data Integrity	Ensures data correctness	Primary key, foreign key
Distributed DB	Data across many servers	Banking system
Client-Server	Front-end + back-end model	App and database
Data Warehouse	Historical data storage	10 years sales data
OLTP	Real-time transaction system	ATM withdrawal
OLAP	Analytical system	Regional sales analysis
Data Mining	Finding patterns & insights	Customer purchase trends

In Simple Words:

• Security keeps data safe
• Authorization controls access
• Integrity keeps data accurate
• Distributed DB shares data across servers
• OLTP runs daily business, OLAP analyzes it, and Data Mining learns from it

Practical Applications & Case Studies of DBMS

Use of Commercial and Open-Source DBMS

A Database Management System (DBMS) is software that helps store, organize, and manage data efficiently.
There are two main types based on cost and ownership:

Type	Description	Examples	Features
Commercial DBMS	Paid, enterprise-level databases used by large organizations	Oracle, Microsoft SQL Server, IBM DB2	High performance, strong security, technical support
Open-Source DBMS	Free to use and modify by anyone	MySQL, PostgreSQL, MariaDB, SQLite	Cost-effective, community support, easy to learn

Commonly Used DBMS in Industry

DBMS	Type	Used By	Highlights
MySQL	Open-source	Web developers, startups	Lightweight, fast, widely used with PHP websites
Oracle Database	Commercial	Banking, insurance, ERP	Very secure, supports large data
SQL Server	Commercial	Corporates, IT firms	Integrates with Microsoft products
PostgreSQL	Open-source	Research, analytics	Advanced queries, data integrity
MongoDB	Open-source (NoSQL)	E-commerce, apps	Stores data in JSON format, flexible

Real-Life Case Studies by Business Sector

Let’s look at how DBMS is practically used in different management areas like Finance, Marketing, HR, Retail, and E-Commerce 

A. Finance Sector (Banking & Investment)

Aspect	Example	DBMS Use
Customer Data Management	Banks like HDFC, SBI	Store account info, transactions, KYC data
Transaction Records	ATMs, NEFT, UPI systems	Real-time debit/credit entries using OLTP
Fraud Detection	Credit card companies	Data mining helps find suspicious patterns
Report Generation	Financial analysts	SQL queries generate monthly reports

Example Case: HDFC Bank uses Oracle Database for managing millions of customer transactions daily. It ensures ACID properties — accuracy, consistency, isolation, and durability — in all operations.

B. Marketing Sector

Aspect	Example	DBMS Use
Customer Segmentation	CRM systems like Salesforce	Store and categorize customer demographics
Campaign Management	Email & digital marketing tools	Track campaign responses and leads
Market Research	SQL + Excel + Tableau	Analyze buying patterns and preferences
Data Mining	Predictive marketing	Suggest products or promotions

Example Case: Amazon Marketing Cloud uses databases to track user behavior — what customers search, view, and buy — to send personalized recommendations.

C. Human Resource (HR) Sector

Aspect	Example	DBMS Use
Employee Records	HRMS software	Store employee data, attendance, payroll
Recruitment Database	Job portals like Naukri	Manage resumes, candidate data
Performance Management	Oracle HR Suite	Track KPIs and appraisal records
Training & Development	LMS systems	Track course progress and certifications

Example Case: Infosys HR Department uses SQL Server to maintain employee attendance, salary, and leave records, ensuring easy retrieval and analysis.

D. Retail Sector

Aspect	Example	DBMS Use
Inventory Management	Big Bazaar, D-Mart	Track stock levels, purchase orders
Billing System	POS (Point of Sale)	Update stock after every sale
Supplier Data	Vendor database	Maintain supplier details and transactions
Sales Analysis	Reporting dashboards	Identify best-selling items

Example Case: D-Mart uses MySQL + SAP ERP to automatically update inventory when a customer purchases an item — ensuring no stockouts or overstock.

E. E-Commerce Sector

Aspect	Example	DBMS Use
Customer Accounts	Flipkart, Amazon	Store login details, orders, payments
Product Database	Product catalog	Details like name, price, stock, images
Order Processing	Transactional database	Ensures each order and payment is recorded
Recommendation System	AI + DBMS	Suggest similar products using user history

Example Case: Flipkart uses MongoDB and MySQL to manage product listings, user accounts, and order histories efficiently.
The system supports millions of concurrent users and maintains real-time inventory synchronization.

Designing a Relational Database for a Business Use Case

Let’s design a simple relational database example for better understanding.

---

Use Case: Online Bookstore

Objective: Store customer orders, books, and payment details efficiently.

Step 1: Identify Entities (Tables)

Table Name	Description
Customers	Customer details
Books	Book information
Orders	Purchase details
Payments	Payment information

Step 2: Define Attributes

Table	Attributes
Customers	CustomerID (PK), Name, Email, Phone
Books	BookID (PK), Title, Author, Price, StockQty
Orders	OrderID (PK), CustomerID (FK), BookID (FK), OrderDate, Quantity
Payments	PaymentID (PK), OrderID (FK), PaymentMode, Status

Step 3: Relationships

Relationship	Description
Customer ↔ Orders	One customer can make many orders (1:M)
Orders ↔ Books	Many orders can include many books (M:N)
Orders ↔ Payments	One order has one payment (1:1)

Step 4: Sample SQL Commands

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(50),
  Email VARCHAR(50),
  Phone VARCHAR(15)
);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(100),
  Author VARCHAR(50),
  Price DECIMAL(10,2),
  StockQty INT
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  BookID INT,
  OrderDate DATE,
  Quantity INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
  FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

CREATE TABLE Payments (
  PaymentID INT PRIMARY KEY,
  OrderID INT,
  PaymentMode VARCHAR(20),
  Status VARCHAR(20),
  FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

Step 5: Output Example

Customer	Book	Quantity	Payment Mode	Status
Jay Singh	Data Analytics 101	2	UPI	Successful
Aman Gupta	Marketing Basics	1	Credit Card	Pending

Benefits of this DB Design:

• Avoids data duplication (Normalization)
• Ensures accuracy (Data Integrity)
• Easy to generate reports — sales, payments, customer orders

Summary Table

Concept	Description	Example
Commercial DBMS	Paid databases for large firms	Oracle, SQL Server
Open-source DBMS	Free, customizable	MySQL, PostgreSQL
Finance Use	Manages transactions	Banking systems
Marketing Use	Stores campaign and lead data	CRM systems
HR Use	Employee management	HRMS
Retail Use	Inventory and billing	POS system
E-Commerce Use	Orders and payments	Flipkart, Amazon
Relational DB Example	Tables with keys and relationships	Online Bookstore

In Simple Words

A DBMS is the brain behind every business operation —
from your online shopping cart to your bank account balance and employee database  all rely on databases to store, secure, and analyze information.