Unit-1
Introduction
(Database System)
Information Technology Department
Outline
Looping
• Introduction of DBMS
• Applications of DBMS
• Advantages of DBMS
• Three levels ANSI SPARC database system
• Data Abstraction in DBMS
• Mappings and data independence
• Database users and DBA
• Database system architecture
What is Database Management System (DBMS)?
Data - Fact that can be recorded or stored
e.g. Person Name, Age, Gender and Weight etc.
Database - Collection of logically related data
e.g. Books Database in Library, Student Database in University etc.
Management - Manipulation, Searching and Security of data
e.g. Viewing result in GCET website, Searching exam papers in GCET website etc.
System - Programs or tools used to manage database
e.g. SQL Server Studio Express, Oracle etc.
DBMS - A Database Management System is a software for creating and managing databases.
Database Management System (DBMS) is a software designed to define, manipulate, retrieve
and manage data in a database.
e.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 4
Applications of DBMS
DBMS is a computerized record-keeping system.
DBMS is required where ever data need to be stored.
E-Commerce (Flikart, Amazon, Shopclues, eBay etc...)
Online Television Streaming (Hotstar, Amazon Prime etc...)
Social Media (WhatsApp, Facebook, Twitter, LinkedIn etc...)
Banking & Insurance
Airline & Railway
Universities and Colleges/Schools
Library Management System
Human Resource Department
Hospitals and Medical Stores
Government Organizations
Exercise Write down any five applications of DBMS other than above.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 6
Reduce data redundancy (duplication)
Computer Civil
Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS
Database management system
can remove such data Same data is stored at
redundancy by storing data four different places.
centrally.
Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS
Electrical Mechanical
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 8
Remove data inconsistency
Computer Civil
Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234
6789 PPS Prof. Ajay Shah Rajkot 1234 PPS
Same data having
Database management different state (values)
system can keep data in
consistent state.
Mobile no is changed
Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234
6789 PPS Prof. Ajay Shah Rajkot 1234 PPS
Electrical Mechanical
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 9
Data isolation
Data are scattered in various files.
File - 1
Files may be in different formats.
Emp_Name Address Mobile Subject
Difficult to retrieve the appropriate data. Prof. Ajay Shah Rajkot 1234 PPS
DBMS allow us to access (retrieve) appropriate data easily. File - 2
Emp_Name Post Salary Load
Prof. Ajay Shah Lecturer 50,000 15
Data isolation is a property that determines when and how
changes made by one operation become visible to other
concurrent users and systems. File - 3
This issue occurs in a concurrency situation. Emp_Name Teaching Knowledge Rating
Prof. Ajay Shah Good Excellent 9
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 10
Guaranteed atomicity
Atomicity: Either transaction execute 0% or 100%.
Sum of both account
before transfer is 3000
Person A Person B
Account A Transfer 500 Account B
Bal : 2000 Bal : 1000
Step 1 : Debit 500 from Account A
Step 2 : Credit 500 into Account B
Transaction Sum of both
is failed Sum of both account account is 2500
after transfer is 3000 so inconsistent
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 11
Allow to implement integrity constraints
Emp_Name Address Mobile_No Subject
Prof. Ajay Shah Rajkot 9876543210 PPS
Should contain exact 10 digits
Student_Name Branch Backlog SPI
Nirav Patel Rajkot 0 8.5
Should be between 0 to 10
DBMS allows us to implement such business rules in our database..
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 12
Sharing of data among multiple users
Want to access
Computer Civil
Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS
Want to access Database management system
allows more than one user to
access same data simultaneously.
Emp_Name Address Mobile Subject Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234 PPS Prof. Ajay Shah Rajkot 1234 PPS
Electrical Mechanical
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 13
Restricting unauthorized access to data
File - 1
Emp_Name Address Mobile Subject
Prof. Ajay Shah Rajkot 1234 PPS
Wants to
access Faculty
File - 2 of other
Emp_Name Post Salary Load college
Prof. Ajay Shah Lecturer 50,000 15
Wants to
access GCET
File - 3 Faculty
Emp_Name Teaching Knowledge Rating
Prof. Ajay Shah Good Excellent 9
DBMS prevents unauthorized user to access data.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 14
Providing backup and recovery services
Provides facilities to backup and restore the database in case of failure.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 15
Advantages of DBMS (Summary)
Reduce data redundancy (duplication)
Avoids unnecessary duplication of data by storing data centrally.
Remove data inconsistency
By eliminating redundancy, data inconsistency can be removed.
Data isolation
A user can easily retrieve proper data as per his/her requirement.
Guaranteed atomicity
Either transaction executes 0% or 100%.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 16
Advantages of DBMS (Summary)
Allow implementing integrity constraints
Business rules can be implemented such as do not allow to store amount less than Rs. 0 in balance.
Sharing of data among multiple users
More than one users can access same data at the same time.
Restricting unauthorized access to data
A user can only access data which is authorized to him/her.
Providing backup and recovery services
Can take a regular auto or manual backup and use it to restore the database if it corrupts.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 17
Basic terms
Data
Data is raw, unorganized facts that need to be processed.
Example: Marks of students
Student_1 = 50/100, Student_2 = 25/100.
Information
When data is processed, organized, structured or presented in a given context so as to make it useful, it is
called information.
Example: Result of students (Pass or Fail)
Student_1 = Pass, Student_2 = Fail.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 19
Basic terms (cont…)
Metadata
Metadata is data about data.
Data such as table name, column name, data type, authorized user and user access privileges for any table is
called metadata for that table.
Faculty
Emp_Name Address Mobile_No Subject
Prof. Ajay Shah Rajkot 9876543210 PPS
Metadata of above table is:
Table name such as Faculty
Column name such as Emp_Name, Address, Mobile_No, Subject
Datatype such as Varchar, Decimal
Access privileges such as Read, Write (Update)
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 20
Basic terms (cont…)
Data dictionary
A data dictionary is an information repository which contains metadata.
• Table Name – Faculty
• Column Name – EmpName, Address, Mob, Subject, Salary
• Datatype – Varchar, Decimal
• Access Privileges – Read, Write (Update)
Data warehouse
A data warehouse is an information repository which stores data.
Faculty
Emp_Name Address Mobile_No Subject
Prof. Ajay Shah Rajkot 9876543210 PPS
Prof. Ajay Patel Surat 0123456789 DBMS
Exercise Why data dictionary and data warehouse are stored in the different places?
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 21
Basic terms (cont…)
Field
A field is a character or group of characters that have a specific meaning.
E.g, the value of Emp_Name, Address, Mobile_No etc are all fields of Faculty table.
Faculty
Emp_Name Address Mobile_No Subject Fields
Prof. Ajay Shah Rajkot 9876543210 PPS
Prof. Ajay Shah Rajkot 9876543210
Prof. Ajay Patel Surat 0123456789 DBMS
Record / Tuple
A record is a collection of logically related fields.
E.g, the collection of fields (Emp_Name, Address, Mobile_No, Subject) forms a record for the Faculty.
Prof. Ajay Shah Rajkot 9876543210 PPS
Record / Tuple
Prof. Ajay Patel Surat 0123456789 DBMS
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 22
3 Levels ANSI SPARC Database System
User 1 User 2 User 3
How data are viewed View
View 1 View 2 View 3
by each users? Level
What data are stored and Conceptual Logical
What relationships exist? Level Level
How the data are actually Internal Physical
stored on storage devices? Level Level
Database
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 24
3 Levels ANSI SPARC Database System
Internal level (Physical level)
It describes how a data is stored on the storage device.
Deals with physical storage of data.
Structure of records on disk - files, pages, blocks and indexes and ordering of records
Internal view is described by the internal schema.
Conceptual level (Logical level)
What data are stored and what relationships exist among those data?
It hides low level complexities of physical storage.
For Example, STUDENT database may contain STUDENT and COURSE tables which will be visible to users but
users are unaware about their storage.
Database administrator works at this level to determine what data to keep in the database.
External level (View level)
It describes only part of the entire database that an end user concern or how data are viewed by each user.
Different user needs different views of the database, so there can be many views in a view level abstraction
of the database. Used by end users and application programmers.
End users need to access only part of the database rather than the entire database.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 25
3 Levels ANSI SPARC Database System: Example
We are storing student information in a student table.
User 1 User 2 User 3
User just interact with system with the help of GUI. View
View 1 View 2 View 3
Users are not aware of how and what the data is stored. Level
Records can be described as fields and attributes along
with their data types, their relationship among each other Conceptual
can be logically implemented. Logical
Level Level
Programmers generally work at this level.
Records can be described as blocks of storage (bytes,
Internal Physical
gigabytes, terabytes etc.) in memory.
Level Level
These details are often hidden from the programmers.
Database
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 26
Data Abstraction in DBMS
Database systems are made-up of complex data structures.
To ease the user interaction with database, the developers hide internal irrelevant details from
users.
This process of hiding irrelevant details from user is called data abstraction.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 27
Mapping and Data Independence
Want to access some data
User 1 User 2 User 3
View
View 1 View 2 View 3
Level
Request
Process of transforming requests and results between the
three levels is called mapping. Conceptual Logical
Level Level
Ability to modify a schema definition in one level without
Internal Physical
affecting a schema definition in the next higher level.
Level Level
Result
Database
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 28
Types of Data Independence
Physical Data Independence
Physical Data Independence is the ability to modify the physical schema without requiring any change in
logical (conceptual) schema and application programs.
Modifications at the internal levels are occasionally necessary to improve performance.
Possible modifications at internal levels are changes in file structures, compression techniques, hashing
algorithms, storage devices, etc.
Examples:
Changing storage devices (e.g., from HDD to SSD).
Modifying indexing strategies (e.g., switching from a B-tree index to a hash index).
Changing data compression techniques.
Moving the database to a different location.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 29
Types of Data Independence
Logical Data Independence
Logical data independence is the ability to modify the conceptual schema without requiring any change in
application programs.
Modification at the logical levels is necessary whenever the logical structure of the database is changed.
Application programs are heavily dependent on logical structures of the data they access. So any change in
logical structure also requires programs to change.
Examples:
Adding a new attribute (column) to a table.
Deleting an attribute from a table.
Modifying the relationships between tables.
Merging two tables into one.
Splitting one table into two.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 30
Types of Database Users
Naive Users (End Users)
Unsophisticated users who have zero knowledge of database system
End user interacts to database via sophisticated software or tools
e.g. Clerk in bank
Application Programmers
Programmers who write software using tools such as Java, .Net, PHP etc…
e.g. Software developers
Sophisticated Users
Interact with database system without using an application program
Use query tools like SQL
e.g. Analyst
Specialized Users (DBA)
User write specialized database applications program
Use administration tools
e.g. Database Administrator
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 32
Role of DBA
Schema Definition
DBA defines the logical schema of the database.
Storage Structure and Access Method Definition
DBA decides how the data is to be represented in the database & how to access it.
Defining Security and Integrity Constraints
DBA decides on various security and integrity constraints.
Granting of Authorization for Data Access
DBA determines which user needs access to which part of the database.
Liaison with Users
DBA provide necessary data to the user.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 34
Role of DBA
Assisting Application Programmer
DBA provides assistance to application programmers to develop application programs.
Monitoring Performance
DBA ensures that better performance is maintained by making a change in the physical or logical schema if
required.
Backup and Recovery
DBA backing up the database on some storage devices such as DVD, CD or magnetic tape or remote servers
and recover the system in case of failures, such as flood or virus attack from this backup.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 35
Database System Architecture
Naive Application Sophisticated Database
user programmer user administrator
uses write uses uses
Application Application Query Administration
interfaces program tool tool
Translates
Interprets DML
DDL
Compiler DML DDL statements intoa
and linker queries interpreter statements
Deals
Executes low into
with level
low
set of level
tables
Application execution of
instructions
instructions DDL
that
DML compiler containing
and
generated DML
program the queryDML
by
metadata
object code and organizer statements
compiler.
evaluation engine
Query evaluation
engine Query processor understands
Buffer File Authorization and Transaction
manager manager integrity manager manager
Manages allocation
Storage manager
Fetches data from
of space on disk
disk storage to Preserves atomicity
Provides interface
memorystorage
for being Checks the authority
and controls
between low-level
used faster Indices Data dictionary of users to access
To provide Disk storage concurrency
data stored and
data and integrity
access to data items Data application program
To store user data
Statistical data To To store
store statistical
metadata constraints
or queries
information about the data
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 37
Questions asked in External Exam
1. List and explain the advantages of DBMS over file based system. OR Explain disadvantages of
files based system.
2. Draw and explain 3 level architecture of DBMS.
3. List and explain different categories/types of database users.
4. List and explain different tasks/roles/functions/duties of DBA (Database Administrator).
5. Explain DBMS architecture with block diagram. OR Explain Database System architecture with
block diagram.
Prof. Hiren Raithatha #202040302 Unit 1 – Introduction 38
Thank
You
