UNIT-1

DATABASE ARCHITECTURE
Data: It is a collection or representation of facts, figures and statistics etc., which
does not have particular meaning. Data is in the form of numbers, characters,
symbols, pictures also.
Information: It is a processed data or collection of data which is having perfect
meaning.
• Data [raw facts]-> data Transformation->Information.
Database: Database is an organized collection of related data for one or more
purposes.
DBMS: DBMS stands for Database Management System
• A DBMS is a software that enables users to create, store, and maintain a
database.
•
Traditional File Processing System (TFS)
1. Each application keeps its own separate file for storing data, leading to data
duplication.
Merits/Advantages:
1. No need of highly technical person to handle the database.
2. Processing speed is high compared to the DBMS approach.
3. No need of external storage.
Let us take an Example of Hospital Management System.
Demerits/Disadvantages:
Each component maintains its own files to store relevant information.
1. Duplication of data: Redundancy
• Information is repeated in multiple files.
• For example, the doctor's details appear in the doctor's file, as well as in
the patient and ward files.
• This results in the same data being stored multiple times across different
files.
2. Data dependence:
Redundancy leads to data dependence, meaning that the information in one file
is dependent on the information in another file. For example, in the hospital
management system, without knowing the doctor’s details, it is difficult to
manage patient and ward files. This interdependence causes data to be closely
linked across files.
3. Data inconsistency:
Due to redundancy, data may become inconsistent, meaning that it is not ordered
or synchronized across different files.
4. Limited data sharing:
Each application has its own private files, limiting the opportunity for data
sharing between different applications.
5. Lack of security:
This approach does not have mechanisms to protect data from unauthorized
access. There is no way to verify the identity of users authorized to access specific
applications.
6. Lengthy development time:
Developing multiple applications in this system is time-consuming. It requires
significant effort and time to manage various files.
Data base Approach
In the database approach, data is stored in a single location, known as a repository,
and it is shared among many users.

As shown in the above figure the DBMS is central system which provides a
common interface between the data and end users.
Advantages
1. Minimum data redundancy:
database stores unique details for each user, so the same data doesn't have to be
duplicated in multiple places. This reduces the repetition of data.
2. Data Consistency:
Reduced data redundancy leads to better data consistency.
3. Data Sharing:
A database allows related data to be easily shared, such as doctors sharing patient
information with each other.
4. Data Independence:
Different data, like doctor and patient information, are stored separately, so they
don't depend on each other.
5. Data Integrity:
Databases provide better security and accuracy of data compared to traditional
file systems.
6. Easy Maintenance
Maintenance is simpler because all data is managed in a centralized system.
Disadvantages
1. Cost of hardware and software.
2. Need for external database.
3. Highly dependent on DBMS operation.
4. Need technical staff to handle database.

Difference between TFS and Database approach:

▪ In TFS approach each user defines and implements their own files for a
specific application to run whereas database approach maintains a single
repository to store multiple users’ data.

Characteristics and purpose of Database Approach

➢ Data Sharing:
Multiple users and applications can share the same data concurrently, ensuring
efficient resource utilization and collaboration.
➢ Backup and Recovery:
The database approach includes built-in mechanisms for data backup and
recovery to protect against data loss and ensure business continuity.
➢ Self-describing nature of a database system:
This is the fundamental characteristic of database. A DBMS catalog stores the
description of a particular database.
➢ Insulation between programs and data:
In the database approach, the data structure is stored in the system catalog, not in
the programs. If we want to change the structure of the file, there is no need to
change the program. This feature is called program-data independence, and it is
not found in the file-based system.
➢ Support of multiple views of the data:
Each user may see a different view of the database, which describes only the data
of interest to that user. A view may be a subset of the database, or it may contain
virtual data.
 ➢ Data Abstraction:
The database approach provides a level of abstraction, allowing users to interact
with data without needing to understand the complexities of its storage and
organization.
➢ Data Security:
DBMS provides mechanisms to control access to data, ensuring that only
authorized users can perform specific operations (e.g., read, update, delete).

Database Users/people associated with database system

▪ Each type of user interacts with the database in different ways depending
on their responsibilities and level of access.
▪ The people or users associated with the database can be broadly
categorized as follows.
1. Database Administrators (DBAs)
2. Database Designers
3. Data Analysts
4. Application Developers
5. End Users

1.Database Administrators (DBAs)

• DBA is a person or a group in charge of implementing the database system.
Role:
DBAs are responsible for the overall management and maintenance of the
database system.
Responsibilities:
• Installing and upgrading the DBMS.
• Monitoring and optimizing database performance.
• Implementing and maintaining data security and integrity.
• Managing backup and recovery processes.
 • Creating and managing database users and roles.
2. Database Designers:
• Database designers are responsible for designing the database structure,
including the schema, tables, relationships, and constraints.
3. Data Analysts:
• Data analysts use the database to extract, analyze, and interpret data for
decision-making purposes.
4. Application Developers:
• Application developers create the software that interacts with the database,
providing user interfaces and tools for end users.
Examples include scientists, engineers, system analysts, etc.
5. Stand-Alone Users :
These users are individuals who use personal databases or small-scale
DBMS applications for their own needs.
Example: A user of a tax package that stores a variety of personal financial
data for tax purposes.
Database system applications
Database system applications are used in many areas to manage and organize
information.
Telecommunication: Manage customer data, call records, and billing.
Human Resources: Store employee information and track payroll.
Universities: Handle student records, courses, and grades.
Libraries: Organize books, borrower details, and inventory.
Banking: Manage accounts, transactions, and customer data.
Airlines: Track flight schedules, bookings, and passenger info.
Military: Store and manage personnel and equipment data.
Manufacturing: Oversee inventory, production processes, and orders.
Accounting: Record financial transactions and manage budgets.
Sales: Track customer orders, inventory, and sales data.
Database System Concepts and Architecture
Data Model
❑ A data model is a collection of concepts that can be used to describe the
structure of a database.
❑ Data model is a set of data structure and conceptual tools used to describe
the structure of a database.
❑ By structure of a database, we mean the data types, relationships and
constraints that apply to the data.
❑ Most data models also include a set of basic operations for specifying
retrievals and updates on the database.
❑ Data model provides the necessary means to achieve abstraction.
Classification of Data models

1. High level or object-based data model

▪ It provides concepts that are close to the way many users perceive data.
Conceptual data models use concepts such as entities, attributes, and
relationships.
▪ This model is used to describe the data. In this model we do not bother
about the storage of data.
▪ “The main aim of this model is only the relationship between the various
objects”.
▪ There are two types :
I. E-R Model
II. Object Model
I. E-R Model:
▪ This is entity relationship model.
▪ “It is a graphical or symbolical or pictorial notation for data base”.
▪ The main components of this model are
o Entity
o Relationship
o Attribute
II. Object Oriented Data model
The Object-Oriented Model in DBMS or OODM is the data model where data
is stored in the form of objects.
This model is used to represent real-world entities.
The data and data relationship are stored together in a single entity known as
an object in the Object-Oriented Model.
1. Objects: Objects are abstractions of real-world entities.
2. Attributes: Attributes describe the properties of an object. For instance, a
“student” object may have attributes like roll number, branch, and marks.
3. Class: A class is a collection of similar objects with shared attributes and
methods. Objects are instances of a class. For instance, “Person,”
“Student,” “Doctor,” and “Engineer” are classes
4. Inheritance: Inheritance allows a new class to inherit attributes and
methods from an existing (base) class. For example, “Student,” “Doctor,”
and “Engineer” classes can inherit from the base class “Person.”

2. Representational or Implementation Data Model

• Representational data models represent data by using record structures
• Representational or implementation data models are the models used most
frequently in traditional commercial DBMSs.
• There are 3 types of record-based models.
 I. Hierarchical database model

• In this model data will be represent in the form of inverted tree like
structure.
• This structure allows representing information using parent-child
relationship.
• Each parent can have many children. But each child has only one parent.
Hence, this relationship is called as one to many relationships
• This model is used to describe the data and maintain the logical and view
level
• At the top of the hierarchy, there is one entity, which is called the root.
ADVANTAGES:
• In this model DBMS provides lot of security.
• There is always link between the parent and child.
• Proper ordering of a tree result is easier and faster.
 DISADVANTAGES:
• Although it is a simple to design but complex to implement.
• If you make any changes in the database structure then you must make
changes in the entire application in the DBMS.
• This model suffers from the insert, delete and update anomalies.

II. Network database model

• It is a flexible of representing objects and their relationships.
• In this data can be representing in the form of relationship through the
pointers.
• This model allows each record have multiple parent and child records;
hence this is referred as many to many relationships.
• It is as same as the hierarchical model but need not be necessary to use a
downward tree like structure to represent the data.
• In the network model, the entities are organized in a graph, in which some
entities can be accessed through several paths as shown in figure below.

ADVANTAGES:
• Data must be tree like structure because of we are using the pointers.
 • Data manipulation can be done easily.
• It is possible to represent many to many relationship
DISADVANTAGES:
• It is very complex to implement the pointer concept.
• It is very difficult to make the structural changes in the database.

III. Relational database model

• This model was developed by E. F CODD in 1960’s.
• In this model all the data is represented (or) organized in the form of two-
dimensional format that is table.
• Table is a combination of unnamed rows and Named columns (or) is a
collection of records.
• Rows are also called as tuples or records.
• Columns are also called as fields or attributes.

3. Low-level or physical data model

Low-level or physical data models describe how data is stored on computer

storage media, like magnetic disks. These models explain how data is organized
in files, including details like:

• Record Formats: The structure of the data in each file.

• Record Orderings: How records are arranged within the files.
 • Access Paths: The methods used to retrieve or access the data.

Database Schema

• A database schema is the overall structure or blueprint of a database. It

defines how the data is organized without containing any actual data.

Characteristics:

• Created during the database design phase.

• Typically, stable and does not change frequently.
• Serves as a framework for database structure.

Categories of schema

1.Physical Schema:

• Description: Describes how data is physically stored on disk.

• Includes: File structures, indexing methods, storage allocation, and data access paths.
• Focus: Addresses performance optimization and storage efficiency.

2. Logical Schema:

• Description: Defines the design of the database at a logical level.

• Includes: Tables, views, relationships, and integrity constraints.
• Focus: Represents how data is structured without concern for how it is physically
implemented.

3.View Schema:

• Description: Represents the database design from a user's perspective.

Classification of DBMS

Database Management Systems (DBMS) can be classified based on various

criteria.

1. Based on Data Model

2. Based on Number of Users
3. Based on Database Location

1.Based on Data Model

• Relational DBMS (RDBMS):

o Uses a table-based structure to store data. Data is organized into
rows and columns.
o Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
o Organizes data in a tree-like structure, with a single parent for each
record.
• Network DBMS:
o Allows more complex relationships by enabling multiple parent-
child relationships, forming a graph structure.
• Object-Oriented DBMS (OODBMS):
o Stores data in the form of objects, similar to object-oriented
programming.

2. Based on Number of Users

• Single-User DBMS:
o Designed for use by a single user at a time.
o Example: Microsoft Access.
• Multi-User DBMS:
o Supports multiple users accessing the database simultaneously.
o Example: MySQL, Oracle.

3. Based on Database Location

• Centralized DBMS:
o The database is stored and managed in a single location, with all
users accessing it remotely.
• Distributed DBMS:
o The database is spread across multiple locations, with each site
maintaining its own DBMS instance.
Data independence

• It is the ability to change the schema at one level of the database system
without having to change the schema at the other levels.
• There are two types of data independence as follows:

1. Logical data independence

2. Physical data independence.

1.Logical data independence:

• It is the ability to change the conceptual schema without affecting the

external schemas or application programs.

2. Physical data independence:

• It is the ability to change the internal schema without affecting the

conceptual or external schema. An internal schema may be changed due to
several reasons such as for creating additional access structure, changing
the storage structure, etc.

DBMS Architecture

• Database Management System (DBMS) architecture is crucial for efficient

data management and system performance.
• It involves the database's design, development, and maintenance,
determining how users interact with and access the system.
• There are 3 different ways to look at the architecture of DBMS they are

1. Single Tier Architecture (One-Tier Architecture)

2. Two-Tier Architecture
3. Three-Tier Architecture.
 Single Tier Architecture (One-Tier Architecture)

• In this architecture, the database is directly available to the user.

• Any changes done here will directly be done on the database itself. It
doesn't provide a handy tool for end users.
• The 1-Tier architecture is used for development of the local application,
where programmers can directly communicate with the database for the
quick response.

Advantages of 1-Tier Architecture

• Simple Architecture: 1-Tier Architecture is the simplest architecture to set
up, as only a single machine is required to maintain it.
• Cost-Effective: No additional hardware is required for implementing 1-
Tier Architecture, which makes it cost-effective.
• Easy to Implement: 1-Tier Architecture can be easily deployed, and hence
it is mostly used in small projects.

Two-Tier Architecture

• The 2-Tier architecture is same as basic client-server. In the two-tier

architecture, applications on the client end can directly communicate with
 the database at the server side. For this interaction, API's like: ODBC,
JDBC are used.
• The user interfaces and application programs are run on the client-side.
• The server side is responsible to provide the functionalities like: query
processing and transaction management.
• To communicate with the DBMS, client-side application establishes a
connection with the server side.

Advantages of 2-Tier Architecture

• Easy to Access: 2-Tier Architecture makes easy access to the database,
which makes fast retrieval.
• Scalable: We can scale the database easily, by adding clients or upgrading
hardware.
• Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture and
Multi-Tier Architecture.
• Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier
Architecture.
• Simple: 2-Tier Architecture is easily understandable as well as simple
because of only two components.

Logical DBMS Architecture (or) 3-Schema Architecture (or) 3 level

Architecture
• This architecture describes how the data transfers from database to users
through the DBMS.
• The aim of this architecture is separation of user application from the
database.
• This Architecture divided into 3 levels hence it is referred as 3 schemas.
Those levels are
1. Internal or physical or low level
2. Conceptual or logical or middle level
3. View or External or high level
 1. Internal level
• It is the lowest level of data abstraction that deals with the physical
representation of the database on the computer and thus, is also known as
physical level.
• It describes how the data is physically stored and organized on the storage
medium.
• In this level users can create only empty structure of the database.
2. Conceptual level
• This level of abstraction deals with the logical structure of the entire
database and thus, is also known as logical level.
• It describes what data is stored in the database, the relationships among
the data and complete view of the user’s requirements without any
concern for the physical implementation.
• That is, it hides the complexity of physical storage structures. The
conceptual view is the overall view of the database, and it includes all
types of operations like insertion, deletion and modifications.

3.External level
• It is the highest level of abstraction that deals with the user’s view of the
database and thus, is also known as view level.
• In general, most of the users and application programs do not require the
entire data stored in the database.
• It permits users to access data in a way that is customized according to
their needs, so that the same data can be seen by different users in
different ways, at the same time.

• The process of transforming the requests and results between various

levels of DBMS architecture is known as mapping.
• There are 2 ways of mapping used in 3 schema architecture.
• They are
• Internal mapping: The mapping between internal and conceptual level
and it gives the correspondence between structure and stored data.
• External mapping: The mapping between external and conceptual level
and it defines the correspondence between particular records and fields.
• The main advantage of three-schema architecture is that it provides data
independence.

Database Languages
• A DBMS has appropriate languages and interfaces to express database
queries and updates.
• Database languages can be used to read, store and update the data in the
database.

Data Definition Language (DDL)

• DDL stands for Data Definition Language. It is used to define database

structure or pattern.
• It is used to create schema, tables, indexes, constraints, etc. in the
database.
• Using the DDL statements, you can create the skeleton of the database.
• DDL Commands are

1. Create: It is used to create objects in the database.

2. Alter: It is used to alter the structure of the database.
3. Drop: It is used to delete objects from the database.
4. Truncate: It is used to remove all records from a table.
5. Rename: It is used to rename an object.

Data Manipulation Language (DML)

• DML stands for Data Manipulation Language. It is used for accessing and
manipulating data in a database. It handles user requests.
• DML Commands are
1. Select: It is used to retrieve data from a database.
2. Insert: It is used to insert data into a table.
3. Update: It is used to update existing data within a table.
4. Delete: It is used to delete all records from a table.

Data Control Language (DCL)

• DCL stands for Data Control Language. It is used to retrieve the stored
or saved data.
• The DCL execution is transactional. It also has rollback parameters.
• (But in Oracle database, the execution of data control language does not
have the feature of rolling back.)
• DCL Commands are
• Grant: It is used to give user access privileges to a database.
• Revoke: It is used to take back permissions from the user.

Transaction Control Language (TCL)

• TCL is used to run the changes made by the DML statement. TCL can be
grouped into a logical transaction.
• TCL Commands are
• Commit: It is used to save the transaction on the database.
• Rollback: It is used to restore the database to original since the last
Commit.

DBMS Interfaces
• Interfaces are the programs which convert system language to user
understandable language; hence it is as same as the translator.
• The user-friendly interfaces provided by DBMS are :
1. Menu-Based Interface
• An interface where you navigate by choosing options from menus.
• Example: ATM Machine: When you use an ATM, you choose what you
want to do (e.g., withdraw money, check balance) from a series of menus.

2. Forms-Based Interface
• An interface where you fill out fields in a form to input or retrieve data.
• Example: Online Registration Form: When signing up for an online
service, you fill out a form with your name, email, and other details to
create an account.
•
3. Graphical User Interface (GUI)
• An interface that uses visual elements like buttons, icons, and windows
that you interact with, usually with a mouse or touchscreen.
• Example: Microsoft Windows: When you use a computer, you click on
icons, drag files, and interact with programs through buttons and windows.

4. Natural Language Interface

• This interface allows users to interact with the database using natural
language, like English or any other spoken language.
• Example: Amazon Alexa: You ask, "Alexa, how many orders did I place
this month?" Alexa understands and tells you the number.

5. Interface for the DBA (Database Administrator)

• This interface is designed for the database administrator (DBA), who
manages and maintains the database.
Example: Microsoft SQL Server Management Studio: A DBA uses this
tool to create a backup of the company’s database or set up new user
accounts.

6. Interface for Parametric Users

• This interface is for users who perform regular, repetitive tasks using the
database.
• Example: Bank Teller System: A bank teller uses a specific screen every
day to enter deposit or withdrawal details for customers.

7.Speech Input and Speech Output Interface

• This interface allows users to interact with the database using voice
commands (speech input) and receive spoken responses (speech output).
• Example: Apple Siri: You say, "Siri, what’s the weather today?" Siri gets
the information from a database and reads it aloud to you.