1 / 10

Example: Taxonomy of Organisms

Example: Taxonomy of Organisms. Hierarchy of categories: Kingdom - phylum – class – order – family – genus - species How would you design a relational schema for this?. Animals. Chordates. Arthropods. Vertebrates. insects. spiders. crustaceans. birds. reptiles. mammals.

mackenzie
Download Presentation

Example: Taxonomy of Organisms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Example: Taxonomy of Organisms • Hierarchy of categories: • Kingdom - phylum – class – order – family – genus - species • How would you design a relational schema for this? Animals Chordates Arthropods Vertebrates insects spiders crustaceans birds reptiles mammals

  2. Relational Schema for Taxonomy • Adjacency list • Idea – each tuple has a parent id

  3. Problem: Recursive Queries • “Find all ancestors/descendants of X” • “Find all arthropods that are not crustaceans” • “Print the whole tree” • Solutions may require many lines of code and may not be very robust

  4. Another Solution: nested sets • Idea: traverse entire tree, assign each object in tree a left_id and a right_id • Each child left_id and right_id are between parent’s left_id and right_id • Label entire tree using depth-first traversal of tree

  5. Nested sets example 1 Animals 2 Chordates 11 Arthropods 3 Vertebrates 10 insects spiders crustaceans 4 5 6 7 8 9 birds reptiles mammals

  6. Challenges • Need a script to traverse database • Script must be rerun every time database is updated • Update time linear in size of table • More costly than B-trees

  7. Multidimensional Arrays • How would you store an array in a database? • One option: x, y, z values as columns • A[5,1,2] = 2.53 becomes:

  8. Problems? • High storage overhead – each index is now stored as an integer value • Queries more complex • Lose proximity information, what is “near” an array element • Iterating over arrays takes longer • A[5,1,2] and A[5,1,3] may not be stored near each other

  9. Should I use a RDBMS? • Advantages: • Leverage years of research and practice • E.g., indexes, query optimization • Many commercial and open-source products available

  10. Should I use a RDBMS? • Disadvantages • May be difficult to express data/queries using relational model • e.g., arrays, hierarchical data • Performance issues • Need to consider these tradeoffs! • Well-designed database can help • “Augment” database to support domain

More Related