Types of relations in RDBMS

 

Learn SQL: Types of relations

One of the most important things in databases is to understand the types of relations in the databases. That stands for both – a process of designing a database model as well as when you’re analyzing your data. Understanding these relations is somehow natural and not so complex but is still essential in the database theory (and practice).

Data model

In the previous article, Learn SQL: SQL Scripts, we’ve extended the data model we’ve used so far by adding a few new tables and filling them with the data. “Very nice. I like.”. That is the model in the picture below:

While the model itself has only 6 tables (real-life models could have hundreds of tables), it contains the most common rules we’ll meet in many other models as well. This also stands for types of relations between tables. Without much effort, you can easily notice that each table is connected/related to another table with exactly one line (foreign key). The primary key from one table (e.g. employee.id) is related to the value from another table (e.g. call.employee_id). We’ll use this relation when we need data from both these tables, mostly when we’re writing select queries. The question remains on how to relate two tables. Even between two lines (relations), we could have some minor differences. We’ll discuss that now.

Types of relations

There are 3 different types of relations in the database:

• one-to-one
• one-to-many, and
• many-to-many

Although they are different, they are represented in (almost) the same manner in the databases and that is the line between the two tables. So, what’s different? We’ll explain each of these relations types separately and comment on what is their actual purpose.

One-to-many relation

The first of our 3 types of relations, we’ll start with is one-to-many. The reason for that is that it’s the most commonly used and the remaining two are “subtypes” of this one. Let’s start with a real-life problem.

Example

Imagine that we want to store a list of all our customers in the database. For each customer, we also want to store the city where this customer is located, and we know that the customer will be in exactly one city.

This the typical example of one-to-many relation and this is how we solved it in our model:

We simply established a relation from the city.id to customer.city_id. And this works, because the customer can be only in one city and the city could have many different customers located in it.

When you want to determine the nature of the relation you need to establish between two tables just do this. In our example – For one city, we could have many different customers located in it. And the other way around – For one customer, we can have only one city it’s located in.

So, how to choose between these 3 different types of relations? If you said the word “many” only once, then this is one-to-many relation. If you would use the word “many” two times, the relation would be many-to-many. And if you wouldn’t use it at all, then it would be one-to-one.

• Tip: One-to-many relation is resolved in such a manner that you add an attribute to the table that “is related to” word “many” and establish the relationship between this attribute and id of the original table.

In our case, we’ve added city_id to the customer table and related it to city.id attribute.

Now, let’s write 2 simple SQL SELECT statements and check if this is really true:

1 2 3 4 5

SELECT * FROM city;   SELECT * FROM customer;

We can easily notice few things:

• Not all cites were used (only these with ids 1, 3 and 4 were)
• Each customer had exactly one city it belongs to (customer.city_id)

Now, we’ll write 3 more queries and join these two tables. Queries are:

1 2 3 4 5 6 7 8 9 10 11

SELECT * FROM customer INNER JOIN city ON customer.city_id = city.id;   SELECT * FROM customer LEFT JOIN city ON customer.city_id = city.id;   SELECT * FROM city LEFT JOIN customer ON customer.city_id = city.id;

The results returned are in the picture below:

Let’s shortly comment on these results.

The first query (using INNER JOIN) returned only rows where cities and customers had a pair. Since we had 4 rows for customers and all 4 had related city defined, the final result also has 4 rows.

The second query (customer LEFT JOIN city) returned the same result as the first one. That happened because all customers had related city defined. In case some customers wouldn’t have city_id defined (NULL), these customers would be included in this result too.

The last query (city LEFT JOIN customer) returns more rows than the previous two queries. It returns all 4 rows they’ve returned, but also returns 3 more rows for cities where we have no customers. And that’s completely ok because if we wrote query this way, we obviously wanted to point to that fact.

Many-to-many relation

The second out of three types of relations is a many-to-many type. This type is used when both tables could have multiple rows on the other side. Let’s see an example.

Example

We need to store calls between employees and customers.

One employee, during the time, could call many customers. Also, one customer, during the time, could receive calls from many employees.

Notice that we’ve mentioned the word “many” two times. This is the signal we need to resolve this using many-to-many relation (out of 3 types of relations we have on disposal). To solve it we’ll:

• Add a table between tables employee and customer
• Add foreign keys (employee_id & customer_id) to that new table (call)

Now, when we look from the employee perspective, one employee could make many (multiple) calls. On the other hand, one customer could be related to many (multiple) calls. Therefore, many-to-many relation is implemented with adding a new table and one-to-many relations from both sides.

Let’s peek into the contents of these three tables now. We’ll use simple queries:

1 2 3 4 5 6 7 8

SELECT * FROM employee;   SELECT * FROM call;   SELECT * FROM customer;

The result is in the picture below:

You can easily notice that the table call has attributes employee_id related to the employee.id and customer_id related to the customer.id. Since they are foreign keys, they hold only values from the set defined in the referenced tables (employee & customer).

Actually, we have 1 more foreign key here and that is the call.call_outcome_id. The relation between tables call and call_outcome is one-to-many. This means that the table call actually relates three tables – customer, employee, and call_outcome.

The attribute call.call_outcome_id could contain NULL value (e.g. when the call starts, we still don’t know the outcome and it shall be defined later). That is the reason why, on the relation line, close to the table call_outcome, you can see a little circle (representing “zero”). Other one-to-many relations have a vertical line (representing “one”).

One-to-one relation

Compared to previously mentioned types of relations, this one is really rarely used. Let’s go with an example.

Example

In the database, we want to store employees, but also their valid identity cards. We’re not interested in storing any other types of documents or identity cards that were previously valid, so we need exactly 1 (or none) identity card for 1 employee.

Let’s check this truly is a one-to-one relation. We’ve been given these rules: One employee could have only one valid identity card in our system. One identity card could belong to only one employee. We haven’t used the word “many”, so this can’t be any type of relation including the word “many”.

We could do two things here:

• Store identity card details in the employee table. This is how it’s usually done and the reason for doing it differently (as mentioned below) is some kind of exception
• Store identity card details in a separate table and relate these two tables with a foreign key. But that foreign key (identity_card.employee_id), referencing employee.id, should, at the same time, be the primary key of the identity_card table. This way we could have only 1 record per employee

We could decide to go with the second option if we want:

• To keep identity card data separately because we want to keep the model clear and follow the same logic in the whole model (each entity from the real-world has its’ own table in the data model)
• Maybe not all employees will have identity cards, so we’ll spare some storage space this way

Please notice that one-to-one was also implemented in the same manner as one-to-many (1 relation) but with the additional condition (the foreign key is also the primary key)

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

A view is created with the CREATE VIEW statement. 

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: A view always shows up-to-date data! The database engine recreates the view, every time a user queries it.

SQL CREATE VIEW Examples

The following SQL creates a view that shows all customers from Brazil:

Example

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';  

What is a query in SQL?

A query is a question or inquiry about a set of data. We use Structured Query Language (SQL) to retrieve meaningful and relevant information from databases. When building a structure, we pull data from tables and fields. The fields are columns in the database table, while the actual data makes up the rows.

SQL Create Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

---

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

The INDEX is used to create and retrieve data from the database very quickly. An Index can be created by using a single or group of columns in a table. When the index is created, it is assigned a ROWID for each row before it sorts out the data.

Proper indexes are good for performance in large databases, but you need to be careful while creating an index. A Selection of fields depends on what you are using in your SQL queries.

Example

For example, the following SQL syntax creates a new table called CUSTOMERS and adds five columns in it.

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, you can create an index on a single or multiple columns using the syntax given below.

CREATE INDEX index_name
   ON table_name ( column1, column2.....);

To create an INDEX on the AGE column, to optimize the search on customers for a specific age, you can use the follow SQL syntax which is given below −

CREATE INDEX idx_age
   ON CUSTOMERS ( AGE );

DROP an INDEX Constraint

To drop an INDEX constraint, use the following SQL syntax.

ALTER TABLE CUSTOMERS
   DROP INDEX idx_age;

What is a table?
The data in an RDBMS is stored in database objects which are called as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows.
Remember, a table is the most common and simplest form of data storage in a relational database. The following program is an example of a CUSTOMERS table −
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
What is a field?
Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.
A field is a column in a table that is designed to maintain specific information about every record in the table.
What is a Record or a Row?
A record is also called as a row of data is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table −
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+
A record is a horizontal entity in a table.
What is a column?
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would be as shown below −
+-----------+
| ADDRESS   |
+-----------+
| Ahmedabad |
| Delhi     |
| Kota      |
| Mumbai    |
| Bhopal    |
| MP        |
| Indore    |
+----+------+
What is a NULL value?
A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is the one that has been left blank during a record creation.
SQL Constraints
Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table.
Following are some of the most commonly used constraints available in SQL −
NOT NULL Constraint − Ensures that a column cannot have a NULL value.
DEFAULT Constraint − Provides a default value for a column when none is specified.
UNIQUE Constraint − Ensures that all the values in a column are different.
PRIMARY Key − Uniquely identifies each row/record in a database table.
FOREIGN Key − Uniquely identifies a row/record in any another database table.
CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX − Used to create and retrieve data from the database very quickly.
Data Integrity
The following categories of data integrity exist with each RDBMS −
Entity Integrity − There are no duplicate rows in a table.
Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values.
Referential integrity − Rows cannot be deleted, which are used by other records.
User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity.
Database Normalization
Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process −
Eliminating redundant data, for example, storing the same data in more than one table.
Ensuring data dependencies make sense.
Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.
Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form.
It is your choice to take it further and go to the fourth normal form, fifth normal form and so on, but in general, the third normal form is more than enough.
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)