我的Oracle项目需求分析.doc

create table Phone_Info ( phone_Id NUMBER not null, brand_Id NUMBER not null, phone_Weight VARCHAR2(20) not null, market_Price NUMBER(8,2) not null, web_Price NUMBER(8,2) not null, phone_Type VARCHAR2(20) not null, phone_Description VARCHAR2(200) not null, activity_TypeId NUMBER not null, admin_Id NUMBER default (1) , Phone_Amount NUMBER not null, Phone_FunctionId number not null, Phone_appearance VARCHAR2(60) , Phone_suitPeople VARCHAR2(60) not null, Phone_netTypeID NUMBER not null, Phone_recommend NUMBER not null, Phone_remark VARCHAR2(100), Clicks int , marketTime date, phone_service varchar(66), phone_StateId int, phone_MerchantAddress varchar(77), IsTicketId int,constraint PK_PHONE_INFO primary key (phone_Id)partition by range(web_Price)( partition P090301 values less than (300), partition P090601 values less than (600), partition P090901 values less than (900), partition P091201 values less than (1200), partition P100301 values less than (1500), partition P100601 values less than (1800), partition P100901 values less than (2100), partition P101201 values less than (3000), partition P110301 values less than (3500), partition P110601 values less than (maxvalue);create table Customer_Info ( customer_Id VARCHAR(20) not null, customer_Name VARCHAR2(40) not null, customer_Adr VARCHAR2(100) not null, customer_Con VARCHAR2(50) not null, customer_QQ VARCHAR2(15), customer_Email VARCHAR2(19), customer_MailCode VARCHAR2(40), admin_Id SMALLINT default (1), customerPass VARCHAR(20), identification_card varchar(18) not null, Customer_stateId int null, score int null, honest int null, OrderPay number null, IsVipId int null, constraint PK_CUSTOMER_INFO primary key (customer_Id);create table Brand_Info ( brand_Id NUMBER not null, brand_Name VARCHAR2(20) not null, brand_Logo VARCHAR2(30) not null, constraint PK_BRAND_INFO primary key (brand_Id);create table Activity_Info ( activity_TypeId NUMBER not null, activity_Name VARCHAR2(20) not null, constraint PK_ACTIVITY_INFO primary key (activity_TypeId);create table Messages_Info ( messages_Id NUMBER not null, messages_Content VARCHAR2(400) not null, messages_IpAdr VARCHAR2(20) not null, messages_Time DATE not null, customer_Id VARCHAR(20) not null, constraint PK_MESSAGES_INFO primary key (messages_Id);create table Phone_review ( review_Id NUMBER not null, phone_Id NUMBER not null, review_Time DATE not null, review_IpAddress VARCHAR2(40) not null, review_Content VARCHAR2(400) not null, constraint PK_PHONE_REVIEW primary key (review_Id);create table Buy_Info ( buy_Id NUMBER not null, phone_Id NUMBER not null, order_Count NUMBER not null, order_Id NUMBER not null, constraint PK_BUY_INFO primary key (buy_Id);create table Order_Info ( order_Id NUMBER not null, customer_Id VARCHAR2(20) not null, order_Price NUMBER not null, Order_StateId NUMBER not null, admin_Id NUMBER not null, order_Time DATE , Ispublic INT, constraint PK_ORDER_INFO primary key (order_Id) partition by range(order_Time) ( partition T090301 values less than (TO_DATE(2009-03-01,YYYY-MM-DD), partition T090601 values less than (TO_DATE(2009-06-01,YYYY-MM-DD), partition T090901 values less than (TO_DATE(2009-09-01,YYYY-MM-DD), partition T091201 values less than (TO_DATE(2009-12-01,YYYY-MM-DD), partition T100301 values less than (TO_DATE(2010-03-01,YYYY-MM-DD), partition T100601 values less than (TO_DATE(2010-06-01,YYYY-MM-DD), partition T100901 values less than (TO_DATE(2010-09-01,YYYY-MM-DD), partition T101201 values less than (TO_DATE(2010-12-01,YYYY-MM-DD), partition T110301 values less than (TO_DATE(2011-03-01,YYYY-MM-DD), partition T110601 values less than (TO_DATE(2011-06-01,YYYY-MM-DD), partition T110901 values less than (TO_DATE(2011-09-01,YYYY-MM-DD), partition T111201 values less than (TO_DATE(2011-12-01,YYYY-MM-DD), partition T120301 values less than (TO_DATE(2012-03-01,YYYY-MM-DD), partition T120601 values less than (TO_DATE(2012-06-01,YYYY-MM-DD), partition T120901 values less than (TO_DATE(2012-09-01,YYYY-MM-DD) );create table PayType_Info ( payType_Id NUMBER not null, payType_Name VARCHAR2(20) not null, constraint PK_PAYTYPE_INFO primary key (payType_Id);create table Phone_ImageInfo ( phone_imageId int not null, phone_imagePic VARCHAR2(50), phone_Id number , constraint PK_PHONE_IMAGEINFO primary key (phone_imageId);create table Admin_Info ( admin_Id NUMBER not null, admin_Name VARCHAR2(30) not null, admin_Pass VARCHAR2(16) not null, adminType_Id NUMBER not null, constraint PK_ADMIN_INFO primary key (admin_Id);create table Bank_Info ( account VARCHAR2(19) not null, UserPass VARCHAR2(6), salary INT, identification_card varchar(18) not null, constraint PK_BANK_INFO primary key (account);create table AdminType_Info ( adminType_Id NUMBER not null, adminType_Name VARCHAR2(50) not null, constraint PK_adminType primary key (adminType_Id);create table Phone_FunctionInfo(Phone_FunctionId number,Phone_FunctionName varchar(15),constraint PK_FunctionInfo primary key (Phone_FunctionId);create table Phone_netType(PHONE_NETTYPEID number primary KEY,PHONE_NETTYPENAME VARCHAR2(20); create table phone_sizeInfo( phone_sizeId number primary key, phone_size varchar(66), phone_Id number);create table phone_ColorInfo ( phone_colorId int primary key, phone_showColor varchar(20), phone_Id int);create table phone_TransType( phone_TransTypeId int primary key, phone_TransTypeName varchar(55), Phone_TransPrice number(10); create table Phone_StateInfo ( phone_StateId int primary key, phone_StateaName varchar(55) ) ; create table IsSendTicket ( IsTicketId int primary key, IsTicketName varchar(44) ); create table Is_VipCustomer ( IsVipId int primary key, IsVipUser varchar(10) ); create table Customer_Log ( Customer_LoginID number primary key, customer_Id varchar(20), StartTime date, LeaveTime date, OnlineTime int ); create table Customer_StateInfo ( Customer_stateId int primary key, Customer_stateName varchar(20) ); create table Order_State ( Order_StateId int primary key, Order_State varchar(55) ); create table Is_Public ( Is_PubId int primary key, Is_PubState varchar(55) );create table OrderLog_Info ( Order_LogId varchar(30) not null, customer_Id VARCHAR2(20) not null, order_Price NUMBER not null, Order_StateId NUMBER not null, admin_Id NUMBER not null, order_Time DATE , Ispublic INT, order_Id NUMBER not null, constraint PK_OrderLog_Info primary key (Order_LogId);alter table OrderLog_Info add constraint FK_OrderLog_Info foreign key (order_Id) references Order_Info(order_Id);alter table Customer_Info add constraint UQ_customer_Id UNIQUE(identification_card); alter table Phone_Info add constraint UQ_Phone_Type UNIQUE(phone_Type); alter table Phone_Info add constraint FK_Phone_FunctionInfo foreign key (Phone_FunctionId) references Phone_FunctionInfo(Phone_FunctionId);alter table Phone_Info add constraint FK_PHONE_BRAND_IN foreign key (brand_Id) references Brand_Info (brand_Id);alter table Customer_Log add constraint Customer_LoginID foreign key (customer_Id) references Customer_Info(customer_Id);alter table Phone_review add constraint FK_Phone_review foreign key (phone_Id) references Phone_Info (phone_Id);alter table Buy_Info add constraint FK_Buy_Info foreign key (phone_Id) references Phone_Info (phone_Id);alter table Buy_Info add constraint ck_orderCount check(order_Count0);alter table Phone_ImageInfo add constraint FK_Phone_ImageInfo foreign key (phone_Id) references Phone_Info(phone_Id);alter table phone_sizeInfo add constraint FK_phone_size foreign key (phone_Id) references phone_Info (phone_Id);alter table phone_ColorInfo add constraint FK_phone_color foreign key (phone_Id) references Phone_Info(phone_Id);alter table Phone_Info add constraint FK_activityInfo foreign key (activity_TypeId)references Activity_Info (activity_TypeId);alter table Admin_Info add constraint FK_admin_Type foreign key (adminType_Id) references AdminType_Info (adminType_Id);alter table Phone_Info add constraint FK_phone_Admin_Id foreign key (admin_Id) references Admin_Info (admin_Id);alter table Phone_Info add constraint FK_phone_netType foreign key (Phone_netTypeID) references Phone_NetType(Phone_netTypeID);alter table Phone_Info add constraint FK_phone_StateInfo foreign key (phone_StateId) references phone_StateInfo (phone_StateId);alter table phone_Info add constraint FK_IsSendTicket foreign key (IsTicketId) references IsSendTicket(IsTicketId);alter table Messages_Info add constraint FK_Message foreign key (customer_Id) references Customer_Info(customer_Id);alter table Order_Info add constraint FK_Order_Info foreign key (customer_Id) references Customer_Info (customer_Id);alter table Customer_Info add constraint FK_Cutomer_StateInfo foreign key (Customer_stateId) references Customer_StateInfo(Customer_stateId);alter table Order_Info add constraint FK_Is_Public foreign key (Ispublic) references Is_Public(Is_PubId);alter table Customer_Info add constraint FK_Is_VipCustomer foreign key (IsVipId) references Is_VipCustomer(IsVipId); comment on table Customer_Info is 用户信息;comment on table Brand_Info is 品牌信息;comment on table Activity_Info is 活动类型信息;comment on table Messages_Info is 网站评论;comment on table Phone_review is 手机评论;comment on table Buy_Info is 购买信息;comment on table Order_Info is 订单信息;comment on table PayType_Info is 支付类型信息;comment on table Phone_ImageInfo is 图片信息;comment on table Admin_Info is 管理员信息;comment on table Bank_Info is 银行支付通;comment on table AdminType_Info is 管理员类型;comment on table Phone_FunctionInfo is 功能类型;comment on table Phone_netType is 网络制式;comment on table phone_sizeInfo is 尺寸信息;comment on table phone_ColorInfo is 颜色信息;comment on table phone_TransType is 尺产品运输类型信息;comment on table Phone_StateInfo is 手机状态信息;comment on table IsSendTicket is 是否有收据;comment on table Is_VipCustomer is 是否vip信息;comment on table Customer_Log is 尺用户日志信息信息;comment on table Customer_StateInfo is 用户状态信息;comment on table Order_State is 订单状态;comment on table Is_Public is 是否公开客户订单信息;create sequence sq_phone start with 1 increment by 1;create sequence sq_brand start with 1 increment by 1;create sequence sq_activity start with 1 increment by 1;create sequence sq_messages start with 1 increment by 1;create sequence sq_review start with 1 increment by 1;create sequence sq_Buy start with 1 increment by 1;create sequence sq_admin start with 1 increment by 1;create sequence sq_Order start with 1 increment by 1;create sequence sq_PayType start with 1 increment by 1;create sequence sq_Image start with 1 increment by 1;create sequence sq_adminType start with 1 increment by 1;create sequence sq_functionInfo start with 1 increment by 1;create sequence sq_netType start with 1 increment by 1; create sequence sq_phone_sizeInfo start with 1 increment by 1; create sequence sq_phone_ColorInfo start with 1 increment by 1; create sequence sq_phone_TransType start with 1 increment by 1; create sequence sq_Phone_StateInfo start with 1 increment by 1; create sequence sq_IsSendTicket start with 1 increment by 1; create sequence sq_Is_VipCustomer start with 1 increment by 1; create sequence sq_Customer_Log start with 1 increment by 1; create sequence sq_Customer_StateInfo start with 1 increment by 1; create sequence sq_Order_State start with 1 increment by 1; create sequence sq_Is_Public start with 1 increment by 1;create or replace trigger tri_phone before insert or update of phone_Id on phone_Info for each rowbegin if inserting then select sq_phone.nextval into:NEW.phone_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); end if;end;create or replace trigger tri_brand before insert or update of brand_Id on Brand_Info for each rowbegin if inserting then select sq_brand.nextval into:NEW.brand_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF;END;create or replace trigger tri_activity before insert or update of activity_TypeId on Activity_Info for each rowbegin if inserting then select sq_activity.nextval into:NEW.activity_TypeId from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace);END IF; END;create or replace trigger tri_messages before insert or update of messages_Id on Messages_Info for each rowbegin if inserting then select sq_messages.nextval into:NEW.messages_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF; END;create or replace trigger tri_review before insert or update of review_Id on Phone_review for each rowbegin if inserting then select sq_review.nextval into:NEW.review_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF; END;create or replace trigger tri_Buy before insert or update of buy_Id on Buy_Info for each rowbegin if inserting then select sq_Buy.nextval into:NEW.buy_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF; END;create or replace trigger tri_Order before insert or update of order_Id on Order_Info for each rowbegin if inserting then select sq_Order.nextval into:NEW.order_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF; END;create or replace trigger tri_PayType before insert or update of payType_Id on PayType_Info for each rowbegin if inserting then select sq_PayType.nextval into:NEW.payType_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF;END;create or replace trigger tri_Image before insert or update of Phone_imageId on Phone_ImageInfo for each rowbegin if inserting then select sq_Image.nextval into:NEW.Phone_imageId from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF;END;create or replace trigger tri_admin before insert or update of admin_Id on Admin_Info for each rowbegin if inserting then select sq_admin.nextval into:NEW.admin_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF;END;create or replace trigger tri_adminType before insert or update of adminType_Id on AdminType_Info for each rowbegin if inserting then select sq_adminType.nextval into:NEW.adminType_Id from dual; else RAISE_APPLICATION_ERROR(-20020, Primary key cannot be replace); END IF;END;create or replace trigger tri_functionInfo before insert or upda