Database Systems

Overview
Chapter 1
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

2
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

3
 1. Basic concepts

Basic concepts
 Data, information, knowledge and wisdom
 Database
 Database management system (DBMS)
 Application program
 Types of database applications

4
 1. Basic concepts

Basic concepts
 Data
 Known facts that can be recorded and that have implicit
meaning.
 Information
 Result of analyzing and interpreting pieces of data.
 Knowledge
 Information combined with experience, understanding, and
insights to draw conclusions.
 Wisdom
 Judgments and decisions using knowledge, experience, and
ethical considerations.

5
 1. Basic concepts

Basic concepts
 Data (raw facts): 308
Just a number

 Information (processed
data with context): 308 km
“The distance from Ho Chi
Minh City to Da Lat is about
308 km via QL20”

 Knowledge (understanding): “308 km is a quite far

distance”

 Wisdom (judgment & action): “It’s very difficult to walk 308

km by any person, but vehicle transport is OK”

6
 1. Basic concepts
Basic Concepts

 Database: group of related files.

 File: group of records of the same
type
 Record: group of related fields
 Field: group of characters into
word(s), or a number
 Byte: group of bits, represents a
single character, which can be a
letter, a number, or another
symbol.
 Bit represents the smallest unit of
data a computer can handle.

7
 1. Basic concepts

Database
 A collection of related data with an
implicit meaning
 Implicit properties:
 Represents some aspect of the real world
(miniworld)
 A logically coherent collection of with some
inherent meaning.
 Designed, built, and populated with data for
a specific purpose.

 UNIVERSITY database
 Information concerning students, lecturers, courses, and
grades in a university environment.
8
 1. Basic concepts

Example: a part of University Database

9
 1. Basic concepts

Database
 Database manipulation involves querying and updating.
 Examples of queries are as follows:
 Retrieve the transcript—a list of all courses and grades—of
‘Smith’
 List the names of students who took the section of the ‘Database’
course offered in fall 2008 and their grades in that section
 List the prerequisites of the ‘Database’ course
 Examples of updates include the following:
 Change the class of ‘Smith’ to sophomore
 Create a new section for the ‘Database’ course for this semester
 Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’ section of last
semester

10
 1. Basic concepts

Basic concepts
 Database management system (DBMS): A computerized
system to create and maintain a database.
 An application program accesses the database by sending
queries or requests for data to the DBMS.
 A query typically causes some data to be retrieved
 A transaction may cause some data to be read and some data to
be written into the database.

11 Application programs
 1. Basic concepts

Some of database applications

 Traditional database: textual or numeric
information
 Multimedia database:
images, audio clips, and
video streams
 Spatial database: geometric
objects
 Geographic information
systems (GIS): store and
analyze maps, weather data,
and satellite images
 Temporal database: historical data
12
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

13
 2. File-based Approach

File-based Approach
 Data is stored in one or more separate computer files
 Data is then processed by computer programs -
applications

14
 2. File-based Approach

File-based Approach
Data entry File handling
and routines
Sales files
Reports File definition
Sales Department Sales Application Programs

Data entry File handling

and routines
Contract files
Reports File definition
Contract Department Contract Application Programs

Sales files
PrivateOwner (ownerNo, fName, lName, address, telNo)
PropertyForRent (propertyNo, street, postcode, rooms, ownerNo)
Client (clientNo, fName, lName, address, telNo, prefType, maxRent)
Lease (leaseNo, propertyNo, clientNo, deposit, paid, Start, Finish)
PropertyForRent (propertyNo, street, city, postcode, rent)
Client
15 (clientNo, fName, lName, address, telNo) Contract files
 2. File-based Approach

File-based Approach
 Problems:
 Data Redundancy
• The same information being kept in several different places
(files)
• Wastes storage space and duplicates effort
 Data Inconsistency
• Various copies of the same data are conflicting
• Inconsistency in data format

16
 2. File-based Approach

File-based Approach PropertyForRent

File

Data entry File handling

routines PrivateOwner
and
File
Reports File definition
Sales Department Sales Application Programs Client
File

Lease
File
Data entry File handling
and routines PropertyForRent
Reports File definition File
Contract Department Contract Application Programs Client
File
17
 2. File-based Approach

Shared File Approach

PrivateOwner
File
Data entry File handling
and routines
Reports File definition
Sales Department PropertyForRent
Sales Application Programs
File

Client
File

Data entry File handling

and routines
Reports File definition Lease
File
Contract Department Contract Application Programs

18
 2. File-based Approach

Shared File Approach

 Data (files) is shared between different applications
 Data redundancy problem is alleviated
 Data inconsistency problem across different versions
of the same file is solved

19
 2. File-based Approach

Shared File Approach

 Problems:
 Rigid data structure: If applications have to share files, the
file structure that suits one application might not suit
another
 Physical data dependency: If the structure of the data file
needs to be changed in some way, this alteration will need
to be reflected in all application programs that use that data
file
 No support of concurrency control: While a data file is
being processed by one application, the file will not be
available for other applications or for ad hoc queries

20
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

21
 3. Database Approach

Database Approach
 Database approach allows user:
 Specify data types, structures and any data constraints to be
stored in the database. All specifications are stored in the
database
 Query data: retrieve (query), update (insert, delete, modify)
 Control access to database:
• a security system
• an integrity system
• a concurrency control system
• a recovery control system
• a user-accessible catalog
 Database System = Database + DBMS software
22
 3. Database Approach

Database Approach

23
 3. Database Approach

Database Approach
 System catalog (metadata) provides description of
data to enable program–data independence
 Logically related data comprises entities, attributes,
and relationships of an organization’s information
 DataBase Management System (DBMS):
 A computerized system to create and maintain a database.
 A general-purpose software system that facilitates the
processes of defining, constructing, manipulating, and
sharing databases among various users and applications

24
Database Catalog of University Database

25
 3. Database Approach

Functions of Database Management System

 Defining a database: specifying the data types, structures,
and constraints for the data to be stored in the database.
 Constructing a database: storing the data itself on some
storage medium that is controlled by the DBMS.
 Manipulating a database: querying the database to
retrieve specific data, updating the database to reflect
changes in the miniworld, and generating reports from the
data.
 Sharing a database: allowing multiple users and programs
to access the database concurrently.
 Protection includes both system protection against
hardware or software malfunction (or crashes), and
security protection against unauthorized or malicious
access.
 Maintaining a database system: allowing the system to
evolve as requirements change over time.
26
 3. Database Approach

Characteristics of the Database Approach

 Self-describing nature of a database system
 The database system contains not only database itself but
also a complete definition of the database structure and
constraints (catalog or meta-data)
 Insulation between programs and data, and data
abstraction
 Program-data independence + Program-operation
independence = Data abstraction
 Support of multiple views of the data
 Sharing of data and multi-user transaction processing

Reading note 1
27
 3. Database Approach

A Simplified Database System Environment

28
 3. Database Approach

DBMS
components

29
 3. Database Approach

Roles in the Database Environment

30
 3. Database Approach

Roles in the Database Environment

 Database administrators (DBA):
 Authorizing access to the database
 Coordinating and monitoring its use
 Acquiring software and hardware resources
 Database designers:
 Identifying the data to be stored
 Choosing appropriate structures to represent and store this data
 Software Engineers
 System analysts: determining specifications of end users
 Application programmers: implementing these specifications as
programs.
 End users
 People whose jobs require access to the database through the
applications
31
 3. Database Approach

Roles in the Database Environment

 Workers behind the Scene
 DBMS system designers and implementers
• Design and implement the DBMS modules and interfaces as a
software package
 Tool developers
• Design and implement tools
 Operators and maintenance personnel
• Responsible for running and maintenance of hardware and
software environment for database system

32
 3. Database Approach

Classification of Database Management Systems

Several criteria can be used to classify DBMSs
 Data models (widely-used)
 Relational data model (SQL system), Object-oriented data
model, Object relational data model, XML-enabled data
model, XML native data model, Graph data model, ...
 Number of users:
 Single-user, multiuser
 Architectures:
 Centralized, distributed, parallel
 Cost:
 Open source, licenses

33
 3. Database Approach

Advantages of Using the DBMS Approach

 Controlling Redundancy
 Restricting Unauthorized Access
 Providing Persistent Storage for Program Objects
 Providing Storage Structures and Search Techniques
for Efficient Query Processing
 Providing Backup and Recovery
 Providing Multiple User Interfaces
 Representing Complex Relationships among Data
 Enforcing Integrity Constraints
 Permitting Inferencing and Actions using Rules and
Triggers

34
 3. Database Approach

When NOT to Use a DBMS

 Overhead costs of using a DBMS:
 High initial investment in hardware, software, and training
 The generality that a DBMS provides for defining and
processing data
 Overhead for providing security, concurrency control,
recovery, and integerity functions
 More desirable to use regular files for:
 Simple, well-defined database applications not
expected to change at all
 Stringent, real-time requirements that may not be
met because of DBMS overhead
 Embedded systems with limited storage capacity
 No multiple-user access to data

35
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

36
 4. Data Models, Schemas, and Instances

Data Model
 Data model: a collection of concepts that can be used to
describe the structure of a database.
 Structure of a database: data types, relationships, and
constraints that apply to the data.
 Data models also include a set of basic operations for
specifying retrievals and updates on the database.
 A data model is a type of data abstraction.

37
 4. Data Models, Schemas, and Instances

Categories of data models

 High-level or conceptual data models: provide
concepts that are close to the way many users perceive
data
 Entity relationship model, object-oriented data model
 Representational or implementation data models:
provide concepts that are easy for end users to
understand while still being close to how data is
actually stored in a computer..
 Relational data model
 Low-level or physical data models: provide concepts
that describe the details of how data is stored in the
computer.

38
 4. Data Models, Schemas, and Instances

Database Schemas and Instances

 Database Schema: the description of a database, which
is specified during database design and is not expected
to change frequently
 Schema Diagram: a displayed schema
 Database Instance (database state, snapshot): the
data in the database at a particular moment in time

39
40
 4. Data Models, Schemas, and Instances

Relational Database Schema Diagram

41
 4. Data Models, Schemas, and Instances

Database state

42
 Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

43
 5. Three-Schema Architecture and Data Independence

Three-Schema Architecture and Data Independence

44
 5. Three-Schema Architecture and Data Independence

Three-Schema Architecture and Data Independence

 External Level
 Users’ view of the database
 Describes that part of database that is relevant to a
particular user
 Conceptual Level
 Community view of the database
 Describes what data is stored in database and relationships
among the data
 Internal Level
 Physical representation of the database on the computer.
 Describes how the data is stored in the database

45
 5. Three-Schema Architecture and Data Independence

Three-Schema Architecture and Data Independence

46
 5. Three-Schema Architecture and Data Independence

Three-Schema Architecture and Data Independence

 Data Independence: is the capacity to change the
schema at one level of a database system without
having to change the schema at the next higher levels
 Logical Data Independence:
 Conceptual schema changes (e.g. addition/removal of
entities) should not require changes to external schema or
rewrites of application programs
 Physical Data Independence:
 Internal schema changes (e.g. using different file
organizations, storage structures/devices) should not
require changes to conceptual or external schemas

47
 5. Three-Schema Architecture and Data Independence

Three-Schema Architecture and Data Independence

48
 5. Three-Schema Architecture and Data Independence

Three-Schema Architecture and Data Independence

 Objectives of Three-Schema Architecture
 All users should be able to access same data
 Users should not need to know physical database storage
details
 DBA should be able to change database storage structures
without affecting the users’ views. Internal structure of
database should be unaffected by changes to physical aspects
of storage.
 DBA should be able to change conceptual structure of
database without affecting all users

49
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

50
 6. Database Languages

Database Languages
 Data Definition Language (DDL) allows the DBA or
user to describe and name entities, attributes, and
relationships required for the application plus any
associated integrity and security constraints
 Data Manipulation Language (DML) provides basic
data manipulation operations (select, insert, update,
delete) on data held in the database
 Data Control Language (DCL) defines activities that
are not in the categories of those for the DDL and DML,
such as granting privileges to users, and defining when
proposed changes to a databases should be irrevocably
made

51
 6. Database Languages

Database Languages
 Procedural DML allows user to tell system exactly how
to manipulate data (e.g., Network and hierarchical
DMLs)
 Non-Procedural DML (declarative language) allows
user to state what data is needed rather than how it is
to be retrieved (e.g., SQL, QBE)
 Fourth Generation Languages (4GLs)
 Non-procedural languages: SQL, QBE, etc.
 Application generators, report generators, etc.

52
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

53
 7. Emerging Database Applications

Database Management Systems Framework

Application Visualization, Collaborative Computing, Mobile Computing, Knowledge-

based Systems
Layer

Layer 3: information extraction & sharing

Data Warehousing, Data Mining, Internet DBs, Collaborative, P2P & Grid
Data Data Management
Management
Layer 2: interoperability & migration
Layer Heterogeneous DB Systems, Client/Server DBs, Multimedia DB Systems,
Migrating Legacy DBs

Layer 1: DB technologies
DB Systems, Distributed DB Systems

Supporting Networking, Mass Storage, Agents, Grid Computing Infrastructure,

Parallel & Distributed Processing, Distributed Object Management
Layer

54
 7. Emerging Database Applications

Emerging Database Applications

 Emerging applications: storage and retrieval of images,
videos, data mining (large amounts of data need to be
stored and analyzed), spatial databases, time series
applications, big data, cloud …
 More complex data structures than relational
representation
 New data types except for the basic numeric and character
string types
 New operations and query languages for new data types
 New storage and retrieval methods
 New security mechanisms
 …
55
 7. Emerging Database Applications

Emerging Database Applications

 Big data storage systems, or NOSQL systems:
 Manage data for social media applications.
 Cloud storage
 Users are provided with storage capabilities on the Web.
 Data warehouses and online analytical processing
(OLAP) systems:
 Extract and analyze useful business information from very
large databases
 Support decision making
 Real-time and active database technology
 Control industrial and manufacturing processes

56
 7. Emerging Database Applications

Big data 1 Petabyte (PB) = 1,000 Terabytes (TB) = 1,000,000 Gigabytes (GB)
1 Exabyte (EB) = 1,000 Petabytes (PB)

 Traditional data: transaction records → rows & columns in

relational DBMS.
 New sources:
 Web traffic, emails, social media (tweets, posts).
 Machine-generated data: smart meters, sensors, trading systems.
 Problem: unstructured/semi-structured → not suitable for
relational DB.
 What is Big Data?
 Extremely large and complex datasets beyond typical DBMS
capabilities.
 Not a ixed size → often petabytes or exabytes (billions–trillions
of records).
 Produced at greater volume, variety, and velocity than
traditional data.

57
 7. Emerging Database Applications

Data Warehouses & Data Marts

 Data warehouse:
 Stores current and historical data from many core
operational transaction systems
 Consolidates and standardizes information for use across
enterprise, but data cannot be altered
 Provides analysis and reporting tools
 Data marts:
 Subset of data warehouse
 Summarized or focused portion of data for use by specific
population of users
 Typically focuses on single subject or line of business

58
 7. Emerging Database Applications

Online analytical processing (OLAP)

 Supports multidimensional data analysis
 Viewing data using multiple dimensions
 Each aspect of information (product, pricing, cost, region,
time period) is different dimension
 Example: How many washers sold in the East in June
compared with other regions?
 OLAP enables rapid, online answers to ad hoc queries

59
 7. Emerging Database Applications

Data mining

 Finds hidden patterns, relationships in datasets

 Example: customer buying patterns
 Infers rules to predict future behavior
 Types of information obtainable from data mining:
 Associations
 Sequences
 Classification
 Clustering
 Forecasting

60
Contents

1 Basic concepts
2 File-based Approach
3 Database Approach
4 Data Models, Schemas, and Instances
5 Three-Schema Architecture and Data Independence
6 Database Languages
7 Emerging Database Applications

61
62