Databases Week 1 – Data Modelling
Database Design Phases
Database design:
- Formal model of relevant aspects of the real world
- The real world serves as measures of correctness
3 phases of Database design:
- Conceptual Database Design:
o What info do we store?
o How are info elements related to each other?
o What are the constraints?
o “ER model or UML model”
- Logical Database Design:
o Transformation of the conceptual schema into the schema
supported by the database
o “Relational Model”
- Physical Database Design:
o Design indexes, table distribution, buffer sizes…
o To maximize performance of the final system
Entity relationship model
An entity is an abstract
object: person company
event.
Entities have attributes:
people have names etc.
, Entity set is collection of similar entities: set of all persons, companies,
trees.
Entity = object & entity set = class
Important difference: the ER model is static
- Models structure of data, not operations
- No methods/functions associated to entity set
Attributes types:
- Simple and Composite:
o Street is composed of street name and number
- Single valued and multi valued:
o Single: age of person
o Multi: person has multiple phone numbers
- Derived:
o Can be computed from other attributes
o Age can be computed given the date of birth
Relationship set is a set of relationship of the same kind:
{ (Messi, PSG)
(Ronaldo, ManU)
(FDJ, Barcelona)
(Messi, Argentina)
}
So, ‘PlaysAt’ relationship set between entity sets footballer and
footballclub.
Role indicators: customer buyer ‘BuysFrom’ seller shop.
The degree of a relationship set refers to number of entity sets
participating in relationship: of degree 2 = binary and of degree 3 =
ternary.
Cardinality Limits
= expresses the number of entities to which another entity can be
associated via a relationship set.
Many different notations: we use UML notation.
2
Database Design Phases
Database design:
- Formal model of relevant aspects of the real world
- The real world serves as measures of correctness
3 phases of Database design:
- Conceptual Database Design:
o What info do we store?
o How are info elements related to each other?
o What are the constraints?
o “ER model or UML model”
- Logical Database Design:
o Transformation of the conceptual schema into the schema
supported by the database
o “Relational Model”
- Physical Database Design:
o Design indexes, table distribution, buffer sizes…
o To maximize performance of the final system
Entity relationship model
An entity is an abstract
object: person company
event.
Entities have attributes:
people have names etc.
, Entity set is collection of similar entities: set of all persons, companies,
trees.
Entity = object & entity set = class
Important difference: the ER model is static
- Models structure of data, not operations
- No methods/functions associated to entity set
Attributes types:
- Simple and Composite:
o Street is composed of street name and number
- Single valued and multi valued:
o Single: age of person
o Multi: person has multiple phone numbers
- Derived:
o Can be computed from other attributes
o Age can be computed given the date of birth
Relationship set is a set of relationship of the same kind:
{ (Messi, PSG)
(Ronaldo, ManU)
(FDJ, Barcelona)
(Messi, Argentina)
}
So, ‘PlaysAt’ relationship set between entity sets footballer and
footballclub.
Role indicators: customer buyer ‘BuysFrom’ seller shop.
The degree of a relationship set refers to number of entity sets
participating in relationship: of degree 2 = binary and of degree 3 =
ternary.
Cardinality Limits
= expresses the number of entities to which another entity can be
associated via a relationship set.
Many different notations: we use UML notation.
2
