학습지 회사의 데이터베이스
목적 : 학습지회사의 회원및 출고교재관리 DB
요구조건 분석
교사정보, 과목정보, 회원정보, 과목등록정보 등이 있다.
1. 교사정보
: 교사코드(id), 교사명, 활동지역 등 (교사등록일,연락처,성별,학력등 생략)2. 과목정보
: 과목코드(id), 과목명, 금액 등 (과목단계,학년 등 생략)3. 회원정보
: 교사코드(id), 학생명, 학생코드(id), 학년, 학교, 부모명, 부모코드(id), 결제수단, 결제정보, 주소 등
- 최초 학습지 가입 시 등록되는 정보로 신청한학습지 과목과 별도 관리4. 8월 과목등록정보
: 학생명, 학생코드(id), 과목코드(id), 최초과목등록일, 회비납부 등
- 매월 교사가 관리 학생들의 과목정보를 올린다.
과목등록은 다음 월의 정보를 미리등록한다. 예) 8월에 들을 과목을 7월에 신청 받아서 서버에 등록 (교재를 신청받은 수량만큼만 미리 인쇄해야하기 때문이다.)
7월 초에 과목 등록을 하면 7월 중순에 결제후 결제여부정보가 컬럽에 업데이트된다.ERD
데이터테이블 생성
교사테이블 생성
DROP TABLE IF EXISTS teacher ;
CREATE TABLE teacher (
교사id INTEGER NOT NULL PRIMARY KEY,
교사명 VARCHAR NOT NULL,
활동지역 VARCHAR
);
INSERT INTO teacher VALUES
( 11, '오선생', '상상1동' ),
( 22, '김선생', '상상2동' ),
( 33, '양선생', '상상3동' ),
( 44, '우선생', '상상4동' );과목 테이블 생성
DROP TABLE IF EXISTS subject ;
CREATE TABLE subject (
과목코드 INTEGER NOT NULL PRIMARY KEY,
과목명 VARCHAR NOT NULL,
금액 INTEGER NOT NULL
);
INSERT INTO subject VALUES
(1, '국어', 30000),
(2, '독해', 15000),
(3, '수학', 35000),
(4, '연산', 15000),
(5, '사회과학', 35000);
회원 테이블 생성
회원이면 표이름이 member 여야하는데 학생회원이라 그냥 student로 함
그리고 부모회원과 자녀회원을 통합함
DROP TABLE IF EXISTS student ;
CREATE TABLE student (
교사코드 INTEGER NOT NULL REFERENCES teacher,
학생명 VARCHAR NOT NULL,
학생코드 INTEGER NOT NULL PRIMARY KEY,
학년 INTEGER NOT NULL,
부모명 VARCHAR NOT NULL,
부모코드 INTEGER NOT NULL,
결제수단 VARCHAR NOT NULL,
결제정보 INTEGER NOT NULL,
주소지 VARCHAR NOT NULL
);
INSERT INTO student VALUES
( 11 , ' 현지예 ', 101 , 4 , '현부모' , 10145 , '신용카드' , 12345678 , ' 서울시 상상구 상상1동 아차아파트 '),
( 11 , ' 현지윤 ', 102 , 4 , '현부모' , 10145 , '신용카드' , 12345679 , ' 서울시 상상구 상상1동 아차아파트 '),
( 11 , ' 최재성 ', 103 , 1 , '최부모' , 10345 , '신용카드' , 12345680 , ' 서울시 상상구 상상1동 아차아파트 '),
( 22 , ' 김장현 ', 104 , 6 , '김부모' , 10445 , '계좌이체' , 12345681 , ' 서울시 상상구 상상2동 하하아파트 '),
( 22 , ' 김정하 ', 105 , 3 , '김부모' , 10445 , '계좌이체' , 12345682 , ' 서울시 상상구 상상2동 하하아파트 '),
( 33 , ' 장재웅 ', 106 , 5 , '장부모' , 10645 , '신용카드' , 12345683 , ' 서울시 상상구 상상3동 코야아파트 '),
( 33 , ' 장재강 ', 107 , 5 , '장부모' , 10645 , '신용카드' , 12345684 , ' 서울시 상상구 상상3동 코야아파트 '),
( 33 , ' 장재서 ', 108 , 5 , '장부모' , 10645 , '신용카드' , 12345685 , ' 서울시 상상구 상상3동 코야아파트 '),
( 33 , ' 이하늬 ', 109 , 5 , '이부모' , 10945 , '신용카드' , 12345686 , ' 서울시 상상구 상상3동 호이아파트 '),
( 33 , ' 이해나 ', 110 , 1 , '이부모' , 10945 , '신용카드' , 12345687 , ' 서울시 상상구 상상3동 호이아파트 '),
( 33 , ' 전준현 ', 111 , 6 , '전부모' , 11145 , '신용카드' , 12345688 , ' 서울시 상상구 상상3동 호이아파트 '),
( 33 , ' 전준성 ', 112 , 3 , '전부모' , 11145 , '신용카드' , 12345689 , ' 서울시 상상구 상상3동 호이아파트 '),
( 44 , ' 조혜윤 ', 113 , 2 , '조부모' , 11345 , '계좌이체' , 12345690 , ' 서울시 상상구 상상4동 라라아파트 '),
( 44 , ' 지민서 ', 114 , 2 , '지부모' , 11445 , '신용카드' , 12345691 , ' 서울시 상상구 상상4동 라라아파트 '),
( 44 , ' 변우리 ', 115 , 3 , '변부모' , 11545 , '계좌이체' , 12345692 , ' 서울시 상상구 상상4동 라라아파트 '),
( 44 , ' 고태규 ', 116 , 4 , '고부모' , 11645 , '신용카드' , 12345693 , ' 서울시 상상구 상상4동 랄라아파트 '),
( 44 , ' 김민준 ', 117 , 4 , '김부모' , 11745 , '신용카드' , 12345694 , ' 서울시 상상구 상상4동 랄라아파트 '),
( 44 , ' 김민채 ', 118 , 6 , '김부모' , 11745 , '신용카드' , 12345695 , ' 서울시 상상구 상상4동 랄라아파트 '),
( 44 , ' 박소현 ', 119 , 3 , '박부모' , 11945 , '신용카드' , 12345696 , ' 서울시 상상구 상상4동 라라아파트 '),
( 44 , ' 박태성 ', 120 , 6 , '박부모' , 11945 , '신용카드' , 12345697 , ' 서울시 상상구 상상4동 라라아파트 ');
7월 등록과목테이블 생성
학습지 회사는 다음달 교재를 미리 제작하는 특성상 그 달의 입회나 휴회등
과목 변동을 반영해서 매월 과목테이블이 업데이트 된다.
그렇게 때문에 프라이머리 키없이 왜래키만 부여하였다.
DROP TABLE IF EXISTS book_list ;
CREATE TABLE book_list (
학생명 VARCHAR NOT NULL ,
학생코드 INTEGER NOT NULL REFERENCES student,
과목코드 INTEGER NOT NULL REFERENCES subject,
교사코드 INTEGER NOT NULL REFERENCES teacher,
최초교재등록일 DATE NOT NULL,
회비납부 BOOLEAN
);
INSERT INTO book_list VALUES
( '현지윤' , 101 , 3 , 11 , '2022-03-01' , 'TRUE' ) ,
( '현지윤' , 101 , 4 , 11 , '2022-07-01' , 'TRUE' ) ,
( '현지윤' , 102 , 3 , 11 , '2022-03-01' , 'TRUE' ) ,
( '현지윤' , 102 , 4 , 11 , '2022-07-01' , 'TRUE' ) ,
( '최재성' , 103 , 1 , 11 , '2021-12-01' , 'TRUE' ) ,
( '최재성' , 103 , 2 , 11 , '2021-12-01' , 'TRUE' ) ,
( '최재성' , 103 , 3 , 11 , '2021-12-01' , 'TRUE' ) ,
( '최재성' , 103 , 4 , 11 , '2021-12-01' , 'TRUE' ) ,
( '김장현' , 104 , 1 , 22 , '2020-05-01' , 'TRUE' ) ,
( '김장현' , 104 , 2 , 22 , '2020-05-01' , 'TRUE' ) ,
( '김장현' , 104 , 3 , 22 , '2020-05-01' , 'TRUE' ) ,
( '김장현' , 104 , 4 , 22 , '2020-05-01' , 'TRUE' ) ,
( '김장현' , 104 , 5 , 22 , '2020-05-01' , 'TRUE' ) ,
( '김정하' , 105 , 1 , 22 , '2021-04-01' , 'TRUE' ) ,
( '김정하' , 105 , 2 , 22 , '2021-04-01' , 'TRUE' ) ,
( '김정하' , 105 , 3 , 22 , '2021-04-01' , 'TRUE' ) ,
( '김정하' , 105 , 4 , 22 , '2021-04-01' , 'TRUE' ) ,
( '장재웅' , 106 , 1 , 33 , '2021-09-01' , 'TRUE' ) ,
( '장재웅' , 106 , 3 , 33 , '2021-09-01' , 'TRUE' ) ,
( '장재강' , 107 , 1 , 33 , '2021-09-01' , 'TRUE' ) ,
( '장재강' , 107 , 3 , 33 , '2021-09-01' , 'TRUE' ) ,
( '장재서' , 108 , 1 , 33 , '2021-09-01' , 'TRUE' ) ,
( '장재서' , 108 , 3 , 33 , '2021-09-01' , 'TRUE' ) ,
( '이하늬' , 109 , 1 , 33 , '2019-10-01' , 'TRUE' ) ,
( '이하늬' , 109 , 3 , 33 , '2019-10-01' , 'TRUE' ) ,
( '이하늬' , 109 , 5 , 33 , '2019-10-01' , 'TRUE' ) ,
( '이해나' , 110 , 1 , 33 , '2022-07-01' , 'TRUE' ) ,
( '이해나' , 110 , 2 , 33 , '2022-07-01' , 'TRUE' ) ,
( '이해나' , 110 , 3 , 33 , '2022-07-01' , 'TRUE' ) ,
( '이해나' , 110 , 4 , 33 , '2022-07-01' , 'TRUE' ) ,
( '이해나' , 110 , 5 , 33 , '2022-07-01' , 'TRUE' ) ,
( '전준현' , 111 , 2 , 33 , '2021-11-01' , 'TRUE' ) ,
( '전준현' , 111 , 3 , 33 , '2021-11-01' , 'TRUE' ) ,
( '전준현' , 111 , 4 , 33 , '2021-11-01' , 'TRUE' ) ,
( '전준현' , 111 , 5 , 33 , '2021-11-01' , 'TRUE' ) ,
( '전준성' , 112 , 2 , 33 , '2022-06-01' , 'TRUE' ) ,
( '전준성' , 112 , 3 , 33 , '2022-06-01' , 'TRUE' ) ,
( '전준성' , 112 , 4 , 33 , '2022-06-01' , 'TRUE' ) ,
( '조혜윤' , 113 , 2 , 44 , '2021-12-01' , 'TRUE' ) ,
( '조혜윤' , 113 , 3 , 44 , '2021-12-01' , 'TRUE' ) ,
( '조혜윤' , 113 , 4 , 44 , '2021-12-01' , 'TRUE' ) ,
( '지민서' , 114 , 1 , 44 , '2022-07-01' , 'TRUE' ) ,
( '지민서' , 114 , 3 , 44 , '2021-05-01' , 'TRUE' ) ,
( '지민서' , 114 , 4 , 44 , '2021-05-01' , 'TRUE' ) ,
( '지민서' , 114 , 5 , 44 , '2021-05-01' , 'TRUE' ) ,
( '변우리' , 115 , 1 , 44 , '2022-05-01' , 'FALSE' ) ,
( '변우리' , 115 , 2 , 44 , '2022-05-01' , 'FALSE' ) ,
( '고태규' , 116 , 1 , 44 , '2021-10-01' , 'TRUE' ) ,
( '고태규' , 116 , 2 , 44 , '2021-10-01' , 'TRUE' ) ,
( '고태규' , 116 , 3 , 44 , '2021-10-01' , 'TRUE' ) ,
( '고태규' , 116 , 4 , 44 , '2021-10-01' , 'TRUE' ) ,
( '고태규' , 116 , 5 , 44 , '2022-07-01' , 'TRUE' ) ,
( '김민준' , 117 , 2 , 44 , '2022-07-01' , 'TRUE' ) ,
( '김민준' , 117 , 4 , 44 , '2022-07-01' , 'TRUE' ) ,
( '김민준' , 117 , 5 , 44 , '2022-07-01' , 'TRUE' ) ,
( '김민채' , 118 , 1 , 44 , '2019-07-01' , 'TRUE' ) ,
( '김민채' , 118 , 2 , 44 , '2019-07-01' , 'TRUE' ) ,
( '김민채' , 118 , 3 , 44 , '2019-07-01' , 'TRUE' ) ,
( '김민채' , 118 , 4 , 44 , '2019-07-01' , 'TRUE' ) ,
( '박소현' , 119 , 1 , 44 , '2022-07-01' , 'FALSE' ) ,
( '박소현' , 119 , 3 , 44 , '2021-07-01' , 'FALSE' ) ,
( '박소현' , 119 , 5 , 44 , '2022-07-01' , 'FALSE' ) ,
( '박태성' , 120 , 3 , 44 , '2021-07-01' , 'TRUE' ) ,
( '박태성' , 120 , 5 , 44 , '2022-07-01' , 'TRUE' ) ;정규화 비정규화
회원을 입회하면
학생회원 정보-부모회원정보-등록과목1
학생회원 정보-부모회원정보-등록과목2
.
.
.
이런식으로 등록이 될텐데
과목 정보는 매월 업데이트 되기 때문에
회원정보를 간략히 보관도 할 겸
회원정보와 등록과목 정보 테이블을 분리하였다.
둘 이상의 자녀가 회원인 부모회원이 있을 수 있어
학생회원과 부모회원을 분리할까 했지만
처음 회원 가입 시 부모회원코드만 주의하면
그뒤로는 참조되는 일이 적기 때문에 분리하지 않았다.
SQL
1. 부모회원 테이블 생성
학습지회사에서 나온 홍보물이나 단체 문자 등을 발송할때
부모정보만 분리한 테이블이 필요하다.
DISTINCT를 사용하여 회원정보에서 부모회원을 분리하자.
-- CREATE TABLE 부모테이블 AS (
-- SELECT
-- DISTINCT 부모명, 부모코드, 결제수단, 결제정보
-- FROM (SELECT 부모명, 부모코드, 결제수단, 결제정보
-- FROM student) 부모정보추출);
2. 7월 총매출 구하기
DROP TABLE IF EXISTS 과목별매출표7월;
CREATE TABLE 과목별매출표7월 AS (
--4_그리고 그걸 다시 표로 만듦
SELECT subject.과목명, subject.금액, 과목별교재수.총교재수, ( subject.금액 * 과목별교재수.총교재수 ) AS 과목별매출
--3_합친 결과에서 과목명/금액/총교재수/과목별매출 을 뽑음
FROM (
(SELECT 과목코드, count(*) AS 총교재수
FROM book_list
GROUP BY 과목코드
ORDER BY 과목코드) 과목별교재수
--1_ book_list에서 과목별교재수 추출함
JOIN subject
ON subject.과목코드 = 과목별교재수.과목코드
--2_ 추출한 [과목별교재수]를 과목코드가 동일한 subject와 합침
));
SELECT sum(과목별매출) AS 총매출 FROM 과목별매출표7월;
7월 총매출 169만5000원
3. 7월의 교사왕 뽑기
7월 교제신청목록인 book_list에서 교사별로 매출을 계산하여 내림차순으로 출력한다.
- LEFT JOIN을 사용하여 컬럼수과 로우수가 각각 다른 테이블을 연결한다.
- book_list와 과목코드로 연결하여 subject에서 금액을 가져오고
- book_list와 교사코드로 연결하여 teacher에서 교사명을 가져왔다.
-교사별 금액 총합 구해서 출력하기
SELECT 교사코드, 교사명, sum(금액) AS 교사별매출
FROM (
SELECT book_list.교사코드, teacher.교사명, subject.금액
FROM (book_list --row가 가장 많아 주가 되는 테이블
LEFT JOIN subject ON book_list.과목코드 = subject.과목코드
LEFT JOIN teacher ON book_list.교사코드 = teacher.교사id )
)교사별매출표
GROUP BY 교사코드, 교사명
ORDER BY 교사별매출 DESC ;
7월의 교사왕은 68만5000원의 우선생님!
4. 오선생님의 퇴사
: 오선생님이 개발자로 전향하기위해 교사를 그만두면서 오선생님이 관리하던 학생을 8월부터 김선생님이 맡기로 했다.
-회원정보와 교재신청 테이블에서 교사코드수정하고 teacher테이블에서 오선생님 정보를 삭제한다.
UPDATE student
SET 교사코드 = 22
WHERE 교사코드 =11
RETURNING *;
UPDATE book_list
SET 교사코드 = 22
WHERE 교사코드 =11
RETURNING *;
DELETE FROM teacher WHERE 교사id = 11;
SELECT*FROM teacher;
5. 교사가 가져갈 교재리스트
주문한 8월교재가 회사에 도착했다.
교사별로 가져갈 교재가 과목, 학년별로 몇권씩인지 교재 리스트를 만들자.
SELECT 교사코드, 과목명, 학년, count(학년) AS 권수
FROM (
SELECT subject.과목명, student.학년, book_list.교사코드
FROM (book_list
LEFT JOIN subject ON book_list.과목코드 = subject.과목코드
LEFT JOIN student ON book_list.학생코드 = student.학생코드 )
ORDER BY 교사코드, 과목명, 학년
)과목별학년별교재수
GROUP BY 과목명, 학년, 교사코드
ORDER BY 교사코드 ;
6. 9월 회비 결재완료자 중에서 10만원이상 결재시 학부모 사은품으로 락앤락세트가 나간다.
사은품을 받을 학부모명단을 결재금액, 교사명을 표시해서 뽑아보자
---- 너무 복잡해서 분리
(1) book_list와 student를 학생코드로 연결하고 부모명을 뽑는다
(2) book_list와 subject를 과목코드로 연결하고 금액을 뽑는다
(3) (1),(2)를 통해 부모명, 금액, 회비납부 표시, 교사코드, 회비납부가 true인 항목만 표시
SELECT student.부모명, subject.금액, book_list.회비납부, book_list.교사코드
FROM (book_list
LEFT JOIN subject ON book_list.과목코드 = subject.과목코드
LEFT JOIN student ON book_list.학생코드 = student.학생코드 )
WHERE book_list.회비납부 = TRUE;(4)금액이랑 회비 곱한값을 sum한 컬럼을 결제금액으로 해서 부모명으로 그룹바이하고 교사코드도 표시.
(5)이중에서 결제금액이 10만원 이상인 사람을 출력
SELECT student.부모명, sum(subject.금액) AS 결제금액, book_list.회비납부, book_list.교사코드
FROM (book_list
LEFT JOIN subject ON book_list.과목코드 = subject.과목코드
LEFT JOIN student ON book_list.학생코드 = student.학생코드 )
WHERE book_list.회비납부 = TRUE
GROUP BY student.부모명, book_list.회비납부, book_list.교사코드;
이렇게 먼저 뽑고
10만원 이상 결제자 조건 씌움
완성쿼리
SELECT 부모명, 결제금액, 교사코드
FROM (
SELECT student.부모명, sum(subject.금액) AS 결제금액, book_list.회비납부, book_list.교사코드
FROM (book_list
LEFT JOIN subject ON book_list.과목코드 = subject.과목코드
LEFT JOIN student ON book_list.학생코드 = student.학생코드 )
WHERE book_list.회비납부 = TRUE
GROUP BY student.부모명, book_list.회비납부, book_list.교사코드
)회비납부리스트
WHERE 결제금액 >= 100000
ORDER BY 교사코드;
축하드립니다! 락앤락 세트 받아가세요~!
ERD
마무리하며...
초기 데이터 모델링의 중요성을 깨달았다.
그리고 지나친 정규화는 오히려 지나친 JOIN을 쓰게만든다는 것을 알았다.
그래도 미니프로젝트를 통해 SQL을 이전보다는 조금 더 이해하게 되었다.
p.s.
1. 교재테이블 생성
과목 테이블에는 과목별 가격만 있어서 학년별 교재코드 반영이 안되었다.
만약 다시 한다면 교재테이블을 추가로 만들어서 과목-학년별-교재코드의 칼럼으로 만들것이다. 그렇게 되면 월별 교재테이블 에 과목코드 뿐만아니라 교재코드도 추가하고 교재코드로 인쇄주문도 넣을 수 있게 된다.
2. 월별 교재테이블 생성 자동화
현업에서는 매월 초 교사들이 각자 데리고 있는 학생들의 과목을 일일이 올린다.
만약 자동화를 한다면 과목변동여부 테이블을 추가해서 과목변동이 생긴 학생들을 날짜와 변동 내용(어떤 과목 추가 교재코드 추가, 어떤 과목 휴회 교재코드 삭제 등)을 입력해둔다.
그리고 익월 교재신청 테이블을 생성할 때 변동없는 건 그대로 받아오고 변동사항은 변경되어서 입력되도록 한다.
는 머릿속 상상일 뿐 실제로 만들다보면 또 모르지.. 이전 방법이 더 편했을 수도
보초딩코라 틀린 내용 있을 수도 있습니다. 댓글 지적 환영
내용이 풍성하네요.