Chapter no.

01

1.Database: A database is an organized collection of related data that can be easily

accessed, managed, and updated. It stores information in a structured way so that it
can be retrieved and processed efficiently. Databases are designed to handle large
volumes of data and ensure that this data remains accurate, consistent, and secure.
Think of a database like a digital filing cabinet — instead of storing paper documents
in drawers, data is stored in tables (rows and columns) inside a computer system.
Example: A banking system database stores customer information, account details,
and transaction history.
An e-commerce website database stores product details, customer profiles, and
order history.

Key Points:
Data is stored systematically in tables.
Designed for fast retrieval and updating.
Works with a Database Management System (DBMS) for better control.

2.DBMS: A Database Management System (DBMS) is software that allows users

and applications to interact with a database. It acts as a bridge between the
database and the end-user, making it easy to store, retrieve, update, and manage
data without having to manually deal with the raw files.
Instead of writing complex file-handling code, we use DBMS commands like
SELECT, INSERT, UPDATE, and DELETE to manage data.

Examples of DBMS:
Relational DBMS: MySQL, Oracle, PostgreSQL, MS SQL Server.
Non-relational DBMS: MongoDB, Cassandra.

Analogy:
Think of DBMS as a librarian:
The bookshelves are the database (storage).
The librarian (DBMS) knows where each book is, ensures you return it, and protects
the library’s order.
3.Overview of DBMS:
A DBMS is not just about storing data — it ensures that data is stored securely,
remains consistent, and is accessible to multiple users without conflicts.
Main Functions of a DBMS
Data Definition – Creating tables, structures, and relationships.
Data Storage – Saving data efficiently on disk.
Data Retrieval – Fetching the required information quickly.
Data Manipulation – Adding, modifying, and deleting data.
Data Security – Protecting data from unauthorized access.
Transaction Management – Handling operations so that the database always
remains in a consistent state.

Advantages of DBMS over File Systems:

Less Data Redundancy (no repeated storage of same data unnecessarily).
Better Data Integrity (accuracy maintained).
Security Control (restricted access to sensitive data).
Multi-user Access (multiple users can work simultaneously).

4. Characteristics of DBMS

a. Data Integrity
Meaning: Data integrity ensures that the stored data is accurate, consistent, and
reliable over its entire lifecycle.
Example: If a student’s roll number is 101, DBMS ensures that this roll number will
not mistakenly be assigned to another student.
How DBMS ensures it:
Using constraints like PRIMARY KEY, UNIQUE, CHECK.

b. Data Security
Meaning: DBMS protects data from unauthorized access and malicious threats.
Example: In a hospital database, only doctors can see patients’ medical history, but
receptionists may only see appointment schedules.

How DBMS ensures it:

User authentication (username/password).
Role-based access control (GRANT, REVOKE commands).
Encryption for sensitive data.

c. Data Independence
Meaning: Changes in the database structure should not affect the way applications
access data.
Example: If we change the name of a table column in the database, the application
can still work without rewriting the entire program (with minimal adjustments).

Types:
Physical Data Independence – Changing how data is stored physically does not
affect logical access.
Logical Data Independence – Changing the logical schema does not affect the
applications.

d. Transaction Control – Rollback

Meaning: A transaction is a set of database operations that must be completed
together. If any operation fails, the database should revert to its previous state.
Example: In online banking, if ₹500 is deducted from one account but not credited to
another due to a failure, rollback will cancel the deduction.

Commands:
ROLLBACK – Undo the last transaction.
COMMIT – Save the transaction permanently.

e. Concurrency Control
Meaning: Concurrency control ensures that multiple users can access and modify
the database at the same time without causing conflicts.
Example: Two employees trying to update the same customer’s address at the same
time should not cause incorrect results.

How DBMS ensures it:

Locking mechanisms (Shared Lock, Exclusive Lock).
Timestamp ordering.

f. Data Recovery – Backup and Restore

Meaning: Data recovery ensures that the database can be restored to a working
state after a crash, failure, or corruption.
Two main strategies:
Backup – Creating copies of database files at regular intervals.
Restore – Using backups to recover data when needed.

Example:
If a company database crashes due to hardware failure, the latest backup can be
restored to minimize data loss.

5. Advantages of DBMS over File System

When we compare a File System (traditional way of storing data in separate files)
with a Database Management System (DBMS), DBMS offers many advantages.
Let’s understand each of the mentioned advantages:

a. Redundancy (Minimizing Data Duplication)

File System Problem:

In a file system, the same data might be stored in multiple files. For example, a
student’s address may be stored in both the library records and exam records
separately. If the address changes, it has to be updated in multiple places.

DBMS Solution:
DBMS uses a centralized database where each piece of information is stored only
once. This avoids data redundancy.

Example:
Student’s address stored only once in a “Student” table. Other tables like “Exam
Results” or “Library Loans” just reference the Student ID.
Advantage: Saves storage space and ensures faster updates.

b. Inconsistency (Ensuring Uniform Data)

File System Problem:
When the same data exists in multiple files, changes in one file might not be
reflected in the others, leading to inconsistent data.
DBMS Solution:
Because DBMS keeps a single version of data in a centralized manner, it ensures
that all users see the same updated data.

Example:
If a student changes their phone number, it is updated once in the DBMS, and all
applications use the updated number.
Advantage: Improves data reliability.

c. Data Sharing (Controlled Multiple Access)

File System Problem:
Multiple users accessing the same file may cause conflicts, and file systems don’t
provide fine-grained sharing controls.
DBMS Solution:
DBMS supports multi-user environments, allowing controlled and simultaneous
access through features like transactions and locks.

Example:
In a banking system, multiple tellers can access a customer’s account data at the
same time without corrupting the data.
Advantage: Improves collaboration and productivity.
d. Centralized Control of Data
File System Problem:
Each application maintains its own files, making management scattered and difficult.
DBMS Solution:
Data is centrally stored and managed by the Database Administrator (DBA), who
controls updates, backups, and access permissions.

Example:
In a university DBMS, the DBA manages student, course, and exam data centrally,
rather than each department keeping separate files.
Advantage: Easier maintenance and better consistency.

e. Standards Enforcement
File System Problem:
No guarantee that the data follows a certain format or structure.
DBMS Solution:
DBMS enforces data standards like naming conventions, formats (e.g., date format),
and validation rules.

Example:
Ensuring that a phone number is always stored in +91-XXXXXXX format.
Advantage: Improves data quality and interoperability.

f. Security Restrictions
File System Problem:
Limited or no control over who can view or edit specific parts of data.
DBMS Solution:
DBMS provides role-based security where users are given permissions (read, write,
update) based on their role.
Example: In a hospital DBMS, doctors can see patient history, but only the admin
can delete records.
Advantage: Protects sensitive information.

g. Integrity

File System Problem:

No in-built mechanism to ensure that the data entered is valid.
DBMS Solution:
DBMS supports integrity constraints such as:
Primary Key (unique identifier for records)
Foreign Key (ensuring related data exists)
Check constraints (ensuring values fall within a range)

Example:
Preventing a student from registering for a course that doesn’t exist.
Advantage: Prevents entry of incorrect data.

h. Data Independence
File System Problem:
Changes in file structure require changes in all applications using it.
DBMS Solution:
DBMS separates logical data structure from the physical storage. Applications can
work without being affected by changes in how data is stored.

Example:
Moving a database from HDD to SSD doesn’t affect applications using the data.
Advantage: Flexibility in data management.

i. New Application Development

File System Problem:
Creating new applications requires starting from scratch, including designing file
storage.

DBMS Solution:
With centralized and well-structured data, new applications can be developed quickly
using existing data.

Example:
Adding a mobile app for an e-commerce site using the same product and customer
database.
Advantage: Reduces development time and cost.

Feature File System DBMS

Redundancy High Low
Consistency Poor High
Data Sharing Difficult Easy
Control Decentralized Centralized
Standards Not enforced Enforced
Security Weak Strong
Integrity Manual Automated
Data Independence No Yes
New Apps Hard Easy

6. Levels of Abstraction in DBMS

DBMS uses three levels of abstraction to hide details from users and make the
system easier to use.

1. Physical Level (Lowest Level)

Definition: Describes how data is physically stored in storage devices (hard disks,
SSDs).
User: Database administrators and system programmers.
Details Covered: File organization, indexing, compression.
Example: Storing student records as blocks on a disk with a B+ tree index.
Analogy: Like the engine of a car — you don’t need to know how it works to drive it.

2. Logical Level (Middle Level)

Definition: Describes what data is stored in the database and the relationships
between the data, without worrying about how it is stored.
User: Database designers, programmers.
Details Covered: Tables, columns, relationships, constraints.
Example: “Student” table with columns like RollNo, Name, Address, without worrying
about disk storage.
Analogy: Like a map of a city — you know where places are, but not how roads are
built.

3. View Level (Highest Level)

Definition: Describes only a part of the database relevant to a user.
User: End-users.
Details Covered: Custom views that hide unnecessary or sensitive data.
Example: In a student portal, the student can only see their own marks, not the
marks of others.
Analogy: Like a restaurant menu — you see only the dishes available, not the full
recipe.

Diagram of Abstraction Levels:

[ View Level ] → What user sees

[ Logical Level ] → How data is organized
[ Physical Level ] → How data is stored

7. Database Architecture
Database architecture defines the structure and interaction of the database
components. The most common type is Three-Tier Architecture.

1. Single-Tier Architecture
Definition: The database and application both run on the same system.
Example: MS Access where both DB and UI are in one application.
Advantage: Simple and fast for small applications.

2. Two-Tier Architecture
Structure:
Tier 1: Client application (UI + business logic)
Tier 2: Database server
Example: Client program (Java, Python) connecting directly to MySQL.
Advantage: Better performance than single-tier.
Limitation: Difficult to scale.

3. Three-Tier Architecture
Structure:
Tier 1: Client (User Interface)
Tier 2: Application Server (Business Logic)
Tier 3: Database Server
Example: Web application with HTML/CSS (UI), PHP/Java (logic), and MySQL
(database).
Advantage:
Security: Database hidden behind application layer
Scalability: Easy to add more clients or servers
Maintainability: Business logic is separate

Three-Tier Architecture Diagram:

[ Client UI ] ↔ [ Application Server ] ↔ [ Database Server ]

8. Types of Data Independence
Data independence means changing one level of database abstraction without
affecting the other levels.

a. Logical Data Independence

Definition: Ability to change the logical schema (tables, columns, relationships)
without changing the application programs.
Example: Adding a new column “Email” to the “Student” table without changing
existing applications.
Advantage: Makes database structure flexible for future changes.

b. Physical Data Independence

Definition: Ability to change the physical storage without changing the logical
schema.
Example: Moving data from HDD to SSD or changing indexing methods without
affecting tables or applications.
Advantage: Freedom to optimize storage without affecting users.

Summary Table:

Type Change Allowed What Remains Unchanged

Logical Table/column structure Application programs
Physical Storage methods Logical schema & programs

9. DBMS Architecture with Respect to Specific Components

A Database Management System (DBMS) is like the brain of a data-driven system. It
handles how data is stored, processed, and retrieved. To understand its architecture,
we break it down into different functional components.
(a)Query Processing Components
Query processing is the set of steps that transform a high-level query (like SQL) into
a sequence of low-level database operations.
It ensures that user queries are interpreted, validated, optimized, and executed
efficiently.

1. DDL Interpreter
Definition: The DDL (Data Definition Language) interpreter handles statements like
CREATE TABLE, ALTER TABLE, and DROP TABLE.
Function: It parses and interprets these commands and sends the results to the data
dictionary (metadata storage) to update database structure definitions.

Example:
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Marks INT
);

The DDL interpreter reads this command and updates the metadata to store the
definition of Students table.

2. DML Compiler
Definition: The DML (Data Manipulation Language) compiler translates high-level
data manipulation commands (INSERT, UPDATE, DELETE, SELECT) into low-level
instructions.
Function: It converts SQL statements into an execution plan (a step-by-step
approach for accessing data).

Example:
SELECT Name FROM Students WHERE Marks > 80;
The DML compiler converts this into an internal plan:
Access the Students table.
Filter rows with Marks > 80.
Retrieve only the Name field.

3. Query Evaluation Engine

Definition: This is the execution unit that follows the compiled execution plan to
actually retrieve or modify data.
Function: Interacts with storage and buffer managers to fetch data blocks, apply
conditions, and return results.
Example: In the above SELECT query, the query evaluation engine physically reads
relevant disk blocks, filters them, and sends the list of student names to the user.

(b) Storage Manager / Storage Management

Definition: Acts as the interface between the low-level stored data and the higher-
level query processing system.
Main Role: Manages how data is stored on disk and retrieved into memory.
Functions:
File Manager – Handles allocation of space and file organization.
Buffer Manager – Decides which data is kept in RAM for quick access.
Data Dictionary Manager – Stores metadata.
Transaction & Recovery Manager – Works with transaction manager for durability.
Example: If the query engine needs 1,000 rows, the storage manager finds where
they are on disk, reads them, and sends them to memory.

(c) Transaction Management

Definition: Ensures that the database remains consistent and reliable when multiple
users or processes are making changes.
Functions:
Concurrency Control: Prevents conflicts when multiple transactions access the same
data.
Recovery Management: Restores the database to a consistent state in case of
failure.
ACID Properties:
Atomicity – All or nothing execution.
Consistency – Data must follow rules.
Isolation – Transactions don’t interfere.
Durability – Once done, it stays done.
Example: If two bank customers try to withdraw from the same account, transaction
management ensures both operations do not result in negative balance.

10. Working of a DBMS

A DBMS works like a middleman between the user and the raw data stored on disk.

Working:

User Issues a Query: A user runs an SQL statement through an application or

directly via a query tool.
Parsing & Validation: The DBMS checks for syntax errors and ensures that the user
has the rights to perform the action.
Query Optimization: The DBMS determines the most efficient way to execute the
query (e.g., using indexes instead of scanning the whole table).
Execution: The DBMS interacts with the storage manager to fetch or update the
data.
Transaction Control: If the operation is part of a transaction, the DBMS ensures ACID
properties.
Return Result: The processed results are sent back to the user in the requested
format.
Example:
When you order something online and check your order history, the DBMS retrieves
the details from multiple tables (Orders, Products, Payments) in milliseconds by
running optimized queries in the background.

11. Database Users

Different users interact with DBMS in different ways.

(a) Naive User

Definition: Users who directly use applications without knowing about the database
structure.
Example: ATM users withdrawing cash—they just press buttons; the software
communicates with DBMS in the background.

(b) Application Programmers

Definition: People who write code that uses DBMS APIs to interact with the
database.
Example: A Java developer writing JDBC code to fetch customer orders.

(c) Sophisticated Users

Definition: Experts who interact with the database directly using complex queries.
Example: Data analysts writing SQL queries to generate business reports.

(d) Specialized Users

Definition: Users who develop special-purpose database applications.
Example: Scientists writing programs to store and retrieve research data in a
customized way.

12. Roles and Responsibilities of a Database Administrator (DBA)

A DBA is like the manager and caretaker of the database.
Key Responsibilities:

Database Design: Decide table structures, relationships, and indexing.

Security Management: Create user accounts, assign privileges, and protect against
unauthorized access.

Backup and Recovery: Schedule regular backups and plan disaster recovery.

Performance Tuning: Optimize queries, manage indexes, and adjust database

configuration.
Monitoring & Maintenance: Keep the database running smoothly, apply patches, and
fix errors.

Capacity Planning: Forecast storage needs and prepare for future growth.

Policy Enforcement: Ensure data integrity and compliance with laws.

Example: In a hospital system, the DBA ensures that only doctors can update patient
records, backups are taken daily, and the system can handle thousands of patients
without slowing down.
Chapter no. 02

1. Client-Server Architecture:
Client-server architecture is a computing model where the client (usually a user
device or software) requests a service, and the server (a powerful machine or
software) provides that service. The two communicate over a network (like the
internet or a local network) using a specific protocol (e.g., HTTP, FTP, SMTP).

Working:
Client Side – The client is the requester.
It could be:
-A web browser requesting a webpage from a web server.
-A mobile banking app requesting account details from the bank’s server.
Server Side – The server processes the request and returns a response.It:
-Receives the request.
-Processes it (possibly by fetching data from a database).
-Sends back the requested information or service.
Communication – This happens via a network using protocols (e.g., TCP/IP for
general communication, HTTP for web pages).

Example:
Google Search
Client: Your web browser.
Server: Google’s data servers.
Process: You type a query → request goes to Google server → server searches data
→ sends results to your browser.

Advantages:
Centralized control of data.
Easy to update and maintain on the server side.
Multiple clients can use the same service.
Disadvantages:
If the server goes down, clients can’t access the service.
Heavy load on the server if too many requests come at once.

2. Types of Client-Server Architecture

a. Single-tier Architecture
In single-tier architecture, client and server functions are performed on the same
machine.
There’s no network communication – everything happens locally.

Example:
Microsoft Excel on your computer (both application logic and data storage are on the
same PC).

Advantages:
Fast execution (no network delay).
Easy to manage.

Disadvantages:
Limited scalability (only one machine).
Not suitable for multiple users.

b. Two-tier Architecture
Here, the client is on one machine and the server (usually a database) is on another
machine. The application logic might be on the client.

Example:
A college result software where:
Client: Frontend application installed on staff computers.
Server: Database server storing all student records.
Advantages:
Better data security on the server.
Supports multiple clients.

Disadvantages:
Performance may degrade if many clients connect at once.

c. Three-tier Architecture
Separates the system into three layers:

Presentation Layer – Client interface (e.g., browser, app).

Application Layer – Processes requests, applies business logic.
Data Layer – Database or data storage.

Example:
Online shopping website:
Presentation Layer: Web browser showing the site.
Application Layer: Server application processing orders.
Data Layer: Database storing products and orders.

Advantages:
Easier maintenance.
Better scalability and security.

Disadvantages:
Slightly more complex than two-tier.

d. N-tier Architecture
Extension of three-tier where there are more than three layers (e.g., extra layers for
authentication, caching, API gateways, etc.).
Example:
Large-scale apps like Amazon:
Client Layer (browser or app).
Web Server Layer.
Application Server Layer.
Database Layer.
Additional layers like caching servers, analytics servers.

Advantages:
Highly scalable.
Easier to add new features.

Disadvantages:
Higher complexity.
More infrastructure cost.

3. Goals of Client-Server Architecture

a. Robust
The architecture should handle failures without crashing.
Example: If one server fails, load balancing should redirect requests to another.

b. Performing and Scalable

It should handle large numbers of requests quickly and allow easy expansion.
Example: Adding more servers to handle increased traffic during festive sales.

c. Take the OO (Object-Oriented) Design Principles

Follow OOP principles like encapsulation, inheritance, and polymorphism to make
code modular and reusable.
Example: A “User” object that can be reused in login, profile, and order modules.
d. Avoid Unnecessary Complexity
Don’t over-engineer; use only the layers and services needed.
Example: A small school database app doesn’t need a multi-layer microservices
system.

e. Maintainable and Extensible

Easy to update without breaking existing functions.
Example: Adding a “dark mode” to a website without affecting other features.

f. Ensure Ease of Testing

Components should be testable individually.
Example: Testing the login module separately from the payment module.

g. Promote Reuse of Application

Common features should be reusable across different apps or clients.
Example: A payment gateway API used by multiple shopping websites.

h. Support for Multiple Client Types

The architecture should serve mobile apps, desktop software, and web browsers
equally.
Example: Facebook works on browsers, Android, and iOS apps using the same
backend.

i. Delivered on Time
Good architecture helps teams work in parallel, making timely delivery easier.
Example: Frontend and backend teams working independently and integrating later.

4. Object-Based Logical Models

In DBMS, logical models describe how data is logically stored, organized, and
related without focusing on the physical storage details.
The Object-Based Logical Models use concepts from object-oriented programming
(OOP) to represent data.
They treat both data and operations on data as a single unit called an object.

Instead of just storing values like numbers or strings, these models store objects that
have:

Attributes (properties) → Data values.

Methods (functions) → Operations that can be performed on the object.

Explanation
In object-based logical models:
The world is viewed as a collection of objects.
Each object belongs to a class (similar to OOP).
Classes can have subclasses (inheritance).
Objects can relate to other objects through relationships.

Examples of such models: Entity-Relationship (E-R) model, Object-oriented model,

Semantic data model, Functional data model.

Example:
Imagine a Library Database:
Object → Book
Attributes: Title, Author, ISBN
Methods: Borrow(), Return()
Object → Member
Attributes: Name, MembershipID
Methods: PayFees(), ReserveBook()

Advantages
Rich data representation – Can store complex data like images, audio, video, not just
numbers and text.
Encapsulation – Data and operations are packaged together.
Inheritance – Reduces redundancy by reusing attributes/methods in subclasses.
Better for complex applications – Useful in CAD, multimedia databases, scientific
research, etc.

Disadvantages
Complexity – More difficult to design compared to simple relational models.
Performance issues – Objects require more storage and processing.
Less standardization – Fewer universally accepted query languages.
Learning curve – Requires knowledge of both OOP and database concepts.

5. Record-Based Logical Model

A Record-Based Logical Model organizes data into fixed-format records (similar to
rows in a table).
Each record consists of fields (attributes), and each field stores a value.
The entire database is seen as a collection of such records.

Explanation
In this model:
Fields → Store individual values (like Name, Age).
Records → Combination of fields representing one entity.
Files (tables) → Collection of related records.
There are three main types:
Relational Model → Data stored in tables.
Hierarchical Model → Data in a tree structure.
Network Model → Data in a graph structure.

Example:
For a STUDENTS table:
RollNo Name Age Marks
101 Riya 20 85
102 Arjun 21 90

Advantages:
Simplicity – Easy to understand for users.
Efficient storage – Fixed-size records allow faster access.
Good for structured data – Works well when the schema is fixed.

Disadvantages:
Poor handling of complex data – Not suitable for multimedia or nested objects.
Rigid structure – Changing the schema can be difficult.
Redundancy – May store duplicate data.

6. Relational Model
Proposed by E.F. Codd in 1970, the Relational Model is the most widely used logical
model in modern databases.
It stores data in the form of tables (relations) made up of rows (tuples) and columns
(attributes).

a. Basic Building Blocks / Structure

Relation (Table) → Main structure for storing data.
Tuple (Row) → A single record in the table.
Attribute (Column) → A data field describing some property of the entity.
Domain → The set of allowed values for an attribute.

Keys →
Primary Key: Uniquely identifies each record.
Foreign Key: Links tables together.
Candidate Key: Possible keys for unique identification.
Constraints → Rules to ensure data integrity (NOT NULL, UNIQUE, CHECK, etc.).
Example Table:
STUDENTS

RollNo (PK) Name Age DeptID (FK)

1 Riya 20 101
2 Arjun 21 102

b. Advantages
Simplicity – Easy to understand and design.
Data independence – Physical storage doesn’t affect logical design.
Flexibility – Can be modified without major disruption.
Data integrity – Enforces constraints.
Standardized query language – SQL.

c. Disadvantages
Performance issues – May be slower for very large datasets compared to
specialized DBs.
Overhead – Requires complex joins for related data.
Not ideal for complex data – Struggles with multimedia or unstructured data.

7. Hierarchical Model
The Hierarchical Model organizes data into a tree-like structure, where each record
(node) has one parent but can have many children.
It’s an older model, mainly used in mainframe systems.

a. Basic Building Blocks / Structure

Root Node → The topmost record.
Parent-Child Relationship → Each parent can have multiple children.
Levels → Different layers in the tree.
Records → Each node stores a set of fields.

Example:
Company Database:
Root: Company
Child: Department
Child: Employee

b. Advantages
Efficiency – Good for queries with predictable structure.
Data integrity – Parent-child links are explicit.
Fast navigation – Can quickly traverse known paths.

c. Disadvantages
Rigid structure – Hard to reorganize or change.
Redundancy – Duplicate data if relationships are complex.
Poor flexibility – One parent per child limit.

8. Network Database Model

The Network Model organizes data as a graph where records (nodes) can have
multiple parent and child records.
It’s more flexible than the hierarchical model.

a. Basic Building Blocks / Structure

Records (Nodes) → Store fields of data.
Sets (Edges) → Define relationships between records.
Owner-Member Relationship → One record is the owner, the other is a member in a
set.
Many-to-Many Relationships → Supported natively.
Example:
In a university database:
A Student can enroll in multiple Courses.
A Course can have multiple Students.

b. Advantages
Flexibility – Can represent complex relationships easily.
Efficiency – Direct navigation between related records.
Supports many-to-many relationships.

c. Disadvantages
Complexity – Harder to design and maintain.
Navigation-based – User must know the path to data.
Declining use – Mostly replaced by relational databases.
Chapter no. 03

1. ER Model.
The Entity–Relationship (ER) model is a conceptual framework used in database
design to visually represent the data and the relationships between data items. It
was introduced by Peter Chen in 1976 as a way to create a clear and simple
blueprint of a database before actually implementing it in a DBMS.

The ER model helps bridge the gap between real-world objects and database
structures.

It represents:
Entities → The objects in the real world.
Attributes → Properties of those objects.
Relationships → How entities are connected.

Why it’s important:

It provides a high-level view of the system.
It reduces confusion during database design.
It ensures data integrity by defining relationships clearly before physical
implementation.

Example:
In a college database:
Entities → Student, Course, Faculty.
Attributes → Student_ID, Name, Age, Course_Name.
Relationships → “Enrolls In” (between Student and Course).

2. Components of an ER Diagram
An ER diagram is the visual representation of the ER model.
It has three main components:
Entities
Attributes
Relationships

a. Entities
An entity is an object in the real world that can be distinctly identified. It can be a
person, place, thing, or concept.

Types of Entities:
i. Strong Entity Type
An entity that can exist independently and has a primary key.
Represented by a rectangle in ER diagrams.

Example:
Student with attributes like Student_ID, Name, Age.
Student_ID acts as the primary key.

ii. Weak Entity Type

Cannot exist without being linked to another entity (called the owner entity).
Does not have a complete primary key of its own.
Represented by a double rectangle in ER diagrams.
Connected to the owner entity via an identifying relationship.

Example:
Dependent in an employee database.
Depends on Employee entity.
Primary key is a combination of Employee_ID and Dependent_Name.
b. Attributes
Attributes describe the properties of an entity.

Types of Attributes:

i. Composite Attributes
Attributes that can be divided into smaller subparts.
Example: Full_Name → First_Name + Middle_Name + Last_Name.

ii. Multivalued Attributes

Attributes that can have multiple values for a single entity.
Represented by a double oval in ER diagrams.
Example: Phone_Number for a student who has more than one contact number.

iii. Derived Attributes

Attributes that can be calculated from other attributes.
Represented by a dashed oval.
Example: Age can be derived from Date_of_Birth.

iv. Null Attributes

Attributes that can have no value for some entities.
Example: Middle_Name can be null for some students.

v. Key Attributes
Uniquely identify an entity in the entity set.
Represented by an oval with the attribute name underlined.
Example: Student_ID in the Student entity.

c. Relationships
A relationship shows how two or more entities are connected.
Example:
Student — Enrolls In → Course.
Represented by a diamond shape in ER diagrams.

3. Types of Keys in ER Diagram

Keys are used to uniquely identify entities in a set.

a. Super Key
A set of one or more attributes that can uniquely identify a record.
Example: (Student_ID), (Student_ID + Name).

b. Composite Key
A key that consists of two or more attributes together to uniquely identify an entity.
Example: (Course_ID, Semester) for a course offered multiple times.

c. Candidate Key
Minimal set of attributes that uniquely identify an entity.
Example: In a Student table: Student_ID, Email_ID (both can be candidate keys).

d. Primary Key
One candidate key chosen as the main identifier of records.
Example: Student_ID as the primary key in the Student entity.

e. Alternate Key
Candidate keys that are not chosen as the primary key.
Example: Email_ID in the Student entity.

f. Secondary Key
An attribute used for data retrieval but not necessarily unique.
Example: Department in a Student entity.
4. Relations

a. Relationship Set
A collection of similar relationships.
Example: All “Enrolls In” relationships between Students and Courses form a
relationship set.

b. Degree of Relationship
The number of entity types involved in a relationship.

i. Unary Relationship
Relationship between entities of the same type.
Example: Employee — Manages → Employee.

ii. Binary Relationship

Relationship between two entity types.
Example: Student — Enrolls In → Course.

iii. Ternary Relationship

Relationship between three different entity types.
Example: Supplier — Supplies → Product — To → Warehouse.

5. Relationship Types in ER Model

In Entity–Relationship (ER) modeling, relationships describe how two or more
entities are connected in a database. Understanding relationship types, constraints,
and degrees is important for designing accurate ER diagrams.

(a) Mapping Constraints / Cardinalities

Mapping constraints (also called cardinalities) define how many instances of one
entity can be associated with instances of another entity.
In ER diagrams, cardinalities are shown near the entities.

1. One-to-One (1:1)
Definition:
One entity instance from Entity A is associated with at most one entity instance from
Entity B, and vice versa.

Example:
Entity A: Person
Entity B: Passport
Each person has only one passport, and each passport belongs to only one person.
Diagram:
Person (1) <-----> (1) Passport

Example:
Like a school locker assigned to exactly one student.

2. One-to-Many (1:N)
One entity instance from Entity A can be related to many instances in Entity B, but
each instance in Entity B is related to only one instance in Entity A.

Example:
Entity A: Teacher
Entity B: Student
One teacher can teach many students, but each student has only one main teacher
(in this relationship context).

Diagram:
Teacher (1) <------> (N) Student
Analogy:
A tree (teacher) with multiple branches (students).

3. Many-to-One (N:1)
Many instances in Entity A relate to one instance in Entity B.

Example:
Entity A: Employee
Entity B: Department
Many employees can work in one department, but a department is their only
assigned home department.
Diagram:
Employee (N) <------> (1) Department

Note: This is logically the reverse of One-to-Many.

4. Many-to-Many (M:N)
Many instances of Entity A can be associated with many instances of Entity B.
Example:
Entity A: Student
Entity B: Course
A student can enroll in multiple courses, and a course can have multiple students.
Diagram:
Student (M) <------> (N) Course

analogy: Like guests and events — many guests attend many events.
(b) Participation Constraints
Participation constraints specify whether all or only some instances of an entity
participate in a relationship.

1. Total Participation
Every instance of the entity must participate in the relationship.
In ER diagrams, total participation is shown by a double line.

Example:
Every employee must belong to a department.
Employee ==belongs to== Department

2. Partial Participation
Some instances of the entity participate in the relationship.
In ER diagrams, partial participation is shown by a single line.
Example:
Not every student is assigned to a club in a school.

(c) Degree of Relationship

The degree of a relationship type is the number of entity types that participate in the
relationship.

1. Binary Relationship Type

Relationship between two entities.

Example:
Customer — places — Order

2. Ternary Relationship Type

Relationship between three entities.
Example:
Doctor — treats — Patient — in — Hospital
Note: Ternary relationships can’t always be broken into binary ones without losing
meaning.

6. Specialization
Specialization is the process of dividing an entity into subclasses based on distinct
characteristics.

Example:
General entity: Employee
Subclasses: Engineer, Manager, Technician
analogy:
Think of a university: "Student" specialized into "Undergraduate" and "Postgraduate"
based on the level of study.

Notation:
In ER diagrams, specialization is shown as a top-down triangle from the parent entity
to subclasses.

7. Generalization
Generalization is the reverse of specialization — it combines multiple entities that
share common attributes into a generalized super-entity.
Example:
Entities: Car, Bike → generalized into Vehicle.
Both Car and Bike have attributes like Registration Number, Owner, etc.

Notation:
Bottom-up triangle pointing to the generalized entity.

8. Attribute Inheritance: When we use specialization or generalization, the subclass

automatically inherits the attributes and relationships of its superclass.
Example:
In specialization:
Superclass: Employee (attributes: EmpID, Name, Salary)
Subclass: Manager (extra attribute: Department)
→ Manager inherits EmpID, Name, Salary from Employee.

Analogy:
Like children inheriting certain traits from parents, but also having unique traits.

9. Characteristics of Specialization & Generalization

Specialization and generalization in ER modeling can be further classified based on

how subclasses are created and what constraints apply to them.

(a) Predicate-Defined Subclass

A subclass is formed based on a condition (predicate) applied to an attribute of the

superclass.
This means that membership of an entity in a subclass is decided by checking
whether it satisfies the predicate.

Example:
Superclass: Employee
Predicate: Salary > 50,000 → Subclass: Senior Employee

Employee (EmpID, Name, Salary)

└── Senior Employee (Salary > 50,000)

Uses: Helps to automatically classify data based on conditions.

(b) Attribute-Defined Specialization
Specialization is determined directly from the value of an attribute without any extra
condition.

Example:
Superclass: Vehicle (attribute: Type)
Type = "Car" → Subclass: Car
Type = "Bike" → Subclass: Bike

This is a special case of predicate-defined specialization where the predicate is

direct equality.

(c) User-Defined Subclass

Subclass membership is decided manually by the database designer or application
user without a fixed condition or attribute.

Example:
In a university, a Student entity can be classified into “Hostel Resident” and “Day
Scholar” based on admin decision.

Advantage:
Flexible, not restricted by attribute values.

(d) Other Constraints on Specialization

Specializations can have constraints that control the membership rules.

1. Disjointness Constraints

Disjoint Specialization:
An entity can belong to only one subclass of the specialization.
Example: A Staff Member can be either a Teacher or an Administrator, not both.

Overlapping Specialization:
An entity can belong to multiple subclasses at the same time.
Example: A Person can be both a Doctor and a Researcher.

2. Completeness Constraints

Total Specialization: All entities of the superclass must belong to at least one
subclass.
Example: Every Employee is either a Permanent Employee or a Temporary
Employee.

Partial Specialization: Some entities in the superclass do not belong to any subclass.
Example: Some Employees are just general employees without specific
classification.

3. Possible Constraints Combination Table

Disjointness Completeness Type

Disjoint + Total Every entity is in exactly one subclass

Disjoint + Partial Some entities in only one subclass, others in none
Overlap + Total Every entity is in at least one subclass, possibly more
Overlap + Partial Some entities in multiple subclasses, others in none

10. Aggregation
Aggregation is an abstraction in ER odelling that treats a relationship itself as an
entity so it can participate in another relationship.
Use case: When we want to express a relationship between a relationship and
another entity.

Example:
Relationship: Teaches (Professor – Course)

Another entity: Department

A department monitors a teaching assignment.
Here, Teaches becomes a higher-level entity for the new relationship.
Diagram (Text Form):

Professor --- Teaches --- Course

\ /
\--- Monitors --- Department

11. Conceptual Design using ER Model (Example)

Conceptual design is the step where we create an ER diagram from real-world
requirements without worrying about technical database storage.

Steps:
Understand requirements from the real world.
Example: “A library has books, members, and loans.”
Identify entities — Book, Member, Loan.
Identify relationships — Member borrows Book (Loan).
Assign attributes — Book (ISBN, Title, Author), Member (ID, Name), Loan
(DateIssued, DateReturned).
Apply constraints — One book can be borrowed by one member at a time.

12. How to Construct an ER Diagram

Steps:

Read and understand requirements — what objects and actions exist.

Identify entities (nouns in the problem statement).
Identify relationships (verbs connecting nouns).
List attributes for each entity and relationship.
Identify keys (unique identifiers for entities).
Determine cardinalities and participation.
Draw the diagram using standard ER notation.
Review and refine — check for redundancy and missing information.

13. ER Diagram for a Car Insurance Company

Requirements:

A set of customers.
Each customer owns one or more cars.
Each car may have zero or many accidents recorded.

Entities:
Customer (CustomerID, Name, Address)
Car (CarID, Model, Year)
Accident (AccidentID, Date, Description)

Relationships:
Owns (Customer – Car) → One-to-Many
Involved_in (Car – Accident) → One-to-Many
Diagram:

Customer (CustomerID, Name, Address)

|
| Owns (1:N)
|
Car (CarID, Model, Year)
|
| Involved_in (0:N)
|
Accident (AccidentID, Date, Description)
Chapter no. 04

1. Entities to Table
Entities in an ER diagram become tables in a database. Each entity’s attributes
become the columns of that table, and the primary key of the entity becomes the
primary key of the table.

(a) Regular Entity Types

Entities that have their own independent existence and primary key are called
regular (strong) entities.

Mapping Rule:
Each regular entity becomes a separate table, with:
All its attributes as columns.
Primary key as defined in ER model.

Example:
Entity: Customer (CustomerID, Name, Email)
→ Table:

CUSTOMER
------------------------
CustomerID (PK) | Name | Email

(b) Weak Entity Types

A weak entity cannot be uniquely identified by its own attributes alone — it depends
on a strong entity.

Mapping Rule:
Create a table for the weak entity.
Include the primary key of the strong entity as a foreign key.
The combination of the weak entity’s partial key and the foreign key becomes the
primary key.

Example:
Weak Entity: Dependent (DependentName, Relationship) depends on Employee
(EmpID).
→ Table:

DEPENDENT
--------------------------------------
EmpID (PK, FK) | DependentName (PK) | Relationship

2. Attributes to Columns of Table

In ER-to-table mapping, attributes become columns. The mapping rules differ
depending on the type of attribute.

(a) Simple Attributes

Definition: Attributes that cannot be divided further.
Mapping: Create a single column for each simple attribute.
Example: Attribute “Name” → Column “Name”.

(b) Composite Attributes

Definition: Attributes made up of multiple subparts.
Mapping: Create separate columns for each sub-attribute.
Example: Address → Street, City, State, Zip.
Instead of one “Address” column, create:
Street | City | State | Zip

(c) Multivalued Attributes

Definition: Attributes that can have multiple values for a single entity.
Mapping: Create a separate table with:
A foreign key referencing the original table.
A column for the multivalued attribute.

Example:
PhoneNumbers for Employee → Separate table:
EMPLOYEE_PHONE
--------------------------
EmpID (FK) | PhoneNumber

(d) Derived Attributes

Definition: Attributes that can be calculated from other attributes.
Mapping: Usually not stored in the database; calculated when needed.
Example: Age is derived from DateOfBirth.

(e) Key Attributes

Definition: Attributes that uniquely identify a record.
Mapping: Becomes the primary key of the table.
Example: CustomerID for CUSTOMER table.

3. Relationships to Table
When converting ER relationships to tables, the approach depends on constraints
and cardinalities.

(a) Foreign Key Approach

Rule: For 1-to-many relationships, add the primary key of one table as a foreign key
in the other table.

Example:
Customer (CustomerID) – Order (OrderID, CustomerID).
(b) Merged Relationship Approach
Rule: For 1-to-1 relationships, merge both entities into a single table if they are
closely related.

Example:
Person (PersonID) – Passport (PassportNumber) can be merged into:

PERSON_PASSPORT
---------------------------
PersonID (PK) | Name | PassportNumber

(c) Cross Reference Approach

Rule: For many-to-many relationships, create a separate relationship table with
foreign keys referencing both entities.

Example:
Student – Course relationship:

ENROLLMENT
--------------------------
StudentID (FK) | CourseID (FK) | DateEnrolled

4. Mapping Inheritance Constraints

When mapping specialization/generalization from ER to tables, there are three main
strategies.

(a) Tables
Separate Table for Each Subclass:
Create a table for the superclass and a table for each subclass with its unique
attributes.

Example:
EMPLOYEE (EmpID, Name)
PERMANENT_EMP (EmpID (PK, FK), Salary)
TEMPORARY_EMP (EmpID (PK, FK), HourlyRate)

(b) Columns
Add a Type Column:
Store subclass type in the superclass table using a discriminator attribute.

Example:
EMPLOYEE (EmpID, Name, EmployeeType, Salary, HourlyRate)

(c) Primary Key

Rule: The primary key of a subclass table is also a foreign key referencing the
primary key of the superclass table.

Benefit: Ensures data consistency and maintains the hierarchy.

Chapter no.05

1. Overview and Role of SQL

SQL : SQL stands for Structured Query Language. It is a special-purpose language
used to communicate with databases. If a database is like a library of information,
then SQL is the “librarian’s language” you use to ask for books, add books, update
book details, or remove books.

Role of SQL in Databases

SQL has several key roles:

1. Data Definition
SQL defines how the database structure (tables, columns, relationships) should be
created.
Example: Creating a table for storing employee details.

2. Data Manipulation
SQL lets you insert, update, delete, and retrieve data.
Example: Adding new employee data or modifying an existing salary.

3. Data Control
SQL manages permissions and security for who can view or modify data.

4. Data Querying
SQL allows you to fetch only the data you need by writing queries.
Example: Show all employees in the "IT" department.

Main Categories of SQL Commands:

Category Purpose Example Commands
DDL (Data Definition Language) Defines database structure CREATE, ALTER,
DROP, TRUNCATE, RENAME
DML (Data Manipulation Language) Manages data inside tables INSERT,
UPDATE, DELETE
DCL (Data Control Language) Manages permissions GRANT, REVOKE
TCL (Transaction Control Language) Controls transactions COMMIT, ROLLBACK
DQL (Data Query Language) Retrieves data SELECT

Analogy:
Think of SQL as the rules for running a restaurant:

DDL: Build the kitchen and dining area.

DML: Cook and serve the food.
DCL: Decide who can enter the kitchen.
TCL: Manage orders and payments.

2. Data Definition Language (DDL)

DDL is a set of SQL commands used to define, modify, and remove the structure of
database objects (tables, views, indexes, schemas, etc.).
Works at the schema level, not on individual rows.
Changes made using DDL are permanent once executed (auto-commit in most
databases).

Key Features of DDL

Creates and changes structure, not data.
Auto-committed (changes are saved immediately).
Cannot be rolled back in most cases.

analogy: If a database is like a cupboard, DDL is about building, reshaping, or

removing the cupboard itself, not about the items (data) inside it.
3. DDL Commands

a. CREATE Statement
Purpose: Used to create new database objects (like tables).
Syntax:

CREATE TABLE table_name (

column1 datatype constraints,
column2 datatype constraints,
...
);

Example:

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
City VARCHAR(50) DEFAULT 'Mumbai'
);

Here:

PRIMARY KEY → Uniquely identifies a record.

NOT NULL → Field must have a value.
CHECK → Ensures condition is met.
DEFAULT → Auto-fills a value if none is provided.
b. ALTER Statement
Purpose: Used to modify the structure of an existing table.
Syntax:

ALTER TABLE table_name action;

Examples:
Add a new column:
ALTER TABLE Students ADD Email VARCHAR(100);

Modify a column datatype:

ALTER TABLE Students MODIFY Age INT;

Drop a column:
ALTER TABLE Students DROP COLUMN City;

c. DROP Statement
Purpose: Permanently deletes a table or other database object.

Syntax:
DROP TABLE table_name;

Example:
DROP TABLE Students;

Note: Deletes both structure and data so no recovery unless you have a backup.
d. RENAME Statement
Purpose: Changes the name of a table or database object.

Syntax:
RENAME TABLE old_name TO new_name;
Example:
RENAME TABLE Students TO CollegeStudents;

e. TRUNCATE Statement
Purpose: Deletes all rows from a table but keeps the table structure.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE Students;
Faster than DELETE because it does not log each row deletion.
Cannot be rolled back in most databases.

4. Domain Integrity Constraints

Domain integrity constraints ensure that the values stored in a column are valid and
consistent according to the rules defined for that domain (data type, range, format,
etc.).
Example: If a column stores “Age”, you can ensure only positive integers are
allowed.

Types of Domain Integrity Constraints:

a. Required Data Constraint / NOT NULL Constraint

Ensures a column cannot have NULL (empty) values.
Used when a field is mandatory.

Syntax:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL
);

Example: Every employee must have a name.

b. CHECK Constraint
Ensures that a column’s value meets a specified condition.
Syntax:

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Age INT CHECK (Age >= 18)
);
Example: An employee’s age must be at least 18.

c. DEFAULT Constraint
Assigns a default value to a column if none is provided.

Syntax:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
City VARCHAR(50) DEFAULT 'Mumbai'
);
Example: If the city is not entered, it will automatically be set to "Mumbai".
Constraint Purpose Example
NOT NULL Prevents null values Name VARCHAR(50) NOT NULL
CHECK Enforces a condition CHECK (Age >= 18)
DEFAULT Auto-assigns a value DEFAULT 'Mumbai'

5. Entity Integrity Constraints:

Introduction to Entity Integrity Constraint

When working with relational databases, the entity integrity constraint ensures that
every row (or tuple) in a table can be uniquely identified.
Without this rule, there would be confusion because two or more rows could appear
to be the same, making it impossible to accurately retrieve, update, or delete a
specific row.

The main rule of entity integrity is: No primary key attribute can be NULL, and all
rows must have a unique primary key value.
Think of a table as a school register — every student must have a unique roll
number; you cannot have two students with the same roll number, and a student
cannot have no roll number at all.

Types of Entity Integrity Constraints

Entity integrity is mainly enforced through two types of constraints in SQL:

(a) Primary Key Constraint

A primary key is a column (or combination of columns) that uniquely identifies each
row in the table.

Rules for Primary Key:

A primary key column cannot have NULL values.
Each value must be unique across all rows.
A table can have only one primary key, but it may consist of multiple columns
(composite primary key).
Example:

CREATE TABLE Students (

Roll_No INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Class VARCHAR(20)
);

Here:
Roll_No is the primary key.
Two students cannot have the same Roll_No.
A row without a Roll_No is not allowed.

Violation Example:
Roll_No Name Age Class
1 Ramesh 15 10A
1 Suresh 16 10B
NULL Mahesh 15 10A

(b) Unique Constraint

A unique constraint also ensures that all values in a column are different, but unlike a
primary key:
A table can have multiple unique constraints.
A column with a unique constraint can have NULL values (but only one NULL in most
databases).
Example:

CREATE TABLE Employees (

Emp_ID INT PRIMARY KEY,
 Email VARCHAR(100) UNIQUE,
Phone_No VARCHAR(15) UNIQUE
);

Here:
Emp_ID is the primary key — no duplicates, no NULL.
Email is unique — two employees cannot have the same email.
Phone_No is unique — two employees cannot share a phone number.

Key Difference Between Primary Key & Unique:

Feature Primary Key Unique Constraint

Allows NULL No Yes (1 NULL allowed in most DBs)
Number per table 1 only Many allowed
Purpose Uniquely identifies each row Prevents duplicate values

->Entity integrity ensures the existence and uniqueness of each record. The primary
key is mandatory for identification, while unique constraints provide additional
uniqueness rules for other columns.

6. Referential Integrity Constraint

Introduction:
Referential integrity is a rule that maintains consistency between related tables. It
ensures that a foreign key value in one table must match an existing primary key
value in another table or be NULL.
This prevents situations where you have a record pointing to something that does
not exist — often called an orphan record.
Think of it as: If a student is assigned to a class, that class must exist in the Classes
table.
If the class is deleted, we must decide what happens to the students in that class.

How Referential Integrity Works

Primary Key → Defined in the parent table (the main table).
Foreign Key → Defined in the child table (the table that refers to the parent).

Example:
CREATE TABLE Departments (
Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(50)
);

CREATE TABLE Employees (

Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Dept_ID INT,
FOREIGN KEY (Dept_ID) REFERENCES Departments(Dept_ID)
);

Here:
Departments is the parent table.
Employees is the child table.
Dept_ID in Employees must match a Dept_ID in Departments.
Referential Integrity Rules
When inserting, updating, or deleting data:

1. Insert Rule: we cannot insert a foreign key value that doesn’t exist in the parent
table.
Example: Adding an employee with Dept_ID = 5 when there is no department with
Dept_ID = 5.

2. Update Rule: we cannot change a primary key in the parent table if it’s being
referenced in the child table (unless you handle it with ON UPDATE actions).

3. Delete Rule: we cannot delete a parent record if there are child records depending
on it (unless you handle it with ON DELETE actions).

ON DELETE / ON UPDATE Options

When defining a foreign key, you can specify what happens when the referenced
record changes or is deleted:

1.CASCADE → Automatically delete/update child rows.

2.SET NULL → Set the foreign key to NULL in child rows.
3.SET DEFAULT → Set the foreign key to its default value.
4.NO ACTION or RESTRICT → Prevent deletion/update if related child records exist.

Example with CASCADE:

CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT,
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
ON DELETE CASCADE
);
If a customer is deleted, all their orders will also be deleted.
->Entity integrity ensures each row is uniquely identifiable (Primary Key, Unique
Constraint).
->Referential integrity ensures relationships between tables remain consistent
(Foreign Key rules).
->Together, they make sure the database remains accurate and reliable.
Chapter no.06

1. Viewing the Structure of a Table

When working with a database, we often need to know how a table is structured —
meaning:
What columns it contains.
What data types each column uses.
Whether a column allows NULL values.
Any default values or constraints (like Primary Key).
In SQL, we can view a table’s structure using commands like DESCRIBE, SHOW
COLUMNS, or the INFORMATION_SCHEMA system table.

Why view the structure?

To understand the format before inserting data.
To check which columns are mandatory.
To avoid errors when writing queries.

Syntax
DESCRIBE table_name;
or
SHOW COLUMNS FROM table_name;

Example
Let’s say we have a table students:

CREATE TABLE students (

id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
marks DECIMAL(5,2),
admission_date DATE );
Viewing the structure:
DESCRIBE students;

Output:

Field Type Null Key Default

id int NO PRI NULL
name varchar(50) NO NULL
age int YES NULL
marks decimal(5,2) YES NULL
admission_date date YES NULL

2. Steps to View All Tables in a Database (With SHOW and DESCRIBE)

Step 1 — See all databases
SHOW DATABASES;

Step 2 — Use the desired database

USE database_name;

Step 3 — View all tables in the current database

SHOW TABLES;
Example Output:

Tables_in_school_db
students
teachers
courses
Step 4 — View structure of a specific table
DESCRIBE students;
or
SHOW COLUMNS FROM students;

3. Common DML Commands (INSERT, DELETE, UPDATE)

DML = Commands that manipulate (change) the data stored in tables, not the table
structure.

a. INSERT Statement
Used to add new records into a table.
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:
INSERT INTO students (id, name, age, marks, admission_date)
VALUES (1, 'Asha', 20, 88.50, '2023-07-01');

b. DELETE Statement
Used to remove existing records from a table.
Syntax:
DELETE FROM table_name
WHERE condition;

->If no WHERE is given, all records are deleted.

Example:
DELETE FROM students
WHERE id = 1;
c. UPDATE Statement
Used to change existing data in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:
UPDATE students
SET marks = 90.00
WHERE name = 'Asha';

4. Difference Between DML and DDL

Aspect DML DDL

Purpose: Manage and manipulate data Define or change table/database structure
Example: INSERT, UPDATE, DELETE, SELECT CREATE, ALTER, DROP,
TRUNCATE
Affects Data inside tables Table/database schema
Can be Rolled Back? Yes (using ROLLBACK) No (usually auto-committed)
Execution Result Changes the records Changes the structure

5. Transaction Control with DML (COMMIT, ROLLBACK)

Transactions: A set of SQL statements executed together as a single unit.
DML commands can be combined with Transaction Control Language (TCL)
commands.
a. COMMIT
Saves all changes permanently to the database.
After COMMIT, you cannot undo the changes.

Example:
UPDATE students SET marks = 95 WHERE id = 2;
COMMIT;

b. ROLLBACK
Undoes changes since the last COMMIT.
Useful when an incorrect update/delete has been made.

Example:
UPDATE students SET marks = 20 WHERE id = 2;
ROLLBACK; -- This will undo the update

->Always use WHERE with UPDATE and DELETE to avoid affecting all rows.
->Use ROLLBACK before COMMIT to undo changes.
->DML without COMMIT will not be permanent in transactional systems until
committed.