.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Types of Relationship in SQL

Posted By:Narayanan       Posted Date: August 13, 2012    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In this Article we learn How to use relationship in SQL with an Example.First ,we must know what is relationship in SQL.
 

What is mean by Relationship?
    Relationship is called as "Data Relationship"information flows from One list to another. SQL supports exchange data among themselves.
How will it work?
   Using Keys. There are Two  Keys in SQL .
1)PrimaryKey. 2)ForeignKey.
What is mean by Primary Key and Foreign Key?
Primary Key:
    Uniquely Identifies Each Record in a Database Table.
     It can't contain Null value.
    It's must contain Unique Keys.
   Each table should have a Primary Key and Only One Primary allowed in the Each Table.
Foreign Key:
    A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
   There are Three Types of Relationship :
1)One to One Relationship.
2)One to Many Relationship.
3)Many to Many Relationship.
First we will discuss about One to One Relationship:
 1)One to One Relationship:
      Split big tables into small pieces without loosing the performance of a flat table model.  
For Eg:
     In Our Example , We have Two Tables. Table names are tbl_EmployeePersonnelDetail,tbl_EmployeeEductionalDetails.  
In this two Tables Primary Keys are employeeId.
Structure of tbl_EmployeePersonnelDetail
Column Name   DataType
employeeId   varchar(20)
firstname      varchar(50)
lastname       varchar(50)
Structure of tbl_EmployeeEductionalDetails
Column Name   DataType
employeeId   varchar(20)
schoolName   varchar(50)
schoolMark    int
CollegeName    varchar(50)
CollegeMark    int
Query:
Insert into tbl_EmployeePersonnelDetail(employeeId, firstname,lastname)values('EMP001','Lakshmi','Narayanan')
Insert into tbl_EmployeeEductionalDetails(employeeId, schoolName,schoolMark,CollegeName,CollegeMark)values('EMP001','Govt HR Sec School','65','Anna University','75')
Insert into tbl_EmployeePersonnelDetail(employeeId, firstname,lastname)values('EMP002','Siva','Pandiyan')
Insert into tbl_EmployeeEductionalDetails(employeeId, schoolName,schoolMark,CollegeName,CollegeMark)values('EMP002','Govt HR Sec School','75','Anna University','65')
Explaination:
    Notice that now there is a field named “employeeId” in the EmployeePersonnelDetail table, that refers to the matching record in the EmployeeEductionalDetails table. This is

called a “Foreign Key” and it is used for all kinds of database relationships
2)One to Many Relationship:
      This is a Most Commonly used type of Relationship. One customer Orders many Items.Many Items buys one Customer. In this Condition we use One To Many Relationship.
Eg:
Table Names are tbl_OrderHeader,tbl_OrderDetails.
Structure of  tbl_OrderHeader:
Column Name       DataType
OrderId       varchar(20)  
OrderDate     DateTime
CustomerId           varchar(20)
Structure of  tbl_OrderDetails:
Column Name       DataType
OrderId       varchar(20)  
ItemCode     varchar(20)
ItemName     varchar(100)
UOMCode             varchar(20)
Qty                     int
Query:
Insert into tbl_OrderHeader(OrderId,OrderDate,CustomerId)values('ORD001','2012-08-13 00:00:00:000','Cust001')
Insert into tbl_OrderDetails(OrderId,ItemCode,ItemName,UOMCode,Qty)values('ORD001','ITM001','Raw Rice','Kg','10')
Insert into tbl_OrderDetails(OrderId,ItemCode,ItemName,UOMCode,Qty)values('ORD001','ITM002',Sugar','Kg','10')
Explaination:
    Notice that now there is a field named “OrderId” in the tbl_OrderHeader table, that refers to the matching each record in the tbl_OrderDetails table.
 3)Many to Many Relationship:
you may need multiple instances on both sides of the relationship.
Eg:
We have Three Tables for Using Many to Many Relationship.
Table names :tbl_Product,tbl_InvoiceDetails,tbl_Invoice
Structure of  tbl_Product:
Column Name       DataType
ProductId       varchar(20)
ProductName     varchar(100)
Structure of  tbl_InvoiceDetails:
Column Name       DataType
ProductId       varchar(20)  
InvoiceId     varchar(20)
Structure of  tbl_Invoice
InvoiceId             varchar(20)
InvoiceDate           DateTime
Query:
Insert into tbl_Product(ProductId,Productname)values('PRD001','Mouse')
Insert into tbl_Product(ProductId,Productname)values('PRD002','CPU Board')
Insert into tbl_Product(ProductId,Productname)values('PRD003','Monitor')
Insert into tbl_Product(ProductId,Productname)values('PRD004','Keyboard')
Insert into tbl_Invoice(InvoiceId,InvoiceDate)values('Inv001','2012-08-13 00:00:00:000')
Insert into tbl_Invoice(InvoiceId,InvoiceDate)values('Inv002','2012-08-13 00:00:00:000')
Insert into tbl_Invoice(InvoiceId,InvoiceDate)values('Inv003','2012-08-13 00:00:00:000')
Insert into tbl_InvoiceDetails(ProductId,InvoiceId)values('PRD001','Inv001')
Insert into tbl_InvoiceDetails(ProductId,InvoiceId)values('PRD001','Inv002')
Insert into tbl_InvoiceDetails(ProductId,InvoiceId)values('PRD002','Inv001')
Insert into tbl_InvoiceDetails(ProductId,InvoiceId)values('PRD003','Inv001')
Insert into tbl_InvoiceDetails(ProductId,InvoiceId)values('PRD004','Inv003')
Explaination:
    A product can appear on many invoices and an invoice can have many products. The relationship between products and invoices is known as a many-to-many relationship.
Conclution:
     Thanks for Reading this Article. If you want to any feedback.Please send a mail to thisiD:naraayanan1983@gmail.com.Thanks


 Subscribe to Articles

     

Further Readings:

Responses
Author: Ravi Ranjan Kumar         Company URL: http://www.dotnetspark.com
Posted Date: August 31, 2012

Please format your code and have give nice glimpse of article.

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend