DATABASE SYSTEM

Database System

This course aims to:

― gives an understanding of how database works in real
time application.

Basic Concepts 2
 Database System
Text Book(s):
1. Database System Concepts (6th Edition) by
Silberschatz, Korth and Sudarshan
2. Fundamentals of Database Systems by Elmasri &
Navathe

Recommended Online Reads:

1. https://www.tutorialspoint.com/dbms/index.htm
2. https://www.w3schools.com/sql/

Basic Concepts 3
 DATABASE SYSTEM

Basic Concepts
 Introduction
Database:
✓ Database is a collection of large, interrelated data.
✓ These data can be stored in the form of tables.

✓ Example:
A Customer database may include attributes or fields such as
cust_no, cust_name, and cust_city.

cust_no cust_name cust_city

1 Amit Hasan Dhaka
2 Rafiq Alam Khulna
3 Saiful Islam Rajshahi

Basic Concepts 5
 Introduction
Database:
✓ Each row in the table is called a tuple or record.
✓ Each column in the table is called a attribute or field.

attributes

cust_no cust_name cust_city

1 Amit Hasan Dhaka
tuples 2 Rafiq Alam Khulna
3 Saiful Islam Rajshahi

Basic Concepts 6
 Introduction
Database Management System:
✓ A software package designed to store and manage databases.
✓ The primary goal of a DBMS is to provide a way to store and
retrieve database information that is both convenient and
efficient.

Database applications:
✓ Banking: all transactions
✓ Airlines: reservations, schedules
✓ Universities: registration, grades
✓ Sales: customers, products, purchases
✓ Manufacturing: production, inventory, orders, supply
chain
✓ Human resources: Employee records, salaries, tax
deductions
Basic Concepts 7
 Database vs File System
Disadvantages of file system:

1. Data redundancy and inconsistency: Data redundancy refers to the

duplication of data. In file systems, the same information may be
duplicated in several places (files). This Data redundancy may also
lead to data inconsistency.

2. Data Isolation: Because data are scattered in various files, and files
may be in different formats, writing new application programs to
retrieve the appropriate data is difficult.

3. Dependency on application programs: Changing files would lead to

change in application programs.

Basic Concepts 8
 Database vs File System
Disadvantages of file system:

4. Atomicity issues: Atomicity of a transaction refers to “All or

nothing”, which means either all the operations in a transaction
executes or none. It is difficult to achieve atomicity in file
processing systems.

5. Data Security: Data should be secured from unauthorised access,

for example a student in a college should not be able to see the
payroll details of the teachers, such kind of security constraints are
difficult to apply in file processing systems.

Basic Concepts 9
 Database vs File System
Advantages of database:

1. No redundant data: Redundancy removed by data normalization. No

data duplication saves storage and improves access time.

2. Data Consistency and Integrity: The root cause of data inconsistency

is data redundancy, since data normalization takes care of the data
redundancy, it also ensures that data are consistent everywhere.

3. Data Security: It is easier to apply access constraints in database

systems so that only authorized user is able to access the data. Each
user has a different set of access thus data is secured from the issues
such as identity theft, data leaks and misuse of data.

Basic Concepts 10
 Database vs File System
Advantages of database:

4. Privacy: Limited access means privacy of data.

5. Concurrent Access- Database provides the ability to share data by

controlling access to data items. Therefore many users can access
data at the same time.

6. Fast access to data – Database systems manages data in such a way

so that the data is easily accessible with fast response times.

7. Easy recovery: Since database systems keeps the backup of data, it is

easier to do a full recovery of data in case of a failure.

Basic Concepts 11
 Database vs File System
Disadvantages of database:

• High implementation cost

• Greater design complexity compared to file system

Basic Concepts 12
 Instances & Schema
Instance: The collection of information stored in the database at a
particular moment is called an instance of the database.

Schema: The overall design of the database is called the database

schema.

Basic Concepts 13
 View of Data
A major purpose of a database system is to provide users with an
abstract view of the data. That is, the system hides certain details
of how the data are stored and maintained.

There are three level of this data abstraction.

Basic Concepts 14
 View of Data
1. Physical level (Internal Schema): This is the lowest level of
abstraction that describes how the data are actually stored. The
physical level describes complex low-level data structures in
detail.

2. Logical level (Conceptual Schema): This level of abstraction

describes what data are stored in the database, and what
relationships exist among those data. The logical level thus
describes the entire database in terms of a small number of
relatively simple structures.

3. View level (External Schema): This level describes only part of

the entire database to simplify users’ interaction with the system.
The system may provide many views for the same database.

Basic Concepts 15
 View of Data

User 1 User 2 User n

View Level
View 1 View 2 … View n

Logical level

Physical Level

Database

Figure: Levels of Abstraction in a DBMS

Basic Concepts 16
 Data Model
Data model is a collection of conceptual tools for describing data,
data relationships, data semantics, and consistency constraints.
The data models can be classified into four different categories:

1. Relational model: In relational model, the data and

relationships are represented by collection of inter-related
tables. This is the most widely used data model.

2. Entity-Relationship Model. The entity-relationship (E-R) data

model uses a collection of basic objects, called entities, and
relationships among these objects. An entity is a “thing” or
“object” in the real world that is distinguishable from other
objects.

Basic Concepts 17
 Data Model
3. Object-Based Data Model: Object-oriented programming
(especially in Java, C++, or C#) has become the dominant
software-development methodology. This led to the
development of an object-oriented data model that can be
seen as extending the E-R model with notions of
encapsulation, methods (functions), and object identity.

4. Semistructured Data Model. The semistructured data model

permits the specification of data where individual data items
of the same type may have different sets of attributes.

Basic Concepts 18
 Database Language
A database system provides a data-definition language to specify
the database schema and a data-manipulation language to
express database queries and updates.

Data-Definition Language (DDL): DDL is used to specify a

database schema by a set of definitions.

Data-Manipulation Language (DML): DML is a language that

enables users to access or manipulate data. The types of access are:

✓ Insert new data into the database

✓ Read data from the database
✓ Update data in the database
✓ Delete data stored in the database

Basic Concepts 19
Database System Architecture

No need to memorize it.

This is just for understanding
the topics discussed next.

Basic Concepts 20
 Database System Architecture
Query Processor:

The query processor components include-

DDL interpreter: It interprets DDL statements and records the

definitions in the data dictionary.

DML compiler: It translates DML statements in a query language

into low-level instructions that the query evaluation engine
understands.

Query evaluation engine: It executes low-level instructions

generated by the DML compiler.

Basic Concepts 21
 Database System Architecture
Storage Manager:
A storage manager is a program module that provides the
interface between the low level data stored in the database and
the application programs and queries submitted to the system.

Basic Concepts 22
 Database System Architecture
The storage manager components include:

1. Authorization and integrity manager, which tests for the

satisfaction of integrity constraints and checks the authority of
users to access data.

2. Transaction manager, which ensures that the database always

remains in a consistent (correct) state.

3. File manager, which manages the allocation of space on disk

storage.

4. Buffer manager, which is responsible for fetching data from

disk storage into main memory.

Basic Concepts 23
 Practice Questions
• List four significant differences between a file-processing
system and a DBMS.

Basic Concepts 24
 Related Resources

➢ Database System Concepts (6th Edition)

• Chapter 1

➢ https://www.tutorialspoint.com/dbms/index.htm

Basic Concepts 25