MIS Exam #2 Part Five

For a supertype-subtype relationship on an ER diagram, how is the relationship represented? (See Table 9-1 on p. 328 in text.)

Create a relation for the superclass, which contains the primary relationship key and all nonkey attributes in common with all subclasses, plus create a separate relation for each subclass with the same primary key (with the same or local name) but with only the nonkey attributes related to that subclass.

How is a "one-to-one" relationship represented?

For a binary or unary one-to-one (1:1) relationship between two entities A and B (for a unary relationship, A and B would be the same entity type), the relationship can be represented by any of the following choices: 1. Adding the primary key of A as a foreign key of B 2. Adding the primary key of B as a foreign key of A 3. Both of the above

For a unary relationship on an ER diagram: (325) How is a "one-to-many" relationship represented when transforming an ER diagram into relational notation?

For a unary 1:N relationship, the entity type (such as EMPLOYEE) is modeled as a relation. The primary key of that relation is the same as for the entity type. Then a foreign key is added to the relation that references the primary key values. A recursive foreign key is a foreign key in a relation that references the primary key values of that same relation. We can represent the relationship in Figure 9-13a as follows: EMPLOYEE(Emp_ID,Name,Birthdate,Manager_ID)

Unary How is a "many-to-many" relationship represented? Does a new relation get created?

For a unary M :N relationship, we model the entity type as one relation. Then we create a separate relation to represent the M:N relationship. The primary key of this new relation is a composite key that consists of two attributes (which need not have the same name) that both take their values from the same primary key. Any attribute Designing Databases associated with the relationship (such as Quantity in Figure 9-13b) is included as a non- key attribute in this new relation. We can express the result for Figure 9-13b as follows: ITEM(Item_Number,Name,Cost) ITEM-BILL(Item_Number,Component_Number,Quantity)

How is a "many-to-many" relationship represented? Does a new relation get created?

Suppose that there is a binary many-to-many (M:N) relationship (or associative entity) between two entity types A and B. For such a relationship, we create a separate relation C. The primary key of this relation is a composite key consisting of the primary key for each of the two entities in the relationship. Any non-key attributes associated with the M:N relationship are included with relation C. Occasionally, the relation created from an M :N relationship requires a primary key that includes more than just the primary keys from the two related relations. Consider, for example, the following situation: In this case, Date must be part of the key for the SHIPMENT relation to uniquely distinguish each row of the SHIPMENT table, as follows: SHIPMENT(Customer_ID,Vendor_ID,Date,Amount) If each shipment has a separate non-intelligent key, say, a shipment number, then Date becomes a non-key and Customer_ID and Vendor_ID become foreign keys, as follows: SHIPMENT(Shipment_Number,Customer_ID,Vendor_ID,Date,Amount) In some cases, there may be a relationship among three or more entities. In such cases, we create a separate relation that has as a primary key the composite of the primary keys of each of the participating entities (plus any necessary additional key elements). This rule is a simple generalization of the rule for a binary M :N relationship.

For a binary relationship on an ER diagram: How is a "one-to-many" relationship represented when transforming an ER diagram into relational notation (e.g., an example of relational notation is CUSTOMER(Customer_ID, Name, Address, City)?

E-R diagram is represented by adding the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation that is on the many side of the relationship.

What are the steps needed to transform an E-R diagram into normalized relations? (322)

1. Represent entities. Each entity type in the E-R diagram becomes a relation. The identifier of the entity type becomes the primary key of the relation, and other attributes of the entity type become nonprimary key attributes of the relation. 2. Represent relationships. Each relationship in an E-R diagram must be represented in the relational database design. How we represent a relationship depends on its nature. For example, in some cases we represent a relationship by making the primary key of one relation a foreign key of another relation. In other cases, we create a separate relation to represent a relationship. 3. Normalize the relations. The relations created in steps 1 and 2 may have unnecessary redundancy. So we need to normalize these relations to make them well structured. 4. Merge the relations. So far in database design we have created various relations from both a bottom-up normalization of user views and from transforming one or more E-R diagrams into sets of relations. Across these different sets of relations, there may be redundant relations (two or more relations that describe the same entity type) that must be merged and renormalized to remove the redundancy.