Mini University DB design

---

Mission

---

EER Diagram

우선 한 building에 여러 room이 포함되는 것이므로 building:room=1:n의 일대다 관계이고, building이 존재해야 room이 존재할 수 있으므로 식별관계로 relation을 설정해주었다.

 

다음으로 한 building에 여러 department가 존재할 수 있지만 학생회관과 같이 특수한 건물에는 어떠한 과도 존재하지 않으므로 building:department=1:n의 일대다 관계이면서 department가 포함되지 않아도 되므로 mandatory 조건을 풀어주었다.

 

이제 room은 building에 물리적으로 포함되어 있지만 특정 department소속의 room들도 존재한다. 한 department가 여러 개의 room을 소유하므로 department:room=1:n의 일대다 관계이지만 어떤 room은 아무 department에도 소속되지 않은 공용일 수도 있으므로 mandatory 조건을 풀어주었고 비식별 관계로 설정해주었다.

 

마지막으로 한 department에 여러 명의 student가 포함되어 있고 한 student가 복수전공과 같이 여러 개의 department에 포함될 수 있으므로 department:student=n:m의 다대다 관계이다. 이때 학생이 없는 과도 없고 소속과가 없는 학생도 없으므로 전부 mandatory관계이다.

---

실행화면

만든 table들이 보인다.

Building에서는 Primary Key는 b_id로 설정해주었고 attribute는 모두 Null이 불가능하도록 하였다. b_id는 관리가 쉽도록 integer로 설정해주었다. b_name은 다양한 길이를 가질 수 있으므로 varchar로 설정하였고 building의 이름은 겹칠 수 없기 때문에 unique key로 설정해주었다.

Department에서 Primary Key는 d_id이고 building에 포함되므로 building과 연결할 FK를 만들기 위해 building_b_id라는 PK도 설정해주었다. Id들은 마찬가지로 관리하기 쉽기 위해 integer로 설정해주었고 d_name, d_email, d_phone은 모두 varchar로 설정하였다. 전화번호는 대부분 비슷한 길이지만 혹시나 모를 예외사항을 위해 가변성을 주었다. 또한 과의 이름, 이메일 주소, 전화번호는 겹칠 수 없기 때문에 전부 unique key로 설정하였다. 추가로 모든 attribute들은 없어어도 안되고 없을 가능성도 없기 때문에 notnull로 설정하였다.

Department_has_student는 department와 student의 다대다 연결을 위한 연결테이블로 PK인 department_d_id, student_s_id만 갖는다.

Room에서는 r_id가 Primary Key이고 type은 관리하기 쉽도록 integer로 설정하였다. r_name은 varchar로 이름에 가변성을 주었다. 이때 room의 이름은 다른 건물에서는 겹치는 이름이 있을 수 있어서 unique key로는 설정하지 않았다. r_capacity는 수용량이므로 integer로 설정하였고 같은 사이즈의 방은 많이 있을 수 있으므로 unique key로 설정하지 않았다. 그러나 모두 정보가 없다면 불편함이 많을 것으로 추정되어 notnull설정을 해주었다. 다음으로 building_b_id는 각각 room이 building에 반드시 포함되어야하므로 연결해주는 FK역할을 해야하므로 PK로 설정을 해주었다. 반면 department_d_id는 room이 department에 반드시 포함되어야 하는 것은 아니므로 비식별에 null possible로 설정해주었다.

Student에서는 s_id를 Primary Key로 설정해주고 integer type으로 설정했다. 이름은 길이가 다를 수 있으므로 s_name은 varchar로 가변성을 주고 동명이인이 존재할 수 있으므로 unique key로 설정하지 않았다. s_email과 s_phone은 겹칠 수 없기 때문에 varchar type에 unique key로 설정해주었다. s_major는 소속과로 varchar type으로 설정하고 과는 많은 사람이 겹치므로 unique key설정을 하지 않았다. 또한 여기서의 major는 첫번째 주전공을 뜻한다.

---

Data입력

mini_univ.sql

INSERT INTO building VALUES(12, 'hightech');
INSERT INTO building VALUES(1, 'main');
INSERT INTO building VALUES(2, 'library');
INSERT INTO building VALUES(3, '60_anniv');
INSERT INTO building VALUES(5, 'building_5');
INSERT INTO building VALUES(7, 'student_center');

INSERT INTO department VALUES(416, 'ice', 'ice@inha.ac.kr', '032-860-7431', 12);
INSERT INTO department VALUES(291, 'mechanical', 'me@inha.ac.kr', '032-860-7300', 5);
INSERT INTO department VALUES(423, 'computer science', 'cse@inha.ac.kr', '032-540-9632', 12);
INSERT INTO department VALUES(244, 'electrical', 'eee@inha.ac.kr', '032-824-7823', 12);
INSERT INTO department VALUES(221, 'biology', 'bio@inha.ac.kr', '032-472-9812', 3);
INSERT INTO department VALUES(289, 'chemistry', 'chm@inha.ac.kr', '032-860-7234', 5);
INSERT INTO department VALUES(539, 'korean edu', 'ke@inha.ac.kr', '032-347-5982', 5);
INSERT INTO department VALUES(564, 'math', 'mth@inha.ac.kr', '032-912-9443', 5);

INSERT INTO room VALUES(232, 'ice1', 45, 12, 416);
INSERT INTO room VALUES(230, 'ice2', 45, 12, 416);
INSERT INTO room VALUES(718, 'dance_room', 50, 7, null);
INSERT INTO room VALUES(300, 'wallchoen', 250, 3, null);
INSERT INTO room VALUES(204, 'ar_ex', 10, 2, null);
INSERT INTO room VALUES(107, 'startup', 30, 1, null);
INSERT INTO room VALUES(512, 'math1', 50, 5, 564);
INSERT INTO room VALUES(734, 'studyroom1', 50, 2, null);

INSERT INTO student VALUES(101, 'John Doe', 'john.doe@example.com', '010-456-7890', 'computer science');
INSERT INTO student VALUES(102, 'Jane Smith', 'jane.smith@example.com', '010-4567-6891', 'biology');
INSERT INTO student VALUES(103, 'Alice Johnson', 'alice.johnson@example.com', '010-4567-892', 'math');
INSERT INTO student VALUES(104, 'Bob Brown', 'bob.brown@example.com', '123-456-7893', 'math');
INSERT INTO student VALUES(105, 'Charlie Davis', 'charlie.davis@example.com', '123-4567-894', 'electrical');
INSERT INTO student VALUES(106, 'Daisy Evans', 'daisy.evans@example.com', '123-456-7895', 'chemistry');
INSERT INTO student VALUES(107, 'Edward White', 'edward.white@example.com', '123-4567-896', 'computer science');
INSERT INTO student VALUES(108, 'Fiona Green', 'fiona.green@example.com', '123-456-7897', 'ice');
INSERT INTO student VALUES(109, 'George Wilson', 'george.wilson@example.com', '123-456-7898', 'ice');
INSERT INTO student VALUES(110, 'Hannah Taylor', 'hannah.taylor@example.com', '123-456-7899', 'korean edu');

INSERT INTO department_has_student VALUES(423, 101);
INSERT INTO department_has_student VALUES(564, 101);
INSERT INTO department_has_student VALUES(221, 102);
INSERT INTO department_has_student VALUES(564, 103);
INSERT INTO department_has_student VALUES(564, 104);
INSERT INTO department_has_student VALUES(244, 105);
INSERT INTO department_has_student VALUES(289, 106);
INSERT INTO department_has_student VALUES(423, 107);
INSERT INTO department_has_student VALUES(416, 108);
INSERT INTO department_has_student VALUES(416, 109);
INSERT INTO department_has_student VALUES(291, 109);
INSERT INTO department_has_student VALUES(539, 110);

---

Select를 이용한 출력

---

Conclusion

Mini Univ DB를 설계하면서 느낀점은 사소한 실수가 DB에 어마어마한 문제를 불러일으킬 수 있다는 점과 한번 설계한 DB에서 사소하더라도 변화가 생긴다면 모든 데이터를 수정하는 것에 있어서 어려움이 있을 것 같다는 점이다. DBA는 이러한 점을 고려해서 보다 직관적이고 간결하게 DB를 구성해야하고 사소한 오류도 허용할 수 없는 굉장히 민감한 작업을 하는 역할이라는 생각이 들었다.

---