1. No category

A4B33DS
Zdeněk Kouba
[email protected]
http://cw.felk.cvut.cz/doku.php/courses/a4b33ds/start
Database application
Application
File
•  sequential access files
•  direct access files
•  index-sequential access files
Database application
Logical schema language
Physical schema language
File
Requirements on a DBMS:
•  Schema
•  (Standardized) Query language (e.g. SQL)
•  Query language API (e.g. ODBC, JDBC, JPA)
•  Response time, throughput optimization
•  Concurrency - transactions
•  Client-server architecture
•  Reliability – failure recovery (RAID)
•  Data replication
•  High availability (e.g. ORACLE Data Guard)
Database technologies
• 
• 
• 
• 
• 
Hierarchical model
Network model
Relational
Object-oriented
Object-relational
Relation
Family name
Given name
Relation – a table
Michal
Vorel
Michal
Švec
Lukáš
Vácha
Radek
Dosoudil
Marek
Suchý
... Etc.
Relationship x Relation
Peter Chen, Peter Pin-Shan (March 1976):
"The Entity-Relationship Model – Toward a Unified View of Data".
ACM Transactions on Database Systems 1.
SSN
Class
Teacher
Name
Surname
teaches
AcademicYear
Code
Name
Course
YearOfStudy
E-R diagram in Chenn‘s notation
NoHoursWeekly
Relationship x Relation
Peter Chen, Peter Pin-Shan (March 1976):
"The Entity-Relationship Model – Toward a Unified View of Data".
Attribute
ACM Transactions on Database Systems
1. of the
Identifying
relationship
attribute
SSN
Class
teaches
Teacher
Name
Surname
Entity
type
AcademicYear
Code
Attribute of
the entity
type
Name
Course
YearOfStudy
E-R diagram in Relationship
Chenn‘s notation
NoHoursWeekly
Cardinality x connectivity
Cardinality (Chen):
Person
1
Born in
N
City
Connectivity(také UML):
Person
N
born/-in
1
City
Unfortunately, the DB community (practicians) did not accept the word „connectivity“.
Data modelling process
(schema creation)
Conceptual model
Real world model – independent on DB technology
Logical model
Depends on technology (relational), independent of
thevendor (Oracle)
Physical model
Depends on particular DB product/version
Relational (99,9%) => conceptual model sometimes skipped
Relational DB technology
Implementation of an Entity type - a table
Implementation of a relationship – key, foreign
key
Person
SSN
7455071111
7906071111
8404251111
8602191111
Name
Jana
Josef
Karel
Luděk
City
FamName
Nováková
Nakoupil
Kubát
Pondělí
BornIn
1
1
3
3
City-ID
1
2
3
•  Key
•  Primary key
•  Foreign key
Realtionship Person – City:
cardinality N : 1
0,N : 0,1
Name
Beroun
Benešov
Příbram
Relation as a(n) (equi)JOIN of other
relations
Rodne_cislo
7455071111
7906071111
8404251111
8602191111
Jmeno
Jana
Josef
Karel
Luděk
Prijmeni Narozen
Nováková
1
Nakoupil
1
Kubát
3
Pondělí
3
Rodne_cislo
7455071111
7906071111
8404251111
8602191111
Jmeno
Jana
Josef
Karel
Luděk
Prijmeni Narozen
Nováková
1
Nakoupil
1
Kubát
3
Pondělí
3
Město-ID
1
2
3
Název
Beroun
Beroun
Příbram
Příbram
Název
Beroun
Benešov
Příbram
Relationship
Partiality
c
d
Place_of_birth
City
a
Connectivity
Person
b
Notation „Crow's Foot“
Relationship N:M
Entity Type 1
relationship
Entity Type 2
Decomposition of an N:M relationship into two
1:N relationships
Entity Type 1
Entity Type
representing
the N:M
relationship
Notace Crow's Foot
Entity Type 2
Relationship N:M
Predmet
Teacher
SSN (PK)
GivenName
teaches
Kod (PK)
Academic_year
Class
Nazev
Rocnik
Hodin_tydne
Surname
Notace Crow's Foot
Recursive relationship
How shall we model a relationship between a person and its mother?
The mother is a Person, too. => It should not be represented by another table!
Person
SSN (PK)
GivenName
Surname
has-a-mother
Person
Primary key
SSN (PK)
GivenName
Surname
SSN_OF_MOTHER
Foreign key
Crow's Foot Notation