[문과 코린이의 IT 기록장] RDBMS Modeling 기초 - 연습문제 1 (도서관의 도서 대출 관리, 중학교 학생 성적관리 프로젝트)
[문과 코린이의 IT 기록장] RDBMS Modeling 기초 - 연습문제 1 (도서관의 도서 대출 관리, 중학교 학생 성적관리 프로젝트)
RDBMS Modeling 기초 - 인프런 | 강의
본 강좌는 데이터베이스 설계 이론을 실습 위주로 쉽게 풀어냈습니다. 책 등을 통해서 경험하신 분들은 대부분 데이터베이스 이론이 어렵다고 느끼고 포기하신 경험들이 있을 겁니다. 저도 그
www.inflearn.com
1. 도서관의 도서 대출 관리
| - 도서관에는 각 서고/서가에 많은 책들이 있다. - 고객들은 인터넷을 통해서 로그인한 후, 도서 목록을 조회할 수 있다. - 고객들은 원하는 책을 대출받을 수 있다. - 고객은 책이 있을 경우, 대출 예약을 할 수 있으며, 대출을 위해서는 직접 방문해 책을 찾아 대출을 해야한다. |
1) 마스터, 관계 테이블 찾아내기
- 마스터 테이블
: 도서관, 서고, 서가, 책, 고객, 도서 목록
* 도서관 : 현재 우리 도서관꺼를 만들기 때문에, 도서관 정보는 따로 필요 없으므로, 제외
a. 서고 : 방 (여러 서고를 가짐) /서가 : 서고 내 책꽂이들 (층, 열을 가짐) => 1:M의 관계를 가짐
b. 책 (목차, 페이지, ...) => 1:M 관계를 가짐
* 1:1 관계를 설명할 때 도서 목록 확장 예정
* 도서목록 : 도서분류코드
- 관계 테이블
: 로그인, 조회, 대출, 대출예약
2) 마스터 테이블 - DataBase Diagram
[ 상속형 ]
- 서고 - 서가 - 책 - 목차 : 상속형으로 이루어짐
ex. 서가는 서고의 서고 id를 참조하고 있으면서, 동시에 서가id + 서고id를 Primary Key로 설정한다.
- 상속형은 pk로 설정한 레코드들이 Unique해야 한다.
- 즉 엄격하게 모든 레코드에 대한 입력을 유도하며, 부모 요소에 대해 파악할 수 있도록 지정해준다.
- 상속형의 단점은, 자식 테이블이 늘어날 때마다 PK가 하나씩 증가한다는 것이다.
- 이와같은 방식으로 설계했기 때문에 [층열] 테이블에서는, 부모 요소들에 대한 정보를 찾아가 파악 가능하다.
[ Unique Key ]
- [책] 테이블은 같은 책이 여러 권 존재할 수 도 있다. 따라서 책 id와 책 seq는 별도로 두며, 책 id는 상속받은 서고id, 서가id와 함께 Primary Key로, 책 seq는 Unqiue Key로 설정한다.
- [책] 테이블과 [대출] 테이블 간 관계를 설정할 때, 해당 책 seq를 통해 관계를 설정한다.
* 만약 책 id로 관계를 설정할 경우, 대출 테이블은 나머지 PK요소인 서고id, 서가id도 상속받아야 한다.
[ 1:1 관계 ]
- [기본목록]테이블의 목록 id는, [논문정보] 세부정보id, [잡지정보] 세부정보id, [일반책자정보] 세부정보id와 1:1관계를 가진다.
[ 기준 테이블 ]
- [책구분] 테이블은 기준 테이블로, [기본목록] 테이블의 '구분' 컬럼에 대한 도메인을 제한하기 위한 테이블이다.
* 기준 테이블은, 도메인을 제한하는 역할을 한다.
- [성별] 테이블은 기준 테이블로, [고객] 테이블의 '성별' 컬럼에 대한 도메인을 제한하기 위한 테이블이다.
// cf ) [고객]테이블의 '성별' 컬럼은 null을 포함하도록 한다. 만약 어떤 사람이 고객정보를 대신 넣는 상태인데, 성별을 파악하지 못하는 상태에서 성별을 구분하기 어렵다면 입력에 문제가 발생할 수 있음. 따라서, null을 사용
* null은 데이터 수집이 되어 있지 않다는 의미 또한 지님.
2. 중학교 학생 성적관리 프로젝트
| - 과목은 학년별로 담당 선생님이 따로 있다. - 시험은 중간고사, 기말고사 두 가지가 있다. - 학생들은 학년, 반에 배정되며, 반별로 학생들에게 고유 번호를 부여하고 있다. -각 반에는 담임 선생님이 배정되어 있다. 선생님 중에는 담임을 맡지 않는 선생님도 있다. - 한 반의 학생은 대략 40명 정도이고, 남녀공학이다. - 석차는 남녀 공통 1등부터 순서대로 정한다. |
* DB의 테이블명과 컬럼명은, C# 프로그램의 클래스명, 프로퍼티명, 데이터 테이블명, 데이터 set, 데이터 row, 일반 변수 등의, 모든 명칭을 결정하는데 영향을 준다. 이 둘을 통일함으로써, 프로그램에 혼돈을 방지한다.
* MVC 모델과, ORM으로 테이블과 클래스를 mapping하는 작업을 하는데, 이 명칭 일치 중요도가 높아진다.
- DB 설계의 시작은 업무의 분석이다. // 업무 분석 : 업무 요구서를 받거나, 분석을 위한 인터뷰 진행
- 위와 같은 사례는, 우리가 학창시절을 거친 경험이 있기 때문에 이해하기가 쉽다.
- 그러나 현장에서, 즉 실제 생산라인 및 특수한 분야의 DB 구축을 진행하게 되면, 고객들이 사용하는 단어조차 이해하기 어려운 경우가 태반이다.
- 따라서, 업무 분석은 굉장히 세부적이고 체계적으로 진행되어야 하며, 용어 및 관계의 이해를 위한 커멘드를 굉장히 많이 달아놓아야 한다.
- 이 커멘드는 결국 비즈니스 로직(관계 테이블) 및 마스터 테이블을 정확하게 구현하도록 돕니다.
1) 마스터 테이블, 관계 테이블 찾아내기
- 마스터 테이블
: 학생 / 학년 / 반(40명정도/반) / 고유번호(반별)
: 선생님 (담임/비담임)
: 과목 / 시험(중간고사, 기말고사) / 성적 / 석차 (반별석차 / 학년별석차) * 어떤 석차인지 모름. 이를 고려해줘야함.
// 남녀공학
- 관계 테이블
: 배정하다
// 학생은 학년-반에 배정된다.
: 시험(보다) -> 결과 : 석차(남녀 공통 1등부터 성적 순서대로) * 이러한 추론을 통해, 마스터 테이블에 성적도 추가
// 학생은 과목에 대해서 시험을 보다
2) 1:M 관계 찾기 및 재정리
- 마스터 테이블
: 학생 (반별 고유번호 존재) => 가장 중요한 마스터 테이블
// 학생 테이블 내에, 컬럼으로서 고유번호 포함
: 학년 / 반 (40명정도/반) * 1:M관계 : 학년 - 반 , 반 - 고유번호
: 과목 (학년별 담당선생님 별도로 존재) / 선생님 (담임, 비담임)
: 성적 (반, 학년별 석차)
// 성적은 석차를 Sort한 것. 즉, 성적의 속성으로서 석차는 포함될 수 있음. (만약 이후에, 성적의 로직이 복잡하다고 판단된다면, 석차를 다른 마스터 테이블로 분리 가능)
: 시험 (중간고사, 기말고사)
- 관계 테이블
: 배정하다 / 시험(보다) / 개강하다 / 폐강하다
// 과목에 대해 개강, 폐강도 고려해야 할 여부
3) Data Diagram
[ TB_schGrade ] // 절대마스터 테이블
| 컬럼명 | 데이터타입 | Null | 비고 |
| schGradeId | tinyint | X | 중학교의 학년은 3으로 고정되어 있음. (절대 변하지 않음) // tinyint : 255자까지 표현 가능. 이 DB에서는 특정 중학교의 학년을 표현하기 때문에, 학년은 부모 테이블을 전혀 가지지 않음. 따라서, 이 테이블은 절대 마스터 테이블(= 기준테이블)이 됨. // 기준 테이블 : PK역할은 하고, FK 역할은 하지 않는 테이블 - 절대 마스터 테이블은 PK역할을 충실히 해야 하며, 마스터 테이블에서는 절대 오류가 나지 않도록 구성하며, 철저하게 오류 검증 코드를 지정 해야 한다. ex ) textbox로 input되어 값이 들어올 때, 마스터 테이블에서 입력값에 대해 오류가 발생하면 그 이하의 테이블들이 모두 문제가 발생할 수 있다. - 따라서 애초에 DB를 설계할 때, 값이 될 수 있는 것들을 넣어서, 스토어드 프로시저를 통해, 사용자들은 select만 가능하도록(insert / update / delete 불가능) 테이블을 막아버리는 것 또한 효율적이다. cf ) 프로그래머들이 ORM으로 제공되는 클래스만 가져다 사용할 수 있도록 도움. |
| schGradeName | varchar(50) | X |
[ TB_schClass ]
| 컬럼명 | 데이터타입 | Null | 비고 |
| schGradeId | tinyint | X | - TB_schGrade를 참조하는 FK - 반 id와 함께 PK의 역할을 함 |
| schClassId | tinyint | X | - 학년 id와 함께 PK의 역할을 함 - TB_schClass 테이블에서 반Id(schClassId)는 한번 입력되면, 절대 삭제되면 안된다. 만약 한번 입력되면, 이후에 자식(TB_schStudent)들이 입력될텐데, 이 경우 반을 삭제하게 되면, 부모 요소가 사라진 자식 요소가 되기 때문이다. // 반 id에는 id 사양을 줄 수 없다. 만약 이를 적용하면, 1학년 1반을 insert했을 경우, 이후 2학년 1반은 사용될 수 없기 때문이다. |
| schClassName | varchar(50) | X | 반id는 1, 2, 3, ... 이런 형식으로 지정되더라도, 실제 현실에서 반 이름은 개나리반, 무궁화반, 진달래반, ... 등으로 사용될 수도 있다. 이러한 경우를 대비해 추가된 컬럼이다. // 일반적으로 PK는 의미를 가지지 않는 것이 중요하다. 이 컬럼과 같이 의미를 가지는 경우, PK에 적절하지 않다. PK는 단순히 Unique하고 Not Null의 조건만 만족하면 족하다. |
| UseFlag | bit | X | - 테이블을 구성할 때는, CRUD를 굉장히 신중하게 고려해야 한다. - 이와 테이블과 같은 경우에는, Insert / Select는 많이 사용되어도 되지만, Delete는 절대 발생하면 안되며, Update의 경우는 필요한 상황을 고려하여 제한적으로 사용되어야 한다. - Update의 예시로는 UseFlag를 들 수 있다. * 기본값 : 1 (올해년도에 해당 반을 사용하지 않을 경우, 0으로 지정하면, 작년도 학생들까지의 이 반의 정보는 살아있지만, 올해는 사용하지 않도록 할 수 있다.) |
| TeacherId | int | X | - 한 반에는 담임선생님이 존재한다. 따라서, 반을 담당하는 담임 선생님을 구분하기 위한 TeacherId 컬럼을 추가한다. - 이는 TB_schTeacher 테이블의 TeacherId와 1:1 관계를 가진다. 그러나, 1:M 관계를 가지는 것은 아니지만 TB_schTeacher테이블을 기분 테이블의 역할을 하도록 해, 해당 TB_schClass에서 TeacherId가 해당 도메인들을 적절하게 참조할 수 있도록 PK-FK 관계를 형성한다. |
| AssignedDate | date | X | - 몇년도에 담임선생님을 하셨는지 정보를 추가하기 위해, AssignedDate 컬럼을 추가한다. |
[ TB_schStudent ]
| 컬럼명 | 데이터타입 | Null | 비고 |
| StudentId | bigtint | X | 방법 1 ) 선택 - 학교의 전체 학생 수는, 하나의 연도에 1200명... 3000명 아니면 그 이상이 될수도 있다. 이것이 몇십년 몇백년 쌓이게 되면, 굉장히 큰 학생들의 데이터가 저장되게 될 것이다. 따라서 이 경우에는 bigint를 사용한다. // bigint : 약 920경 방법 2) - 학교의 전체 학생 수는, 하나의 연도에 1200명... 3000명 아니면 그 이상이 될수도 있다. 때문에 tinyint는 적절하지 않으며, smallint를 사용한다. - EntranceDate가 있기 때문에, smallint로도 충분하다.이 경우는 EntranceDate와 함께 PK가 되어야 한다. // smallint : -32768 - 32767 |
| StudentName | varchar(50) | X | |
| schGradeId | tinyint | X | |
| schClassId | tinyint | O | - 재학생은 학년, 반이 항상 존재하지만, 신입생(1학년)의 경우에는, 반배정 직전 학년만 존재하는 상황이 있을 수 있다. 반배정 이전, 이들을 전산상에 등록하고 싶을 때에는, Null값을 입력할 수 있도록 해줘야 한다. 따라서 해당 테이블에서 schClassId는 Null을 허용해준다. |
| StudentSeqNo | tinyint | O | - 위와 같은 이유로, schClassId의 자식요소인 StudentSeqNo 또한 Null을 허용해준다. |
| EntranceDate | date | X | - 입학년도 - 학생들 중, 이것이 없는 학생은 존재하지 않는다. 따라서 Null이 존재할 수 없음. |
| GraduateDate | date | O | - 졸업년도 - 재학생일 경우 Null의 값을 가질 수 있다. 즉, Null이면 재학생, Null이 아니면 졸업생으로 표현 가능해야 하므로, Null을 허용한다. |
- schGradeId + schClassId + StudentSeqNo는, PK로 지정되어야 하지만 Null을 허용하고 있는 부분이 있기 때문에, PK가 될 수 없다. 따라서 이 셋은 Unique속성을 부여한다.
[ TB_schSubject ] // 절대마스터 테이블
| 컬럼명 | 데이터타입 | Null | 비고 |
| SubjectId | tinyint | X | |
| SubjectName | varchar(50) | X |
- 과목은 학년별로 존재할 것이다.
- 과목 - 선생님의 관계
: 과목은 선생님을 가질 수 없다.
: 선생님 또한 과목을 가질 수 없다.
=> 이 둘은 선천적으로 연관성이 없는 테이블이다.
[ TB_schTeacher] // 절대마스터 테이블
| 컬럼명 | 데이터타입 | Null | 비고 |
| TeacherId | tinyint | X | |
| TeacherName | varchar(50) | X |
- 고려사항 ex. 1학년 수학을 담당하던 선생님이, 2학년 선생님의 부재로 2학년 수학까지 담당해야 한다면?
cf )
schClass와 Teacher를 outer Join으로 보면 비담임에 대한 자료들을 확인할 수 있을 것.
select * from TB_schClass as a right outer join TB_schTeacher as b on a.TeacherId = b.TeacherId;
-- TB_schTeacher는 모든 컬럼이 나오고, TB_schClass는 공통된 부분만 나오기 때문에,
-- TB_schTeacher 테이블의 값이 Null이라면, 비담임인 선생님을 파악할 수 있다.schClass와 Teacher를 inner Join으로 보면 담임에 대한 자료들을 확인할 수 있을 것.
select * from TB_schClass as a inner join TB_schTeacher as b on a.TeacherId = b.TeacherId;
-- 두 테이블의 TeacherId가 공통된 컬럼들만 나오기 때문에, 담임인 요소들을 구분 가능하다.
[ OpenLecture ] // 관계 테이블 (과목 - 선생님 사이의)
| 컬럼명 | 데이터타입 | Null | 비고 |
| Seq | bigint | X | - 이 테이블은 관계 테이블이므로, 학교가 폐교될 때까지, 10년 ... 100년.. 계속 쌓여나갈 것이다. - 이는 보조키로서 활동한다. |
| TeacherId | tinyint | X | - 과목과 선생님 사이의 관계 테이블이므로, 선생님 TeacherId 필요 |
| SubjectId | tinyint | X | - 과목과 선생님 사이의 관계 테이블이므로, 과목 SubjectId 필요 |
| OpenDate | date | X | - 어떤 년도에 개강한 과목인지 파악하기 위해, OpenDate 필요 |
| schGradeId | tinyint | X | - 같은 과목이더라도 학년별로 다를 수 있기 때문에, 학년을 구분해줄 수 있는 schGradeId 컬럼을 추가한다. - 즉, 학년별 수업시간표를 구분한다는 것이다. - TB_schGrade의 FK |
- 강좌개강 테이블 : a학년 b과목에 대한 c선생님이, d년도에 개강했다 -> 파악 가능
[ TB_Score ] // 관계 테이블
| 컬럼명 | 데이터타입 | Null | 비고 |
| StudentId | bigint | X | - 1명 학생에 대한 성적을 매기는 관계 테이블이기 때문에, StduentId 필요 |
| OpenLectureSeq | bigint | X | - OpenLecture에 대해서 성적을 매기는 것이기 때문에, 해당 테이블의 Seq를 참조하는 FK로 설정. - OpenLecture 테이블을 통해, 몇년도에 어떤 과목의 어느 선생님이 개강한 수업인지에 대한 정보를 가져올 수 있음. |
| Score | tinyint | X | - 기본값 : 0 |
4) Stored Procedure
| [ 참고 - 테이블 접두어] a. tbl_ : 테이블 b. vw_ : 뷰 c. sp_ : 스토어드 프로시저 ex. SP_Insert_테이블명 / SP_Update_컬럼명 / SP_테이블명_Select함수기능() // SSMS에서는 함수, 트리거, 저장 프로시저, 뷰 등을 다른 카테고리로 관리하고 있다. // 그러나, 프로그래머 입장에서는 이를 구분할 수 없다. 따라서 이들이 접두어를 통해 구분할 수 있도록 설계하도록 돕는 것이 필요하다. |
-- SP_Insert_schGrade : Stored Procedure 이름 (TB_schGrade에 Insert를 할 때)
Create PROCEDURE SP_Insert_schClass
@schGradeId tinyint,
@schClassId tinyint,
@schClassName varchar(50),
@Useflag bit,
@TeacherId int,
@AssignedDate date
AS
BEGIN
INSERT INTO TB_schClass VALUES(@schGradeId, @schClassId, @schClassName, @Useflag, @TeacherId, @AssignedDate);
END
-- schClass 테이블에서 Select 스토어드 프로시저를 생성한다면?
-- : SP_schClass_GetById(schGradeId, schClassId) // 학년 및 반별 ID에 따른 레코드를 요청할 경우
-- : SP_schClass_GetAll() // 모든 레코드를 달라고 요청할 경우
-- : SP_schClass_GetByGrade(schGradeId) // 학년별 ID에 따른 레코드를 요청할 경우
-- 이러한 스토어드 프로시저를 생성해야 한다.-- 스토어드 프로시저 실행방법
exec SP_Insert_schClass 1,1,'1반',1,1,'2022-08-30' ;
exec SP_Insert_schClass 1,2,'2반',1,1,'2022-08-30';
exec SP_Insert_schClass 1,3,'3반',1,1,'2022-08-30';
exec SP_Insert_schClass 2,1,'3반',1,1,'2022-08-30';
exec SP_Insert_schClass 2,2,'3반',1,1,'2022-08-30';
exec SP_Insert_schClass 2,3,'3반',1,1,'2022-08-30';
exec SP_Insert_schClass 3,1,'3반',1,1,'2022-08-30';
exec SP_Insert_schClass 3,2,'3반',1,1,'2022-08-30';
exec SP_Insert_schClass 3,3,'3반',1,1,'2022-08-30';
select * from TB_schClass;
-- 반의 속성들을 update할 수는 있지만, PK는 update가 불가능하다.
-- 즉 여기서는 반의 이름을 update시켜주는 것 (PK가 아님)
update TB_schClass
set schClassName = cast(schClassId as varchar) + '반' -- schClassId를 varchar로 cast하고 '반'을 붙여라.
select * from TB_schClass;
5) 참고 : 테이블 정렬 (Arrange Tables)
- 테이블 정렬을 하면, 다이어그램 내에 있는 모든 테이블들이 정리된다.
- 만약, 테이블 정렬을 했을 경우, 두 그룹으로 분류된 경우, 이 ERD는 완성되지 않은 ERD라는 것을 파악할 수 있다.
- 실제로 모든 비즈니스는 얽히고 섥혀 연결되기 때문에, 이와 같이 분리되는 설계가 나오기는 쉽지 않기 때문이다.
ex. Log 테이블과 같은 예외상황 제외
| * 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :) |