创建银ATM存取款数据库

一个客户信息可以有多个账户信息，且各个账户是独立的

一个账户可以有多种存款类型，各个存款类型是独立的

一个账户有多个交易信息，交易信息是关联的（即一个账户在某个交易日期，他的交易金额是多少，交易类型是什么，是相互有关联的）

CREATE TABLE userInfo --用户信息表
(
customerID number NOT NULL,
customerName varCHAR2(8) NOT NULL,
PID varCHAR(18) NOT NULL,
telephone varCHAR(20) NOT NULL,
address VARCHAR(50)
);

CREATE TABLE cardInfo --银行卡信息表
(
cardID CHAR(19) NOT NULL,
curID VARCHAR(10) NOT NULL,
savingID number NOT NULL,
openDate DATE NOT NULL,
openMoney number NOT NULL,
balance number NOT NULL,
pass CHAR(6) NOT NULL,
IsReportLoss NUMBER(1) NOT NULL,
customerID NUMBER NOT NULL
);

CREATE TABLE tradeInfo --交易信息表
(
tradeDate DATE NOT NULL,
tradeType CHAR(6) NOT NULL,
cardID CHAR(19) NOT NULL,
tradeMoney NUMBER NOT NULL,
remark LONG
);

CREATE TABLE Deposit --存款类型表
(
savingID NUMBER NOT NULL,
savingName VARCHAR(30) NOT NULL,
descrip VARCHAR(100)
);

/*$$$$$$$$$$$$$加约束$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
ALTER TABLE Deposit
ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID);

/* userInfo表的约束
customerID 顾客编号 主键
customerName 开户名 必填
PID 身份证号 必填，只能是18位或15位，身份证号唯一约束
telephone 联系电话 必填，格式为xxxx-xxxxxxxx或手机号11位
address 居住地址 可选输入
*/

--ALTER TABLE userInfo
--drop CONSTRAINT CK_telephone

ALTER TABLE userInfo
ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID);

ALTER TABLE userInfo
ADD CONSTRAINT CK_PID CHECK( length(PID)=18 or length(PID)=15 );

ALTER TABLE userInfo
ADD CONSTRAINT UQ_PID UNIQUE(PID);

ALTER TABLE userInfo
ADD CONSTRAINT CK_telephone CHECK(regexp_like(telephone,'(^\d{3,4}-\d{8}$)|(^\d{11}$)'));

/*cardInfo表的约束
cardID 卡号 必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义，
如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始
curType 货币 必填，默认为RMB
savingType 存款种类 活期/定活两便/定期
openDate 开户日期 必填，默认为系统当前日期
openMoney 开户金额 必填，不低于1元
balance 余额 必填，不低于1元,否则将销户
pass 密码 必填，6位数字，默认为6个8
IsReportLoss 是否挂失 必填，0/1值，默认为0未挂失
customerID 顾客编号 必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡
*/

ALTER TABLE cardInfo
ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID);
ALTER TABLE cardInfo
ADD CONSTRAINT CK_cardID CHECK(regexp_LIKE(cardID,'1010 3576 \d{4} \d{4}'));
ALTER TABLE cardInfo
MODIFY (curID VARCHAR(10) DEFAULT 'RMB');
--CONSTRAINT CK_savingType CHECK(savingType IN ('活期','定活两便','定期')),
ALTER TABLE cardInfo
MODIFY (openDate DATE DEFAULT sysdate);
ALTER TABLE cardInfo
ADD CONSTRAINT CK_openMoney CHECK(openMoney>=1);
ALTER TABLE cardInfo
ADD CONSTRAINT CK_balance CHECK(balance>=1);
ALTER TABLE cardInfo
ADD CONSTRAINT CK_pass CHECK(regexp_LIKE(pass,'^[0-9]{6}$'));
ALTER TABLE cardInfo
MODIFY (pass CHAR(6) DEFAULT '888888');
ALTER TABLE cardInfo
MODIFY (IsReportLoss NUMBER(1) DEFAULT 0);
ALTER TABLE cardInfo
ADD CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID);
ALTER TABLE cardInfo
ADD CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID);

/* tradeInfo表的约束
tradeType 必填，只能是存入/支取
cardID 卡号 必填，外健，可重复索引
tradeMoney 交易金额 必填，大于0
tradeDate 交易日期 必填，默认为系统当前日期
remark 备注 可选输入，其他说明
*/

 

ALTER TABLE tradeInfo
ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取'));
ALTER TABLE tradeInfo
ADD CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID);
ALTER TABLE tradeInfo
ADD CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0);
ALTER TABLE tradeInfo
MODIFY (tradeDate DATE DEFAULT sysdate);

--存款类型
INSERT INTO deposit (savingID,savingName,descrip) VALUES (1,'活期','按存款日结算利息');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (2,'定期一年','存款期是1年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (3,'定期二年','存款期是2年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (4,'定期三年','存款期是3年');
INSERT INTO deposit (savingID,savingName) VALUES (5,'定活两便');
INSERT INTO deposit (savingID,savingName) VALUES (6,'通知');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (7,'零存整取一年','存款期是1年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (8,'零存整取二年','存款期是2年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (9,'零存整取三年','存款期是3年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (10,'存本取息五年','按月支取利息');
SELECT * FROM DEPOSIT;

INSERT INTO userInfo(customerID,customerName,PID,telephone,address )
VALUES(1,'张三','123456789012345','010-67898978','北京海淀');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1234 5678',1,1000,1000,1);

INSERT INTO userInfo(customerID,customerName,PID,telephone)
VALUES(2,'李四','321245678912345678','0478-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1134',2,1,1,2);

INSERT INTO userInfo(customerID,customerName,PID,telephone)
VALUES(3,'王五','567891234532124670','010-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1130',2,1601,1601,3);

INSERT INTO userInfo(customerID,customerName,PID,telephone)
VALUES(4,'丁六','567891321242345618','0752-43345543');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1004',2,1,1,4);

/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，
同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）
*/
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1234 5678',900) ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1130',300) ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='1010 3576 1212 1130';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1004',1000) ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('支取','1010 3576 1212 1130',1900) ;
/*-------------更新银行卡信息表中的现有余额--报错-----------------*/
UPDATE cardInfo SET balance=balance-1900 WHERE cardID='1010 3576 1212 1130';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
VALUES('存入','1010 3576 1212 1134',5000) ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134';

commit;
/*--------检查测试数据是否正确---------*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;
SELECT * FROM userInfo;

--delete from tradeInfo;
--delete from cardInfo;
--delete from userInfo;