��

�� , �� .

� �� , �� , �� , �� . � SQL �� :

��

# The Widget table.
#
# Detailed information on a widget.  Linked to by Purchase_Order_Item
# by way of the widget_id field.  Linked to Widget_Color by way of
# the widget_color field, and to the Widget_Size table by way of the
# widget_size field.
CREATE TABLE Widget_Table (
  widget_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT,
  widget_name CHAR(60) NOT NULL,
  widget_color_id MEDIUMINT(8) NOT NULL,
  widget_size_id MEDIUMINT(8) NOT NULL,
  widgets_on_hand SMALLINT NOT NULL,
  widget_price FLOAT(8,2) NOT NULL,
  commission_percent FLOAT(2,2) NOT NULL,
  PRIMARY KEY (widget_id),
  KEY (widget_name),
  KEY (widget_color_id,widget_size_id)
);

�� MySQL, �� Widget_Table. �� , �� , �� , �� , � �� .

��, �� , �� . � MySQL �� mysqladmin.

prompt> mysqladmin create Widget-DB

�� , �� . � �� Widget_Table �� Widget_color_id � widget_size_id. �� . �� , �� , �� .

�� :

��
��

�� - �� . �� , �� , �� . �� , �� "L" �� "HUGE", �� , �� .

Widget_id �� - �� (3 ��) �� . �� NOT NULL � AUTO_INCREMENT. NOT NULL �� ANSI SQL �� , �� -�� widget �� , �� . �� , MySQL �� . ��, �� , �� , �� . �� , �� , �� .

AUTO_INCREMENT �� MySQL. �� MySQL, �� , �� , �� , �� . �� widgets, �� .

�� . �� AUTO_INCREMENT, �� . �� . �� AUTO_INCREMENT ��.

�� KEY. �� . �� . �� , �� , �� .

�� Widget_Table. �� . �� Purchase_Order.

# The Purchase Order table.
#
# customer_id links us to the Customer_Table
# Where we can get more information about the customer.
#
# To allow for N items on a Purchase order we have to
# have another table called Purchase_Order_Item that
# we link to be way of purchase_order.
CREATE TABLE Purchase_Order (
  purchase_order MEDIUMINT(8) NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT NOT NULL,
  order_date DATE NOT NULL,
  due_date DATE NOT NULL,
  close_date DATE NOT NULL,
  status_code TINYINT(2) UNSIGNED NOT NULL,
  last_action_date TIMESTAMP,
  PRIMARY KEY (purchase_order),
  KEY (customer_id,order_date,status_code)
);

�� , �� . �� , �� , �� , �� . �� Customer. � �� . �� , �� widgets � �� . �� , �� . �� , �� . �� , �� purchase_order, �� . �� , �� .

�� last_action_date. �� TIMESTAMP. �� , �� INSERT �� UPDATE. �� , �� .

�� Purchase_Order_Item:

# The Purchase_Order_Item table.
#
# Since there can be more than one item on a purchase order
# we need a table that contains a single item.  We link back to
# The main Purchase_Order table by use of the purchase_order field.
#
# We can also link back to the Widget_Table table by use of the
# widget_id field.
#
# Last of all we link to the Status table by way of the status_code field.
CREATE TABLE Purchase_Order_Item (
  purchase_order SMALLINT NOT NULL,
  order_sequence SMALLINT NOT NULL,
  widget_id MEDIUMINT(8) NOT NULL,
  quantity SMALLINT(4) UNSIGNED NOT NULL,
  status_code TINYINT(2) UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  item_due_date DATE NOT NULL,
  deliver_date DATE NOT NULL,
  last_action_date TIMESTAMP,
  PRIMARY KEY (purchase_order,order_sequence),
  KEY (widget_id),
  KEY (status_code,order_date)
);

� �� Purchase_Order_Item �� . �� , ��, �� .

�� Customer:

# The Customer table.
#
# We need to know where to send those widgets.
#
# Links back to Purchase_Order by way of customer_id.
CREATE TABLE Customer (
  customer_id SMALLINT NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(80) NOT NULL,
  customer_contact VARCHAR(80) NOT NULL,
  customer_address VARCHAR(80),
  customer_city VARCHAR(80),
  customer_zip VARCHAR(10),
  customer_phone VARCHAR(20),
  customer_fax VARCHAR(20),
  PRIMARY KEY (customer_id),
  KEY (customer_name)
);

�� . �� : �� , � �� , ��, �� .

�� . �� , �� . �� , ��, �� . �� , �� , � �� . ��, �� .

# The Sales_droid table.
#
# Keep track of the people who sell the widgets.
#
CREATE TABLE Sales_Droid (
  sales_droid_id SMALLINT NOT NULL AUTO_INCREMENT,
  sales_droid_first_name VARCHAR(80) NOT NULL,
  sales_droid_last_name VARCHAR(80) NOT NULL,
  sales_droid_phone VARCHAR(20) NOT NULL,
  PRIMARY KEY (sales_droid_id)
);

�� :

# The Status table.
#
# Table to contain all valid status codes.
#
# Links to to Purchase_Order_Item and Purchase_Item by way of status_code.
CREATE TABLE Status (
  status_code TINYINT NOT NULL AUTO_INCREMENT,
  status_text VARCHAR(80) NOT NULL,
  PRIMARY KEY(status_code)
);

�� Status �� . �� ID, �� , �� .

# The Widget_Color table.
#
# Table to contain all valid color codes.
CREATE TABLE Widget_Color (
  widget_color_id TINYINT NOT NULL AUTO_INCREMENT,
  color_text VARCHAR(80) NOT NULL,
  PRIMARY KEY(widget_color_id)
);

# The Widget_Size table.
#
# Table to contain all valid color codes.
CREATE TABLE Widget_Size (
  widget_size_id TINYINT NOT NULL AUTO_INCREMENT,
  size_text VARCHAR(80) NOT NULL,
  PRIMARY KEY(widget_size_id)
);

�� Widget_Color � Widget_Size �� Status. �� .

��! �� .

� �� , �� , �� . �� , �� -�� .

�� .

������ ���� ������

�������

��

��