Databases Reference
In-Depth Information
Figure 15.1
Single table database for “sales.”
SELECT custNo, custName, custAddress, creditRat AS customer
FROM sales;
These queries shows that they are nothing more than projections of the sales table
over two different sets of columns. If we join the two tables, productSales and customer,
over the common attribute custNo, they will produce the original table, sales. This is
called a
lossless join
and shows that the two tables are equivalent to the single table in
terms of meaningful content.
The two smaller tables, productSales and customer, have nice performance and
storage properties that the single table, sales, doesn't have. Let's revisit the problems in
the sales table mentioned above.
1.
In the sales table we see that certain product and customer information is stored
redundantly, wasting storage space. The redundant data is custNo, custName,
custAddress, and creditRat. In the two-table equivalent, there is only redundancy
in custNo. Attribute values for custName, custAddress, and creditRat are only
stored once. In most cases, as is the case here, this separation of data results in
smaller tables.
