The hierarchical  data is an example of the composite design pattern. The entity relationship diagrams (aka ERdiagram) are used to represent logical and physical relationships between the database tables. The diagram below shows how the table can be designed to store tree data by maintaining the adjacency information via superior_emp_id.   As you can see the “superior_emp_id” is a foreign key that points to the emp_id in the same table. So, Peter has null as he has no superiors. John and Amanda points to  Peter who is their…

Read More Read More

There is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization. Benefits of normalizing your database include: Avoiding repetitive entries Reducing required storage space Preventing the need to restructure existing tables to accommodate new data. Increased speed and flexibility of queries, sorts, and summaries. Following are the three normal forms: First Normal Form For a table to be in…

Read More Read More

SQL statements are good for set at a time operation. So it is good at handling set of data. But there are scenarios where we want to update row depending on certain criteria. we will loop through all rows and update data accordingly. There’s where cursors come in to picture. Cursors in database management systems (DBMS) are programming constructs used to retrieve and manipulate data from a result set, typically within a procedural language like SQL or PL/SQL. They enable…

Read More Read More

You can use a self-join to find the manager of an employee whose emp_id is 3 Select e.emp_id,e.emp_name, title Fromemployee e, employee s where e.superior_emp_id = s.employee_idand e.emp_id = 3 This should return: 1, Peter, CIO To find the superior for an employee with emp_id 3 in a database management system (DBMS), you would typically execute a SQL query that retrieves the superior’s information based on the hierarchical relationship stored in the database. Assuming there’s a table named “employees” with columns like…

Read More Read More

Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design – to enhance performance.The sacrifice to performance is that you increase redundancy in database. Denormalization in database management refers to the process of intentionally introducing redundancy into a database design, usually for performance reasons. This involves adding redundant data to one or more tables within the database to help optimize read performance, simplify queries, or reduce…

Read More Read More

Group by” clause group similar data so that aggregate values can be derived. The “Group by” clause in a database management system (DBMS) is used to group rows that have the same values into summary rows or groups based on one or more columns. It is commonly used with aggregate functions like COUNT, SUM, AVG, MAX, or MIN to perform calculations on each group of rows. Essentially, it helps in categorizing data and applying aggregate functions to each category.

Yes, it can be done using the “modified preorder tree traversal” as described below.As shown in the previous diagram above, each node is marked with a left and right numbers using a modified preorder traversalas shown above. This can be represented in a database table as shown below. Yes, there are alternative methods for storing tree structures in a relational database. One common approach is called the “adjacency list model,” where each node in the tree has a reference to its parent node. Another approach is the…

Read More Read More

In fourth normal form it should not contain two or more independent multi-v about an entity and it should satisfy “Third Normal form”. Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy. Sure, I’d be happy to explain Fourth Normal Form (4NF) and Fifth Normal Form (5NF). Fourth Normal Form (4NF): 4NF is a level of database normalization that builds upon the concepts of Third Normal…

Read More Read More

A query nested inside a SELECT statement is known as a subquery and is an alternative to complex join statements. A subquery combines data from multiple tables and returns results that are inserted into the WHERE condition of the main query. A subquery is always enclosed within parentheses and returns a column. A subquery can also be referred to as an inner query and the main query as an outer query. JOIN gives better performance than a subquery when you…

Read More Read More

As you can see the numbers indicate the relationship between each node. All left values greater than 6 and right values less than 11 are descendants of  6-11 (i.e Id: 3 Amanda). Now If you want to extract out the 2-6 sub-tree for Amanda. What SQL query you will write? SELECT * FROM employee WHERE left_val BETWEEN 6 and 11 ORDER BY left_val ASC; To extract the sub-tree rooted at the node with ID 2-6 for Amanda, you would typically…

Read More Read More