Unit 1

Database Concepts and Architecture

 Data and Information:
Data refers to raw, unprocessed facts, figures, symbols, or observations
that represent something but lack meaning or context on their own. It is
the foundational building block used to generate
meaningful information when organized, interpreted, or structured.
Information is processed, organized, and structured data that has been
given context, meaning, and purpose, making it useful for decision-
making, communication, or action. Unlike raw data, information
answers questions like "who," "what," "where," and "why."
 Database:
A database is an organized collection of structured data stored electronically in a
computer system. Databases allow for efficient data management, storage,
retrieval, and manipulation.
Its components: i)Schema ii)Table iii)column iv)Row
• Types of Databases
1.Relational Databases (SQL)
1. Organize data into tables with rows and columns
2. Examples: MySQL, PostgreSQL, Oracle, SQL Server
3. Use SQL (Structured Query Language) for operations
2.NoSQL Databases
1. Designed for unstructured or semi-structured data
2. Types: Document, Key-Value, Wide-Column, Graph
3. Examples: MongoDB, Cassandra, Redis, Neo4j
3.NewSQL Databases
1. Combine SQL reliability with NoSQL scalability
2. Examples: Google Spanner, CockroachDB
Database Management System and RDBMS:
A Database Management System (DBMS) is software that allows users
to define, create, maintain, and control access to a database. It acts as
an intermediary between the database and end-users or applications,
ensuring data is organized, secure, and efficiently retrievable.
RDBMS is DBMS that maintains relationship between multiple data files is
called RDBMS.It used by enterprises for storing complex and large amount of
data.
File Management system(Flat file system):A flat file is a file that contains
records,and in which each record is specified in a single line.
Difference between file based system and Database system
File based system Database system

It is less complex system It is very complex system

The cost of file processing system is less then The cost of database system is much more than a file
database system. processing system.
It is difficult to maintain as it provides less controlling It provides many facilities to maintain program.
facility.
If one application fail it does not affects other files in If database fail it affects all application that
system. dependent on database.
Hardware cost is less than database system. Hardware cost is high in database than file system.
Difference between DBMS and Flat file system
DaDBMS FlFlat file system

DBMS provides a brilliant mechanism for protection. It is hard to protect a file.

It has loads of different techniques to store and It does not have the efficiency to store the data and
retrieve data. retrieve it in a good way.

It uses some sort of locking to control the In this type of data system, there is no control over
unauthorized access to the data. the redirection and update of the information.

It minimizes the chances of duplication of the data. Data redundancy is more in this type of system.

Less inconsistency in data. More data inconsistency.

Key Functions of a DBMS
1.Data Definition
•Allows users to define the database structure (tables, relationships, constraints).
•Uses Data Definition Language (DDL) (e.g., CREATE, ALTER, DROP).
2.Data Manipulation
•Enables inserting, updating, deleting, and querying data.
•Uses Data Manipulation Language (DML) (e.g., SELECT, INSERT, UPDATE, DELETE).
3.Data Security & Access Control
•Ensures only authorized users can access/modify data.
•Uses authentication, authorization, and encryption.
4.Data Integrity & Constraints
•Enforces rules (e.g., primary keys, foreign keys, NOT NULL, UNIQUE).
•Prevents invalid data entry.
5.Concurrency Control
•Manages simultaneous access by multiple users to prevent conflicts.
•Uses locking mechanisms and transaction isolation levels.
6.Backup & Recovery
•Provides tools to restore data in case of failure (e.g., transaction logs, checkpoints).
7.Performance Optimization
•Uses indexing, query optimization, and caching for faster data retrieval.
Difference Between Database and DBMS
Database:
 A database is a collection of organized data.
 It stores data in tables, files, or records.
 Example: A table of student records with name, roll number, and
marks.
DBMS:
 A DBMS is a software used to create, manage, and interact with
databases.
 It provides tools to insert, update, delete, and query data.
 Example: MySQL, Oracle, Microsoft SQL Server.
Database Users
Database users are individuals or systems that interact with a database to
perform operations such as querying, updating, managing, or administering
data. They can be categorized based on their roles and levels of interaction
with the database.
Types of database users:
1) Naïve Users : Naïve users are unsophisticated users who interact with the
system by invoking one of the application programs that have been written
previously.
2) Application programmers : Application are computer professionals who
write application program.
3) Sophisticated Users : It interact with the system without writing programs.
They form their requests in a database query language.
4) Specialized Users : Specialized users are sophisticated users who write
specialized database applications that do not fit into the traditional data
processing framework.
 Benefits of Databases:
Databases provide a structured, efficient, and secure way to store, manage, and
retrieve data. They offer significant advantages over traditional file-based systems.
Below are the key benefits:
1. Data Centralization & Reduced Redundancy
✔ Single source of truth – All data is stored in one place, eliminating
inconsistencies.
✔ Minimized duplication – Normalization ensures data is stored only once,
reducing storage waste.
2. Improved Data Integrity & Accuracy
✔ Constraints & Validation – Enforces rules (e.g., PRIMARY KEY, FOREIGN KEY, NOT
NULL).
✔ ACID Compliance – Ensures reliable transactions (Atomicity, Consistency,
Isolation, Durability).
3. Efficient Data Retrieval & Querying
✔ Fast searches – Indexes optimize query performance.
✔ Powerful querying – SQL allows complex data filtering, sorting, and joining.
4. Enhanced Security & Access Control
✔ User authentication – Only authorized users can access data.
✔ Role-based permissions – Different access levels for admins, users, and guests.
✔ Encryption – Protects sensitive data (e.g., passwords, financial records).
5. Concurrent Access & Multi-User Support
✔ Simultaneous usage – Multiple users/apps can work without conflicts.
✔ Locking mechanisms – Prevents data corruption during updates.
6. Scalability & Performance
✔ Handles large datasets – Optimized for growing data volumes.
✔ Vertical & horizontal scaling – Can expand with hardware or distributed systems (e.g., sharding)
.7. Backup & Disaster Recovery
✔ Automated backups – Prevents data loss.
✔ Point-in-time recovery – Restores databases to a previous state.
8. Data Consistency & Standardization

✔ Uniform structure – Follows schemas for predictable data storage.

✔ Reduces anomalies – Normalization eliminates update/delete issues.
9. Integration with Applications
✔ APIs & connectors – Easy interaction with web/mobile apps (e.g., REST APIs, ORMs).
✔ Supports business logic – Enforces rules at the database level (e.g., triggers, stored procedures).
10. Cost & Time Efficiency
✔ Reduces manual work – Automation minimizes repetitive tasks.
✔ Lower long-term costs – Efficient storage and retrieval save resources.
Data Models:
A data model is an abstract framework that organizes and standardizes
how data is stored, retrieved, and managed in a database system. It
defines the logical structure of data and the relationships between
different data elements.
They are of three types:
1) Object-Based Logical Model
a) Entity Relationship ER model
b) Object-Oriented Model
2)Record-Based Model
a) Hierarchical Model
b) Network Model
c) Relation Model
3)Physical Data Model
Types of data models:
1. Hierarchical Model
• Structure: Tree-like structure with parent-child relationships
• Characteristics:
• One-to-many relationships
• Fast access for predefined queries
• Rigid structure (difficult to modify)
• Use Cases: File systems, XML data
• Example: IBM's Information Management System (IMS)
2. Network Model
• Structure: Graph-like structure allowing many-to-many relationships
• Characteristics:
• More flexible than hierarchical
• Complex to design and maintain
• Uses pointers to establish relationships
• Use Cases: Complex manufacturing systems
• Example: Integrated Data Store (IDS)
3. Relational Model (Most Common)
• Structure: Organizes data into tables (relations) with rows and columns
• Characteristics:
• Uses SQL for queries
• Supports ACID properties(Atomicity, Consistency, Isolation, and Durability.)
• Flexible and scalable
• Use Cases: Most business applications
• Examples: MySQL, PostgreSQL, Oracle
4. Entity-Relationship Model (Conceptual Model)
• Structure: Visual representation of entities and their relationships
• Characteristics:
• Used in database design phase
• Uses ER diagrams
• Independent of implementation
• Use Cases: Database design planning
5. Object-Oriented Model
• Structure: Stores data as objects (similar to OOP)
• Characteristics:
• Supports inheritance, polymorphism
• Complex data types
• Good for complex applications
• Use Cases: CAD systems, multimedia applications
• Examples: ObjectDB, db4o
6. Object-Relational Model
• Structure: Combines relational and object-oriented features
• Characteristics:
• Extends RDBMS with OO capabilities
• Supports user-defined types
• Use Cases: Applications needing complex data in relational DB
• Examples: PostgreSQL, Oracle
Database Schemas:
• The schema is the structural definition of a database. It represents
the logical configuration that determines how data is organized and ho
Key Characteristics of a Schema:
• Blueprint of the database.
• Defines tables, fields, relationships, constraints.
• Remains constant unless altered by DBA.
• It keep relationships between data are established.
Database Instances
• The instance (or database state) represents the actual data stored in
the database at a particular moment in time. It changes frequently as
data is inserted, updated, or deleted.
Key Characteristics of an Instance:
• Snapshot of the database at a given time
• Dynamic (changes with transactions)
• Also called "extension" of the database
 Differences Between Schema and
Instance
Feature Schema Instance

Definition Structure of the database Actual data in the database

Changes frequently with CRUD

Stability Rarely changes (modified by DBA)
ops(Create, Read, Update, and Delete)

Dependency Independent of data Must conform to schema

Example CREATE TABLE statements Rows in tables at a given time

Analogy Building blueprint Current state of the building

Three-Schema Architecture:
The Three-Schema Architecture is a foundational framework in database management
systems (DBMS) that delineates three distinct layers of data abstraction: External,
Conceptual, and Internal.
• 1. External Schema (View Level):This topmost layer defines how individual users or
user groups perceive the data. Each user can have a tailored view that presents only
the data relevant to their needs, effectively hiding the complexities of the underlying
database structure. For instance, a sales department might access customer purchase
histories, while the HR department views employee records, all from the same
database .
• 2. Conceptual Schema (Logical Level):Serving as an intermediary, the conceptual
schema provides a unified and abstract representation of the entire database. It
outlines the logical structure, including entities, relationships, and constraints, without
delving into physical storage details. This level ensures consistency and integrity across
various external views and is typically managed by database administrators .
• 3. Internal Schema (Physical Level):At the base, the internal schema details how data
is physically stored and accessed on storage devices. It encompasses file structures,
indexing methods, and storage paths. Changes at this level, such as optimizing storage
mechanisms, can be made without affecting the conceptual or external schemas,
thereby providing physical data independence .
Advantages of the Three-Schema Architecture
Data Independence: Modifications in storage structures or user views don't
necessitate changes across all layers.
Enhanced Security: Users access only the data pertinent to them, reducing
exposure to sensitive information.
Improved Flexibility: Supports multiple user views and can adapt to evolving
business requirements without overhauling the entire system.
Simplified Maintenance: Isolating changes to specific layers streamlines
updates and maintenance tasks
Limitations
Increased Complexity: Implementing and managing multiple schemas can be
intricate and resource-intensive.
Potential Performance Overhead: The abstraction layers might introduce
latency in data retrieval and processing.
Consistency Challenges: Ensuring synchronization across all schemas requires
meticulous design and management .
 Data Independence:
Data Independence is a critical feature of database systems that allows
modifications to the database schema at one level without affecting the
schema or applications at higher levels. This separation ensures flexibility,
maintainability, and scalability.
Types of Data Independence
• There are two main types of data independence:
1. Physical Data Independence: Physical data independence is the capacity
to change the physical storage structures or devices without affecting the
conceptual schema or application programs.
2. Logical Data Independence: Logical data independence is the ability to
change the logical schema, such as adding or removing entities,
attributes, or relationships, without altering the external schema or
application programs.
Database Languages and Interfaces:
Database languages are used to read, store and update the data in the database.
Specific languages are used to perform various operations of the database.
a)DDL(Data Definition Language):Data Definition Language(DDL) is used for describing
structures or patterns and its relationship in a database. It is also used to define the
database schema, tables, index, Constraints, etc.
• The commands used in DDL are:
• Create: It is used to create a database or table.
• Alter: It is used to make a change in the structure of a database.
• Drop: It is used to completely delete a table from the database
• Rename: It is used to rename a table.
• Truncate: It is used to delete the entities inside the table while holding the structure
of the table.
• Comment: It is used to comment on the data dictionary.
b)DML(Data Manipulation Language):DML is used to manipulate the data
present in the table or database. We can easily perform operations such as
store, modify, update, and delete on the database.
• The commands used in DML are:
• Select: It shows the record of the specific table. Also, it can be used with a
WHERE clause to get the particular record.
• Insert: It allows users to insert data into the database or tables.
• Update: It is used to update or modify the existing data in database tables.
• Delete: It is used to delete records from the database tables. Also, it can be
used with a WHERE clause to delete a particular row from the table.
• Merge: It allows the insert and update(UPSERT) operations.
c)DCL(Data Control Language):DCL works to deal with SQL commands that are
used to permit a user to access, modify and work on a database. it is used to
access stored data. It gives access, revokes access, and changes the permission
to the owner of the database as per the requirement.
• The commands used in DCL are:
• Grant: It is used to give access to security privileges to a specific database
user.
• Revoke: It is used to revoke the access from the user that is being granted by
the grant command.
d) TCL(Transaction Control Language):
• It can be grouped into a logical transaction and is used to run the changes
made by the DML command in the database.
• Commit: Transaction on the database is saved using Commit.
• Rollback: The database gets restored to the original since the last commit.
Interface:
An interface is a program that allows users to input queries into a database without writing the code
in the query language. An interface can be used to manipulate the database for adding, deleting,
updating, or viewing the data.
a. Menu-based Interfaces
• Simplified interfaces with options presented via menus (often used in retail or banking systems).
b. Form-based Interfaces
• Allow users to enter data into forms (used in data entry systems).
c. Graphical User Interfaces (GUIs)
• Use icons and visual indicators (e.g., Microsoft Access, phpMyAdmin).
d. Natural Language Interfaces
• Let users interact with the database using natural human language (like querying a database via
ChatGPT or voice assistant).
e. Query-based Interfaces
• Users write structured queries (primarily SQL).
f. Application Program Interfaces (APIs)
• Allow programs to interact with databases via code, such as Python + SQLAlchemy, Java + JDBC,
etc.
The database System environment:
The database system environment refers to the ecosystem of components and
processes that work together to store, manage, and interact with data. It
includes hardware, software, data, users, procedures, and interfaces. Below is
a breakdown of the key elements:
1. Hardware Components
2. Software Components
3. Data
4. Users
5.Network Environment
Centralized DBMS Architecture:
• A centralized Database Management System (DBMS) architecture is a model
where all data storage, management, and processing occur on a single
central server. Clients or terminals connect to this central server to access or
manipulate the data. This architecture is often referred to as a monolithic or
mainframe-based system.
Features of Centralized DBMS Architecture
• Single Point of Control: All data resides in one location, simplifying
administration, maintenance, and security enforcement.
• Simplified Data Consistency: With a single data source, there's minimal risk
of data duplication or inconsistency.
• Cost-Effective for Small-Scale Applications: Ideal for environments with
limited users or data volume, such as embedded systems or small business
applications.
Client/Server Architecture:
Client/Server Architecture is a computing model where tasks are divided
between clients (requesters) and servers (providers). Clients initiate
communication by sending requests, and servers respond by providing
services or resources, typically over a network.
Types of client server architecture:
a)1-tier architecture:1-Tier Architecture is a software model where all
components of an application—user interface (UI), business logic,
and data storage—reside on a single machine or platform. There is no
separation between layers, and no network communication is required.
b)2-tier architecture: 2-Tier Architecture divides an application into two
layers:
• Client Tier: Handles the user interface (UI) and business logic (e.g., input
validation, workflows).
• Server Tier: Manages data storage and processing (e.g., databases like
MySQL, PostgreSQL).
Communication occurs directly between the client and server over a
network, typically using protocols like HTTP or SQL.
c)3-tier architecture: A 3-tier architecture separates its tiers from each other
based on the complexity of the users and how they use the data present in
the database.
Database(Data)tier
Application(Middle)tier
User(presentation)tier
Classification of Database Management systems:
1)On the basis of database model:
a)Hierarchical Databases
b)Network Databases
c)Relational databases
d)Object-oriented databases
2)On the basis of number of users:
a)Single User DBMS
b)Multi user DBMS
3)On the basis of data distributions:
a)Centralized DBMS
b)Distributed DBMS
a)Hierarchical Databases: Hierarchical databases organize data in a tree-like
structure, where each record (node) has a single parent except the root node.
Relationships are strictly one-to-many, forming parent-child hierarchies (e.g.,
organizational charts, file systems).
b)Network Databases: Network databases are a type of database model that
organizes data in a graph structure, allowing records (nodes) to have multiple
parent and child relationships.
c)Relational databases: Relational databases organize data
into tables (relations) consisting of rows (tuples) and columns (attributes).
d)Object-oriented databases: An object-oriented database (OODB) organizes
data as objects, mirroring the principles of object-oriented programming
(OOP). These objects encapsulate data (attributes) and behavior
(methods) into self-contained units, managed by an Object-Oriented Database
Management System (OODBMS).
a)Single User DBMS:
A Single-User Database Management System (DBMS) is designed for
environments where only one user can access and modify the database at a
time. Unlike multi-user systems, it lacks concurrency controls and prioritizes
simplicity over scalability, making it ideal for personal or small-scale
applications.

b)Multi user DBMS: A Multi-User DBMS (Database Management System) is

a type of DBMS that supports access to the database by multiple users
simultaneously. It ensures data consistency, integrity, and concurrent
access control, allowing users to perform operations like querying,
updating, and managing data at the same time without interfering with
each other.
a)Centralized DBMS:
A Centralized Database Management System (DBMS) stores and manages data
in a single location or server, with all users accessing and modifying the
database through this central node. This model contrasts with distributed
systems, where data is spread across multiple nodes.

b)Distributed DBMS:
A Distributed Database Management System (DDBMS) stores and manages
data across multiple physical or geographic locations, interconnected via a
network. Unlike centralized systems, it distributes data to improve scalability,
fault tolerance, and performance. Users interact with the database as a single
logical entity, while the system handles the complexity of data distribution
behind the scenes.