Chapter 1.

Introduction
COMP3278
Introduction to Database Management Systems

Department of Computer Science, The University of Hong Kong

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278
For other uses, please email : ckchui@[Link]
We are going to learn…
Getting started…
What is a database management system (DBMS)?
Why is a DBMS important?

Why don’t we simply use a file to store all the data?

A brief introduction to a DBMS

2
Section 1.1

Getting started

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278 3

For other uses, please email : ckchui@[Link]
What is a database?
Data
Some values referring to real world facts.
May be in various formats, e.g., text, image, audio
file, video file, etc.
Database
A large collection of inter-related data.

Database management system (DBMS)

DBMS = database(s) + a set of programs that store
and access the data.
4
Database applications
Many daily applications involve databases.

Banking

What data are stored

in the backend
database of an ATM
machine?

DBMS
5
Database applications
Many daily applications involve databases.

Banking

What data are stored

in the backend
database of an ATM
machine?
Customers Login
records Password

Account … DBMS
Transactions
balance …etc.
6
Database applications
Many daily applications involve databases.

Banking
Airline

What data are stored in

the backend database of
an airline booking
system?

Airport Flight (date, time,

Aircraft
information origin, destination…)

Customer
DBMS
Booking …etc.
record
7
Database applications
Many daily applications involve databases.

Banking
Airlines
Universities

Student
Emails
records

News and University Course Borrowed

DBMS
announcement fee payment registration books …etc.
8
Database applications
Many daily applications involve databases.

Banking
Airlines
Universities
Social network Application
program
Online maps
An information system can consists of the
data layer and the application layer.

DBMS
Locations Roads Images
9
Database applications
Many daily applications involve databases.

Banking
Airlines
Universities
Social network Application
program
Online maps Customer
information
Smart card systems
Balance
One database may support
many different applications DBMS
simultaneously. Transactions
10
File system v.s. DBMS
In the early days, application programs were built
on top of file systems. (e.g., DOS, Windows file
explorer)
So that I don’t need to spend
Why don’t we simply use a file resources (e.g., $$$) on
system to manage our data? maintaining a database/
hiring a database
administrator…
Boss, let us use a
database to organize
our data ☺!

11
File system v.s. DBMS
6 drawbacks of storing DB in file systems:
1. Difficulty in accessing data - May be inefficient to
locate a piece of information.

If we keep all the savings account

…

Chui Chun Kit; 60123456; CB320, HKU, records in a text file, I need to
Pokfulam Rd; account balance : $100
scan the file once to retrieve any
Jolly; 91234567; CYC 311; HKU,
Pokfulam Rd; account balance: $2,000 particular record.
Yvonne; 31234567; CB 415, HKU,
Pokfulam Rd; account balance: $1,000

Savings-account records 12
File system v.s. DBMS
6 drawbacks of storing DB in file systems:
2. Data redundancy and inconsistency - Data got
updated in one place but forget to update the other
one.
…

…
Contact
Chui Chun Kit; 60123456; CB320, HKU, Ben/ account balance: $100,000/ CYC
Pokfulam Rd; account balance : $100 314, HKU, Pokfulam Rd/ 21234567/ information
Chui Chun Kit/ account balance: $4,000/
duplicated among
Jolly; 91234567; CYC 311; HKU,
Pokfulam Rd; account balance: $2,000 CB320, The University of Hong Kong, different files!
Pokfulam Rd, Hong Kong/ 60123456/
Yvonne; 31234567; CB 415, HKU,
Pokfulam Rd; account balance: $1,000 Kevin/ account balance $20,000/ 2nd
Floor … Central/ 62234567/

Savings-account records Checking-account records 13

File system v.s. DBMS
6 drawbacks of storing DB in file systems:
3. Data isolation – Because data are scattered in various
files, and files may be in different formats, writing new
programs to retrieve the appropriate data is difficult.
One file uses “;” to
…

…
separate fields while
Chui Chun Kit; 60123456; CB320, HKU, Ben/ account balance: $100,000/ CYC the other uses “/”,
Pokfulam Rd; account balance : $100 314, HKU, Pokfulam Rd/ 21234567/
also, the fields are in
Jolly; 91234567; CYC 311; HKU, Chui Chun Kit/ account balance: $4,000/ different orders!
Pokfulam Rd; account balance: $2,000 CB320, The University of Hong Kong,
Pokfulam Rd, Hong Kong/ 60123456/
Yvonne; 31234567; CB 415, HKU,
Pokfulam Rd; account balance: $1,000 Kevin/ account balance $20,000/ 2nd
Floor … Central/ 62234567/

Savings-account records Checking-account records 14

File system v.s. DBMS
6 drawbacks of storing DB in file systems:
4. Atomicity problems – How can we ensure that a
transfer of money is done completely?
Transfer $100 to cheque account…

What would happen

…

…
if the system crashes
Chui Chun Kit; 60123456; CB320, HKU, Ben/ account balance: $100,000/ CYC right after money is
Pokfulam Rd; account balance : $100 314, HKU, Pokfulam Rd/ 21234567/
deducted but not
Jolly; 91234567; CYC 311; HKU, Chui Chun Kit/ account balance: $4,000/ deposited?
Pokfulam Rd; account balance: $2,000 CB320, The University of Hong Kong,
Pokfulam Rd, Hong Kong/ 60123456/
Yvonne; 31234567; CB 415, HKU,
Pokfulam Rd; account balance: $1,000 Kevin/ account balance $20,000/ 2nd
Floor … Central/ 62234567/

Savings-account records Checking-account records 15

File system v.s. DBMS
6 drawbacks of storing DB in file systems:
5. Concurrent access anomalies – Inconsistency can
occur, e.g., two customers reading and updating a
balance at the same time.
10:00 Kit opens
the file. 10:15 Boss opens
…

the same file.

Chui Chun Kit; 60123456; CB320, HKU,
Pokfulam Rd; account balance : $100 11:00 Boss added one
Jolly; 91234567; CYC 311; HKU, more customer “Ben”
Pokfulam Rd; account balance: $2,000 12:00 Kit saves to the file and saved the
Yvonne; 31234567; CB 415, HKU, the file and file.
Pokfulam Rd; account balance: $1,000 overwrite the file
13:00 Boss open
boss saved.
Ben; 21234567; CYC 314, HKU, Pokfulam the file and
Rd; account balance: $100,000 found that “Ben”
Savings-account records is not in the file! 16
File system v.s. DBMS
6 drawbacks of storing DB in file systems:
6. Integrity problem – Programmers need to enforce
consistency constraints by adding appropriate code in the
various application programs, which is hard to manage.
…

The savings account records should

Chui Chun Kit; 60123456; CB320, HKU, have at least $200 in the account
Pokfulam Rd; account balance : $100 balance.
Jolly; 91234567; CYC 311; HKU,
Pokfulam Rd; account balance: $2,000

Yvonne; 31234567; CB 415, HKU,

Pokfulam Rd; account balance: $1,000 A checker program has to be
added before every withdraw and
transfer transactions.
Savings-account records 17
Database Management System
Big DBMS vendors: Oracle, IBM DB2, Microsoft SQL server etc.

Open source DBMS: MariaDB, PostgreSQL, MySQL.

18
Database Management System
A good DBMS aims at handling all problems related
to large DB management.
DBMS provides an environment that is both
convenient and efficient to use.
Three powerful concepts supported by a DBMS:
1. Data abstraction.
2. Data modeling.
3. Database languages.

19
1. Data abstraction
Three levels of data abstraction.
1. Physical level
Describes HOW the data are
actually stored.
Describes complex low-level data
structures in detail.
E.g., Whether the actual data are
compressed or not? With what data
compression scheme?
Physical level

20
1. Data abstraction
Three levels of data abstraction.
2. Logical level
Describes WHAT data are stored.
Describes the relationship among data.
Physical data independence
Logical level
Adding an index to speed up the access won’t
affect the data stored in the database.
Compressing the data in the physical level also won’t
affect application programs or user interfaces.

We use the logical level of abstraction to Physical level

model the information in the database.
21
1. Data abstraction
Three levels of data abstraction.
View level
3. View level
View 1 View 2 …
Describes only part of the entire DB.
Many users of the database system do
not need to access all information in the
database. i.e. different users should have Logical level
different view to the database.
It simplify users’ interaction with the
database system.
Physical level

22
1. Data abstraction
Important concept of Schema and Instance
A Schema describes the structure of the database.
An Instance is the actual content of the database at a
particular time.
customer {
Schema and string customer_name;
instance are analog string customer_address;
to type and value in Logical level
}
programming.
A schema of customer

Name Address Name Address

Kit CB320, University of Hong Kong … Kit CB320, University of Hong Kong …
Ben CB314, University of Hong Kong … Jolly CB311, University of Hong Kong …

An instance of customers Another instance of customers 23

2. Data modeling
Data modeling is used to describe data in a
systematic way.
What DATA I would like to store
should be stored? the account records of
my customers.

I need to store the “Customer” data, and

Design a data model for each customer, I will store his/ her
name, address and account balance!
name

address
customer Name Address Account balance
account_ Kit HKU… $100
balance Ben … $2,000
The data model The data instance 24
2. Data modeling
Data modeling is used to describe data in a
systematic way. A customer can have more
than one account!!!

name

address
customer Name Address Account balance
account_ Kit HKU… $100
balance Kit … $2,000
The data model The data instance 25
2. Data modeling
Data modeling is used to describe data in a
systematic way. A customer can have more
than one account!!!

Easy ☺! I add a unique

account ID to distinguish
different account of the
same customer!
account_id name

address
customer Account ID Name Address Account balance
account_ 1 Kit HKU… $100
balance 2 Kit HKU… $2,000

The data model The data instance 26

2. Data modeling
Data modeling is used to describe data in a
systematic way. You are wasting my
disk space  $$ !!!!!

account_id name
Data redundancy
address
customer Account ID Name Address Account balance
account_ 1 Kit HKU… $100
balance 2 Kit HKU… $2,000

The data model The data instance 27

2. Data modeling
Data modeling is used to describe data in a
systematic way.
Let me separate the
customer information with
the account information.

Customer ID Name Address Account ID Balance

1 Kit HKU… 1 $100
2 … … 2 $2,000

Account ID Name Address Account balance

1 Kit HKU… $100
2 Kit HKU… $2,000

The data instance 28

2. Data modeling
Data modeling is used to describe data in a
systematic way. An extra information is needed to
represent the relationship of the
customers with the accounts, then one
Customer ID Account ID customer can have more than one
1 1 account, and the customer info will not be
1 2 duplicated ☺!

Customer ID Name Address Account ID Balance

1 Kit HKU… 1 $100
2 … … 2 $2,000

29
2. Data modeling
Data modeling is used to describe data in a
systematic way. An extra information is needed to
represent the relationship of the
customers with the accounts, then one
Customer ID Account ID customer can have more than one
1 1 account, and the customer info will not be
1 2 duplicated ☺!
owner
Customer ID Name Address Account ID Balance
1 Kit HKU… 1 $100
2 … … 2 $2,000
customer account account_
customer_id balance

customer owner account

name address The data model account_id

30
2. Data modeling
Data modeling is used to describe data in a
systematic way. Well done ☺!

We need to design the data model

carefully so that it can:
1. Capture user requirements.
2. Store/ access data efficiently.
account_
customer_id balance

customer owner account

name address The data model account_id

31
2. Data modeling
Data modeling is used to describe data in a
systematic way.
A collective tool for describing
Data
Data relationships
Data semantics
Data constraints
account_
customer_id balance

customer owner account

name address The data model account_id

32
3. Database languages
DBMS provide different tools for managing data.
Data definition language (DDL)

CID Name Address

DDL DBMS Empty

The database
creates the
Customer table
accordingly
I want to CREATE a database table called
“Customers” with attributes “Customer
ID”, “Name” and “Address”.

33
3. Database languages
DBMS provide different tools for managing data.
Data definition language (DDL)
Data manipulation language (DML)
CID Name Address

1 Ben HKU…
DML DBMS 10 Kit HKU…

12 Jolly …

10 Kit HKU…

I want to SELECT the customer record

with customer ID = 10 from the
Customer table.
34
Section 1.2

Database
Overview

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278

For other uses, please email : ckchui@[Link]
Database Overview
A DBMS is a complex system partitioned into
modules that deal with each of the responsibilities
of the overall system.
Query processer
The functional
components can be
broadly divided into
Storage manager

Query processer
Storage manager
Disk storage

36
Storage manager
A program module in DBMS that provides interface
between the low-level data and the application
programs/queries.
Query processer
Consists of
File manager
Transaction manager Storage manager
Authorization
Authorization and File
manager
Transaction
manager
and integrity
Buffer
manager
manager
integrity manager
Buffer manager Disk storage

37
File manager
Manages the allocation of space on disk storage and
the data structures used to represent information
stored on disk.
Query processer

Storage manager
Authorization
File Transaction Buffer
and integrity
manager manager manager
manager

Disk storage

38
Transaction manager
Ensures that the database remains in a consistent
(correct) state despite system failures.
Query processer
Ensures interaction
among all concurrent
transactions does not
make the DB Storage manager

inconsistent. File Transaction

Authorization
and integrity
Buffer
manager manager manager
manager

Disk storage

39
Authorization and integrity manager

Tests for the satisfaction of integrity constraints and

checks the authority of users to access data.
Query processer

Storage manager
Authorization
File Transaction Buffer
and integrity
manager manager manager
manager

Disk storage

40
Buffer manager
Responsible for fetching data from disk storage into
main memory.

Decide what data to Query processer

cache in main
memory.
Storage manager
Authorization
File Transaction Buffer
and integrity
manager manager manager
manager

Disk storage

41
Disk storage
Disk storage consists of
Data files – stores the
database itself. Query processer

Data dictionary –
stores metadata about
the structure of the
Storage manager
database, in particular
the schema of the File Transaction
Authorization
and integrity
Buffer
manager manager manager
database. manager

Indices – provide fast

Disk storage
access to data records. Data files
Data
Indices
dictionary

42
Query processer
A program module in DBMS that converts high-level
user requests to efficient low-level commands to
DB.
Query processer
Consists of DDL DML
Query
evaluation
interpreter compiler
engine
DDL interpreter
DML compiler Storage manager
Authorization
Query evaluation engine File
manager
Transaction
manager
and integrity
Buffer
manager
manager

Disk storage
Data
Data files Indices
dictionary

43
DDL interpreter
Interprets DDL statements and records the
definition in the data dictionary
Query processer
Query
DDL DML
evaluation
interpreter compiler
engine

Storage manager
Authorization
File Transaction Buffer
and integrity
manager manager manager
manager

Disk storage
Data
Data files Indices
dictionary

44
DML compiler
Translates DML statements in a query language into
an evaluation plan.
Query processer
Evaluation plan - low-
level instructions that the DDL DML
Query
evaluation
interpreter compiler
query evaluation engine engine

understand.
Storage manager
Query optimization – Authorization
File Transaction Buffer
Picks the lowest cost manager manager
and integrity
manager
manager

evaluation from among

the alternative plans. Disk storage
Data
Data files Indices
dictionary

45
Query evaluation engine
Executes low-level instructions generated by the
DML compiler.
Query processer
Query
DDL DML
evaluation
interpreter compiler
engine

Storage manager
Authorization
File Transaction Buffer
and integrity
manager manager manager
manager

Disk storage
Data
Data files Indices
dictionary

46
Database Administrator
Duties of DBA
Schema definition.
Storage structure and access method
definition (e.g., hash table or B+ tree?)
Granting user authority to access the database.
Monitoring performance and responding to changes
in requirements.
An important and well-paid job.
[Link]

47
Summary
Simple file systems are inadequate for enterprise
needs – the 6 drawbacks.

Three powerful concepts supported by a DBMS:

Data abstraction - 3 levels of abstraction, schema and
instances.
Data modeling.

Database languages – DDL and DML.

48
Chapter 1.

END
COMP3278 Introduction to
Database Management Systems

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278

For other uses, please email : ckchui@[Link]