|
- Facts:
- The ER-Model is a
high level model
- The ER-Model omits many operational details
- The ER-Model is a
high level model
- Strenght:
- The ER-model simple enough to be used in non-technical communications
- Weakness:
- the ER-model does not have sufficient detail to be used in designing the storage file structure for the data
We need a lower level model to do so...
- Fact:
- The
Relational Model
is a
medium level model
- The Relational Model contains enough details to allow us to define the database (i.e. structure of the tables or relations)
- The
Relational Model
is a
medium level model
- Obtaining
a Relational Model
from a ER-model:
- There is
procedure that
we can use to:
- Convert an ER-model into a Relational Model
- There is
procedure that
we can use to:
- We will illustrate the procedure (steps) now with the Company Database
- What do entities
look like inside the ER-model:
You see that:
- An entity instance
is represented by the
attribute values
of the entity:
- All entity instances of the same entity type have the same set of attributes
- An entity instance
is represented by the
attribute values
of the entity:
- How to represent
entities in the ER-model
using a
Relational Model:
- Each entity in the
ER-model
is represented
by one relation (= table)
in the Relational model
- The relation name is chosen
as the name of the
entity
(or something close to it)
- The attributes in the relation schema consists of all the attributes of the entity in the ER-model
Example:
- The entity type
Project in the ER-diagram:
is represented by the follow Relation Schema:
- Information about the
projects are stored
in the relation (table) as follows:
- Each entity in the
ER-model
is represented
by one relation (= table)
in the Relational model
- When we create a
relation for
each entity in
the ER-model,
the initial
relational model
for the entities of the
Company Database is
as follows:
- You may
encounter the following problems
in the initial relational model:
- Some relations may
not have
a key attribute
(i.e.:
weak entities !!!)
- This is a violation of the key constraint of the Relational Model and must be fixed !!!
- Some attributes in
a relation is
not atomic
(i.e.: multi-valued attributes !!)
- This is a violation of the domain property of the Relational Model and must be fixed !!!
- Some relations may
not have
a key attribute
(i.e.:
weak entities !!!)
- Handling
weak entities:
- Handling weak entity requires the weak (= identifying) relationship
We will handle the weak entities after discussing how to represent relationships in the Relational Model (later)
- Multi-valued attributes:
- Recall that:
- The relational data model only allows atomic values for attributes (to make it easy to store).
- Therefore:
- Multi-valued attributes are not allowed !!!
There is a standard technique to solve this problem...
- Recall that:
- Example: why
storing multi-valued
attribues is
undesirable:
- The attribute
{Locations}
is
multi-valued
and will cause
inefficiency in storage:
- Because the length
of the attribute Locations
is
variable,
is very inefficient to
store such values
in files with
fixed sized records
(We WANT fixed sized records because accessing an arbitrary record can be done by computing its offset (just like accessing an array in programming langages)).
- The attribute
{Locations}
is
multi-valued
and will cause
inefficiency in storage:
- Naive Solution:
- Store each attribute value as an atomic value using a separate tuple
Example:
- Problem with this
naive solution:
- We have a lot of
redundancy !!!
E.g.:
- The fact that Manufacturing has department number D1234 is stored 3 times !!!
This can cause inconsistencies if the system makes a change to some of the data.
Example:
- We have a lot of
redundancy !!!
- How to remove multi-valued attributes
- Move the multi-valued attribute into a new relation X
- Add the primary key of the original relation to the relation X
- Example:
Result:
- Instead of the
relation
Department( DName, DNumber, {Location} )we now have 2 relations:
Department( DName, DNumber ) Dept_Loc( DNumber, Location )
- Notice that:
- The relations do not have any multi-valued attributes !!!!
- Instead of the
relation
|
The red underlined attributes are the the primary key in each relation
The blue underlined attributes are the candidate keys
Next:
- We learn how to represent the relationships....
|
|