[SQL 프로젝트] 가상의 도서관 데이터베이스 구축 및 분석

[프로젝트] 

가상의 도서관 데이터베이스 구축 및 분석

 

[기본 지식]

Primary Key	테이블에서 각 행을 고유하게 식별하는 열, NULL 불가
Auto Increment	새로운 행이 추가될 때 자동으로 값이 증가함, 주로 기본 키에 사용.
VARCHAR(100)	문자열을 최대 100자까지 저장할 수 있음.
NOT NULL	해당 열에 NULL 값을 저장할 수 없음, 반드시 값을 입력해야 함!
INT	정수를 저장하는 데이터 타입

 

 

1. 데이터베이스 및 테이블 생성

-- 도서 정보 테이블 생성
CREATE TABLE Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    genre VARCHAR(50),
    publication_year INT,
    copies_available INT
);

-- 도서관 회원 정보 테이블 생성
CREATE TABLE Members (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    membership_date DATE,
    email VARCHAR(100)
);

-- 대출 기록 테이블 생성
CREATE TABLE Loans (
    loan_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    member_id INT,
    loan_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (member_id) REFERENCES Members(member_id)
);

 

Books 테이블을 생성

테이블 안에 book_id, title, author, genre, publication_year, copies_available 각 열을 생성

 

Members 테이블을 생성

테이블 안에 member_id, name, membership_date, email 각 열을 생성

 

Loans 테이블을 생성

테이블 안에 loan_id, loan_date, rutuen_date, book_id, member_id 각 열을 생성

book_id, member_id는 Books, Members 테이블에서 참조해야 하므로 외래키 사용

 

---

2. 가상 데이터 삽입

-- 도서 정보 삽입
INSERT INTO Books (title, author, genre, publication_year, copies_available) VALUES
('To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960, 4),
('1984', 'George Orwell', 'Dystopian', 1949, 6),
('Moby Dick', 'Herman Melville', 'Adventure', 1851, 2),
('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 1925, 5);

-- 회원 정보 삽입
INSERT INTO Members (name, membership_date, email) VALUES
('Alice Johnson', '2023-01-15', 'alice.johnson@example.com'),
('Bob Smith', '2023-02-20', 'bob.smith@example.com'),
('Charlie Brown', '2023-03-05', 'charlie.brown@example.com'),
('Diana Prince', '2023-04-12', 'diana.prince@example.com');

-- 대출 기록 삽입
INSERT INTO Loans (book_id, member_id, loan_date, return_date) VALUES
(1, 1, '2023-07-01', '2023-07-15'),
(2, 2, '2023-07-05', '2023-07-20'),
(3, 3, '2023-07-10', NULL),
(4, 4, '2023-07-12', '2023-07-26');

대출 기록 삽입에서 NULL 값은 반납하지 않음을 알려줌.

---

3. 통계

 

- 도서 정보 조회

SELECT * FROM Books;

Books 테이블 모든 정보 조회

 

 

- 대출 중인 도서 목록

SELECT b.title, b.author, m.name AS member_name, l.loan_date
FROM Loans l
JOIN Books b ON l.book_id = b.book_id
JOIN Members m ON l.member_id = m.member_id
WHERE l.return_date IS NULL;

정보선택: 도서 테이블에서 도서 제목 + 저자, 회원 테이블에서 회원 이름, 대출 테이블에서 대출일 선택

 

테이블 연결: 대출 테이블 참조하고

대출 테이블의 book_id와 도서 테이블의 Book_id를 Books 테이블에 합침 -> 대출 기록에 대한 도서의 제목과 저자를 찾을 수 있음

대출 테이블의 member_id와 회원 테이블의 member_id를 Members 테이블에 합침 -> 대출한 사람의 이름을 찾을 수 있음

 

필터링: 대출 테이블의 반환일이 NULL인 경우(반환이 되지 않음) -> 도서가 반환되지 않은 대출 기록만 선택

 

JOIN의 목적: 각 테이블에만 존재하는 정보를 통합하기 위함.

 

 

 

- 장르별 도서 수

SELECT genre, COUNT(*) AS num_books
FROM Books
GROUP BY genre;

도서 테이블에서

장르와 각 장르에 속하는 도서의 총 권수를 계산하고, 이를 num_books라는 별칭으로 표시

COUNT(*)는 각 장르에 대해 책의 수를 세는 함수

장르별로 그룹화

 

 

 

- 회원 대출 기록

SELECT m.name, b.title, l.loan_date, l.return_date
FROM Loans l
JOIN Members m ON l.member_id = m.member_id
JOIN Books b ON l.book_id = b.book_id
ORDER BY m.name;

대출 테이블에서

회원 이름, 도서명, 대출일, 대출 반환일을 선택하고

대출 테이블의 회원 아이디와 회원 테이블의 회원 아이디가 일치하면 회원 테이블에 조인

대출 테이블의 도서 아이디와 도서 테이블의 도서 아이디가 일치하면 도서 테이블에 조인

회원명으로 오름차순 정렬