Traditional Database Models - Database Fundamentals Handout

Learning Objectives

At the end of this Chapter you will:

??Describe the main features of the hierarchic and network database models.

??Be able to design a simple database upon hierarchic and network lines.

??Explain how data is retrieved from these types of system.

??Assess the shortcomings and advantages of these types of database model.

Hierarchic database design principles

A hierarchic database system is one that is built as a tree of related record types. All hierarchic databases have one, and only one ‘root’ record type. This root records type may participate in one-to-many relationships with any number of ‘child’ record types.

Fig4-1 A root record with child records.

67

It is only capable of showing one type of relationship (1-M) and that all relationships must work in one direction only towards a single root entity. If the customer has accounts at more than one branch, then the record will have to make multiple appearances under different roots.

Implementing a hierarchic schema

A hierarchic database schema consists of a simple series of record descriptions, each of which includes a reference to the ‘owner’ of that record.

DBNAME = BANKSYSTEM RECORD TYPE = BANK FILDS BRANCHENAME = CHAR20 ADDRESS – CHAR 30 MANAGER_NO = INTEGER RECORD TYPE = ACCOUNT PARENT = BANK FIELDS

ACCNO = INTEGER UNIQUE BALANCE = FLOAT ACC_TYPE = CHAR 1 RECORD TYPE = EMP PARENT = BANK FIELDS

EMPNO = INTEGER UNIQUE INS_NO = CHAR 9

68

RECORD TYPE = CUSTOMER PARENT = ACCOUNT FIELDS REFNO = INTEGER NAME = CHAR 20 ADDRESS = CHAR 30 STATUS = CHAR 8

Figure 4.4 A hierarchic database definition.

The idea of relating two records together in a database schema by means of common data values is simply not present in a hierarchic system. This has the following implications for data integrity in a hierarchic system:

1. No Child record may exist without a parent.

2. When a parent record is removed, then all of its child records are automatically removed. 3. Any changes to a parent record are, by implication, cascaded to the information that is

stored about its child records.

Hierarchic data retrieval

Operators in a hierarchic system work on a ‘record at a time’ basis, meaning that each activation of an operator retrieves just one record. To retrieve a set of records requires a retrieval operation to be applied repetitively.

The simplest retrieval operation is GET FIRST, meaning ‘get the first record’. To find the name of a branch with a given address, we would say:

GET FIRST bank

WHERE bank.address = ’12 Low Street’; PRINT bank.branchname;

69

This operation reads all BANK type records until it finds one satisfying the given condition. This operation examines the root set of records in our system. It is possible to perform a direct access operation on records below the root. The details of an account could be retrieved thus:

GET FIRST account

WHERE account.accno = 67843; PRINT account.balance, account.type;

This second command will examine all of the account records starting from the first account under the first branch and read all account records from that point onwards until it finds an account with the given ACCNO value.

To display the details of the customers, who own a particular account, we would start the search at a level lower. This command retrieves customer details for the account 678543:

GET FIRST account

WHERE account.accno = 678543; GET NEXT WITHIN PARENT customer; WHILE DB_ STATUS_OK

PRINT customer.name;

GET NEXT WITHIN PARENT customer END-WHILE

Hierarchic data updating

To insert data into a hierarchic database, it is necessary to build a data record with the required values and then insert it under the required owner record. For instance, to insert a new account at the Grawley branch, we need to do the following:

account.accno = 929292; account.balance = 0.00; account.type = ‘C’; INSERT account

WHERE bank.branchname = ‘Crawley’;

70

customer.refno = 2394; Customer.name = ‘LA Parath’; Customer.status = ‘Business’; INSERT customer

WHERE account.accno = 929292;

Both of these INSERT operations would fail if there were no parent record with the existing search condition.

To update a record, we need to find the record to be updated and then change the relevant values. To change the address of a customer, we would say;

GET HOLD FIRST customer WHERE refno = 2345;

Cusomter.address = ‘PO Box 457, Kellysville’; REPLACE;

To delete a record, we locate it and then simply give the DELETE command, for example:

GET HOLD FIRST customer

WHERE customer refno = 2345; DELETE;

This would simply delete the first customer with this reference number. If this customer was attached to many accounts and we wished to delete all record of their existence, we would need a loop:

GET HOLD FIRST customer

WHERE customer.refno = 2345; WHILE DE_STATUS_OK

DELETE;

GET HOLD NEXT customer WHERE

71

END_WHILE

Network databases

A major disadvantage of hierarchic databases is that they are only capable of representing 1-M relationships that emanate in a single line from a root record type.

A network database consists primarily of a collection of record types. Record types are associated together by links. A link represents a 1-M relationship between a record of one type (‘the owner’) and a set of records of another type (‘the members’). The major difference between hierarchic and network systems is that there is no constraint on the number of direction of links that may be established between record types. The need for a root record type is removed and record sets are free to participate in any number of 1-M relationships.

In Figure 4.5, we have a network database where BANK is participating ina1-M relationship with ACCOUNT. This is indicated by the direction of the arrow from BANK to ACCOUNT. We also have an arrow going from CUSTOMER to REGISTRATION, indicating a 1-M relationship and likewise from ACCOUNT to REGISTRATION.

Fig4.5 A Simple Network database

72

Implementing a network schema

A network database schema consists of a series of record type declaration and set declarations that establish the links between record types.

The most important difference between this code and that used to implement the hierarchic system is the absence of any ownership references within the record declarations. Instead, we have ‘set’ declarations to represent the relationships between record types. The OWNER IS X MEMBER IS Y syntax establishes a 1-M relationship between X and Y which is physically implemented as a chain of connecting pointers between an owner record and its ‘ownees’.

SCHEMA NAME BANK SYSTEM.

RECORD BANK.

FLD BRANCHNAME CHAR(20) DUPLICATES NOT ALLOWED.

FLD BANK-ADDRESS CHAR(30)

RECORD ACCOUNT.

FLD ACCNO INTEGER(6) DUPLICATES NOT ALLOWED. FLD TYPE CHAR(1)

FLD BALANCE FLOAT(10, 2)

RECORD CUSTOMER.

FLD REFNO INTEGER(4) DUPLICATES NOT ALLOWED FLD NAME CHAR(20)

FLD CUST-ADDRESS CHAR (30) FLD STATUS CHAR(8)

SET BANK-ACCOUNTS.

73

SET ACCOUNT-OWNER

OWNER IS A ACCOUNT MEMBER IS REGISTRATION MANDATORY AUTOMATIC

SET CUSTOMER-ACCOUNTS

OWNER IS CUSTOMER MEMBER IS REGISTRATION OPTIONAL MANUAL

SET BANK-FILE

OWNER IS SYSTEM MEMBER IS BANK

SET CUSTOMER-FILE

OWNER IS SYSTEM MEMBER IS CUSTOMER

Figure 4.8 A network schema

Network data retrieval

Network data retrieval operates work on a ‘record at a time’ basis. As with a hierarchic system, an operation has to be applied repetitively to retrieve a set of records.

The programmer has to be able to ‘navigate’ the database to the actual record that is required. This record will be within a set of records of the given type, and the process of navigation may require following ownership and membership links between different sets and within a set of records.

The process of retrieving a record consists of two stages.

1. Use the FIND operator to navigate the database to a particular record. The address of this record becomes the CRU.

2. Use the GET operator to retrieve the record located by the CRU.

The simplest form of retrieval is the location of a single record within a SYSTEM-owned set. For instance, to retrieve the name of a bank with a given address, we require the following routine:

74

FIND ANY BANK USING ADDRESS; IF DB_STATUS_OK

GET BANK;

PRINT BANK. BRANCHNAME END-IF;

The FIND ANY operator locates the first record within the given set that satisfies the given constraint. A DB_STATUS flag is required in case the operation has failed to locate any such record, leaving the CRU in an undermined state. If the CRU does have a determined state (that is, record has been located), then the GET operator is activated to return the record located via the CRU.

Network Updating

The updating of data in a network database requires a record to be built and then navigation to the point where the update must take place.

With SYSTEM-owned sets, this is relatively straightforward. For instance, to create a new customer record, we can simply say:

CUSTOMER.NAME: = ‘K Chan’;

CUSTOMER.ADDRESS: = 57, Peterlee Road’; CUSTOMER.REFNO: = ‘9182’;

CUSTOMER.STATUS: = ‘Domestic’; STORE CUSTOMER;

The STORE operator automatically causes the insertion of the given customer into the SYSTEM- owned set CUSTOMER-FILE. Navigation as such is not necessary here. To change the details of a customer, some navigation is required. For instance, the alternation if a customer’s address would require the following:

CUSTOMER.NAME = ‘P Jones’

FIND FOR UPDATE ANY CUSTOMER WITHIN CUSTOMER-FILE USING NAME; GET CUSTOMER;

75

MODIFY CUSTOMER;

To create a new account, we need to find the set of accounts belonging to a branch and then insert it into that set thus:

BANK.BRANCHNAME: = ‘Crawley’; ACCOUNT.ACCNO: = 929292; ACCOUNT.BALANCE: = 0.00; ACCOUNT.TYPE: = ‘C’;

FIND ANY BANK WITHIN ABANK-FILE USING BRANCHNAME; IF DB_STATUS _OK

FIND LAST WITHIN BANK-ACCOUNTS; STORE ACCOUNT

END-IF;

Deleting a record is performed using the ERASE operator:

CUSTOMER.NAME ‘ ‘P Jones’

FIND FOR UPDATE ANY CUSTOMER WITHIN CUSTOMER-FILE USING NAME; ERASE CUSTOMER;

This will only delete the first customer found with this name. If we wished to delete all customers with this name, we require a loop:

CUSTOMER.NAME = ‘P Jones’

FIND FOR UPDATE FIRST CUSTOMER WITHIN CUSTOMER-FILE UAING NAME; WHILE DB_STSTUS_OK

ERASE CUSTOMER;

FIND FOR UPDATE NEXT WITHIN CUSTOMER-FILE USING NAME END-WHILE;

Each of these examples will only succeed if the given customers did not currently own any registrations. ERASE needs to be qualified in order to specify what should be done in the case of a record that ‘owns’ other records:

76

ERASE PERMANENT: deletes a record along with all records that belong to sets that it owns whose membership is MANDATORY. Members of set that it owns whose membership is OPTIONAL are disconnected but not deleted.

ERASE SELECTIVE: deletes a record, all records insets that it owns whose membership is MANDATORY and all records in sets that it owns whose membership is OPTIONAL and which do not participate in any other sets.

ERASE ALL: deletes a record along with all records in all sets that this record own, regardless of whether membership is OPTIONAL or MANDATORY.

SUMMARY

??Hierarchic and network databases implement a database as a collection of record types associated together in a series of 1-M relationships.

??In a hierarchic system, the terminology ‘parent’ and ‘child’ is used to describe that 1- M tie between record types.

??In a hierarchic system, there exists one root record type. All other record types must have exactly one parent and must be linked either directly or indirectly to the root record type.

??In a network system, 1-M relationships are characterized by sets which connect an ‘owner’ with its ‘members’. There is no restriction on the number of set connections that may be established between and within record types.

??In a network system, record types that are the approximate equivalent of weak entities may be established to represent M-N relationships between record types.

??Hierarchic and network systems both manipulate data on a ‘record at a time’ basis. Hierarchic operations are characterized by tree handling, network operations are typically navigational in character.

Test your Understanding

77

At the EverCare Country General Hospital, patients are admitted and given a unique Patient Id. We record their name, home address and date of admission. They are assigned to a ward and to a consultant. They may then be diagnosed a set of conditions. Whilst in our care, they will receive a set of treatment. Each treatment will have a data and details of the nurse administering the treatment, the drug being administered and the dosage. Drugs have a unique Drug Id, a name and a recommended dosage (which may not be the same as the dosage given in individual treatments). Nurses have a Nurse Id and a name and are assigned to a ward. Each ward has a Sister (who is a nurse). Consultants have a Doctor Id and a name.

Using this and the E/R diagram that you drew for Exercise 1 (a) in chapter 2, draw a diagram of record sets and links to show how this would be represented as a network database.

Write a network database schema to implement your answer to question 1 above. Write commands based on your schema to:

a. Find the name of the patient with ID ‘12345’; b. List the conditions diagnosed for patient ‘12345’;

c. Find the names of patients assigned to the consultant with the name “Dr Fleischman’; d. Find the names of all drugs administered to patients assigned to ‘Dr Fleischman’;

78

In document Database Fundamentals Handout (Page 66-78)