Database Relationships

Database Relationships

What are table relationships?

In a relational database, relationships enable you to prevent redundant data.

For example, if you are designing a database that will track information about books, you might have a table named "Titles" that stores information about each book, such as the book's title, date of publication, and publisher. There is also information that you might want to store about the publisher, such as the publisher's telephone number, address, and ZIP Code/Postal Code. If you were to store all this information in the "Titles" table, the publisher's telephone number would be duplicated for each title that the publisher prints.

Multiple Choice

A Primary Key is a code that uniquely identifies a _____________ in a Database.

• Record

• Field

• Text

• Number

Multiple Choice

In a database table, rows are called __________

• Field

• Record

• Primary Key

Multiple Choice

Tables are set up to contain columns and rows of information. In a database table, the columns are called __________.

• Cell

• Field

• Column

• Record

Multiple Choice

Database forms are computerized versions of paper forms, such as a patient record, job application or a class registration form.

• True
• False

​One-to-many relationships

​

A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. For example, the "Publishers" and "Titles" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title comes from only one publisher.

​One-to-one relationship

​

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

​Many-to-many relationship

​In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B.

How to define relationships between tables?

When you create a relationship between tables, the related fields do not have to have the same names. However, related fields must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if theFieldSizeproperty of both of the matching fields is the same. 

Word Cloud

What are the most important concepts or terms you learned about database relationships today?

​Recap

Database relationships are critical in connecting various pieces of data in a structured way to enhance insights. Common types of relationships include:

• One-to-One: Each record in one table corresponds to exactly one record in another.

• One-to-Many: A single record in one table is related to multiple records in another.

• Many-to-Many: Multiple records in one table correspond to multiple records in another.