Joe Celko's Trees and Hierarchies in SQL for Smarties

Joe Celko's Trees and Hierarchies in SQL for Smarties

The Morgan Kaufmann Series in Data Management Systems
2004, Pages 17-34
Joe Celko's Trees and Hierarchies in SQL for Smarties

Chapter 2 - Adjacency List Model rights and content

Publisher Summary

The chapter discusses adjacent list models used in databases. The chapter describes a method for showing hierarchies in SQL that consists of a column for the boss and another column for the employee in a relationship. It is a direct implementation in a table of the adjacency list model of a graph. Oracle is the first commercial database to use SQL, and the sample database that comes with their product, nicknamed the “Scott/Tiger” database in the trade because of its default user and password codes, uses an adjacency list model in a combination Personnel/Organizational chart table. The organizational structure and the personnel data are mixed together in the same row. This model is popular as it is the most natural way to convert from an IMS database or from a procedural language to SQL. The simple adjacency list model is not a normalized schema. A normalized schema has no data redundancy and is safe from data anomalies. The chapter proposes three characteristics required in a data model. First, that the typical adjacency list model table includes information about the node, as well as who the boss in each row is. The second characteristic is that each fact appears in one place in the schema, but the subtree of each node can be in more than one row. The third characteristic is that each fact appears one time in the schema. Violations of these conditions result in anomalies. The chapter lists down the fundamental problems of adjacency list models and provide solutions to these problems.

References (0)

Cited by (0)

View full text