Overview of Database Systems Kanda Runapongsa (krunapon@kku.ac.th) - - PowerPoint PPT Presentation
Overview of Database Systems Kanda Runapongsa (krunapon@kku.ac.th) Dept of Computer Engineering Khon Kaen University Overview What is a Database, in particular, a relational DBMS? Why should we consider a DBMS to manage data? How
2
What is a Database, in particular, a relational
Why should we consider a DBMS to manage
How is application data represented in a
How is data in a DBMS retrieved and
How does a DBMS support concurrent access
What are the main components of a DBMS? Who is involved with databases in real life?
3
The success of an organization
Being able to acquire accurate data Being able to acquire timely data Being able to manage data effectively Being able to analyze data
Information processing is a rapidly
We can acquire more and more data,
4
A database
A very large, integrated collection of
Models real-world enterprise
Entities (e.g., students, courses) Relationships
A Database Management System
A software package designed to store
5
A motivating scenario
A company has a large collection (500 GB)
products, sales, and so on
This data is accessed concurrently by several
employees
Questions about the data must be answered
quickly
Changes made to the data by different users
must be applied consistently
Access to certain parts of the data must be
restricted
6
We can try to store the data in files This approach has many drawbacks
We probably do not have 500 GB of
We must therefore store data in a
Even if we have 500 GB of main
7
Using Files to store and manage data
We have to write special programs to
These programs are likely to complex
We must protect the data from
8
Using Files to store and manage data
We must ensure that data is restored to
Operating systems provide only a
This is not sufficiently flexible to enforce
security policies in which different users have permission to access different subsets of the data
9
Reduced application development
This can be done by using queries
Data independence
The DBMS can provide an abstract view
Efficient data access
A DBMS utilizes a variety of
10
Concurrent access
Users can think of the data as being
Crash recovery
The DBMS protects users from the
Uniform data administration
When several users share the data,
11
Data integrity
The DBMS can enforce integrity
For example, before inserting salary
Security
The DBMS can enforce access controls
12
A data model is a collection of high-
A DBMS allows a user to define the
What is the model that most DBMSs
The relational data model
13
The data models of the DBMS are closer
A semantic data model is a more abstract,
What is a widely used semantic data
The entity-relationship model
14
Most widely DBMS data model today Central data construct: a relation
A set of records A table with rows and columns
Every relation has a schema, which
A schema specifies the relation’s
15
The integrity constraints are
For example
We could specify that every student has
The ability to specify uniqueness of the
16
Relational data model
IBM’s DB2, Informix, Oracle, Sybase, Microsoft’s
Access, FoxBase, Paradox, Tandem, and Teradata
Hierarchical model
IBM’s IMS DMS
Network model
IDS and IDMS
Object-oriented model
ObjectStore and Versant
Object-relational model
IBM’s DB2, Informix, ObjectStore, Oracle, Versant
17
The database description consists of
Physical schemas Conceptual schemas External schemas
A Data Definition Language (DDL) is
18
All DBMS vendors also support SQL
External Conceptual
Physical Disk
19
The conceptual schema (the logical
In a relational DBMS,
The conceptual schema describes all
The choice of relations, and the
20
The physical schema summarizes
We must decide what file
21
External schemas allow data access to
Any given database has exactly one
Each external schema consists of a
22
A view is conceptually a relation, but
Rather, they are computed using a
A user can treat a view just like a
Even though the records in the view
23
Conceptual schema:
Students(sid:string, name:string, login:string,
age:integer, gpa:real)
Courses(cid:string, cname:string,
credit:integer)
Enrolled(sid:string, cid:string, grade:string)
Physical schema:
Relations stored as unordered files Index on first column of Students
External schema (view):
Course_info(cid:string, enrollment:integer)
24
Application programs are insulated
Data independence is achieved
One of the most important benefits of
25
Physical data independence
Protection from changes in physical structure
Logical data independence
The conceptual schema insulates users from
Protection from changes in logical structure of
The users can be shielded from changes in the
26
Questions involving the data stored in
A DBMS provides a specification
A DBMS enables users to create,
27
An important task of a DBMS is to
A DBMS allows users to think of their
28
Concurrent execution of user programs is
essential for good DBMS performance
Because disk accesses are frequent, and
relatively slow, it is important to keep the cpu humming by working on several user programs concurrently
Interleaving actions of different user programs
can lead to inconsistency :e.g., check is cleared while account balance is being computed
DBMS ensures such problems don’t arise: users
can pretend they are using a single-user system
29
A locking protocol
A set of rules to be followed by each
To ensure that even though actions of
A lock is a mechanism used to control
30
Two kinds of locks are commonly
Shared locks on an object can be held by
When do we want to use shared locks?
Read an object
Exclusive locks on an object ensures that
When do we want to use exclusive locks?
Write an object
31
A transaction is any one execution of
This is the basic unit of change as
Partial transactions are not allowed The effect of a group of transactions
32
Transactions can be interrupted before
A DBMS must ensure that the changes
For example, if the DBMS is in the middle of
transferring money form account A to account B, but the crash occurs, both accounts must be in the same state before the transfer
33
A transaction is an atomic sequence
“atomic sequence”: all instructions in
There is no effect of the execution of
Each transaction, executed
34
DBMS ensures that execution of {T1, .., Tn} is
Before reading/writing an object, a transaction
requests a lock on the object, and waits till the DBMS gives it the lock. All locks are released at the end of the transaction
Idea; If an action of Ti (say, writing X) affects Tj
(which perhaps reading X), one of them, say Ti, will obtain the lock on X first and Tj is forced to wait until Ti completes; this effectively orders the transactions
What if Tj already has a lock on Y and Ti later
requests a lock on Y?
35
DBMS ensures atomicity (all-or-
Idea: keep a log (history) of all
36
Before a change is made to the
After a crash, the effects of partially
37
To do a database recovery The DBMS maintains a log of all writes to the
database
A crucial property of the log Each write action must be recorded in the log
(on disk) before the corresponding change is reflected in the database itself
Why it must be recorded on disk first?
If the system crashes just after making the
change in the database but before the change is recorded in the log, the DBMS would be unable to detect and undo this change
38
The property that the change in database
To ensure this property, the DBMS must
The log is used to ensure that the
39
The following actions are recorded in
Ti writes an object: the old value and the
Log record must go to disk before the
change in DBMS
Ti commits/aborts: a log record
Log records chained together by Xact
40
Bringing the database to a consistent
Why is it slow? The DBMS must ensure that the effects
The DBMS must also ensure that the
41
We can speed up this slow process by?
Periodically forcing some information to
The periodic operation is called a
Periodic checkpointing can reduce the
However, checkpointing too often also
42
A typical DBMS has a layered
The DBMS accepts SQL commands
When a user issues a query, the parsed
43
A query optimizer uses information
An execution plan is a blueprint for
44
The codes that implements relational
A file in a DBMS is a collection of
The files and access methods layer
45
The lowest layer of the DBMS software
Higher layers allocate, deallocate, read,
The DBMS supports concurrency and
46
The transaction manager ensures
The lock manager keeps tracks of
47
The recovery manager is responsible
The disk space manager, buffer
48
End users and DBMS vendors DB application programmers
E.g., smart webmasters
Database administrator (DBA)
Designs logical/physical schemas Handles security and authorization Data availability, crash recovery Database tuning as needs evolve
49
DBMS used to maintain, query large
Benefits include recovery from
Levels of abstraction give data
50
A DBMS typically has a layered
DBAs hold responsible jobs and are
DBMS R&D is one of the broadest,
51
Database Management Systems,