[MySQL] 5. 데이터 베이스 모델링

[MySQL] 5. 데이터 베이스 모델링

1. 기본 명령어와 개념

2.  시나리오: 장부에 고객 방문 기록과 구매 이력 남기기

3. 데이터 베이스 모델링하기

---

 

1. 기본 명령어와 개념

(1) primary Key(=PK/기본키)

-후보키들 중 메인으로 선정되는 키로, 관계형 데이터베이스(RDBMS)에서 레코드의 식별자로 이용하기 가장 적합한 후보 키이다. (=주민등록번호, 사원 번호 등)

-기본키는 null/중복되는 값을 가질 수 없다.

 

(2) Foreign Key(=FK/외래키)

-(항목) references 외부 테이블(항목): 이 구문은 외래 키를 정의하는 데 사용된다.

-외래 키는 한 테이블의 열에 대해 다른 테이블의 열을 참조할 때 사용된다. 이를 통해 테이블 간의 관계를 설정하고, 데이터 무결성을 유지할 수 있다.

? 데이터 무결성: 데이터가 무결하다는 것은, 데이터 생성 이후 변함 없이 생성 당시의 상태를 유지하고 있음을 의미한다.

 

(3) auto_increment

-열에 대한 속성으로, 해당 열의 값이 자동으로 증가하도록 설정한다.

-보통 이 속성은 주로 기본 키(Primary key) 열에 사용되며, 새로운 레코드가 삽입될 때마다 자동적으로 값이 증가하여, 고유한 식별자를 생성한다.

-일반적으로 auto_increment 속성은 정수 데이터 타입에만 사용된다. 대표적으로 INT/BIGINT와 같은 정수형 자료형과 함께 사용된다!

---

2. 시나리오: 장부에 고객 방문 기록과 구매 이력 남기기

-만약 데이터 베이스라는 개념이 없다면, 엑셀/장부를 활용해 기록을 남길 것이다.

-처음에는 온 시간/날짜 순으로 정렬되어 있을 것이다.

아래의 표를 살펴보면 방문했지만 물건을 구매하지 않은 손님도 있다.

 

-고객 방문 기록을 구매/비구매 방문으로 구분할 수도 있다.

하지만 비구매 방문 고객의 표에서는 구매한 물건/단가/수량을 쓰지 않기 때문에, 공간이 낭비된다.

 

-그렇다면 다시 표를 구문해, (1) 고객테이블과 (2) 고객들이 구매한 물건들의 테이블로 구분할 수 있을 것이다.

-하지만 고객 테이블을 살펴보면, 여러번 방문한 고객의 경우 여러번 중복해서 표에 올라간 것을 알 수 있다.

 

-중복되는 고객들의 데이터를 지우고, 이름을 PK(Primary Key)로 설정한다. 

-또한 구매 테이블만 보면 누가 구매했는지 알 수 없기 때문에, 구매 테이블에 고객명을 추가하면 더욱 보기 편해진다.

 

 

-현재 고객 테이블 내의 "고객 이름" 항목은 Primary Key이다.(=PK)

-구매 테이블 내에서의 "고객 이름"은 Foriegn Key이다.(=FK, 외래키)

 

학생 : 성적 ----> 1:N

직원 : 급여 ----> 1:N

 

=> 고객 : 구매한 물건 ----> 1:N

=> 고객은 물건을 여러번 구매할 수 있다.

하지만 고객은 단 한 명의 객체(ex: 김경호, 윤종신, 임재범)를 가지며, 중복될 수 없다.

따라서 고객과 물건의 관계는 1:N의 관계를 가지게 된다.

 

-테이블 설계 단계: 위에서 정리한 것을 바탕으로 테이블을 설계해보자.

-Foriegn Key(외래키): 외부 테이블을 참조할 수 있는 키.

아래의 테이블에서는 구매 테이블 내의 "고객 이름" 항목이 FK이다.

 

---

2. 데이터 베이스 모델링 하기

 

-- 데이터 집합 생성
create database db_denco_market;

use db_denco_market;

-- db 삭제 명령어
-- drop database db_denco_market

-- 사전 기반 지식 : mysql은 대소문자를 구별하지 않습니다.
-- 하지만 구별하는 RDBMS도 있습니다.

 -- 테이블 설계하기
 
 -- 고객 테이블 생성
 create table userTBL(
     userName char(3) not null primary key,
     birthYear int not null,
     addr char(2) not null,
     mobile char(12)
 );
 
--  show tables;
--  select*from userTBL;
--  desc userTBL;

-- 구매 테이블 생성
create table buyTBL(
    id int not null auto_increment,
    userName char(3) not null,
    prodName char(3) not null,
    price int not null,
    amount int,
    primary key(id),
    foreign key(userName) references userTBL(userName)
);

 

 

 

-- 데이터 입력하기

insert into usertbl(userName,birthYear,addr,mobile) 
values('이승기',1987,'서울','011-1111-111'),
	  ('김경호',1987,'전남','019-333-333'),
      ('윤종신',1987,'경남',''),
      ('임재범',1987,'서울','011-222-222'),
      ('은지원',1987,'경북','011-444-444'),
      ('조관우',1987,'경기','011-555-555'),
      ('김범수',1987,'경남','011-1111-111'),
      ('조용필',1987,'경기','011-1111-111'),
      ('바비킴',1987,'서울','011-1111-111'),
      ('성시경',1987,'경남',null);

desc usertbl;
-- insert into usertbl(userName,birthYear,addr,mobile)
-- values('김경호',1971,'전남','019-333-3333');

-- 다른 테이블 삭제하고자 하는 테이블을 참고하고 있다면,
-- 테이블 삭제가 바로 안 된다.
-- 굳이 삭제하고 싶다면, 참조 테이블을 먼저 삭제해야 한다.alter
-- drop table buytbl;
-- drop table usertbl;

-- delete 데이터를 삭제하는 명령어이다.
-- delete from usertbl;

-- 이승기 찾아보기
-- select * from usertbl  
-- where userName ='이승기'