RELATIONAL DATABASE DESIGN CONCEPTS
Click here for Part I of audio-text lecture and feed it to the speech agent
Click here for Part II of audio-text lecture and feed it to the speech agent
1. Semantic Modeling: The "meaning" of the
data can be expressed by, for example,
the entity/relationship model.
2. The ER (entity/relationship) Diagram
can be drawn. The many-to-many,
many-to-one relationships are shown.
3. Dependency Graph: From the ER Diagram,
the Dependency Graph can be derived.
4. Decomposition: The Dependency Graph is
decomposed based upon certain normalization
considerations (3NF, 4NF, etc.)
5. Relations: Each decomposed graph corresponds
to one relation.
RELATIONAL DATABASE DESIGN EXAMPLE
(This is a very much simplified example to
illustrate concepts only.)
1. Semantic Modeling: Employee has ID, salary and
reports to a manager who is also an employee.
The conceptual graph (CG) is
Employee --has--> ID
Manager --is-a-> Employee
2. ER Diagram
ID, Salary and Manager are attributes of
the entity Employee.
3. Dependency graph
ID -> Salary, ID -> Manager
4. Database schema
EMP (ID, SALARY, MANAGER)
Note: MANAGER is a foreign key of EMP and
its domain is subset of the domain of ID.
DESIGN OF OTHER DATABASE MODELS
After the ER diagram is obtained, instead of the relational
database model, we can also map the ER diagram into the
network model, the hierarchical model or the object-oriented
Given a conceptual model such as the
ER diagram, we want to MAP the
conceptual design to:
Can we do this for any ER diagram?
Suppose this can be done, what are
ER schema diagram for the COMPANY database:
STEP 1: For regular entity type E in ER schema,
create a relation R that includes all the simple
attributes, and component attributes of composite
attributes. Select the primary key.
STEP 2: For weak entity type W in ER
schema, with owner entity type E, create a
relation R, include all simple attributes
(or component attributes of composite attributes)
of W as attributes of R. In addition, include
the primary key attributes of the relation Q for
the owner entity type E. Primary key is the
combination of primary key of Q and partial
key of R.
STEP 3: For 1:1 relationship X, suppose S and
T are the relations for the entity types
participating in it. Include primary key
of T as foreign key of S. Include other
attributes of the relationship X as
attributes of S.
ER-TO-RELATIONAL MAPPING (continued)
STEP 4: For 1:N relationship Y, suppose S
relation corresponds to the entity type at the
N-side, and T relation corresponds to the
entity type at the other side. Include
primary key of T as foreign key of S.
STEP 5: For M:N relationship Z, create a
new relation R to represent Z. Include
simple attributes of Z in R. Include
the primary keys of S and T as foreign
keys of R, their combination forms the
primary key of R.
STEP 6: For multivalued attribute A,
create a new relation R that includes
an attribute corresponding to A.
Include primary key of the relation
of the entity type having A as an
attribute. Primary key is their combination.
ER-TO-RELATIONAL MAPPING (continued)
STEP 7: For n-ary relationship type X,
and n>2, create a new relation R.
Include primary key of each participating
entity type's relation as foreign key
of R. Include attributes of X as
simple attributes of R.
Entity Type -> Relations
1:1 or 1:N -> absorbed in relations
for entity types
M:N or N-ary -> Relations
The relationship types may not be
mapped explicitly as relations.
They can be materialized using
The relational database schema corresponding to the COMPANY ER diagram.
STEP 1: For each regular entity type E,
create a record type R. All simple
(or composite) attributes of E are
included as fields of R. A multi-valued
attribute of E is included as a
vector field or repeating group of R.
STEP 2: For each weak entity type WE,
with owner entity type IE, make a
repeating group in the record type
representing IE, to represent the
attributes of WE.
STEP 3: For 1:1 or 1:N relationship
type X, create a set type relating
the record types S1 and S2 representing
the participating entity types.
ER-TO-NETWORK MAPPING (continued)
STEP 4: For each M:N relationship type
X between entity types E1 and E2,
with corresponding record types S1 and
S2, create a link record L, and
make it the member record type of
the two set types E1 and E2.
Entity Type -> Record types
1:1 or 1:N -> one set type
M:N -> two set types for
a link record type
N-nary: multiple set types
for a link record
The network database schema corresponding to the COMPANY ER diagram.
STEP 1: Entity types can be represented by
record types (segment types).
STEP 2: 1:N relationship types can be
represented by parent-child
relationship (PCR) types.
STEP 3: M:N relationship type can
be represented as though it were a
1:N relationship type. Record
instances at the N-side are duplicated
because each record may be related
to several parents. The advantage
is to keep everything in a single
(Alternately, create another
hierarchy having virtual parent-child
relationship type (VPCR) to
represent the M:N relationship type)
The hierarchical database schema corresponding to the COMPANY ER diagram
is shown in (a), and another schema for the same database with two
hierarchies and four VPCRs is shown in (b).
DEALING WITH N-ARY RELATIONSHIP TYPES
The n-ary relationship type SUPPLY with n=3 as shown in (a)
is mapped into the relational schema shown in (b).
The SUPPLY relationship type is mapped into the network model.
The SUPPLY relationship type is mapped into the hierarchical model.
We need to start from an Extended ER (EER) model.
STEP 1: Create an OO class for each EER class.
STEP 2: Add reference attributs for each
binary relationship into the OO classes
that participate in that relationship.
STEP 3: Include appropriate methods for
each class (not available from EER).
STEP 4: An OO class that corresponds to
a subclass in the EER schema inherits
the types and methods of the superclass
in the OO schema.
STEP 5: Weak entity types can be absorbed
into composite multivalued attributes
of the owner entity type.
STEP 6: N-ary relationships with N > 2
can be mapped into a separate
SUMMARY OF MAPPING ER TO FOUR MODELS
To represent a link:
In the relational model, include same
attribute in two relations.
In the network model, use set-type
to represent link.
In the hierarchical model, use the
parent-child relationship to
In the object-oriented model, use the
reference attributes to represent link.
Advantage of relational model:
Simplicity. Use only one concept.
Formal query language.
Advantage of network model:
Can represent 1:N and 1:1 directly.
Efficiency in navigation and data
Advantange of object-oriented model:
Comprehensive and integrated approach.
Most 'natural' for some applications.