Database Reference
In-Depth Information
CUSTOMER (
CustNo
, CustAddr, CustName)
ORDER (
OrdrNo
, OrdrDate, OrdrAmt, CustNo)
Foreign Key:
CustNo
REFERENCES
CUSTOMER
ON DELETE CASCADE
PAYMENT (OrdrNo, PmntNo, PmntDate, PmntAmt)
Foreign Key:
OrdrNo
REFERENCES
ORDER
PRODUCT (ProdID, ProdCost)
FLOWER (
ProdID
, ProdCost, FlwrName, FlwrSize)
Foreign Key:
ProdID
REFERENCES
PRODUCT
ARRANGEMENT (
ProdID
, ProdCost, ArngeType)
Foreign Key:
ProdID
REFERENCES
PRODUCT
CHANNEL (
ChnlID
, ChnlName)
GROWER (
GrwrID
, GrwrName)
SHIPMENT (OrdrNo, ChnlID, GrwrID, ShipDate, ShipQty)
Foreign Key:
OrdrNo
REFERENCES
ORDER
ChnlID
REFERENCES
CHANNEL
GrwrID
REFERENCES
GROWER
Figure 11-10
Relational schema for florist business.
Special Considerations
Before declaring that the logical design phase is complete, you need to examine
each statement in the logical schema carefully. This is the basis for defining the
physical files, records, fields, and restrictions. The logical schema, therefore, must
truly define and represent the real-world information requirements. Here is a
list of special considerations in verifying the logical schema for the relational
model:
•
Ensure that the logical schema defines each and every table in the relational
model.
•
Define each table and its columns with meaningful names. Remember, these
names will be used for accessing tables and the columns.
•
For each column, define appropriate data types and data lengths.
•
If there are any edits to be specified for specific columns, do so clearly for these
columns.
•
If any column may not contain null values, express this clearly.
•
Clearly indicate the columns that make up the primary key for each table.
Primary keys cannot contain null values.
•
Show the foreign key columns distinctly. Indicate the table to which a foreign
key establishes a relationship.
•
Ensure that all intersection tables are clearly defined with proper primary key
and foreign key columns.
•
Indicate the columns for which duplicate values are not allowed in the table.
Search WWH ::
Custom Search