Java Reference
In-Depth Information
SELECT c.First_Name, c.Last_Name, i.Name, oi.Qty
FROM CUSTOMERS c, ORDERS o, ORDERED_ITEMS oi,
INVENTORY i
WHERE o.Order_Number = 4 AND
c.Customer_Id = o.Customer_Id AND
i.Item_Number = oi.Item_Number AND
o.Order_Number = oi.Order_Number;
SQL commands such as the SELECT command shown above are reviewed briefly
later in this chapter and are discussed in considerable detail in subsequent chapters.
Relationships
As illustrated in the preceding discussions of primary and foreign keys, they are
defined to model the relationships among the different tables in a database. These
tables can be related in one of three ways:
One-to-one
One-to-many
Many-to-many
One-to-one relationships
In a
one-to-one
relationship
,
every row in the first table has a corresponding row in
the second table. This type of relationship is often created to separate different types
of data for security reasons. For example, you might want to keep confidential
information such as credit-card data separate from less restricted information.
Another common reason for creating tables with a one -to-one relationship is to
simplify implementation. For example, if you are creating a Web application involving
several forms, you might want to use a separate table for each form.
Other reasons for breaking a table into smaller parts with one -to-one relationships
between them are to improve performance or to overcome inherent restrictions such
as the maximum column count that a database system supports.
Tables related in a one -to-one relationship should always have the same primary key.
This is used to perform joins when the related tables are queried together.
One-to-many relationships
In a
one -to-many
relationship, every row in the first table can have zero, one, or many
corresponding rows in the second table. But for every row in the second table, there
is exactly one row in the first table. For example, there is a one-to-many relationship
between the Orders Table and the Ordered_Items Table reviewed previously.
