1、*中心開發部文檔編 號2011-6-14版 本A1密級商密A項目名 稱時代集團產品跟蹤平臺項目來 源時代集團DR-RD-020(V1.1) 數據 庫設寫:查:核:計說明書（內部資料請勿外傳）期:期:期:準:期:版權所有不得復制時代集團產品跟蹤平臺數據庫設計說明書 1引言1.1編寫目的1.2術語表.1.3參考資料2 數據庫環境說明 3 數據庫的命名規則 4 邏輯設計5 物理設計5.1 表匯總5.2 表X : XXX表5.3 視圖的設計 5.4 存儲過程、函數及觸發器的設計 6 安全性設計6.1 防止用戶直接操作數據庫的方法 6.2 用戶帳號密碼的加密方法 6.3 角色與權限 7 優化8數據庫管理

2、與維護說明1引言1.1 編寫目的本文檔是時代集團產品跟蹤平臺概要設計文檔的組成部分，編寫數據庫設計文檔的目的是：明確數據庫的表名、字段名 等數據信息，用來指導后期的數據庫腳本的開發，本文檔遵循SQL數據庫設計和開發規范 本文檔的讀者對象是需求人員、系統設計人員、開發人員、測試人員。1.2 術語表序號術語或縮略語說明性定義1ESIDEn terStorageld 入庫編號2ProldProductId產品編號3PMIDProductManagerld產品管理員編號1.3 參考資料資料名稱作者文件編號、版本資料存放地點2數據庫環境說明數據庫 實例數據庫系統數據庫 部署環 境數據庫設 計工具數據庫存

3、 放位置說明3數據庫的命名規則數據庫名稱：時代集團的英文名稱time-group 表名：英文（表的用途）+下劃線+英文字段名：相關屬性的英文名4邏輯設計提示：數據庫設計人員根據需求文檔，創建與數據庫相關的那部分實體關系圖（ ERD 如果采用面向對象方法（00A）,這里實體相當于類（class ）。5物理設計提示：（1）主要是設計表結構。一般地，實體對應于表，實體的屬性對應于表的列，實體之間的關系成為表的約束。邏輯設計中的實體大部分可以轉換成物理設計中的表，但是它們并不一定是對應的。（2）對表結構進行規范化處理（第三范式）。5.1 表匯總表名功能說明Enter_storage產品入庫（新產品的登

4、記、修改、查詢）Exit_storage產品岀庫（登記產品岀庫信息）Seller_info銷售商信息維護表（對銷售商信息的添加、修改和查詢）Product_info產品信息表（對新產品的發布，修改，查詢等維護）Repair_info維修表（對已岀售產品的維修進行登記，修改）L管理員表（包含五種身份，銷售人員，庫存管理人員，售后服務人員，產品 信息管理人員，系統管理員）5.2 表:表的索引：索引是否建立要根據具體的業務需求來確定。允許為空：不填的表示為“是”。唯一：不填的表示為“是”。表的記錄數和增長量：根據具體的業務需求確定。增長量應確定單位時間如果量大可以按每天，如果不大可以按每月。表字段的

5、區別度：主要是考慮到將來在此字段上建立索引類型選擇時作為參考，當字段值唯一時可以不考慮，當字段值不唯一時，估算一個區別度，近似即可。 例如：如果一個表的 NAME字段有共2000個值,其中有1999個不同 值,1999/2000=0.99越接近1區別度越高,反之區別度越低。表的并發：根據具體的業務需求預測表的并發。1.表名En ter storage數據庫用戶庫存管理人員主鍵ESID入庫產品編號其他排序字段Product， date，num,storageManagerld索引字段序號字段名稱數據類型（精度范圍）允許為空Y/N唯一Y/N區別 度默認值約束條件/說明1ESIDintNY2Prod

6、uctVarchar(50)NN3dateSmallDateTimeNN4numVarchar(50)NN5storageMa nager IdVarchar(50)NYMysql腳本CREATE TABLE dbo.Enter_storage(ESID int IDENTITY(1,1) NOT NULL,product varchar(50) NOT NULL,date smalldatetime NOT NULL,num varchar(6) NOT NULL,storageManagerId nvarchar(50) NOT NULL,CONSTRAINT PK_addStorage

7、PRIMARY KEY CLUSTERED(ESID ASC)WITH (PADN DEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LO ON) ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO記錄數增長量表的并發補充說明2.表名Exit storage數據庫用戶庫存管理人員主鍵ExitStorageId出庫產口口編號其他排序字段Product， date，num,storageManagerld索引字段序號字

8、段名稱數據類型（精度范圍）允許為空Y/N唯一Y/N區別度默認值約束條件/說明1ExitStorageIdintNY2ProductVarchar(50)NN3dateSmallDateTimeNN4numVarchar(50)NN5storageMa nager IdVarchar(50)NYMysql腳本CREATE TABLE dbo.Exit_storage(ExitStorageldint IDENTITY(1,1) NOTNULL,product varchar(50) NOT NULL, date smalldatetime NOT NULL,n um varchar (6) NO

9、T NULL,CONSTRAINPK_subStorage PRIMARYKEYCLUSTERE (ExitStorageId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPl =OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCK ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY )ON PRIMARYGOSET ANSI_PADDING OFFGO/*Object:Table dbo.E nter_storageScript Date: 01/17/2011 16:00:59 */SET

10、ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發補充說明3.表名Seller i nfo數據庫用戶銷售商管理員主鍵n ame其他排序字段telephone ， address， product, stockNum， stockDate ， stockPrice索引字段序號字段名稱數據類型（精度范圍）允許為空Y/N唯一Y/N區別度默認值約束條件/說明1n ameVarchar(50)NY2telepho neVarchar(50)NN3addressSmallDateTimeNN4productVar

11、char(50)NN5stockNumVarchar(50)NN6stockDat eSmallDateTimeNN7stockPriVarchar(50)NNceMysql腳本CREATE TABLE dbo.Seller_i nfo( n ame varchar(50) NOT NULL, telephone varchar(20) NOT NULL, address varchar(50) NOT NULL, product varchar(50) NOT NULL, stockNum varchar (6) NOT NULL, stockDate smalldatetime NOT N

12、ULL, stockPrice varchar(10) NOT NULL,CONSTRAINT PK_maketer_l nfo PRIMARY KEY CLUSTERE (n ame ASC)WITH (PADN DEX = OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO/* Object: Table dbo.Repair_i nfo ScriptDate:

13、 12/16/2010 09:59:13 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發補充說明4.表名Product i nfo數據庫用戶產品信息管理人員主鍵proId其他排序字段name price ，proDate, storage索引字段序號字段名稱數據類型（精度范圍）允許為空Y/N唯一Y/N區別度默認值約束條件/說明1proIdintNY2n ameVarchar(50)NN3priceVarchar(50)NN4proDateSmallDateTimeNN5storageV

14、archar(50)NYMysql腳本CREATE TABLE dbo.Product_i nfo( prold int IDENTITY(1,1) NOT NULL, n ame varchar(50) NOT NULL, price varchar(6) NOT NULL, proDate smalldatetime NOT NULL, storage varchar NOT NULL,CONSTRAINT PK_product_lnfoPRIMARY KEYCLUSTERED(proId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPl =O

15、FF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCK ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY )ON PRIMARYGOSET ANSI_PADDING OFFGO/* Object: Table dbo.Logi n ScriptDate: 01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發補充說明5.表名Repair i nfo數據庫用戶售后服務人員主鍵repairId產品維修編號其

16、他排序字段repairName， repairtel ， product, customer， date， customertel索引字段序號字段名稱數據類型（精度范圍）允許為空Y/N唯一Y/N區別度默認值約束條件/說明1repairIdintNY2repairNameVarchar(50)NN3repairtelSmallDateTimeNN4productVarchar(50)NN5customerVarchar(50)NNdateSmallDateTimeNNcustomert elVarchar(50)NNMysql腳本CREATE TABLE dbo.Repair_i nfo( re

17、pairld int IDENTITY(1,1) NOT NULL, repairName nchar(10) NULL, repairtel nchar(10) NULL, product varchar(50) NOT NULL, customer varchar(20) NOT NULL, date smalldatetime NOT NULL, customertel nchar(10) NULL,CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (repairId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NOR

18、ECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO/*Object:Tabledbo.Product_i nfoScript Date: 01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發補充說明6.表名Log in數據庫用戶管理人員主鍵ruserId用戶編號

19、其他排序字段password，userName tel, birthday ，registerTime ，type索引字段序號字段名稱數據類型（精度范圍）允許為空Y/N唯一Y/N區別度默認值約束條件/說明1userIdintNY2passwordVarchar(50)NN3userNameSmallDateTimeNN4telVarchar(50)NN5birthdayVarchar(50)NN6registerTi meSmallDateTimeNN7typeVarchar(50)NNMysql腳本CREATE TABLE dbo.Login(userId varchar(10) NOT N

20、ULL,password varchar(16) NOT NULL,userName varchar(20) NOT NULL,tel varchar(20) NOT NULL,birthday smalldatetime NOT NULL,registerTime smalldatetime NOT NULL,type nvarchar(50) NOT NULL,CONSTRAINT PK_manager_lnfo PRIMARY KEY CLUSTERED(userId ASC)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF,IGNOR

21、E_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO記錄數增長量表的并發補充說明5.3 視圖的設計根據XXX產品的概要設計文檔來確定。視圖的命名按照XX數據庫設計規范中關于視圖的命名規范命名 視圖的設計應注意以下幾點：1盡量減少列中使用的公式。2去掉所有不必要的列。3不要使同一個文檔屬于多個分類。4避免使用表單公式。5.4 存儲過程、函數及觸發器的設計存儲過程及觸發器的命名按照XX數據庫設計規范中關于存儲過程及觸發器的命名 規范命名。存儲過程：根

22、據具體得業務邏輯確定輸入參數個數，類型，確定對哪幾個表進行何種作。在定義存儲過程時，要使用其完成單一、相對集中的任務，不要定義已由其他提供功能的過程。例如：不要定義強制數據完整性的過程（使用完整性約束)。函數 ：函數與存儲過程非常相似，它也是存儲在數據庫中的對象。但是可以在 SQL 命令中使用函數。就好像建立自己的 substr 函數一樣觸發器 ：觸發器是存儲在數據庫中的程序，它在某一特定事件發生時執行。這些程 序可以用PL/SQL和java語言編寫，也可以用作c語言的調用，數據庫允許用 戶定義這些程序，然后在相關的表，視圖或者數據庫動作執行insert ，update 或 delete 語句

23、時執行。 1. 存儲過程：CREATE PROCEDURE Enter_storage_GetMaxIdASDECLARE TempID int SELECT TempID = max( ESID )+ 1 FROM Enter_storage IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Enter_storage_ExistsESID intASDECLARE TempID intSELECT TempID = count ( 1) FROM Enter_storage WHERE ESID =ESID IF Temp

24、ID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Enter_storage_ADD ESID int output ,product varchar ( 50 ),date smalldatetime ,num varchar ( 6),storageManagerId nvarchar ( 50)ASINSERT INTO Enter_storage ( product , date , num , storageManagerId ) VALUES (product , date , num, storageManagerId)SET ESID = I

25、DENTITY CREATE PROCEDURE Enter_storage_Update ESID int ,product varchar ( 50 ),date smalldatetime , num varchar ( 6), storageManagerId nvarchar ( 50)ASUPDATE Enter_storage SET product = product , date = date , num = num, storageManagerId storageManagerIdWHERE ESID =ESIDCREATE PROCEDURE Enter_storage

26、_DeleteESID intASDELETE Enter_storage WHERE ESID =ESIDCREATE PROCEDURE Enter_storage_GetModelESID intASSELECTESID , product , date , num, storageManagerIdFROM Enter_storage WHERE ESID =ESIDCREATE PROCEDURE Enter_storage_GetListASSELECTESID , product , date , num, storageManagerIdFROM Enter_storage C

27、REATE PROCEDURE Exit_storage_GetMaxId ASDECLARE TempID int Exit_storage WHERESELECT TempID = max( ExitStorageId )+ 1 FROM IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Exit_storage_ExistsExitStorageId intASDECLARE TempID intSELECT TempID = count ( 1) FROM Exit_storage ExitStorageId =Exi

28、tStorageIdIF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Exit_storage_ADD ExitStorageId int output , product varchar ( 50 ), date smalldatetime , num varchar ( 6),storageManagerId nvarchar ( 50)ASINSERT INTO Exit_storage ( product , date , num , storageManagerId ) VALUES (product , date , num, st

29、orageManagerId)SET ExitStorageId = IDENTITYCREATE PROCEDURE Exit_storage_Update ExitStorageId int , product varchar ( 50 ), date smalldatetime ,num varchar ( 6), storageManagerId nvarchar ( 50)ASUPDATE Exit_storage SET product = product , date = date , num = num, storageManagerIdWHERE ExitStorageId=

30、 ExitStorageIdCREATE PROCEDURE Exit_storage_Delete ExitStorageId intASDELETE Exit_storage WHERE ExitStorageId= ExitStorageIdCREATE PROCEDURE Exit_storage_GetModel ExitStorageId intASSELECTExitStorageId , product , date , num, storageManagerIdFROM Exit_storage WHERE ExitStorageId= ExitStorageIdCREATE

31、 PROCEDURE Exit_storage_GetListASSELECTExitStorageId , product , date , num, storageManagerId FROM Exit_storage CREATE PROCEDURE Login_Exists userId varchar ( 10)ASDECLARE TempID intSELECT TempID = count ( 1) FROM Login WHERE userId IF TempID = 0RETURN 0ELSERETURN 1storageManagerId = userIdCREATE PR

32、OCEDURE Login_ADD userId varchar ( 10), password varchar ( 16), userName varchar ( 20),tel varchar ( 20),birthday smalldatetimeregisterTimesmalldatetime ,type nvarchar( 50)ASINSERT INTO Login( userId ,password, userName , tel , birthday , registerTime , type ) VALUES (userId , password, userName , t

33、el , birthday , registerTime , type)CREATE PROCEDURE Login_Update userId varchar ( 10), password varchar ( 16), userName varchar ( 20), tel varchar ( 20), birthday smalldatetime , registerTime smalldatetime , type nvarchar ( 50)ASUPDATE Login SET password = password , userName = userName , tel = tel

34、 , birthday = birthday , registerTime = registerTime , type = typeWHERE userId = userIdCREATE PROCEDURE Login_DeleteuserId varchar ( 10)ASDELETE Login WHERE userId = userIdCREATE PROCEDURE Login_GetModeluserId varchar ( 10)ASSELECTuserId , password , userName , tel ,FROM Login WHERE userId = userIdb

35、irthday, registerTime, typeCREATE PROCEDURE Login_GetListASSELECTuserId , password , userName , tel ,birthday, registerTime, typeFROM Login CREATE PROCEDURE Product_info_GetMaxId ASDECLARE TempID intSELECT TempID = max( proId )+1 FROM Product_infoIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROC

36、EDURE Product_info_Exists proId intASDECLARE TempID intSELECT TempID = count ( 1) FROM Product_infoIF TempID = 0 RETURN 0 WHERE proId =proIdELSERETURN 1CREATE PROCEDURE Product_info_ADD proId int output ,name varchar ( 50 ),price varchar ( 6),proDate smalldatetime ,storage varchar ( 4) ASINSERT INTO

37、 Product_info ( name , price , proDate , storage ) VALUES (name, price , proDate , storage)SET proId = IDENTITYCREATE PROCEDURE Product_info_UpdateproId int ,name varchar ( 50 ),price varchar ( 6),proDate smalldatetime ,storage varchar ( 4)ASUPDATE Product_info SET name = name, price = price , proDa

38、te = WHERE proId =proIdCREATE PROCEDURE Product_info_DeleteproId intproDate , storage =storageASDELETE Product_info WHERE proId =proIdCREATE PROCEDURE Product_info_GetModelproId intASSELECTproId , name, price , proDate , storage FROM Product_info WHERE proId =proIdCREATE PROCEDURE Product_info_GetLi

39、stASSELECTproId , name, price , proDate , storage FROM Product_info CREATE PROCEDURE Repair_info_GetMaxId ASDECLARE TempID intSELECT TempID = max( repairId )+ 1 FROM Repair_info IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Repair_info_ExistsrepairId intASDECLARE TempID intSELECT TempID

40、 = count ( 1) FROM Repair_info WHERE repairId = repairId IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Repair_info_ADD repairId int output , repairName nchar ( 10), repairtel nchar ( 10 ), product varchar ( 50 ), customer varchar ( 20), date smalldatetime , customertel nchar ( 10)AS, date , cust

41、omertelINSERT INTO Repair_info repairName , repairtel ) VALUES (, product , customerrepairName , repairtel, product , customer , date , customertelSET repairId= IDENTITYCREATE PROCEDURE Repair_info_Update repairId int , repairName nchar ( 10), repairtel nchar ( 10 ), product varchar ( 50 ), customer

42、 varchar ( 20),date smalldatetimecustomertelASnchar ( 10)UPDATE Repair_info SET repairName = product , customerWHERE repairId CREATE PROCEDURErepairName , repairtel = customer , =repairId Repair_info_Deletedate = = repairteldate , product =customertel = customertelrepairId intASDELETE Repair_info WH

43、ERE repairId =repairIdCREATE PROCEDURE Repair_info_GetModel repairId intASSELECTrepairId , repairName , repairtelFROM Repair_info WHERE repairId =repairId CREATE PROCEDURE Repair_info_GetList AS, product, customer, date, customertelSELECTrepairId , repairName , repairtel FROM Repair_info CREATE PROC

44、EDURE Seller_info_Exists, product, customer, date, customertelname varchar ( 50 )ASDECLARE TempID intSELECT TempID = count ( 1)FROM Seller_infoWHEREname =nameIF TempID = 0 RETURN 0ELSERETURN 1CREATE PROCEDURE Seller_info_ADDname varchar ( 50 ), telephone address product stockNumvarchar ( 20),( 50 ),

45、( 50 ),( 6),varcharvarcharvarcharstockDatestockPriceASsmalldatetimevarchar ( 10 ), product , stockNum ,stockDate , stockPriceINSERT INTO Seller_info ( name , telephone , address ) VALUES (name, telephone , address , product , stockNum , stockDate , stockPrice )CREATE PROCEDURE Seller_info_Updatename varchar ( 50 ),telephonevarchar( 20)addressvarchar( 50 ),productvarchar( 50 ),stockN