Skip to content Skip to main navigation Skip to footer

Relational Database

This tutorial talks about the relational database, primary keys, foreign keys, and data integrity. This will help with the conceptual to physical mapping later in this category, and with the transition to SQL later.

The word “relation” is an abstract mathematical term used in set theory. A mathematical relation has the same logical properties as a table in a database. This is the origin of the term “relational database”, i.e. a database consisting of a set of relations (i.e., tables).

Relational Database

Objectives

This tutorial covers the following objectives:

  • What a relational database is
  • Define a primary key
  • Define a foreign key
  • Define a column-integrity rule
  • Identify row, column, primary key, unique key, and foreign key elements given a diagram of a table containing these elements
  • Identify violations of data-integrity rules

Purpose

  • The conceptual data model will be transformed into a relational database design.
  • This means that our entities, attributes, relationships, and unique identifiers will be translated into objects in a relational database.
  • Compare this to a clothing designer who is taking his design from paper and implementing it with fabric.
  • The designer needs to understand how to sew the designs just like you will need to understand the structure of relational database objects.

Relational Database Illustrated

  • A relational database is a database that is seen by the user as a collection of two-dimensional tables, each containing rows and columns.
  • The table below contains employee data.
Relational Database Illustrated

Each row of data describes an employee. Each column is an attribute of that employee. If we wanted to find out the last name and department number of employee number 210, we would need to access the third row in the table, and then find the values for first_name and department_no for that row.

But how do we find the correct row in the first place? Would we have to go through the whole table and look at every row?

Answer: No.

Language to Access Data

  • Structured query language (SQL) allows us to access data in relational databases in an efficient way.
  • Instead of manually searching through each row to find the record for employee number 200, we use the following SQL statement:
SELECT last_name, department_id
FROM employees
WHERE employee_id = 200;

You can see the result of this statement below.

SQL Query Illustrated

SQL Query Illustrated

Specific SQL Query

  • To find all the employees in department number 90, we write a different SQL statement:
SELECT *
FROM employees
WHERE department_id = 90;
  • The “*” after SELECT means we want all the columns in the table.
  • Again, you can see the result below.
Specific SQL Query

SQL allows us to access the whole table or just parts of the table, depending on what comes after SELECT and what is specified in the WHERE clause.

Primary Key in a Relational Database

  • A primary key (PK) is a column or set of columns that uniquely identifies each row in a table.
Multiple Column Primary Key
Single Column Primary Key

Notice that in ACCOUNTS, BANK_NO is not unique and ACCT_NO is not unique. However, the combination of BANK_NO and ACCT_NO is unique.

Primary Key Candidates

  • A table can have more than one column, or combinations of columns, that could serve as the table’s primary key.
  • Each column, or combination of columns, is called a “candidate” key because it could be selected for use as the primary key.
Primary Key Candidates

What makes EMPLOYEE_ID and PAYROLL_ID good candidates for the primary key?

Answer: They are both unique and not null.

Choose a Candidate Key

  • Select one candidate key to be the primary key for the table.
  • The other candidates become alternate keys (or unique keys).
Choose a Candidate Key

Why having alternate or unique keys can be useful?

Answer: It’s another way to locate a record. If you forget your employee ID, but know your payroll ID (or have a payroll stub with the ID on it), then you can still access your employee record.

Foreign Key in a Relational Database

  • A foreign key (FK) is a column, or combination of columns, in one table that contains values that match the primary key value in another table.
Foreign Key

If the foreign key (DEPARTMENT_ID) in EMPLOYEES has a value of 10, then there needs to be a row in DEPARTMENTS with a DEPARTMENT_ID of 10. Otherwise, it is a violation of referential integrity.

Foreign Key Rules

  • If a primary key is composed of one or more foreign keys, the FK value cannot be NULL.
Foreign Key Rules

Notice that in the example, SHELF_NO is part of the primary key of BOOKS. It is also a foreign key to SHELVES. Since it is part of the PK of BOOKS, it cannot be null.

Column Integrity in a Relational Database

  • A column must contain only values that are consistent with the defined data format of the column.
Column Integrity

Summary of Data-Integrity Rules

  • Data-integrity rules (also known as constraints) define the relationally correct state for a database.
  • Data-integrity rules ensure that users can perform only those operations that leave the database in a correct, consistent state.
Summary of Data-Integrity Rules

The first three shown here are a summary of the data-integrity rules we’ve covered. The last one though, which is an example of user-defined integrity, has not been explained yet, but you definitely should get an idea about its importance by now.

Terminology

Key terms used in this tutorial included:

  • Candidate key
  • Column
  • Foreign key
  • Primary key
  • Relational database
  • Row
  • Unique key

Summary

In this tutorial, you should have learned how to:

  • Define a primary key
  • Define a foreign key
  • Define a column-integrity rule
  • Identify row, column, primary key, unique key, and foreign key elements given a diagram of a table containing these elements
  • Identify violations of data-integrity rules

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

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