Defining the database schema

In this step, you customize the physical layer by adding tables to contain recipe information in the WebSphere Commerce database schema.

About this task

The following diagram identifies the changes to the WebSphere Commerce database schema. The new tables that you add to the schema in this lesson are highlighted within the image:

Diagram displaying the Recipes tool schema customization.

Procedure

  1. Start the WebSphere Commerce test server.
  2. Open a new browser window and enter the following URL: http://localhost/webapp/wcs/admin/servlet/db.jsp
  3. In the text box, type:
    Apache DerbyDB2
    CREATE TABLE XPROJECT
(XPROJECT_ID BIGINT NOT NULL,
TIME INTEGER ,
DIFFICULTY INTEGER ,
PRJNAME VARCHAR(64) NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPROJECT PRIMARY KEY (XPROJECT_ID),
CONSTRAINT F_XPROJECT FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE,
CONSTRAINT I_XPROJECT UNIQUE (PRJNAME)
);

CREATE TABLE XPRJDES (
XPROJECT_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(254),
LONGDESC VARCHAR(1024),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJDES PRIMARY KEY (XPROJECT_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJDES1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJMTR (
XPRJMTR_ID BIGINT NOT NULL,
XPROJECT_ID BIGINT NOT NULL,
PRJMTRNAME VARCHAR(64) NOT NULL,
AMOUNT DOUBLE,
QTYUNIT_ID CHAR(16),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJMTR PRIMARY KEY (XPRJMTR_ID),
CONSTRAINT F_XPRJMTR1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJMTRDES (
XPRJMTR_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(254),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJMTRDES PRIMARY KEY (XPRJMTR_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJMTRDES1 FOREIGN KEY (XPRJMTR_ID) 
REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJMTRDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJMTRCATREL (
XPRJMTR_ID BIGINT NOT NULL,
CATENTRY_ID BIGINT NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJMTRCATREL PRIMARY KEY (XPRJMTR_ID,STOREENT_ID ),
CONSTRAINT F_XPRJMTRCATREL1 FOREIGN KEY (XPRJMTR_ID) 
REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJMTRCATREL2 FOREIGN KEY (CATENTRY_ID) 
REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJMTRCATREL3 FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJINS(
XPRJINS_ID BIGINT NOT NULL,
XPROJECT_ID BIGINT NOT NULL,
SEQ INTEGER,
ISOPTIONAL INTEGER,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJINS PRIMARY KEY (XPRJINS_ID),
CONSTRAINT F_XPRJINS FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJINSDES (
XPRJINS_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(1024),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJINSDES PRIMARY KEY (XPRJINS_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJINSDES1 FOREIGN KEY (XPRJINS_ID) 
REFERENCES XPRJINS(XPRJINS_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJINSDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJCATREL (
XPROJECT_ID BIGINT NOT NULL,
CATENTRY_ID BIGINT NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJCATREL PRIMARY KEY (XPROJECT_ID,CATENTRY_ID ),
CONSTRAINT F_XPRJCATREL1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJCATREL2 FOREIGN KEY (CATENTRY_ID) 
REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJCATREL3 FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJCOL
(XPRJCOL_ID BIGINT NOT NULL,
PRJCOLNAME VARCHAR(64) NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJCOL PRIMARY KEY (XPRJCOL_ID),
CONSTRAINT F_XPRJCOL FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE,
CONSTRAINT I_XPRJCOL UNIQUE (PRJCOLNAME)
);

CREATE TABLE XPRJCOLDES (
XPRJCOL_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(254),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJCOLDES PRIMARY KEY (XPRJCOL_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJCOLDES1 FOREIGN KEY (XPRJCOL_ID) 
REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJCOLDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJPRJCOLREL (
XPROJECT_ID BIGINT NOT NULL,
XPRJCOL_ID BIGINT NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJPRJCOLREL PRIMARY KEY (XPROJECT_ID,XPRJCOL_ID ),
CONSTRAINT F_XPRJPRJCOLREL1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJPRJCOLREL2 FOREIGN KEY (XPRJCOL_ID) 
REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJPRJCOLREL3 FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE
);
    Oracle
    CREATE TABLE XPROJECT 
(XPROJECT_ID NUMBER NOT NULL, 
TIME INTEGER , 
DIFFICULTY INTEGER , 
PRJNAME VARCHAR2(64) NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPROJECT PRIMARY KEY (XPROJECT_ID), 
CONSTRAINT F_XPROJECT FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE, 
CONSTRAINT I_XPROJECT UNIQUE (PRJNAME) 
); 

CREATE TABLE XPRJDES ( 
XPROJECT_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(254), 
LONGDESC VARCHAR2(1024), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJDES PRIMARY KEY (XPROJECT_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJDES1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJMTR ( 
XPRJMTR_ID NUMBER NOT NULL, 
XPROJECT_ID NUMBER NOT NULL, 
PRJMTRNAME VARCHAR2(64) NOT NULL, 
AMOUNT NUMBER, 
QTYUNIT_ID VARCHAR2(16), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJMTR PRIMARY KEY (XPRJMTR_ID), 
CONSTRAINT F_XPRJMTR1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJMTRDES ( 
XPRJMTR_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(254), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJMTRDES PRIMARY KEY (XPRJMTR_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJMTRDES1 FOREIGN KEY (XPRJMTR_ID) 
REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJMTRDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJMTRCATREL ( 
XPRJMTR_ID NUMBER NOT NULL, 
CATENTRY_ID NUMBER NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJMTRCATREL PRIMARY KEY (XPRJMTR_ID,STOREENT_ID ), 
CONSTRAINT F_XPRJMTRCATREL1 FOREIGN KEY (XPRJMTR_ID) 
REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJMTRCATREL2 FOREIGN KEY (CATENTRY_ID) 
REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJMTRCATREL3 FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJINS( 
XPRJINS_ID NUMBER NOT NULL, 
XPROJECT_ID NUMBER NOT NULL, 
SEQ INTEGER, 
ISOPTIONAL INTEGER, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJINS PRIMARY KEY (XPRJINS_ID), 
CONSTRAINT F_XPRJINS FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJINSDES ( 
XPRJINS_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(1024), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJINSDES PRIMARY KEY (XPRJINS_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJINSDES1 FOREIGN KEY (XPRJINS_ID) 
REFERENCES XPRJINS(XPRJINS_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJINSDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJCATREL ( 
XPROJECT_ID NUMBER NOT NULL, 
CATENTRY_ID NUMBER NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJCATREL PRIMARY KEY (XPROJECT_ID,CATENTRY_ID ), 
CONSTRAINT F_XPRJCATREL1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJCATREL2 FOREIGN KEY (CATENTRY_ID) 
REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJCATREL3 FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJCOL 
(XPRJCOL_ID NUMBER NOT NULL, 
PRJCOLNAME VARCHAR2(64) NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJCOL PRIMARY KEY (XPRJCOL_ID), 
CONSTRAINT F_XPRJCOL FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE, 
CONSTRAINT I_XPRJCOL UNIQUE (PRJCOLNAME) 
); 

CREATE TABLE XPRJCOLDES ( 
XPRJCOL_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(254), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJCOLDES PRIMARY KEY (XPRJCOL_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJCOLDES1 FOREIGN KEY (XPRJCOL_ID) 
REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJCOLDES2 FOREIGN KEY (LANGUAGE_ID) 
REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJPRJCOLREL ( 
XPROJECT_ID NUMBER NOT NULL, 
XPRJCOL_ID NUMBER NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJPRJCOLREL PRIMARY KEY (XPROJECT_ID,XPRJCOL_ID ), 
CONSTRAINT F_XPRJPRJCOLREL1 FOREIGN KEY (XPROJECT_ID) 
REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJPRJCOLREL2 FOREIGN KEY (XPRJCOL_ID) 
REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJPRJCOLREL3 FOREIGN KEY (STOREENT_ID) 
REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE 
);
  4. Click Submit Query.
  5. Add entries to the KEYS table for the newly created tables.
    In the text box, type:
    
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) 
VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPROJECT', 'XPROJECT_ID', 10000);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) 
VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPRJMTR', 'XPRJMTR_ID', 10000);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) 
VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPRJINS', 'XPRJINS_ID', 10000);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) 
VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPRJCOL', 'XPRJCOL_ID', 10000);
  6. Click Submit Query.

Results

The following tables provide additional information about the Project schema, and are provided as a reference:
XPROJECT
This table holds Project information.
Column name Data type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project. PK
TIME INTEGER The time used for the project.
DIFFICULTY INTEGER The difficulty level for the project.
PRJNAME VARCHAR(64) NOT NULL The name of this project.
STOREENT_ID INTEGER NOT NULL The store that the project belongs to. FK to STOREENT table.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJDES
This table holds language-dependent information related to a project.
Column name Data type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project. PK, FK to XPROJECT table.
LANGUAGE_ID INTEGER NOT NULL The identifier of the language. PK, FK to LANGUAGE table.
SHORTDESC VARCHAR(254) The short description for the project.
LONGDESC VARCHAR(1024) The long description for the project.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJMTR
This table holds material information that is related to a Project.
Column name Data type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project. FK to XPROJECT table.
XPRJMTR_ID BIGINT NOT NULL The identifier for the project material. PK
PRJMTRNAME VARCHAR(64) NOT NULL The name of the material.
AMOUNT DOUBLE The amount of the material.
QTYUNIT_ID CHAR(16) The quantity unit of the material.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJMTRDES
This table holds material-related language-dependent information.
Column name Data type Description Constraint (PK, FK)
XPRJMTR_ID BIGINT NOT NULL The identifier for the project material. PK, FK to XPRJMTR table.
LANGUAGE_ID INTEGER NOT NULL The identifier of the language. PK, FK to LANGUAGE table.
SHORTDESC VARCHAR(254) The short description for the material.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJMTRCATREL
This table contains relationships between materials and catalog entries.
Column name Data type Description Constraint (PK, FK)
XPRJMTR_ID BIGINT NOT NULL The identifier for the project material. PK, FK to XPRJMTR table.
CATENTRY_ID BIGINT NOT NULL The identifier of the catentry. FK to CATENTRY table.
STOREENT_ID INTEGER NOT NULL The store that the relationship belongs to. PK, FK to STOREENT table.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJINS
This table holds instruction information that is related to a project.
Column name Data type Description Constraint (PK, FK)
XPRJINS_ID BIGINT NOT NULL The identifier for the project instruction. PK
XPROJECT_ID BIGINT NOT NULL The identifier for the project. FK to XPROJECT table.
SEQ INTEGER The sequence number for the project instruction.
ISOPTIONAL INTEGER If the step is optional.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJINSDES
This table holds language-dependent information that is related to an instruction.
Column name Data type Description Constraint (PK, FK)
XPRJINS_ID BIGINT NOT NULL The identifier for the project instruction. PK, FK to XPRJINS table.
LANGUAGE_ID INTEGER NOT NULL The identifier of the language. PK, FK to LANGUAGE table.
SHORTDESC VARCHAR(1024) The short description for the material.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJCATREL
This table hold the relationship between project and catentry.
Column name Data type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project. PK, FK to XPROJECT table.
CATENTRY_ID BIGINT NOT NULL The identifier of the catentry. PK,FK to CATENTRY table.
STOREENT_ID INTEGER NOT NULL The store that the relationship belongs to. FK to STOREENT table.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJCOL
This table holds information that is related to a project collection.
Column name Data type Description Constraint (PK, FK)
XPRJCOL_ID BIGINT NOT NULL The identifier for the project collection. PK
PRJCOLNAME VARCHAR(64) NOT NULL The name of this project collection.
STOREENT_ID INTEGER NOT NULL The store that the project collection belongs to. FK to STOREENT table.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJCOLDES
This table holds language-dependent information that is related to a project collection.
Column name Data type Description Constraint (PK, FK)
XPRJCOL_ID BIGINT NOT NULL The identifier for the project collection. PK, FK to XPRJCOL table.
LANGUAGE_ID INTEGER NOT NULL The identifier of the language. PK, FK to LANGUAGE table.
SHORTDESC VARCHAR(254) The short description for the project collection.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJPRJCOLREL
This table holds the relationship between project and project collection.
Column name Data type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project. PK, FK to XPROJECT table.
XPRJCOL_ID BIGINT NOT NULL The identifier of the project collection. PK,FK to XPRJCOL table.
STOREENT_ID INTEGER NOT NULL The store that the relationship belongs to. FK to STOREENT table.
OPTCOUNTER SMALLINT Reserved for IBM internal use.