Relational databases: Defining relationships between Database tables

An important part of designing a relational database is dividing the data elements into related tables. Once you're ready to start working with the data, you can rely on relationships between tables to aggregate the data in a meaningful way. For example, unless you know which customer placed a particular order, the order information is useless. So far, you may have realized that you did not store customer and order information in the same table. Instead, the order and customer data are stored in two related tables, and the relationships between the two tables are used to view each order and its corresponding customer information at the same time. If the normalized table is the basis of a relational database, then the relationship is the basis.This article uses the following data to demonstrate. The process of normalizing data through the boyce-codd paradigm (BCNF) resulted in seven related tables:
book: {title *,ISBN, price}
author: { First Name *, last name *}
zip Code: {ZIP code *}
Publisher: { Publisher *}
status: {state *}
City: {City *}

relationship type
You have a lot of relationships with your family. For example, you and your mother are relatives. You have only one mother, but she may have several children. You and your siblings are relatives-you may have a lot of siblings, and of course they will have a lot of siblings. If you get married, you and your spouse have spouses--each other--but only one at a time. Database relationships are very similar because they are associations between tables. There are three types of relationships:

• one-to: Two tables can only have a single record on either side of a relationship. Each primary key value is related only to one (or no) record in the related table. They're like couples-you can get married or not, but if you get married, you and your spouse have only one spouse. Most A-to-one relationships are enforced by business rules and do not flow naturally from the data. Without such a rule, you can usually merge the two tables into a single table without breaking any normalization rules.
• one-to-many:
• Many-to-many: Each record in two tables can be related to any number of records (or no records) in another table. For example, if you have several siblings, so do your siblings (with many siblings). A many-to-many relationship requires a third table, called an association table or linked table, because the relational system cannot accommodate the relationship directly.

build relationship
Span data-sgtrans-text= "5-2" when you start building relationships between related tables, you're probably already familiar with the data. Therefore, the association at this time is more pronounced than at the beginning. The database system relies on matching values found in two tables to form a relationship. When a match is found, the system extracts the data from the two tables to create the virtual record. For example, you might want to see all the books written by a particular author. In this case, the system will match the values between the book and the author table. It is important to remember that in most cases, the generated records are dynamic, which means that any changes made to the virtual record are usually returned to the underlying table.

These match values are primary key and foreign key value. (Relational models do not require relationships based on primary keys.) You can use any candidate key in the table, but using the primary key is an accepted standard. You learned in part 2nd that the primary key-the primary key uniquely identifies each record in the table. A simply, it is the primary key of one table in another table. Therefore, you have nothing to do-simply add the primary key field as a foreign key to the related table.



Return to your sample table and start typing the foreign key as needed. (Continue with the paper list-it's too early to actually create tables in the database system.) It's much easier to correct mistakes on paper. Keep in mind that you want to add a primary key value to the related table. Simply recalling the relationships between entities, the rest is easy:

• Books are related to categories.
• Books are related to publishers.
• Books are related to the author.
• The author is associated with the postal code.
• The postal code is related to the city.
• The city is related to the state.

This particular step is not written in stone, and you may find it easier to add foreign keys during normalization. When you move a field to a new table, you may add the primary key of the new table as a foreign key to the original table. However, foreign keys typically change when you continue to normalize the remaining data. You may find it more efficient to complete all of these tasks at once after all tables have been fully normalized.

let's start with the books table and iterate through each table at a time, where the table has only three fields. Specifically, add the primary key from the author, category, and publisher tables to the book. When you are finished, the books table has seven fields:
Book
Title (primary key)
ISBN (primary key)
Price
A masterpiece. The name is many to many
Author. Many-to-many names
Category (FK) category. Category many-to-many
Press. Publishers a couple of more

Keep in mind that the primary key in the authors table is a complex key based on the first name and last name fields. Therefore, you must add both fields to the books table. Note that the Foreign key field name contains the FK suffix. Adding suffixes improves readability and is self-documenting. You might find it easier to track foreign keys if the names of the other keys identify foreign keys. It doesn't matter if the name of the primary key and foreign key is different.

There are three kinds of relationships: books and authors, books and categories, and books and publishers. What you may not be aware of is the issue of two of these relationships:

• book to the author: A book can have multiple authors.
• Book Category: A book can have more than one category.

Both of these relationships represent many-to-many relationships. As we have already told you, the table cannot directly accommodate these relationships and requires a third linked table. (The relationship between books and publishers is a one-to-many relationship, as is now said.) ) )

both of the newly discovered many-to-many relationships require a linked table that contains the primary key in each table as the foreign key. The new link table is:
Book author
Title. Title one-to-many
Book. One-to-many
A masterpiece. Name a couple of more
Author. Last name one to many

Book classification
Title. Title one-to-many
Book. One-to-many
Category (FK) category. One-to-many categories

you do not need to change the categories, author, or publisher tables. However, foreign keys named FK, LASTNAMEFK, and CATEGORYFK must be removed from the book:
Book
Title (primary key)
ISBN (primary key)
Price
Press. Publishers a couple of more

now, let's go to the authors table, which currently has two fields. Each author is associated with a postal code value in the ZipCodes table. However, each zip code may involve multiple authors. To accommodate this one-to-many relationship, enter the primary key in the ZipCodes table as a foreign key into the authors table:
Author
First name (primary key)
Last name (primary key)
Zip code. Zip Code One-to-many

at this point, you are ready to process the remaining address components. It is strange to see them partitioned into tables, but this is the result of normalizing the data by BCNF. Each postal code value will have a corresponding city and state value. The values for each city and state are entered only once in their respective tables. ZipCode and City tables require foreign key fields to accommodate these relationships:
Zip code
Zip code
City. City One-to-many

City
City (primary key)
State. One-to-many states

State
Status (primary key)

From one point to nine points
Finally, you have nine tables: book, author, category, publisher, zip code, city, state, book author, and book category. Figure One displays a graphical representation of the sample library database at the end of this process. It's hard to imagine a simple data table that can be divided into nine pieces.

figure I

original representation in nine tables required.

Because of the simplicity of the example, you might want to know how this relational business can help you. It looks like you're still storing redundant data in the form of a key, just different. That's because we're only performing in a few fields. Try to imagine a table with more than 10 fields. Of course, you still have to store the table's primary key as a foreign key value in the related table, but this can constitute a maximum of one or two additional fields. Compared to an alternative to adding all more than 10 entries in the table for each record.

Relational databases: Defining relationships between Database tables