數(shù)據(jù)庫設(shè)計(jì)文檔

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

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

3、 放位置說明3數(shù)據(jù)庫的命名規(guī)則數(shù)據(jù)庫名稱：時(shí)代集團(tuán)的英文名稱time-group 表名：英文（表的用途）+下劃線+英文字段名：相關(guān)屬性的英文名4邏輯設(shè)計(jì)提示：數(shù)據(jù)庫設(shè)計(jì)人員根據(jù)需求文檔，創(chuàng)建與數(shù)據(jù)庫相關(guān)的那部分實(shí)體關(guān)系圖（ ERD 如果采用面向?qū)ο蠓椒ǎ?0A）,這里實(shí)體相當(dāng)于類（class ）。5物理設(shè)計(jì)提示：（1）主要是設(shè)計(jì)表結(jié)構(gòu)。一般地，實(shí)體對應(yīng)于表，實(shí)體的屬性對應(yīng)于表的列，實(shí)體之間的關(guān)系成為表的約束。邏輯設(shè)計(jì)中的實(shí)體大部分可以轉(zhuǎn)換成物理設(shè)計(jì)中的表，但是它們并不一定是對應(yīng)的。（2）對表結(jié)構(gòu)進(jìn)行規(guī)范化處理（第三范式）。5.1 表匯總表名功能說明Enter_storage產(chǎn)品入庫（新產(chǎn)品的登

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

5、區(qū)別度：主要是考慮到將來在此字段上建立索引類型選擇時(shí)作為參考，當(dāng)字段值唯一時(shí)可以不考慮，當(dāng)字段值不唯一時(shí)，估算一個(gè)區(qū)別度，近似即可。 例如：如果一個(gè)表的 NAME字段有共2000個(gè)值,其中有1999個(gè)不同 值,1999/2000=0.99越接近1區(qū)別度越高,反之區(qū)別度越低。表的并發(fā)：根據(jù)具體的業(yè)務(wù)需求預(yù)測表的并發(fā)。1.表名En ter storage數(shù)據(jù)庫用戶庫存管理人員主鍵ESID入庫產(chǎn)品編號其他排序字段Product， date，num,storageManagerld索引字段序號字段名稱數(shù)據(jù)類型（精度范圍）允許為空Y/N唯一Y/N區(qū)別 度默認(rè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記錄數(shù)增長量表的并發(fā)補(bǔ)充說明2.表名Exit storage數(shù)據(jù)庫用戶庫存管理人員主鍵ExitStorageId出庫產(chǎn)口口編號其他排序字段Product， date，num,storageManagerld索引字段序號字

8、段名稱數(shù)據(jù)類型（精度范圍）允許為空Y/N唯一Y/N區(qū)別度默認(rè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記錄數(shù)增長量表的并發(fā)補(bǔ)充說明3.表名Seller i nfo數(shù)據(jù)庫用戶銷售商管理員主鍵n ame其他排序字段telephone ， address， product, stockNum， stockDate ， stockPrice索引字段序號字段名稱數(shù)據(jù)類型（精度范圍）允許為空Y/N唯一Y/N區(qū)別度默認(rè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記錄數(shù)增長量表的并發(fā)補(bǔ)充說明4.表名Product i nfo數(shù)據(jù)庫用戶產(chǎn)品信息管理人員主鍵proId其他排序字段name price ，proDate, storage索引字段序號字段名稱數(shù)據(jù)類型（精度范圍）允許為空Y/N唯一Y/N區(qū)別度默認(rè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記錄數(shù)增長量表的并發(fā)補(bǔ)充說明5.表名Repair i nfo數(shù)據(jù)庫用戶售后服務(wù)人員主鍵repairId產(chǎn)品維修編號其

16、他排序字段repairName， repairtel ， product, customer， date， customertel索引字段序號字段名稱數(shù)據(jù)類型（精度范圍）允許為空Y/N唯一Y/N區(qū)別度默認(rè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記錄數(shù)增長量表的并發(fā)補(bǔ)充說明6.表名Log in數(shù)據(jù)庫用戶管理人員主鍵ruserId用戶編號

19、其他排序字段password，userName tel, birthday ，registerTime ，type索引字段序號字段名稱數(shù)據(jù)類型（精度范圍）允許為空Y/N唯一Y/N區(qū)別度默認(rè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記錄數(shù)增長量表的并發(fā)補(bǔ)充說明5.3 視圖的設(shè)計(jì)根據(jù)XXX產(chǎn)品的概要設(shè)計(jì)文檔來確定。視圖的命名按照XX數(shù)據(jù)庫設(shè)計(jì)規(guī)范中關(guān)于視圖的命名規(guī)范命名 視圖的設(shè)計(jì)應(yīng)注意以下幾點(diǎn)：1盡量減少列中使用的公式。2去掉所有不必要的列。3不要使同一個(gè)文檔屬于多個(gè)分類。4避免使用表單公式。5.4 存儲過程、函數(shù)及觸發(fā)器的設(shè)計(jì)存儲過程及觸發(fā)器的命名按照XX數(shù)據(jù)庫設(shè)計(jì)規(guī)范中關(guān)于存儲過程及觸發(fā)器的命名 規(guī)范命名。存儲過程：根

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

23、時(shí)執(zhí)行。 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