Appendix D Network Model branch branch name branch_city branch_assets customer account customer name CAB account_number customer_street balance customer_city (a)E-R diagram customer name customer_street customer city branch_name branch_city assets customer branch account_number balance CustRInk account AcctRink BrnchRInk Rlink (b)Data structure diagram Figure D.8 E-R diagram and its corresponding data-structure diagram. 3.Create the following many-to-one links: CustRInk from Rlink record type to customer record type .AcctRInk from Rlink record type to account record type BrncRInk from Rlink record type to branch record type The resulting data-structure diagram appears in Figure D.8b. A sample database corresponding to the described schema appears in Fig- ure D.9.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,re- spectively,and Turner has account A-305 in the Round Hill branch. We can extend this technique in a straightforward manner to deal with rela- tionships that span more than three entity sets.We create a many-to-one link from the Rlink record to the record types corresponding to each entity set involved in the relationship.We can also extend the technique to deal with a general rela- tionship that has descriptive attributes.We need to add one field to the dummy record type for each descriptive attribute
6 Appendix D Network Model Figure D.8 E-R diagram and its corresponding data-structure diagram. 3. Create the following many-to-one links: • CustRlnk from Rlink record type to customer record type • AcctRlnk from Rlink record type to account record type • BrncRlnk from Rlink record type to branch record type The resulting data-structure diagram appears in Figure D.8b. A sample database corresponding to the described schema appears in Figure D.9. 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 Turner has account A-305 in the Round Hill branch. We can extend this technique in a straightforward manner to deal with relationships that span more than three entity sets. We create a many-to-one link from the Rlink record to the record types corresponding to each entity set involved in the relationship. We can also extend the technique to deal with a general relationship that has descriptive attributes. We need to add one field to the dummy record type for each descriptive attribute
D.3 The DBTG CODASYL Model 7 A-102400 A-101500 A-201900 A-305350 Hayes Main Harrison Perryridge Horseneck 1700000 Lindsay Park Pittsfield Downtown Brooklyn 9000000 Turner Putnam Stamford 4 Round Hill Horseneck 8000000 Figure D.9 Sample database corresponding to diagram of Figure D.8b. D.3 The DBTG CODASYL Model The first database-standard specification,called the CODASYL DBTG 1971 report, was written in the late 1960s by the Database Task Group.Since then,a number of changes have been proposed many of which are reflected in our discussion concerning the DBTG model. D.3.1 Link Restriction In the DBTG model,only many-to-one links can be used.Many-to-many links are disallowed to simplify the implementation.We represent one-to-one links using a many-to-one link.These restrictions imply that the various algorithms of Section D.2 for transforming an E-R diagram to a data-structure diagram must be revised. Consider a binary relationship that is either one to many or one to one.In this case,the transformation algorithm defined in Section D.2.1 can be applied directly.Thus,for our customer-account database,if the depositor relationship is one to many with no descriptive attributes,then the appropriate data-structure diagram is as shown in Figure D.10a.If the relationship has a descriptive attribute (for example,access-date),then the appropriate data-structure diagram is as shown in Figure D.10b. If the depositor relationship,however,is many to many,then our transforma- tion algorithm must be refined;if the relationship has no descriptive attributes (Figure D.11a),then this algorithm must be employed: 1.Replace the entity sets customer and account with record types customer and account,respectively. 2.Create a new dummy record type,Rlink,that may either have no fields or have a single field containing an externally defined unique identifier. 3.Create the following two many-to-one links:
D.3 The DBTG CODASYL Model 7 Figure D.9 Sample database corresponding to diagram of Figure D.8b. D.3 The DBTG CODASYL Model The first database-standard specification, called the CODASYL DBTG 1971 report, was written in the late 1960s by the Database Task Group. Since then, a number of changes have been proposed many of which are reflected in our discussion concerning the DBTG model. D.3.1 Link Restriction In the DBTG model, only many-to-one links can be used. Many-to-many links are disallowed to simplify the implementation. We represent one-to-one links using a many-to-one link. These restrictions imply that the various algorithms of Section D.2 for transforming an E-R diagram to a data-structure diagram must be revised. Consider a binary relationship that is either one to many or one to one. In this case, the transformation algorithm defined in Section D.2.1 can be applied directly. Thus, for our customer-account database, if the depositor relationship is one to many with no descriptive attributes, then the appropriate data-structure diagram is as shown in Figure D.10a. If the relationship has a descriptive attribute (for example, access-date), then the appropriate data-structure diagram is as shown in Figure D.10b. If the depositor relationship, however, is many to many, then our transformation algorithm must be refined; if the relationship has no descriptive attributes (Figure D.11a), then this algorithm must be employed: 1. Replace the entity sets customer and account with record types customer and account, respectively. 2. Create a new dummy record type, Rlink, that may either have no fields or have a single field containing an externally defined unique identifier. 3. Create the following two many-to-one links:
8 Appendix D Network Model customer name customer_streef customer city account number balance customer account (a) customer_name customer street customer_city account_number balance customer account access date access date (b) Figure D.10 Two data-structure diagrams. .CustRInk from Rlink record type to customer record type AcctRInk from Rlink record type to account record type The corresponding data-structure diagram is as shown in Figure D.11b.An instance of a database corresponding to the described schema appears in Fig- ure D.12.We encourage you to compare this sample database with the one de- scribed in Figure D.4. If the relationship depositor is many to many with a descriptive attribute (for example,access date),then the transformation algorithm is similar to the one described.The only difference is that the new record type Rlink now contains the field access date. customer account customer name depositor account number customer street balance customer_city (a)E-R diagram customer_name customer_street customer_cify account_number balance customer accouni CustRInk AcctRInk Rlink (b)Data structure-diagram Figure D.11 E-R diagram and its corresponding data-structure diagram
8 Appendix D Network Model Figure D.10 Two data-structure diagrams. • CustRlnk from Rlink record type to customer record type • AcctRlnk from Rlink record type to account record type The corresponding data-structure diagram is as shown in Figure D.11b. An instance of a database corresponding to the described schema appears in Figure D.12. We encourage you to compare this sample database with the one described in Figure D.4. If the relationship depositor is many to many with a descriptive attribute (for example, access date), then the transformation algorithm is similar to the one described. The only difference is that the new record type Rlink now contains the field access date. Figure D.11 E-R diagram and its corresponding data-structure diagram
D.3 The DBTG CODASYL Model 9 Hayes Main Harrison A-102400 2 A-101 500 Johnson Alma Palo Alto 3 A-201 900 Smith North Rye A-215 700 5 Figure D.12 Sample database corresponding to the diagram of Figure D.11. In the case of general(that is,nonbinary)relationships,the transformation algorithm is the same as the one described in Section D.2.2.Thus,the E-R diagram of Figure D.8a is transformed into the data-structure diagram of Figure D.8b. D.3.2 DBTG Sets Given that only many-to-one links can be used in the DBTG model,a data-structure diagram consisting of two record types that are linked together has the general form of Figure D.13.This structure is referred to in the DBTG model as a DBTG set.The name of the set is usually chosen to be the same as the name of the link connecting the two record types. In each such DBTG set,the record type A is designated as the owner (or parent) of the set,and the record type B is designated as the member(or child)of the set. Each DBTG set can have any number of set occurrences-that is,actual instances of linked records.For example,in Figure D.14,we have three set occurrences corresponding to the DBTG set of Figure D.13. Since many-to-many links are disallowed,each set occurrence has precisely one owner,and has zero or more member records.In addition,no member record of a set can participate in more than one occurrence of the set at any point.A mem- ber record,however,can participate simultaneously in several set occurrences of different DBTG sets. As an illustration,consider the data-structure diagram of Figure D.15.There are two DBTG sets: A 、B Figure D.13 DBTG set
D.3 The DBTG CODASYL Model 9 Figure D.12 Sample database corresponding to the diagram of Figure D.11. In the case of general (that is, nonbinary) relationships, the transformation algorithm is the same as the one described in Section D.2.2. Thus, the E-R diagram of Figure D.8a is transformed into the data-structure diagram of Figure D.8b. D.3.2 DBTG Sets Given that only many-to-one links can be used in the DBTGmodel, a data-structure diagram consisting of two record types that are linked together has the general form of Figure D.13. This structure is referred to in the DBTG model as a DBTG set. The name of the set is usually chosen to be the same as the name of the link connecting the two record types. In each such DBTG set, the record type A is designated as the owner (or parent) of the set, and the record type B is designated as the member (or child) of the set. Each DBTG set can have any number of set occurrences— that is, actual instances of linked records. For example, in Figure D.14, we have three set occurrences corresponding to the DBTG set of Figure D.13. Since many-to-many links are disallowed, each set occurrence has precisely one owner, and has zero or more member records. In addition, no member record of a set can participate in more than one occurrence of the set at any point. A member record, however, can participate simultaneously in several set occurrences of different DBTG sets. As an illustration, consider the data-structure diagram of Figure D.15. There are two DBTG sets: Figure D.13 DBTG set
10 Appendix D Network Model al a3 b3 b4 b5 Figure D.14 Three set occurrences. 1.depositor,which has customer as the owner of the DBTG set,and account as the member of the DBTG set 2.account branch,which has branch as the owner of the DBTG set,and account as the member of the DBTG set The set depositor can be defined as follows: set name is depositor owner is customer member is account The set account branch can be defined similarly: set name is account _branch owner is branch member is account An instance of the database appears in Figure D.16.There are six set occur- rences listed next:three of set depositor (sets 1,2,and 3),and three of set account branch (sets 4,5,and 6). 1.Owner is customer record Hayes,with a single member account record A-102 2.Owner is customer record Johnson,with two member account records A-101 and A-201 3.Owner is customer record Turner,with three member account records A-305, A-402,andA-408. customer_name customer streef customer_city branch_name branch_city assets customer brancht depositor account branch account number balance account Figure D.15 Data-structure diagram
10 Appendix D Network Model Figure D.14 Three set occurrences. 1. depositor, which has customer as the owner of the DBTG set, and account as the member of the DBTG set 2. account branch, which has branch as the owner of the DBTG set, and account as the member of the DBTG set The set depositor can be defined as follows: set name is depositor owner is customer member is account The set account branch can be defined similarly: set name is account branch owner is branch member is account An instance of the database appears in Figure D.16. There are six set occurrences listed next: three of set depositor (sets 1, 2, and 3), and three of set account branch (sets 4, 5, and 6). 1. Owner is customerrecord Hayes, with a single member accountrecord A-102. 2. Owner is customer record Johnson, with two member account records A-101 and A-201. 3. Owner is customer record Turner, with three member account records A-305, A-402, and A-408. Figure D.15 Data-structure diagram