UNIT-I

INTRODUCTION TO DATABASE AND

SYSTEM ARCHITECTURE
TOPICS
Database Systems and their Applications
Database Vs File System
Data Models
View of Data
Instances & Schemas
Database Languages- DDL and DML
Transaction Management
Database users and Administrators
Database System Structure
INTRODUCTION TO DBMS
A database is a collection of related data
E:g telephone numbers.
Properties
A database is designed, built, and populated with data for a specific
purpose.
A database can be of any size and varying complexity.
E:g: library, income tax department
A database may be generated and maintained manually or it may be
computerized
DBMS- Database Management System
The DBMS is a general-purpose software systems that facilitates the
process defining, constructing, manipulating, retrieving and sharing
database among various users and applications.
DBMS include protecting the database and maintaining it over long
period of time.
Protection includes both system protection against hardware or
software malfunctions, and security protection unauthorized access.
DATA BASE SYSTEM APPLICATIONS

Database applications span various fields, including:

• Enterprise Information: Sales, accounting, and human
resources.
• Manufacturing: Supply chain management and inventory
tracking.
• Banking and Finance: Customer accounts, loans, credit card
transactions, and financial markets.
• Education: Student information and course registrations.
• Airlines: Reservations and schedules.
• Telecommunication: Call records and billing.
• Web-based Services: Social media, online retail, and targeted
advertising.
• Document Databases: Articles, patents, and research papers.
• Navigation Systems: Location data and route information.
DATABASE SYSTEM VS FILE SYSTEMS

A File System is a way of storing and organizing files on a

storage device like a hard disk. Each application stores and
retrieves data independently, using files like .txt, .csv, etc.
A DBMS is software that allows users to store, modify,
and retrieve data from a structured database. It provides
tools and languages (like SQL) to interact with data easily,
securely, and efficiently.
DATABASE SYSTEM VS FILE SYSTEMS
File system
One way to keep the information on the computer and store it in
FAT file format.
Each table should be stored separately as a file.
Application programs to be written in order to access the data.
File processing systems has number of major disadvantages
compared to DBMS:
Data redundancy
Same information may be duplicated in several places.
User defines and maintains needed files for a specific application
to run. Two users maintain same files for different applications.
E:g: address and telephone number of a particular customer may
appear in a file that consists saving account records and in a file
that consists of current- account records
Data inconsistency
Changes made by one user does not reflect in files used by
second user.
E:g change of customer address may be reflected in savings
account records but not in other records such as current-
account record .

Difficulty in accessing data

File processing environment do not allow wanted data to be
retrieved in convenient and efficient way.
E:g: bank officer want the information of customers who lives
in Hyderabad area ,then the data processing department takes
few days time to generate the information either manually or to
write application program.
If he want to trim the query again few days are required.
Data isolation
As the data is scattered in different files , and the files may
be in different formats, writing a new application programs
to retrieve the appropriate data is difficult.

Integrity problem
Data values stored in the data base must satisfy certain
consistency constraints .
E:g: (acc-balance>25$) this become a part of application
program
The constraints has changed or added it is difficult to
change the program code.
Atomicity problems
Failures of computer may leave database in an inconsistent state
with partial updates carried out in file systems
The fund transfer must be atomic
E:g : transfer of funds from one account to another should either
complete or not happen at all before the system fails.

Concurrent -access anomalies

Multiple users updating data simultaneously can lead to
inconsistencies. For example, concurrent withdrawals from the
same bank account might not be properly reflected in the
balance.
To provide supervision is difficult because data may be accessed
by many different application program that have not been
coordinated previously
E:g : account 500 –A withdraw 50 and B withdraw 100 the
balance should be 350 but it will shows as 450 for A and 400 for
B which are not correct.
Security problems
Enforcing security constraints is difficult in file-processing
systems, as not all users should have access to all data.
Every user of the database system should not access all the
data.
As application programs are added to the file-processing
system in an ad hoc manner ,maintaining security is
difficult.
E:g: payroll personnel – bank employees not customer
accounts.
Feature File System DBMS
High (same data
Low (data centralized,
Data Redundancy stored in multiple
no duplication)
files)
Easy due to
Data Consistency Difficult to maintain
constraints and rules
Low (no user High (login, roles,
Data Security
management) permissions)
Powerful SQL
Querying Capability Manual searching
querying
Manual and Automated and
Backup & Recovery
error-prone reliable
Concurrency Handled by DBMS
Difficult (no locking)
Control internally
Enforced using
Data Integrity Not enforced
primary/foreign keys
Scalability Limited High
Optimized for large
Performance Poor with large data
data volumes
Data models
A data model is a conceptual way to structure and organize
data in a database. It defines how data is connected, stored,
and accessed.
These are classified as 4 categories
Relational models
Collection of tables to represent both data and relationship
among those data.
It is an example of record based model.
Entity-relationship model (E-R models)

These are the collection of objects called entities and

relationship among these objects.
Object-based data model
Extending the ER model with notions of encapsulation,
methods and object identity is object oriented data model.
Object-based data model combines the features of object
oriented data model and relational model.
Semi structured data model:
Permits specification of data where individual data items of
same type may have different set of attributes.
Hierarchical data model:
A hierarchical database model is a data model in which the
data is organized into a tree-like structure,
Example: College database(inorder to search for student
record we need to traverse from
college->dept->year->section->student.
 VIEW OF DATA
A major purpose of a database system is to provide users
with an abstract view of data where the system hides certain
details of how the data are stored and maintained

Data abstraction
Complexity is hidden by the developers from the users
through several levels of abstractions.

Physical level
Logical level
View level
Physical level
How the data is actually stored in the memory.
Data base administrators will be aware of certain details like
usability, size of memory, and the blocks of storage.
Lowest level of abstraction.

Logical level
Information that is actually stored in the database in the
form of tables and also stores the relationship among the
data.
Here each record is describe by the type definition and the
interrelationship among all the records.
Programmers & data administrators works at this level.
Second level of abstraction.
View level
This is the highest level of abstraction which describes only
a part of the entire program.
The system may provide many views from the same
database.
Application programs hide details of data types.
Views can also hide information for security purposes.
Database users can see different views of database.
Instances and schemas
Collection of information stored in the database at a particular
moment is called instance of database.
The overall design of the database is called the database
schema-blueprint.
Types of schemas
Physical schema
The physical schema describes how data is stored in the
storage system.
It defines:
◼ File organization,
◼ Indexing,
◼ Compression,
◼ Memory blocks and storage format.
To optimize performance, memory usage, and storage.
Example:Table Student stored as a B-tree file in disk block [Link]
built on RollNo using a hash [Link] is invisible to end users and
managed by the database administrator (DBA).
Logical schema
Describes the database design at the logical level.
The logical schema defines the structure of the database at the logical
level — what tables exist, what are their columns, data types, and how they
relate.
Ex: CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(20)
);

CREATE TABLE Course (

CourseID INT,
CourseName VARCHAR(50),
Credits INT
);
This logical schema defines two tables (Student, Course) and their
structure.
 View Schema
• The view schema, also known as the external schema, defines
how individual users or applications view the data.
• Each user may have a different subset or view of the database,
based on access permissions or business needs.
• Provides security and customized data access.

Example:
CREATE VIEW StudentNames AS
SELECT Name FROM Student;
DDL & DML
CLASSIFICATION OF SQL COMMAND SET
DDL (Data definition language)
DML (Data Manipulation language)
TCL (Transaction Control language)
DCL (Data Control Language)
DQL(Data Query Language)
DDL
◼ CREATE
◼ ALTER
◼ TRUNCATE
◼ DROP
◼ RENAME
◼ COMMENT
DML
◼ INSERT
◼ UPDATE
◼ DELETE

TCL
DATA BASE ARCHITECTURE
Architecture is influenced by underlying computer system on
which database system runs.
Database systems can be client-server or centralized.
Now a days users of database system are not present at the site
of database systems ,but connect through a network.
Client machines-on which remote users work, server
machines- on which database system runs.
Database applications are usually partitioned into two or three
parts.
Two-tier architecture:
◼ The application is partitioned into a component that
resides at the client machine which invokes database
system functionality at the server machine through query
language statements.
◼ ODBC and JDBC are used for interaction
Three-tier architecture:
◼ Client machine acts as a front end and does not contain
any database calls.
◼ Client end communicates with an application server
through forms.
◼ The application server in turn communicates with
database system to access data.
DATABASE USERS AND ADMINISTRATOR
Database users
People who work with the database can be categorized as database
users or database administrators.
There are 4 types of database system users:
Naive users
Invoke one of the application programs that have been written
previously
E.g. Bank tellers, clerical staff, Railway reservation counter
clerks, ATMS.
The typical user interface for naive users is a forms interface,
where the user can fill in appropriate fields of the form.
Application programmers
They are the developers who interact with the database by means
of DML queries. These DML queries are written in the
application programs like C, C++, JAVA, Pascal etc. These
queries are converted into object code to communicate with the
database
Sophisticated users
They are database developers, who write SQL queries to
select/insert/delete/update data.
They do not use any application or programs to request the
database.
They directly interact with the database by means of query
language like SQL
Specialized users
These are also sophisticated users, but they write special
database application programs. They are the developers who
develop the complex programs to the requirement.
CAD Systems, Expert systems, Complex data systems (
audio, video etc)
Database administrator
Coordinates all the activities of the database system; the
database administrator has a good understanding of the
enterprise’s information resources and needs.
Database administrator's duties include:
Schema definition
Storage structure and access method definition
Schema and physical organization modification
Granting user authority to access the database
Specifying integrity constraints
Ensuring that enough free disk space is available and
upgrading disk space as required.
Monitoring performance and responding to changes in
requirements.
Backup and Recovery
TRANSACTION MANAGEMENT
A transaction is a collection of operations that performs a
single logical function in a database application.
Requirements:
◼ All or none-atomicity.
◼ Correctness-consistency.
◼ Persistence-durability.
Transaction-management component ensures that the
database remains in a consistent (correct) state despite
system failures (e.g., power failures and operating system
crashes) and transaction failures.
Database should also perform failure recovery that deletes
the system failures and restore the database to the state that
existed prior to the occurrence of failure.
Concurrency-control manager controls the interaction
among the concurrent transactions, to ensure the consistency
of one other.
DATABASE DESIGN AND ER-DIAGRAMS
Database design process can be divided into 6 steps
Requirements analysis
What data is to be stored in the database.
What applications have to be built on the database.
What operations are frequently used to perform
requirements.

Conceptual database design

With the information gathered in step 1 is used to develop
the high level description of data to be stored in the data
base.
Which is carried out through ER diagram.
ER model is one of the high level model or semantic data
model to design the database
 The goal of ER model is to create simple description of data
which provide discussion among all the people involved in
the design process, even those who have no technical
background.

Logical database design

This is to convert the conceptual database design into
database schema
The task here is to convert ER schema into relational
database schema.

Schema refinement
This is to analyze the collection of relations in our relational
database schema to identify potential problems and refine it.
Physical database design
This phase involves building indexes of some tables and
clustering some tables.
It can also do the substantial redesign of part of database
schema obtained from the earlier step.

Application and security design.

We have to identify the entities involved in the application.
We must describe the role of each entity
For each role we have to identify the parts of database that
must be accessible and the parts of database that must not be
accessible.
We have to take steps to the rules ensured to be enforced.

A complete database design is a subsequent tuning phase of

all the steps until the design is satisfactory.