SQL SERVER – Implementation of relationships

1-1 Relationship: It is the most uncommon relationship.You might use one-one relationship to divide a table with many fields,to isolate a part of table  for security reasons can be implemented by referencing a primary key in one table to a primary key in other table.

1-m Relationship: It is the most common type of relationship, a record in Table A can have multiple matching records in B, where as record in B should have only one matching record in A. It can be implemented by Primary-foreign key relationships.

M-m Relationship: Database doesn’t support m-m relationships, The same can be implemented by introducing a third (Intermediate) table, which connects both, It is equivalent to having 2 1-m relationships with junction table.The Intermediate table contains a foreign key which is made up of Primary keys in I & II table. For Example the orders and products table have many-many relationship as defined, by creating 2 1-m relationships with Order details table.

There are five normal forms

1st NF: Eliminate the repeating groups in the table.

2nd NF: All Non-key columns should depend on entire primary key, but not on partial key.

3rd NF: Eliminate interdependencies between non-key attributes.

If the table should be in 3rd normal form, the minimum no. of tables should be one.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s