파라미터: 클라이언트가 서버에 보내는 자료
외래키: 테이블간의 1:1 연관관계(속성대 속성)를 나타냄(부모릴레이션, 자녀릴레이션)
무결성 분석
1. 개체 무결성- 기본키
2. 도메인 무결성- 속성
3. 참조 무결성- 외래키
외래키 속성지정
1. on delete cascade
부모릴레이션에서 delete할때 자식릴레이션도 함께 삭제
2. on update cascade
부모릴레이션에서 update할때 자식릴레이션도 함께 수정
3. on delete set null
부모릴레이션에서 delete할때 자식릴레이션 외래키는 nul l값으로 대체
실습
member테이블 생성
create table member(
id varchar(4) primary key,
name varchar(10),
addr varchar(20)
);
car 테이블 생성
create table car(
no int primary key auto_increment,
carNum varchar(4) not null,
carInfo varchar(10),
id varchar(4),
foreign key(id)
references member(id)
on delete cascade
on update cascade
);
member 레이블 삽입
insert into member values('a000','kim','suwon'),
('a001','Park','seoul'),('a002','Choi','busan'),('a003','Lee','incheon');
+------+------+---------+
| id | name | addr |
+------+------+---------+
| a000 | kim | suwon |
| a001 | Park | seoul |
| a002 | Choi | busan |
| a003 | Lee | incheon |
+------+------+---------+
car 레이블 삽입
insert into car values(default,'1111','sonata','a000'),
(default,'2222','K3','a001'),(default,'3333','K5','a000'),(default,'4444','K3','a003');
+----+--------+---------+------+
| no | carNum | carInfo | id |
+----+--------+---------+------+
| 1 | 1111 | sonata | a000 |
| 2 | 2222 | K3 | a001 |
| 3 | 3333 | K5 | a000 |
| 4 | 4444 | K3 | a003 |
+----+--------+---------+------+
member 테이블에 id가 a003인 회원의 id를 a004로 변경해보기
update member set id='0004' where id='0003';
+------+------+---------+
| id | name | addr |
+------+------+---------+
| a000 | kim | suwon |
| a001 | Park | seoul |
| a002 | Choi | busan |
| a004 | Lee | incheon |
+------+------+---------+
자식릴레이션인 car테이블의 id a003이 a004로 변경되었는지 확인
select * from car;
+----+--------+---------+------+
| no | carNum | carInfo | id |
+----+--------+---------+------+
| 1 | 1111 | sonata | a000 |
| 2 | 2222 | K3 | a001 |
| 3 | 3333 | K5 | a000 |
| 4 | 4444 | K3 | a004 |
+----+--------+---------+------+
변경됨을 확인
member 테이블에 id가 a004인 회원의 정보를 삭제하기
delete from member where id='a004';
+------+------+-------+
| id | name | addr |
+------+------+-------+
| a000 | kim | suwon |
| a001 | Park | seoul |
| a002 | Choi | busan |
+------+------+-------+
a004회원 삭제 됨을 확인
자녀릴레이션인 car테이블에 id가 a004인 레이블이 삭제되었는지 확인
select * from car;
+----+--------+---------+------+
| no | carNum | carInfo | id |
+----+--------+---------+------+
| 1 | 1111 | sonata | a000 |
| 2 | 2222 | K3 | a001 |
| 3 | 3333 | K5 | a000 |
+----+--------+---------+------+
id가 a004인 레이블 삭제 됨을 확인
constraint- 외래키에 이름을 지어주는 것.
초기에 DB작업할때 잠시 외래키를 꺼두어야 하는 경우 사용할 수 있다.
(자녀테이블에 자료 삽입할때 부모테이블을 계속 기다릴 수는 없으니)
ERD(Entity Relationship Diagram)
E:표현하고자 하는 대상
R:대상의 관계
D:대상과 그 관계를 그림으로 나타냄
예) 자동차를 등록하는 프로그램
직원은 아이디, 이름, 부서가 존재(표현하고자 하는 대상)
자동차는 차번호, 차종 존재(표현하고자 하는 대상)
직원은 자동차를 0~3대 소유가능(대상의 관계)
자동차는 공동소유 불가능(대상의 관계)
등록은 등록일이 있다(대상의 관계)
1:N 관계에서 N 테이블이 자녀, 1테이블이 부모가 된다.(N에서 1의 기본키를 참조)
1:1 관계에서는 어느 한쪽이나 기본키를 가져도 됨.(개발자 마음)
N:M관계에서는 관계를 나타내는 테이블을 만들어서 N,M테이블에 각각 기본키 부여하고 관계테이블에 두개의 외래키 지정.
실습1
장바구니 구현
고객은 id,이름,전화번호
물건은 물건id, 종류, 가격
고객은 여러개의 물건을 구매할 수 있다
하나의 물건을 여러명이 구매할 수 있다
구매시에 수량을 등록
고객 테이블 생성
create table customer(
custId varchar(4) primary key,
name varchar(10) not null,
tel varchar(10) not null
);
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| custId | varchar(4) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| tel | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
물건 테이블 생성
create table product(
prodId varchar(10) primary key,
kind varchar(10) not null,
price int not null default 0
);
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| prodId | varchar(10) | NO | PRI | NULL | |
| kind | varchar(10) | NO | | NULL | |
| price | int | NO | | 0 | |
+--------+-------------+------+-----+---------+-------+
장바구니 테이블 생성
create table cart(
custId varchar(4),
prodId varchar(10),
cnt int not null default 0,
cartNum varchar(14) primary key,
foreign key(custId) references customer(custId)
on delete cascade on update cascade,
foreign key(prodId) references product(prodId)
on delete cascade on update cascade
);
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| custId | varchar(4) | YES | MUL | NULL | |
| prodId | varchar(10) | YES | MUL | NULL | |
| cnt | int | NO | | 0 | |
| cartNum | varchar(14) | NO | PRI | NULL | |
+---------+-------------+------+-----+---------+-------+
고객 정보 입력
insert into customer values('a000','kim','9570-9334'),
('a001','lee','2203-4457'),('a002','park','9330-0023'),
('a003','choi','2341-9003');
+--------+------+-----------+
| custId | name | tel |
+--------+------+-----------+
| a000 | kim | 9570-9334 |
| a001 | lee | 2203-4457 |
| a002 | park | 9330-0023 |
| a003 | choi | 2341-9003 |
+--------+------+-----------+
물건 정보 입력
insert into product values('aa0000','aa',5000),
('bb0000','bb',3500),('aa0001','aa',580),
('aa0002','aa',9000),('cc0000','cc',15000),
('bb0001','bb',4500),('aa0003','aa',2300);
+--------+------+-------+
| prodId | kind | price |
+--------+------+-------+
| aa0000 | aa | 5000 |
| aa0001 | aa | 580 |
| aa0002 | aa | 9000 |
| aa0003 | aa | 2300 |
| bb0000 | bb | 3500 |
| bb0001 | bb | 4500 |
| cc0000 | cc | 15000 |
+--------+------+-------+
장바구니 정보 입력
insert into cart values('a000','aa0000',2,'a000aa0000'),('a000','aa0003',1,'a000aa0003'),
('a002','bb0000',5,'a002bb0000'),('a001','aa0003',1,'a001aa0003'),('a002','bb0001',1,'a002bb0001'),
('a003','cc0000',2,'a003cc0000'),('a002','aa0000',2,'a002aa0000'),('a000','cc0000',5,'a000cc0000');
+--------+--------+-----+------------+
| custId | prodId | cnt | cartNum |
+--------+--------+-----+------------+
| a000 | aa0000 | 2 | a000aa0000 |
| a000 | aa0003 | 1 | a000aa0003 |
| a000 | cc0000 | 5 | a000cc0000 |
| a001 | aa0003 | 1 | a001aa0003 |
| a002 | aa0000 | 2 | a002aa0000 |
| a002 | bb0000 | 5 | a002bb0000 |
| a002 | bb0001 | 1 | a002bb0001 |
| a003 | cc0000 | 2 | a003cc0000 |
+--------+--------+-----+------------+
데이터베이스 프로젝트
요구사항
도서목록 테이블 생성
create table bookInfo
(
b_no int primary key auto_increment,
b_name varchar(20) not null,
b_date date not null,
b_write varchar(20) not null,
b_pud varchar(20) not null
);
도서목록 데이터 삽입
insert into bookinfo values(1, '드래곤볼', '1984-07-18', '토리야마 아키라', '주간소년점프'),
(2, '원피스', '1997-7-20', '오다에이치로', '주간소년점프'),
(3, '나루토', '2000-3-3', '키시모토마사시', '주간소년점프'),
(4, '원펀맨', '2009-7-3', 'ONE', '점프코믹스'),
(5, '귀멸의칼날', '2020-12-4', '고토게 코요하루', '주간소년점프');
고객정보 테이블 생성
create table memberInfo(
m_id varchar(10) primary key,
m_name varchar(10) not null,
m_pwd varchar(10) not null,
m_tel varchar(13) not null,
m_age int default 0,
m_sex char(1),
m_point int default 0
);
고객정보 데이터 삽입
insert into memberInfo values
('aaaa', '홍길동','aaaa','010-0012-1200', 56, 'm', 1004),
('asdf', '길성준','dddd','010-5900-0480', 56, 'm', default),
('owl', '이상수','cccc','010-5004-0708', 56, 'm', 15),
('glew', '박지효','qwww','010-0700-6550', 56, 'f', 112),
('peft', '박은지','wwdw','010-4750-9750', 56, 'f', 185);
+------+-----------+-------+---------------+-------+-------+---------+
| m_id | m_name | m_pwd | m_tel | m_age | m_sex | m_point |
+------+-----------+-------+---------------+-------+-------+---------+
| aaaa | 홍길동 | aaaa | 010-0012-1200 | 56 | m | 1004 |
| asdf | 길성준 | dddd | 010-5900-0480 | 56 | m | 0 |
| glew | 박지효 | qwww | 010-0700-6550 | 56 | f | 112 |
| owl | 이상수 | cccc | 010-5004-0708 | 56 | m | 15 |
| peft | 박은지 | wwdw | 010-4750-9750 | 56 | f | 185 |
+------+-----------+-------+---------------+-------+-------+---------+
도서대여대장 및 리뷰 테이블 생성
create table bookRent(
m_id varchar(10),
b_no int,
r_rent timestamp not null,
r_return timestamp,
r_no varchar(14) primary key,
r_review varchar(40),
foreign key(m_id) references memberInfo(m_id)
on delete set null on update cascade,
foreign key(b_no) references bookInfo(b_no)
on delete cascade on update cascade
);
도서대여대장 및 리뷰 테이블 데이터 삽입
insert into bookRent(m_id, b_no, r_rent, r_no, r_review) values
('aaaa', 1, now(), 'aaaa1', '이 책은 저의 마음을 위로해주었습니다.'),
('aaaa', 4, now(), 'aaaa4', '내용이 너무 지루했습니다. 불면증이 있으신 분 추천.'),
('asdf', 1, now(), 'asdf1', '보는 내내 시간 가는줄 몰랐습니다. 추천합니다.'),
('glew', 4, now(), 'glew4', '우리 아이가 재밌다고하네요. 아이들에게 추천해요.'),
('glew', 2, now(), 'glew2', '눈물 펑펑 쏟았어요. 울고 싶은 분 꼭 읽으세요.'),
('glew', 5, now(), 'glew5', '잠시나마 동심의 세계로 돌아간것 같습니다.'),
('peft', 3, now(), 'peft3', '내용이 너무 잔인하네요. 아이들은 금지!'),
('peft', 2, now(), 'peft2', '이 작가의 책을 좋아해서 읽었습니다. good!');
리뷰가 없는 데이터 삽입
insert into bookRent(m_id, b_no, r_rent, r_no) values
('peft',1,now(),'peft1');
반납날짜 데이터 삽입
update bookrent set r_return=now() where r_no='aaaa1';
update bookrent set r_return=now() where r_no='aaaa4';
update bookrent set r_return=now() where r_no='asdf1';
update bookrent set r_return=now() where r_no='glew2';
update bookrent set r_return=now() where r_no='glew4';
update bookrent set r_return=now() where r_no='glew5';
update bookrent set r_return=now() where r_no='peft1';
update bookrent set r_return=now() where r_no='peft2';
update bookrent set r_return=now() where r_no='peft3';
+------+------+---------------------+---------------------+-------+----------------------------------------------------------+
| m_id | b_no | r_rent | r_return | r_no | r_review |
+------+------+---------------------+---------------------+-------+----------------------------------------------------------+
| aaaa | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:09:55 | aaaa1 | 이 책은 저의 마음을 위로해주었습니다. |
| aaaa | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:14 | aaaa4 | 내용이 너무 지루했습니다. 불면증이 있으신 분 추천. |
| asdf | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:10:31 | asdf1 | 보는 내내 시간 가는줄 몰랐습니다. 추천합니다. |
| glew | 2 | 2022-08-02 16:03:43 | 2022-08-02 16:10:41 | glew2 | 눈물 펑펑 쏟았어요. 울고 싶은 분 꼭 읽으세요. |
| glew | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:44 | glew4 | 우리 아이가 재밌다고하네요. 아이들에게 추천해요. |
| glew | 5 | 2022-08-02 16:03:43 | 2022-08-02 16:10:49 | glew5 | 잠시나마 동심의 세계로 돌아간것 같습니다. |
| peft | 1 | 2022-08-02 16:05:53 | 2022-08-02 16:11:02 | peft1 | NULL |
| peft | 2 | 2022-08-02 16:03:43 | 2022-08-02 16:11:20 | peft2 | 이 작가의 책을 좋아해서 읽었습니다. good! |
| peft | 3 | 2022-08-02 16:03:43 | 2022-08-02 16:11:22 | peft3 | 내용이 너무 잔인하네요. 아이들은 금지! |
+------+------+---------------------+---------------------+-------+-----------------------------------------------------------+
6번. 도서목록에서 원피스를 지웠을때 도서대여대장에서도 지워지는지 테스트
delete from bookInfo where b_name='원피스';
+------+-----------------+------------+------------------------+--------------------+
| b_no | b_name | b_date | b_write | b_pud |
+------+-----------------+------------+------------------------+--------------------+
| 1 | 드래곤볼 | 1984-07-18 | 토리야마 아키라 | 주간소년점프 |
| 3 | 나루토 | 2000-03-03 | 키시모토마사시 | 주간소년점프 |
| 4 | 원펀맨 | 2009-07-03 | ONE | 점프코믹스 |
| 5 | 귀멸의칼날 | 2020-12-04 | 고토게 코요하루 | 주간소년점프 |
+------+-----------------+------------+------------------------+--------------------+
도서목록에서 원피스 삭제됨 확인
+------+------+---------------------+---------------------+-------+----------------------------------------------------------+
| m_id | b_no | r_rent | r_return | r_no | r_review |
+------+------+---------------------+---------------------+-------+----------------------------------------------------------+
| aaaa | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:09:55 | aaaa1 | 이 책은 저의 마음을 위로해주었습니다. |
| aaaa | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:14 | aaaa4 | 내용이 너무 지루했습니다. 불면증이 있으신 분 추천. |
| asdf | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:10:31 | asdf1 | 보는 내내 시간 가는줄 몰랐습니다. 추천합니다. |
| glew | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:44 | glew4 | 우리 아이가 재밌다고하네요. 아이들에게 추천해요. |
| glew | 5 | 2022-08-02 16:03:43 | 2022-08-02 16:10:49 | glew5 | 잠시나마 동심의 세계로 돌아간것 같습니다. |
| peft | 1 | 2022-08-02 16:05:53 | 2022-08-02 16:11:02 | peft1 | NULL |
| peft | 3 | 2022-08-02 16:03:43 | 2022-08-02 16:11:22 | peft3 | 내용이 너무 잔인하네요. 아이들은 금지! |
+------+------+---------------------+---------------------+-------+-----------------------------------------------------------+
도서대여대장에서 원피스(m_id=2) 도서 삭제됨 확인.
7. 도서에 대한 정보가 갱신되면 그에따라 도서대여대장에서도 갱신되어야 하는지 확인
b_no=3인 도서를 b_no=2로 변경
update bookinfo set b_no =2 where b_no = 3;
+------+-----------------+------------+------------------------+--------------------+
| b_no | b_name | b_date | b_write | b_pud |
+------+-----------------+------------+------------------------+--------------------+
| 1 | 드래곤볼 | 1984-07-18 | 토리야마 아키라 | 주간소년점프 |
| 2 | 나루토 | 2000-03-03 | 키시모토마사시 | 주간소년점프 |
| 4 | 원펀맨 | 2009-07-03 | ONE | 점프코믹스 |
| 5 | 귀멸의칼날 | 2020-12-04 | 고토게 코요하루 | 주간소년점프 |
+------+-----------------+------------+------------------------+--------------------+
도서목록의 b_no=3이 b_no=2로 변경됨을 확인.
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
| m_id | b_no | r_rent | r_return | r_no | r_review |
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
| aaaa | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:09:55 | aaaa1 | 이 책은 저의 마음을 위로해주었습니다. |
| aaaa | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:14 | aaaa4 | 내용이 너무 지루했습니다. 불면증이 있으신 분 추천. |
| asdf | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:10:31 | asdf1 | 보는 내내 시간 가는줄 몰랐습니다. 추천합니다. |
| glew | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:44 | glew4 | 우리 아이가 재밌다고하네요. 아이들에게 추천해요. |
| glew | 5 | 2022-08-02 16:03:43 | 2022-08-02 16:10:49 | glew5 | 잠시나마 동심의 세계로 돌아간것 같습니다. |
| peft | 1 | 2022-08-02 16:05:53 | 2022-08-02 16:11:02 | peft1 | NULL |
| peft | 2 | 2022-08-02 16:03:43 | 2022-08-02 16:11:22 | peft3 | 내용이 너무 잔인하네요. 아이들은 금지! |
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
도서대여대장에서 b_no=3이 b_no=2로 변경됨을 확인.
8.고객이 탈퇴하였을 경우는 그 고객이 대여한 도서도 같이 삭제되지 않고 흔적이 남아서 추후에 인기 도서목록을 분석할 때 사용되어야 함.
고객정보에서 홍길동을 삭제해봄.
delete from memberinfo where m_name='홍길동';
+------+-----------+-------+---------------+-------+-------+---------+
| m_id | m_name | m_pwd | m_tel | m_age | m_sex | m_point |
+------+-----------+-------+---------------+-------+-------+---------+
| asdf | 길성준 | dddd | 010-5900-0480 | 56 | m | 0 |
| glew | 박지효 | qwww | 010-0700-6550 | 56 | f | 112 |
| owl | 이상수 | cccc | 010-5004-0708 | 56 | m | 15 |
| peft | 박은지 | wwdw | 010-4750-9750 | 56 | f | 185 |
+------+-----------+-------+---------------+-------+-------+---------+
고객정보 테이블에서 홍길동의 정보가 사라짐을 확인
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
| m_id | b_no | r_rent | r_return | r_no | r_review |
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
| NULL | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:09:55 | aaaa1 | 이 책은 저의 마음을 위로해주었습니다. |
| NULL | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:14 | aaaa4 | 내용이 너무 지루했습니다. 불면증이 있으신 분 추천. |
| asdf | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:10:31 | asdf1 | 보는 내내 시간 가는줄 몰랐습니다. 추천합니다. |
| glew | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:44 | glew4 | 우리 아이가 재밌다고하네요. 아이들에게 추천해요. |
| glew | 5 | 2022-08-02 16:03:43 | 2022-08-02 16:10:49 | glew5 | 잠시나마 동심의 세계로 돌아간것 같습니다. |
| peft | 1 | 2022-08-02 16:05:53 | 2022-08-02 16:11:02 | peft1 | NULL |
| peft | 2 | 2022-08-02 16:03:43 | 2022-08-02 16:11:22 | peft3 | 내용이 너무 잔인하네요. 아이들은 금지! |
+------+------+---------------------+---------------------+-------+---------------------------------------------------------+
도서대여대장 테이블에서 홍길동의 m_id가 NULL값으로 변경됨을 확인.
9. 고객의 정보가 수정되면 고객대여대장에서도 수정되어야 한다.
길성준의 m_id를(asdf) cccc로 변경
update memberinfo set m_id='cccc' where m_id='asdf';
+------+-----------+-------+---------------+-------+-------+---------+
| m_id | m_name | m_pwd | m_tel | m_age | m_sex | m_point |
+------+-----------+-------+---------------+-------+-------+---------+
| cccc | 길성준 | dddd | 010-5900-0480 | 56 | m | 0 |
| glew | 박지효 | qwww | 010-0700-6550 | 56 | f | 112 |
| owl | 이상수 | cccc | 010-5004-0708 | 56 | m | 15 |
| peft | 박은지 | wwdw | 010-4750-9750 | 56 | f | 185 |
+------+-----------+-------+---------------+-------+-------+---------+
고객정보 테이블에서 길성준의 m_id가(asdf) cccc로 변경됨을 확인
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
| m_id | b_no | r_rent | r_return | r_no | r_review |
+------+------+---------------------+---------------------+-------+--------------------------------------------------------+
| NULL | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:09:55 | aaaa1 | 이 책은 저의 마음을 위로해주었습니다. |
| NULL | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:14 | aaaa4 | 내용이 너무 지루했습니다. 불면증이 있으신 분 추천. |
| cccc | 1 | 2022-08-02 16:03:43 | 2022-08-02 16:10:31 | asdf1 | 보는 내내 시간 가는줄 몰랐습니다. 추천합니다. |
| glew | 4 | 2022-08-02 16:03:43 | 2022-08-02 16:10:44 | glew4 | 우리 아이가 재밌다고하네요. 아이들에게 추천해요. |
| glew | 5 | 2022-08-02 16:03:43 | 2022-08-02 16:10:49 | glew5 | 잠시나마 동심의 세계로 돌아간것 같습니다. |
| peft | 1 | 2022-08-02 16:05:53 | 2022-08-02 16:11:02 | peft1 | NULL |
| peft | 2 | 2022-08-02 16:03:43 | 2022-08-02 16:11:22 | peft3 | 내용이 너무 잔인하네요. 아이들은 금지! |
+------+------+---------------------+---------------------+-------+---------------------------------------------------------+
도서대여대장 테이블에서 길성준의 m_id(asdf)가 cccc로 변경됨을 확인.
11번은 8번과 동일한 작업.
12번은 9번과 동일한 작업.
