Skip to main content

How should data be structured to support join operations in many to many relationships? Oracle sql plsql interview question for fresher and experienced.

 Many to Many Relationships in join operations: 


Joining tables or bridging tables are done on the basis of many to many relationships which are used to store records by every possible combination of records of both the tables. In order to handle many to many relationships is quite complex than one to one relationship and one to many relationships. It occurs when the multiple records in the table are related to the multiple records in another table. Students which are enrolling into the multiple courses are example of this type, which are part of the school or college database.

Understanding of this concept are useful for designing scalable and flexible databases. In order to fulfill the various application requirements, the complex data interaction is required. This concept is important in the relational database management system. Problems or challenges occurred like database schema becoming too complex or maintaining the performance on the consistent basis. In ecommerce platforms, it is extensively used. Single order can contain unique order id and contains the list of products. Each products available in the stock can be used in the multiple orders can have different order ids.Double joins is applied to the bridge tables for retrieving the elements.

Complexity may occur in the database schema design:
This concept can complicate the database schema design. Junction table is introduced to handle this issue which aid in adding another layer of complexity. Foreign key used in the junction table are correctly referenced with the primary key used in the related tables. Maintaining data integrity becomes quite challenging. Any changes like updation and deletion in the primary key table must be reflecting in the junction table in order to avoid data inconsistencies or orphaned records. Careful, well planning and time to time maintenance activities are required.

Performance Overhead:
It can bring out the performance overhead issue. Joins and queries are using the junction tables which are resource intensive and expands the data volume. Each join operation will slow down the query execution time. It requires the database to match rows from the related tables. Complex queries with multiple joins can cause increase in performance overhead. Due to evolving number of joins in the particular query can cause performance issue by passing load on the database server to retrieve the required and excepted results to bring out the slowness in the execution time.

Data redundancy:
Junction table can contain the duplicated data which leads to the increase in the storage requirements. Junction table contains the number of relationships increase between the two or more rows which cause increase in its size. Data management may become difficult task due to data redundancy. Duplicated data needs to be consistent across the database. If any changes made to the rows of the columns in the junction table can occur burden of maintenance.

Use of Junction tables:
It acts as intermediaries between the related entities which holds the foreign key of the one entity that reference the primary key of another entity. In order to retrieve the necessary data, you can perform the joins between the junction table and related tables. This can increase your schema design. If the two table names are student and marks respectively, then the junction table will be appearing as like student_marks.

Query Optimization:
It is useful for maintaining the performance. Indexing on the foreign key columns in the junction table can increase the speed of the query execution. When indexing is applied on the columns, you enable or allows the database to retrieve those records or rows which matches the required condition and reducing the time of the joins.Denormalisation can improve the performance by storing the redundant data by avoiding complex joins. Drawback of denormalization can lead to data inconsistency if it is not managed properly. Changes in one table must be reflecting in the multiple places which fulfill the application needs. Instead, normalized schema is preferred to store the redundant data in the single table by using the multiple tables and junction table.

No SQL Database:

It provides an alternative approach to handle this without the use of junction table. It is sub-divided into document oriented and graph databases.
Document oriented databases:

Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea,IBM and Mphasis etc" INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results.In other words,multiple select statements of similar column name in same order and having same data type to produce the unified result.In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. Column must be of the same datatype for these operations of one select statement with another select statements. Columns must be in the same order in this set operators in the multiple select statements. Same number of co...
Post a Comment
Read more

Difference between decode and case oracle sql plsql interview question and answer

" This is the most accepted question for PLSQL developer role.It has been asked in many interviews like Mphasis and Kyndryl. "  INTRODUCTION: Case was came after oracle database version 8.1.6 whereas decode was came before oracle database version 8.1.6 .Decode and case statements in an oracle work or acts like if then elsif conditional statements. if sal='2000000' then 'General Manager' elsif sal='3000000' then 'Senior Manager' elsif sal='1200000' then 'Manager' else 'Employee' end if; create table student_details(Student_RollNo Number,Student_Name Varchar2(20),student_marks Number,student_result varchar2(1)); insert into student_details values (100,'Rahul',90,'P'); insert into student_details values (101,'Rakesh',30,'F'); insert into student_details values (102,'Ram',60,'P'); insert into student_details values (103,'Rohan',10,'F'); insert into student_detai...
Post a Comment
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Post a Comment
Read more