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
             --has--> Salary
             --report-to-> Manager
    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 database model.

Given a conceptual model such as the ER diagram, we want to MAP the conceptual design to:
Relational
Hierarchical
Network
Object-Oriented

Can we do this for any ER diagram?

Suppose this can be done, what are the implications?


ER schema diagram for the COMPANY database:


ER-TO-RELATIONAL MAPPING

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.

SUMMARY

Entity Type -> Relations

Relationship Type
1:1 or 1:N -> absorbed in relations for entity types
M:N or N-ary -> Relations

TRADEOFF

The relationship types may not be mapped explicitly as relations. They can be materialized using joins.
The relational database schema corresponding to the COMPANY ER diagram.


ER-TO-NETWORK MAPPING

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.

SUMMARY

Entity Type -> Record types

Relationship Type
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 type
The network database schema corresponding to the COMPANY ER diagram.


ER-TO-HIERARCHICAL MAPPING

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 hierarchy.

(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.


ER-TO-OO MAPPING


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 object type.

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 represent link.

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 manipulations.

Advantange of object-oriented model:
Comprehensive and integrated approach. Most 'natural' for some applications.