Chapter 7 Notes

Mapping ER/EER Diagrams to Relations Definitions Primary Key – A primary key is a field (or combination of fields, called a composite key), which uniquely identifies a row or entity.

Candidate Key – a key, although not selected as a primary key, can also be used to uniquely identify a row or entity.

Foreign Key - a field in one relation, which refers to a field in another. Foreign keys are used in relational schema to represent a relationship between two relations/entities. A foreign key in one relation refers to the primary key field of the related relation/entity. Examples of a Foreign Key Suppose we have two tables, Customer and Purchase. These entities are related by the Purchases relationship. Purchase s

Customer

Purchase

If we were to show the relations (tables) that are created based on the above ER diagram, it would look like the schema shown below. Customer CustID

CustName

DOB

ProductID

ProductName

CreditNumber

Purchase PurchaseID

Date

PurchasingCustID

In the example above, the PurchasingCustID field in the Purchase relation is the foreign key, and it refers to the primary key of the Customer relation. The foreign key represents the relationship between customer and purchase, or in other words, indicates the customer who purchased the given product.

Relational Schema 

The example above is an example of a relational schema. The relational schema shows the relations (tables) and the attributes of the relations, and the keys of each relation, including primary keys and foreign keys.

 

Often when creating a relational schema, you indicate primary keys using an underline. Foreign keys are sometimes represented using an arrow from the foreign key field in the relation, to the primary key field in the relation to which it refers. Customer CustID

CustName

DOB

ProductID

ProductName

CreditNumber

Purchase PurchaseID

Date

PurchasingCustID

Steps to Mapping ER/EER Diagrams to Relational Schema: 1. 2. 3. 4. 5. 6. 7. 8. 9.

Mapping Strong Entity Types Mapping Weak Entity Types Mapping of 1:1 Relationships Mapping of 1:N Relationships Mapping of M:N Relationships Mapping of Multi-valued Attributes Mapping of n-ary Relationships (Relationships whose degree is >1 Mapping of Specialization/Generalization Mapping of Unions (Categories)

For examples and detail of mapping ER and EER diagrams to relations, access the PowerPoint Presentation, Chapter7Mapping.ppt from the course outline.