《我的Oracle项目需求分析.doc》由会员分享，可在线阅读，更多相关《我的Oracle项目需求分析.doc（45页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、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 N
2、UMBER 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 varcha
3、r(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 va
4、lues 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 Cus
5、tomer_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),
6、 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 VARCHA
7、R2(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 VAR
8、CHAR2(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
9、 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
10、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 le
11、ss 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,
12、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 T1106
13、01 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
14、(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, pho
15、ne_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);
16、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
17、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
18、_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);
19、 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_Log
20、inID 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
21、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
22、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_P
23、hone_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 Custome
24、r_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 Phon
25、e_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
26、_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_adm
27、in_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(Ph
28、one_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 F
29、K_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 Custo
30、mer_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
31、 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 图片信息;co
32、mment 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_T
33、ransType 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
34、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 sequenc
35、e 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 increme
36、nt 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 i
37、ncrement 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