Page 237 - IT_V1.0_Class10
P. 237

  One-to-Many: This is one of the most common types of relationship between the tables in a database.
                   In this type of relationship, “one record of one table is related to multiple records of another table.” It is
                   represented as a 1:n relationship.
                   Let us understand the concept of One-to-Many relationship with the help of example.

                                        Table: Department

                                               DeptID          DeptName            Location
                                           (Primary Key)
                                                  1              Finance            Mumbai
                                                  2             Marketing           Ambala
                                                  3               Sales             Chennai
                                        Table: Employee

                                            EmployeeID            Name              DeptID
                                           (Primary Key)                         (Foreign Key)
                                                101               Rohit                1
                                                102              Mayank                1
                                                103                John                2
                                                104               Divya                3
                                                105               Shefali              2
                   In the given relationship, each department can have many employees, but each employee belongs to only
                   one department. So, the relationship between Department and Employee tables is One-to-Many.

                   Many to Many: In this relationship, “Multiple records of one table is related to multiple records of another
                   table”. Generally, this type of relationship is set when certain records must be saved more than once in
                   both the related tables. It is represented as a n:n relationship.
                                         Table: Teacher
                                                Tid              Tname
                                                101             Mr. Rahul
                                                102            Ms. Anamika

                                         Table: Subject
                                                Sid              Sname                Tid
                                                S101             Accounts             101
                                                S102               Eco                102
                                                S103               B.st               102
                                                S104              Math                101
                                                S105               B.st               101

                 In the above tables ONE TEACHER can teach MANY SUBJECTS. ONE SUBJECT is taught by MANY TEACHERS.
                 Similarly, a shopkeeper may sell multiple products to multiple customers. So Many-to-Many relationship
                 exists between a product and a customer.

                 Advantages of Relating Tables in a Database

                 There are various advantages of relating tables in a database. Few of them are as given below:
                   Relationships  help reduce  data  redundancy  and ensure data  consistency by maintaining referential
                   integrity, which prevents discrepancies when data is deleted.




                                                                                      Working with Multiple Tables   235
   232   233   234   235   236   237   238   239   240   241   242