Relationship (database)
Introduction
A relational database is a type of database that organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row. Rows are also called records or tuples. Columns are also called attributes. Generally, each table/relation represents one "entity type" (such as customer or product). The rows represent instances of that type of entity and the columns representing values attributed to that instance.
History
The concept of a relational database originated with Edgar F. Codd, a British computer scientist working at IBM. In his seminal 1970 paper "A Relational Model of Data for Large Shared Data Banks," Codd proposed shifting from storing data in hierarchical or navigational structures to organizing data in tables containing rows and columns.
Key Concepts
Tables
A table is a collection of related data held in a structured format within a database. It consists of columns, and rows. In relational databases, and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.
Fields
Each table is made up of one or more fields, which store the database's raw data. A field is a column in a table that is designed to maintain specific information about every record in the table.
Records
A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.
Relational Keys
A key is a single or combination of multiple fields in a table. It is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views.
Types of Keys
Primary Key
A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
Foreign Key
A foreign key is a column or a set of columns in one table that is used to link two tables together. The Foreign Key in one table points to the Primary Key in another table.
Composite Key
A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually, the columns do not guarantee uniqueness.
Relationships
In the context of relational databases, a relationship is established between two database tables when one table uses a foreign key that references the primary key of another table. This is the basic concept behind the term "relational database."
One-to-One Relationships
A one-to-one relationship exists when one row in a table may be linked with only one row in another table and vice versa.
One-to-Many Relationships
A one-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A.
Many-to-Many Relationships
A many-to-many relationship exists when multiple rows in table A may be linked with multiple rows in table B and vice versa.
Normalization
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
Conclusion
Relational databases have been a prevalent technology for decades. They provide a way to organize data into tables and establish links between these tables based on relationships. These relationships are established using primary and foreign keys.