Database Management System

Data – Data refers to raw facts and figures that are stored and managed electronically in
databases.
🔹 Difference Between Data and Information:
Data Information
Raw facts Processed data
No meaning alone Meaningful and useful
Example: "80, "Aman scored 80 in Math and 90 in Science"
90"

⭕Important data requirements in software applications using Facebook as an example.

Here's an explanation of each requirement shown in the table:
✅ 1. Integrity
 Meaning: Data must be correct, accurate, and consistent.
 Example: If you enter your country in your Facebook profile, it should be a valid country
name, not something incorrect like "1234" or "xyz".
 Why important? It avoids garbage or misleading data in the system.

✅ 2. Availability
 Meaning: Data should be accessible at all times when needed.
 Example: You should be able to open Facebook anytime and view your profile and posts.
 Why important? Ensures users don’t face downtime or data loss.

✅ 3. Security
 Meaning: Data should be protected from unauthorized access.
 Example: Only your friends should be able to view your posts — not strangers or
hackers.
 Why important? Prevents data leaks and protects privacy.
✅ 4. Independent of Application
 Meaning: Data should be usable across multiple platforms/devices.
 Example: Whether you access Facebook from your Android app or a laptop browser, the
same data (posts, messages) should be available.
 Why important? Allows seamless user experience across devices.

✅ 5. Concurrent
 Meaning: Multiple users can access data at the same time without conflict.
 Example: All your friends should be able to see your post as soon as you publish it, even
if they open it at the same time.
 Why important? Supports real-time updates and social interaction.

Limitations of the file-based data management system — a method where data is stored in
flat files (like .txt, .csv, or .dat) and accessed using programs written in languages like C, Java,
or Python.
1. Dependency of Program on Physical Structure of Data
2. Complex Process to Fetch Data
3. Loss of Data on Simultaneous Access
4. Inability to Give Access Based on Record (Security)
5. Data Redundancy
These problems show why traditional file-based systems are inefficient for managing large or
complex data. That's why DBMS (Database Management System) was introduced — to
handle:
 data consistency
 easy retrieval
 multi-user support
 security
 reducing redundancy
📘What is a Database?
A database is:
 A shared collection of logically related data.
 Includes descriptions (metadata) of the data (like data types, relationships).
 Designed to meet the information needs of users/organizations.
✅ Example: A student database in a university contains student records, course enrollments, and
grades.

💻What is a DBMS (Database Management System)?

A DBMS is:
 Software that allows users to define, create, update, and secure a database.
 It ensures data integrity, concurrency, and security.
 Requires high-performance hardware and can be costly.
✅ Popular DBMS examples: Oracle, MySQL, PostgreSQL, Microsoft SQL Server.
✅ Core Functions of a DBMS
Function What it Means

Data Storage Stores data in structured formats (tables, rows, columns).

Data Retrieval (Querying) Allows users to run queries (e.g., using SQL) to fetch specific data.

Data Insertion, Update, Let’s users add, modify, or remove data easily.
Deletion

Data Security Controls access to data using authentication and authorization.

Data Integrity Enforces rules (like primary/foreign keys) to ensure valid, consistent data.

Concurrency Control Allows multiple users to access and update data at the same time safely.

Backup and Recovery Supports backing up data and restoring it after crashes or failures.

Data Independence Separates data from application logic, so changes in structure don’t break
apps.

Transaction Management Ensures operations are atomic, consistent, isolated, and durable (ACID).

🔁Application Program and SQL

  Application programs interact with the database using SQL (Structured Query
Language).
 These programs send SQL commands to:
o Insert data
o Fetch/query data
o Update or delete records
✅ Example in SQL:
sql
SELECT * FROM students WHERE age > 20;

🌐Client-Server Architecture
 A client (user or software) sends a request.
 A server responds by processing that request and returning data or a service.
 The server hosts the database and controls access.
 The client could be a web browser, mobile app, or desktop software.
✅ Real-world example:
When you open Facebook on your phone (client), it sends a request to Facebook’s servers to
load your profile, feed, etc.

Types of Database–
1. Relational Database (RDBMS)
 Stores data in tables (rows and columns)
 Uses SQL for querying
 Example: MySQL, PostgreSQL, Oracle, SQL Server
📌 Best for structured data and complex queries.

2. NoSQL Database
 Non-relational; handles unstructured or semi-structured data
 Types:
o Document (e.g., MongoDB)
o Key-Value (e.g., Redis)
o Column-Family (e.g., Cassandra)
o Graph (e.g., Neo4j)
📌 Best for big data, scalability, and flexibility.

3. Hierarchical Database
  Data is organized in a tree-like structure (parent-child)
 Example: IBM IMS
📌 Used in early mainframe systems.

4. Network Database
 More flexible than hierarchical; allows many-to-many relationships
 Example: Integrated Data Store (IDS)
📌 Used in older, complex systems.

✅ Relational Databases –
 A Relational Database stores data in relations, which are essentially tables.
  Each relation (table) must have a unique name.
A relation consists of:
oRows (called tuples) → represent records
o Columns (called attributes) → represent fields or properties
 Cardinality → refers to the number of rows (tuples) present in a relation (table) in a
relational database.
 Degree of a Relation → The degree of a relation refers to the number of attributes
(columns) in a table.
🔹 Example:
Student Table
Student_ID Name Age Course
101 Aman 20 B.Tech
102 Priya 21 BCA
103 Rahul 22 B.Sc
 Table name: Student
 Attributes: Student_ID, Name, Age, Course
 Tuples: Each row like (101, Aman, 20, B.Tech)
 Cardinality: 3
 Degree of relation: 4
 Domain (Age): e.g. 18 to 25

Integrity –
It refers to the accuracy, consistency, and reliability of the data stored in the database. It
ensures that the data adheres to certain rules and constraints, preserving its quality and
trustworthiness throughout its lifecycle.
Types of Integrity in RDBMS:
1. Entity Integrity
o Ensures that each table has a primary key and that the key is unique and not
null.
o ✅ Example: A students table must have a unique student_id for each record.
2. Referential Integrity
o Maintains consistency between related tables using foreign keys.
o ✅ Example: If orders.customer_id references customers.customer_id, the
referenced customer_id must exist in the customers table.
3. Domain Integrity
o Ensures that data values are valid according to the defined domain (type, format,
range).
o ✅ Example: A column age should only accept integers between 0 and 120.
4. User-Defined Integrity
o Involves custom rules defined by the user or business logic.
o ✅ Example: In a banking database, a rule that an account balance cannot be
negative.

Key Type Purpose Uniqueness Allows Defined In Example

NULLs?
 Candidat Potential keys that ✅ Yes ❌ No Main table email,
e Key can uniquely student_id in
identify a record students table
Primary Selected candidate ✅ Yes ❌ No Main table student_id in
Key key to uniquely students table
identify each
record
Foreign Establishes ❌ No ✅ Yes Child/ department_id
Key relationship (usually) referencing table in students
between two tables referencing
departments

🔁 Relationships:
 A Primary Key is chosen from one of the Candidate Keys.
 A Foreign Key refers to a Primary Key in another table to maintain referential
integrity.

📌 Example:
students table:
student_id (PK) email (Candidate) department_id (FK)
101 a@domain.com D01
departments table:
department_id (PK) department_name
D01 Computer Science

Normalization in DBMS
Definition:
Normalization is the process of organizing data in a relational database to reduce redundancy
and improve data integrity. It involves dividing large tables into smaller ones and defining
relationships among them to ensure that data is stored logically and consistently.
The goals of normalization are:
 Eliminate redundant data.
 Ensure data dependencies make sense.
 Simplify the database structure.

Functional Dependency
Definition:
A functional dependency (FD) is a relationship between attributes in a relation. If attribute A
determines attribute B, we say B is functionally dependent on A, written as:
A→B
This means, for each value of A, there is exactly one value of B.

Types of Functional Dependencies

1. Fully Functional Dependency
 A non-prime attribute is fully functionally dependent on the entire primary key and not
just a part of it.
 This is essential for Second Normal Form (2NF).
Example:
In a relation Student(CourseID, StudentID, StudentName), if the primary key is (CourseID,
StudentID):
 StudentName is fully dependent on StudentID only → ❌ Not fully functional.
 If StudentName depends on the whole key (CourseID, StudentID), then it's a fully
functional dependency.

2. Partial Dependency
 A non-prime attribute depends only on part of a composite primary key.
  Violates 2NF.
Example:
In the above example, if StudentName depends only on StudentID, not on the whole key
(CourseID, StudentID), then it is a partial dependency.
3. Transitive Dependency
 A non-prime attribute depends on another non-prime attribute, which in turn depends on
the primary key.
 Violates Third Normal Form (3NF).
Example:
In a relation Employee(EmpID, DeptID, DeptLocation):
 EmpID → DeptID (each employee belongs to one department)
 DeptID → DeptLocation (each department is in one location)
 Thus, EmpID → DeptLocation is a transitive dependency.

🔰 Normal Forms in DBMS

Definition:
Normal forms are rules or standards used to determine the level of normalization in a relational
database. The aim is to reduce data redundancy and improve data integrity.
✅ 1. First Normal Form (1NF)
Rule:
 All values in a column must be atomic (indivisible).
 Each column should contain only one value per row.
 No repeating groups or arrays.
Example (Not in 1NF):
Student(ID, Name, Subjects)
(1, 'Aman', 'Math, English')
Convert to 1NF:
Student(ID, Name, Subject)
(1, 'Aman', 'Math')
(1, 'Aman', 'English')

✅ 2. Second Normal Form (2NF)

Rule:
 Must be in 1NF.
 No partial dependency: A non-prime attribute must depend on the whole composite
primary key (not just a part of it).
Applies only when there is a composite primary key.
Example:
Enrollment(StudentID, CourseID, StudentName)
PK: (StudentID, CourseID)
Here, StudentName depends only on StudentID → ❌ Partial Dependency.
Convert to 2NF:
Split into:
 Student(StudentID, StudentName)
 Enrollment(StudentID, CourseID)
✅ 3. Third Normal Form (3NF)
Rule:
 Must be in 2NF.
 No transitive dependency: A non-prime attribute should not depend on another non-
prime attribute.
Example:
Employee(EmpID, DeptID, DeptName)
PK: EmpID
FDs: EmpID → DeptID, DeptID → DeptName
⇒ Transitive: EmpID → DeptName
Convert to 3NF:
 Employee(EmpID, DeptID)
 Department(DeptID, DeptName)

✅ 4. Boyce-Codd Normal Form (BCNF)

Rule:
 Must be in 3NF.
 For every functional dependency A → B, A should be a super key.
Example:
Schedule(Course, Instructor, Room)
FDs: Course → Room
Instructor → Room
But neither Course nor Instructor is a super key ⇒ violates BCNF
Convert to BCNF by decomposing into relations where all FDs have a super key as
determinant.

✅ 5. Fourth Normal Form (4NF)

Rule:
 Must be in BCNF.
 No multi-valued dependency.
Example:
Employee(EmpID, Skill, Language)
One employee can have many skills and speak many languages ⇒ violates 4NF
Convert to 4NF:
 EmpSkill(EmpID, Skill)
 EmpLanguage(EmpID, Language)

✅ 6. Fifth Normal Form (5NF or PJ/NF)

Rule:
 Must be in 4NF.
 No join dependency – The relation should not lose any information after decomposition
into smaller relations.
This is rare in practice but important in complex systems.

🌐 SQL – Structured Query Language

SQL is used to interact with relational databases. It is divided into several sublanguages
based on the type of operation they perform.
1. 🛠 DDL – Data Definition Language
 Purpose: Defines and modifies the structure of database objects.
 Commands:
o CREATE – Create new tables, views, etc.
o ALTER – Modify existing structures.
o DROP – Delete objects.
o TRUNCATE – Remove all records from a table, structure remains.
 Effect: Changes are recorded in the data dictionary (metadata store).

2. ✏️DML – Data Manipulation Language

 Purpose: Deals with manipulation (CRUD operations) of data within the tables.
 Commands:
o SELECT – Retrieve data.
o INSERT – Add new data.
o UPDATE – Modify existing data.
o DELETE – Remove data.
 Effect: Affects the actual data in the tables.

3. 🔐 DCL – Data Control Language

  Purpose: Controls access to data in the database.
 Commands:
o GRANT – Give privileges to users.
o REVOKE – Remove privileges.
 Effect: Manages security and permissions.
4. 🔄 TCL – Transaction Control Language
 Purpose: Manages transactions (a sequence of SQL operations treated as a single unit).
 Commands:
o COMMIT – Save changes permanently.
o ROLLBACK – Undo changes.
o SAVEPOINT – Set a point within a transaction to roll back to.
 Effect: Controls consistency and atomicity of transactions.

SQL Data types –

🔠 CHAR(n) – Fixed-Length Character Data Type
Property Details
Useful for Storing character strings of a fixed (pre-determined) length.
Storage Size Allocates space for exactly n characters, regardless of actual
input length.
Storage If the input is shorter than n, trailing spaces are added to fill up
Characteristic the size.
Maximum Size 2000 bytes
Example CHAR(10) storing "Hello" will take up 10 bytes, adding 5 spaces
at the end.
Alternate Name CHARACTER(n)
🔤 VARCHAR2(n) – Variable-Length Character Data Type
Property Details
Useful for Storing character strings of variable length.
Storage Size Uses space for actual length of input + 2 bytes overhead (to store
length info).
Storage No trailing spaces are added.
Characteristic
Maximum Size 4000 bytes
Example VARCHAR2(10) storing "hello" uses 7 bytes (5 for text + 2 for
length).
Alternate Name CHARACTER VARYING(n)

SQL Integer Data Types: SMALLINT, INTEGER, and INT

In SQL, the following data types are commonly used to store whole numbers (integers):
 SMALLINT
 INTEGER
 INT (synonym for INTEGER)
🔸 Standard SQL Behavior
In standard SQL and in many RDBMS (like MySQL, PostgreSQL, SQL Server), these types
differ in range and storage size:
Type Typical Range Storage Size
SMALLINT -32,768 to 32,767 2 bytes
INTEGER / INT -2,147,483,648 to 2,147,483,647 4 bytes
SQL Non-integral Data types:
🔢 Definition
 NUMBER(p, s): A numeric data type where:
o p (Precision) = Total number of significant digits (both sides of the decimal
point).
o s (Scale) = Number of digits to the right of the decimal point.

📊 Breakdown of Examples in the Image

1. NUMBER(3)
 Only precision is specified (3), scale defaults to 0.
 Can store a number with up to 3 digits, no decimals.
 ✅ Example: 999 is valid, 999.9 is not.
2. NUMBER(3, 1)
  Precision = 3, Scale = 1
 Can store 2 digits before the decimal and 1 digit after.
 ✅ Example: 99.9 is valid, 999.9 is not.
3. NUMBER(3, 2)
 Precision = 3, Scale = 2
 Can store 1 digit before the decimal and 2 digits after.
 ✅ Example: 9.99 is valid, 99.99 is not.
4. NUMBER(3, 3)
 Precision = 3, Scale = 3
 All digits are after the decimal.
 ✅ Example: 0.999 is valid, 9.999 is not.

⚠️Key Rule
The number of total digits (including both sides of the decimal) must not exceed the
precision.

Using NUMBER Data Type:

Data Type Stored Explanation
Value
NUMBER 121.79 No precision or scale is specified, so Oracle allows up to 38
significant digits. The value is stored as-is.
NUMBER(3) 122 Precision = 3, Scale defaults to 0 → only 3 digits total, no
decimals → Oracle rounds the value to 122.
NUMBER(5,2 121.79 Precision = 5, Scale = 2 → 3 digits before decimal, 2 digits after.
) Since 121.79 fits, it's stored without change.
NUMBER(6,1 121.8 Precision = 6, Scale = 1 → 5 digits before decimal, 1 digit after.
) So 121.79 is rounded to 1 decimal → 121.8.
NUMBER(4,2 ❌ error Only 2 digits before decimal are allowed (4 - 2 = 2), but 121 has
) 3 digits. Result: Error due to overflow, value can’t be stored.

📋 Specialized SQL Data Types Summary

Data Type Used For Size/Precision Example Usage
DATE Storing dates (without Up to second '19-JUN-2025' (Date of
fractional seconds) precision Birth)
TIMESTAMP Date and time with Up to 9 digits '2025-06-19
fractional seconds (nanoseconds) 14:30:45.123456789'
CLOB Large text data Up to 4 GB of Storing blog articles,
character data XML, JSON
BLOB Large binary data Up to 4 GB of binary Storing images, videos,
data PDF files