(DB) 데이터베이스 설계

사용자의 다양한 요구사항을 고려하여 데이터베이스를 생성하는 과정

설계 과정 > 오류가 발견 > 변경 필요 > 이전 단계 설계 내용 변경 가능

 

1단계 : 요구사항 분석

목적	데이터베이스 용도 파악	ex) 쇼핑몰
결과물	요구 사항 명세서	- 쇼핑몰에 가입하려면 아이디,비밀번호,이름 등을 입력해야 한다. - 가입한 회원에게는 등급과 적립금이 부여된다 - 회원은 회원아이디로 식별한다 - 상품에 대한 상품번호,상품명, 재고량,단가 정보를 유지해야한다 - 상품은 상품번호를 식별한다 - 회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다. - 회원이 상품을 주문하면 주문에 대한 주문번호, 주문수량, 배송지, 주문일자 정보를 유지해야한다. 등
주요작업	- 실제로 사용할 사용자 범위 결정
	- 사용자가 수행하는 업무 분석
	- 요구 사항 수집
	- 수집된 요구 사항에 대한 분석 결과 명세서로 작성

2단계 : 개념적 설계

목적	DBMS에 독립적인 개념적 구조 설계	ex) 쇼핑몰
결과물	개념적 스키마(E-R 다이어그램)	개체와 속성 추출 개체 : 회원, 상품 속성 : 아이디,비밀번호,이름,등급,적립금 　　　,상품명, 재고량, 단가 키속성 : 회원 아이디,상품번호 관계 추출 - 주문같은 경우, 회원이 상품을 주문 시 생성되는 정보이기 때문에 특정관계(주문)의 속성이다. 관계 : 주문 회원-상품 개체관계: 다대다(N:M) 참여 특성: 회원-선택적 참여, 상품- 선택적 참여 주문관계속성 : 주문번호, 주문수량, 배송지, 주문일자 E-R 다이어그램
주요작업	요구사항 분석 결과를 기반으로 중요한 개체를 추출하고, 개체간의 관계를 결정하여 E-R 다이어그램으로 표현
작업과정	1. 개체추출, 각 개체의 주요 속성과 키 속성 선별 2. 개체 간의 관계 결정 3. E-R 다이어그램으로 표현
개체와 속성 추출	저장할만한 가치가 있는 중요 데이터를 가진 사람이나 사물
개체 추출	요구 사항 문장에서 업무와 관련이 깊은 의미 있는 명사 -업무와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외 - 의미가 같은 명사가 여러개일 경우는 대표 명사 하나만 선택
개체 간 관계 추출	개체간의 의미 있는 연관성을 표현한 동사추출 - 매핑 카디널리티: 1:1, 1:N, N:M - 참여 특성: 필수적 참여, 선택적 참여

3단계 : 논리적 설계

목적	DBMS에 적합한 논리적 구조 설계
주요작업	E-R 다이어그램을 릴레이션 스키마로 변환
	RULE 1 : 모든 개체는 릴레이션으로 변환 　- 개체이름 : 릴레이션 이름 　- 개체의 속성 : 릴레이션의 속성 　- 개체의 키 속성 : 릴레이션의 식별자
	RULE 2 : 다대다 관계는 릴레이션으로 변환 　- 관계의 이름 : 릴레이션의 이름 　- 관계의 속성 : 릴레이션의 속성 　- 관계에 참여하는 개체를 규칙 1에 따라 릴레이션으로 변화한 후 이 릴레이션의 기본키를 관계 릴레이션에 포함시켜 외래키로 지정하고 외래키들을 조합하여 관계 릴레이션의 기본키로 지정
	RULE 3 : 일대다 관계는 외래키로 표현 　- 일대다 관계에서 일측 개체 릴레이션의 기본키를 다측 개체 릴레이션에 포함 　- 관계의 속성들도 다측 개체 릴레이션에 포함시킴 　- 약한개체가 참여하는 일대다 관계는 외래키를 포함해서 기본키로 지정 　- 다측 개체 릴레이션은 외래키를 포함하여 기본키를 지정(약한 개체는 주 개체에 따라 존재 여부가 결정되므로 주 개체의 기본키를 포함해서 기본키 지정)
	RULE 4 : 일대일 관계는 외래키로 표현 　- 일반적인 일대일 관계는 외래키를 서로 주고 받는다 　- 일대일 관계에 필수적으로 참여하는 개체의 릴레이션만 외래키를 받는다 　- 모든 개체가 일대일 관계에 필수적으로 참여하면 릴레이션을 하나로 합친다 ex) 결혼테이블
	RULE 5 : 다중 값 속성은 릴레이션으로 변환
	- 릴레이션 스키마 변환 후 속성의 데이터타입, 길이, null값 허용 여부, 기본 값, 제약조건 등을 세부적으로 결정하고 결과를 문서화시킴
결과물	논리적 스키마(릴레이션 스키마), 테이블 명세서

4단계 : 물리적 설계

목적	DBMS로 구현 가능한 물리적 구조 설계
결과물	물리적 스키마
주요작업	- 저장 레코드 양식 설계
	- 접근 경로 설계
	- 레코드 집중의 분석 및 설계
	- 파일의 저장 구조 및 탐색 기법

5단계 : 구현

결과물 & 주요작업	ex) 쇼핑몰
데이터베이스 생성	CREATE TABLE tb_member( m_id VARCHAR(200) NOT NULL COMMENT '회원아이디’, m_pw VARCHAR(200) NOT NULL COMMENT '비밀번호’, m_name VARCHAR(200) NOT NULL COMMENT '이름’, m_age INT COMMENT '나이’, m_job VARCHAR(200) NOT NULL COMMENT '직업’, m_grade VARCHAR(200) NOT NULL DEFAULT 'silver' COMMENT '등급’, m_point INT NOT NULL DEFAULT 0 COMMENT '적립금’, PRIMARY KEY(m_id), CHECK (m_age >= 21) );

 

 

 

 

 

 

실습1. 회원의 레벨의 이름이 관리자인 회원의 정보(이름, 레벨이름,이메일)를 조회하시오.

select 
	m.m_name		as '이름'
	,l.level_name	as '권한'
	,m.m_email		as '이메일'
from
	tb_member as m
	inner join
	tb_member_level as l
	on
	m.m_level = l.level_num
where
	l.level_name ='관리자';

 

실습2. 회원들 중에 2월에 로그인한 회원의 정보(아이디와 이메일)를 조회하시오.

select 
	/*distinct*/ m.m_id as '아이디'
	,m.m_email as '이메일'
from 
	tb_member as m
	inner join
	tb_login as l 
	on 
	m.m_id = l.login_id
where
	month(l.login_date)=2
group by m.m_id
; 

/* between '2021-02-01' and last_day('2021-02-01') > month내장함수로 표현 가능*/

 

실습3. 회원들 중에 로그인하지 않은 회원의 정보(아이디와 이메일)를 조회하시오. >휴면 아이디

select 
	m.m_id as '아이디'
	,m.m_email as '이메일'
from 
	tb_member as m
	left join
	tb_login as l 
	on 
	m.m_id = l.login_id 
where l.login_num is null;

 

실습4. 판매자별 상품명 목록을 조회하시오.

select 
m.m_id as '판매자 아이디'
, group_concat(distinct g_name separator '/ ') as '상품명 목록'
from
	tb_member as m
inner join
	tb_goods as g
on 
m.m_id = g.g_seller_id 
group by m.m_id ;

선생님 풀이

select 
	g.g_seller_id '아이디'
	,group_concat(distinct g.g_name order by g.g_name ) as '상품목록'
from tb_goods as g
group by g.g_seller_id ;

group_concat > null을 제외한 문자열을 합쳐준다(https://fruitdev.tistory.com/16)

 

 

실습5. 회원들 중에 로그인하지 않은 회원의 정보(아이디와 이메일)를 다음과 같이 조회 하시오. (행열전환)

select 
	group_concat(if(m.m_id='id010', m.m_email,null )) as 'id010'
	,group_concat(if(m.m_id='id011', m.m_email,null )) as 'id011'
	,group_concat(if(m.m_id='id012', m.m_email,null )) as 'id012'
from
	tb_member as m
	left join
	tb_login as l 
	on 
	m.m_id = l.login_id 
where l.login_num is null;

 

실습6. 회원들 중에 2021년 3월부터 오늘까지 로그인하지 않은 회원의 정보(아이디와 이메일)를 다음과 같이 조회 하시오.

/* 틀림 */

select 
	m.m_id '아이디'
	,m.m_email '이메일'
from 
	tb_member as m
	join
	tb_login as l
	on
	m.m_id = l.login_id
where l.login_date not between '2021-03-01' and curdate()
group by m.m_id  ;

선생님 풀이

select 
	m.m_id '아이디'
	,m.m_email '이메일'
from 
	tb_member as m
	left join
	tb_login as l
	on
	m.m_id = l.login_id
	and 
	l.login_date > last_day('2021-02-01')
where l.login_num is null;

 

실습7. 회원들의 이메일를 활용하여 회원 이름을 추출하고 회원아이디와 회원이름을 조회하시오.

select
	m.m_id as memberId
	,SUBSTRING_INDEX(m.m_email, '@', 1) as memberEmail
from 
	tb_member as m;

 

실습8. 판매자 별 총 판매액을 조회하시오.

select 
	g.g_seller_id '판매자'
	, sum(g.g_price * o.o_amount) as '총판매액'
from
	tb_goods as g
	inner join
	tb_order as o 
	on 
	g.g_code = o.o_g_code 
	inner join 
	tb_member as m 
	on 
	g.g_seller_id = m.m_id 
group by g.g_seller_id
order by 총판매액 desc;

/* order by 는 실행순서가 가장 마지막이기 때문에 as로 만들어준 별칭을 써줘도 된다 */

> sum함수 못쓰고 group_concat 함수 써볼려고 노력했는데 결국 못품...

 

 

실습9. 회원의 레벨 별 인원을 조회하시오.

select 
	l.level_name '회원레벨'
	,count(m.m_id) as '인원수'
from
	tb_member as m 
	right join
	tb_member_level as l 
	on 
	m.m_level = l.level_num 
group by l.level_num ;

 

실습10. 회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여 회원아이디와 이메일과 함께 조회 하시오.

select 
	m.m_id '회원아이이디'
	,m.m_email '이메일'
	,g.g_name '상품명'
	,max(g.g_price*o.o_amount ) '구매금액'
from
	tb_member as m 
	inner join 
	tb_order as o 
	on 
	m.m_id = o.o_id 
	inner join 
	tb_goods as g 
	on 
	o.o_g_code = g.g_code 
group by m.m_id;

>as 같은거 tab키로 정렬해서 쓰기