COMPLETING THE LOGICAL DESIGN - Database Design and Development: An Essential Guide for IT Professionals - page 337

TABLES COLUMNS PRIMARY KEYS

CUSTOMER
ORDER
PAYMENT
PRODUCT
FLOWER
ARRANGEMENT
CHANNEL
GROWER
SHIPMENT

CustNo, CustAddr, CustName
OrdrNo, OrdrDate, OrdAmt
PmntNo, PmntDate, PmntAmt
ProdID, ProdCost
FlwrName, FlwrSize
ArngeType
ChnlID, ChnlName
GrwrID, GrwrName
ShipDate, ShipQty

CustNo
OrderNo
OrderNo, PmntNo
ProductID
ProductID
ProductID
ChnlID
GrwrID
OrdrNo, ChnlID, GrwrId

FOREIGN KEYS

ORDER: CustNo
PAYMENT: OrderNo
SHIPMENT: OrderNo, ChnlID, GrwrID
FLOWER: ProdID
ARRANGEMENT: ProdID

Figure 11-9 List of logical design components for florist business.

note the relational constraints about the applicable columns in each table. In short,
the logical schema is nothing but descriptions of the relational tables.

Logical Design Components

In the previous section, we discussed a florist business and covered the representa-
tion of individual components. Let us pull the separate representations of individ-
ual components together and list the complete set of logical design components.
Figure 11-9 displays such a list.
Note each component. Again, note how every component is part of the under-
lying design concept—the two-dimensional table or relation.

Logical Schema

Creating and defining the logical schema essentially completes the logical design
phase. The logical schema for the relational data model contains the representation
and descriptions of the relational tables. Let us take the complete relational data
model for the florist business and define the logical schema. Each table in the rela-
tional data model must be defined. Every column in each table must be expressed
in the logical schema.
Figure 11-10 displays the logical schema for the relational data model repre-
senting the information requirements for the florist business. We have used stan-
dard relational notation to express the schema. Data definition languages are used
to define the schema to the data dictionary.
Carefully note the definition for each table in the logical schema. Also, for each
table, observe how the columns are defined. Make a note of the definition of rela-
tional constraints in the schema.