1、【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.DR-RD-020(V1.1)數據庫設計說明書數據庫設計說明書(內部資料 請勿外傳)編編 寫：寫：日日 期：期：檢檢 查：查：日日 期：期：審審 核：核：日日 期：期：批批 準：準：日日 期：期：版權所有版權所有 不得復制不得復制時代集團產品跟蹤平臺 .1數據庫設計說明書 .11引言.21.1編寫目的.21.2術語表.21.3參考資料.32數據庫環(huán)境說明.33數據庫的命名規(guī)則.34邏輯設計.35物理設計.45.1表匯總.45.2表X：XXX 表.45.3視圖的設計.65.4存儲過程、函數及觸發(fā)器的設計.66安全性設計.66.1防止

2、用戶直接操作數據庫的方法.66.2用戶帳號密碼的加密方法.76.3角色與權限.7文檔編號文檔編號2011-6-14版本版本A1密級密級商密A項目名稱項目名稱時代集團產品跟蹤平臺數據庫設計文檔模板數據庫設計文檔模板項目來源項目來源時代集團【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.7優(yōu)化.78數據庫管理與維護說明.71引言引言1.1編寫目的編寫目的本文檔是時代集團產品跟蹤平臺概要設計文檔的組成部分，編寫數據庫設計文檔的目的是：明確數據庫的表名、字段名等數據信息，用來指導后期的數據庫腳本的開發(fā)，本文檔遵循SQL 數據庫設計和開發(fā)規(guī)范 。本文檔的讀者對象是需求人員、系統(tǒng)設計人員、

3、開發(fā)人員、測試人員。1.2術語表術語表序號序號術語或縮略語術語或縮略語說明性定義說明性定義1ESIDEnterStorageId 入庫編號2ProIdProductId 產品編號3PMIDProductManagerId 產品管理員編號1.3參考資料參考資料資料名稱作者文件編號、版本資料存放地點2數據庫環(huán)境說明數據庫環(huán)境說明數據庫實例數據庫系統(tǒng)數據庫部署環(huán)境數據庫設計工具數據庫存放位置說明3數據庫的命名規(guī)則數據庫的命名規(guī)則數據庫名稱：時代集團的英文名稱time-group【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.表名：英文（表的用途）+下劃線+英文字段名：相關屬性的英文名

4、4邏輯設計邏輯設計提示：數據庫設計人員根據需求文檔，創(chuàng)建與數據庫相關的那部分實體關系圖（ERD） 。如果采用面向對象方法（OOAD） ，這里實體相當于類（class） 。5物理設計物理設計提示：（1）主要是設計表結構。一般地，實體對應于表，實體的屬性對應于表的列，實體之間的關系成為表的約束。邏輯設計中的實體大部分可以轉換成物理設計中的表，但是它們并不一定是一一對應的。（2）對表結構進行規(guī)范化處理（第三范式） 。5.1表匯總表匯總表名功能說明Enter_storage產品入庫（新產品的登記、修改、查詢）Exit_storage產品出庫（登記產品出庫信息）Seller_info銷售商信息維護表（對

5、銷售商信息的添加、修改和查詢）Product_info產品信息表（對新產品的發(fā)布，修改，查詢等維護）Repair_info維修表（對已出售產品的維修進行登記，修改）L管理員表（包含五種身份，銷售人員，庫存管理人員，售后服務人員，產品信息管理人員，系統(tǒng)管理員）5.2表：表的索引: 索引是否建立要根據具體的業(yè)務需求來確定。允許為空：不填的表示為“是” 。唯一：不填的表示為“是” 。表的記錄數和增長量：根據具體的業(yè)務需求確定。增長量應確定單位時間如果量大可以按每天,如果不大可以按每月。表字段的區(qū)別度：主要是考慮到將來在此字段上建立索引類型選擇時作為參考,當字段值唯一時可以不考慮,當字段值不唯一時,估

6、算一個區(qū)別度,近似即可。例如：如果一個表的 NAME 字段有共 2000 個值,其中有 1999 個不同值,1999/2000=0.99 越接近 1 區(qū)別度越高,反之區(qū)別度越低。表的并發(fā)：根據具體的業(yè)務需求預測表的并發(fā)。1【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.表名Enter_storage數據庫用戶庫存管理人員主鍵ESID 入庫產品編號其他排序字段Product，date，num,storageManagerId索引字段序號字段名稱數據類型（精度范圍）允許為空 Y/N唯一Y/N區(qū)別度默認值約束條件/說明1ESIDintNY2ProductVarchar(50)NN3d

7、ateSmallDateTimeNN4numVarchar(50)NN5storageManagerIdVarchar(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 PRIMARY KEY CLUSTE

8、RED (ESID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO記錄數增長量表的并發(fā)補充說明2.2.表名Exit_storage數據庫用戶庫存管理人員主鍵ExitStorageId 出庫產品編號其他排序字段Product，date，num,storageManagerId索引字段序號字段名稱數據類型（精

9、度范圍）允許為空 Y/N唯一Y/N區(qū)別度默認值約束條件/說明1ExitStorageIdintNY2ProductVarchar(50)NN3dateSmallDateTimeNN【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.4numVarchar(50)NN5storageManagerIdVarchar(50)NYMysql 腳本CREATE TABLE dbo.Exit_storage(ExitStorageId int IDENTITY(1,1) NOT NULL,product varchar(50) NOT NULL,date smalldatetime NOT

10、NULL,num varchar(6) NOT NULL, CONSTRAINT PK_subStorage PRIMARY KEY CLUSTERED (ExitStorageId ASC)WITH (PAD_INDEX = 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.Enter_storage Scr

11、ipt Date: 01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發(fā)補充說明3.3.表名Seller_info數據庫用戶銷售商管理員主鍵name 其他排序字段telephone，address，product, stockNum，stockDate，stockPrice索引字段序號字段名稱數據類型（精度范圍）允許為空 Y/N唯一Y/N區(qū)別度默認值約束條件/說明1nameVarchar(50)NY2telephoneVarchar(50)NN3add

12、ressSmallDateTimeNN4productVarchar(50)NN5stockNumVarchar(50)NN6stockDateSmallDateTimeNN【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.7stockPriceVarchar(50)NNMysql 腳本CREATE TABLE dbo.Seller_info(name varchar(50) NOT NULL,telephone varchar(20) NOT NULL,address varchar(50) NOT NULL,product varchar(50) NOT NULL,stock

13、Num varchar(6) NOT NULL,stockDate smalldatetime NOT NULL,stockPrice varchar(10) NOT NULL, CONSTRAINT PK_maketer_Info PRIMARY KEY CLUSTERED (name ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANS

14、I_PADDING OFFGO/* Object: Table dbo.Repair_info Script Date: 12/16/2010 09:59:13 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發(fā)補充說明4.4. 表名Product_info數據庫用戶產品信息管理人員主鍵proId其他排序字段name，price，proDate, storage索引字段序號字段名稱數據類型（精度范圍）允許為空 Y/N唯一Y/N區(qū)別度默認值約束條件/說明1proIdintNY2nameVar

15、char(50)NN3priceVarchar(50)NN4proDateSmallDateTime NN【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.5storageVarchar(50)NYMysql 腳本CREATE TABLE dbo.Product_info(proId int IDENTITY(1,1) NOT NULL,name varchar(50) NOT NULL,price varchar(6) NOT NULL,proDate smalldatetime NOT NULL,storage varchar(4) NOT NULL, CONSTRAINT

16、PK_product_Info PRIMARY KEY CLUSTERED (proId ASC)WITH (PAD_INDEX = 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.Login Script Date: 01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET

17、QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發(fā)補充說明5.5.表名Repair_info數據庫用戶售后服務人員主鍵repairId 產品維修編號其他排序字段repairName，repairtel，product, customer，date，customertel索引字段序號字段名稱數據類型（精度范圍）允許為空 Y/N唯一Y/N區(qū)別度默認值約束條件/說明1repairIdintNY2repairNameVarchar(50)NN3repairtelSmallDateTimeNN4productVarchar(50)NN5custom

18、erVarchar(50)NNdateSmallDateTimeNNcustomertelVarchar(50)NN【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.Mysql 腳本CREATE TABLE dbo.Repair_info(repairId 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

19、,customertel nchar(10) NULL, CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (repairId ASC)WITH (PAD_INDEX = 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.Product_info Script Date:

20、01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO記錄數增長量表的并發(fā)補充說明6.6.表名Login數據庫用戶管理人員主鍵userId 用戶編號其他排序字段password，userName，tel, birthday，registerTime，type索引字段序號字段名稱數據類型（精度范圍）允許為空 Y/N唯一Y/N區(qū)別度默認值約束條件/說明1userIdintNY2passwordVarchar(50)NN3userNameSmallDateTimeNN4telVa

21、rchar(50)NN5birthdayVarchar(50)NN6registerTimeSmallDateTimeNN【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.7typeVarchar(50)NNMysql 腳本CREATE TABLE dbo.Login(userId varchar(10) NOT NULL,password varchar(16) NOT NULL,userName varchar(20) NOT NULL,tel varchar(20) NOT NULL,birthday smalldatetime NOT NULL,registerTime

22、smalldatetime NOT NULL,type nvarchar(50) NOT NULL, CONSTRAINT PK_manager_Info PRIMARY KEY CLUSTERED (userId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO記錄數增長量表的并發(fā)補充說明5.35.3視圖

23、的設計視圖的設計根據 XXX 產品的概要設計文檔來確定。視圖的命名按照xx 數據庫設計規(guī)范中關于視圖的命名規(guī)范命名。視圖的設計應注意以下幾點:1 盡量減少列中使用的公式。2 去掉所有不必要的列。3 不要使同一個文檔屬于多個分類。4 避免使用表單公式。5.45.4存儲過程、函數及觸發(fā)器的設計存儲過程、函數及觸發(fā)器的設計存儲過程及觸發(fā)器的命名按照xx 數據庫設計規(guī)范中關于存儲過程及觸發(fā)器的命名規(guī)范命名。存儲過程：根據具體得業(yè)務邏輯確定輸入參數個數，類型,確定對哪幾個表進行何種作。 在定義存儲過程時，要使用其完成單一、相對集中的任務，不要定義已由其他提供功能的過程。例如：不要定義強制數據完整性的過程

24、（使用完整性約束） 。【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.函數 ：函數與存儲過程非常相似，它也是存儲在數據庫中的對象。但是可以在SQL 命令中使用函數。就好像建立自己的 substr 函數一樣觸發(fā)器 ：觸發(fā)器是存儲在數據庫中的程序，它在某一特定事件發(fā)生時執(zhí)行。這些程序可以用 PL/SQL 和 java 語言編寫，也可以用作 c 語言的調用，數據庫允許用戶定義這些程序，然后在相關的表，視圖或者數據庫動作執(zhí)行insert，update 或 delete 語句時執(zhí)行。1.存儲過程：CREATE PROCEDURE Enter_storage_GetMaxIdASDECLA

25、RE TempID intSELECT TempID = max(ESID)+1 FROM Enter_storageIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Enter_storage_ExistsESID intASDECLARE TempID intSELECT TempID = count(1) FROM Enter_storage WHERE ESID=ESID IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Enter_storage_ADDESID in

26、t output,product varchar(50),date smalldatetime,num varchar(6),storageManagerId nvarchar(50) AS INSERT INTO Enter_storage(product,date,num,storageManagerId)VALUES(product,date,num,storageManagerIdSET ESID = IDENTITYCREATE PROCEDURE Enter_storage_UpdateESID int,product varchar(50),date smalldatetime,

27、num varchar(6),storageManagerId nvarchar(50) AS UPDATE Enter_storage SET 【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.product = product,date = date,num = num,storageManagerId = storageManagerIdWHERE ESID=ESID CREATE PROCEDURE Enter_storage_DeleteESID int AS DELETE Enter_storageWHERE ESID=ESID CREATE PROCEDURE En

28、ter_storage_GetModelESID int AS SELECT ESID,product,date,num,storageManagerIdFROM Enter_storageWHERE ESID=ESID CREATE PROCEDURE Enter_storage_GetList AS SELECT ESID,product,date,num,storageManagerIdFROM Enter_storageCREATE PROCEDURE Exit_storage_GetMaxIdASDECLARE TempID intSELECT TempID = max(ExitSt

29、orageId)+1 FROM Exit_storageIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Exit_storage_ExistsExitStorageId intASDECLARE TempID intSELECT TempID = count(1) FROM Exit_storage WHERE ExitStorageId=ExitStorageId IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Exit_storage_ADDExitStorageId

30、int output,product varchar(50),date smalldatetime,num varchar(6),storageManagerId nvarchar(50)【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔. AS INSERT INTO Exit_storage(product,date,num,storageManagerId)VALUES(product,date,num,storageManagerIdSET ExitStorageId = IDENTITYCREATE PROCEDURE Exit_storage_UpdateExitSto

31、rageId int,product varchar(50),date smalldatetime,num varchar(6),storageManagerId nvarchar(50) AS UPDATE Exit_storage SET product = product,date = date,num = num,storageManagerId = storageManagerIdWHERE ExitStorageId=ExitStorageId CREATE PROCEDURE Exit_storage_DeleteExitStorageId int AS DELETE Exit_

32、storageWHERE ExitStorageId=ExitStorageId CREATE PROCEDURE Exit_storage_GetModelExitStorageId int AS SELECT ExitStorageId,product,date,num,storageManagerIdFROM Exit_storageWHERE ExitStorageId=ExitStorageId CREATE PROCEDURE Exit_storage_GetList AS SELECT ExitStorageId,product,date,num,storageManagerId

33、FROM Exit_storageCREATE PROCEDURE Login_ExistsuserId varchar(10)ASDECLARE TempID intSELECT TempID = count(1) FROM Login WHERE userId=userId IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Login_ADD【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.userId varchar(10),password varchar(16),userName varchar(20),tel varc

34、har(20),birthday smalldatetime,registerTime smalldatetime,type nvarchar(50) AS INSERT INTO Login(userId,password,userName,tel,birthday,registerTime,type)VALUES(userId,password,userName,tel,birthday,registerTime,typeCREATE PROCEDURE Login_UpdateuserId varchar(10),password varchar(16),userName varchar

35、(20),tel varchar(20),birthday smalldatetime,registerTime smalldatetime,type nvarchar(50) AS UPDATE Login SET password = password,userName = userName,tel = tel,birthday = birthday,registerTime = registerTime,type = typeWHERE userId=userId CREATE PROCEDURE Login_DeleteuserId varchar(10) AS DELETE Logi

36、nWHERE userId=userId CREATE PROCEDURE Login_GetModeluserId varchar(10) AS SELECT userId,password,userName,tel,birthday,registerTime,typeFROM LoginWHERE userId=userId CREATE PROCEDURE Login_GetList AS SELECT userId,password,userName,tel,birthday,registerTime,typeFROM Login【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精

37、品文檔.CREATE PROCEDURE Product_info_GetMaxIdASDECLARE TempID intSELECT TempID = max(proId)+1 FROM Product_infoIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Product_info_ExistsproId intASDECLARE TempID intSELECT TempID = count(1) FROM Product_info WHERE proId=proId IF TempID = 0RETURN 0ELS

38、ERETURN 1CREATE PROCEDURE Product_info_ADDproId int output,name varchar(50),price varchar(6),proDate smalldatetime,storage varchar(4) AS INSERT INTO Product_info(name,price,proDate,storage)VALUES(name,price,proDate,storageSET proId = IDENTITYCREATE PROCEDURE Product_info_UpdateproId int,name varchar

39、(50),price varchar(6),proDate smalldatetime,storage varchar(4) AS UPDATE Product_info SET name = name,price = price,proDate = proDate,storage = storageWHERE proId=proId CREATE PROCEDURE Product_info_DeleteproId int AS DELETE Product_info【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.WHERE proId=proId CREATE PROCED

40、URE Product_info_GetModelproId int AS SELECT proId,name,price,proDate,storageFROM Product_infoWHERE proId=proId CREATE PROCEDURE Product_info_GetList AS SELECT proId,name,price,proDate,storageFROM Product_infoCREATE PROCEDURE Repair_info_GetMaxIdASDECLARE TempID intSELECT TempID = max(repairId)+1 FR

41、OM Repair_infoIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Repair_info_ExistsrepairId intASDECLARE TempID intSELECT TempID = count(1) FROM Repair_info WHERE repairId=repairId IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Repair_info_ADDrepairId int output,repairName nchar(10),repai

42、rtel nchar(10),product varchar(50),customer varchar(20),date smalldatetime,customertel nchar(10) AS INSERT INTO Repair_info(repairName,repairtel,product,customer,date,customertel)VALUES(repairName,repairtel,product,customer,date,customertel【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.SET repairId = IDENTITYCREAT

43、E PROCEDURE Repair_info_UpdaterepairId int,repairName nchar(10),repairtel nchar(10),product varchar(50),customer varchar(20),date smalldatetime,customertel nchar(10) AS UPDATE Repair_info SET repairName = repairName,repairtel = repairtel,product = product,customer = customer,date = date,customertel

44、= customertelWHERE repairId=repairId CREATE PROCEDURE Repair_info_DeleterepairId int AS DELETE Repair_infoWHERE repairId=repairId CREATE PROCEDURE Repair_info_GetModelrepairId int AS SELECT repairId,repairName,repairtel,product,customer,date,customertelFROM Repair_infoWHERE repairId=repairId CREATE

45、PROCEDURE Repair_info_GetList AS SELECT repairId,repairName,repairtel,product,customer,date,customertelFROM Repair_infoCREATE PROCEDURE Seller_info_Existsname varchar(50)ASDECLARE TempID intSELECT TempID = count(1) FROM Seller_info WHERE name=name IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Se

46、ller_info_ADDname varchar(50),telephone varchar(20),【精品文檔】如有侵權，請聯(lián)系網站刪除，僅供學習與交流.精品文檔.address varchar(50),product varchar(50),stockNum varchar(6),stockDate smalldatetime,stockPrice varchar(10) AS INSERT INTO Seller_info(name,telephone,address,product,stockNum,stockDate,stockPrice)VALUES(name,telephone,address,product,stockNum,stockDate,stockPriceCREATE PROCEDURE Seller_info_Updatename varchar(50),telephone varchar(20),address varchar(50),product varchar(50),stockNum varchar(6),stockDate smalldatetime,stockPrice varchar(10)