-

Table Relationships


 Before moving to the topic “Table Relationship”, we would have a look on the definition of Database, Table, Column and Row, as these entities participate in relationships.

Database

Database is an organized collection of data for fast processing on these data. These data are arranged in such form that enables fast retrieval of information and manipulation on these data. In RDBMSs, database organizes its data in relational form only using tables, column and rows. We can define relationships between these tables.

Table

A table is a collection of related data in a structured form using columns and rows within a database. It organizes its data using vertical columns and horizontal rows, intersection of a column and a row is known as a cell which stores the actual value in a table. Each column in a table has a unique name and no two columns of a table can have same name. A table have a defined number of columns with any number of rows. Rows can be identified with the help of primary key, a subset of columns which can identify each row uniquely.
Example: Microsoft Excel sheet in a MS Excel workbook is like a large table which operates with columns and rows.

Column

A column in a table stores a specific category of information. It is also known attributes or fields. Name of columns are unique in a table and table can have a defined number of columns only. For example, Employee Table can have Name, Address and Contact as columns, to categorize the related information.

Row

Rows are also known as tuples or records and represents a single data item in a table. Each row in Employee table represents one employee.
Database,Table.Column,Row
Database,Table.Column,Row

Table Relationship

In RDBMS, relationship defines a connection between two tables by matching data in key columns. For example, in above image, you can see that an employee works in a department. Relationships depends on the cardinality, where cardinality is the degree of relationship which determines the relationship between two tables. It determines how an entity from one table is related to the single entity of another table. There are three types of relationships as:
  1. One to One (1 : 1)
  2. One to Many (1 : m)
  3. Many to Many (m : m)
Lets discuss each relationship in detail:

One to One Relationship

When one row from table T1 can have only one matching row in table T2, relationship between table T1 and table T2, is a one-to-one relationship. For example, one employee from “Employee Table” can have only one driving license detail in “Driving License Table”. This is an uncommon relationship, as such kind of information can easily be accommodated in one table. But, we can choose this in some scenario like; Splitting a wide table, in two tables, for maintenance and / or any security purpose.
One to one relationship
One to one relationship

One to Many Relationship

One to Many relationship is the most common relationship in use. In this relationship, a row from table T1 can have multiple matching rows in table T2. Primary key of table T1 is used as a foreign key in table T2 to relate the rows. For example, an Employee can work on multiple projects, which would yield multiple matching rows in “Project Detail” table for an employee from “Employee” Table.
One to many relationship
One to many relationship

Many to Many Relationship

A row from table T1 can have multiple matching rows in table T2 and a row from table T2 can have multiple matching rows in table T1. For example, an Employee from “Employee” table can opt for multiple policies from “Policy” table, and one policy from “Policy” table can be opted by multiple employees of “Employee” table. We need a bridge table to implement many-to-many relationship. This bridge table keeps references of primary keys from both the tables.
Many to many relationship
Many to many relationship

Comments

Post a Comment

Popular posts from this blog

-
Image
CLUSTERED INDEX VS NON CLUSTERED INDEX Clustered Index Only one clustered index can be there in a table because of the sorted order Sort the records and store them physically according to the order Data retrieval is faster than non-clustered indexes Do not need extra space to store logical structure A primary key by default creates a clustered index on it. Non Clustered Index There can be any number of non-clustered indexes in a table because of the number of unique keys we can create. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files Data insertion/update is faster than clustered index Use extra space to store logical structure A unique key/keys by dafault created an unclustered index on it. In Depth Explanation: Clustered Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered ind...
Read more
-
Image
SQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS What are JOINS? Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys. id first_name last_name movie_id 1 Adam Smith 1 2 Ravi Kumar 2 3 Susan Davidson 5 4 Jenny Adrianna 8 6 Lee Pong 10 id title category 1 ASSASSIN'S CREED: EMBERS Animations 2 Real Steel(2012) Animations 3 Alvin and the Chipmunks Animations 4 The Adventures of Tin Tin Animations 5 Safe (2012) Action 6 Safe House(2012) Action 7 GIA 18+ 8 Deadline 2009 18+ 9 The Dirty Picture 18+ 10 Marley and me Romance   Types of joins Cross JOIN Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another. In other words it gives us combinations of each row of first table with all records in second table. Suppose we want to get all member records against all the movie records, we can use the script shown below to ge...
Read more
-
Image
COMMON TABLE EXPRESSIONS(cte): It is used best used to extract the hierarchical data  and it is best explained with an example: Problem Many organizations have some type of hierarchy for business processes. When it comes to large organizations, the hierarchy can get very complex and large, so building a hierarchy in a RDBMS is a tedious task. We have to create views, cursors and so on, but using a CTE in SQL Server is a better solution to retrieve hierarchy-based data. Solution Common Table Expressions (CTE) have two types, recursive and non-recursive. We will see how the recursive CTE works with examples. A recursive CTE can be explained in three parts: Anchor Query: This is the first statement which is executed. This query will give the base data for the CTE. Separator: This is the middle part where in we generally use a UNION ALL and few more operators. Recursive Query: This is the main part, this is the CTE query which refers to the same CTE by recursion. ...
Read more