Home [SQL筆記] 資料庫設計範例
Post
Cancel

[SQL筆記] 資料庫設計範例

Posted Mar 12, 2011
By R
6 min read

測試用…

業務需求說明

模擬銀行業務

  1. 銀行開戶(註冊個人資料)、開卡(辦理銀行卡)。(一個人可以辦理多張銀行卡，但是最多只能辦3張)
  2. 存錢
  3. 查詢餘額
  4. 取錢
  5. 轉帳
  6. 查看交易記錄
  7. 帳戶掛失
  8. 帳戶注銷

資料表設計

  1. 帳戶資料表
  2. 銀行卡表
  3. 交易資訊表(儲存存錢和取錢的記錄)
  4. 轉帳資訊表(儲存轉帳資料記錄)
  5. 狀態資訊變化表(儲存銀行卡狀態：1.正常2.掛失3.凍結4.註銷)

資料表結構設計

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

create table AccountInfo --帳戶信息表
(
    AccountId int primary key identity(1,1), --帳戶編號
    AccountCode varchar(20) not null, --身份証號碼
    AccountPhone varchar(20) not null, --電話號碼
    RealName varchar(20) not null, --真實姓名
    OpenTime smalldatetime not null --開戶時間
)
create table BankCard --銀行卡
(
    CardNo varchar(30) primary key, --銀行卡卡號
    AccountId int not null, --帳戶編號(與帳戶信息表形成主外鍵關係)
    CardPwd varchar(30) not null, --銀行卡密碼
    CardMoney money not null, --銀行戶餘額
    CardState int not null, --1.正常2.掛失3.凍結4.註銷
    CardTime smalldatetime default(getdate()) --開卡時間
)
create table CardExchange --交易資訊表(儲存存錢和取錢的記錄)
(
    ExchangeId int primary key identity(1,1), --交易自動編號
    CardNo varchar(30) not null, --銀行卡號(與銀行卡表形成主外鍵關係)
    MoneyInBank money not null,--存錢金額
    MoneyOutBank money not null,--取錢金額
    ExchangeTime smalldatetime not null--交易時間
)
create table CardTransfer --轉帳資訊表(儲存轉帳資料記錄)
(
    TransferId int primary key identity(1,1),--轉帳自動編號
    CardNoOut varchar(30) not null,--轉出銀行卡號(與銀行卡表形成主外鍵關係)
    CarNoIn varchar(30) not null,--轉入銀行卡號(與銀行卡表形成主外鍵關係)
    TransferMoney money not null,--交易金額
    TransferTime smalldatetime not null--交易時間
)
create table CardStateChange --狀態資訊變化表(儲存銀行卡狀態：1.正常2.掛失3.凍結4.註銷)
(
    StateId int primary key identity(1,1),--狀態信息自動編號
    CardNo varchar(30),--銀行卡號(與銀行卡表形成主外鍵關係)
    OldState int not null,--銀行卡原始狀態
    NewState int not null,--銀行卡新狀態
    StateWhy varchar(200),--狀態變化原因
    StateTime smalldatetime--記錄產生時間
)
--為張三、李四、王五三個人進行開戶開卡的操作
--張三身份証:A1122334455
--李四身份証:B2233445566
--王五身份証:C3344556677
insert into AccountInfo (AccountCode,AccountPhone,RealName,OpenTime) values
('A1122334455','1122334455','張三',getdate()),
('B2233445566','2233445566','李四',getdate()),
('C3344556677','3344556677','王五',getdate())

insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState) values
('999998888800',1,'123456',0,1),
('999998888801',2,'123456',0,1),
('999998888802',3,'123456',0,1)

select * from AccountInfo
select * from BankCard

/*---------------------------------------------------------
帳戶信息表
AccountId	AccountCode	AccountPhone	RealName	OpenTime
1	A1122334455	1122334455	張三	2023-05-10 01:48:00
2	B2233445566	2233445566	李四	2023-05-10 01:48:00
3	C3344556677	3344556677	王五	2023-05-10 01:48:00
------------------------------------------------------------*/

--進行存錢操作：張三存錢$2000，李四存錢$8000，王五存錢$50000
--張三存錢$2000
update BankCard set CardMoney = CardMoney + 2000  where CardNo ='999998888800'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('999998888800',2000,0,getdate())--交易記錄

--李四存錢$8000
update BankCard set CardMoney = CardMoney + 8000  where CardNo ='999998888801'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('999998888801',8000,0,getdate())--交易記錄

--王五存錢$500000
update BankCard set CardMoney = CardMoney + 500000  where CardNo ='999998888802'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('999998888802',500000,0,getdate())--交易記錄

/*---------------------------------------------------------
帳戶信息表
AccountId	AccountCode	AccountPhone	RealName	OpenTime
1	    A1122334455	    1122334455	    張三	2023-05-10 01:48:00
2	    B2233445566	    2233445566	    李四	2023-05-10 01:48:00
3	    C3344556677	    3344556677	    王五	2023-05-10 01:48:00

銀行卡表
CardNo	AccountId	CardPwd	CardMoney	CardState	CardTime
999998888800	1	123456	2000.00	    1	2023-05-10 01:54:00
999998888801	2	123456	8000.00 	1	2023-05-10 01:54:00
999998888802	3	123456	500000.00	1	2023-05-10 01:54:00

交易資訊表(儲存存錢和取錢的記錄)
ExchangeId	CardNo	MoneyInBank	MoneyOutBank	ExchangeTime
1	999998888800	2000.00	    0.00	    2023-05-10 02:05:00
2	999998888801	8000.00	    0.00	    2023-05-10 02:06:00
3	999998888802	500000.00	0.00	    2023-05-10 02:06:00
------------------------------------------------------------*/

--轉帳：張三給王五轉帳1000元
--轉帳：張三給王五轉帳1000元
update BankCard set CardMoney = CardMoney - 1000 where CardNo = '999998888800'--張三減1000元
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '999998888802'--王五加1000元
insert into CardTransfer(CardNoOut,CarNoIn,TransferMoney,TransferTime)
values('999998888800','999998888802',1000,GETDATE())--轉帳資訊表(儲存轉帳資料記錄)

select * from AccountInfo
select * from BankCard
select * from CardExchange
select * from CardTransfer

/* ------------------------------------------------------
銀行卡表
CardNo	AccountId	CardPwd	CardMoney	CardState	CardTime
999998888800	1	123456	1000.00	1	2023-05-10 01:54:00
999998888801	2	123456	8000.00	1	2023-05-10 01:54:00
999998888802	3	123456	501000.00	1	2023-05-10 01:54:00

轉帳資訊表(儲存轉帳資料記錄)
TransferId	CardNoOut	CarNoIn	TransferMoney	TransferTime
1	999998888800	999998888802	1000.00	2023-05-10 02:18:00
----------------------------------------------------------------*/

truncate刪除資料表所有數據，自動編號重置(編號歸0)truncate table 表名

https://www.bilibili.com/video/BV1XV411C7TP?p=17

Notes, SQL
T-SQL sql script 資料庫設計
This post is licensed under CC BY 4.0 by the author.
Contents