728x90
1. 요구사항
- 계좌 생성을 하기 위해서는 고객은 신분증을 은행원에게 제출한 뒤, 본인 확인이 되면 회원정보(User)가 등록 되어야 합니다. 회원정보를 등록할 수 있는 테이블이 필요합니다.
- 은행원은 회원정보를 입력한 뒤, 계좌를 생성합니다. 계좌 생성시에 은행원은 고객에게 1000원을 받고, 1000 원이 입금된 상태로 계좌가 등록됩니다.
- 은행원은 회원 정보를 조회할 수 있어야 합니다. 은행원이 회원 정보를 조회하게 되면, 관련된 계좌 목록을 함 께 확인할 수 있습니다. 고객은 여러 개의 계좌를 가질 수 있습니다.
- 회원에는 어떤 정보가 필요할까요?
- 계좌에는 어떤 정보가 필요할까요?
- 회원과 계좌는 어떤 연관 관계가 있을까요?
- 회원과 계좌 중에 어떤 테이블이 FK를 가져야 할까요?
- 고객은 다른 고객에게 계좌 이체를 할 수 있어야 합니다.
- 본인의 특정계좌에 입금한 내역을 볼 수 있어야 합니다.
- 본인의 특정계좌에 출금한 내역을 볼 수 있어야 합니다.
- 본인의 특정계좌에 입출금한 내역을 볼 수 있어야 합니다.
- 홍길동이 임꺽정에게 계좌 이체를 합니다.
- 홍길동의 입장에서는 출금이 되는 내역입니다.
- 임꺽정의 입장에서는 임금이 되는 내역입니다.
- 계좌이체를 기록하는 history 테이블이 필요합니다.
- 계좌이체를 하게 되면 계좌의 잔액이 변경되어야 합니다.
user 테이블 생성
create table user_tb(
number int auto_increment primary key,
name varchar(20) not null,
username varchar(20) unique not null,
password varchar(100) not null,
created_at timestamp not null
);
account 테이블 생성
create table account_tb(
number int primary key,
password varchar(100) not null,
balance int not null,
created_at timestamp not null,
user_number int,
foreign key(user_number) references user_tb(number)
);history 테이블 생성
create table history_tb(
number int auto_increment primary key,
amount int not null,
w_balance int,
d_balance int,
w_account_number int,
d_account_number int,
created_at timestamp not null,
foreign key(w_account_number) references account_tb(number),
foreign key(d_account_number) references account_tb(number)
);2. 쿼리작성하기
- 위 요구 사항을 만족할 수 있는 쿼리를 작성하세요.
- 몇 개의 쿼리가 필요할까요?
user등록
insert into user_tb(name, username, password, created_at) values('홍길동', 'hong', 1234, now());
insert into user_tb(name, username, password, created_at) values('임꺽정', 'im', 5678, now());
insert into user_tb(name, username, password, created_at) values('김아무', 'kim', 4321, now());account등록
insert into account_tb(number, password, balance, created_at, user_number) values (1111, '1234', 1000, now(), 1);
insert into account_tb(number, password, balance, created_at, user_number) values (2222, '5678', 1000, now(), 2);
insert into account_tb(number, password, balance, created_at, user_number) values (3333, '9765', 1000, now(), 3);
insert into account_tb(number, password, balance, created_at, user_number) values (4444, '4321', 1000, now(), 2);정보조회
set @f_name := '홍길동';
select *
from account_tb a
left join user_tb u on a.user_number = u.number
where u.name = @f_name;계좌이체 (홍길동이 임꺽정에게 500원 이체 후 이체 내역)
set @amount = 500; -- 이체금액
set @w_number = 1111; -- 홍길동 출금계좌
set @d_number = 2222; -- 임꺽정 입금계좌
update account_tb set balance = balance - @amount where number = @w_number;
update account_tb set balance = balance + @amount where number = @d_number;
set @w_balance = (select balance from account_tb where number = @w_number); -- 출금계좌잔액
set @d_balance = (select balance from account_tb where number = @d_number); -- 입금계좌잔액
insert into history_tb(amount, w_balance, d_balance, w_account_number, d_account_number, created_at) values(@amount, @w_balance, @d_balance, 1111, 2222, now());
select * from history_tb;3. ER-Diagram 제출하기
728x90
