Appendix E Hierarchical Model Hayes Main Harrison Johnson Alma Palo Alto Smith North Rye A-102400 A-101 500 A-201900 A-201 900 A-215 700 (a) A-102400 A-101 500 A-201900 A-215700 Hayes Main Harrison Johnson Alma Palo Alto Johnson Alma Palo Alto Smith North Rye Smith North Rye (b) Figure E.7 Sample database corresponding to diagram of Figure E.6b. A sample database corresponding to the tree-structure diagram of Figure E.6b appears in Figure E.7.There are two database trees.The first tree(Figure E.7a) corresponds to the tree-structure diagram Ti;the second tree(Figure E.7b)corre- sponds to the tree-structure diagram T2.As we can see,all customer and account records are replicated in both database trees.In addition,account record A-201 ap- pears twice in the first tree,whereas customer records Johnson and Smith appear twice in the second tree. If a relationship also includes a descriptive attribute,the transformation from an E-R diagram to a tree-structure diagram is more complicated.A link cannot contain any data value.In this case,a new record type needs to be created,and the appropriate links need to be established.The manner in which links are formed depends on the way the relationship depositor is defined. Consider the E-R diagram of Figure E.3a.Suppose that we add the attribute access date to the relationship depositor,to denote the most recent date on which a customer accessed the account.This newly derived E-R diagram appears in Figure E.8a.To transform this diagram into a tree-structure diagram,we must 1.Create a new record type access date with a single field. 2.Create the following two links:
6 Appendix E Hierarchical Model Figure E.7 Sample database corresponding to diagram of Figure E.6b. A sample database corresponding to the tree-structure diagram of Figure E.6b appears in Figure E.7. There are two database trees. The first tree (Figure E.7a) corresponds to the tree-structure diagram T1; the second tree (Figure E.7b) corresponds to the tree-structure diagram T2. As we can see, all customer and account records are replicated in both database trees. In addition, account record A-201 appears twice in the first tree, whereas customer records Johnson and Smith appear twice in the second tree. If a relationship also includes a descriptive attribute, the transformation from an E-R diagram to a tree-structure diagram is more complicated. A link cannot contain any data value. In this case, a new record type needs to be created, and the appropriate links need to be established. The manner in which links are formed depends on the way the relationship depositor is defined. Consider the E-R diagram of Figure E.3a. Suppose that we add the attribute access date to the relationship depositor, to denote the most recent date on which a customer accessed the account. This newly derived E-R diagram appears in Figure E.8a. To transform this diagram into a tree-structure diagram, we must 1. Create a new record type access date with a single field. 2. Create the following two links:
E.2 Tree-Structure Diagrams 7 access date customer : account customer name customer street depositor account number customer city balance (a)E-R diagram customer name customer street customer_city customer access_date access date account number balance account (b)Tree structure diagram Figure E.8 E-R diagram and its corresponding tree-structure diagram. .customer date,a many-to-one link from access date record type to customer record type date account,a many-to-one link from account record type to access date record type Hayes Main Harrison Johnson Alma Palo Alto Turner Putnam Stamford 10June2009 24May2009 17June2009 10June2009 A-102 400 A-101 500 A-201 900 A-305 350 Figure E.9 Sample database corresponding to diagram of Figure E.8b
E.2 Tree-Structure Diagrams 7 Figure E.8 E-R diagram and its corresponding tree-structure diagram. • customer date, a many-to-one link from access daterecord type to customer record type • date account, a many-to-one link from account record type to access date record type Figure E.9 Sample database corresponding to diagram of Figure E.8b
Appendix E Hierarchical Model customer name customer streef customer_city account number balance customer account access date access date access_date access date account_number balance customer name customer_street customer city account customer Figure E.10 Tree-structure diagram with many-to-many relationships. The resulting tree-structure diagram is illustrated in Figure E.8b. An instance corresponding to the described schema appears in Figure E.9.It shows that: Hayes has account A-102,which was last accessed on 10 June 2009. Johnson has two accounts:A-101,which was last accessed on 24 May 2009, and A-201,which was last accessed on 17 June 2009. Hayes Main Harrison Johnson Alma Palo Alto Smith North Rye 10June2009 24May2009 17June2009 21une2009 3 June 2009 A-102 400 A-101 500A-201900 A-201 900 A-215 700 (a) A-102400 A-101500 A-201900 A-215700 10June2009 24May2009 17June2009 21June2009 3 June 2009 Hayes Main Harrison Johnson Alma Palo Alto Johnson Alma Palo Alto Smith North Rye Smith North Rye (b) Figure E.11 Sample database corresponding to diagram of Figure E.10
8 Appendix E Hierarchical Model Figure E.10 Tree-structure diagram with many-to-many relationships. The resulting tree-structure diagram is illustrated in Figure E.8b. An instance corresponding to the described schema appears in Figure E.9. It shows that: • Hayes has account A-102, which was last accessed on 10 June 2009. • Johnson has two accounts: A-101, which was last accessed on 24 May 2009, and A-201, which was last accessed on 17 June 2009. Figure E.11 Sample database corresponding to diagram of Figure E.10
E.2 Tree-Structure Diagrams 9 Turner has account A-305,which was last accessed on 10 June 2009 Note that two different accounts can be accessed on the same date,as were accounts A-102 and A-305.These accounts belong to two different customers,so the access date record must be replicated to preserve the hierarchy. If the relationship depositor were one to one with the attribute date,then the transformation algorithm would be similar to the one described.The only difference would be that the two links customer date and date account would be one-to-one links. Assume that the relationship depositor is many to many with the attribute access date;here again,we can choose among a number of alternative transfor- mations.We shall use the most general transformation;it is similar to the one applied to the case where the relationship depositor has no descriptive attribute. The record types customer,account,and access date need to be replicated,and two separate tree-structure diagrams must be created,as in Figure E.10.A sample database corresponding to this schema is in Figure E.11. Until now,we have considered only binary relationships.We shift our at- tention here to general relationships.The transformation of E-R diagrams cor- responding to general relationships into tree-structure diagrams is complicated. branch branch_name branch_city assets customer account customer_name CAB account_balance customer_street balance customer_city (a)E-R diagram branch_name branch_city assets branch_name branch_city assets branch branch customer_name customer_streef customer_city account_number balance account account_number balance customer_name customer_street customer_city customer (b)Tree structure diagrams Figure E.12 E-R diagram and its corresponding tree-structure diagrams
E.2 Tree-Structure Diagrams 9 • Turner has account A-305, which was last accessed on 10 June 2009. Note that two different accounts can be accessed on the same date, as were accounts A-102 and A-305. These accounts belong to two different customers, so the access date record must be replicated to preserve the hierarchy. If the relationship depositor were one to one with the attribute date, then the transformation algorithm would be similar to the one described. The only difference would be that the two links customer date and date account would be one-to-one links. Assume that the relationship depositor is many to many with the attribute access date; here again, we can choose among a number of alternative transformations. We shall use the most general transformation; it is similar to the one applied to the case where the relationship depositor has no descriptive attribute. The record types customer, account, and access date need to be replicated, and two separate tree-structure diagrams must be created, as in Figure E.10. A sample database corresponding to this schema is in Figure E.11. Until now, we have considered only binary relationships. We shift our attention here to general relationships. The transformation of E-R diagrams corresponding to general relationships into tree-structure diagrams is complicated. Figure E.12 E-R diagram and its corresponding tree-structure diagrams
10 Appendix E Hierarchical Model Rather than present a general transformation algorithm,we present a single ex- ample to illustrate the overall strategy that you can apply to deal with such a transformation. Consider the E-R diagram of Figure E.12a,which consists of the three entity sets customer,account,and branch,related through the general relationship set CAB with no descriptive attribute. There are many different ways to transform this E-R diagram into a tree- structure diagram.Again,all share the property that the underlying database tree (or trees)will have replicated records.The most straightforward transformation is to create two tree-structure diagrams,as shown in Figure E.12b. An instance of the database corresponding to this schema is illustrated in Fig- ure E.13.It shows that Hayes has account A-102 in the Perryridge branch;Johnson has accounts A-101 and A-201 in the Downtown and Perryridge branches,respec- tively;and Smith has accounts A-201 and A-215 in the Perryridge and Mianus branches,respectively. We can extend the preceding transformation algorithm in a straightforward manner to deal with relationships that span more than three entity sets.We simply replicate the various record types,and generate as many tree-structure Downtown Brooklyn 9000000 Perryridge Horseneck 17000000 Mianus Horseneck 400000 Haves Main Harrison Smith North Rye Johnson Alma Palo Alto A-102 400 Johnson Alma Palo Alto Smith North Rye A-101500 A-201900 A-201900 A-215700 (a) Downtown Brooklyn 9000000 Perryridge Horseneck 17000000 Mianus Horseneck 400000 A-102400 A-101 500 A-201900 A215700 Hayes Main Harrison Johnson Alma Palo Alto Johnson Alma Palo Alto Smith North Rye (b) Smith North Rye Figure E.13 Sample database corresponding to diagram of Figure E.12b
10 Appendix E Hierarchical Model Rather than present a general transformation algorithm, we present a single example to illustrate the overall strategy that you can apply to deal with such a transformation. Consider the E-R diagram of Figure E.12a, which consists of the three entity sets customer, account, and branch, related through the general relationship set CAB with no descriptive attribute. There are many different ways to transform this E-R diagram into a treestructure diagram. Again, all share the property that the underlying database tree (or trees) will have replicated records. The most straightforward transformation is to create two tree-structure diagrams, as shown in Figure E.12b. An instance of the database corresponding to this schema is illustrated in Figure E.13. It shows that Hayes has account A-102 in the Perryridge branch; Johnson has accounts A-101 and A-201 in the Downtown and Perryridge branches, respectively; and Smith has accounts A-201 and A-215 in the Perryridge and Mianus branches, respectively. We can extend the preceding transformation algorithm in a straightforward manner to deal with relationships that span more than three entity sets. We simply replicate the various record types, and generate as many tree-structure Figure E.13 Sample database corresponding to diagram of Figure E.12b