What is many-to-many relationship data structure?

-



 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. Denormalization 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.

Steps to structure data:


Junction tables is also called as an associative table or the link table. It is commonly used to structure the data to support join operations. This structure is allowed to represent the relationships between the tables. Each record in one table is associated with the multiple records in another table.

Step 1: Create two main tables:

Two tables new_student and new_courses are created and rows with values are inserted in both tables respectively. Below mentioned screenshots are provided you for the reference.

create table new_student(student_id int,name varchar(10));



desc new_student;
INSERT ALL
INTO new_student(student_id,name) VALUES (1,'Rahul')
INTO new_student(student_id,name) VALUES (2,'Rishav')
INTO new_student(student_id,name) VALUES (3,'Roshan')
SELECT * FROM dual;

select * from new_student;

create table new_courses(course_id int,course_name varchar2(10));
desc new_courses; 

INSERT ALL
INTO new_courses(course_id,course_name) VALUES (1,'HTML')
INTO new_courses(course_id,course_name) VALUES (2,'CSS')
INTO new_courses(course_id,course_name) VALUES (3,'JAVASCRIPT')
SELECT * FROM dual;


select * from new_courses;
Step 2: Create the junction table:

It will link the primary key of both the tables i.e new_student and new_courses.It will store the reference of the foreign keys.


create table new_enrollment(student_id int,course_id int);


desc new_enrollment;


INSERT ALL
INTO new_enrollment(student_id,course_id) VALUES (1,1)
INTO new_enrollment(student_id,course_id) VALUES (1,2)
INTO new_enrollment(student_id,course_id) VALUES (2,1)
INTO new_enrollment(student_id,course_id) VALUES (3,3)
SELECT * FROM dual;


select * from new_enrollment;

Here, Rahul which is having student_id as 1 are enrolled in two course_id 1 and 2 where course_id=1 represents HTML and course_id=2 represents CSS. Rishav which is having student_id 2 is enrolled in one course_id 1 where course_id=1 represents HTML. Roshan which is having student_id 3 is enrolled in one course_id 3 where course_id=3 represents JAVASCRIPT.

Step 3:KEY DESIGN:

Composite primary key: In the junction table, the combination of student_id and course_id acts as the composite primary keys and ensure that each combination is unique.

Foreign key:
Each column in the junction table new_enrollment  that references another table new_student and new_courses should be a foreign key.Student_id is a foreign key referencing student_id  in the new_student table.Course_id is a foreign key referencing course_id in the new_courses table.


ALTER TABLE new_student ADD CONSTRAINT new_student_pk PRIMARY KEY(student_id);


desc new_student;


ALTER TABLE new_courses ADD CONSTRAINT new_courses_pk PRIMARY KEY(course_id);



desc new_courses;


ALTER TABLE new_enrollment ADD CONSTRAINT new_enrollment_fk FOREIGN KEY(student_id) REFERENCES new_student(student_id); 

desc new_enrollment;




ALTER TABLE new_enrollment ADD CONSTRAINT new_enrollment_fk1 FOREIGN KEY(course_id) REFERENCES new_courses(course_id); 



desc new_enrollment;

STEP 4:SQL JOINS ARE USED FOR QUERIES:

From the below query, you can find out all the name of student who are enrolled in course name HTML.

select new_student.name 
FROM new_student
JOIN new_enrollment ON new_student.student_id=new_enrollment.student_id
JOIN new_courses ON new_enrollment.course_id=new_courses.course_id
where new_courses.course_name='HTML';


select new_courses.course_name 
FROM new_courses
JOIN new_enrollment ON new_courses.course_id=new_enrollment.course_id
JOIN new_student ON new_enrollment.student_id=new_student.student_id
where new_student.name='Roshan';



STEP 5: OPTIONAL additional columns are added in the junction table:

By adding date_of_enrollment and grade, one can track when the student is enrolled in the courses and what grade received in the course.

ALTER TABLE new_enrollment ADD date_of_Enrollment date;



ALTER TABLE new_enrollment ADD grade varchar2(1);



select * from new_enrollment;



UPDATE new_enrollment 
SET date_of_Enrollment =to_date('2025-03-01','yyyy-mm-dd')
WHERE course_id=1 and student_id=1;

UPDATE new_enrollment 
SET date_of_Enrollment =to_date('2025-03-02','yyyy-mm-dd')
WHERE course_id=1 and student_id=2;

UPDATE new_enrollment 
SET date_of_Enrollment =to_date('2025-03-03','yyyy-mm-dd')
WHERE course_id=2 and student_id=1;

UPDATE new_enrollment 
SET date_of_Enrollment =to_date('2025-03-04','yyyy-mm-dd')
WHERE course_id=3 and student_id=3;

select * from new_enrollment;


UPDATE new_enrollment 
SET grade ='A'
WHERE course_id=1 and student_id=1;

UPDATE new_enrollment 
SET grade ='B'
WHERE course_id=1 and student_id=2;

UPDATE new_enrollment 
SET grade ='C'
WHERE course_id=2 and student_id=1;

UPDATE new_enrollment 
SET grade ='D'
WHERE course_id=3 and student_id=3;



select * from new_enrollment;

Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"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. It merges the content of the two structurally compatible organized tables into single table.  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. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

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

-
Image
 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 ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more