What is a tree based system and how can you illustrate a tree-based system with a SQL query? Please be detailed. Thanks!
SQL QUERY FOR TREE
We have some tree structures stored in SQL Server and use the technique mentioned above to store a node's hierarchy in the record. i.e.
tblTreeNode
TreeID = 1
TreeNodeID = 100
ParentTreeNodeID = 99
Hierarchy = ".33.59.99.100."
[...] (actual data payload for node)
FOR SELECTION
SELECT * FROM tblNode WHERE Hierarchy LIKE '%.100.%'
Here's the insert trigger:
--Setup the top level if there is any
UPDATE T RIGGER
SET T.TreeNodeHierarchy = '.' + CONVERT(nvarchar(10), T.TreeNodeID) + '.'
FROM tblTreeNode AS T
INNER JOIN inserted i ON T.TreeNodeID = i.TreeNodeID
WHERE (i.ParentTreeNodeID IS NULL) AND (i.TreeNodeHierarchy IS NULL)
WHILE EXISTS (SELECT * FROM tblTreeNode WHERE TreeNodeHierarchy IS NULL)
BEGIN
--Update those items that we have enough information to update - parent has text in Hierarchy
UPDATE CHILD
SET CHILD.TreeNodeHierarchy = PARENT.TreeNodeHierarchy + CONVERT(nvarchar(10),CHILD.TreeNodeID) + '.'
FROM tblTreeNode AS CHILD
INNER JOIN tblTreeNode AS PARENT ON CHILD.ParentTreeNodeID = PARENT.TreeNodeID
WHERE (CHILD.TreeNodeHierarchy IS NULL) AND (PARENT.TreeNodeHierarchy IS NOT NULL)
END
FOR UPDATE TRIGGER:
--Only want to do something if Parent IDs were changed--Only want to do something if
IF UPDATE(ParentTreeNodeID)
BEGIN
--Update the changed items to reflect their new parents
UPDATE CHILD
SET CHILD.TreeNodeHierarchy = CASE WHEN PARENT.TreeNodeID IS NULL THEN '.' + CONVERT(nvarchar,CHILD.TreeNodeID) + '.' ELSE PARENT.TreeNodeHierarchy + CONVERT(nvarchar, CHILD.TreeNodeID) + '.' END
FROM tblTreeNode AS CHILD
INNER JOIN inserted AS I ON CHILD.TreeNodeID = I.TreeNodeID
LEFT JOIN tblTreeNode AS PARENT ON CHILD.ParentTreeNodeID = PARENT.TreeNodeID
--Now update any sub items of the changed rows if any exist
IF EXISTS (
SELECT *
FROM tblTreeNode
INNER JOIN deleted ON tblTreeNode.ParentTreeNodeID = deleted.TreeNodeID
)
UPDATE CHILD
SET CHILD.TreeNodeHierarchy = NEWPARENT.TreeNodeHierarchy + RIGHT(CHILD.TreeNodeHierarchy, LEN(CHILD.TreeNodeHierarchy) - LEN(OLDPARENT.TreeNodeHierarchy))
FROM tblTreeNode AS CHILD
INNER JOIN deleted AS OLDPARENT ON CHILD.TreeNodeHierarchy LIKE (OLDPARENT.TreeNodeHierarchy + '%')
INNER JOIN tblTreeNode AS NEWPARENT ON OLDPARENT.TreeNodeID = NEWPARENT.TreeNodeID
END
one more bit, a check constraint to prevent a circular reference in tree nodes:
FOR ALTERING:
ALTER TABLE [dbo].[tblTreeNode] WITH NOCHECK ADD CONSTRAINT [CK_tblTreeNode_TreeNodeHierarchy] CHECK
((charindex(('.' + convert(nvarchar(10),[TreeNodeID]) + '.'),[TreeNodeHierarchy],(charindex(('.' + convert(nvarchar(10),[TreeNodeID]) + '.'),[TreeNodeHierarchy]) + 1)) = 0))
What is a tree based system and how can you illustrate a tree-based system with a...
Please can I have the solution to this problem? Exercise 25.4 Briefly answer the following questions: 1. What is the difIerences between the WINDOW clause and the GROUP BY clause'? 2. Give an example query that cannot be expressed in SQL without the WINDOW clause but that can be expressed with the WINDOW clause. :3. What is the fTCLrne of a window in SQL:19997 4. Consider the fonowing simple GROUP BY query. SELECT FROM WHERE GROUP BY T.year, SUM (S.sales)...
7. Illustrate Kruskal's algorithm by giving detailed steps to find the minimum spanning tree for the following graph. You must explain the steps. 10 T,
What is SQL? How is SQL like an Access query? How is it different? Why might an accountant or auditor need to be familiar with SQL? Accounting Information System
What evidence can you provide to illustrate why crowdsourcing is able to reduce system development costs? And What are some challenges with crowdsourcing. Limit your response to 200 words.
Can you please upload the video to step by step with eclipse and mysql workbench.please................................ Part 1 Design the database using the ER approach. Then using Java and SQL, implement the following functionality: 1. Implement a button called “Initialize Database” . When a user clicks it, all necessary tables will be created (or recreated) automatically, with each table be populated with at least 10 tuples so that each query below will return some results. All students should use the database...
Can you please upload the video to step by step with eclipse and mysql workbench.please................................ Part 1 Design the database using the ER approach. Then using Java and SQL, implement the following functionality: 1. Implement a button called “Initialize Database” . When a user clicks it, all necessary tables will be created (or recreated) automatically, with each table be populated with at least 10 tuples so that each query below will return some results. All students should use the database...
1.If you do not have the world schema you can obtain the scripts to create the database schema from https://dev.mysql.com/doc/index-other.html Queries 1. Write a SQL subquery that shows the unique region names where the languages spoken are English, French or German. 2. Write a SQL query that shows the country name, region, language for the Caribbean region 3. Write a SQL query that shows the country name once and shows the number of cities in that country. The number of...
Describe how DSS(Decision Support System) can illustrate the tenets of decision making. That is, identify how systems can provide support in a manner that is prescribed by the decisions-making literature covered in this chapter. What changes would you make to an electronic book catalog system (such as you find in your library) to transform it into a good DSS. Describe the DSS you might provide to Sherlock Holmes. Be sure to describe all components of a DSS. Consider a company...
What are the challenges of a market-driven health care system and how can public and private insurances control premiums and co-payments? Please cite at least 2 references, thanks!
I need help in creating this phylogeny tree based on the data
provided. If you could please show me how it would look like and
why!