Chapter 1:

Database System Concepts

and Architecture

Jan - 2014 1
Outline
 File-based Approach and Database Approach

 Three-Schema Architecture and Data

Independence

 Database Languages

 Data Models, Database Schema, Database

State

 Data Management Systems Framework

Jan - 2014 2
Outline
 File-based Approach and Database Approach

 Three-Schema Architecture and Data

Independence

 Database Languages

 Data Models, Database Schema, Database

State

 Data Management Systems Framework

Jan - 2014 3
File-based Approach
 Data is stored in one or more separate
computer files
 Data is then processed by computer
programs - applications

Jan - 2014 4
File-based Approach

Jan - 2014 5
File-based Approach
 Problems/Limitations
 Data Redundancy
 Data Inconsistency
 More details: see [2]

Jan - 2014 6
File-based Approach

Jan - 2014 7
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.

Jan - 2014 8
File-based Approach
 Shared File Approach
 Other 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.

Jan - 2014 9
 Files

Customer
Files
Applications File
Applications Customer
Customer Stock
Orders File
File

Order
Customer
File Orders
Stock
Customer
File
File
Customer
Customer Stock Invoicing
Invoicing File

Order
Order
File
File

Purchase
Stock
File Orders
Purchase Supplier
Orders
Supplier File
File

Stock
File
Stock
Stock
Control
Control
Order
File Shared file approach

Jan - 2014 10
Database Approach
 Arose because:
 Definition of data was embedded in application
programs, rather than being stored separately and
independently
 No control over access and manipulation of data
beyond that imposed by application programs
 Result:
 The Database and Database Management
System (DBMS).

Jan - 2014 11
 Database Approach

Jan - 2014 12
Database Approach
 Data
 Known facts that can be recorded and that have
implicit meaning
 Information? Knowledge?
 More: www.whatis.com
 Database: Shared collection of logically
related data and a description of this data,
designed to meet the information needs of an
organization

Jan - 2014 13
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 general-
purpose software system that facilitates the processes of
defining, constructing, manipulating, and sharing
databases among various users and applications (or a
software system that enables users to define, create,
maintain, and control access to the database)

Jan - 2014 14
Database Approach
 Data Definition Language (DDL)
 Permits specification of data types, structures and any data
constraints to be stored in the database
 All specifications are stored in the database
 Data manipulation language (DML).
 Query language: retrieve (query), update (insert, delete, modify)
 Controlled access to database may include:
 a security system
 an integrity system
 a concurrency control system
 a recovery control system
 a user-accessible catalog
 Database System = the Database + DBMS software

Jan - 2014 15
Database Approach
 Roles in the Database Environment
 Database Administrator (DBA): responsible for
 authorizing access to DB
 coordinating & monitoring its use
 acquiring software and hardware resources
 security breach, poor response time

 Database Designers: responsible for:

 identifying the data to be stored in DB
 choosing appropriate structures to represent and store this data
 Application Programmers
 End Users
 More details: see [1,2]-chapter 1

Jan - 2014 16
Database Approach
 DBMS components:

Jan - 2014 17
Database Approach
 Characteristics of the Database Approach:
 Self-describing nature of a database system
 Insulation between programs and data, and data
abstraction
 Program-data independence + Program-operation
independence = Data abstraction
 A data model is a type of data abstraction
 Support of multiple views of the data
 Sharing of data and multi-user transaction processing
 Other advantages of using the DBMS approach: see
[1]-1.6

Jan - 2014 18
Database Approach
 History of database systems
 First generation: Hierarchical and Network
 Second generation: Relational
 Third generation: Object-Relational, Object-
Oriented
 Brief history of database applications
 see [1]-section 1.7

Jan - 2014 19
Example of Network Model
Schema

Jan - 2014 20
Example of Relational Model
Schema

Jan - 2014 21
Outline
 File-based Approach and Database Approach

 Three-Schema Architecture and Data

Independence

 Database Languages

 Data Models, Database Schema, Database

State

 Data Management Systems Framework

Jan - 2014 22
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
Jan - 2014 23
 Three-Schema Architecture and
Data Independence
 Three-level architecture and data independence

Jan - 2014 24
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

Jan - 2014 25
Three-Schema Architecture and
Data Independence
 Internal Level
 Physical representation of the database on the
computer.
 Describes how the data is stored in the database

Jan - 2014 26
Three-Schema Architecture and
Data Independence

Jan - 2014 27
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 level
 Logical Data Independence
 Refers to immunity of external schemas to changes in
conceptual schema
 Conceptual schema changes (e.g. addition/removal of
entities) should not require changes to external
schema or rewrites of application programs

Jan - 2014 28
Three-Schema Architecture and
Data Independence
 Physical Data Independence
 Refers to immunity of conceptual schema to
changes in the internal schema
 Internal schema changes (e.g. using different file
organizations, storage structures/devices) should
not require changes to conceptual or external
schemas

Jan - 2014 29
Three-Schema Architecture and
Data Independence

Jan - 2014 30
Outline
 File-based Approach and Database Approach

 Three-Schema Architecture and Data

Independence

 Database Languages

 Data Models, Database Schema, Database

State

 Data Management Systems Framework

Jan - 2014 31
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 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

Jan - 2014 32
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.
(see [2])

Jan - 2014 33
Outline
 File-based Approach and Database Approach

 Three-Schema Architecture and Data

Independence

 Database Languages

 Data Models, Database Schema, Database

State

 Data Management Systems Framework

Jan - 2014 34
Data Models, Database Schema,
Database State
 Data Model: An integrated collection of concepts
for describing data, relationships between data,
and constraints on the data in an organization

 Categories of data models include:

 Object-based (Conceptual) Describe data at
 ERD, Object-Oriented, … the conceptual &
 Record-based (Representational) external levels
 Relational, Network, Hierarchical

 Physical: used to describe data at the internal level

Jan - 2014 35
Data Models, Database Schema and
Database State
 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 State (Snapshot): the data in the
database at a particular moment in time

Jan - 2014 36
Data Models, Database Schema
and Database State

Jan - 2014 37
Outline
 File-based Approach and Database Approach

 Three-Schema Architecture and Data

Independence

 Database Languages

 Data Models, Database Schema, Database

State

 Data Management Systems Framework

Jan - 2014 38
Data Management Systems Framework
 Where are we?
Application Visualization, Collaborative Computing, Mobile Computing,
Knowledge-based Systems
Layer

Layer 3: information extraction & sharing

Data Warehousing, Data Mining, Internet DBs, Collaborative, P2P &
Data Grid 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

Jan - 2014 39
Data Management Systems Framework

 Extending database capabilities for new applications

 Example applications: storage and retrieval of images,
videos, data mining (large amounts of data need to be
stored and analyzed), spatial databases, time series
applications, …
 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
 …
Jan - 2014 40
Summary
 File-based Approach and Database
Approach
 Three-Schema Architecture and Data
Independence
 Database Languages
 Data Models Database Schema and
Database State
 Data Management Systems Framework
 Next week: ER Model

Jan - 2014 41
Jan - 2014 42