Slides

An Introduction to Database Management Systems
SQL – First Contact
Informationslogistik
Unit 1: Introduction
Ronald Ortner
19. II. 2013
Ronald Ortner
An Introduction to Database Management Systems
Outline
1
An Introduction to Database Management Systems
Overview
2
SQL – First Contact
A Query Language
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
Overview
Outline
1
An Introduction to Database Management Systems
Overview
2
SQL – First Contact
A Query Language
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
SQL – First Contact
Overview
Eine Frage zum Einstieg
Eine Frage
Wie stellen Sie sich Ihren Arbeitsalltag als LogistikerIn vor?
(das ist übrigens so ziemlich die letzte Folie auf Deutsch)
Ronald Ortner
An Introduction to Database Management Systems
SQL – First Contact
Overview
Eine Frage zum Einstieg
Eine Frage
Wie stellen Sie sich Ihren Arbeitsalltag als LogistikerIn vor?
Data are important!
(das ist übrigens so ziemlich die letzte Folie auf Deutsch)
Ronald Ortner
An Introduction to Database Management Systems
SQL – First Contact
Overview
Why Database Management Systems?
Isn’t MS Excel all I need?
Issues:
Redundancy and inconsistency
How to join data in distinct files?
What if there are several users of a file? (e.g.: privileges to edit?)
Data loss
Violation of data integrity
Security (another facette of the multi-user problem)
Reinventing the wheel several times
Ronald Ortner
An Introduction to Database Management Systems
Overview
This Lecture
At the end of the term you shall be able to
query information from databases,
design databases for any purpose.
Each lecture consists of
a theoretical part (mainly concerning database design),
a practical part
(mainly dealing with the data query language SQL).
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
SQL – First Contact
Overview
Data Abstraction
There are three levels of data abstraction:
Physical level (how the data is stored, e.g. on a hard disk)
Logical level (how is the data structured)
User view (what an individual user can see)
Ronald Ortner
An Introduction to Database Management Systems
Overview
Data Models
Database management systems are based on data models.
A data model determines
how data objects can principally defined,
how data can be accessed and manipulated.
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
Overview
Data Models
Database management systems are based on data models.
A data model determines
how data objects can principally defined,
how data can be accessed and manipulated.
Accordingly, a data model consists of
a data definition language (DDL)
a data manipulation language (DML)
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
SQL – First Contact
Overview
DDL and DML
The data definition language (DDL) provides e.g. data types to store
information (e.g. numbers, strings, etc.).
The data manipulation language (DML) consists of
a query language (to ’read’ the data)
a ’genuine’ data manipulation language (to modify the data)
Ronald Ortner
An Introduction to Database Management Systems
SQL – First Contact
Overview
Intensional and Extensional Level
Any database consists of
an intensional level (the structure how the data is stored)
an extensional level (the data itself)
While the intensional level usually remains unmodified in a database’s
lifetime, the extensional level will change regularly.
Note that changing the intensional level may cause a lot of trouble.
Ronald Ortner
An Introduction to Database Management Systems
Overview
Several Types of Data Models
There are several types of data models:
network model
hierarchical model
object oriented model
relational model
deductive model
XML
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
Overview
Several Types of Data Models
There are several types of data models:
network model (outdated)
hierarchical model (outdated)
object oriented model
relational model
deductive model
XML
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
Overview
Several Types of Data Models
There are several types of data models:
network model (outdated)
hierarchical model (outdated)
object oriented model (short intro)
relational model
deductive model
XML
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
Overview
Several Types of Data Models
There are several types of data models:
network model (outdated)
hierarchical model (outdated)
object oriented model (short intro)
relational model (focus)
deductive model
XML
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
SQL – First Contact
Overview
Several Types of Data Models
There are several types of data models:
network model (outdated)
hierarchical model (outdated)
object oriented model (short intro)
relational model (focus)
deductive model (visit my logic lecture)
XML (M. Antenreiters lecture ’Datenbeschreibungssprachen’)
Ronald Ortner
An Introduction to Database Management Systems
A Query Language
Outline
1
An Introduction to Database Management Systems
Overview
2
SQL – First Contact
A Query Language
Ronald Ortner
SQL – First Contact
An Introduction to Database Management Systems
SQL – First Contact
A Query Language
Verwendete Software
MySQL Community Edition (Version 5.0.77-4)
SQLYog Community Edition (Version 8.8.2)
Datenbanken zum Download auf Lehrveranstaltungsseite
Ronald Ortner
An Introduction to Database Management Systems
SQL – First Contact
A Query Language
Zur Installation
zuerst MySQL installieren
(Passwort vergeben und merken)
dann SQLYog installieren
(neue Connection, dann gemerktes Passwort eingeben)
zum Importieren der Datenbanken siehe Liesmich-File
Ronald Ortner
An Introduction to Database Management Systems
A Query Language
SQL – First Contact
Demonstration of SQL live in the lecture
sorry, no slides for this, but...
... there are handouts with all the important information.
Try it yourself at home and in the exercises!
additional help with SQL:
- Website: http://sqlzoo.net
- Lecture notes VO: Oracle I
Ronald Ortner
SQL – First Contact