Transcript Database Concept

CSE2132
Database Systems
Week 1 Lecture
Database Concepts
Database Concepts
1. 1
Data - An Informal Definition
Data are raw facts. Facts concerning people, places, events, or other
objects/concepts.
They can be of any type:
qualitative or quantitative
historical or prediction
abstract or subjective
Sources
(1)External to an organization: telephone calls, turnaround
documents, remote workstation (point of sale) transactions.
(2)Internal to an organization: financial and management accounting
production and operation management, payroll and personnel.
(3)From “strange” places: metal sensor on the street, digitizing of an
image, hardware failure messages.
Database Concepts
1. 2
Data and the Organization
• Data by itself is useless unless it is some how aggregated,
organized and prepared in a form convenient for decision
making or other organizational activities.
DATA
INFORMATION
ACTION
A lack of data leads to inadequate information and thus ill-informed
decisions and business collapse.
Database Concepts
1. 3
Data as Resource
Why use database?
Data is a valuable corporate resource which needs adequate, accuracy,
consistency and security controls.
The centralized control of data means that for many applications the data
will already exist, and facilitate quicker development.
Data will no longer be related by application programs, but by the
structure defined in the database.
Easier to maintain systems
Database Concepts
1. 4
Basic Definitions
Database: A collection of related data
Schema: Some part of the real world about which data is
stored in the database.
Database Management System(DBMS):A software package to
facilitate the creation, maintenance and protection of a
computerized database.
Database Concepts
1. 5
A DBMS and Interfaced Utilities
(Frontend)
Report
Generator
Graphics
Query Language(e.g.SQL)
DBMS
Data Dictionary
Data
Application
Generator
Dataentry
Screens
Application
Programs
Database Concepts
1. 6
Traditional File Systems
Databases are often contrasted to traditional file systems
though they are now rarely used. But as the problems that
existed were the impetus for the development of the
“Database Concept” it is worth noting some of these.
- Applications were often considered in relative isolation.
- Data that should have been together was not.
- The potential for flexible enquiry and reporting was limited.
- All validations were in the programs.
- Procedures were required for backup and recovery.
- All programmers had access to all records.
- There was limited concurrent access.
Database Concepts
1. 7
Objectives of Database Technology
· Program-Data Independence
· Minimal Data Redundancy
· Increased Data Sharing
· Improved Data Quality
· Improved Security of Data
· Improved Access to Data
· Reduced Program Maintenance
· Inter-relate Data Through Model
Database Concepts
1. 8
Program-Data Independence
· Data is stored independently of the programs
· The degree to which descriptions of data are embedded in
application programs.
· Can the database structure be changed with no impact on
programs ?
· Role of the database catalog or dictionary.
· Maintenance costs are high !
Database Concepts
1. 9
Degrees of Data Independence
· Device Characteristics
· Blocking Factors
· Data Access Organization
· Physical Record Location
Greater
Independence
· Logical Views (Local)
· Virtual Data Items
· Virtual Records
· Data Value Characteristics
· Data Element Name Only
Database Concepts
1. 10
Inter-Related Data
CLAIMS
RENEWALS
D
B
M
S
AGENCY
RENEWALS
CLAIMS AGENCY
Data related by structure
Flexible Inquiry easier
QUERY
LOCAL
VIEWS
Database Concepts
1. 11
Application Development
· Some companies use database technology as just a multikey access method:
CLAIMS
DBMS
CLAIMS DB
PREMIUMS
DBMS
PREMIUMS DB
Database Concepts
1. 12
Application Development
· Applications & their data needs are not considered in
isolation.
· Centralized control of one or several databases takes
place. i.e database administration.
· Data administration is seen as an important part of
system development.
CLAIMS
PREMIUMS
D
B
M
S
CLAIMS
PREMIUMS
Database Concepts
1. 13
Semantic Data Integrity
· Validation or integrity rules may be defined and
automatically invoked at run time by the DBMS regardless of
the source of update i.e. application program, 4GL screen or
query language.
· Significant variation exists among DBMS in the level of
support for semantic data integrity.
· ISO suggest that 100% of all enterprise rules should be held
in the conceptual schema, and specifically none in
application programs.
· An area of significant development during the 1990's.
Database Concepts
1. 14
Semantic Data Integrity
Application
Programs
4GL Screens &
Stored Procs.
Interactive
Query
Language
D
B
M
S
CATALOGUE
Data Definitions &
Integrity Rules
STORED DATA
Database Concepts
1. 15
Database Transactions
A database transaction may be defined as a group (one or more) of
database operations which need to be treated as one atomic unit
which may either succeed or fail. That is all the database operations
must succeed or all the database operations must fail.
If this is not the case tasks can be half complete and the database
loses it’s integrity.
EMP#
E1
E2
SALARY
50,000
18,000
DEPT
DEPT#
T0TAL SALARY
D1
D1
D1
D2
68,000
90,000
Database Concepts
1. 16
Concurrency Control
Problems such as the “lost update” can arise if no measures are taken to
support concurrent transactions. The DBMS should support multiple
concurrent transactions potentially using the same data and ensure that
the data remains consistent at all times.
Transaction 2
Transaction 1
Part 2
read
Part 2
QOH 10
read
QOH 10
QOH=QOH+10
write
Part 2
Part 2
QOH 10
QOH 20
QOH=QOH-5
write
Part 2
QOH 5
Database Concepts
1. 17
Record Locking
To avoid concurrency problems, transactions must be made
logically serial. One common technique used is record
locking. That is, a transaction can lock a record, preventing
update by another transaction, until completed. Although
TX2 waits a little while both complete without error.
TX2
TX1
Part 10
Part 20
Part 30
lock
lock
lock
end trans.
time
Part 301
lock
Part 20
wait
Part 20
lock
Part 101
lock
end trans.
Database Concepts
1. 18
Deadlock
The technique of locking introduces it’s own problems.DBMS packages
with record locking must also be able to handle "deadlock" situations.
TRANSACTION A
lock 10
request 20 wait
TRANSACTION B
lock 20
request 10 wait
Note that both transactions above could wait forever!
Deadlock detection algorithms can be used to detect this situation
amongst locked records. Usually the least expensive transaction is
aborted.
Database Concepts
1. 19
Restart and Recovery
· Many DBMS maintain redundant copies of data in a log file to
support recovery from failures. This may be on disk or tape.
· Copies of record images after update , together with periodic dumping
of the database allow for rollforward recovery where the database
contents are not intact. A dump of the database is an exact copy of the
content of the database at some point in time.
· Copies of record images before update allow rollback recovery of
changes made by incomplete or failed transactions.
· Many DBMS take periodic checkpoints to reduce recovery time in the
event of failure. A rollback recovery then goes back to the checkpoint
rather than the start of the transaction.
Database Concepts
1. 20
Rollforward Recovery
A log file of After Images of records
changed since the last dump was taken
are applied in a forward order thru time.
A dump of the database
from the previous period.
The restored database
shows no effect of the
major disaster such as a
“disk crash”.
Database Concepts
1. 21
Rollback Recovery
A Log file of Before Images
of records which are changed
are kept. If a transaction fails,
the before images are written
back to the database in reverse
order thru time. When the start
of the failed transaction is
reached the rollback stops.
The restored database
in a state as if the
transaction never occurred
so no effects of the transaction
failure are apparent.
Database Concepts
1. 22
Security
· Each user may require identification with a user-id and
password.
· Users may be limited in the data they can see and what
actions they can perform on that data.
· The DBMS may encrypt and decrypt data as it is stored and
retrieved.
· Many systems now provide data value sensitive security.
Database Concepts
1. 23
Easy Access to Data
· High level query languages provided by DBMS provide
easy access to data
20 READ #1, CUSTNO, NAME, BAL
30 IF END #1 GO TO 80
40 IF BAL > 200
50
PRINT CUSTNO, NAME, BAL
60 ENDIF
70 etc
VERSUS
SELECT CUSTNO, NAME, BAL FROM CUSTDB
WHERE BAL > 200;
Database Concepts
1. 24
Data Models
A way of viewing or abstracting the data.
There are a number of data models on which current
generation DBMS are based:
Hierarchical, Network, Relational,
Extended Relational and Object-Oriented
Database Concepts
1. 25
The Costs of DBMS
The initial purchase cost
Planning and design
Database education
Application conversion
System overheads (response)
Complexity of support
Database Concepts
1. 26