資料庫設計不求人（機房收費系統）

前言

我想在進行資料庫設計之前，需要要明白為什麼設計資料庫，不進行資料庫設計可以嗎?如果隻是随便在院子裡搭一個棚子需不需要先畫圖設計，但是如果要建造一棟大廈又需不需要設計？

思考之後，可以得出結論：面對複雜的資料庫時，我們需要進行資料庫設計。

概念

在資料庫項目開發中，一個良好設計的資料庫無疑有很多的好處。有較少的資料備援、節省存儲空間，能夠保證資料的完整性。那麼如何設計一個資料庫，又有哪些流程。

按照軟體開發的流程：

各階段得到的檔案：

實作

這裡通過設計學生使用的機房收費系統的資料庫來示範：

需求分析

分析使用者的業務和資料處理需求，這一階段，我們得到資料字典和資料流圖

資料流圖（Data Flow Diagram，DFD）：通過資料流圖來表達系統内資料處理的過程。

下面以收費系統的使用者登入過程為例，來說明資料流圖如何描述資料處理過程。

上圖描述了使用者登入系統的過程。使用者在登陸界面輸入使用者名和密碼進行登陸驗證。通過查詢使用者資訊檔案來核對使用者的身份，如果身份驗證通過則建立一條登陸記錄添加到登陸記錄檔案中，完成登陸登記。

資料字典和資料流圖配合，能更清楚的表達資料處理的要求。用詞條的方式定義在資料流圖中出現的所有被命名的圖形元素，包含資料流、加工、資料檔案、資料元素以及資料源和資料譚。

下面是一個簡單的示例：

• 資料元素條目（資料進行中最小、不可分割的機關）

• 資料流條目（資料結構在系統的傳播路徑）

• 資料存儲檔案條目（資料儲存的地方）

• 加工條目（對資料流的處理）

• 資料源及資料譚條目（即外部實體）

概要設計

在概要階段，我們要進行資料模組化，即傳統的實體-關系方法，它使用了三種互相關聯的資訊：資料實體、描述實體的屬性、描述實體間互相連接配接的關系，使用E-R圖來表述。

• 資料實體

  在E-R圖中，實體用矩形來表示，是具有若幹屬性資訊的組合體。資料實體可以是外部實體（如顯示器）、事物（如報表）、角色（如學生）、行為（如打電話）或者事件（如單擊）、組織機關（如院系）、地點（如教室）、或結構（如檔案）。

• 屬性

  屬性定義資料實體的特征，在E-R圖中用橢圓形來表示。

• 關系

  各個實體之間的關系通過菱形來表示，它們之間的關聯有三種：一對一（1：1）、一對多（1：m）、多對多（n：m）。

在這裡為了避免屬性太多而顯得太亂，我去掉了實體的屬性，僅展示實體和它們之間的聯系。

詳細設計

在詳細設計中，需要把概要設計得到的E-R圖轉換為關系模式，并且進行範式檢查

原則：

• 一個實體轉換為一個關系模式，實體的屬性就是關系的屬性，實體的鍵就是關系的鍵；
• 聯系在轉換為關系模式時，分為以下三種不同的情況：
• 若聯系為1：1，可以在兩個實體類型轉換成的兩個關系模式中任意一個關系模式的屬性中加入另一個關系模式的鍵和聯系類型的屬性。
• 若聯系為1：n，則在N端實體類型轉換成的關系模式中加入1端實體類型的鍵和聯系類型的屬性。
• 若聯系為m:n，則将聯系類型也轉換成關系模式，其屬性為兩端實體類型的鍵加上聯系類型的屬性，而鍵為兩端實體鍵的組合。

範式檢查

• 第一範式：每列都不可分割，在關系型資料庫中，第一範式滿足
• 第二範式：不出現局部依賴
• 第三範式：不出現傳遞依賴

最後的關系模式為下面所示：

使用者（賬号，密碼，姓名，性别，類型，操作人）

T_User (userID,userPWD,userName,userSex,userType,signOperator)

登陸（賬号，登陸時間，登出時間，機器名稱）

T_UserLogin (userID,loginDateTime,logoutDateTime,loginComputer)

系統（臨時費率，固定費率，至少上機時間，準備時間,至少充值金額，計費機關，修改人）

T_System (tempRate,rate,leastTime,preprareTime,limitRechargCash,unitTime,systemModifier)

學生（學号，姓名，性别，班級，年級，學院，系部，專業，注冊人，注冊時間）

T_Student (stuNo,stuName,stuSex,class,grade,academy,department,major,register,registerDateTime)

卡（卡号，金額，類型，使用狀态，售卡人，注冊時間）

T_Card (cardID,cash,type,status,register,registerDateTime)

充值（卡号，充值金額，充值人，充值時間，是否結賬）

T_CardRecharge (cardID,cash,rechargeOperator,rechargeDateTime,isCheck)

退卡（卡号，退卡金額，退卡人，退卡時間，是否結賬）

T_CardCancel (cardID,cash,cancelOperator,cancelDateTime,isCheck)

學生綁定卡（學号，卡号，綁定人，綁定時間，綁定狀态）

T_Binding (stuNo,cardID,bindOperator,bindDateTime,isBind)

上機（卡号，上機時間，下機時間，消費時間，消費金額，上機機器）

T_OnLine (cardID,onLineDateTime,offLineDateTime,costTime,costCash,computer)

賬單（充值金額，退還金額，剩餘金額，結賬時間，結賬人）

T_Bill (rechargeCash,cancelCash,remainCash,checkDateTime,checkOperator)

實作

這裡使用了PowerDesigner來實作資料庫

SQL檔案為：

/*==============================================================*/
/* Database name:  JFCharge                                     */
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2016/3/10 9:54:07                            */
/*==============================================================*/


use JFCharge
go

if exists (select 
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Binding') and o.name = 'FK_T_BINDIN_REFERENCE_T_CARD')
alter table T_Binding
   drop constraint FK_T_BINDIN_REFERENCE_T_CARD
go

if exists (select 
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Binding') and o.name = 'FK_T_BINDIN_REFERENCE_T_STUDEN')
alter table T_Binding
   drop constraint FK_T_BINDIN_REFERENCE_T_STUDEN
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_Bill')
            and   type = 'U')
   drop table T_Bill
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_Binding')
            and   type = 'U')
   drop table T_Binding
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_Card')
            and   type = 'U')
   drop table T_Card
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_CardCancel')
            and   type = 'U')
   drop table T_CardCancel
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_CardRecharge')
            and   type = 'U')
   drop table T_CardRecharge
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_OnLine')
            and   type = 'U')
   drop table T_OnLine
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_Student')
            and   type = 'U')
   drop table T_Student
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_System')
            and   type = 'U')
   drop table T_System
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_User')
            and   type = 'U')
   drop table T_User
go

if exists (select 
            from  sysobjects
           where  id = object_id('T_UserLogin')
            and   type = 'U')
   drop table T_UserLogin
go

/*==============================================================*/
/* Table: T_Bill                                                */
/*==============================================================*/
create table T_Bill (
   rechargeCash         decimal(,)        null,
   cancelCash           decimal(,)        null,
   remainCash           decimal(,)        null,
   checkDateTime        datetime             null,
   operator             varchar()          null
)
go

/*==============================================================*/
/* Table: T_Binding                                             */
/*==============================================================*/
create table T_Binding (
   stuNo                varchar()          not null,
   cardID               varchar()          not null,
   operator             varchar()          not null,
   bindDateTime         datetime             not null,
   isBind               char()              not null,
   constraint PK_T_BINDING primary key (stuNo, cardID)
)
go

/*==============================================================*/
/* Table: T_Card                                                */
/*==============================================================*/
create table T_Card (
   cardID               varchar()          not null,
   cash                 decimal(,)        not null,
   type                 char()              not null,
   status               char()              not null,
   register             varchar()          not null,
   registerDateTime     datetime             not null,
   constraint PK_T_CARD primary key (cardID)
)
go

/*==============================================================*/
/* Table: T_CardCancel                                          */
/*==============================================================*/
create table T_CardCancel (
   cardID               varchar()          not null,
   cash                 decimal(,)        not null,
   operator             varchar()          not null,
   cancelDateTime       datetime             not null,
   isCheck              char()              not null
)
go

/*==============================================================*/
/* Table: T_CardRecharge                                        */
/*==============================================================*/
create table T_CardRecharge (
   cardID               varchar()          not null,
   cash                 decimal(,)        not null,
   operator             varchar()          not null,
   rechargeDateTime     datetime             not null,
   isCheck              char()              not null
)
go

/*==============================================================*/
/* Table: T_OnLine                                              */
/*==============================================================*/
create table T_OnLine (
   cardID               varchar()          not null,
   onLineDateTime       datetime             not null,
   offLineDateTime      datetime             null,
   costTime             datetime             null,
   costCash             decimal(,)        null,
   computer             varchar()          not null
)
go

/*==============================================================*/
/* Table: T_Student                                             */
/*==============================================================*/
create table T_Student (
   stuNo                varchar()          not null,
   stuName              varchar()          not null,
   stuSex               char()              not null,
   class                varchar()          not null,
   grade                varchar()          not null,
   academy              varchar()          not null,
   department           varchar()          not null,
   major                varchar()          not null,
   register             varchar()          not null,
   registerDateTime     datetime             not null,
   constraint PK_T_STUDENT primary key (stuNo)
)
go

/*==============================================================*/
/* Table: T_System                                              */
/*==============================================================*/
create table T_System (
   tempRate             decimal(,)        not null,
   rate                 decimal(,)        not null,
   leastTime            int                  not null,
   preprareTime         int                  not null,
   limitRechargeCash    decimal(,)        not null,
   unitTime             int                  not null,
   systemModifier       varchar()          not null
)
go

/*==============================================================*/
/* Table: T_User                                                */
/*==============================================================*/
create table T_User (
   userID               varchar()          not null,
   userPWD              varchar()          not null,
   userName             varchar()          not null,
   userSex              char()              not null,
   userType             char()              not null,
   operator             varchar()          not null,
   constraint PK_T_USER primary key (userID)
)
go

if exists (select  from  sys.extended_properties
           where major_id = object_id('T_User') and minor_id = )
begin 
   declare @CurrentUser sysname 
select @CurrentUser = user_name() 
execute sp_dropextendedproperty 'MS_Description',  
   'user', @CurrentUser, 'table', 'T_User' 

end 


select @CurrentUser = user_name() 
execute sp_addextendedproperty 'MS_Description',  
   '使用者表', 
   'user', @CurrentUser, 'table', 'T_User'
go

/*==============================================================*/
/* Table: T_UserLogin                                           */
/*==============================================================*/
create table T_UserLogin (
   userID               varchar()          not null,
   loginDateTime        datetime             not null,
   logoutDateTime       datetime             null,
   loginComputer        varchar()          not null
)
go

alter table T_Binding
   add constraint FK_T_BINDIN_REFERENCE_T_CARD foreign key (cardID)
      references T_Card (cardID)
go

alter table T_Binding
   add constraint FK_T_BINDIN_REFERENCE_T_STUDEN foreign key (stuNo)
      references T_Student (stuNo)
go


           

小結

其實我們在設計資料庫時，如果隻是按照标準的流程和規則來完成工作，那麼很可能感覺無從下手，我們可以先通過自己的一些生活經驗來完成初步的資料庫設計。

比如說，分析機房收費系統在确定實體的時候，我們平時的生活經驗就是這個系統應該要有學生、上機卡（需要注冊、充值）、操作員（給我們辦理上下機）、系統等實體，先初步确定之後再仔細分析。還有範式檢查的時候還有更高的範式，但是我們一般在設計的時候使其滿足第三範式就可以了。

以上就是資料庫設計的大緻流程，以機房收費系統為例子，這當中可能由于部落客了解的偏差，會有不合适的地方，希望大家可以指出來，共同進步。