Nirsa

엑셀로 SQL 쿼리 만들기

엑셀에 있는 데이터를 SQL 쿼리로 변환하여 사용하는 방법 입니다. 

1. 엑셀에 데이터 준비

2. 아래 내용을 엑셀에 넣습니다.

B2, C2는 처음 데이터가 시작되는 엑셀 위치 입니다.

//            테이블이름     컬럼1      컬럼2            데이터1     데이터2
="INSERT INTO TableName (FIELDNAME1,FIELDNAME2) values ('"&B2&"', '"&C2&"')"

3. 변경된 데이터를 확인 후 변경된 내용의 우측 하단을 마우스 클릭하여 쭉 내립니다.

아래와 같이 단순 반복할 필요 없이, 필요한 쿼리들이 생성된것을 볼 수 있습니다.

• 데이터베이스 정규화란?

이상 문제를 해결하기 위해 속성들 끼리의 종속 관계를 분석하여 여러개의 릴레이션으로 분해하는 과정 입니다. 일반적으로 테이블을 여러개로 분해하면 속도는 상대적으로 느려질 수 있지만, 분해하지 않으면 이상 문제들이 발생 합니다.

• 이상 문제

• 삽입 이상 : 데이터를 저장할 때 원하지 않는 정보가 함께 삽입되는 경우
• 삭제 이상 : 튜플을 삭제함으로써 유지되어야 하는 정보 까지도 연쇄적으로 삭제되는 경우
• 갱신 이상 : 중복된 튜플 중 일부의 속성만 갱신 시킴 으로써 정보의 모순성이 발생하는 경우

예를들어 아래와 같은 테이블이 있을 경우 이상 문제가 발생할 수 있습니다.

• 삽입 이상 : 신입 학생이 입학하여 학번과 학년 등을 입력하려 했으나 아직 과목이 정해지지 않았거나, 시험을 보지 않아 성적이 없는 상태이기 때문에 불필요한 정보(과목 이름, 성적)를 함께 삽입 해야 함
• 삭제 이상 : 학생 번호가 2번인 학생의 과목에 대한 성적을 삭제할 경우 학생 번호와 학년 등 모든 정보가 같이 삭제되어 학생의 정보 자체가 사라짐
• 갱신 이상 : 학생 번호가 3번인 학생이 2학년이 되어 학년 정보를 변경 하려 하는데 3개를 모두 하나씩 바꿔줘야 함, 하나라도 안바꿀 경우 한명의 학생에 대한 정보가 서로 달라지는 정보의 모순성이 발생

• 함수적 종속이란?

함수적 종속은 어떠한 릴레이션에서 속성들의 부분 집합을 X, Y라 할 때 특정 튜플에서 X의 값이 Y의 값을 함수적으로 결정 한다면 Y가 X에 함수적으로 종속 되었다고 합니다.

함수적 종속의 추론 규칙은 아래와 같습니다.

• 재귀 규칙 : Y가 X의 부분 집합이면 X → Y 이다.
• 증가 규칙 : X → Y 이면 WX → WY 이고 WX → Y 이다.
• 이행 규칙 : X → Y 이고 Y → Z 이면 X → Z 이다.
• 유니온 규칙 : X → Y 이고 X → Z 이면 X → YZ 이다.
• 분해 규칙 : X → YZ 이면 X → Y와 X → Z 이다.
• 가이행 규칙 : 만일 W → X 이고 XY → Z 이면 WY → Z 이다.

• 정규화 과정

• 제1 정규형

도메인은 모두 원자값이어야 합니다. 핸드폰 번호에 2가지가 들어가 있기 때문에 제1 정규형을 만족할 수 없게 됩니다. (관계형 데이터 베이스의 경우 기본적으로 도메인은 모두 원자값이기 때문에 제1 정규형은 지켜져 있습니다)

위의 테이블을 제1 정규형에 맞춰 수정하면 아래와 같습니다.

• 제2 정규형

제2 정규형은 부분 함수 종속을 제거 합니다. 아래 표에서 학번은 학부와 등록금을 결정할 수 있고, 학번과 과목 코드는 성적을 결졍할 수 있습니다. 

함수(학번, 과목코드)에서 부분 함수인 학번 혼자서 학부와 등록금을 결정할 수 있기 때문에 제2 정규형을 만족할 수 없기 때문에 성적, 학부, 등록금에 모두 영향을 주는 학번을 기준으로 릴레이션을 아래와 같이 분리 시킵니다.

• 제3 정규형

제3 정규형은 이행 함수 종속을 제거 합니다. 현재 아래 테이블은 학번(X)이 학부(Y)를 결정하고, 학부(Y)가 등록금(Z)을 결정 하고, 이행 규칙에 따라 학번(X)이 등록금(Z)을 결정할 수 있습니다. (이행 규칙 : X → Y 이고 Y → Z 이면 X → Z 이다.)

현재는 이행 규칙을 만족하므로 제3 정규형을 만족 시키지 못하며, 이행 함수 종속을 제거해주어야 합니다.

또한 위의 상태에서 갱신 이상이나 여러 문제가 발생할 수 있기 때문에 X → Y, Y → Z 의 형태로 릴레이션을 또 다시 분해 합니다.

• BCNF : 결정자 중 후보키가 아닌 것들은 제거
• 제4 정규형 : 다치 종속 제거
• 제5 정규형 : 조인 종속성 제거

• 데이터 모델의 종류

• 계층 데이터 모델 : 트리 데이터 모델이라고도 하며 부모-자식 관계를 가집니다. (DBMS : IMS, System 2000 등)
• 네트워크 데이터 모델 : 망 데이터 모델이라고도 하며 그래프 형태로 표현 합니다. (DBMS : DBTG, IDMS, ids 2, Total, DMS/1100 등)
• 관계 데이터 모델 : 표 데이터 모델이라고도 하며 구조가 단순하고 사용이 편리 합니다. (DBMS : DB2, Ingres, Informix, SQL Server, Oracle, Mysql 등)

• 관계형 데이터 모델이란?

관계형 데이터 모델은 논리적인 릴레이션 구조로 구성되며 사용자는 원하는 데이터(waht)만 명시하고 어떻게 이 데이터를 찾을 것인가(how)는 명시할 필요가 없습니다. DB의 논리적인 면과 물리적인 면을 구분하여 데이터의 독립성을 향상 시키며 당시의 다른 데이터 모델에 비해서 보다 단순한 구조를 이루었습니다.

• 관계형 데이터 모델 관련 용어

릴레이션(Relation) : 정보 저장의 형태가 2차원 구조의 테이블임, 흔히 말하는 테이블과 같은 단어이며 아래와 같은 모양 입니다.

속성(Attribute) : 테이블의 각 열을 의미 합니다.

튜플(Tuple) : 테이블의 한 행을 구성하는 속성들의 집합이며 레코드라고도 부릅니다.

도메인(Domain) : 속성이 취할 수 있는 값들의 집합, 즉 각 속성들의 데이터 타입 입니다.

기수(Cardinality) : 튜플의 수

차수(Degree) : 속성의 수

• 관계형 데이터 모델 키의 종류

- 슈퍼키(Super Key) : 유일성은 갖지만 최소성을 만족 시키지 못하는 속성의 집합 (최소성이란 하나로 모든 튜플을 구분할 수 있지만 여러개의 튜플을 합쳐도 구분하기에 상관 없는걸 최소성을 만족 시키지 못한다고 합니다)

- 후보키(Candidate Key) : 유일성과 최소성을 모두 갖는 속성의 집합이며 여러개가 존재할 수 있습니다. (주민등록번호, 핸드폰번호 등)

- 기본키(Primary Key) : 개체 식별자로 튜플을 유일하게 식별할 수 있는 속성의 집합 입니다. (후보키중에서 하나가 기본키가 됩니다)

- 대체키(Alternate Key) : 기본키를 제외하고, 기본키가 될 수 있는 후보키 입니다.

- 외래키(Foreign Key) : 다른 테이블을 참조하는데 사용되는 속성으로 외래키가 참조할 부모 속성은 기본키이거나 널값이어야 합니다. 참조 무결성을 위해 사용 됩니다.

• 관계형 데이터 제약 조건

- 개체 무결성 : 기본키와 관련된 제약 조건으로 릴레이션에서 특정 속성을 기본키로 지정할 때 어떠한 속성도 NULL과 중복을 허용하지 않습니다. (ex. 주민등록번호에 값이 비거나 중복되어선 안되고, 즉 유일성과 최소성을 가지게 됩니다)

- 참조 무결성 : 외래키와 관련된 제약 조건으로 NULL이거나 참조하는 릴레이션에는 기본키여야 합니다. (외래키는 NULL이거나 부모 릴레이션의 값과 같아야 합니다)

도메인 무결성 : 특정 속성의 값은 그 속성에 정의된대로의 값만 허용 합니다. (ex. age에 address를 입력할 수 없음)

• ER모델 (Entity-Relationship Model)

ER모델은 피터 첸이 제안한 개념적 데이터 모델로써 현실 세계를 개체와 개체 간의 관계를 이용하여 개념적 구조로 표현하여 ERD(Entity-Relationship Diagram)으로 작성할 수 있습니다.

• 개체, 속성, 관계

개체는 현실 세계에서 꼭 필요한 사람이나 사물과 같이 구별되는 모든 것을 가르키며 데이터로써 DB에 저장할 가치가 있는 중요한 사람, 사물, 개념, 사건 등을 뜻 하며 최소 하나 이상의 개체를 가지고 있어야 합니다. (ex. 학원에 필요한 개체 : 학생, 수업 등)

속성은 개체나 관계가 가지고 있는 고유의 특성으로 DB에 저장할 데이터의 가장 작은 논리적 단위 입니다. (ex. 학생 개체의 속성 : 학생의 이름, 학생의 성별, 학생의 핸드폰 번호 등)

관계는 서로 다른 개체가 맺고 있는 의미 있는 연관성을 뜻합니다. 즉, 개체 사이의 대응 관계(매핑)를 뜻합니다.

• ERD(Entity-Relationship Diagram) 구성 요소

ER다이어그램은 아래와 같이 그림으로 표현되는 구성 요소들이 있습니다.

각각의 참여나 관계 등에 따라 각 테이블을 이어주는 선이 변경 됩니다. 1:1 관계(남자-여자), 1:n 관계(학교-학생), n:m 관계(학생-수업) 등이 있으며 참여 부분은 아래와 같습니다. 

선택적 참여(부분 참여) : 개체 중 일부만 관계에 참여해도 되는 것을 의미 (ex. 도서 개체가 고객 개체와의 구매 관계에 선택적으로 참여)

필수적 참여(전체 참여) : 개체가 관계에 반드시 참여 해야 하는것을 의미 (ex. 학생 개체가 수업 개체와의 수강 관계에 필수적으로 참여)