728x90

테이블 간의 참조 관계의 종류

1:n관계
- author입장에서의 post와의 관계

n:1관계
- post입장에서의 author와의 관계

1:1관계
- 한 테이블의 레코드가 다른 테이블의 레코드 하나와만 관련
- author테이블과 author_address 테이블로 분리 가능

n:m관계
- 만약 여러 author가 posting 글 1개를 수정할 수 있다면 n:m 관계
- post의 author_id가 multi가 되므로, author_id를 문자열로 하여 여러개 두는 것은 가능.
- 그러나 정규화가 되지 않는 문제 발생
- 해결책
  - 연결테이블을 만들어 1:n, n:1 관계로 풀어주는 것이 일반적인 해결책
  - post_author 와 같은 테이블 생성
  - post_author는 post와 n:1관계, author와 n:1관계

데이터모델링

데이터 모델링이란 구축할 DB 구조를 약속된 표기법에 의해 표현하고 설계해 나가는 과정을 의미

데이터모델링 단계
- 개념적 데이터 모델링
  - ERD 다이어그램을 통해 데이터베이스의 구조를 시각화
  - 이 단계는 추상화된 DB구조를 그리는 것이므로, 필요시 생략 가능
- 논리적 데이터 모델링
  - 구체적으로 데이터베이스 설계. 각 데이터의 타입, 관계, key등을 지정
  - 엑셀 또는 ERD 사용
- 물리적 데이터 모델링
  - 실제 데이터베이스를 만드는 과정이고, SQL 작성이 여기에 해당

개념적 데이터 모델링
- ERD (Entity Relationship Diagram)란 'Entity 개체'와 'Relationship 관계'를 중점적으로 표시하는 데이터베이스 구조를 한 눈에 알아보기 위해 그려놓는 다이어그램
- ERD를 사용한 개념적 데이터 모델링
- 관계 표시 : mandatory, optional 키워드는 항상 상대방
  https://dbguide.tistory.com/entry/모델링-IE-표기법 참고
- https://app.diagrams.net/ 등의 사이트에 UI 제공

논리적 데이터 모델링
- 개념적인 데이터 모델이 완성되면, 구체화된 업무 중심의 데이터 모델을 설계
- 이 단계에서 업무에 대한 Key, 속성, 관계등 구체화된 정보를 표시
- https://dbdiagram.io/d/ordersystem-655f183d3be1495787947418 참고

물리적 데이터 모델링
- 최종적으로 데이터 베이스에 실제 테이블을 만드는 SQL문 작업

모델링 실습- 스키마 설계

주문관리 서비스 설계

요구사항
- 모든 테이블 컬럼 자유설계
- ordersystem DB 생성
- 해당 서비스에서 회원가입 가능
  - 회원의 종류가 user,admin,seller로 구성
- 회원이 상품과 재고수량을 등록
  - 상품명,상품가격,재고
  - 누가 등록했는지에 대한 정보가 남아야 함에 유의
- 회원이 여러 상품을 한꺼번에 주문가능
  - ORDER, ORDER_DETAILS

산출물
- ER다이어그램을 통해 추상화

릴레이셔널 스키마를 통해 구체화

테이블 생성문

use board;

-- Ordersystem Practice

-- Create members table
create table members(id INT not null auto_increment, name VARCHAR(255) not null,
email VARCHAR(255) not null, password VARCHAR(255) not null,
role enum('user', 'admin', 'seller') default 'user',
primary key(id));

-- Create item table
create table items(id INT not null auto_increment, product_name VARCHAR(255) not null,
product_price decimal(10,3) not null, product_number BIGINT not null,
seller_id INT not null, foreign key(seller_id) references members(id),
primary key(id));

-- Create orders table
create table orders(id INT not null auto_increment, member_id INT not null,
ordered_date DATETIME default current_timestamp,
primary key(id),
foreign key(member_id) references members(id));

-- Create orders_details table
create table orders_details(id INT not null auto_increment, order_id INT not null, item_id INT not null,
ordered_num INT NOT NULL, primary key(id),
foreign key(order_id) references orders(id),
foreign key(item_id) references items(id));

-- Insert into members
insert into members(id, name, email, password, role) values (1, "Summer", "summer@gmail.com", "summer123", 'admin');
insert into members(id, name, email, password, role) values (2, "Jake", "jake@gmail.com", "jake123", 'user');
insert into members(id, name, email, password) values (3, "Tom", "tom@naver.com", "tom123");
insert into members(id, name, email, password, role) values (4, "Kyle", "kyle@yahoo.com", "kyle123", 'user');
insert into members(id, name, email, password, role) values (5, "Ella", "ella@yahoo.com", "ella1223", 'seller');

-- Insert into items
insert into items(id, product_name, product_price, product_number, seller_id)
values (1, "book", 13.2, 50, 1);
insert into items(id, product_name, product_price, product_number, seller_id)
values (2, "laptop", 492.1, 10, 2);
insert into items(id, product_name, product_price, product_number, seller_id)
values (3, "phone", 290.8, 20, 3);
insert into items(id, product_name, product_price, product_number, seller_id)
values (4, "pear", 4.2, 300, 4);
insert into items(id, product_name, product_price, product_number, seller_id)
values (5, "apple", 1.7, 450, 5);

-- Insert into orders
insert into orders(id, member_id) values (1, 1);
insert into orders(id, member_id) values (2, 2);
insert into orders(id, member_id) values (3, 2);
insert into orders(id, member_id) values (4, 1);
insert into orders(id, member_id) values (5, 2);

-- Insert into orders_details
insert into orders_details(id, order_id, item_id, ordered_num)
values(1, 1, 1, 10);
insert into orders_details(id, order_id, item_id, ordered_num)
values(2, 1, 2, 20);
insert into orders_details(id, order_id, item_id, ordered_num)
values(3, 2, 1, 30);

DB 설계 추가

데이터베이스 설계

1.요구사항 분석 및 명세화

(1)목표: 내가 이 웹사이트를 만들 때 필요한 기능들을 염두해두고 데이터베이스를 어떻게 활용해야 하는지 용도를 파악한다

ex) 식료품 쇼핑몰을 만들 때 어떤 정보들을 저장해야 할까? 큰 틀 : 회원, 상품, 주문, 제조업체 등

→큰 틀을 기준으로 필요할 데이터들을 쭉 적어보기

id, 비밀번호, 전화번호, 기본배송지, 생년월일, 별명, 등급, 추천인, 포인트, 이메일, 성별, 쿠폰, 제품명, 재고, 가격, 원산지, 용량, 제품의 상세정보,리뷰, 주문번호, 주문수량, 주문일자, 제조업체명, 유통기한, 주소, 연락처, 담당자 등

(2) 결과물 : 요구 사항 명세서 작성

2. 개념적 설계

(1) 목표: 요구사항 명세서에서 개체, 속성, 관계 추출 - 요구사항명세서를 자세히 서술하는 게 중요한 이유

개체: 데이터로 표현하고자 하는 현실세계의 개념이나 정보의 단위 → 쉽게 말해 테이블을 뭐로 만들 것인지 찾아보기

속성: 각 개체에 대한 정보 → 쉽게말해 테이블의 컬럼을 뭐로 설정할지 찾아보기

개체와 속성 추출 방식 : 명로하게 딱 정해진 방식은 없으나, 보통 요구사항명세서 상에서 명사들에 밑줄을 쳐본 다음 개체와 속성으로 나눔

개체는 독립적으로 존재할 수 있어야 함.

관계는 개체가 어떤 행동을 해야 생기는 것

개체: 회원

속성 : 아이디, 비밀번호, 이름, 나이, 직업, 등급

개체: 상품

속성: 상품번호, 상품명, 재고량, 단가

개쳬: X(주문은 동사)

속성: 주문번호, 주문수량, 배송지, 주문일자

개체: 제조업체

속성: 제조업체명, 전화번호, 위치, 담당자 (공급은 동사-공급일자와 공급량은 제조업체가 공급을 해야 생김 )

표로 정리하면 이런 식

관계 추출 : 개체간의 관계를 결정함

관계: 개체간의 의미있는 연관성 - 동사찾기(개체간의 연관성을 의미 있게 표현한 동사를 찾아봄)

⇒ 회원은 여러 상품을 주문할 수 있고(유의미한 관계, 회원-상품)

⇒주문에 대한 주문번호, 주문수량, 배송지, 주문일자 정보를 저장해야 한다.(주문의 속성으로써 유의미)

⇒한 상품당 한 제조업체가 공급하고,(유의미한 관계, 상품-제조업체)

⇒공급일자와 공급량 정보를 유지해야 한다.(공급의 속성으로써 유의미)

관계는 관계유형도 정해줘야 함

개체 간 몇대 몇 관계인지 정리하기(1:1, 1:N, N:M)

표로 정리하면 이런식

(2) 결과물: e-r 다이어그램

https://app.diagrams.net 사이트

다이어그램 기호

3. 논리적 설계

(1)목표: E-R 다이어그램을 테이블로 만들고, 각 테이블의 키 설정, 데이터 타입 설정, 제약 조건 설정이 필요함

(2) 결과물 : 테이블 스키마

기본키 : 테이블 내에서 각 레코드를 고유하게 실별하기 위한 컬럼(들)

-두 개 이상의 컬럼들의 조합으로 기본키 설정 가능함(꼭 한 컬럼으로 할 필요 없음)

-NULL값을 가질 수 없음

-필요한 이유: 레코드들을 고유하게 식별할 수 있어야만 내가 원하는 검색, 수정, 삭제, 업데이트가 가능함 - 기본키로 조회했을 때 레코드가 하나만 나와야 함

외래키: 다른 테이블의 기본키를 참조

-외래키는 다른 테이블의 기본키를 참조하므로 한 테이블의 데이터가 다른 테이블의 데이터를 참조 - 외래키는 다른 테이블에서는 기본키

-이를 통해 관련 있는 데이터 간의 연결성을 유지하고 데이터의 일관성을 보장

-필요한 이유: JOIN을 통한 데이터 검색, 테이블 간 관계 표현, 중복 데이터 피함

데이터 타입

(1) 문자열

CHAR(N)	고정 길이의 문자열을 저장, 최대 길이 N을 설정하며, 입력한 길이보다 작은 문자열일 경우 공백으로 채워짐	N보다 긴 문자열 저장 불가
VARCHAR(N)	가변 길이의 문자열을 저장, 최대길이 N을 설정하며, 입력한 길이보다 작은 문자열도 저장 가능	메모리 공간활용 효율적
TEXT	큰 가변 길이의 문자열을 저장할 때 사용, 길이 제한이 없으며, 대용량 텍스트 데이터 저장 가능

(2( 숫자

INT	정수형 데이터 타입(4byte)	-2,147,483,648 ~ +2,147,483,647 또는 0 ~ 4,294,967,295 수 표현 가능
SMALLINT	정수형 데이터 타입(2byte)	-32,768 ~ 32,767 또는 0 ~ 65,536 수 표현 가능
TINYINT	정수형 데이터 타입(1byte)	-128 ~ +127 또는 0 ~ 255 수 표현 가능 → TRUE(1) FALSE(0) 표현 시 사용
FLOAT	부동 소수점 숫자(실수) 데이터 타입

제약 조건

AUTO_INCREMENT	정수형 열에 대해 자동으로 증가하는 값을 할당 데이터를 넣을 때 auto_increment 설정이 된 컬럼엔 데이터를 넣지 않아도 저절로 증가함	CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50) );
UNIQUE	중복된 값을 가지면 안됨 그러나 NULL값은 가질 수 있음(기본키와 차이), NULL 과 NULL은 중복X 이메일 컬럼에 사용 가능 → 보통 이메일을 기본키로 사용하진 않으나 중복된 이메일을 갖진 않으므로	CREATE TABLE user ( user_id VARCHAR(20) PRIMARY KEY, user_name VARCHAR(50), user_email VARCHAR(50) UNIQUE );
NOT NULL	NULL값을 허용하지 않음 기본키는 자체적으로 NOT NULL 제약을 갖고 있기 때문에 굳이 따로 설정 안해도 됨	CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE );
CHECK	컬럼 값이 일정 조건을 만족하도록 설정	CREATE TABLE employees ( employees_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT CHECK (age ≥ 0 ) );
DEFAULT	해당 컬럼에 데이터를 삽입할 때 값이 명시되지 않으면 기본 값이 자동으로 사용	CREATE TABLE user ( idx INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT ad_except TINYINT DEFAULT 1 );
ENUM	속성값으로 들어올 수 있는 값을 지정해 놓음 지정한 값 이외에는 입력이 불가	rank ENUM (’SILVER’, ‘GOLD’, ‘BRONZE’);

MYSQL WORKBENCH

E-R 다이어그램 → 테이블 스키마

(1) 모든 개체는 테이블로 변환한다. → 관게와 개체를 구분해야 함

(2) 다대다(n:m) 관계는 테이블을 따로 만들어준 이후, 관계로부터 파생된 속성을 추가한다. 그리고 각 개체(주문 관계에 참여하는 두 개체, 회원과 상품)의 기본키를 외래키로 사용한다.

(3) 일대다(1:n) 관계는 테이블을 따로 만들지 않고, 외래키로만 포함해준다.

→ 1측 개체에 해당하는 기본키를 n측 개체에 해당하는 테이블에 외래키로 추가

→ 관계로부터 파생된 속성을 n측 속성에 추가

(4) 일대일 관계는 외래키로 표현한다. (우리가 만든 E-R 다이어그램에는 포함되지 않음)

→ 일대다 관계와 같이 테이블을 따로 만들지 않음

(5) 다중 값 속성은 테이블로 변환한다. (우리가 만든 E-R 다이어그램에는 포함되지 않음)

→ 부하직원을 이름대신 사원번호로 넣어주면 됨

(6) 각 테이블 속성별로 데이터타입, 제약조건 설정하기

ex) 상품 테이블

728x90

'Database' 카테고리의 다른 글

[MariaDB] DB 서버 구성 (2)	2023.11.27
[MariaDB] 정규화 (2)	2023.11.27
[MariaDB] DB Dump (1)	2023.11.24
[MariaDB] 저장 프로시저 (1)	2023.11.24
[MariaDB] 사용자 관리 (1)	2023.11.24

⠀⠀⠀⠀⠀

[MariaDB] DB 설계

테이블 간의 참조 관계의 종류

데이터모델링

데이터베이스 설계

1.요구사항 분석 및 명세화

2. 개념적 설계

3. 논리적 설계

E-R 다이어그램 → 테이블 스키마

'Database' 카테고리의 다른 글

티스토리툴바

[MariaDB] DB 설계

테이블 간의 참조 관계의 종류

데이터모델링

데이터베이스 설계

1.요구사항 분석 및 명세화

2. 개념적 설계

3. 논리적 설계

E-R 다이어그램 → 테이블 스키마

'Database' 카테고리의 다른 글

관련글

티스토리툴바