Transcript Inledning till databasteori

Introduction to databases
What is a database?
Logiskt sammanhängande mängd av data, med en därtill hörande
betydelse, strukturerad och försedd med data avsedda för ett visst
ändamål, med en viss användargrupp i åtanke och återspeglande någon
aspekt av världen.
What is a database management system?
A set of programs allowing a user to create and maintain databases.
DBMS
User / Programmer
Database system
Application programs / Queries
DBMS
Programs for query management
Programs for data management
Metadata
Database
Why databases?
Persistence
Sharing
Data independence
Types of databases
Relational databases
Hierarchical databases
Network databases
Relational databases
A relation scheme is a set of attributes
Example: PERSON(SS#, Name, Age, Salary)
Every attribute has a domain
Example: Name has the domain String, Age
has the domain Integer
A tuple for a relation scheme gives a value to
each attribute in the scheme
Example: (778899, John Smith, 26, 13000)
The value for each attribute must be in the
domain
A relation is a set of tuples
Relational databases
PERSON
Relation
scheme
SS#
Relation
650101-2288
750203-3133
500107-5532
800515-0044
Name
Eva Svensson
Per Jonsson
Sven Olsson
Pia Eriksson
Age
Salary
33
23
47
17
25000
20000
25000
18000
A relation can be viewed as a
table without duplicates
From reality to database
Reality
Subset
school
Model
course
teacher
school
pupil
teacher
subject
course
library
pupil
loan
book
subject
((sNr), address)
((eNr), firstName, surName)
((lNr), firstName, surName)
((äNamn), courseBook)
((sNr, eNr, lNr, cName)
borrower
Database structure
library
borrower
book
loan
((bName), address)
((lNr), firstName, surName)
((ISBN), title)
((bName, lNr, ISBN, date)
Database design
Library
borrower
library
borrower
book
loan
loan
book
((bNamn), address)
((lNr), firstName, surName)
((ISBN), title)
((bNamn, lNr, ISBN, date)
Why design?
Why not a simple table?
ISBN
Title
Author
Library Address
Borrower
BorrowerAdd
Date
12345
23456
12346
12347
23412
121212
1212
100
Mitt liv
Ditt liv
Vårt liv
Vilket liv!
Mitt liv
Stickning
Matlagning
Matematik
Pelle
Lisa
Pelle
Lisa
Lisa
Johan
Eva
Vera
Stora
Stora
Stora
Stora
Stora
Stora
Stora
Stora
Kalle
Kalle
Vera
Mona
Kalle
Kalle
Vera
Mona
Karlavägen 12
Karlavägen 12
Verdandig 3
Månvägen 7
Karlavägen 12
Karlavägen 12
Verdandig 3
Månvägen 7
891102
890723
890809
891011
891112
890909
891010
891102
Storgatan 19
Storgatan 19
Storgatan 19
Storgatan 19
Storgatan 19
Storgatan 19
Storgatan 19
Storgatan 19
An unnormalised relation scheme
Name
Regno
Per Eriksson
Eva Olsson ABC123
Per Eriksson
Per Eriksson
Pia Johnsson
Pia Johnsson
Bo Persson CDE654
Share
Salary Model
ABC123
50
18000
DEF456
GHI789
GHI789
BCD321
100
18000
50 25000 Volvo
Volvo
10025000 Mercedes
50 25000 Toyota
50 30000 Toyota
10030000 Ford
Volvo
Problems with unnormalised
schemes
Redundancy
Update anomalies
Functional dependencies
A functional dependency means that one attribute
uniquely determines another attribute.
Example: Name --> Salary
This functional dependency means that if two
tuples have the same value on Name, then they
must have the same value on Salary.
Functional dependencies
A
B
C
D
E
x
y
y
y
z
y
w
x
x
y
z
y
z
w
z
x
x
w
z
y
w
z
x
w
y
z
x
x
y
w
Which ones of the functional dependencies are satisfied by the relation abov
A --> B
AB --> C
CD --> B
CD --> E
Keys
A key in a relation scheme is an attribute (or a minimal
set of attributes) that functionally determines all the
other attributes in the scheme.
Thus, a key uniquely identifies a tuple in a relation.
What is the key in this relation scheme?
MOVIE(Film, Theatre, Time, Price)
First normal form
A relation scheme is in first normal form if all attribute
values are atomic.
SS#
Surname
Not 1NF 750101-0032 Svensson
550401-0044 Olsson
1NF
SS#
Surname
750101-0032 Svensson
750101-0032 Svensson
550401-0044 Olsson
550401-0044 Olsson
First name
Gunnar, Sven
Karin, Eva
First name
Gunnar
Sven
Karin
Eva
What are
the keys?
Second normal form
A relation scheme is in
second normal form if every
attribute is functionally
dependent on the whloe
key.
Name
Per Eriksson
Per Eriksson
Pia Johnsson
Pia Johnsson
Name
Regno
Per Eriksson
Per Eriksson
Pia Johnsson
Pia Johnsson
Salary
ABC123
DEF456
GHI789
BCD321
Regno
ABC123
DEF456
GHI789
BCD321
Name
Per Eriksson
Pia Johnsson
Salary
25000
30000
Third normal form
A relation scheme is in third normal form if
each attribute is functionally dependent on
the key, the whole key, and nothing else
than the key.
Third normal form
If an attribute does not satisfy the condition for 3NF, it is
removed from the relation scheme. It will form a new relation
scheme together with the attributes it is functionally
dependent on.
Example:
PERSON(SS#, Name, Country, Number_of_inhabitants)
SS# --> Name, Country
Country --> Number_of_inhabitants
Number_of_inhabitants does not satisfy the condition for
3NF. It is removed and will form a new relation schema
together with Country:
PERSON(SS#, Name, Country)
Third normal form
Decompose the following relation scheme to relation schemes in
3NF.
BOOK(Copy#, SS#, Date-of-loan, Return-date,
Library, Library_address, Person_address, Title)
A copy of a book with a title is borrowed by a person, who has an
address, at a date and is returned at another date. The copy of the
book resides at a library with an address.
From reality to database
Reality
Subset
school
Model
course
teacher
school
pupil
teacher
subject
course
library
pupil
loan
book
subject
((sNr), address)
((eNr), firstName, surName)
((lNr), firstName, surName)
((äNamn), courseBook)
((sNr, eNr, lNr, cName)
borrower
Database structure
library
borrower
book
loan
((bName), address)
((lNr), firstName, surName)
((ISBN), title)
((bName, lNr, ISBN, date)
From conceptual schema to database
Main steps
1. Every object type becomes a relation scheme
2. Every 1-1 and 1-m attribute of an object type becomes
an attribute in the corresponding relation scheme
3. Every m-m attribute becomes a relation scheme - the
attributes in this scheme are the key attributes in the
associated relation schemes
From conceptual schema to database
String
Integer
name
PERSON
String
model
age
owns (m,m,p,p)
CAR
String
regno