UNIT 01

DATABASE SYSTEMS

1. What is a Database System?

A database system is a combination of:

Database: A collection of related data that is organized so that it can

be easily accessed, managed, and updated.

Database Management System (DBMS): Software that allows users to

define, create, maintain, and control access to the database.

Database: Organized collection of data.

DBMS (Database Management System): Software that manages the

database and allows users and applications to interact with it.

Users: People or applications that use the system.

2. Components of a Database System

1.Database – The actual data stored in a structured way (tables, files,

etc.)
2.DBMS – Software that manages the data (e.g., MySQL, Oracle,
SQL Server)
3.Users – People who interact with the system (admins, programmers,
end users)
4.Hardware – Physical devices (server, storage, network)
5.Application programs – Software tools that help users interact with
the DBMS

3. Key Functions of a DBMS

1. Storing and managing large amounts of data
2. Providing data security and integrity
3. Allowing multiple users to access data concurrently
4. Supporting queries and reports
5. Enabling backup and recovery

Example
 UNIT 01
Imagine a university database system:

1. The database contains tables for students, courses, grades, etc.

2. The DBMS (e.g., MySQL) allows staff to query data (e.g., "List all
students with grade A").
3. Users include students (view grades), faculty (enter marks), admin
(manage system).
4. Simple Diagram

┌────────────┐

│ Users / │

│ Applications│

└─────┬──────┘

┌────────────┐

│ DBMS │ ← Manages data access

└─────┬──────┘

┌────────────┐

│ Database │ ← Actual data stored (tables, files)

└────────────┘

DATA vs INFORMATION

Data refers to raw, unprocessed facts and figures, while information is

the processed, organized, and structured form of that data, providing context
 UNIT 01
and meaning. Essentially, data is the building block, and information is the
meaningful outcome derived from that data.

5. Data:

Definition:Raw, unorganized facts, figures, symbols, or descriptions that

have no inherent meaning or context.

Examples:A student's test score, a customer's purchase amount, a

timestamp, an employee's ID number.

Characteristics:Often stored in a database, but without a specific purpose

or interpretation.

6. Information:

Definition: Data that has been processed, organized, and structured to

convey meaning and significance.

Examples: An average test score of a class, a customer's purchase history, a

report summarizing sales data, a customer's contact information.

Characteristics: Provides context, enabling decision-making and

knowledge acquisition.

INTRODUCING THE DATABASE

7. What is a DBMS?

A DBMS is a system that allows users to create, modify, and query

databases while ensuring data integrity, security, and efficient data access.
Unlike traditional file systems, DBMS minimizes data redundancy,
prevents inconsistencies, and simplifies data management with features like
concurrent access and backup mechanisms. It organizes data
into tables, views, schemas, and reports, providing a structured approach to
data management.

Example:A university database can store and manage student information,

faculty records, and administrative data, allowing seamless retrieval, insertion,
and deletion of information as required.
 UNIT 01
8. Key Features of DBMS

Data Modeling: Tools to create and modify data models, defining the
structure and relationships within the database.

Data Storage and Retrieval: Efficient mechanisms for storing data and
executing queries to retrieve it quickly.

Concurrency Control: Ensures multiple users can access the database

simultaneously without conflicts.

Data Integrity and Security: Enforces rules to maintain accurate and

secure data, including access controls and encryption.

Backup and Recovery: Protects data with regular backups and enables
recovery in case of system failures.

9. Types of DBMS

There are several types of Database Management Systems (DBMS), each

tailored to different data structures, scalability requirements, and application
needs. The most common types are as follows:

1. Relational Database Management System (RDBMS)

RDBMS organizes data into tables (relations) composed of rows and

columns. It uses primary keys to uniquely identify rows and foreign keys to
establish relationships between tables. Queries are written in SQL (Structured
Query Language), which allows for efficient data manipulation and retrieval.

Examples: MySQL, Oracle, Microsoft SQL Server and Postgre SQL.

2. No SQL DBMS

No SQL systems are designed to handle large-scale data and provide high
performance for scenarios where relational models might be restrictive. They
store data in various non-relational formats, such as key-value
pairs, documents, graphs, or columns. These flexible data models enable rapid
scaling and are well-suited for unstructured or semi-structured data.

Examples: MongoDB, Cassandra, DynamoDB and Redis.

 UNIT 01
3. Object-Oriented DBMS (OODBMS)

OODBMS integrates object-oriented programming concepts into

the database environment, allowing data to be stored as objects. This approach
supports complex data types and relationships, making it ideal for applications
requiring advanced data modeling and real-world simulations.

Examples: ObjectDB, db4o.

10.Database Languages

Database languages are specialized sets of commands and

instructions used to define, manipulate, and control data within a database.
Each language type plays a distinct role in database management, ensuring
efficient storage, retrieval, and security of data. The primary database languages
include:

1. Data Definition Language (DDL)

DDL is the short name for Data Definition Language, which deals with
database schemas and descriptions, of how the data should reside in the database.

CREATE: to create a database and its objects like (table, index, views,
store procedure, function, and triggers)

ALTER: alters the structure of the existing database

DROP: delete objects from the database

TRUNCATE: remove all records from a table, including all spaces

allocated for the records are removed

COMMENT: add comments to the data dictionary

RENAME: rename an object

2. Data Manipulation Language (DML)

DML focuses on manipulating the data stored in the database, enabling

users to retrieve, add, update, and delete data.

SELECT: retrieve data from a database

 UNIT 01
INSERT: insert data into a table

UPDATE: updates existing data within a table

DELETE: Delete all records from a database table

MERGE: UPSERT operation (insert or update)

CALL: call a PL/SQL or Java subprogram

EXPLAIN PLAN: interpretation of the data access path

LOCK TABLE: concurrency Control

3. Data Control Language (DCL)

DCL commands manage access permissions, ensuring data security by

controlling who can perform certain actions on the database.

GRANT: Provides specific privileges to a user (e.g., SELECT, INSERT).

REVOKE: Removes previously granted permissions from a user.

4. Transaction Control Language (TCL)

TCL commands oversee transactional data to maintain consistency,

reliability, and atomicity.

ROLLBACK: Undoes changes made during a transaction.

COMMIT: Saves all changes made during a transaction.

SAVEPOINT: Sets a point within a transaction to which one can later roll
back.

5. Data Query Language (DQL)

DQL is a subset of DML, specifically focused on data retrieval.

SELECT: The primary DQL command, used to query data from the
database without altering its structure or contents.

11.Paradigm Shift from File System to DBMS

 UNIT 01
Before the advent of modern Database Management Systems (DBMS),
data was managed using basic file systems on hard drives. While this approach
allowed users to store, retrieve, and update files as needed, it came with
numerous challenges.

A typical example can be seen in a file-based university management

system, where data was stored in separate sections such as Departments,
Academics, Results, Accounts, and Hostels. Certain information like student
names and phone numbers was repeated across multiple files, leading to the
following issues:

1. Redundancy of data

When the same data exists in multiple places, any update must
be manually repeated everywhere. For instance, if a student changes their
phone number, it must be updated across all sections. Failure to do so leads
to unnecessary duplication and wasted storage.

2. Inconsistency of Data

Data is said to be inconsistent if multiple copies of the same data do not

match each other. If the Phone number is different in Accounts Section and
Academics Section, it will be inconsistent. Inconsistency may be because of
typing errors or not updating all copies of the same data.

3. Complex Data Access

A user should know the exact location of the file to access data, so the
process is very cumbersome and tedious. If the user wants to search the student
hostel allotment number of a student from 10000 unsorted students’ records, how
difficult it can be.

4. Lack of Security

File systems provided limited control over who could access certain data.
A student who gained access to a file with grades might easily alter it without
proper authorization, compromising data integrity.

5. No Concurrent Access
 UNIT 01
File systems were not designed for multiple users working at the same
time. If one user was editing a file, others had to wait, which hindered
collaboration and slowed down workflows.

6. No Backup and Recovery

File systems lacked built-in mechanisms for creating backups or recovering

data after a loss. If a file was accidentally deleted or corrupted, there was no easy
way to restore it, potentially causing permanent data loss.

12.Advantages of DBMS

Data organization: A DBMS allows for the organization and storage of data
in a structured manner, making it easy to retrieve and query the data as needed.

Data integrity: A DBMS provides mechanisms for enforcing data integrity

constraints, such as constraints on the values of data and access controls that
restrict who can access the data.

Concurrent access: A DBMS provides mechanisms for controlling

concurrent access to the database, to ensure that multiple users can access the
data without conflicting with each other.

Data security: A DBMS provides tools for managing the security of the data,
such as controlling access to the data and encrypting sensitive data.

Backup and recovery: A DBMS provides mechanisms for backing up and

recovering the data in the event of a system failure.

Data sharing: A DBMS allows multiple users to access and share the same
data, which can be useful in a collaborative work environment.

Disadvantages of DBMS

Complexity: DBMS can be complex to set up and maintain, requiring

specialized knowledge and skills.

Performance overhead: The use of a DBMS can add overhead to the

performance of an application, especially in cases where high levels of
concurrency are required.
 UNIT 01
Scalability: The use of a DBMS can limit the scalability of an application,
since it requires the use of locking and other synchronization mechanisms to
ensure data consistency.

Cost: The cost of purchasing, maintaining and upgrading a DBMS can be

high, especially for large or complex systems.

Limited Use Cases: Not all use cases are suitable for a DBMS, some
solutions don't need high reliability, consistency or security and may be better
served by other types of data storage.

13.Applications of DBMS

Enterprise Information: Sales, accounting, human resources,

Manufacturing, online retailers.

Banking and Finance Sector: Banks maintaining the customer details,

accounts, loans, banking transactions, credit card transactions. Finance: Storing
the information about sales and holdings, purchasing of financial stocks and
bonds.

University: Maintaining the information about student course enrolled

information, student grades, staff roles.

Airlines: Reservations and schedules.

Telecommunications: Prepaid, postpaid bills maintance.

FILE SYSTEM

In a Database Management System (DBMS), a file system is the underlying

method used to store and organize data files on a storage medium like a hard
drive. While a DBMS manages the data within those files, the file system handles
the basic operations of reading, writing, and managing the files themselves.

Here's a more detailed explanation:

 UNIT 01
File System:file systems were used to store and manage data on storage
devices. A file system organizes and manages files on disk. Each file contains
records, and each record contains fields (data values).

👉 Features of File System in DB context

1. Data is stored in separate files for each application.

2. Access methods like sequential, indexed, or hashed are used.
3.
4. Files are managed by the operating system.
5. No standard way to relate data in different files.

⚠ Limitations of File System

1. Data redundancy – same data stored in multiple files.

2. Data inconsistency – changes in one file may not reflect in others.
3. Difficulty in accessing data – complex programming needed to retrieve
related data.
4. Poor security – file-level access control, no fine-grained data control.
5. No concurrency control – simultaneous access may cause data issues
6. No transaction support – can’t guarantee atomicity, consistency, isolation,
durability (ACID).

Purpose:

A file system, like NTFS or FAT32 on Windows, or ext4 on Linux, is the

software that organizes files and directories on a storage device.

Functionality:

It provides the basic operations for creating, deleting, renaming, and

accessing files.

Data Storage:

It stores data in a hierarchical structure of directories and files.

Limitations:
 UNIT 01
File systems lack the ability to manage complex relationships between
data, enforce data consistency, or provide advanced features like transactions and
concurrency control.

DBMS (Database Management System):

Purpose:

A DBMS is a software application that manages and organizes structured

data within a database.

Functionality:

It provides advanced operations for storing, retrieving, and manipulating

data, including features like data validation, indexing, transactions, concurrency
control, and backup/recovery.

Data Organization:

DBMS uses a structured approach, often relational models with tables,

rows, and columns, or other models like No SQL.

Advantages:DBMS offers better data consistency, integrity, and security

compared to file systems.

Relationship between File System and DBMS:

1. A DBMS relies on the underlying file system to store the actual data files
that make up the database.
2. The DBMS provides an abstraction layer, meaning it shields the user from
the complexities of the underlying file system.
3. The DBMS uses the file system to store and retrieve data, but it also
manages how that data is organized, accessed, and secured within the
database.

In simpler terms: Think of the file system as the physical storage space and the
DBMS as the librarian who manages and organizes the books (data) within that
space. The librarian (DBMS) uses the shelves (file system) to store the books, but
they also keep track of which book is where and who has access to them.
 UNIT 01
Problems with File System in DBMS Context

Before DBMS came into use, data was stored and managed using the
traditional file system. This approach had many problems:

1️⃣ Data Redundancy

➡ The same data is stored in multiple files for different applications.
➡ Wastes storage space.

2️⃣ Data Inconsistency

➡ Updates made in one file may not be reflected in other files where the same data
exists.
➡ Leads to unreliable or conflicting data.

3️⃣ Difficulty in Data Access

➡ Each application needs custom code to retrieve and update data.
➡ No standard query facility like SQL.

4️⃣ Lack of Concurrency Control

➡ If two users try to update the same data at the same time, it can cause incorrect
data.
➡ File systems don’t handle multiple users well.

5️⃣ Poor Security

➡ Only basic file-level security provided by the operating system.
➡ No fine-grained control over who can access or change specific data items.
 UNIT 01
6️⃣ No Transaction Management
➡ Cannot ensure atomicity, consistency, isolation, or durability (ACID properties).
➡ Partial updates may leave data in an inconsistent state during failures.

7️⃣ Integrity Constraints Are Not Enforced

➡ No automatic checks for data validity (e.g. no primary key, foreign key rules).
➡ Data errors can easily occur.

IMPORTANCE:

💡 Why DBMS was needed?

A DBMS overcomes these problems by:

✅ Centralizing data management
✅ Reducing redundancy
✅ Ensuring consistency
✅ Providing security and transaction control
✅ Supporting easy access through query languages

DATABASE SYSTEM DATA MODELS:

A Data Model in Database Management System (DBMS) is the concept of

tools that are developed to summarize the description of the database. Data
Models provide us with a transparent picture of data which helps us in creating an
actual database. It shows us from the design of the data to its proper
implementation of data.

Types of Relational Models

1. Conceptual Data Model

2. Representational Data Model
3. Physical Data Model

It is basically classified into 3 types:-

 UNIT 01

Data models are crucial in DBMS because they provide a blueprint for
designing and managing databases, ensuring data accuracy, consistency, and
efficient retrieval. They define how data is structured, stored, accessed, and related
within the system, enabling better understanding, easier maintenance, and
improved performance.

Here's a more detailed look at the importance of data models in DBMS:

1. Structure and Organization:

Data models provide a structured way to represent data, making it easier to

understand the relationships between different pieces of information.

They define the entities (like customers, products, orders), their attributes
(like customer name, product price, order date), and the relationships between
them (e.g., a customer can place multiple orders).

This structured approach helps in organizing complex data in a logical

manner, which is essential for efficient storage and retrieval.
 UNIT 01
2. Data Integrity and Consistency:

By defining constraints and rules, data models ensure data integrity and
consistency.

For example, a data model can specify that a customer ID must be unique,
preventing duplicate entries. It can also define relationships between tables using
primary and foreign keys, ensuring data consistency across different parts of the
database.

This reduces errors, inconsistencies, and data duplication, leading to more

reliable and trustworthy data.

3. Efficient Retrieval and Performance:

A well-designed data model optimizes database performance by minimizing

data redundancy and organizing data in a way that aligns with typical queries and
operations.

This leads to faster data retrieval, improved query performance, and better
overall system efficiency.

For example, a relational model with proper indexing can significantly speed
up searching and retrieving specific records.

4. Communication and Collaboration:

Data models serve as a common language and visual representation for

communication between developers, database administrators, and business users.

They help stakeholders understand the structure and relationships of data,

facilitating better collaboration and ensuring that the database meets the specific
needs of the business.

This shared understanding reduces ambiguity and ensures that everyone is

on the same page when it comes to data management.

5. Reduced Development Time and Cost:

By providing a clear blueprint for database design, data models help

streamline the development process.
 UNIT 01
They reduce the need for manual coding and database structure creation,
saving time and resources.

This leads to faster development cycles and lower overall development

costs.

6. Facilitating System Development:

Data models act as a blueprint for building the database system.

They guide the creation of database schemas, tables, relationships, and

constraints, ensuring that the database aligns with the application's requirements.

This helps in building robust and scalable applications that can handle the
organization's data needs effectively.

7. Scalability and Adaptability:

Data models can be designed to accommodate future growth and changes in

business requirements.

They provide a foundation that can be modified or extended as needed,

ensuring that the database can adapt to evolving needs without disrupting existing
data structures and applications.

BASIC BUILDING BLOCKS

A data model is a structure of the data that contains all the required details of
the data like the name of the data, size of the data, relationship with other data and
constraints that are applied on the data. It is a communication tool. A data model is
essential in order to store the database in a sorted manner. It will provide the
interaction between the system analyst, designer and application programmer. It
improves the understanding of designing of the database in which the organization
is interested. A data model constitutes of building blocks. They are:

1. Entities

2. Attributes

3. Relationships
 UNIT 01
4. Constraints

These are explained as following below in brief.

Entities: Entities are real time objects that exist.It can be a person, place,
object, event, concept. Entities are represented by a rectangle box containing the
entity name in it. Example: Student, employee.

Attributes: It is the set of characteristics representing an entity.It is

represented by a ellipse symbol with attribute name on it. Example: A student has
attributes like name, roll number, age and much more.

Relationship: It describes the association between two entities.It is

represented using diamond symbol containing relationship name with it.The data
model generally uses three kinds of relationships:one to many, many to many,
one to one. Example: The relationship between two entities Student and Class has
many to many relationship.

Constraints: Constraints are conditions applied on the data.It provides the

data integrity. Example: A student can take a maximum of 2 books from the
library is applied as a constraint on the student database.

BUSINESS RULES

Business rules in a Database Management System (DBMS) are the

constraints and logic that govern how data is stored, accessed, and manipulated
within the database. They reflect real-world business policies and procedures,
ensuring data integrity, consistency, and accuracy. These rules can be enforced at
various levels, including field, relationship, and even transaction levels, to
maintain data quality and prevent errors.

Elaboration:Business rules are essentially the "rules of the game" for a

database, dictating how data should be managed. They are not just technical
specifications, but rather reflect the business's understanding of its data and how
it's used. Here's a more detailed look:

Purpose:Business rules are crucial for maintaining data integrity,

preventing errors, and ensuring that the database accurately reflects the business's
operations.
 UNIT 01
Examples:Field-level rules: A field might have a rule that it can only
accept numerical values, or that it must be a certain length.

Relationship rules: A rule might dictate that a customer record must be

associated with at least one order record.

Transaction rules: A rule might state that a transaction cannot be

processed if the user's balance is insufficient.

Implementation:

Business rules can be implemented in various ways, including:

Constraints: Using NOT NULL, UNIQUE, CHECK, and FOREIGN

KEY constraints in the database schema.

Triggers: Defining code that automatically executes when specific

database events occur (e.g., inserting, updating, deleting).

Stored procedures: Encapsulating complex business logic into reusable

procedures that can be called by applications.

Importance:

Data Integrity: Business rules help ensure that the data in the database is
accurate, consistent, and reliable.

Error Prevention: By enforcing rules, they prevent invalid or inconsistent

data from being entered into the database.

Business Process Enforcement: They reflect the business's policies and

procedures, ensuring that the database supports the organization's operations.

Standardization: They help standardize how data is managed across

different parts of the organization.

Communication: They facilitate communication between users and

database designers, ensuring that the database meets the business's needs.

EVOLUATION OF DATA MODELS

 UNIT 01
The evolution of data models in DBMS (Database Management Systems)
has progressed from simple hierarchical structures to complex, flexible models like
NoSQL databases. Early models like hierarchical and network models were limited
in their ability to represent complex relationships, leading to the development of
the relational model, which revolutionized database management. Further
advancements led to object-oriented and NoSQL models, addressing the need for
handling diverse data types and large volumes of data.

Here's a breakdown of the key stages:

1. Hierarchical Model:

The earliest model, resembling an upside-down tree, where each record had
a single parent.

Good for representing one-to-many relationships but struggled with more

complex relationships.

Examples include IBM's Information Management System (IMS).

2. Network Model:

An improvement over the hierarchical model, allowing records to have

multiple parents, enabling more complex relationships.

Still limited in its ability to handle very complex relationships.

3. Relational Model:

Introduced by Edgar Codd in the early 1970s, it revolutionized database

management.

Organizes data into tables (relations) with rows (tuples) and columns
(attributes).

Uses relationships between tables based on common attributes, providing

flexibility and scalability.

The most widely used model today, with SQL as the standard query
language.
 UNIT 01
4. Object-Oriented Model:

Introduced to better represent real-world entities and their relationships

using object-oriented programming concepts.

Encapsulation, inheritance, and polymorphism are key features.

5. No SQL Databases:

Developed to address the limitations of relational databases in handling large

volumes of unstructured and semi-structured data.

Offer various models like document, key-value, column-family, and graph,

each with its strengths.

Examples include MongoDB (document), Redis (key-value), Cassandra

(column-family), and Neo4j (graph).

6. Graph Databases:

A type of NoSQL database specialized in representing and querying

complex relationships between data.

Use nodes and edges to represent entities and relationships, making them
suitable for social networks, recommendation systems, and fraud detection.

7. Multi-Model Databases:

Combine multiple data models within a single DBMS, offering flexibility

and efficiency for diverse application needs.

For example, a database might support both document and key-value models.

8. Trends:

The integration of AI and machine learning into data models for enhanced
analytics and predictive modeling.

Emerging technologies like blockchain are influencing the evolution of data

storage and management.
 UNIT 01
DEGREES OF DATA ABSTRACTION

1. Data Abstraction is one of the most important concepts in DBMS. Data

abstraction is the process of hiding unwanted and irrelevant details from
the end user. It helps to store information in such a way that the end user
can access data which is necessary, the user will not be able to see what
data is stored or how it is stored in a database.
2. Data abstraction hides unwanted details, showing users only relevant data.
3. It keeps data safe by hiding how and where data is stored.
4. Abstraction simplifies access while maintaining data security and
efficiency.

For example, while buying clothes, users see only color, size, and brand, not
production details.

Levels of Abstraction in DBMS

There are three levels of data abstraction in DBMS that are mentioned below:

1. Physical or Internal level

2. Logical or Conceptual level
3. View or External level

Physical or Internal Level

It is the lowest level of data abstraction which defines how data is stored in
database . It defines data structures used to store data and methods to access data
in database. It is very complex to understand and hence kept hidden from
user. Database administrator decides how and where to store the data in database.

Physical level deals with actual storage details like data organization, disk
space allocation and data access methods.

Logical or Conceptual Level

It is intermediate level present next to physical level. It defines what data is

present in database and their relationships between them . It is less complex as
compared to physical level. Programmers generally work at this level and
 UNIT 01
depending on data, structure of tables, relationships and their constraints is
decided at this level.

View or External Level

It is the highest level in abstraction. There are different levels of views and
each view defines only a part of whole data required to user. This level defines
many views of same database for simplification of view to user. This is the
highest level and easiest to understand for user.