Database ent System

Managem (DBMS)
A Database Management System (DBMS) Diagram:
is software that facilitates the creation,
nginx
organization, management, and retrieval
of data in a structured manner. The CopyEdit
primary purpose of a DBMS is to:
University
1. Store and Organize Data: It
├── Department 1
ensures efficient storage of large
volumes of data. │ ├── Course 1
2. Ensure Data Integrity and │ ├── Course 2
Security: It provides mechanisms
to enforce data consistency and ├── Department 2
access control. │ ├── Course 3
3. Support Multi-User Access:
│ ├── Course 4
Multiple users can access and
manipulate data concurrently • Advantage: Fast retrieval due to
without conflicts. predefined paths.

4. Enable Efficient Query Processing: • Disadvantage: Rigid structure,

It allows users to retrieve specific making modifications difficult.
data using query languages like
SQL.
Network Model
5. Provide Backup and Recovery: It
ensures data persistence and The Network Model is an extension of the
recovery in case of failures. hierarchical model but allows multiple
parent-child relationships (many-to-many).
Hierarchical Model
It uses graphs where records are
The Hierarchical Database Model connected via pointers.
organizes data in a tree-like structure with
Example:
a parent-child relationship. Each parent
node can have multiple child nodes, but In a banking system:
each child has only one parent.
• A Customer can have multiple
Example: Accounts.

Consider a university database where: • An Account can belong to multiple

Customers (joint accounts).
• A University has multiple
Departments. Diagram:

• Each Department has multiple css

Courses.
CopyEdit
 Customer 1 ────► Account A ◄──── | User Applications (SQL, Forms, APIs)
Customer 2 |
│ +-------------------------+--------------------------
+
▼
▼
Account B
+-----------------------------------------------------
• Advantage: More flexibility in
+
relationships.
| Query Processor | Transaction
• Disadvantage: Complex
Manager |
implementation due to pointer-
based access. +-------------------------+--------------------------
+
▼
Components of DBMS
+-----------------------------------------------------
The key components of a DBMS and their
+
interactions are:
| Storage Manager | Index Manager
1. DBMS Engine – Handles database
|
interactions and ensures
consistency. +-----------------------------------------------------
+
2. Database Schema – Defines
structure, tables, and constraints. ▼

3. Query Processor – Translates user +-----------------------------------------------------

queries into database operations. +

4. Transaction Manager – Ensures | Physical Database (Tables, Indexes,

ACID (Atomicity, Consistency, Logs) |
Isolation, Durability) properties.
+-----------------------------------------------------
5. Storage Manager – Manages +
physical data storage and retrieval.
6. User Interfaces – Enables users to
Keys in Relational Model
interact (SQL, GUI, APIs).
Keys are attributes that help uniquely
Diagram:
identify records in a table.
pgsql
Types of Keys:
CopyEdit
1. Primary Key – Uniquely identifies a
+----------------------------------------------------- record (e.g., Student_ID in a
+ Student table).
 2. Candidate Key – A minimal set of 5. Key Attribute – Uniquely identifies
attributes that uniquely identify a an entity (e.g., Student_ID).
row.
Example ER Diagram for a Student Entity:
3. Foreign Key – A reference to the
pgsql
primary key in another table.
CopyEdit
4. Super Key – A set of attributes that
uniquely identify a tuple. +----------------------+
5. Composite Key – A key made up of | Student |
multiple attributes.
+----------------------+
6. Alternate Key – Candidate keys not
| Student_ID (PK) | ← Key
chosen as the primary key.
Attribute
Example Table: Employee
| Name |
Emp_ID (PK) Name Dept_ID (FK) | DOB |
101 Alice D1 | {Phone Number} |←
Multivalued
102 Bob D2
| Address (City, State) | ← Composite
• Emp_ID → Primary Key
| Age (Derived) |
• Dept_ID → Foreign Key (refers to
Department table) +----------------------+

Types of Attributes in ER Model Mapping Cardinality in ER Model

Attributes define properties of an entity in Mapping cardinality defines the number

an Entity-Relationship (ER) Model. of entity instances related to another.

1. Simple Attribute – Cannot be Types of Mapping Cardinality:

broken down further (e.g., Name, 1. One-to-One (1:1)
Age).
o Each entity in A is related to
2. Composite Attribute – Can be at most one entity in B.
divided into sub-parts (e.g., Full
Name → First Name, Last Name). o Example: Each Person has
one Passport.
3. Derived Attribute – Calculated
from other attributes (e.g., Age Diagram:
from DOB). scss
4. Multivalued Attribute – Can have CopyEdit
multiple values (e.g., Phone
Numbers). Person (1) -------- (1) Passport
 2. One-to-Many (1:M) Let me know if you need further details!
o One entity in A maps to
multiple entities in B. 2/2
o Example: One Teacher 4o
teaches multiple Students.
You said:
Diagram:
Explain the following Relational Algebra
scss operations. Select, Project, Union, Set
difference, Cartesian product Explain
CopyEdit
Aggregate functions with suitable
Teacher (1) -------- (M) Student examples. Discuss DML triggers. Explain
DDL commands. Explain DML commands.
3. Many-to-One (M:1)
Discuss Tuple Relational Calculus (TRC).
o Multiple entities in A relate
ChatGPT said:
to a single entity in B.
Here’s a detailed explanation of each topic
o Example: Multiple
you asked about:
Employees work in one
Department.
Diagram: Relational Algebra Operations
scss Relational Algebra is a procedural query
language used to retrieve data from
CopyEdit
relational databases. Below are the basic
Employee (M) -------- (1) Department operations:
4. Many-to-Many (M:N) 1. Select (σ)
o Multiple entities in A relate • Purpose: Filters rows (tuples)
to multiple entities in B. based on a specified condition.
o Example: Students enroll in • Syntax: σ_condition (Relation)
Courses.
• Example: Get employees with
Diagram: salary > 50000
scss scss
CopyEdit CopyEdit
Student (M) -------- (N) Course σ_salary > 50000 (Employee)
2. Project (π)
This summary provides a comprehensive • Purpose: Extracts specific columns
explanation of DBMS concepts, models, (attributes) from a relation.
keys, attributes, and mapping
• Syntax: π_attribute_list (Relation)
cardinalities with examples and diagrams.
 • Example: Get names of all
employees
Aggregate Functions (SQL Examples)
scss
Aggregate functions perform calculations
CopyEdit on a set of values and return a single
value.
π_name (Employee)
3. Union ( ∪ ) Functio Descriptio Example
Result
n n SQL
• Purpose: Combines tuples from
two relations, removing duplicates. Counts SELECT Number
COUNT the COUNT(*) of
• Requirement: Both relations must () number of FROM employe
be union-compatible (same records Employee; es
attributes).
SELECT
• Example: Calculates Total
SUM(Salar
SUM() the total salary
nginx y) FROM
sum paid
Employee;
CopyEdit
Employee ∪ Manager Computes SELECT
the AVG(Salary Average
4. Set Difference ( - ) AVG()
average ) FROM salary
• Purpose: Returns tuples that are in value Employee;
the first relation but not in the Returns SELECT
second. the MAX(Salar Highest
MAX()
• Example: Employees who are not maximum y) FROM salary
managers value Employee;

nginx Returns SELECT

the MIN(Salary Lowest
CopyEdit MIN()
minimum ) FROM salary
Employee - Manager value Employee;
5. Cartesian Product ( × )
• Purpose: Combines every tuple of DML Triggers
one relation with every tuple of
another. Definition:

• Example: Pair each employee with A DML Trigger is a special stored

each department procedure that automatically runs when a
Data Manipulation Language (DML) event
nginx occurs (INSERT, UPDATE, DELETE).
CopyEdit Purpose:
Employee × Department
 • Enforce business rules Command Description
• Audit data changes Deletes all data from a table
TRUNCATE
• Maintain data integrity but not the table itself

Example: Example:

sql sql

CopyEdit CopyEdit

CREATE TRIGGER audit_salary CREATE TABLE Employee (

AFTER UPDATE ON Employee EmpID INT PRIMARY KEY,

FOR EACH ROW Name VARCHAR(50),

BEGIN Salary DECIMAL(10, 2)

INSERT INTO AuditLog(EmpID, OldSalary, );

NewSalary, ChangeDate)
VALUES (OLD.EmpID, OLD.Salary,
DML Commands (Data Manipulation
NEW.Salary, NOW());
Language)
END;
Purpose:
• This trigger logs salary changes of
DML commands are used to manipulate
employees.
data within database tables.

Command Description
DDL Commands (Data Definition
Language) INSERT Adds new data into a table

Purpose: UPDATE Modifies existing data

DDL commands define or modify database DELETE Removes data from a table
schema and structure.
Retrieves data (though
Command Description SELECT technically considered part of
SQL, not pure DML)
Creates new database objects
CREATE
(tables, views, indexes, etc.) Examples:
Modifies an existing database sql
ALTER
object
CopyEdit
Deletes database objects INSERT INTO Employee (EmpID, Name,
DROP
permanently Salary) VALUES (1, 'Alice', 50000);
UPDATE Employee SET Salary = 55000
WHERE EmpID = 1;
DELETE FROM Employee WHERE EmpID =
1;

Tuple Relational Calculus (TRC)

Definition:
TRC is a non-procedural query language
where you describe what to retrieve
rather than how to retrieve it.
Syntax:
r
CopyEdit
{ T | P(T) }
• T → Tuple variable
• P(T) → Predicate (condition)
Example:
Get names of employees with salary >
50000
r
CopyEdit
{ T.Name | T ∈ Employee AND T.Salary >
50000 }
• Meaning: Retrieve T.Name for
every T in Employee where T.Salary
> 50000