createschema.sql

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

CREATE TABLE STOREFRONT_ADDRESS_BOOK (
  PROFILE_ID       NUMBER (30)   NOT NULL,
  USER_ID          NUMBER (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               NUMBER (30)   NOT NULL,
  ORIGINATOR_PROFILE_ID  NUMBER (30),
  BILLING_PROFILE_ID     NUMBER (30),
  SHIPPING_PROFILE_ID    NUMBER (30),
  STATUS_ID              NUMBER (30)   NOT NULL,
  PLACEMENT_DATE         VARCHAR2 (50)  NOT NULL,
  TAX_PRICE              NUMBER (12,2) NOT NULL,
  SHIPPING_PRICE         NUMBER (12,2) NOT NULL,
  SUBTOTAL               NUMBER (12,2) NOT NULL,
  TOTAL                  NUMBER (12,2) NOT NULL,
  CONSTRAINT XPKSTOREFRONT_ORDER
  PRIMARY KEY ( ORDER_ID )
);


CREATE TABLE STOREFRONT_ORDER_ITEM (
  ORDER_PRODUCT_ID  NUMBER (30)   NOT NULL,
  PRODUCT_ID        NUMBER (30)   NOT NULL,
  ORDER_ID          NUMBER (30)   NOT NULL,
  QUANTITY          NUMBER (30)   NOT NULL,
  PRODUCT_PRICE     NUMBER (12,2) NOT NULL,
  DESCRIPTION       VARCHAR2 (2000)  NOT NULL,
  LINE_NUMBER       NUMBER,
  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         NUMBER (30)   NOT NULL,
  TITLE              VARCHAR2 (30),
  L_NAME             VARCHAR2 (30),
  F_NAME             VARCHAR2 (30),
  MIDDLE_NAME        VARCHAR2 (30),
  COMPANY            VARCHAR2 (80),
  PHONE_1            VARCHAR2 (30),
  ADDRESS_1          VARCHAR2 (50)  NOT NULL,
  ADDRESS_2          VARCHAR2 (50),
  CITY               VARCHAR2 (30)  NOT NULL,
  STATE_CODE         VARCHAR2 (30)  NOT NULL,
  COUNTRY            VARCHAR2 (30)  NOT NULL,
  ZIP                VARCHAR2 (30)  NOT NULL,
  EMAIL_1            VARCHAR2 (255),
  LANGUAGE_PREF      VARCHAR2 (10),
  CONSTRAINT XPKSTOREFRONT_PROFILE
  PRIMARY KEY ( PROFILE_ID )
);


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


CREATE TABLE STOREFRONT_USER (
  USER_ID   NUMBER (30)   NOT NULL,
  LOGIN_ID  VARCHAR2 (30)  NOT NULL,
  PASSWORD  VARCHAR2 (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    NUMBER (30)   NOT NULL,
  ARTIST_NAME  VARCHAR2 (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  NUMBER (30)   NOT NULL,
  NAME      VARCHAR2 (255)  NOT NULL,
  CONSTRAINT XPKSTOREFRONT_GENRE
  PRIMARY KEY ( GENRE_ID )
);

CREATE TABLE STOREFRONT_ALBUM (
  PRODUCT_ID       NUMBER (30)   NOT NULL,
  GENRE_ID         NUMBER (30)   NOT NULL,
  ARTIST_ID        NUMBER (30)   NOT NULL,
  NAME             VARCHAR2 (255)  NOT NULL,
  LARGE_IMAGE_URL  VARCHAR2 (512),
  SMALL_IMAGE_URL  VARCHAR2 (512),
  LABEL            VARCHAR2 (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                    NUMBER (30)   NOT NULL,
  SKU                           VARCHAR2 (30)  NOT NULL,
  DESCRIPTION                   VARCHAR2 (2000),
  MSRP_PRICE                    NUMBER (12,2),
  DERIVED_PRICE                 NUMBER (12,2),
  DERIVED_SALES_PRICE           NUMBER (12,2),
  NAME                          VARCHAR2 (255),
  CONSTRAINT XPKSTOREFRONT_PRODUCT
  PRIMARY KEY ( PRODUCT_ID )
);

CREATE UNIQUE INDEX XAK1STOREFRONT_PRODUCT ON
  STOREFRONT_PRODUCT(SKU)
;