createschemahsql.sql

基于struts框架的web开发应用实例

CREATE TABLE STOREFRONT_ADDRESS_BOOK (
  PROFILE_ID       NUMERIC (30)   NOT NULL,
  USER_ID          NUMERIC (30),
  PROFILE_PRIMARY  CHAR (5),
  CONSTRAINT XPKSTOREFRONT_ADDRESS_BOOK
  PRIMARY KEY ( PROFILE_ID )
);

-- ALTER TABLE STOREFRONT_ADDRESS_BOOK ADD CONSTRAINT TRUE_FALSE161A CHECK  (PROFILE_PRIMARY IN ('true', 'false') );

CREATE INDEX XIF463STOREFRONT_ADDRESS_BOOK ON
STOREFRONT_ADDRESS_BOOK(USER_ID)
;


CREATE TABLE STOREFRONT_ORDER (
  ORDER_ID               NUMERIC (30)   NOT NULL,
  ORIGINATOR_PROFILE_ID  NUMERIC (30),
  BILLING_PROFILE_ID     NUMERIC (30),
  SHIPPING_PROFILE_ID    NUMERIC (30),
  STATUS_ID              NUMERIC (30)   NOT NULL,
  PLACEMENT_DATE         VARCHAR (50)  NOT NULL,
  TAX_PRICE              NUMERIC (12,2) NOT NULL,
  SHIPPING_PRICE         NUMERIC (12,2) NOT NULL,
  SUBTOTAL               NUMERIC (12,2) NOT NULL,
  TOTAL                  NUMERIC (12,2) NOT NULL,
  CONSTRAINT XPKSTOREFRONT_ORDER
  PRIMARY KEY ( ORDER_ID )
);


CREATE TABLE STOREFRONT_ORDER_ITEM (
  ORDER_PRODUCT_ID  NUMERIC (30)   NOT NULL,
  PRODUCT_ID        NUMERIC (30)   NOT NULL,
  ORDER_ID          NUMERIC (30)   NOT NULL,
  QUANTITY          NUMERIC (30)   NOT NULL,
  PRODUCT_PRICE     NUMERIC (12,2) NOT NULL,
  DESCRIPTION       VARCHAR (2000)  NOT NULL,
  LINE_NUMBER       NUMERIC,
  CONSTRAINT XPKSTOREFRONT_ORDER_ITEM
  PRIMARY KEY ( ORDER_PRODUCT_ID )
);

CREATE INDEX XIF40STOREFRONT_ORDER_ITEM ON
  STOREFRONT_ORDER_ITEM(ORDER_ID)
;

CREATE INDEX XIF460STOREFRONT_ORDER_ITEM ON
  STOREFRONT_ORDER_ITEM(PRODUCT_ID)
;


CREATE TABLE STOREFRONT_PROFILE (
  PROFILE_ID         NUMERIC (30)   NOT NULL,
  TITLE              VARCHAR (30),
  L_NAME             VARCHAR (30),
  F_NAME             VARCHAR (30),
  MIDDLE_NAME        VARCHAR (30),
  COMPANY            VARCHAR (80),
  PHONE_1            VARCHAR (30),
  ADDRESS_1          VARCHAR (50)  NOT NULL,
  ADDRESS_2          VARCHAR (50),
  CITY               VARCHAR (30)  NOT NULL,
  STATE_CODE         VARCHAR (30)  NOT NULL,
  COUNTRY            VARCHAR (30)  NOT NULL,
  ZIP                VARCHAR (30)  NOT NULL,
  EMAIL_1            VARCHAR (255),
  LANGUAGE_PREF      VARCHAR (10),
  CONSTRAINT XPKSTOREFRONT_PROFILE
  PRIMARY KEY ( PROFILE_ID )
);


CREATE TABLE STOREFRONT_SEQUENCE_GENERATOR (
  SEQUENCE_NAME  VARCHAR (30)  NOT NULL,
  CURRENT_VALUE  NUMERIC (30)   NOT NULL,
  CONSTRAINT XPKSTOREFRONT_SEQ_GEN
  PRIMARY KEY ( SEQUENCE_NAME )
);


CREATE TABLE STOREFRONT_USER (
  USER_ID   NUMERIC (30)   NOT NULL,
  LOGIN_ID  VARCHAR (30)  NOT NULL,
  PASSWORD  VARCHAR (15)  NOT NULL,
  CONSTRAINT XPKSTOREFRONT_USER
  PRIMARY KEY ( USER_ID )
);

CREATE UNIQUE INDEX XAK1STOREFRONT_USER ON
  STOREFRONT_USER(LOGIN_ID)
;

CREATE TABLE STOREFRONT_ARTIST (
  ARTIST_ID    NUMERIC (30)   NOT NULL,
  ARTIST_NAME  VARCHAR (255)  NOT NULL,
  CONSTRAINT XPKSTOREFRONT_ARTIST
  PRIMARY KEY ( ARTIST_ID )
);

CREATE INDEX XIE1STOREFRONT_ARTIST ON
  STOREFRONT_ARTIST(ARTIST_NAME)
;

CREATE TABLE STOREFRONT_GENRE (
  GENRE_ID  NUMERIC (30)   NOT NULL,
  NAME      VARCHAR (255)  NOT NULL,
  CONSTRAINT XPKSTOREFRONT_GENRE
  PRIMARY KEY ( GENRE_ID )
);

CREATE TABLE STOREFRONT_ALBUM (
  PRODUCT_ID       NUMERIC (30)   NOT NULL,
  GENRE_ID         NUMERIC (30)   NOT NULL,
  ARTIST_ID        NUMERIC (30)   NOT NULL,
  NAME             VARCHAR (255)  NOT NULL,
  LARGE_IMAGE_URL  VARCHAR (512),
  SMALL_IMAGE_URL  VARCHAR (512),
  LABEL            VARCHAR (255)  NOT NULL,
  CONSTRAINT XPKSTOREFRONT_ALBUM
  PRIMARY KEY ( PRODUCT_ID )
);

CREATE INDEX XIE1STOREFRONT_ALBUM ON
  STOREFRONT_ALBUM(NAME, PRODUCT_ID)
;

CREATE INDEX XIF265STOREFRONT_ALBUM ON
  STOREFRONT_ALBUM(ARTIST_ID)
;

CREATE INDEX XIF270STOREFRONT_ALBUM ON
  STOREFRONT_ALBUM(GENRE_ID)
;

ALTER TABLE STOREFRONT_ALBUM ADD  CONSTRAINT R_265A
 FOREIGN KEY (ARTIST_ID)
  REFERENCES STOREFRONT_ARTIST (ARTIST_ID) ;

ALTER TABLE STOREFRONT_ALBUM ADD  CONSTRAINT R_270A
 FOREIGN KEY (GENRE_ID)
  REFERENCES STOREFRONT_GENRE (GENRE_ID) ;

CREATE TABLE STOREFRONT_PRODUCT (
  PRODUCT_ID                    NUMERIC (30)   NOT NULL,
  SKU                           VARCHAR (30)  NOT NULL,
  DESCRIPTION                   VARCHAR (2000),
  MSRP_PRICE                    NUMERIC (12,2),
  DERIVED_PRICE                 NUMERIC (12,2),
  DERIVED_SALES_PRICE           NUMERIC (12,2),
  NAME                          VARCHAR (255),
  CONSTRAINT XPKSTOREFRONT_PRODUCT
  PRIMARY KEY ( PRODUCT_ID )
);

CREATE UNIQUE INDEX XAK1STOREFRONT_PRODUCT ON
  STOREFRONT_PRODUCT(SKU)
;