Home Page --- Guang Huang

• Technical Skills

• Professional Experiences

• Education/Honors

• Selected Projects

• Blue Matrix Software Ltd. Co. Beijing, China

• Institute of Software of Chinese Academy of Sciences

• Hua-Wei Telecom Company, China

• Dynamic Data Collection IR System new!

• Advance Database

• Advance Operating System

• Advance Computer Architecture

• Multimedia Network

• Information Retrieval

• Artificial Intelligence

• Dynamic Data Collection IR System new!

• E-Commerce Online-BookStore (JSP)

• Distributed File System (JAVA/RMI)

• VLSA design: n-bytes BCD Adder (VLSA)

• Tomasulo's algorithm(dynamic scheduling) simulation

• Information Retrieval Engine (JAVA)

• Game Player Agent(Empire-Builder)

• Related Links

• Address

• Phone Number

• Email Address

HOME > Course Projects > Adv. Database > Online Bookstore

E-R Dialog
Table Description

Download the codes from HERE

TOP

E-R Dialog

TOP

Table Description

Table Name	Create Statement	Index	Child	Par.	Pri. Key
ADDRESS	CREATE TABLE ADDRESS ( Address_ID INTEGER NOT NULL, Zip_Code VARCHAR(8) DEFAULT '' NOT NULL, City VARCHAR(20) DEFAULT '' NOT NULL, Street VARCHAR(20) NOT NULL, State_Abbr VARCHAR(3) NOT NULL);
BOOK	CREATE TABLE BOOK ( Note VARCHAR(255) DEFAULT '' NULL, Book_Price NUMBER(7,2) NOT NULL CHECK (Book_Price >= '0'), Book_Description VARCHAR(511) DEFAULT '' NULL, Book_Author VARCHAR(20) DEFAULT '' NOT NULL, Book_Publisher VARCHAR(20) DEFAULT '' NOT NULL, Book_Title VARCHAR(20) DEFAULT '' NOT NULL, Book_Pricture VARCHAR(20) DEFAULT 'DefPic.jpg' NULL, Sub_Category_ID INTEGER NOT NULL, Book_ISBN VARCHAR(20) DEFAULT '' NOT NULL );
BOOK_ ORDER	CREATE TABLE BOOK_ORDER ( User_ID VARCHAR(15) NOT NULL, Order_ID INTEGER NOT NULL, Order_Amount NUMBER(7,2) DEFAULT 0 NOT NULL CHECK (Order_Amount >= '0'), Order_Date DATE DEFAULT Sysdate NOT NULL, Shipping_Date DATE DEFAULT Sysdate NOT NULL, Shipping_Transaction_Number INTEGER NOT NULL, Shipping_Addres_ID INTEGER DEFAULT 0 NOT NULL, Shipment_Type INTEGER NOT NULL, Payment_Transaction_Number INTEGER NOT NULL);
BOOK_ REFERENCE	CREATE TABLE BOOK_REFERENCE ( Book_ISBN VARCHAR(20) DEFAULT '' NOT NULL, Ref_Book_ISBN VARCHAR(20) DEFAULT '' NOT NULL);
CATEGORY	CREATE TABLE CATEGORY ( Category_Description VARCHAR(255) DEFAULT '' NULL, Category_Name VARCHAR(20) DEFAULT '' NOT NULL, Category_ID INTEGER NOT NULL);
CHECK_ PAYMENT	CREATE TABLE CHECK_PAYMENT ( Check_Number VARCHAR(5) DEFAULT '' NOT NULL, Check_Bank_Number VARCHAR(20) DEFAULT '' NOT NULL, Payment_Transaction_Number INTEGER NOT NULL);
CREDIT_ CARD_ PAYMENT	CREATE TABLE CREDIT_CARD_PAYMENT ( Card_Type VARCHAR(10) DEFAULT 'Master' NOT NULL CHECK (Card_Type IN ('Master', 'Visa', 'Discover', 'American')), Card_Exp VARCHAR(5) DEFAULT '' NOT NULL, Card_Number VARCHAR(20) DEFAULT '' NOT NULL, Payment_Transaction_Number INTEGER NOT NULL);
CUSTOMER	CREATE TABLE CUSTOMER ( Password VARCHAR(15) DEFAULT '' NOT NULL, email VARCHAR(20) DEFAULT '' NOT NULL, First_Name VARCHAR(15) DEFAULT '' NOT NULL, Last_Name VARCHAR(15) DEFAULT '' NOT NULL, Phone_Number VARCHAR(15) DEFAULT '' NOT NULL, User_ID VARCHAR(15) NOT NULL, Address_ID INTEGER NOT NULL, Register_Date DATE DEFAULT Sysdate NOT NULL, Last_Login_Time DATE DEFAULT Sysdate NOT NULL, Visits_Number INTEGER DEFAULT 0 NOT NULL CHECK (Visits_Number >= '0'));
ORDERED_ BOOK	CREATE TABLE ORDERED_BOOK ( Order_ID INTEGER NOT NULL, Order_Quantity INTEGER DEFAULT 1 NOT NULL CHECK (Order_Quantity >= '0'), Book_ISBN VARCHAR(20) DEFAULT '' NOT NULL);
PAYMENT	CREATE TABLE PAYMENT ( Payment_Type VARCHAR(20) NOT NULL, Payment_Amount NUMBER(7,2) DEFAULT 0 NOT NULL CHECK (Payment_Amount >= '0'), Payment_Transaction_Number INTEGER NOT NULL, Payment_Date DATE DEFAULT Sysdate NOT NULL);
SHIPMENT	CREATE TABLE SHIPMENT ( Shipment_Description VARCHAR(255) DEFAULT '' NULL, Shipment_Charge NUMBER(7,2) DEFAULT 0 NOT NULL CHECK (Shipment_Charge >= '0'), Shipment_Type INTEGER NOT NULL, Shipment_Name VARCHAR(20) NOT NULL);
STATE	CREATE TABLE STATE ( State_Full_Name VARCHAR(20) DEFAULT '' NOT NULL, State_Abbr VARCHAR(3) NOT NULL);
SUB_ CATEGORY	CREATE TABLE SUB_CATEGORY ( Sub_Category_Description VARCHAR(255) DEFAULT '' NULL, Sub_Category_Name VARCHAR(20) DEFAULT '' NOT NULL, Sub_Category_ID INTEGER NOT NULL, Category_ID INTEGER NOT NULL);

TOP

Column(s) of "ADDRESS" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	No	Address_ID
VARCHAR(3)	NOT NULL	No	Yes	State_Abbr
VARCHAR(20)	NOT NULL	No	No	Street
VARCHAR(20)	NOT NULL	No	No	City
VARCHAR(8)	NOT NULL	No	No	Zip_Code

Index(s) of "ADDRESS" Table

Name	Type	Unique
XPKADDRESS	PK	Yes
XAK1ADDRESS	AK1	Yes
XAK2ADDRESS	AK2	Yes
XIF65ADDRESS	IF65	No

Child Relationship(s) of "ADDRESS" Table

FK Name	Type	Cardinality	Child Table
is_destination_of	Non-identifying	One-to-Zero-One-or-More
lived_with	Non-identifying	One-to-Zero-One-or-More

Child Table(s) of "is_destination_of" Child Relationship

Name

BOOK_ORDER

Child Table(s) of "lived_with" Child Relationship

Name

CUSTOMER

Parent Relationship(s) of "ADDRESS" Table

FK Name	Type	Null Option	Cardinality	Parent Table
contain	Non-identifying	No Nulls	One-to-Zero-One-or-More

Parent Table(s) of "contain" Parent Relationship

Name

STATE

Primary Key Column(s) of "ADDRESS" Table

Name	Datatype	Null Option	Is PK	Is FK
Address_ID	INTEGER	NOT NULL	Yes	No

TOP

Column(s) of "BOOK" Table

Datatype	Null Option	Is PK	Is FK	Name
VARCHAR(20)	NULL	Yes	No	Book_ISBN
VARCHAR(20)	NOT NULL	No	No	Book_Title
VARCHAR(20)	NOT NULL	No	No	Book_Publisher
VARCHAR(20)	NOT NULL	No	No	Book_Author
VARCHAR(511)	NULL	No	No	Book_Description
NUMBER(7,2)	NOT NULL	No	No	Book_Price
VARCHAR(20)	NULL	No	No	Book_Pricture
INTEGER	NOT NULL	No	Yes	Sub_Category_ID
VARCHAR(255)	NULL	No	No	Note

Index(s) of "BOOK" Table

Name	Type	Unique
XPKBOOK	PK	Yes
XIF20BOOK	IF20	No

Child Relationship(s) of "BOOK" Table

FK Name	Type	Cardinality	Child Table
is_refered_by	Identifying	One-to-Zero-One-or-More
refer_to_	Identifying	One-to-Zero-One-or-More
is_in	Identifying	One-to-Zero-One-or-More

Child Table(s) of "is_refered_by" Child Relationship

Name

BOOK_REFERENCE

Child Table(s) of "refer_to_" Child Relationship

Name

BOOK_REFERENCE

Child Table(s) of "is_in" Child Relationship

Name

ORDERED_BOOK

Parent Relationship(s) of "BOOK" Table

FK Name	Type	Null Option	Cardinality	Parent Table
include	Non-identifying	No Nulls	One-to-Zero-One-or-More

Parent Table(s) of "include" Parent Relationship

Name

SUB_CATEGORY

Primary Key Column(s) of "BOOK" Table

Name	Datatype	Null Option	Is PK	Is FK
Book_ISBN	VARCHAR(20)	NULL	Yes	No

TOP

Column(s) of "BOOK_ORDER" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	No	Order_ID
INTEGER	NOT NULL	No	Yes	Shipping_Addres_ID
VARCHAR(15)	NOT NULL	No	Yes	User_ID
DATE	NOT NULL	No	No	Order_Date
NUMBER(7,2)	NOT NULL	No	No	Order_Amount
INTEGER	NOT NULL	No	No	Shipping_Transaction_Number
DATE	NOT NULL	No	No	Shipping_Date
INTEGER	NOT NULL	No	Yes	Payment_Transaction_Number
INTEGER	NOT NULL	No	Yes	Shipment_Type

Index(s) of "BOOK_ORDER" Table

Name	Type	Unique
XPKBOOK_ORDER	PK	Yes
XAK2BOOK_ORDER	AK2	Yes
XIF18BOOK_ORDER	IF18	No
XIF19BOOK_ORDER	IF19	No
XIF55BOOK_ORDER	IF55	No
XIF69BOOK_ORDER	IF69	No

Child Relationship(s) of "BOOK_ORDER" Table

FK Name	Type	Cardinality	Child Table
include	Identifying	One-to-One-or-More (P)

Child Table(s) of "include" Child Relationship

Name

ORDERED_BOOK

Parent Relationship(s) of "BOOK_ORDER" Table

FK Name	Type	Null Option	Cardinality	Parent Table
Pay	Non-identifying	No Nulls	One-to-Zero-One-or-More
is_used_to	Non-identifying	No Nulls	One-to-One-or-More (P)
is_destination_of	Non-identifying	No Nulls	One-to-Zero-One-or-More
make	Non-identifying	No Nulls	One-to-Zero-One-or-More

Parent Table(s) of "Pay" Parent Relationship

Name

PAYMENT

Parent Table(s) of "is_used_to" Parent Relationship

Name

SHIPMENT

Parent Table(s) of "is_destination_of" Parent Relationship

Name

ADDRESS

Parent Table(s) of "make" Parent Relationship

Name

CUSTOMER

Primary Key Column(s) of "BOOK_ORDER" Table

Name	Datatype	Null Option	Is PK	Is FK
Order_ID	INTEGER	NOT NULL	Yes	No

TOP

Column(s) of "BOOK_REFERENCE" Table

Datatype	Null Option	Is PK	Is FK	Name
VARCHAR(20)	NOT NULL	Yes	Yes	Book_ISBN
VARCHAR(20)	NOT NULL	Yes	Yes	Ref_Book_ISBN

Index(s) of "BOOK_REFERENCE" Table

Name	Type	Unique
XPKBOOK_REFERENCE	PK	Yes
XIF26BOOK_REFERENCE	IF26	No
XIF29BOOK_REFERENCE	IF29	No

Child Relationship(s) of "BOOK_REFERENCE" Table

FK Name	Type	Cardinality	Child Table

Parent Relationship(s) of "BOOK_REFERENCE" Table

FK Name	Type	Null Option	Cardinality	Parent Table
is_refered_by	Identifying		One-to-Zero-One-or-More
refer_to_	Identifying		One-to-Zero-One-or-More

Parent Table(s) of "is_refered_by" Parent Relationship

Name

BOOK

Parent Table(s) of "refer_to_" Parent Relationship

Name

BOOK

Primary Key Column(s) of "BOOK_REFERENCE" Table

Name	Datatype	Null Option	Is PK	Is FK
Book_ISBN	VARCHAR(20)	NOT NULL	Yes	Yes
Ref_Book_ISBN	VARCHAR(20)	NOT NULL	Yes	Yes

TOP

Column(s) of "CATEGORY" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	No	Category_ID
VARCHAR(20)	NOT NULL	No	No	Category_Name
VARCHAR(255)	NULL	No	No	Category_Description

Index(s) of "CATEGORY" Table

Name	Type	Unique
XPKCATEGORY	PK	Yes
XAK1CATEGORY	AK1	Yes

Child Relationship(s) of "CATEGORY" Table

FK Name	Type	Cardinality	Child Table
Include	Non-identifying	One-to-Zero-One-or-More

Child Table(s) of "Include" Child Relationship

Name

SUB_CATEGORY

Parent Relationship(s) of "CATEGORY" Table

FK Name	Type	Null Option	Cardinality	Parent Table

Primary Key Column(s) of "CATEGORY" Table

Name	Datatype	Null Option	Is PK	Is FK
Category_ID	INTEGER	NOT NULL	Yes	No

TOP

Column(s) of "CHECK_PAYMENT" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	Yes	Payment_Transaction_Number
VARCHAR(20)	NOT NULL	No	No	Check_Bank_Number
VARCHAR(5)	NOT NULL	No	No	Check_Number

Index(s) of "CHECK_PAYMENT" Table

Name	Type	Unique
XPKCHECK_PAYMENT	PK	Yes
XIF7CHECK_PAYMENT	IF7	No

Child Relationship(s) of "CHECK_PAYMENT" Table

FK Name	Type	Cardinality	Child Table

Parent Relationship(s) of "CHECK_PAYMENT" Table

FK Name	Type	Null Option	Cardinality	Parent Table
	Subtype		Is a

Parent Table(s) of "" Parent Relationship

Name

PAYMENT

Primary Key Column(s) of "CHECK_PAYMENT" Table

Name	Datatype	Null Option	Is PK	Is FK
Payment_Transaction_Number	INTEGER	NOT NULL	Yes	Yes

TOP

Column(s) of "CREDIT_CARD_PAYMENT" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	Yes	Payment_Transaction_Number
VARCHAR(20)	NOT NULL	No	No	Card_Number
VARCHAR(5)	NOT NULL	No	No	Card_Exp
VARCHAR(10)	NOT NULL	No	No	Card_Type

Index(s) of "CREDIT_CARD_PAYMENT" Table

Name	Type	Unique
XPKCREDIT_CARD_PAYMENT	PK	Yes
XIF8CREDIT_CARD_PAYMENT	IF8	No

Child Relationship(s) of "CREDIT_CARD_PAYMENT" Table

FK Name	Type	Cardinality	Child Table

Parent Relationship(s) of "CREDIT_CARD_PAYMENT" Table

FK Name	Type	Null Option	Cardinality	Parent Table
	Subtype		Is a

Parent Table(s) of "" Parent Relationship

Name

PAYMENT

Primary Key Column(s) of "CREDIT_CARD_PAYMENT" Table

Name	Datatype	Null Option	Is PK	Is FK
Payment_Transaction_Number	INTEGER	NOT NULL	Yes	Yes

TOP

Column(s) of "CUSTOMER" Table

Datatype	Null Option	Is PK	Is FK	Name
VARCHAR(15)	NOT NULL	Yes	No	User_ID
VARCHAR(20)	NOT NULL	No	No	email
VARCHAR(15)	NOT NULL	No	No	First_Name
VARCHAR(15)	NOT NULL	No	No	Last_Name
VARCHAR(15)	NOT NULL	No	No	Phone_Number
INTEGER	NOT NULL	No	Yes	Address_ID
VARCHAR(15)	NOT NULL	No	No	Password
INTEGER	NOT NULL	No	No	Visits_Number
DATE	NOT NULL	No	No	Last_Login_Time
DATE	NOT NULL	No	No	Register_Date

Index(s) of "CUSTOMER" Table

Name	Type	Unique
XPKCUSTOMER	PK	Yes
XIF73CUSTOMER	IF73	No

Child Relationship(s) of "CUSTOMER" Table

FK Name	Type	Cardinality	Child Table
make	Non-identifying	One-to-Zero-One-or-More

Child Table(s) of "make" Child Relationship

Name

BOOK_ORDER

Parent Relationship(s) of "CUSTOMER" Table

FK Name	Type	Null Option	Cardinality	Parent Table
lived_with	Non-identifying	No Nulls	One-to-Zero-One-or-More

Parent Table(s) of "lived_with" Parent Relationship

Name

ADDRESS

Primary Key Column(s) of "CUSTOMER" Table

Name	Datatype	Null Option	Is PK	Is FK
User_ID	VARCHAR(15)	NOT NULL	Yes	No

TOP

Column(s) of "ORDERED_BOOK" Table

Datatype	Null Option	Is PK	Is FK	Name
VARCHAR(20)	NOT NULL	Yes	Yes	Book_ISBN
INTEGER	NOT NULL	Yes	Yes	Order_ID
INTEGER	NOT NULL	No	No	Order_Quantity

Index(s) of "ORDERED_BOOK" Table

Name	Type	Unique
XPKORDERED_BOOK	PK	Yes
XIF19ORDERED_BOOK	IF19	No
XIF57ORDERED_BOOK	IF57	No

Child Relationship(s) of "ORDERED_BOOK" Table

FK Name	Type	Cardinality	Child Table

Parent Relationship(s) of "ORDERED_BOOK" Table

FK Name	Type	Null Option	Cardinality	Parent Table
is_in	Identifying		One-to-Zero-One-or-More
include	Identifying		One-to-One-or-More (P)

Parent Table(s) of "is_in" Parent Relationship

Name

BOOK

Parent Table(s) of "include" Parent Relationship

Name

BOOK_ORDER

Primary Key Column(s) of "ORDERED_BOOK" Table

Name	Datatype	Null Option	Is PK	Is FK
Book_ISBN	VARCHAR(20)	NOT NULL	Yes	Yes
Order_ID	INTEGER	NOT NULL	Yes	Yes

TOP

Column(s) of "PAYMENT" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	No	Payment_Transaction_Number
VARCHAR(20)	NOT NULL	No	No	Payment_Type
NUMBER(7,2)	NOT NULL	No	No	Payment_Amount
DATE	NOT NULL	No	No	Payment_Date

Index(s) of "PAYMENT" Table

Name	Type	Unique
XPKPAYMENT	PK	Yes

Child Relationship(s) of "PAYMENT" Table

FK Name	Type	Cardinality	Child Table
Pay	Non-identifying	One-to-Zero-One-or-More
	Subtype	Is a
	Subtype	Is a

Child Table(s) of "Pay" Child Relationship

Name

BOOK_ORDER

Child Table(s) of "" Child Relationship

Name

CREDIT_CARD_PAYMENT

Child Table(s) of "" Child Relationship

Name

CHECK_PAYMENT

Parent Relationship(s) of "PAYMENT" Table

FK Name	Type	Null Option	Cardinality	Parent Table

Primary Key Column(s) of "PAYMENT" Table

Name	Datatype	Null Option	Is PK	Is FK
Payment_Transaction_Number	INTEGER	NOT NULL	Yes	No

TOP

Column(s) of "SHIPMENT" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	No	Shipment_Type
VARCHAR(20)	NOT NULL	No	No	Shipment_Name
NUMBER(7,2)	NOT NULL	No	No	Shipment_Charge
VARCHAR(255)	NULL	No	No	Shipment_Description

Index(s) of "SHIPMENT" Table

Name	Type	Unique
XPKSHIPMENT	PK	Yes
XAK1SHIPMENT	AK1	Yes

Child Relationship(s) of "SHIPMENT" Table

FK Name	Type	Cardinality	Child Table
is_used_to	Non-identifying	One-to-One-or-More (P)

Child Table(s) of "is_used_to" Child Relationship

Name

BOOK_ORDER

Parent Relationship(s) of "SHIPMENT" Table

FK Name	Type	Null Option	Cardinality	Parent Table

Primary Key Column(s) of "SHIPMENT" Table

Name	Datatype	Null Option	Is PK	Is FK
Shipment_Type	INTEGER	NOT NULL	Yes	No

TOP

Column(s) of "STATE" Table

Datatype	Null Option	Is PK	Is FK	Name
VARCHAR(3)	NULL	Yes	No	State_Abbr
VARCHAR(20)	NOT NULL	No	No	State_Full_Name

Index(s) of "STATE" Table

Name	Type	Unique
XPKSTATE	PK	Yes

Child Relationship(s) of "STATE" Table

FK Name	Type	Cardinality	Child Table
contain	Non-identifying	One-to-Zero-One-or-More

Child Table(s) of "contain" Child Relationship

Name

ADDRESS

Parent Relationship(s) of "STATE" Table

FK Name	Type	Null Option	Cardinality	Parent Table

Primary Key Column(s) of "STATE" Table

Name	Datatype	Null Option	Is PK	Is FK
State_Abbr	VARCHAR(3)	NULL	Yes	No

TOP

Column(s) of "SUB_CATEGORY" Table

Datatype	Null Option	Is PK	Is FK	Name
INTEGER	NOT NULL	Yes	No	Sub_Category_ID
INTEGER	NOT NULL	No	Yes	Category_ID
VARCHAR(20)	NOT NULL	No	No	Sub_Category_Name
VARCHAR(255)	NULL	No	No	Sub_Category_Description

Index(s) of "SUB_CATEGORY" Table

Name	Type	Unique
XPKSUB_CATEGORY	PK	Yes
XAK1SUB_CATEGORY	AK1	Yes
XIF67SUB_CATEGORY	IF67	No

Child Relationship(s) of "SUB_CATEGORY" Table

FK Name	Type	Cardinality	Child Table
include	Non-identifying	One-to-Zero-One-or-More

Child Table(s) of "include" Child Relationship

Name

BOOK

Parent Relationship(s) of "SUB_CATEGORY" Table

FK Name	Type	Null Option	Cardinality	Parent Table
Include	Non-identifying	No Nulls	One-to-Zero-One-or-More

Parent Table(s) of "Include" Parent Relationship

Name