[과제] 가족관계등록부 ERD 작성

과제 내용

• 가족관계등록부 ERD 도출
  • 테이블, 속성, 릴레이션만 도출
  • 한글명만 도출

ERD

개념적 모델링

먼저 관계를 파악하기 위해 개념적인 모델을 그려보았다.

요구사항에 대해 정리해보자.

1. 엔티티에 해당하는 것은 사람 뿐이며 사람과 사람 간에 관계를 가진다.
2. 한 사람은 여러 관계를 가질 수 있고, 관계명은 다양하다.
3. 사람에 대한 기본 정보 뿐만 아니라 사망 여부도 표시가 필요하다.
4. 현재의 가족관계등록부에 표시되는 내용 뿐만 아니라 과거의 이력도 저장할 수 있어야 한다. (ex. 이혼 등등)

ERD 1차 작성

• 고려사항
  • 개념 모델링에서 도출된 그대로 사람 테이블과 관계 테이블로 나누어 작성하였다.
  • 관계명은 행정상 바뀔 수 있는 여지가 존재한다고 생각하였기 때문에, 관계에 대한 이름을 저장하는 구분 테이블로 분리하였다.
  • 관계 모델에서는 데이터의 정합성을 생각하여 관계 정보가 중복되지 않도록 부/모에 대한 정보는 가지지 않고 배우자, 자녀에 대한 정보만 저장하도록 한다.
  • 즉, 부/모에 대한 정보를 조회하기 위해서는 관계테이블에서 나와 자녀 관계로 이어진 사람을 찾아야한다.
• 문제점
  • 가족관계등록부를 조회할 때 쿼리문이 매우 복잡해진다. (조인 다수 발생)
  • 요구사항 중 4번에 해당하는 과거 이력을 고려하지 못하고 작성했던 것이기 때문에 과거의 이력을 조회할 수 없다.
• 해결방법
  • 구분 테이블과 관계 테이블을 통합한다.
  • 관계 테이블에 과거의 이력인지 현재 관계에 해당하는 것인지를 표시할 수 있도록 상태코드를 추가한다.
  • 쿼리 단순화를 위해 관계 테이블에 정보를 중복 저장한다. (부/모에 대한 관계도 저장하도록 변경)
    • 필요하다면 중복을 선택하고 쿼리를 단순화하기도 한다❗️
    • 실제로도 우리나라의 주민등록 DB에는 관계가 중복되어 저장되어 있다.

ERD 최종 작성

• 추가로 고려해볼 사항
  • 사람 테이블에서는 데이터의 중복을 막기위해 Unique Key로 주민등록번호를 지정하면 되지만, 관계 테이블에서는 어떻게 데이터의 유일성을 보장할 수 있을까?

작성한 ERD를 바탕으로 쿼리 작성해보기

1. 본인의 가족관계증명서를 뽑는 쿼리를 작성

SELECT 관계.관계명, 사람.성명, 사람.사망여부, 사람.출생연월일, 사람.주민등록번호, 사람.성별, 사람.본
FROM 사람
	INNER JOIN (SELECT 관계.가족번호 관계.관계명
				FROM 관계
				WHERE 관계.사람번호 = (SELECT P.사람번호 FROM 사람 AS P WHERE P.주민등록번호="*********")
					AND 관계.상태코드 = TRUE)
	ON 사람.사람번호 = 관계.가족번호;

2. 내 딸의 가족관계증명서를 뽑는 쿼리를 작성

SELECT 사람.사람번호 INTO @CHILD
FROM 사람
    INNER JOIN (SELECT 관계.가족번호
                FROM 관계
                WHERE 관계.사람번호 = (SELECT P.사람번호 FROM 사람 AS P WHERE P.주민등록번호="*********")
                    AND 관계.관계명 = "자녀"
                    AND 관계.상태코드 = TRUE)
    ON 관계.가족번호 = 사람.사람번호
WHERE 사람.성명 = "자녀이름";

SELECT 관계.관계명, 사람.성명, 사람.사망여부, 사람.출생연월일, 사람.주민등록번호, 사람.성별, 사람.본
FROM 사람
    INNER JOIN (SELECT 관계.가족번호 관계.관계명
                FROM 관계
                WHERE 관계.사람번호 = @CHILD)
    ON 사람.사람번호 = 관계.가족번호;

3. 아버지의 형제자매 목록 추출

SELECT 사람.사람번호 INTO @FATHER
FROM 사람
    INNER JOIN (SELECT 관계.가족번호
                FROM 관계
                WHERE 관계.사람번호 = (SELECT P.사람번호 FROM 사람 AS P WHERE P.주민등록번호="*********")
                    AND 관계.관계명 = "부"
                    AND 관계.상태코드 = TRUE)
    ON 관계.가족번호 = 사람.사람번호;

SELECT 관계.관계명, 사람.성명, 사람.사망여부, 사람.출생연월일, 사람.주민등록번호, 사람.성별, 사람.본
FROM 사람
    INNER JOIN (SELECT 관계.가족번호 관계.관계명
                FROM 관계
                WHERE 관계.사람번호 = (SELECT R.가족번호 FROM 관계 AS R WHERE R.사람번호 = @FATHER AND R.관계명 = "부" AND R.상태코드 = TRUE)
					AND 관계.관계명 = "자녀"
                    AND 관계.상태코드 = TRUE)
    ON 사람.사람번호 = 관계.가족번호;

4. 엄마가 재혼한 경우 어머니 목록을 추출

SELECT 관계.관계명, 사람.성명, 사람.사망여부, 사람.출생연월일, 사람.주민등록번호, 사람.성별, 사람.본
FROM 사람
	INNER JOIN (SELECT 관계.가족번호 관계.관계명
				FROM 관계
				WHERE 관계.사람번호 = (SELECT P.사람번호 FROM 사람 AS P WHERE P.주민등록번호="*********")
					AND 관계.관계명 = "모")
	ON 사람.사람번호 = 관계.가족번호;