Outline

1. Outline • Hierarchical Database Management Systems • Appendix D – 3rd ed. (Appendix E – 4th ed. Appendix D – 5th and 6th 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

2. Hierarchical Schema department dname dnumber mgrname mgrstartdate employee project name ssn bdate address pname pnumber plocation • 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 • hierarchical schemas. PCR

3. Hierarchical Schema research administration Smith Wong Narayan Zelaya Wallace Jabbar • 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: Department Employee Department research administration Project ... Product1 Product2 Computerization Product3

4. Hierarchical Schema • Properties of a Hierarchical Schema • 1. One record type, called the root of the hierarchical schema, does not 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.

5. Hiearchical Schema • 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 project name ssn bdate address pname pnumber plocation S W T worker supervisee dependent

6. Hiearchical Schema • - hierarchical occurrence D Administration Level 0: E Zalaya E Wallace E Jabbar P computerization P new-benefit Level 1: T Abner S Zalaya S Jabbar W Wong W Zalaya W Jabbar W Zalaya Level 2:

7. Hiearchical Schema • - linearized form of a hierarchical occurrence • procedurePre_order_traversal (root_record) • begin • output(root_record); • if no child node then return; • elsefor 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

8. Virtual PCR • 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

9. Virtual PCR • 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 E E P Project Employee Employee Project VP Y R Epointer Ppointer VC

10. Virtual PCR • 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.

11. Virtual PCR • Virtual Parent-child Relationships • - Example: • The relationship: Project A B C D Employees working on the project E1, E3, E5 E2, E4, E6 E1, E4 E2, E3, E4, E5 can be stored as follows:

12. Virtual PCR • Virtual Parent-child Relationships • - Example: E1 A E2 Ep1, 10 Ep2, 8 Ep3, 5 E3 B E4 Ep4, 20 Ep5, 8 Ep6, 30 E5 E6 C Ep7, 6 Ep8, 9 D Ep10, 3 Ep11, 7 Ep12, 25 Ep9, 20

13. Virtual PCR ERD for Chapter 6 database example dependent n n 1 1 Works on employee m n n 1 project n 1 1 1 department Dept_locations n 1

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

15. Data Definition • 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

16. Data Definition • Data Definition in the Hierarchical Model • - Example • SCHEMA NAME = COMPANY • HIERARCHIES = HIERARCHY1, HIERARCHY2 • RECORD • NAME = EMPLOYEE • TYPE = ROOT OF HIERARCHY2 • DATA ITEMS = • FNAME CHARACTER 15 • MINIT CHARACTER 1 • LNAME CHARACTER 15 • SSN CHARACTER 9 • BDATE CHARACTER 9 • ADDRESS CHARACTER 30

17. Data Definition • 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 HIERARCHY1 • DATAITEMS = • DNAME CHARACTER 15 • DNUMBER INTEGER • KEY = DNAME • KEY = DNUMBER • ORDER BY DNAME

18. Data Definition • 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

19. Data Definition • 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

20. Data Definition • 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

21. Data Definition • 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

22. Data Definition • Data Definition in the Hierarchical Model • - Example • RECORD • NAME = ESUPERVISEE • PARENT = EMPLOYEE • CHILD NUMBER = 1 • DATA ITEMS = • DEPNAME POINTER WITH IRTUAL PARENT = EMLPOYEE

23. Data Manipulation • 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

24. Data Manipulation • 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

25. Data Manipulation • 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

26. Data Manipulation • 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;

27. Data Manipulation • 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;

28. Data Manipulation • 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. 80TH ...’; • P_EMPLOYEE.BDATE := ‘10-Jan-55’; • P_EMPLOYEE.SEX := ‘M’; • P_EMPLOYEE.SALARY := ‘30000.00’; • $INSERT EMPLOYEE FROM P_EMPLOYEE;

29. Data Manipulation • 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;

30. Data Manipulation • 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;

31. Parent/child relationships department dname dnumber mgrname mgrstartdate employee project name ssn bdate address pname pnumber plocation • Two parent/child relationships are in the above schema: • department/employee • department/project

32. Parent/child instances department dname dnumber mgrname mgrstartdate employee project name ssn bdate address 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 Narayan English Zelaya Wallace Jabbar

33. Parent/child instances department dname dnumber mgrname mgrstartdate employee project name ssn bdate address 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 Administration Reorganization ProductX ProductY ProductZ Computerization Newbenefits

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

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

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

37. Navigating through the database Consider the database Headquarters Research Borg Reorganization Smith ProductX Wong ProductY Administration Narayan ProductZ English Zelaya Computerization Wallace Newbenefits Jabbar GU Department (dname=headquarters) Loop GNP exit when status code = ???? End Loop Program would retrieve the Department record and all of its dependents