For each weak entity in the model-there are three: inventory, order, and item-translate the entity directly to a
TABLEstatement as in Step 1.
Include all attributes as in Step 1.
Include as attributes the primary key attributes of the owning entity; that is, the entity the weak entity is related to. These attributes are in the table and are also included as part of the primary key of the weak entity.
For example, for the inventory entity, create the following:
CREATE TABLE inventory ( wine_id int(5) DEFAULT '0' NOT NULL, inventory_id int(3) NOT NULL, on_hand int(5) NOT NULL, cost float(5,2) NOT NULL, case_cost float(5,2) NOT NULL, dateadded timestamp(12) DEFAULT NULL, PRIMARY KEY (wine_id,inventory_id) );
wine_id is included from the wine table and forms part of the
KEY definition. All attributes can't be
NULL in this inventory table, so you'll note liberal use of
dateadded attribute has a
NULL, which if no value is inserted, is automatically filled with the current date and time.
A similar approach is taken with orders, in which
cust_id is included from the customer table as an attribute and as part of the
CREATE TABLE orders ( cust_id int(5) DEFAULT '0' NOT NULL, order_id int(5) DEFAULT '0' NOT NULL, date timestamp(12), discount float(3,1) DEFAULT '0.0', delivery float(4,2) DEFAULT '0.00', note varchar(120), PRIMARY KEY (cust_id,order_no) );
The items table is slightly more complex, but made easier because orders has already been defined. The items table includes the
KEY attributes of the entity it is related to (that is, orders). Because the
KEY of orders is already resolved, the resolution is as follows:
CREATE TABLE items ( cust_id int(5) DEFAULT '0' NOT NULL, order_id int(5) DEFAULT '0' NOT NULL, item_id int(3) DEFAULT '1' NOT NULL, qty int(3), price float(5,2), date timestamp(12), PRIMARY KEY (cust_id,order_no,item_id) );