도서관 관리 시스템

문제풀이/데이터베이스 설계 2024. 1. 26. 18:17

테이블 설계 요구사항: 도서관 관리 시스템

도서관 관리 시스템을 위한 데이터베이스를 설계해야 합니다. 다음 요구사항을 충족해야 합니다:

도서(Books): 각 도서는 제목, 저자, 출판사, 출판일, ISBN(국제 표준 도서 번호)을 가지고 있어야 합니다.
회원(Members): 회원은 이름, 이메일, 가입일, 그리고 회원 ID를 가지고 있어야 합니다.
대출 정보(Loans): 어떤 회원이 어떤 책을 대출했는지에 대한 정보가 필요합니다. 대출일, 반납 예정일, 실제 반납일을 포함해야 합니다.
벌금(Fines): 반납이 지연될 경우 회원은 벌금을 지불해야 합니다. 회원별 누적 벌금 정보가 필요합니다.

이제 이 요구사항을 바탕으로 테이블 구조(스키마)를 설계해보세요. 각 테이블의 필드, 필드의 타입, 그리고 테이블 간의 관계를 정의하는 것을 목표로 합니다.

Step1) 최초 작성

접근1 ) 벌금과 회원을 1:1 관계로 생성

유저의 전체 숫자가 벌금을 받은 유저의 숫자보다는 훨씬 많을 것이기 때문에 벌금 받은 유저를 조회해야하는 경우가 많을 경우에는 별도의 테이블을 만드는 편이 효율적일 것 같다.

CREATE TABLE IF NOT EXISTS members (
	id BIGINT AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	email VARCHAR(255) NOT NULL,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP        
);

CREATE TABLE IF NOT EXISTS books (
	id BIGINT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(50) NOT NULL,
    publisher VARCHAR(100) NOT NULL,
    published_at DATETIME,
	isbn BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS borrows (
	id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    book_id BIGINT NOT NULL,
    expected_returned_at DATETIME NOT NULL,
    returned_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES members(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

CREATE TABLE IF NOT EXISTS fines (
	user_id BIGINT NOT NULL,
    fine INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES members(id)
);

접근2) 벌금을 회원 테이블의 한 컬럼으로 사용

벌금 생성에 대한 히스토리를 쌓는 것이 아닌 누적 금액만 알면 되는 것이라면 굳이 별도의 테이블을 만들기보다는 컬럼으로 관리한다면 보다 단순한 구조를 유지할 수 있는 장점이 있을 것 같다.

CREATE TABLE IF NOT EXISTS members (
	id BIGINT AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	email VARCHAR(255) NOT NULL,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    fine INT 
);

CREATE TABLE IF NOT EXISTS books (
	id BIGINT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(50) NOT NULL,
    publisher VARCHAR(100) NOT NULL,
    published_at DATETIME,
	isbn BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS borrows (
	id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    book_id BIGINT NOT NULL,
    expected_returned_at DATETIME NOT NULL,
    returned_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES members(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

Step2) 피드백

2번 방법을 선택하여 답안을 제출하였습니다.

fine이 단순한 경우 2번으로 처리하는 것도 적합하지만, 요구 사항이 더 복잡할 경우에는 1번으로 처리하는 것이 좋다.
ISBN의 경우에는 고유값이므로 UNIQUE를 설정해주는 것이 좋겠다.

ALTER TABLE books ADD UNIQUE (isbn);

Step3) 더미 데이터 추가

쿼리 작동을 확인할 수 있는 더미 데이터들을 추가해주겠습니다.

-- members 테이블에 더미 데이터 추가
INSERT INTO members (name, email, fine) VALUES 
('John Doe', 'john.doe@example.com', 0),
('Jane Smith', 'jane.smith@example.com', 500),
('Emily Johnson', 'emily.johnson@example.com', 0);

-- books 테이블에 더미 데이터 추가
INSERT INTO books (title, author, publisher, published_at, isbn) VALUES 
('The Great Gatsby', 'F. Scott Fitzgerald', 'Charles Scribner\'s Sons', '1925-04-10', 9780743273565),
('To Kill a Mockingbird', 'Harper Lee', 'J. B. Lippincott & Co.', '1960-07-11', 9780061120084),
('1984', 'George Orwell', 'Secker & Warburg', '1949-06-08', 9780451524935),
('Pride and Prejudice', 'Jane Austen', 'T. Egerton, Whitehall', '1813-01-28', 9780198321668),
('The Catcher in the Rye', 'J.D. Salinger', 'Little, Brown and Company', '1951-07-16', 9780316769488);

-- borrows 테이블에 더미 데이터 추가
-- 가정: 현재 날짜는 2024-01-26
INSERT INTO borrows (user_id, book_id, expected_returned_at, returned_at, created_at) VALUES 
(1, 1, '2024-01-30', NULL, '2023-12-26'),  -- John Doe가 'The Great Gatsby'를 대출, 아직 반납 안함
(1, 2, '2024-01-10', '2024-01-20', '2023-12-26'),  -- John Doe가 'To Kill a Mockingbird'를 대출, 이미 반납함
(2, 3, '2024-01-15', NULL, '2023-12-26'),  -- Jane Smith가 '1984'를 대출, 아직 반납 안함 (연체)
(3, 4, '2024-01-25', NULL, '2023-12-26'),  -- Emily Johnson가 'Pride and Prejudice'를 대출, 아직 반납 안함
(3, 5, '2024-01-22', '2024-01-25', '2023-12-26');  -- Emily Johnson가 'The Catcher in the Rye'를 대출, 이미 반납함

-- 현재 날짜를 기준으로 연체된 대출 건에 대한 벌금을 계산하고 members 테이블의 fine 컬럼을 업데이트
SET SQL_SAFE_UPDATES = 0;
UPDATE members
SET fine = fine + COALESCE((
    SELECT SUM(DATEDIFF(CASE 
                             WHEN returned_at IS NULL THEN CURDATE() 
                             ELSE returned_at 
                         END, expected_returned_at) * 100)  -- 하루 연체당 100의 벌금
    FROM borrows
    WHERE members.id = borrows.user_id
      AND expected_returned_at < CASE 
                                   WHEN returned_at IS NULL THEN CURDATE() 
                                   ELSE returned_at 
                               END
), 0)
WHERE id IN (
    SELECT user_id
    FROM borrows
    WHERE expected_returned_at < CASE 
                                   WHEN returned_at IS NULL THEN CURDATE() 
                                   ELSE returned_at 
                               END
);
SET SQL_SAFE_UPDATES = 1;

Step4) SQL 문제

시나리오에 알맞은 sql 문을 작성해보겠습니다.

문제 1: 특정 회원의 현재 대출 정보 조회

요구사항: 회원 ID가 2인 회원이 현재 대출 중인 모든 책의 제목, 대출일, 반납 예정일을 조회합니다.

-- step1) 대출 중인 대출 기록을 추려낸다.
SELECT * FROM borrows br WHERE br.returned_at IS NULL;


-- step2) 대출 기록과 관련된 책의 정보도 추가한다.
SELECT * FROM borrows br 
    INNER JOIN books b 
    ON br.book_id = b.id
    WHERE br.returned_at IS NULL;
    
    
-- step3) 필요한 컬럼만 추려낸다.
SELECT b.title, br.created_at as borrowed_at, br.expected_returned_at
    FROM borrows br
    INNER JOIN books b
    ON br.book_id = b.id
    WHERE br.returned_at IS NULL;
    

-- step4) 이 중에서 회원ID 2인 경우만 추려낸다.
SELECT b.title, br.created_at as borrowed_at, br.expected_returned_at
    FROM borrows br
    INNER JOIN books b
    ON br.book_id = b.id
    WHERE br.returned_at IS NULL
    AND br.user_id = 2;

정답

SELECT b.title, br.created_at as borrowed_at, br.expected_returned_at
    FROM borrows br
    INNER JOIN books b
    ON br.book_id = b.id
    WHERE br.returned_at IS NULL
    AND br.user_id = 2;

문제 2: 연체된 대출 건과 연체료 조회

요구사항: 2024-01-26 기준으로 아직 반납되지 않고 반납 예정일이 지난 모든 대출 정보(회원 이름, 책 제목, 대출일, 반납 예정일, 연체료)를 조회합니다. 연체료는 연체된 일수당 100으로 계산합니다.

-- step1) 아직 반납이 되지 않은 기록을 추려낸다.
SELECT * FROM borrows br WHERE br.returned_at IS NULL;


-- step2) 그 중에서 반납 예정일이 2024-01-26 이전인 데이터를 추려낸다.
SELECT * FROM borrows br
	WHERE br.returned_at IS NULL
        AND br.expected_returned_at < '2024-01-26';
         

-- step3) 반납예정일로부터 2024-01-26까지 몇일이 지났는지 확인한다.
SELECT (DATEDIFF('2024-01-26', br.expected_returned_at)) as delayed_day
	FROM borrows br
        WHERE br.returned_at IS NULL
        AND br.expected_returned_at < '2024-01-26';
        

-- step4) 하루에 100원씩을 계산에서 벌금을 산출한다.
SELECT (DATEDIFF('2024-01-26', br.expected_returned_at) * 100) as fine
	FROM borrows br
        WHERE br.returned_at IS NULL
        AND br.expected_returned_at < '2024-01-26';


-- step5) 필요한 모든 필드들을 추가한다.
SELECT 
    m.id AS user_id,
    b.title AS title,
    br.created_at AS borrowed_at,
    br.expected_returned_at,
    (DATEDIFF('2024-01-26', br.expected_returned_at) * 100) AS fine
	FROM borrows br
		INNER JOIN books b 
		ON br.book_id = b.id
		INNER JOIN members m
		ON br.user_id = m.id
	WHERE br.returned_at IS NULL
	AND br.expected_returned_at < '2024-01-26';

정답

SELECT 
    m.id AS user_id,
    b.title AS title,
    br.created_at AS borrowed_at,
    br.expected_returned_at,
    (DATEDIFF('2024-01-26', br.expected_returned_at) * 100) AS fine
	FROM borrows br
		INNER JOIN books b 
		ON br.book_id = b.id
		INNER JOIN members m
		ON br.user_id = m.id
	WHERE br.returned_at IS NULL
	AND br.expected_returned_at < '2024-01-26';

문제 3: 가장 많이 대출된 책과 해당 책의 대출 횟수 조회

요구사항: 도서관에서 가장 많이 대출된 상위 3권의 책과 해당 책들의 대출 횟수를 조회합니다.

-- step1) 대출 기록에서 도서 id별 대출 횟수를 확인한다.
SELECT br.book_id, count(br.book_id) AS borrow_count FROM borrows br GROUP BY br.book_id;


-- step2) 내림 차순 정렬을 사용해서 대출 횟수가 높은 순으로 정렬한다.
SELECT br.book_id, count(br.book_id) AS borrow_count 
	FROM borrows br
	GROUP BY br.book_id
	ORDER BY borrow_count DESC;
    

-- step3) 그 중 3개만 데이터를 추려낸다.
SELECT br.book_id, count(br.book_id) AS borrow_count 
	FROM borrows br
	GROUP BY br.book_id
	ORDER BY borrow_count DESC
	LIMIT 3;
    
    
-- step4) join을 통해서 필요한 책 정보를 가져온다.
SELECT b.title, count(br.book_id) AS borrow_count 
	FROM borrows br 
		INNER JOIN books b
        ON br.book_id = b.id
	GROUP BY br.book_id 
	ORDER BY borrow_count DESC 
	LIMIT 3;

정답

SELECT b.title, count(br.book_id) AS borrow_count 
	FROM borrows br 
		INNER JOIN books b
        ON br.book_id = b.id
	GROUP BY br.book_id 
	ORDER BY borrow_count DESC 
	LIMIT 3;

STEP5) 정리

다음 번에는 index를 설정할 때와 안할 때의 차이도 함께 확인하는 문제를 풀어보면 좋겠다.

DATEDIFF() 함수를 사용해서 날짜의 차이르 구할 수 있다.

'문제풀이 > 데이터베이스 설계' 카테고리의 다른 글

음식 배달 서비스 시스템 (0)	2024.02.01
온라인 쇼핑몰 주문 관리 시스템 (1)	2024.01.29

ABOUT ME

단순할수록 오래간다 단순할수록 오래간다

테이블 설계 요구사항: 도서관 관리 시스템

Step1) 최초 작성

접근1 ) 벌금과 회원을 1:1 관계로 생성

접근2) 벌금을 회원 테이블의 한 컬럼으로 사용

Step2) 피드백

Step3) 더미 데이터 추가

Step4) SQL 문제

STEP5) 정리

'문제풀이 > 데이터베이스 설계' 카테고리의 다른 글

티스토리툴바

ABOUT ME

테이블 설계 요구사항: 도서관 관리 시스템

Step1) 최초 작성

접근1 ) 벌금과 회원을 1:1 관계로 생성

접근2) 벌금을 회원 테이블의 한 컬럼으로 사용

Step2) 피드백

Step3) 더미 데이터 추가

Step4) SQL 문제

STEP5) 정리

'문제풀이 > 데이터베이스 설계' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바