Databases week 2 – Translating conceptual to
relational models
From conceptual to relational model
Basic idea: entity sets and r4elationship sets are represented as
tables.
Roughly:
One table for each entity set (name table = name entity set)
One table for each relationship set (name table = name
relationship set)
Columns roughly corresponding to attributes
A strong entity set becomes a table with:
Columns for attributes
Weak entity set becomes table that includes:
Columns for attributes and
Columns for the primary keys of the identifying entity
A many-to-many relationship set becomes table with:
Columns for the attributes of the relationship set and
For the primary keys of the participating entity sets
Eliminating tables
Many-to-(zero or)one relations can be represented by:
- Adding an extra attribute/column to the many-side with the
primary key of the one-side
, If participation is partial (0..1) then replacing the yable by an
attribute will result in null values for the entities that don’t
participate in the relationship set
- If participation is total (0..1 or 1..1) declare foreign key not null
- For one-to-one (0..1 or 1..1) relationship sets either side can be
extended with the key of the other
- Tables for relationship sets linking weak entity sets to the
identifying entity set can always be eliminated
o No extra table is needed! Table of weak ES already
contains the key of identifying ES
Cardinalites and constraints
When translating ES and
relationship sets to tables:
- Every table should have
a primary key (if
possible)
- Declared foreign key
constraints for each relation
Foreign keys should be declared:
- Not null, or not,
- Unique, or not
To model the cardinality
limits as good as possible
All columns in tables from RS
are not nullable
Attributes should be declared
not null and/or unique if
appropriate
2
relational models
From conceptual to relational model
Basic idea: entity sets and r4elationship sets are represented as
tables.
Roughly:
One table for each entity set (name table = name entity set)
One table for each relationship set (name table = name
relationship set)
Columns roughly corresponding to attributes
A strong entity set becomes a table with:
Columns for attributes
Weak entity set becomes table that includes:
Columns for attributes and
Columns for the primary keys of the identifying entity
A many-to-many relationship set becomes table with:
Columns for the attributes of the relationship set and
For the primary keys of the participating entity sets
Eliminating tables
Many-to-(zero or)one relations can be represented by:
- Adding an extra attribute/column to the many-side with the
primary key of the one-side
, If participation is partial (0..1) then replacing the yable by an
attribute will result in null values for the entities that don’t
participate in the relationship set
- If participation is total (0..1 or 1..1) declare foreign key not null
- For one-to-one (0..1 or 1..1) relationship sets either side can be
extended with the key of the other
- Tables for relationship sets linking weak entity sets to the
identifying entity set can always be eliminated
o No extra table is needed! Table of weak ES already
contains the key of identifying ES
Cardinalites and constraints
When translating ES and
relationship sets to tables:
- Every table should have
a primary key (if
possible)
- Declared foreign key
constraints for each relation
Foreign keys should be declared:
- Not null, or not,
- Unique, or not
To model the cardinality
limits as good as possible
All columns in tables from RS
are not nullable
Attributes should be declared
not null and/or unique if
appropriate
2
