[문과 코린이의 IT 기록장] RDBMS Modeling 기초 - 연습문제 1 (도서관의 도서 대출 관리, 중학교 학생 성적관리 프로젝트)

[문과 코린이의 IT 기록장] RDBMS Modeling 기초 - 연습문제 1 (도서관의 도서 대출 관리, 중학교 학생 성적관리 프로젝트)

[문과 코린이의 IT 기록장] RDBMS Modeling 기초 - 연습문제 1 (도서관의 도서 대출 관리, 중학교 학생 성적관리 프로젝트)

 

---

RDBMS Modeling 기초 - 인프런 | 강의

---

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도 상속받아야 한다.

책seq Unique설정방법 : 1) Add -> 2) Is Unique : Yes설정

 

[ 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 테이블과 같은 예외상황 제외

 

---

* 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :)