Introduction to Database Relationships

|
Database

Database Relationship

A Database Relationship describes a relation between two tables in a relational database system. Usually that association between the tables is made when the second table has a foreign key that relates to a primary key in the first table. The data can be retrieved from related tables using join statements. The database relationships can be one of the following:

  • One-to-one: Each table has only one record on each side of the relationship. That being said, a primary key can only relate to a maximum one record in the related table.
  • One-to-many: The first table’s primary key can relate to none, one record or many records in the related table.
  • Many-to-many: Each record from both tables can have a relationship to none or many records in the related table. Usually a many-to-many relationship requires a third table called linking table or associate table since relational systems cannot make this associations directly.

Database diagrams

    As a graphical solution to show the database structure and relations between the tables, the database diagrams are an important part of the database design and documentation. Also known as Entity Relationship Diagram (ERD), the database diagram is a type of flowchart usually made with the UML (Unified Modeling Language) to standardize the database design.

Database diagrams have three main components:

  • Entity: The defined object that can have data stored about it. Usually represented on the diagram by a rectangle. It can be grouped by type, set and category.
  • Relationship: This represents how the entities related between each other. Relationships are usually represented on the diagram  as diamonds or even labels on the connecting lines.
  • Attribute: An entity’s property, represented on the diagram usually in an oval or circle shape.

To properly draw a basic however complete database diagram the database designer has first to define the purpose and scope of what is being modeled as a database. Then identify the entities and start drawing them in rectangles and labeling them as nouns. With the entities defined,  the relationship can be determined between them. To represent these relationships, lines should be drawn between the entities with a diamond shaped label to describe the relationship. It is good practice to put the relationship cardinality in the line, so whether is one-to-one(1-1), one-to-many(1-N) or many-to-many (N-N). To add an extra layer of details, key attributes should be added to the entities usually shown as oval shapes. 

The Anatomy of a Database Relationship

    Relation is a mathematical term ( specifically in the mathematics logical foundation). It means the relation between things. Most mathematical theory texts exemplify relationships involving pairs, or couples. However with a database it is usual to have relation between three (triples) or any number of things (n-tuples).

Since table, rows and columns refers to a kind of a structured picture, it is not the best term to use as definition for the anatomical parts of a relation. Instead it is used the terms proposed by E.F. Codd in 1969:

  • Relation: What it’s commonly called a table.
  • (n-)tuple: It would be each row of a table. 
  • Attribute: Each column of a table. It has an attribute name not repeatable on the same relation (table), and each one has an attribute value not repeatable on the same tuple (row).
  • Cardinality: The number of tuples in a relation (the number of rows).
  • Degree: The number of attributes of a relation.  In figure 1 we are seeing a degree 3 relation, or 3-tuple relation.
  • Heading: The set of attributes.
  • Body: The set of tuples.

Figure 1

Anatomy of  a Relation

From An Introduction to Relational Database Theory(4th ed p. 21), by Hugh Darwen, 2014.

Some Tool to Build a Database Diagram

    Several tools are available to assist in the database design and making the database diagram. Here are listed some of them. Note that there are other tools, and it is not in the scope of this paper that one is better than the other. It is also important to realise that some of that tools are has a limited free tier, however these offers may change in the future:

References


Post’s featured image credit: https://www.bloggersinsights.com/images/embed_image/hr7v9dbms.jpg

Paper by Luis Fernando Maschietto Junior

Leave a Reply

Your email address will not be published. Required fields are marked *