Transcript Introduction to Databases

Introduction to
Database Systems
Why databases are important
• Because we use databases all
the time!!
–
–
–
–
–
Google
Youtube
Facebook
E-shopping
Banking
Databases!
• Almost all private and official
organisations use databasers,
one way or another
RHS – 2009
2
Why databases are important
• What is it, actually…?
– A database is just a wellstructured collection of data,
where data can be easily stored
and retrieved
• Why is that useful…?
– Data becomes information
– Information becomes knowledge
– Knowledge becomes wisdom
RHS – 2009
3
Types of Databases
• There are several types of databasesystems:
– Hierachical databases
– Network-databases
– Object-oriented databases
– Relational databases
• Relational databases is by far the most
common type, we will concentrate on that
type in the following
RHS – 2009
4
Types of Databases
• Relational databases are (still) the most
common type of database…
• …but does not fit perfectly with the objectoriented paradigm
• Mapping from object model to relational
model is a very common task
• Why is this so…? Inertia, probably…
RHS – 2009
5
A database system is often called
DBMS
• Data
• Base
• Management
• System
RHS – 2009
6
What is a DBMS…?
• A DBMS is the database itself, plus a
number of programs used for interaction
and maintenance of the database
– Backup, restore
– Performance monitoring
– User interface
– Interfaces to other programs
– Etc.
RHS – 2009
7
What is a DBMS…?
Computer
Maintenance
Driver
Database
Performance
User Interface
RHS – 2009
8
What is a DBMS…?
Computer
Computer
Maintenance
Driver
Database
Performance
User Interface
RHS – 2009
9
What is a DBMS…?
Computer
Computer
Maintenance
Driver
Performance
Database
Computer
User Interface
RHS – 2009
10
What is a DBMS…?
• The exact physical configuration of
database and programs is not as such
important – it is a matter of setup
• No difference in functionality
• It is more relevant to think of such a
system in terms of layers
RHS – 2009
11
What is a DBMS…?
User interface layer
Business
logic layer
DBMS
layer
User Interface
Business Logic
Data processing
Maintenance
Performance
Driver
RHS – 2009
Database
12
What is a DBMS…?
• A multi-layer (or multi-tier) structure is well
suited for the Internet!
• Database on a central server, user
interface through an Internet browser
• We use that every day!
– Facebook
– Net-banking
– World of Warcraft… 
RHS – 2009
13
DBMS – a closer look
• In many environments, it is absolutely
critical that the DBMS functions perfectly!
– Financial systems
– Emergency systems
– Traffic
• Perfectly – no persistent errors must
become visible to the outside world
RHS – 2009
14
DBMS – a closer look
• Many functions in a DBMS handle error
management and prevention
– Transaction support
– Concurrency management
– Recovery services
– Authorisation services
– Integrity services
RHS – 2009
15
DBMS – Transactions
• A transaction is a set of changes to the
state of the database
• Before the transaction, the database is in
a valid and consistent state
• After the transaction, the database is in a
valid and consistent state
• Example: Bank transfer
RHS – 2009
16
DBMS – Transactions
Account A:
20000
Transfer 5000
From A to B
Account B:
10000
Transfers history:
RHS – 2009
17
DBMS – Transactions
Account A:
20000
Step 1:
Deduct 5000
from A
Account B:
10000
Transfers history:
RHS – 2009
18
DBMS – Transactions
Account A:
15000
Step 2:
Deposit 5000
to B
Account B:
10000
Transfers history:
RHS – 2009
19
DBMS – Transactions
Account A:
15000
Step 3:
Update
transfers
history
Account B:
15000
Transfers history:
RHS – 2009
20
DBMS – Transactions
Account A:
15000
Account B:
15000
Transfers history:
Transferred 5000 from A to B
RHS – 2009
21
DBMS – Transactions
• What if…
– Power goes out between Step 1 and 2
– A disk error occurs
–…
• In any case, the database could be left in
an inconsistent state!
• For a transaction, either none or all of the
steps must be completed
RHS – 2009
22
DBMS – Transactions
• In general, transactions must be ACID:
– Atomic – either all steps or none
– Consistent – leaves the database in a
consistent state
– Isolated – other processes cannot see a
transaction ”in progress”
– Durable – the change is permanent (but the
affected data may of course be updated later)
RHS – 2009
23
DBMS – Concurrency
• Concurrency management is somewhat
related to transaction
• The DBMS must be able to allow multiple
clients concurrent access to the database
• No problem when reading data
• Becomes tricky when clients are writing
data…
RHS – 2009
24
DBMS – Concurrency
• The DBMS must ”lock” certain parts of the
data in the database, when a uses wants
to update it
• May cause requests to queue up…
• Conflicting goals:
– Ensure consistency (lock enough)
– Ensure efficiency (lock only what is needed)
RHS – 2009
25
DBMS – Recovery
• In case of failure (of any kind), the DBMS
must be able to recover the database
• Recover: When DBMS is back online, the
database must be in a consistent state
• Often achieved by using duplicate
databases, either online or offline
• Online duplicate enables immediate
recovery
RHS – 2009
26
DBMS – Authorisation
• Many databases contain
sensitive data
• We may wish to limit the
actions a specific user can
perform on the database
• Such user rights can be
defined on several levels
RHS – 2009
27
DBMS – Authorisation
• User rights
– Only read data, or read and write
– Only work with certain parts of data
– Only do certain updates to data
– Only allowed to do maintenance
– And so on…
• User rights are often defined in terms of
defining user roles
RHS – 2009
28
DBMS – Authorisation
Role Administrator
Actions
Maintenance
Super-user
Manager
Empolyee
Add/delete
tables
Yes
No
No
No
No
Make
Backup
Yes
Yes
No
No
No
Yes
No
Yes
No
No
Yes
No
Yes
Yes
No
Yes
No
Yes
Yes
Yes
Write data
Write data
(predefined)
Read data
RHS – 2009
29
DBMS – Integrity
• Data can be incorrect on
multiple levels
• Each piece of data can
be correct as such, but a
collection of data might
be inconsistent
• Can be handled using
constraints on data
RHS – 2009
30
DBMS – Integrity
• A constraint can specify cross-field
consistency rules
• ”Sum of fields 2- 10 may not exceed value
in field 1”
• ”Field 2 must at most be half of field 1”
• Updates that will break a constraint are not
allowed by the DBMS
RHS – 2009
31