Atnaujinkite slapukų nuostatas

Joe Celko's Trees and Hierarchies in SQL for Smarties 2nd edition [Minkštas viršelis]

3.93/5 (110 ratings by Goodreads)
(Independent Consultant, Austin, Texas)
Kitos knygos pagal šią temą:
Joe Celko's Trees and Hierarchies in SQL for Smarties 2nd editionDidesnis paveikslėlis
Kitos knygos pagal šią temą:
Pastovi nuoroda: https://www.kriso.lt/db/9780123877338.html
Raktažodžiai:
Provides information on developing database applications in SQL, covering such topics as adjacendy list model, nested sets, binary trees, data modeling, graphs, and hierarchical database systems.

The demand for SQL information and training continues to grow with the need for a database behind every website capable of offering web-based information queries. SQL is the de facto standard for database retrieval, and if you need to access, update, or utilize data in a modern database management system, you will need SQL to do it. The Second Edition of Joe Celko's Trees and Hierarchies in SQL for Smarties covers two new sets of extensions over three entirely new chapters and expounds upon the changes that have occurred in SQL standards since the previous edition's publication. Benefit from mastering the challenging aspects of these database applications in SQL as taught by Joe Celko, one of the most-read SQL authors in the world.

*Expert advice from a noted SQL authority and award-winning columnist who has given 10 years of service to the ANSI SQL standards committee

*Teaches scores of advanced techniques that can be used with any product, in any SQL environment

*Offers graph theory and programming techniques for working around deficiencies and gives insight into real-world challenges



The demand for SQL information and training continues to grow with the need for a database behind every website capable of offering web-based information queries. SQL is the de facto standard for database retrieval, and if you need to access, update, or utilize data in a modern database management system, you will need SQL to do it. The Second Edition of Joe Celko's Trees and Hierarchies in SQL for Smarties covers two new sets of extensions over three entirely new chapters and expounds upon the changes that have occurred in SQL standards since the previous edition's publication. Benefit from mastering the challenging aspects of these database applications in SQL as taught by Joe Celko, one of the most-read SQL authors in the world.

*Expert advice from a noted SQL authority and award-winning columnist who has given 10 years of service to the ANSI SQL standards committee

*Teaches scores of advanced techniques that can be used with any product, in any SQL environment

*Offers graph theory and programming techniques for working around deficiencies and gives insight into real-world challenges

Daugiau informacijos

Achieve programming success with the master of SQL, Joe Celko
Introduction xiii
1 Graphs, Trees, and Hierarchies
1(10)
1.1 Defining Tree and Hierarchies
3(3)
1.1.1 Trees
3(1)
1.1.2 Properties of Hierarchies
4(1)
1.1.3 Types of Hierarchies
5(1)
1.2 Network Databases
6(1)
1.3 Modeling a Graph in a Program
7(1)
1.4 The Great Debate
7(1)
1.5 Note on Recursion
8(3)
2 Adjacency List Model
11(24)
2.1 The Simple Adjacency List Model
12(1)
2.2 The Simple Adjacency List Model Is Not Normalized
13(4)
2.2.1 UPDATE Anomalies
14(1)
2.2.2 INSERT Anomalies
15(1)
2.2.3 DELETE Anomalies
15(1)
2.2.4 Structural Anomalies
16(1)
2.3 Fixing the Adjacency List Model
17(4)
2.3.1 Concerning the Use of NULLs
20(1)
2.4 Navigation in Adjacency List Model
21(6)
2.4.1 Cursors and Procedural Code
21(1)
2.4.2 Self-joins
21(2)
2.4.3 Finding a Subtree with Recursive CTE
23(1)
2.4.4 Finding a Subtree with Iterations
24(1)
2.4.5 Finding Ancestors
25(2)
2.5 Inserting Nodes in the Adjacency List Model
27(1)
2.6 Deleting Nodes in the Adjacency List Model
27(3)
2.6.1 Deleting an Entire Subtree
27(1)
2.6.2 Promoting a Subordinate after Deletion
28(1)
2.6.3 Promoting an Entire Subtree after Deletion
29(1)
2.7 Leveled Adjacency List Model
30(5)
2.7.1 Numbering Levels
31(1)
2.7.2 Aggregation in the Hierarchy
31(4)
3 Path Enumeration Models
35(14)
3.1 Finding the Depth of the Tree
37(1)
3.2 Searching for Subordinates
37(2)
3.3 Searching for Superiors
39(1)
3.4 Deleting a Subtree
39(1)
3.5 Deleting a Single Node
39(1)
3.6 Inserting a New Node
40(1)
3.7 Splitting up a Path String
40(2)
3.8 Microsoft SQL Server's HIERARCHYID
42(2)
3.9 Edge Enumeration Model
44(1)
3.10 XPath and XML
45(4)
4 Nested Sets Model of Hierarchies
49(62)
4.1 Finding Root and Leaf Nodes
51(2)
4.2 Finding Subtrees
53(1)
4.3 Finding Levels and Paths in a Tree
54(9)
4.3.1 Finding the Height of a Tree
54(1)
4.3.2 Finding Levels of Subordinates
55(5)
4.3.3 Finding Oldest and Youngest Subordinates
60(2)
4.3.4 Finding a Path
62(1)
4.3.5 Finding Relative Position
63(1)
4.4 Functions in the Nested Sets Model
63(1)
4.5 Deleting Nodes and Subtrees
64(7)
4.5.1 Deleting Subtrees
65(3)
4.5.2 Deleting a Single Node
68(3)
4.5.3 Pruning a Set of Nodes from a Tree
71(1)
4.6 Closing Gaps in the Tree
71(3)
4.7 Summary Functions on Trees
74(9)
4.7.1 Iterative Parts Update
75(5)
4.7.2 Recursive Parts Update
80(3)
4.8 Inserting and Updating Trees
83(14)
4.8.1 Moving a Subtree within a Tree
85(4)
4.8.2 MoveSubtree Second Version
89(2)
4.8.3 Insertion of an Immediate Subtree
91(2)
4.8.4 Subtree Duplication
93(3)
4.8.5 Swapping Siblings
96(1)
4.9 Converting Nested Sets Model to Adjacency List Model
97(1)
4.10 Converting Adjacency List Model to Nested Sets Model
98(4)
4.10.1 Stack Algorithm
98(2)
4.10.2 Ben-Gan's Recursive Common Table Expression (CTE)
100(2)
4.11 Separation of Edges and Nodes
102(2)
4.11.1 Multiple Structures
103(1)
4.11.2 Multiple Nodes
103(1)
4.12 Comparing Nodes and Structure
104(4)
4.13 Nested Sets Code in Other Languages
108(3)
5 Frequent Insertion Trees
111(38)
5.1 The Data Type of (lft, rgt)
113(2)
5.1.1 Exploiting the Full Range of Integers
113(1)
5.1.2 FLOAT, REAL, or Double Precision Numbers
114(1)
5.1.3 NUMERIC(p, s) or DECIMAL(p, s) Numbers
114(1)
5.2 Computing the Spread to Use
115(9)
5.2.1 Varying the Spread
118(1)
5.2.2 Divisor Parameter
118(1)
5.2.3 Divisor via Formula
119(1)
5.2.4 Divisor via Table Lookup
119(1)
5.2.5 Partial Reorganization
120(2)
5.2.6 Rightward Spread Growth
122(2)
5.3 Total Reorganization
124(6)
5.3.1 Reorganization with Lookup Table
124(5)
5.3.2 Reorganization with Recursion
129(1)
5.4 Rational Numbers and Nested Intervals Model
130(17)
5.4.1 Partial Order Mappings
132(3)
5.4.2 Summation of Coordinates
135(3)
5.4.3 Finding Parent Encoding and Sibling Number
138(2)
5.4.4 Calculating the Enumerated Path and Distance between Nodes
140(4)
5.4.5 Building a Hierarchy
144(1)
5.4.6 Depth-first Enumeration by Left Interval Boundary
145(1)
5.4.7 Depth-first Enumeration by Right Interval boundary
145(1)
5.4.8 All Descendants of a Node
146(1)
5.5 Egyptian Fractions
147(2)
6 Linear Version of the Nested Sets Model
149(8)
6.1 Insertion and Deletion
151(1)
6.2 Finding Paths
152(1)
6.3 Finding Levels
153(1)
6.4 Cash Register Tape Problem
153(4)
7 Binary Trees
157(16)
7.1 Binary Tree Traversals
159(2)
7.2 Binary Tree Queries
161(3)
7.2.1 Find Parent of a Node
162(1)
7.2.2 Find Subtree at a Node---Recursive Common Table Expression (CTE)
163(1)
7.2.3 Find Subtree at a Node---Data Driven
163(1)
7.3 Deletion from a Binary Tree
164(1)
7.4 Insertion into a Binary Tree
165(1)
7.5 Heaps
165(3)
7.6 Binary Tree Representation of Multiway Trees
168(1)
7.7 Stern--Brocot Numbers
169(4)
8 Other Models for Trees
173(14)
8.1 Adjacency List with Self-References
173(1)
8.2 Subordinate Adjacency List
174(1)
8.3 Hybrid Models
175(5)
8.3.1 Adjacency and Nested Sets Model
175(1)
8.3.2 Nested Sets with Depth Model
176(1)
8.3.3 Adjacency and Depth Model
177(1)
8.3.4 Computed Hybrid Models
178(2)
8.4 Path Enumeration Using Prime Number Products
180(7)
8.4.1 Find the Subordinates of a Node
181(1)
8.4.2 Find the Superiors of a Node
181(1)
8.4.3 Hierarchy with Levels
182(1)
8.4.4 Insert New Employee under a Boss
182(1)
8.4.5 Delete an Employee
183(1)
8.4.6 Decomposing a Path
184(1)
8.4.7 Helpful Functions
184(3)
9 Proprietary Extensions for Trees
187(8)
9.1 Oracle Tree Extensions
187(3)
9.1.1 Related Tree Extensions
190(1)
9.2 DB2 and the WITH Operator
190(1)
9.3 Date's EXPLODE Operator
191(1)
9.4 Tillquist and Kuo's Proposals
192(1)
9.5 Microsoft Extensions
192(1)
9.6 Other Methods
193(2)
10 Hierarchies in Data Modeling
195(16)
10.1 Types of Hierarchies
200(1)
10.2 Data Definition Language Constraints
200(11)
10.2.1 Uniqueness Constraints
201(3)
10.2.2 Disjoint Hierarchies
204(3)
10.2.3 Representing l:l, l:m, and n:m Relationships
207(4)
11 Hierarchical Encoding Schemes
211(8)
11.1 ZIP Codes
211(1)
11.2 Dewey Decimal Classification
212(1)
11.3 Strength and Weaknesses
213(2)
11.4 Shop Categories
215(2)
11.5 Statistical Tools for Decision Trees
217(2)
12 Graphs in SQL
219(20)
12.1 Adjacency List Model Graphs
219(12)
12.1.1 SQL and the Adjacency List Model
220(2)
12.1.2 Paths with CTE
222(5)
12.1.3 Nonacyclic Graphs
227(2)
12.1.4 Adjacency Matrix Model
229(2)
12.2 Split Node Nested Sets Models for Graphs
231(8)
12.2.1 All Nodes in the Graph
231(1)
12.2.2 Path End Points
232(1)
12.2.3 Reachable Nodes
233(1)
12.2.4 Edges
233(1)
12.2.5 Indegree and Outdegree
234(1)
12.2.6 Source, Sink, Isolated, and Internal Nodes
235(1)
12.2.7 Converting Acyclic Graphs to Nested Sets
236(3)
13 Petri Nets
239(8)
13.1 Data Definition Language for Petri Nets
242(5)
14 State Transition Graphs
247(10)
14.1 The Temporal Side of Changes
252(5)
15 Hierarchical Database Systems (IMS)
257(16)
15.1 Types of Databases
257(1)
15.2 Database History
258(4)
15.2.1 Data Language I
260(1)
15.2.2 Control Blocks
260(1)
15.2.3 Data Communications
261(1)
15.2.4 Application Programs
261(1)
15.2.5 Hierarchical Databases
261(1)
15.2.6 Strengths and Weaknesses
262(1)
15.3 Sample Hierarchical Database
262(9)
15.3.1 Department Database
264(1)
15.3.2 Student Database
264(1)
15.3.3 Design Considerations
265(1)
15.3.4 Example Database Expanded
265(1)
15.3.5 Data Relationships
266(2)
15.3.6 Hierarchical Sequence
268(1)
15.3.7 Hierarchical Data Paths
268(1)
15.3.8 Database Records
269(1)
15.3.9 Segment Format
270(1)
15.3.10 Segment Definitions
271(1)
15.4 Summary
271(2)
Index 273
Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas. He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases.