Database Design with Semantic Object Models z Mapping

Database Design with Semantic Object Models z Mapping 7 types of semantic objects into relations

Simple objects z. Transform object into relation z. Make ID into key z. Other attributes become columns

Composite objects z. One relation for base object y. Include single-valued attributes z. One relation for each (simple or group) multivalued attribute z. Key of child relation is composite: y. Foreign key of object y. Local key of multivalued attribute

Separate versus nested groups z. Iterate composite object procedure z. Outer group is base object for inner group

Compound objects z. Basically 3 types of binary relationships

Compound objects z 1: 1 - place key of either table into other table

Compound objects z 1: N - place key of parent into child table

Compound objects z. N: M - create intersection table with keys of both relations

Hybrid objects z. Create a table for the multi-valued group attribute of the composite object z. Place key of non-composite object into that table SALES-ORDER (Sales. Order. Number, Date, Subtotal, Tax, Total, Phone, Salesperson. Name) ITEM (Item. Number, Item. Description, Unit. Price) LINE-ITEM (Sales. Order. Number, Item. Number, Quantity, Extended. Price)

Hybrid objects z. Actually, 4 cases of hybrid objects

Hybrid Object Max Cardinality z. Case One z. ITEM in one ORDER and only one Line. Item

Hybrid Object Max Cardinality z. Case Two z. ITEM in one or more Line. Items within one SALES-ORDER

Hybrid Object Max Cardinality z. Case Three z. ITEM in one Line. Item within one SALESORDER

Hybrid Object Max Cardinality z. Case Four z. ITEM in one or more Line. Items within one SALES-ORDER

Hybrid object common cases z Case 4: O 3 is inserted into R-G 2 but not as part of its local key z Case 1: like 3 but add O 1 to R 2 and R 2(O 1, O 2) = R-G 1(O 1, O 2) z Case 2: like 4 but add O 1 to R 3 and R-G 2(O 1, O 3) Í R 3(O 1, O 3)

Association objects z One relation for each of the 3 participating objects y. Each relation has its own key y. Association relation has parents’ keys as foreign keys y. If association has no unique ID, use foreign keys z Unlike intersection relations, association tables may have additional attributes

Super/subtype objects z One table for parent z One table for each subtype z Normally same key for all z May add subtype attribute(s) to parent for efficiency y. One label if exclusive y. One Boolean per type, if not exclusive

Archetype/version objects z One table for archetype z One table for version z Key of version table contains key of archetype table