[데이터베이스 기초] 3. 데이터 모델링 - 공유 킥보드 DB 완성

- 공유 킥보드 DB

 

 

앞서 정규화 과정을 통해 각각의 데이터베이스에서 발생하는 오류와 정규화를 통해 해결하는 방법을 공부하고 위 사진과 같이 고객테이블, 대여 테이블,  가격 테이블, 회사 테이블로 나누었습니다.

 

앞에서 배웠던 것처럼 데이터베이스를 좀 더 완벽하게 만들기 위해 각각의 테이블을 어떻게 수정하면 좋을지 알아보고 이에 따라 테이블을 수정해보겠습니다.

 

 

 

- 고객 테이블

 

 

• 회원 가입 시 입력 받는 데이터 추가
• 기본키를 회원의 ID 대신 고유 번호로 설정
• 비밀번호는 값을 그대로 넣으면 보안에 취약

 

 

 

- 대여 테이블

 

 

• 고객 번호를 외래키로 사용
• 대여 장소보다 정확하게 위도와 경도 데이터를 받음
• 대여 상태를 입력 받아 킥보드를 대여하는 것인지 반납하는 것인지를 기록
• 브랜드명과 연식을 함께 외래키로 사용하는 대신 킥보드 ID를 생성하여 외래키로 사용

 

 

 

- 킥보드 테이블

• 킥보드 ID를 기본키로 사용하고 테이블 명을 킥보드로 설정
• 브랜드 이름 대신 브랜드 고유 번호를 생성하여 외래키로 사용
• 가격을 계산하는 쿼리가 많이 사용되어 분당 가격 컬럼을 킥보드 테이블에 입력
• 기본료나 분당 가격이 변경되는 경우가 적기 때문에 데이터 불일치를 감안함

 

 

 

- 브랜드 테이블

 

 

• 브랜드 번호를 기본키로 사용하고 테이블 명을 브랜드로 설정
• 킥보드가 없더라도 새로운 브랜드를 등록할 수 있음

 

 

 

- 최종 공유 킥보드 DB

 

위에서 변경한 테이블을 바탕으로 IE를 작성하면 아래와 같이 작성할 수 있습니다.

 

이제 작성된 IE를 바탕으로 공유 킥보드 DB테이블을 직접 코드를 통해 구현해보겠습니다.

 

-- 3차 정규화 설정된 테이블을 수정하여 최종 형태의 공유 킥보드 DB를 만드세요.
CREATE TABLE customer(
    customer_number     VARCHAR(10) PRIMARY KEY, 
    name                VARCHAR(10) NOT NULL,
    id                  VARCHAR(15) NOT NULL UNIQUE,
    pw                  VARCHAR(20) NOT NULL,
    phone_number        VARCHAR(11),
    birth_date          DATE
);


CREATE TABLE brand(
    brand_number        INT PRIMARY KEY,
    name                VARCHAR(20)     NOT NULL UNIQUE,
    company             VARCHAR(20)     NOT NULL
);

CREATE TABLE kickboard(
    id                  VARCHAR(4)      PRIMARY KEY,
    brand_number        INT             NOT NULL,
    model_year          INT             NOT NULL,
    basic_price         INT             NOT NULL,
    price_per_minute    INT             NOT NULL,
    FOREIGN KEY (brand_number) REFERENCES brand(brand_number)
);

CREATE TABLE borrow(
    customer_number     VARCHAR(10),
    rental_time         DATETIME,
    lat_location        FLOAT,
    lon_location        FLOAT                   NOT NULL,
    rental_status       ENUM('대여', '반납')    NOT NULL,
    kickboard_id        VARCHAR(4)              NOT NULL,
    CONSTRAINT borrow_pk PRIMARY KEY (customer_number, rental_time),
    FOREIGN KEY (customer_number) REFERENCES customer(customer_number),
    FOREIGN KEY (kickboard_id) REFERENCES kickboard(id)
);


-- 주석을 해제하여 전체 테이블의 구조 및 외래키 설정을 확인하세요.
SELECT * FROM information_schema.table_constraints WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
DESC customer;
DESC brand;
DESC kickboard;
DESC borrow;

 

 

코드 결과

 

 

 

 

 

 

# 출처 엘리스 AI트랙 7기 -  [수업자료]데이터 모델링