Database Systems

Concepts and Architecture

Chapter 1
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

2
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

3
Introduction
} Collection of related data with an
implicit meaning
} Represents some aspect of the real
world
} Designed, built, and populated with
data for a specific purpose.
} UNIVERSITY database
} Information concerning students, courses, and grades in
a university environment
à Data records: STUDENT, COURSE, SECTION,
GRADE_REPORT, LECTURER
4
Introduction
} Traditional database: textual or numeric
information
} Multimedia database:
images, audio clips, and
video streams

} Spatial database: Store and

analyze maps, weather data,
and satellite images
} Temporal database: historical data

5
Introduction
} Database management system (DBMS)
} Collection of programs to create and maintain a database

6
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

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

8
 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
9 (clientNo, fName, lName, address, telNo)
Contract files
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

10
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
11
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

12
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

13
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

14
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).

15
Database Approach

16
Database Approach
} Data
} Known facts that can be recorded and that have implicit
meaning
} Information? Knowledge? Wisdom?
} 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

17
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

21
Database Approach
} Database System = Database + DBMS software
} 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
22
A Simplified Database System Environment

23
Database Approach
} Roles in the Database Environment
} Database Administrator (DBA)
} Database Designers
} Application Programmers
} End Users

24
Database Approach
} Database administrators (DBA) are responsible for:
} Authorizing access to the database
} Coordinating and monitoring its use
} Acquiring software and hardware resources
} Database designers are responsible for:
} Identifying the data to be stored
} Choosing appropriate structures to represent and store
this data

25
Database Approach
} Application programmers
} Implement these specifications as programs
} End users
} People whose jobs require access to the database

26
Database Approach
} 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

27
Database Approach
} DBMS components:

28
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

29
When Not to Use a DBMS
} 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

30
Database Approach
} History of database systems
} First generation: Hierarchical and Network
} Second generation: Relational
} Third generation: Object-Relational, Object-Oriented

31
Example of Network Model Schema

32
Example of Relational Model Schema

33
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture & Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

34
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

35
Three-Schema Architecture and Data Independence

36
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

37
Three-Schema Architecture and Data Independence

38
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

39
Three-Schema Architecture and Data Independence

40
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

41
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

42
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])

43
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Data Management Systems Framework

44
Data Models, Database Schema and 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)
} ER, Object-Oriented, … Describe data at
the conceptual &
} Record-based (Representational) external levels
} Relational, Network, Hierarchical
} Physical: used to describe data at the internal level

45
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

46
Relational Database Schema

47
48
Database state
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

49
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
} Emergence of Big Data Storage Systems and NOSQL Databases
} …
READ MORE AT 1.7 (p23)
50
Contents

1 Introduction
2 File-based Approach and Database Approach
3 Three-Schema Architecture and Data Independence
4 Database Languages
5 Data Models, Database Schema, Database State
6 Database Applications

51
52