Outline Hierarchical DBMS Hierarchical Database Management Systems Appendix

Outline Hierarchical DBMS Hierarchical Database Management Systems Appendix D – 3 rd ed. (Appendix E – 4 th ed. Appendix D – 5 th and 6 th ed. ) • Hierarchical Schemas • record type • parent-child relationship • hierarchical occurrence trees • linearized form of hierarchical occurrence • Virtual parent-child relationships • Data definition in the hierarchical model • Data manipulation language for the hierarchical model Jan. 2012 Yangjun Chen ACS-3902 1

Hierarchical Schema Hierarchical DBMS • A hierarchical schema consists of record types and PCR types. - A record is a collection of field values. - Records of the same type are grouped into record types. - A PCR type (parent-child relationship type) is a 1: N relationship between two record types. • A hierarchical database schema consists of a number of PCR hierarchical schemas. department dname dnumber mgrname mgrstartdate employee name ssn bdate address Jan. 2012 Yangjun Chen project pname pnumber plocation ACS-3902 2

Hierarchical Schema Hierarchical DBMS • PCR occurrence - Each PRC occurrence relates a record of a type (e. g. , a department) to some records of another type (e. g. , employee). PCR type: PCR occurrences: research Department Employee Department Project Jan. 2012 Yangjun Chen Smith Wong administration Narayan Zelaya Wallace Jabbar research administration Product 1 Product 2 Product 3 Computerization ACS-3902 3 . . .

Hierarchical Schema Hierarchical DBMS • Properties of a Hierarchical Schema 1. One record type, called the root of the hierarchical schema, does not participate as a child record type in any PCR type. 2. Every record type except the root participants as a child record type in exactly one PCR type. 3. A record type can participate as parent record type in any number (zero or more) of PCR types. 4. A record type that does not participate as parent record type in any PCR is called a leaf of the hierarchical schema. 5. If a record type participate as parent in more than one PCR type, then its child record types are ordered. The order is displayed, by convention, from left to right in a hierarchical diagram. Jan. 2012 Yangjun Chen ACS-3902 4

Hiearchical Schema Hierarchical DBMS • Hierarchical occurrence Each hierarchical occurrence, called an occurrence tree, is a tree structure whose root is a single record from some record type. Each subtree of the root is again a hierarchical occurrence. - type indicator D department dname dnumber mgrname mgrstartdate P E employee name ssn bdate address T dependent Jan. 2012 Yangjun Chen project pname pnumber plocation S W worker supervisee ACS-3902 5

Hiearchical Schema Hierarchical DBMS - hierarchical occurrence D Level 0: Level 1: Level 2: E Zalaya E Wallace T Abner S Zalaya Jan. 2012 Yangjun Chen Administration E Jabbar S Jabbar P computerization W Wong ACS-3902 P new-benefit W Zalaya W Jabbar 6 W Zalaya

Hiearchical Schema Hierarchical DBMS - linearized form of a hierarchical occurrence procedure Pre_order_traversal (root_record) begin output(root_record); if no child node then return; else for each child_record of root_record in left to right order do Pre_order_traversal (child_record) end D administration E Zelaya E Wallace T Abner S Zelaya S Jabbar E Jabbar P computerization W Wong W Zelaya P new-benefit W Jabbar W Zelaya Jan. 2012 Yangjun Chen ACS-3902 7

Virtual PCR Hierarchical DBMS • Virtual Parent-child Relationships - Problems with hierarchical model 1. M: N relationship causes redundancy 2. The case where a record type participates as child in more than one PCR type causes redundancy 3. N-ary relationships with more than two participating record type can not be modelled - Method dealing with the three problems: virtual record type virtual PCR relationship Jan. 2012 Yangjun Chen ACS-3902 8

Virtual PCR Hierarchical DBMS • Virtual Parent-child Relationships - virtual record type A virtual (or pointer) record type VC is a record type with the property that each of its records contains a pointer to a record of another type VP. VC plays the role of “virtual child” and VP of ‘virtual parent” in a “virtual parent-child relationship” (VPCR). A record of a VC type is a pointer to a record of some VP type. A record of a VP type is a “real” record. Example: M: N relationship between Employee and Project: P Project Employee E VP Y Epointer E Employee R Ppointer VC Jan. 2012 Yangjun Chen ACS-3902 9 Project P

Virtual PCR Hierarchical DBMS • Virtual Parent-child Relationships - intersection data in a virtual record An employee may participate in several projects. But for each project, he/she may work for different hours per week. Therefore, the data representing “different hours per week” should be included in the virtual records since each pointer to an employee record may have a different value. Such data are called intersection data. - VPCR The relationship between a virtual child and the corresponding virtual parent is called a Virtual Parent-Child-Relationship. Jan. 2012 Yangjun Chen ACS-3902 10

Virtual PCR Hierarchical DBMS • Virtual Parent-child Relationships - Example: The relationship: Project Employees working on the project A E 1, E 3, E 5 B E 2, E 4, E 6 C E 1, E 4 D E 2, E 3, E 4, E 5 can be stored as follows: Jan. 2012 Yangjun Chen ACS-3902 11

Virtual PCR Hierarchical DBMS • Virtual Parent-child Relationships - Example: A E 1 Ep 1, 10 Ep 2, 8 Ep 3, 5 E 2 E 3 B E 4 Ep 4, 20 Ep 5, 8 Ep 6, 30 E 5 E 6 C Ep 7, 6 Ep 8, 9 D Ep 9, 20 Ep 10, 3 Ep 11, 7 Ep 12, 25 Jan. 2012 Yangjun Chen ACS-3902 12

Virtual PCR Hierarchical DBMS ERD for Chapter 6 database example dependent n n 1 1 m Works on employee n project Jan. 2012 Yangjun Chen n 1 1 n 1 Dept_locations 1 n ACS-3902 1 department 13

Virtual PCR Hierarchical DBMS • Virtual Parent-child Relationships - Hierarchical schema using VPCR - for a Company database D Department Dname Dnum E Employee Ename Minit … . . . P L Dlocation Location Project Pname …. . . Y Demployee EPTR M Dmanager Start. Date MPTR Jan. 2012 Yangjun Chen Esupervisee SPTR S T W Pworker Hours WPTR ACS-3902 14 Dependent DEPname Minit . . .

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Hierarchical data definition language (HDDL) • record type data item of a record type key clause parent • virtual record type virtual parent • CHILD NUMBER clause (the left-to-right order) • ORDER BY clause (the order of individual records of the same record type) sequence key Jan. 2012 Yangjun Chen ACS-3902 15

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example SCHEMA NAME = COMPANY HIERARCHIES = HIERARCHY 1, HIERARCHY 2 RECORD NAME = EMPLOYEE TYPE = ROOT OF HIERARCHY 2 DATA ITEMS = FNAME CHARACTER 15 MINIT CHARACTER 1 LNAME CHARACTER 15 SSN CHARACTER 9 BDATE CHARACTER 9 ADDRESS CHARACTER 30 Jan. 2012 Yangjun Chen ACS-3902 16

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example SEX CHARACTER 1 SALARY CHARACTER 10 KEY = SSN CHARACTER 10 ORDER BY LNAME, FNAME RECORD NAME = DEPARTMENT TYPE = ROOT OF HIERARCHY 1 DATAITEMS = DNAME CHARACTER 15 DNUMBER INTEGER KEY = DNAME KEY = DNUMBER ORDER BY DNAME Jan. 2012 Yangjun Chen ACS-3902 17

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example RECORD NAME = DLOCATION PARENT = DEPARTMENT CHILD NUMBER = 1 DATA ITEMS = LOCATION CHARACTER 15 RECORD NAME = DMANAGER PARENT = DEPARTMENT CHILD NUMBER = 3 DATA ITEMS = MGRSTARTDATE CHARACTER 9 MPTR POINTER WITH VIRTUAL PARENT = EMPLOYEE Jan. 2012 Yangjun Chen ACS-3902 18

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example RECORD NAME = PROJECT PARENT = DEPARTMENT CHILD NUMBER = 4 DATA ITEMS = PNAME CHARACTER 15 PNUMBER INTEGER PLOCATION CHARACTER 15 KEY = PNAME KEY = PNUMBER ORDER BY PNAME Jan. 2012 Yangjun Chen ACS-3902 19

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example RECORD NAME = PWORKER PARENT = PROJECT CHILD NUMBER = 1 DATA ITEMS = HOURS CHARACTER 4 WPTR POINTER WITH VIRTUAL PARENT = EMLPOYEE RECORD NAME = DEMPLOYEES PARENT = DEPARTMENT CHILD NUMBER = 2 DATA ITEM = EPTR POINTER WITH VIRTUAL PARENT = EMPLOYEE Jan. 2012 Yangjun Chen ACS-3902 20

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example RECORD NAME = DEPENDENT PARENT = EMPLOYEE CHILD NUMBER = 2 DATA ITEMS = DEPNAME CHARACTER 15 SEX CHARACTER 1 BIRTHDATE CHARACTER 9 RELATIONSHIP CHARACTER 10 ORDER BY DESC BIRTHDATE Jan. 2012 Yangjun Chen ACS-3902 21

Data Definition Hierarchical DBMS • Data Definition in the Hierarchical Model - Example RECORD NAME = ESUPERVISEE PARENT = EMPLOYEE CHILD NUMBER = 1 DATA ITEMS = DEPNAME POINTER WITH IRTUAL PARENT = EMLPOYEE Jan. 2012 Yangjun Chen ACS-3902 22

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - Hierarchical data manipulation language (HDML) • a record-at-a-time • HDML must be embedded in a host language such as COBOL, PL/1, Pascal, . . . user-work-area - programs • currency indicators - current of database: a single hierarchical schema and its current database records - current of hierarchy for each hierarchical schema - current of record type for each record type • command classification - retrieval: GET - record update: INSERT, DELETE, REPLACE - current record: GET HOLD Jan. 2012 Yangjun Chen ACS-3902 23

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - GET • GET FIRST <record type name> [WHERE <condition>] • GET NEXT <record type name> [WHERE <condition>] $GET FIRST EMPLOYEE WHERE SALARY < ‘ 20000. 00’; while DB_STATUS = 0 do begin writeln (P_EMPLOYEE. FNAME, P_EMPLOYEE. LNMAE); $GET NEXT EMPLOYEE WHERE SALARY < ‘ 20000. 00’ end Jan. 2012 Yangjun Chen ACS-3902 24

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - GET • GET (FIRST | NEXT) PATH <hierarchical path> [WHERE <condition>] $GET FIRST PATH EMPLOYEE, DEPENDENT WHERE EMPLOYEE. FNAME=‘John’ AND DEPENDENT. DEPNAME=‘John’; while DB_STATUS = 0 do begin writeln (P_EMPLOYEE. LNAME, P_EMPLOYEE. BDATE, P_DEPENDENT. BIRTHDATE); $GET NEXT PATH EMPLOYEE, DEPENDENT WHERE EMPLOYEE. FNAME=‘John’ AND DEPENDENT. DEPNAME=‘John’ end Jan. 2012 Yangjun Chen ACS-3902 25

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - GET • GET NEXT <child record type name> WITHIN [VIRTUAL] PARENT [<parent record type name] [WHERE <condition>] $GET FIRST PATH DEPARTMENT, PROJECT WHERE DEPARTMENT. DNAME=‘Research’; while DB_STATUS = 0 do begin writeln (P_PROJECT. PNAME); $GET NEXT PROJECT WITHIN PARENT end; Jan. 2012 Yangjun Chen ACS-3902 26

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - Calculating aggregate functions: COUNT, AVERAGE, . . . $GET FIRST PATH DEPARTMENT, DEMPLOYEE; while DB_STATUS = 0 do begin total_sal : =0; no_of_emps : =0; writeln (P_DEPARTMENT. DNAME); while DB_STATUS = 0 do begin $GET VIRTUAL PARENT EMPLOYEE; total_sal : = total_sal + conv_sal(P_EMPLOYEE. SALARY); no_of_emps : = no_of_emps +1; $GET NEXT DEMPLOYEES WITHIN PARENT DEPARTMENT end writeln (‘no of emps =’, no_of_emps, ‘avg sal of emps =’, total_sal/no_of_emps); $GET NEXT PATH DEPARTMENT, DEMPLOYEES end; Jan. 2012 Yangjun Chen ACS-3902 27

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - Update: INSERT, DELETE, REPLACE P_EMPLOYEE. FNAME : = ‘John’; P_EMPLOYEE. LNAME : = ‘Smith’; P_EMPLOYEE. MINIT : = ‘F’; P_EMPLOYEE. SSN : = ‘ 567875432’; P_EMPLOYEE. ADDRESS : = ‘ 40 N. W. 80 TH. . . ’; P_EMPLOYEE. BDATE : = ‘ 10 -Jan-55’; P_EMPLOYEE. SEX : = ‘M’; P_EMPLOYEE. SALARY : = ‘ 30000. 00’; $INSERT EMPLOYEE FROM P_EMPLOYEE; Jan. 2012 Yangjun Chen ACS-3902 28

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - Update: INSERT, DELETE, REPLACE $GET HOLD FIRST EMPLOYEE WHERE SEX = ‘M’; while DB_STATUS = 0 do begin writeln (P_EMPLOYEE. LNAME, P_EMPLOYEE. FNAME); $DELETE EMPLOYEE $GET HOLD NEXT EMPLOYEE WHERE SEX = ‘M’ end; Jan. 2012 Yangjun Chen ACS-3902 29

Data Manipulation Hierarchical DBMS • Data Manipulation in the Hierarchical Model - Update: INSERT, DELETE, REPLACE $GET FIRST PATH DEPARTMENT, DEMPLOYEE WHERE DNAME = ‘Reseach’; while DB_STATUS = 0 do begin $GET HOLD VIRTUAL PARENT EMPLOYEE OF DEMPLOYEES P_EMPLOYEE. SALARY : = P_EMPLOYEE. SALARY * 1. 1; $REPLACE EMPLOYEE FROM P_EMPLOYEE $GET NEXT DEMPLOYEES WHERE PARENT DEPARTMENT end; Jan. 2012 Yangjun Chen ACS-3902 30

Parent/child relationships Hierarchical DBMS department dname dnumber mgrname mgrstartdate employee name ssn bdate address project pname pnumber plocation Two parent/child relationships are in the above schema: • department/employee • department/project Jan. 2012 Yangjun Chen ACS-3902 31

Parent/child instances Hierarchical DBMS department dname dnumber mgrname mgrstartdate employee name ssn bdate address project pname pnumber plocation Using the data we had previously seen in Ch 7, we can depict the following 3 instances of department/employee: Headquarters Research Administration Borg Smith Wong Jan. 2012 Yangjun Chen Narayan English ACS-3902 Zelaya 32 Wallace Jabbar

Parent/child instances Hierarchical DBMS department dname dnumber mgrname mgrstartdate employee name ssn bdate address project pname pnumber plocation Using the data we had previously seen in Ch 7, we can depict the following 3 instances of department/project: Headquarters Research Reorganization Product. X Product. Y Jan. 2012 Yangjun Chen Product. Z Administration Computerization ACS-3902 33 Newbenefits

Hierarchical records Hierarchical DBMS In the following 3 hierarchical records are depicted. This is another way that such information is often depicted in practice. Research Smith Wong Narayan English Product. X Product. Y Product. Z Note that a child record cannot exist without its parent record - this is similar to the concepts of • FK integrity and • weak entity Headquarters Administration Borg Zelaya Wallace Jabbar Jan. 2012 Yangjun Chen Computerization Newbenefits ACS-3902 34 Reorganization

Schema definition Schema name = company record name=department field dname field dnumber field mgrname field mgrstartdate key=dnumber record name=employee parent=department field name field ssn field bdate field address key=ssn record name=project parent=department field pname field pnumber field location key=pnumber Jan. 2012 Yangjun Chen Hierarchical DBMS character 15 integer character 30 date Need: Names of records parent/child relationships character 15 character 9 date character 30 fields keys syntax will vary, but these are the types of things that must be present. character 15 integer character 15 ACS-3902 35

Data manipulation Hierarchical DBMS Navigational - not set-oriented - you retrieve one record at a time T, GU, GNP, ISRT, DLE IMS command names Retrieval GU, Get unique retrieve a specific record GN, Get next using your current position, get the next record in the database GNP, Get next within parent using your current position, get the next child record for that parent Updating REPL are ISRT, Insert DLET, Delete REPL, Replace Jan. 2012 Yangjun Chen ACS-3902 36

Navigating through the database Hierarchical DBMS Consider the database Headquarters Research Borg Smith Wong Narayan English Product. X Product. Y Product. Z Administration Zelaya Wallace Jabbar GU Department (dname=headquarters) Loop GNP exit when status code = ? ? End Loop Jan. 2012 Yangjun Chen Reorganization Computerization Newbenefits Program would retrieve the Department record and all of its dependents ACS-3902 37