What is a Database?

A database is an organized collection of structured information stored electronically, typically

managed by a Database Management System (DBMS)

Data -- Raw, unprocessed facts and figures (e.g., "16", "cimage", "2025-06-27")

BaseThe foundation or structure for storing data

A Database Management System (DBMS) is software designed to efficiently store, organize,

retrieve, and manage data in databases.

Advantages of a Database Management System (DBMS)

i) Improved Data Integrity and Consistency:

A DBMS enforces rules and constraints to ensure that data remains accurate, consistent, and
reliable across the entire organization. This minimizes errors and eliminates data redundancy and
inconsistency, which are common in traditional file-based systems.

ii) Enhanced Data Security:

DBMSs provide robust security features such as user authentication, access controls, and
encryption. Only authorized users can access or modify data, greatly reducing the risk of data
breaches and unauthorized access.
iii) Efficient Data Access and Sharing

A DBMS allows multiple users to access and share data simultaneously, supporting collaboration and
efficient workflows. It enables fast and flexible data retrieval through powerful query languages.

iv)Minimized Data Redundancy

Centralized storage reduces unnecessary duplication of data, saving storage space and simplifying
data management.

v) Automated Backup and Recovery

DBMS includes built-in backup and recovery mechanisms, ensuring data can be restored in the event
of hardware failure, accidental deletion, or other disasters.

History of DBMS

 Charles W. Bachman designed the first DBMS, the Integrated Data Store (IDS), in 1960.
 IBM IMS (Information Management System) was first introduced in 1968 as a database.
 In 1970, Edgar F. Codd proposed the relational model, which organized data in tables
(relations).
 1990s Object-oriented DBMS;
 2000s– Now NoSQL and cloud databases
File system:

i) Only one user can access data at a time.

ii) File systems provide less security in comparison to DBMS.

iii) It doesn't provide inbuilt mechanism for backup and recovery of data if it is lost.

iv) Redundant data can be present in a file system.

v) It is less expensive than DBMS.

DBMS

i) Multiple users can access data at a time.

ii) DBMS has more security mechanisms as compared to file systems.

iii)It provides backup and recovery of data even if it is lost.

iv) In DBMS there is no redundant data.

v)It has a comparatively higher cost than a file system.

DBMS VS RDBMS

DBMS:
 i) Stores data as files (navigational or hierarchical format)
ii) No relationships between data
iii) Usually supports a single user
iv) Low security
v) Suitable for small quantities of data
vi) Ex- File systems, XML, etc.

RDBMS:

i) Stores data in tabular form (tables with rows and columns)

ii) Tables are related to each other via keys
iii) Supports multiple users concurrently
iv) Multiple levels of security
v) Handles large amounts of data efficiently
vi) Ex- MySQL, Oracle, SQL Server, PostgreSQL, Microsoft Access

What is RDBMS?

An RDBMS (Relational Database Management System) is software that enables the creation,
management, and use of relational databases, where data is stored in tables made up of rows and
columns. These tables, often called "relations,"

Key Features of RDBMS

Tabular Structure: Data is organized into tables (relations) with rows (records) and columns
(fields), making it easy to store, retrieve, and manage related data.

Relationships: Tables can be linked to each other using keys (primary and foreign keys), allowing
complex data relationships without redundancy.

SQL Support: Most RDBMSs use Structured Query Language (SQL) for querying, updating, and
managing data.

Examples of RDBMS

Popular RDBMS software includes MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL,
and IBM Db2.

In Relational Database Management Systems (RDBMS), keys are crucial for ensuring data integrity
and enabling efficient access to data. They uniquely identify rows (records) in a table and define
relationships between tables.

Here are the main types of keys in RDBMS:

1. Primary Key

i) A column or set of columns that uniquely identifies each record in a table.

ii) Must contain unique values.

iii) Cannot contain NULL values.

iv) There can be only one primary key in a table.

Example:

In a Students table, student_id can be a primary key.

CREATE TABLE Students (

student_id INT PRIMARY KEY,

name VARCHAR(100)

);

2. Candidate Key

i) A set of attributes that can uniquely identify a record in a table.

ii) There can be multiple candidate keys, but only one is chosen as the primary key.

Example:

In an Employees table, both employee_id and email may be unique. So both are candidate keys.

3. Alternate Key

A candidate key that is not chosen as the primary key.

Example:

If employee_id is the primary key, then email becomes the alternate key.
4. Composite Key (or Compound Key)

i) A key made up of two or more columns that together uniquely identify a row.

ii) Used when a single column is not sufficient.

Example:

In an Enrollment table:

(student_id, course_id) can be a composite primary key.

CREATE TABLE Enrollment (

student_id INT,

course_id INT,

PRIMARY KEY (student_id, course_id)

);

5. Foreign Key

A column or set of columns in one table that refers to the primary key in another table.

Used to establish relationships between tables.

Example:
CREATE TABLE Orders (

order_id INT PRIMARY KEY,

customer_id INT,

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);

6. Super Key

A set of one or more attributes that uniquely identifies a record in a table.

Every candidate key is a super key, but not every super key is a candidate key.

Example:

In a table with employee_id and email, {employee_id, email} is a super key.

DDL Commands in SQL (Data Definition Language)

DDL is used to define, modify, or delete the structure of database objects like tables, schemas,
indexes, and more.

DDL Commands:

CREATE

ALTER
DROP

TRUNCATE

RENAME

Examples

1. CREATE

Used to create a new table.

CREATE TABLE Students (

student_id INT PRIMARY KEY,

name VARCHAR(100),

age INT

);

2. ALTER

Used to modify an existing table (add, modify, or delete columns).

ALTER TABLE Students ADD email VARCHAR(100);

ALTER TABLE Students MODIFY age SMALLINT;

ALTER TABLE Students DROP COLUMN email;

3. DROP

Used to delete a table (or other object) permanently.

Deletes the table structure and all its data. Cannot be rolled back.

4. TRUNCATE

Used to remove all rows from a table but keep the table structure.

TRUNCATE TABLE Students;

Faster than DELETE but cannot be rolled back in most DBMSs.

5. RENAME

Used to rename a table or column (syntax varies by DBMS).

RENAME TABLE Students TO Learners;

The three-level architecture of a DBMS:

The three-level architecture of a DBMS is a fundamental design that separates a database system
into three distinct layers, each providing a different view or abstraction of the data. This
structure enhances data independence, security, and flexibility. The three levels are:
External Level (View Level)

Conceptual Level (Logical Level)

Internal Level (Physical Level)

i) External Level (View Level):

The topmost layer.

Provides different customized views of the database to various users.

Users interact with this level and are unaware of the data storage or structure details.

ii) Conceptual Level (Logical Level):

The middle layer.

Describes the logical structure of the entire database, including relationships, constraints, and
schema.

Managed by the database administrator (DBA).

iii) Internal Level (Physical Level):

The lowest layer.

Deals with the physical storage of data on hardware.

Specifies how data is stored, indexed, and accessed on storage devices.

In MySQL, constraints are rules defined on table columns or whole tables to ensure data
integrity—they prevent invalid, duplicate, or inconsistent data from being stored.
MySQL constraints:
1. NOT NULL

Forces a column to always have a value (no NULLs allowed).

CREATE TABLE users (

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

age INT

);

2. UNIQUE

Ensures all values in a column (or group of columns) are distinct.

CREATE TABLE people (

person_id INT,

email VARCHAR(100) UNIQUE

);
3. PRIMARY KEY

A combination of NOT NULL + UNIQUE, used to uniquely identify each row. Only one per table.

CREATE TABLE customers (

cust_id INT NOT NULL PRIMARY KEY,

name VARCHAR(50)

);

4. FOREIGN KEY

Maintains referential integrity between tables.

CREATE TABLE customers (

cust_id INT NOT NULL PRIMARY KEY,

name VARCHAR(50)

CREATE TABLE orders (

order_id INT NOT NULL PRIMARY KEY,

cust_id INT,
 FOREIGN KEY (cust_id) REFERENCES customers(cust_id)

);

5. CHECK

Validates column values against a condition.

CREATE TABLE employees (

emp_id INT PRIMARY KEY,

age INT,

CHECK (age >= 18 AND age <= 65)

);

6.DEFAULT

Assigns a default value if the column is not specified.

CREATE TABLE items (

item_id INT PRIMARY KEY,

CITY VARCHAR(30) DEFAULT ‘PATNA’

);

AUTO_INCREMENT
Automatically increments a numeric column—typically used with PRIMARY KEY.

CREATE TABLE products (

product_id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100)

);

create table orders(id int primary key AUTO_INCREMENT, name varchar(30), cust_id int,
FOREIGN key (cust_id) REFERENCES customer(ID));

Transaction Management in DBMS

A transaction is a single unit of work that performs one or more operations on a database.

Example: Transferring money from one account to another:

Deduct from Account A

Add to Account B

This should either fully happen or not happen at all.

ACID properties in DBMS

Atomicity: All operations in a transaction must be completed; if not, none are applied (all or
nothing).
Consistency: After a transaction, the database must remain in a consistent state, satisfying all
rules and constraints.

Isolation: Concurrent transaction execution should not interfere, ensuring transactions appear
independent to users.

Durability: Once a transaction is committed, changes are permanent, even if the system fails.

Atomicity Examples

1. Banking Transaction: A customer transfers money from one account to another. If the debit
operation succeeds but the credit operation fails, the transaction should be rolled back to maintain
atomicity.

2. Order Processing: A customer places an order, and the system updates the inventory and
processes the payment. If the payment processing fails, the inventory update should be rolled back
to maintain atomicity.

Consistency Examples

1. Student Information: A database stores student information, including their age. If a transaction
attempts to insert a student with an age of -10, the database should reject the transaction because
the age is invalid.
2. Product Pricing: A database stores product prices, and a transaction attempts to update the
price to a negative value. The database should reject the transaction because the price is invalid.

Isolation Examples

1. Concurrent Updates: Two transactions, T1 and T2, are updating the same customer's address.
Isolation ensures that T1 sees either the old address or the new address updated by T2, but not a
partially updated address.

2. Read-Only Transaction: A read-only transaction is retrieving data while another transaction is

updating the same data. Isolation ensures that the read-only transaction sees a consistent view of
the data.

Durability Examples

1. System Crash: A transaction updates a customer's account balance and commits the changes. If
the system crashes immediately after, the changes should be persisted to disk and survive the
crash.

2. Power Failure: A transaction updates a database and commits the changes. If a power failure
occurs immediately after, the changes should be persisted to disk and survive the power failure.

WHERE clause

The WHERE clause in SQL is used to filter records in a query based on specific conditions. It
allows you to retrieve only the rows that meet the specified criteria.
Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Examples:

Filter by a single condition:

SELECT *

FROM employees

WHERE age > 30;

This retrieves all employees whose age is greater than 30.

Using multiple conditions with AND:

SELECT *

FROM employees

WHERE age > 30 AND department = 'HR';

This retrieves employees older than 30 who work in the HR department.

Using OR for alternative conditions:

SELECT *

FROM employees

WHERE age > 30 OR department = 'HR';

This retrieves employees who are either older than 30 or work in the HR department.

Using LIKE for pattern matching:

SELECT *

FROM employees

WHERE name LIKE 'A%';

This retrieves employees whose names start with the letter "A".

Using IN for multiple values:

SELECT *

FROM employees

WHERE department IN ('HR', 'Finance', 'IT');

This retrieves employees working in HR, Finance, or IT departments.

Using BETWEEN for range filtering:

SELECT *

FROM employees

WHERE salary BETWEEN 30000 AND 50000;

This retrieves employees with salaries between 30,000 and 50,000.

Using IS NULL or IS NOT NULL:

SELECT *

FROM employees

WHERE manager_id IS NULL;