Reduction of an E-R Schema to Tables.

Reduction of an E-R Schema to Tables

We can represent a database that conforms to an E-R database schema by a collection of tables. For each entity set and for each relationship set in the database, there is a unique table to which we assign the name of the corresponding entity set or relation- ship set. Each table has multiple columns, each of which has a unique name.

Both the E-R model and the relational-database model are abstract, logical representations of real-world enterprises. Because the two models employ similar de- sign principles, we can convert an E-R design into a relational design. Converting a database representation from an E-R diagram to a table format is the way we arrive at a relational-database design from an E-R diagram. Although important differences exist between a relation and a table, informally, a relation can be considered to be a table of values.

In this section, we describe how an E-R schema can be represented by tables; and in Chapter 3, we show how to generate a relational-database schema from an E-R schema.

The constraints specified in an E-R diagram, such as primary keys and cardinality constraints, are mapped to constraints on the tables generated from the E-R diagram. We provide more details about this mapping in Chapter 6 after describing how to specify constraints on tables.

Tabular Representation of Strong Entity Sets

Let E be a strong entity set with descriptive attributes a1, a2,... , an. We represent this entity by a table called E with n distinct columns, each of which corresponds to one of the attributes of E. Each row in this table corresponds to one entity of the entity set E.

As an illustration, consider the entity set loan of the E-R diagram in Figure 2.8. This entity set has two attributes: loan-number and amount. We represent this entity set by a table called loan, with two columns, as in Figure 2.23. The row

(L-17, 1000)

in the loan table means that loan number L-17 has a loan amount of $1000. We can add a new entity to the database by inserting a row into a table. We can also delete or modify rows.

Let D1 denote the set of all loan numbers, and let D2 denote the set of all balances. Any row of the loan table must consist of a 2-tuple (v1, v2), where v1 is a loan (that is, v1 is in set D1) and v2 is an amount (that is, v2 is in set D2). In general, the loan table will contain only a subset of the set of all possible rows. We refer to the set of all possible rows of loan as the Cartesian product of D1 and D2, denoted by

As another example, consider the entity set customer of the E-R diagram in Fig- ure 2.8. This entity set has the attributes customer-id, customer-name, customer-street, and customer-city. The table corresponding to customer has four columns, as in Figure 2.24.

Tabular Representation of Weak Entity Sets

Let A be a weak entity set with attributes a1, a2,..., am. Let B be the strong entity set on which A depends. Let the primary key of B consist of attributes b1, b2,..., bn. We represent the entity set A by a table called A with one column for each attribute of the set:

{a1, a2,..., am}∪ {b1, b2,..., bn}

As an illustration, consider the entity set payment in the E-R diagram of Figure 2.16. This entity set has three attributes: payment-number, payment-date, and payment-amount. The primary key of the loan entity set, on which payment depends, is loan-number. Thus, we represent payment by a table with four columns labeled loan-number, payment- number, payment-date, and payment-amount, as in Figure 2.25.

Tabular Representation of Relationship Sets

Let R be a relationship set, let a1, a2,... , am be the set of attributes formed by the union of the primary keys of each of the entity sets participating in R, and let the descriptive attributes (if any) of R be b1, b2,... , bn. We represent this relationship set by a table called R with one column for each attribute of the set:

{a1, a2,..., am}∪ {b1, b2,..., bn}

As an illustration, consider the relationship set borrower in the E-R diagram of Fig- ure 2.8. This relationship set involves the following two entity sets:

• customer, with the primary key customer-id

• loan, with the primary key loan-number

Since the relationship set has no attributes, the borrower table has two columns, labeled customer-id and loan-number, as shown in Figure 2.26.

Redundancy of Tables

A relationship set linking a weak entity set to the corresponding strong entity set is treated specially. As we noted in Section 2.6, these relationships are many-to-one and have no descriptive attributes. Furthermore, the primary key of a weak entity set in- cludes the primary key of the strong entity set. In the E-R diagram of Figure 2.16, the weak entity set payment is dependent on the strong entity set loan via the relation-ship set loan-payment. The primary key of payment is {loan-number, payment-number}, and the primary key of loan is {loan-number}. Since loan-payment has no descriptive attributes, the loan-payment table would have two columns, loan-number and payment-number. The table for the entity set payment has four columns, loan-number, payment-number, payment-date, and payment-amount. Every (loan-number, payment-number) com- bination in loan-payment would also be present in the payment table, and vice versa.

Thus, the loan-payment table is redundant. In general, the table for the relationship set

linking a weak entity set to its corresponding strong entity set is redundant and does not need to be present in a tabular representation of an E-R diagram.

Combination of Tables

Consider a many-to-one relationship set AB from entity set A to entity set B. Using our table-construction scheme outlined previously, we get three tables: A, B, and AB. Suppose further that the participation of A in the relationship is total; that is, every entity a in the entity set A must participate in the relationship AB. Then we can combine the tables A and AB to form a single table consisting of the union of columns of both tables.

As an illustration, consider the E-R diagram of Figure 2.27. The double line in the diagram indicates that the participation of account in the account-branch is total.

Hence, an account cannot exist without being associated with a particular branch.

Further, the relationship set account-branch is many to one from account to branch.

Therefore, we can combine the table for account-branch with the table for account and require only the following two tables:

• account, with attributes account-number, balance, and branch-name

• branch, with attributes branch-name, branch-city, and assets

Composite Attributes

We handle composite attributes by creating a separate attribute for each of the com- ponent attributes; we do not create a separate column for the composite attribute itself. Suppose address is a composite attribute of entity set customer, and the com- ponents of address are street and city. The table generated from customer would then contain columns address-street and address-city; there is no separate column for address.

Multivalued Attributes

We have seen that attributes in an E-R diagram generally map directly into columns for the appropriate tables. Multivalued attributes, however, are an exception; new tables are created for these attributes.

For a multivalued attribute M, we create a table T with a column C that corresponds to M and columns corresponding to the primary key of the entity set or relationship set of which M is an attribute. As an illustration, consider the E-R diagram in Figure 2.22. The diagram includes the multivalued attribute dependent-name. For this multivalued attribute, we create a table dependent-name, with columns dname, re- ferring to the dependent-name attribute of employee, and employee-id, representing the primary key of the entity set employee. Each dependent of an employee is represented as a unique row in the table.

Tabular Representation of Generalization

There are two different methods for transforming to a tabular form an E-R diagram that includes generalization. Although we refer to the generalization in Figure 2.17 in this discussion, we simplify it by including only the first tier of lower-level entity sets — that is, savings-account and checking-account.

1. Create a table for the higher-level entity set. For each lower-level entity set, create a table that includes a column for each of the attributes of that entity set plus a column for each attribute of the primary key of the higher-level entity set. Thus, for the E-R diagram of Figure 2.17, we have three tables:

• account, with attributes account-number and balance

• savings-account, with attributes account-number and interest-rate

• checking-account, with attributes account-number and overdraft-amount

2. An alternative representation is possible, if the generalization is disjoint and complete — that is, if no entity is a member of two lower-level entity sets directly below a higher-level entity set, and if every entity in the higher level entity set is also a member of one of the lower-level entity sets. Here, do not create a table for the higher-level entity set. Instead, for each lower-level entity set, create a table that includes a column for each of the attributes of that entity set plus a column for each attribute of the higher-level entity set. Then, for the E-R diagram of Figure 2.17, we have two tables.

• savings-account, with attributes account-number, balance, and interest-rate

• checking-account, with attributes account-number, balance, and overdraft- amount

The savings-account and checking-account relations corresponding to these tables both have account-number as the primary key.

If the second method were used for an overlapping generalization, some values such as balance would be stored twice unnecessarily. Similarly, if the generalization were not complete — that is, if some accounts were neither savings nor checking accounts — then such accounts could not be represented with the second method.

Tabular Representation of Aggregation

Transforming an E-R diagram containing aggregation to a tabular form is straight- forward. Consider the diagram of Figure 2.19. The table for the relationship set  manages between the aggregation of works-on and the entity set manager includes a column for each attribute in the primary keys of the entity set manager and the relationship set works-on. It would also include a column for any descriptive attributes, if they exist, of the relationship set manages. We then transform the relationship sets and entity sets within the aggregated entity.