Introduction to Database Systems

1
Introduction to Database Systems
2
Why databases are important

• Because we use databases all the time!!
• Google
• Youtube
• Facebook
• E-shopping
• Banking
• Almost all private and official organisations use
  databasers, one way or another

3
Why databases are important

• What is it, actually?
• A database is just a well-structured collection
  of data, where data can be easily stored and
  retrieved
• Why is that useful?
• Data becomes information
• Information becomes knowledge
• Knowledge becomes wisdom

4
Types of Databases

• There are several types of database-systems
• 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

5
Types of Databases

• Relational databases are (still) the most common
  type of database
• but does not fit perfectly with the
  object-oriented paradigm
• Mapping from object model to relational model is
  a very common task
• Why is this so? Inertia, probably

6
A database system is often called DBMS

• Data
• Base
• Management
• System

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

8
What is a DBMS?
Computer
Maintenance
Database
Driver
Performance
User Interface
9
What is a DBMS?
Computer
Computer
Maintenance
Database
Driver
Performance
User Interface
10
What is a DBMS?
Computer
Computer
Maintenance
Database
Driver
Performance
Computer
User Interface
11
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

12
What is a DBMS?
User inter- face layer
User Interface
Business logic layer
Data processing
Business Logic
Maintenance
Database
DBMS layer
Driver
Performance
13
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 ?

14
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

15
DBMS a closer look

• Many functions in a DBMS handle error management
  and prevention
• Transaction support
• Concurrency management
• Recovery services
• Authorisation services
• Integrity services

16
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

17
DBMS Transactions
Account A 20000
Account B 10000
Transfer 5000 From A to B
Transfers history
18
DBMS Transactions
Step 1 Deduct 5000 from A
Account A 20000
Account B 10000
Transfers history
19
DBMS Transactions
Step 2 Deposit 5000 to B
Account A 15000
Account B 10000
Transfers history
20
DBMS Transactions
Step 3 Update transfers history
Account A 15000
Account B 15000
Transfers history
21
DBMS Transactions
Account A 15000
Account B 15000
Transfers history Transferred 5000 from A to B
22
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

23
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)

24
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

25
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)

26
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

27
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

28
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

29
DBMS Authorisation
Role Actions Admini-strator Mainte-nance Super-user Manager Empolyee
Add/delete tables Yes No No No No
Make Backup Yes Yes No No No
Write data Yes No Yes No No
Write data (predefined) Yes No Yes Yes No
Read data Yes No Yes Yes Yes
30
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

31
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