create_shopschema.sql

Sun公司Dream项目

create table OPERA_SHOP_INFO(SHOPID VARCHAR(30) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(30) NOT NULL,NAME VARCHAR(30) NOT NULL,URL VARCHAR(30) NOT NULL);
CREATE TABLE OPERA_CONTENT_METADATA(CONTENTID VARCHAR(30) NOT NULL PRIMARY KEY,TITLE VARCHAR(30),DESCRIPTION VARCHAR(100),PUBLISHER VARCHAR(30),ARTIST VARCHAR(30),CREATIONDATA VARCHAR(30),LANGUAGE VARCHAR(30),COUNTRY VARCHAR(30),RUNTIME VARCHAR(30),PREVIEWFILE VARCHAR(100),THUMBNAIL1 VARCHAR(100),THUMBNAIL2 VARCHAR(100),CONTENTLOC VARCHAR(30),DATATYPE VARCHAR(30),RESOLUTION VARCHAR(30),BITRATE VARCHAR(30),MIMETYPE VARCHAR(100),RATING VARCHAR(30),DRMTYPE VARCHAR(30),DELIVERYMODE VARCHAR(30),RELPROD VARCHAR(30),PASSWORD VARCHAR(30));
CREATE TABLE OPERA_COUNT(USERNUMBER INTEGER,SHOPNUMBER INTEGER);
CREATE TABLE OPERA_USER_INFO(USERID VARCHAR(30) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(30) NOT NULL,PHONE VARCHAR(15) NOT NULL,SIM VARCHAR(30) NOT NULL,USERNAME VARCHAR(30),EMAIL VARCHAR(50));
//CREATE TABLE OPERA_LICENSE_INFO(LICENSETYPE INTEGER,USERID VARCHAR(30) NOT NULL,SHOPID VARCHAR(30) NOT NULL,CONTENTID VARCHAR(30) NOT NULL,TOTAL INTEGER NOT NULL,REMAINING INTEGER NOT NULL,STARTTIME TIMESTAMP,ENDTIME TIMESTAMP,CONSTRAINT SYS_FK_7 FOREIGN KEY(USERID) REFERENCES OPERA_USER_INFO(USERID) ON DELETE CASCADE,CONSTRAINT SYS_FK_8 FOREIGN KEY(SHOPID) REFERENCES OPERA_SHOP_INFO(SHOPID) ON DELETE CASCADE);
CREATE TABLE OPERA_COUPON_INFO(COUPONID VARCHAR(30) NOT NULL PRIMARY KEY,CONTENTID VARCHAR(30),SHOPID VARCHAR(30),VALUE FLOAT,DESCRIPTION VARCHAR(100),EXPIREDATE TIMESTAMP,LICENSETYPE INTEGER,USED INTEGER,CONSTRAINT SYS_FK_13 FOREIGN KEY(SHOPID) REFERENCES OPERA_SHOP_INFO(SHOPID) ON DELETE CASCADE);
CREATE TABLE OPERA_USER_COUPON(COUPONID VARCHAR(30),USERID VARCHAR(30),CONSTRAINT SYS_FK_16 FOREIGN KEY(COUPONID) REFERENCES OPERA_COUPON_INFO(COUPONID) ON DELETE CASCADE,CONSTRAINT SYS_FK_17 FOREIGN KEY(USERID) REFERENCES OPERA_USER_INFO(USERID) ON DELETE CASCADE);
CREATE TABLE SHOP_PRODUCT(CONTENTID VARCHAR(30) NOT NULL PRIMARY KEY,CATEGORY VARCHAR(30),LISTPRICE FLOAT,PRICE FLOAT,FILENAME VARCHAR(50));
CREATE TABLE SHOP_TRANSACTION(TRANSDATE DATE,USERID VARCHAR(30),TOTALPURCHASE FLOAT,PAYMENTMETHOD VARCHAR(30),CONSTRAINT SYS_FK_28 FOREIGN KEY(USERID) REFERENCES OPERA_USER_INFO(USERID) ON DELETE CASCADE);
CREATE TABLE PLACE(CODE INTEGER,NAME VARCHAR(255));
CREATE INDEX ICODE ON PLACE(CODE);
CREATE TABLE CUSTOMER(NR INTEGER,NAME VARCHAR(255),PLACE INTEGER);
CREATE INDEX INR ON CUSTOMER(NR);
CREATE USER DEMO PASSWORD "DEMO";
GRANT PBDBA to DEMO;