Introduction Dr. Jürgen Eckerle - BFH-TI / Organisation

Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Database Systems
Unit 1
Introduction
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Lecturers – SS 2008
Dr. Jürgen Eckerle
http://staff.ti.bfh.ch/erj1
Email: [email protected]
Room N.314, Phone 312
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-2
1
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Lecturers – SS 2006
Marcel Pfahrer
Lic. rer. pol., dipl. Ing. HTL
http://prof.hti.bfh.ch/prm1
Email: [email protected]
Room N.622, Phone 335
Rolf Jufer-Meier
Lic. oec. publ., dipl. Wirtschaftsinformatiker
http://prof.hti.bfh.ch/?id=rolfjufer
Email: [email protected]
Room N.622, Phone 463
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-3
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Course Information
• Course materials
– Slide copies
– Exercises
– A book: “Fundamentals of Database Systems”.
– Additional material: On my homepage.
• Grading
– One written exam during semester
– Written final exam (integrated with “Programming 2”)
• Database System
– Microsoft SQL Server 2005
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-4
2
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Accompanying Book
• Elmasri, R. / Navathe, S.B.
Fundamentals of Database Systems
4th Edition, Pearson, 2004, ISBN 0-321-20448-4
• Elmasri, R. / Navathe, S.B
Grundlagen von Datenbanksystemen
3. Auflage, Pearson, ISBN 3-8273-7136-8
• Elmasri, R. / Navathe, S.B
Conception et architecture de base de données
4ème édition, Pearson, ISBN 2-7440-7055-6
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-5
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Why a Course on Database Systems?
• In the course of a day, most of us encounter several activities
that involve some interaction with a database.
For example,
– if we go to a bank to deposit or withdraw funds,
– if we make a hotel or airline reservation,
– if we access a computerized library catalog to search for a
bibliographic item, or
– if we buy some item – such as a book, toy, or computer –
from an Internet vendor through its Web page.
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-6
3
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Why a Course on Database Systems?
• Today, information is a key resource and is still growing in
importance and volume.
• Data allows to represent information.
• Databases help us to
– store it (file structures, disk management)
– understand it (data models)
– keep it secure (security, recovery)
– find it and use it (query languages, concurrency control
and data analysis tools)
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-7
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Learning Goals
• In this lesson you will learn
– How data base systems are structured and what are the
elementary concepts
– How data and their relationships are modelled using the
Entity-Relationship-Model
– The relational data model and relational database
constraints
– The Relational Algebra and the relational calculus
– The data manipulation language SQL
– Normalizations for Relational Databases
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-8
4
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
What is a Database?
• A definition in a very broad sense
„A Database (DB) is a
collection of related data.“
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1-9
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Characteristics of a Database
• A database represents some aspect of the real world, sometimes called the miniworld or the Universe of Discourse
(UoD). Changes to the UoD are reflected in the database.
• A database is a logically coherent collection of data with
some inherent meaning. A random assortment of data
cannot correctly be referred to as a database.
• A database is designed, built, and populated with data for a
specific purpose. It has an intended group of users and
some preconceived applications in which these users are
interested.
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 10
5
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Characteristics of a Database
In other words, a database
• has some source from which data is derived,
• some degree of interaction with events in the real world, and
• an audience that is actively interested in the contents of the
database.
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 11
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
More Characteristics of Databases (Atzeni et al., 2000)
• Databases can be large
– certainly larger than the main memory available
• Databases are shared
– various applications and users must be able to gain
access to data of common interest
– redundancy (and, consequently) the possibility of
inconsistencies is reduced
• Databases are persistent
– they have a lifespan that is not limited to single executions
of the programs that use them
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 12
6
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
What is a Database?
• A definition in a much closer sense
„A Database is a collection of
data managed by a DBMS.“
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 13
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
What is a Database?
„A Database Management System (DBMS) is a
collection of programs that enables users to
create and maintain a database.”
• The DBMS is hence a general-purpose software system that
facilitates the process of
– defining,
– constructing,
– manipulating, and
– sharing databases among various users and applications.
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 14
7
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
What is a Database?
• Additionally,
– protection including both
• system protection against hardware or software malfunction (or
crashes), and
• security protection against unauthorized or malicious access.
– maintain the database system by allowing the system to
evolve as requirements change over time.
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 15
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
What is a Database System?
„A Database System (DBS) is a DBMS together
with one or more Databases managed by this
DBMS.”
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 16
8
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 17
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Information and Data
• Every organisation has an Information System to manage
the information necessary to perform the functions of the
organisation
• The existence of the information system is partly independent
of the extent to which it is automated
• Information may be recorded by means of Data
„... data alone has no significance, but once interpreted
and suitably correlated, it provides information that
allows us to improve our knowledge of the world.“
Atzeni et al., 2000, p. 3
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 18
9
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Example
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 19
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Example
Examples
Knowledge
Basic principles of
the exchange market
Concept
Context
Information
Meaning
Rate of Exchange
Data
Syntax
1,70
Chars
Character Set
„1", „7“, „0“ and „,“
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 20
10
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Information in Business
• In order to run an enterprise efficiently, there is a lot of
information that is needed
• These information is needed regardless of whether
computers are used, but computers allow to handle large
information volumes much easier and to share information
among people
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 21
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Business Value of Data
• The value of a business applications typically lies in the data
contained in it.
• While functions change frequently very rapidly, some data of
an enterprise remain usually stable for a very long time (15
years and longer)
• A survey among the top 500 companies in the USA has
shown that one third of these companies would be bankrupt
after 14 days if their data should get lost irrecoverable
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 22
11
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Information Needs
• Every organization contains actors with different roles and
competences
• This is leading to different information needs
• The differences may be in the extent, the structure, the
detailing, and/or the aggregation level
• The primary goal of data management is to provide data in a
form that allows to satisfy all the different information needs
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 23
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Structured vs. Unstructured Data
Address:
Peter Sample
One Street 2
17AH21 Acity
Somewhere
+88 828 92'841
UnstructuredData
Data
Unstructured
DB / PRM1 / 20.05.2008
Name:
First name:
Address:
ZIP:
City:
Country:
Phone #:
Sample
Peter
One Street 2
17AH21
Acity
Somewhere
+88 828 92'841
StructuredData
Data
Structured
db01_introduction.ppt / Version 1.2
1 - 24
12
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
Aggregated vs. Non-Aggregated Data
Invoice
Peter Sample
One Street 2
17AH21 Acity
Somewhere
Customer#:
Date:
Invoice#:
9399
2005-10-21
784 923
Item
-------1
2
3
4
Qty
--------500
500
5000
50
Text
--------------Box
Pen
Paper
Big Box
Total
Non-aggregateddata
data
Non-aggregated
DB / PRM1 / 20.05.2008
Invoice#:
Customer#:
Nb of items:
Total amount:
784 923
9399
4
$ 2100
Amount
------------700
400
250
750
--------2100
AggregatedData
Data
Aggregated
db01_introduction.ppt / Version 1.2
1 - 25
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
An Example, a University Database
Task: A university database maintains information concerning
students, courses, classes, time-tables, and grades in a
university environment. Look on the web pages of your
university (BFH-TI).
• Which data can be found?
• How this data is structured? In which way it is stored?
• Which kind of relationships are given?
• Can you describe and represent the data and their
relationships in an abstract way?
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 26
13
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
An Example, a University Database
Questions to be answered:
• How to find all the courses of a given lecturer?
• How to find all the courses in which a student participate?
• How to find all the courses of course of studies which has to
be done in a given semester?
• Is there any redundancy?
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 27
Berner Fachhochschule
Hochschule für Technik und Informatik HTI
DB / PRM1 / 20.05.2008
db01_introduction.ppt / Version 1.2
1 - 28
14