DBMS Questions Asked in Companies

Is there any other way to to store tree structure in a relational database

by

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 … Read more

How will you find out the superior for an employee whose emp_id is 3

by

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 … Read more

How will you represent a hierarchical structure shown below in a relational database? or How will you store a tree data structure into DB tables?

by

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 … Read more

What are the two types of indexes and explain them in detail? Orwhat’s the difference between clustered and non-clustered indexes

by

There are basically two types of indexes:- Clustered Indexes. Non-Clustered Indexes. In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data. The correct answer to your question would be the difference between clustered and non-clustered indexes. … Read more

I have a table which has lot of inserts, is it a good database designto create indexes on that table

by

Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen? All indexing fundamentals in database use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance. Creates a new page to balance the tree.Shuffle and move the data to pages. So if your … Read more