Databases

Author: Dipl.-Inf. (FH) Salvatore Sabba

Lecture: 2
Content: Administrative
Course of
studies: Information Technology
Semester: WS 2024 / 2025
Administrative
• Registration FORMS
Please fill out the following FORMS to
register yourself to my lecture/exercise

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
2
End
Administrative
Databases

Author: Dipl.-Inf. (FH) Salvatore Sabba

Lecture: 2
Content: Chapter 3
Course of
studies: Information Technology
Semester: WS 2024 / 2025
Chapter 3: IT specialist
• Actual Status:

 User:
The requirements have been discussed!

 Theorist:

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
Concepts have been created.

 And now?
 We need software
 We need manpower

5
Chapter 3: IT specialist
• We need software that …

 … allows the user to access the database

 … enables the user to perform operations on

the data without issues

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 … manages concurrent access to the
database

Databasemanagementsystem (DBMS)
6
 system
(DBMS)
Database-
management-
Chapter 3: IT specialist

Data basis

Frankfurt University – Databases

7

© Dipl.-Inf. (FH) Salvatore Sabba

Chapter 3.1: The DBMS
• The access on the database must be
managed by a monitoring program
• Reasons:

 There is a long distance between the

user's query and the physical data

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 The road information is managed by the
monitoring program

8
Chapter 3.2: Problem list
• Persistent storage means, that the data
must be stored on the hard disk

• hard disk access is considerably

slower than main memory accesses –
impact on the performance!

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
9
Chapter 3.2: Problem list
• Question:

How can we organize the physical storage

of data, to reach less read access (hard
disk) by serving the most important
accesses on the data basis?

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
Before we can answer this question, we
need to know several things …

10
Excursion: Hard disk a)
• How works a c)
hard disk? b)

a) Track
b) Sector

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
c) Sector of track
d) Cluster of
sectors

d)
https://en.wikipedia.org/wiki/Disk_sector 11
 Chapter 3.3: Three manager
on their way to the data basis

DBMS Stored data basis

Data, which
Returns the read only
Requests a were read by
requested memory

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
stored set the read
set access
Returns the only memory
stored site
Read only
File manager memory
manager
Requests a
stored site

12
 Chapter 3.3: Three manager
on their way to the data basis
Manager Task View on data basis
DBMS determines which collection of datasets
datasets to read
File manager determines which collection of sites1 /
site to read blocks
Read only memory Knows the physical actual physical form
manager location of the

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
requested site &
executes the access

Sites1 ≠ datasets
this means that datasets which belong together should
be stored on one site, so less read only memory accesses
are needed
13
Chapter 3.4: Clustering
• Clustering takes care, that often-
requested data (which belong together), are
stored physically next to each other

 Aim:
Improving the access speed on stored data

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Supports …
 … the sequential reading of several
datasets
 … the reading of several datasets from
different files

14
Chapter 3.4: Clustering
• Example I:
Table Person – most queries are executed
by using the name

 Due to clustering … persons, whose names

lay alphabetically next to each other, would

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
be stored side-by-side.

Intra-File Clustering

15
Chapter 3.4: Clustering
• Example II:
Information are stored in two separated
tables (Person & City) and most queries
are executed by using the city

 Due to clustering … persons would be stored

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
sorted by their city side-by-side

Inter-File Clustering

16
Chapter 3.4: Clustering
• Example III:
Additional to example II we now use in
our query also the name

 Due to clustering … the data would be

stored sorted by using the city and

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
afterwards the name

➢ Can be complicated / complex

17
Chapter 3.5: Most important
storage structures
• There are four storage structures
which can improve the performance

 Index process (Indizierung)

 Hashing

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Pointer chains (Zeigerketten)

 Compression processes (Verdichtungsverfahren)

18
Chapter 3.6: Data- & DB-
administration
• Data administration (DA)

 Describes the overall management activity

 Decision which data is stored
 Aim of data storage

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
19
Chapter 3.6: Data- and DB-
administration
• Database Administration (DBA)

 Implementation of wanted concepts

 Selection & installation of the DB-System
 Creating database(s)
 Data design and data structure

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Administration of interfaces
 Responsible for availability & backup
 Decides the storage structure (e.g.
clustering, indices, …)

20
Chapter 3.6: Data- and DB-
administration
• Best practice would be:

 DBA can make changes, without that the

user recognize anything on his application

 Other user groups should only recognize

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
performance improvements

Immunity of the application

concerning storage structure
and access technic changes

21
Chapter 3.7: Summary
• Database management system (DBMS)
• Performance problem with read only
memory accesses
• Three Manager

Frankfurt University – Databases

Intra-File and Inter-File Clustering

© Dipl.-Inf. (FH) Salvatore Sabba

•

• Storage structure to improve performance

• Data- (DA) and
database administrator (DBA)

22
End Chapter 3
Databases

Author: Dipl.-Inf. (FH) Salvatore Sabba

Lecture: 2
Content: Chapter 4
Course of
studies: Information Technology
Semester: WS 2024 / 2025
Chapter 4: Three faces of a DB
• The database must be seen from three
different perspectives:

 User

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Theoretician (DA)

 IT - specialist (DBA)

25
Chapter 4.1: Two definitions
• Three parties agree to the following two
definitions.
 Definition I

A database (DB) is a collection of data,

which are saved in a data basis.

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
A DB also has a DBMS that handles
all user requests to the database.

26
Chapter 4.1: Two definitions
 Definition II

A database system is an application with a

unique DBMS, which allows the user to
implement the different databases.
The data basis of this databases are all

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
administrated with the native DBMS.

27
 Chapter 4.1: Two definitions

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
28
Chapter 4.2: Three levels
• We distinguish a DB in three levels

 Intern level
physical storage

 Extern level

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
the different views, the end user has on the
data

 Conceptual level
administrates the connection between intern
and extern level

29
Chapter 4.2: Three levels
• Consequence for a relational database
system:

 Internal level: is not relational

(storage takes place in the same way as all other
database models, e.g. pointer)

Frankfurt University – Databases

 External level: can, but does not have to be

© Dipl.-Inf. (FH) Salvatore Sabba

relational (e.g. by using C++ - object orientated
working)

 Conceptual level: is strictly relational

(interface [over DBMS] for the user)

30
Chapter 4.3: External Level
• Level of the individual user
(programmer / operators)

• Every user communicates with

the system in a different language
(e.g. operators with Java)

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
• A part of such language
is the data language

31
Chapter 4.3: External Level
• The data language works on processes,
which have to do with data and operations

 aka embedded data language

 controls all actions to the database

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Example:
SQL can be used both - alone and embedded

32
Chapter 4.3: External Level
• A data language consists of two parts

 Data Definition Language (DDL)

Definition of database objects to
administrate and structure the data basis

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Data Manipulation Language (DML)
actual access on the data

33
Chapter 4.4: Conceptual Level
• Is the fundamental set of rules and
structure of a database

 defines tables,

 describes the possible relations between the

entities, etc.

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
• All extern levels have to go through this
level

 Please note: several extern levels, only one

conceptual level

34
Chapter 4.4: Conceptual Level

If there are done any changes on the

conceptual level and the user do not
notice anything and the software is
not affected, the DBMS guarantees

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
logical data independence.

35
Chapter 4.5: Internal level
• It exists only one ‘single’ intern level –
in contrast to the external level

• It only takes care of the physical storage

of data, which was defined in the
conceptual level

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
• The aim is: to save datasets persistent and
to find them as fast as possible

36
Chapter 4.5: Internal level

If there are done any changes on the

internal level and the user do not
notice anything and the software is
not affected, the DBMS guarantees

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
physical independence.

37
Chapter 4.5: Internal level
• This kind of data independences are very
important and are expected by the user
• Also important for the developer,
otherwise there have to be quiet a lot of
adaptions due to every change

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Update application / source code
 Retraining of employees

38
Chapter 4.6: The DBA 2.0
• Detailed description of the tasks:
 Design of the conceptual level
 Conceptual schema
 Logical database design

 Design of the intern level

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Internal schema
 Physical database design

 User support
 e.g. create, update, … of databases

39
Chapter 4.6: The DBA 2.0
 Define rules with which the integrity of the
data pool should be ensured
(more specific in later chapters - constraints)

 Regulation and implementation of backups

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Reorganizations of data pool
(e.g. update index tables due to data archiving)

40
Chapter 4.7: The DBMS 2.0
• The DBMS is the software systems, via
the whole communication in between all
three levels takes place

• Decisive for the quality of a software

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
• It characterizes the design and
functionalities for each users

41
Chapter 4.7: The DBMS 2.0
• Has to support at least the following
fields:

 Data definition (DDL)

e.g. create, update, delete tables/attributes

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Data manipulation (DML)
e.g. search, insert, update, delete datasets

42
Chapter 4.7: The DBMS 2.0
 Optimization of manipulation
instructions

 Queries should be results-oriented

 Results show exactly update- or search-

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
requestions

 Several solutions are available and the

DBMS can find the optimal solution

43
 Chapter 4.7: The DBMS 2.0

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
44
 Chapter 4.7: The DBMS 2.0
 Data security and data integrity
 Let’s look at the following example
Name Firstname CityID CityID City
Becker Peter 119 26 Palermo
Mendez Daniel 212 119 Wiesbaden

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
Trapani Maria 26 212 Frankfurt
Urgesi Marco 212 … …
… … …

 CityID 26 in the city table is going to be

deleted by an user

45
Chapter 4.7: The DBMS 2.0
 The database can’t view the city information
when viewing Ms. Trapani

Conceptual problem

 This problem leads to problems

(program crash, error messages, …)

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 The integrity of the database is violated!!!

 The task of the DBMS is to prevent this

problems

referential integrity

46
Chapter 4.7: The DBMS 2.0
• Recover of data and control of
competitive accesses
 User wants to delete a city and all persons
from this city
 Deletion process …
 Deleted city from the city table

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Two out of three persons are deleted from the
person table
 Deletion process is unexpectedly interrupt
(e. g. caused by a system crash)

Data inconsistency

47
Chapter 4.7: The DBMS 2.0
 Problem can be solved with transactions

Definition (general):
A transaction is an activity which is
completed within itself in a system.

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
Definition (context with databases):
Before and after a transaction the
database system has to be consistent.

48
Chapter 4.7: The DBMS 2.0
 The DBMS should restore the last consistent
data pool during the restart of the system,
so that the status of the last properly done
transaction is established

 Transactions are controlled through the

transactions-manager, which is closely

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
connected to the DBMS

49
Chapter 4.7: The DBMS 2.0
 Data Dictionary
 All information about the database are
deposited here
 Contraint information
 Attribute information
 Table information

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
SELECT table_name FROM information_schema.tables;

 All of this information (metadata) are

normally deposited by the DBMS in an
own database, where the user can simply
have access to

50
Chapter 4.8: Summary
• Database and database system

• Architecture of the tree levels of a

database system

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
• Short description of the three levels

• Tasks of the database administrator

51
Chapter 4.8: Summary
• Different aspects of the DBMS

 DDL

 DML

 Optimization

Frankfurt University – Databases

© Dipl.-Inf. (FH) Salvatore Sabba
 Data security and data integrity

 Recovery

 Data Dictionary
52
End Chapter 4