Login

What is a Relational Database? - Elements, Design & Advantages

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Types of Data: Text, Numbers & Multimedia

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:05 Database Relationships
  • 2:11 Rules for Relational Databases
  • 3:15 Primary and Foreign Keys
  • 6:17 Cardinality of Relationships
  • 9:06 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay
Create an account to start this course today
Try it free for 5 days!
Create An Account

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Paul Zandbergen

Paul has a PhD from the University of British Columbia and has taught Geographic Information Systems, statistics and computer programming for 15 years.

The relational database is the most widely used type of database management system. Learn how the relational database organizes data using tables, keys and associations.

Database Relationships

A relational database consists of tables that are linked together in some meaningful way. For example, consider a company that sells products to customers. The company maintains a database of the products it sells. Each product has a unique code so it can be identified. The product database consists of a table, and every product is a record in this table. The second database the company maintains is a database of its customers. Each customer also has a unique code as well as their name and address. The third database is a database of orders placed by customers. Each order also has a unique code.

So what are the relationships between the three tables? Think of a single order placed by a customer. An order will contain at least one product, but possibly more than one. So each order will include the unique codes for each product. An order is also linked to a particular customer, so each order will include the unique code for this customer. Because customers are linked to their order and each order contains the product codes, you can look up which products each customer has bought.

Once these relationships are established in the database, it becomes possible to look for meaningful patterns in the data. For example, the company may be interested in finding out if certain products are often bought together. This might give some insights into how to best market products as a bundle. Or the company might be interested in identifying the customers that buy similar products over time as part of different orders instead of as a single order. A well-designed relational database makes it possible to examine such patterns based on the relationships between the various tables.

Rules for Relational Databases

In order for a relational database to function, a number of basic rules need to be followed:

  • Each table has a unique name.
  • Each table contains multiple rows.
  • Each row in a table is unique.
  • Every table has a key to uniquely identify the rows.
  • Each column in a table has a unique attribute name.

These rules are implemented as part of the overall database design. Typically, the design for a database is developed by a database specialist in close consultation with various people in the organization. These include the actual database users but also more senior executives who need to make sure the database supports not only day-to-day operations but also provides the information necessary to support decision-making.

Primary and Foreign Keys

You know that a relational database management system uses relationships to link tables together. Relationships are also called table associations. Relationships are created using keys. A key for a database table consists of one or more fields that uniquely identify a record. Keys are important in a database because they assist in maintaining data consistency, and they make it possible to create associations between tables.

A primary key is a minimal set of fields whose values uniquely identify a record in a table. Each table can only have one primary key, even though this may consist of multiple attributes. In most databases a key consists of a single attribute. For example, for a book, you could use the ISBN number since there are no two books with the same ISBN number. For employees of an organization, you could use their Social Security Number, which is unique to each individual. For the example of customers, products, and orders, you would create your own unique codes as you build the database. For example, the table of customers contains a field for Customer ID.

Sometimes a key is composed of multiple attributes. This means that only the combination of these attributes uniquely identifies a record. This is called a composite key.

A foreign key is a field whose values are the same as the primary key of another table. For the example of customers, products, and orders, you would create a table of orders. For each order you record the customer using the Customer ID. In the table of orders, the field Customer ID is the foreign key since it is the primary key in the table of customers. The table of orders has its own primary key, called Order ID, to uniquely identify each order. If the same customer makes multiple orders, this results in duplicate values in the Customer ID field.

This applies as a general rule: primary keys are unique, while foreign keys may contain duplicate values. Remember, keys create relationships in tables, and a relationship is an association between two or more tables. In the example, the field Customer ID is used to create the association. This field acts as the primary key in the table of customers and the foreign key in the table of orders.

Cardinality of Relationships

There are a number of different types of relationships based on how many records in one table are related to how many records in the other table. This is known as cardinality. There are four types of cardinality:

  • One-to-one (1:1).
  • One-to-many (1:m).
  • Many-to-one (m:1).
  • Many-to-many (m:m).

Let's look at an example of each. The simplest relationship is a one-to-one (1:1) relationship. Consider that a voter can only cast one vote in an election. An election ballot can therefore belong only to one voter. This means there is a one-to-one relationship between a voter and a ballot.

To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?
I am a teacher
What is your educational goal?
 Back

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back

Earning College Credit

Did you know… We have over 95 college courses that prepare you to earn credit by exam that is accepted by over 2,000 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create An Account
Support