Databases I
1.
Introduction
1
[email protected]
Databases I
Course Overview:
•Introduction
•ER modelling
•Relational database design
• Transforming ER model into relational DB model
• Design of the database schema
•Normalization
•Referential Integrity: Constraints
•Implementation
• DDL: Creation of database, tables and fields
• Loading data into the database
•Structured Query Language (SQL)
• Simple queries
• Complex queries: Joins, views, nested queries
•Referential Integrity: Temporal Data and Triggers
•Physical data organization
•Indexing Structures
Databases I
Reading List
• Ramez Elmasri, Shamkant Navathe, Fundamentals of Database Sytems, 7th
Edition, Global Edition, 2015 [El]
• Carlos Coronel, Steven Morris, Database Systems, Design, Implementation &
Management, 13th edittion, 2018 [Co]
• Alfons Kemper, Andre Eickler, Datenbanksysteme, 9th edition, 2013 [Ke]
3
[email protected]
Databases I
Why would one use a database with database management
system (instead of working with simple files)?
4
[email protected]
Databases I
What is a Database?
• A database allows the storage and management of large - structured or
unstructured – data volume.
• Traditional databases (relational databases) store structured data that depend on
the existence of a data model.
• The data is not structured according to the needs of an individual application, but
rather according to a previously defined section from the real world (“miniworld”).
• The structural description of the data model (meta data) is called the database
schema and is stored in the database dictionary or database catalogue .
• The database management system has exclusive control over the data, not a
singular application program.
• Data in a database is persistent and shareable [Co]
• Multiple users with different needs can work via different applications with the
same data but get different views of it.
5
Databases I
Characteristics of a database approach –
versus a file-based approach [El]
• Self-describing nature of a database system
A fundamental characteristic of the database approach is that the database system
contains not only the database itself but also a complete definition or description of
the database structure and constraints.
The database stores not only data but also the meta-data (structure, schema). The
structure is stored in the data dictionary (sometimes also called data catalogue).
6
[email protected]
Databases I
Characteristics of a Database Approach [El]
• Insulation between programs and data, and data abstraction
The database approach brings with it a program-data-independence. This is
achieved via data abstraction.
“ A DBMS provides users with a conceptual representation of data that does not
include many of the details of how the data is stored or how the operations are
implemented. Informally, a data model is a type of data abstraction that is used
to provide this conceptual representation. The data model uses logical concepts,
such as objects, their properties, and their interrelationships, that may be easier
for most users to understand than computer storage concepts. Hence, the data
model hides storage and implementation details that are not of interest to most
database users. “
7
[email protected]
Databases I
Characteristics of a database approach [El]
Data Dictionary
column name data type constraint
name char(30) UTF-8
ID int(6)
program char(35)
Student Data
name ID program
Smith 40 CS
Dunn 50 CS
O‘Reilly 35 Management
Internal Storage Format
Data Item Name Starting Position in Record Length in Characters (bytes)
name 1 30
ID 31
program 37
8
[email protected]
Databases I
Differences between a database approach and a file-based
approach [El]
• Support of multiple views of the data
Different applications provide different views of the data to the user.
• Sharing of data and multiuser transaction processing
The DBMS provides concurrency control to ensure that multiple users
(applications) that write on the data of the database at the same time, do so in
such a way that the data stays correct (consistent). Each transaction must have
the following properties:
– Atomicity
– Consistency
– Isolation
– Durability
9
[email protected]
Databases I
Database Abstraction Levels
Application 1 Application 2 User
Aplication Requests,
Query Processing
Logical Level
Data
Physical Level
10
Databases I
Levels of Abstraction
• Physical level - internal view: responsible for disk storage / RAID, storage
structures (e.g. B-Trees, hashing), buffer and page management.
• Logical level - conceptual view: Description of the overall structure of the data -
regardless of its physical storage (database schema).
• External views: Views of the user / applications: show and process particular
parts of the data (that is relevant for the user / application). Works with subsets
of the data.
11
[email protected]
Databases I
DB / DBMS Architecture
User Application 1 Application 2 Administrative Tool
DML und Query Language DDL und DCL
Query Processing, Transaction Schema Administrative Tasks,
Query Optimization Management Management
physical Organisation of Data
Logfiles Indices (Raw) Data DataDictionary
12
Databases I
SQL Language Components
SQL Language Components
• DDL : Data Definition Language: instruction set for creating and
changing a database schema (= DB structure)
• DCL : Data Control Language: instruction set for the
administration of the database.
• DML : Data Manipulation Language: Instruction set for inserting,
upda ng and dele ng values in the database, as used e.g. in
CRUD applications.
• Query language: instruction set for reading the data of a DB
• SQL comprises all 4 language parts / command sets.
In which order are the language components needed when designing,
implementing and then using a DB?
13
[email protected]
Databases I
Tasks of the DBMS (Database Server)
14
[email protected]
Databases I
Database Design
Starting Point
Section of the real
world, „miniworld“
conceptual schema of the database
15
Databases I
Real World: University
Students Professors
Courses
conceptual modelling
st_number pr_number
Student Professor
name
name
take
teach
course_number
Course
title 16
Databases I
History of Database Development
1970
1980
1990
2000
2010
17
[email protected]
