Introduction to databases

1
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.
2
DBMS
User / Programmer
Database system
Application programs / Queries
DBMS
Programs for query management
Programs for data management
Metadata
Database
3
Why databases?
Persistence Sharing Data independence
4
Types of databases
Relational databases
Hierarchical databases
Network databases
5
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
6
Relational databases
PERSON
Relation scheme
SS Name Age
Salary
650101-2288 Eva Svensson 33
25000 750203-3133 Per Jonsson 23
20000 500107-5532 Sven Olsson 47
25000 800515-0044 Pia Eriksson 17
18000
Relation
A relation can be viewed as a table without
duplicates
7
From reality to database
8
Database design
Library borrower
library
((bNamn), address)
borrower
((lNr), firstName, surName)
loan
book
((ISBN), title)
loan
((bNamn, lNr, ISBN, date)
book
Why design?
Why not a simple table?
ISBN
Title
Author
Library
Address
Borrower
BorrowerAdd
Date
12345
Mitt liv
Pelle
Stora
Storgatan 19
Kalle
Karlavägen 12
891102
23456
Ditt liv
Lisa
Stora
Storgatan 19
Kalle
Karlavägen 12
890723
12346
Vårt liv
Pelle
Stora
Storgatan 19
Vera
Verdandig 3
890809
12347
Vilket liv!
Lisa
Stora
Storgatan 19
Mona
Månvägen 7
891011
23412
Mitt liv
Lisa
Stora
Storgatan 19
Kalle
Karlavägen 12
891112
121212
Stickning
Johan
Stora
Storgatan 19
Kalle
Karlavägen 12
890909
1212
Matlagning
Eva
Stora
Storgatan 19
Vera
Verdandig 3
891010
100
Matematik
Vera
Stora
Storgatan 19
Mona
Månvägen 7
891102
9
An unnormalised relation scheme
Name Regno Share Salary
Model
Per Eriksson ABC123 50 25000 Volvo Eva Olsson
ABC123 50 18000 Volvo Per Eriksson DEF456 100 250
00 Mercedes Per Eriksson GHI789 50 25000 Toyota Pi
a Johnsson GHI789 50 30000 Toyota Pia
Johnsson BCD321 100 30000 Ford Bo
Persson CDE654 100 18000 Volvo
10
Problems with unnormalised schemes
Redundancy Update anomalies
11
Functional dependencies
A functional dependency means that one attribute
uniquely determines another attribute.
Example Name --gt Salary This functional
dependency means that if two tuples have the same
value on Name, then they must have the same value
on Salary.
12
Functional dependencies
Which ones of the functional dependencies are
satisfied by the relation above? A --gt B
AB --gt C CD --gt B CD --gt E
13
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)
14
First normal form

• A relation scheme is in first normal form if all
  attribute values are atomic.

SS Surname First name 750101-0032 Svensson Gunna
r, Sven 550401-0044 Olsson Karin, Eva
Not 1NF
What are the keys?
SS Surname First name 750101-0032 Svensson Gunna
r 750101-0032 Svensson Sven 550401-0044 Olsson Ka
rin 550401-0044 Olsson Eva
1NF
15
Second normal form
Name Regno Salary

• A relation scheme is in second normal form if
  every attribute is functionally dependent on the
  whloe key.

Per Eriksson ABC123 25000 Per Eriksson DEF456 2500
0 Pia Johnsson GHI789 30000 Pia
Johnsson BCD321 30000
Name Regno
Per Eriksson ABC123 Per Eriksson DEF456 Pia
Johnsson GHI789 Pia Johnsson BCD321
Name Salary
Per Eriksson 25000 Pia Johnsson 30000
16
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.
17
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 --gt Name,
Country Country --gt 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) COUNTRY(Country, Number_of_inhabitants)
18
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.

19
From reality to database
20
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

21
From conceptual schema to database
String
String
String
Integer
model
age
name
regno
owns (m,m,p,p)
PERSON
CAR