세상의 모든 Software 지식을 정리

"기본키(Primary Key)에 의존하지 않고 일반 컬럼에 의존하는 칼럼들을 제거한다." 이를 이전적 함수 종속관계라고하며 이를 해결하는 것이 바로 제 3정규화이다. 위의 주문 테이블에서 키가 아닌 모든 컬럼은 기본키인 주문ID에 종속적인가? 그렇지 않다. 회원명, 전화번호, 회원등급은 회원ID에 종속적이다. 즉 위의 견본데이터에서 회원 ID가 "HONG"임을 알면 회원명은 '홍길동', 전화번호는 222-2222, 회원등급은 '우수'임을 알 수 있다. 그럼 이 설계의 문제점은 무엇인가? 회원이 주문을 할 때마다 동일한 회원에대한 회원명, 전화번호, 회원등급에 대한 데이터가 계속해서 중복되어 쌓일 것이다. 또한 중복되는 데이터들로 인한 데이터 무결성을 유지하는데 문제가 될 수 있다. 만약 홍길동의 전화번호가 222-2222에서 211-1111으로 수정이 되어졌다면 주문 테이블에서 홍길동이란 회원의 모든 전화번호를 수정해 주어야 할 것이다. 제 3정규화란 기본키에 전적으로 의존하지 않고 키가 아닌 일반 칼럼에 의존하는 칼럼을 제거시켜 원래의 제자리에 위치시키는 것이다. 따라서 다음과 같이 새로운 테이블을 구성할 수 있다.

2. 제 2정규화 "복합키(Composit Primary Key)에 전체적으로 의존하지 않는 속성들을 제거한다." 우선 제 2정규화의 대상이 되는 테이블은 키가 여러 칼럼으로 구성된 경우이다. 제 2정규화를 이해하기 위해서 우선 '키에 의존적(지배적)이다' 라는 말의 의미를 먼저 살펴보자. '키의 값을 알면 칼럼 값을 알 수 있다.' 위 테이블의 기본키는 사원번호이다. 키가 아닌 일반칼럼은 모두 기본키에 의해 지배되어야 한다. 키에의해 지배되어야 한단느 것은 위의 사원 테이블에서 사원번호를 알면 그 사원에 대한 이름, 주소, 성별, 입사일을 알 수 있음을 의미한다. 만약 사원 테이블에 상품명이라는 칼럼이 들어와 있다고 하자. 상품명이 사원번호에 의조적인가? 그렇지 않다. 이런 칼럼들은 전혀 의미가 없으며 걸러져 제자를 찾아가야만한다. 기본적으로 테이블의 키가 아닌 모든 칼럼들은 기본키에 의존적이어야만 한다. 만약 테이블의 기본키가 여러 컬럼으로 구성된 경우 즉, 복합키인 경우도 역시 모든 칼럼들은 복합키 전체에 의존적이어야 한다. 만약 복합키 전체에 의존하지 않는 컬럼이 존재하는 경우 기본 테이블에서 제거시켜 새로운 테이블을 생성해 주어야 한다. 평가코드는 학번, 과정코드 전체에 의존적이지만 과정명은, 기간은 복합키의 일부분인 과정코드에 의해서 지배됨을 알 수 있다. 이렇게 테이블이 설계되었을 경우의 문제점은 과정명, 기간에 중복되는 데이터가 계속 쌓이게 된다. 또한 이렇게 설계되었을 경우에 데이터의 무결성을 유지하는데 어려움이 따른다. 만약 'JAVA' 과정명이 C++로 수정되었을 경우에 그에 해당하는 모든 데이터를 수정해야 한다. 제 2정규화 이론을 적용시켜 복합키의 일부분 즉, 과정코드에 의존적인 속성인 과정명, 기간을 기본 테이블에서 제거시켜 새로운 테이블을 구성하면 다음과 같은 두 개의 테이...

1. 제 1정규화 "반복되는 그룹 속성을 제거한 뒤 기본 테이블의 기본키를 추가해 새로운 테이블을 생성하고 기존 테이블과 1:N의 관계를 형성한다." 만약 홍길동이라는 사람이 운전면허1종, MCSE, 정보처리기사 자격증을 갖고 있다고 하자. 이럴 경우 홍길동의 데이터를 모두 저장하기 위해서는 아래와 같이 3개의 레코드가 필요할 것이다. 이렇게 하면  몇 가지 문제가 생길 수 있다. 즉 쓸데없는 데이터베이스의 공간이 낭비가 발생한다. 또한 만약 홍길동의 주소나 핸드폰번호가 바뀌게 되면 3개의 레코드를 모두 변경해야 한다. 그래서 1차 정규화의 대상이 되는 것이다. 이를 해결하기 위해서는 반복 되어지는 그룹 속성을 제거한 뒤 새로운 테이블을 생성하고 기존의 테이블과 1:N의 관계를 형성하면 된다. <2번째 예제> 어떤 학원에서 진행 중인 각 과정들의 데이터를 과정이라는 테이블에서 관리한다고 하자. 그리고 각 과정에 소요되는 교재를 과정 테이블에서 컬럼으로 관리한다면 다음과 같다. 이 테이블엣 교재 컬럼의 개수는 학원의 과정들 중에서 가장 많은 교재를 사용하는 과정을 기준으로 설정되어야 하며 아래의 견본 데이터에서는 5권이라 가정했다. 이렇게 데이터가 들어갈 경우 교재가 5권인 웹프로그래밍의 컬럼들은 모두 채워지지만 그외의 과목은 최소 1개 이상의 NULL값이 들어가지게된다. 테이블에 NULL값이 많다는 것은 쓸데없는 데이터 저장 공간의 낭비이다. 그래서 교재컬럼처럼 반복되는 속성을 갖는 칼럼의 경우 따로 분리해서 기존의 테이블과 1:N의 관계형성을 해야 한다.

복습: 2016/06/13, 2016/06/14 <ALL> -->모두(ALL)보다 크다는 가장 큰값보다 큰값을 찾는다. -->모두(ALL)보다 작다는 가장작은 값보다 작은 값을 찾는다. SELECT name,rating FROM restaurant_ratings WHERE rating>ALL (SELECT rating FROM restaurant_ratings WHERE rating>3 AND rating>9); --->Arthurs가 된다. 만약 rating<ALL이 되면 Pizza house가 된다. >=,<=인 경우는 7의 값을 포함한다. <ANY의 사용법> ANY는 서브쿼리의 결과값 중 하나라도 조건에 맞으면 true가 된다. SELECT name,rating FROM restaurant_ratings WHERE rating>ANY (SELECT rating FROM restaurant_ratings WHERE rating>3 AND rating<9); --> 다음과 같이 해석 할 수 있다. 평가점수(rating)이(5,7)중 어느 하다보다 큰 열을 찾아라 The Shack와 Arthur`s이다.

복습: 2016/06/13, 2016/06/14 *외부조인은 조인되는 테이블 중 하나의 모든 행을 다른 테이블에서 일치하는 정보와 함께 반환합니다. *내부조인에서 두 테이블의 행들을 비교하지만, 두 테이블 사이의 순서는 중요하지 않습니다. *왼쪽 외부조인은 왼쪽 테이블의 모든 행을 가져다 오른쪽 테이블의 행과 비교합니다. 왼쪽테이블과 오른쪽 테이블이 일대다 관계에 있을 때 유용합니다. *왼쪽 외부 조인에서는 FROM뒤에 그리고 조인전에 나오는 테이블이 왼쪽 테이블이고 조인 뒤에 나오는 테이블이 오른쪽 테이블입니다. <왼쪽 외부조인> 소녀(girls)들이 어느 장난감(toys)을 가지고 있는지를 알아보는데 왼쪽 외부 조인을 사용할 수 있다. SELECT g.girl,t.toy FROM girl g LEFT OUTER JOIN toys t ON g.toy_id=t.toy_id; -->외부조인은 다른 테이블과 일치하는 것이 있는가에 상관없이 행을 반환한다는 차이가 있다. 왼쪽 외부조인의 결과가 NULL값이면 오른쪽 테이블에 왼쪽 테이블에 상응하는 값이 없다는 의미이다. <오른쪽 외부조인> 오른쪽외부조인도 오른쪽 테이브을 왼쪽 테이블과 비교한다는 점을 제외하면 왼쪽 외부조인과 같습니다. <자신을 참조하는 참조키> -->자신을 참조하는 참조키는 같은 테이블에서 또다른 목적으로 사용하는 기본키이다. <셀프조인>  ->정규화된 데이터베이스에서는 같은 테이블을 두 개 사용할 일은 절대 없다. 대신, 두 테이블을 시뮬레이션 하기 위해 셀프조인을 사용할 수 있다. --> 셀프조인을 통해 하나의 테이블로 같은 정보를 가진 테이블이 두 개 있는 것처럼 쿼리를 보낼 수 있다. SELECT c1.name,c2.name AS boss FROM clown_info c1 INNER JOIN clown_info c2 ON c1.b...

* my_contact 테이블과 job_current 테이블이 있을 때, --->" 내 리스트에 있는 사람들 중에 누가 연봉이 가장 많을까?"를 구하여라 SELECT mc.first_name, mc.last_name, jc.salary FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.salary=(SELECT MAX(salary) FROM job_current jc); ** 서브쿼리를  SELECT열에서 사용. zip_code 테이블과 my_contacts테이블이 있을 때 my_contacts 테이블의 모든 행에 대해 이름(first_name)과 성(last_name)그리고 주(state) (우편번호를 가져다 zip_code테이블에서 해당 주 이름을 가져와 사용)를 가져옵니다. 예) SELECT mc.first_name, mc.last_name, (SELECT state  FROM zip_code  WHERE mc.zip_code=zip_code)AS state  FROM my_contacts mc; **자연조인을 포함한 서브쿼리 앤디의 연봉을 가져오고, 연봉을 비교하고, 그리고 앤디보다 더 많이 버는 사람의 이름가 성을 연봉과 함께 반환한다. 예) SELECT mc.first_name, mc.last_name,jc.salary      FROM my_contacts AS mc NATURAL JOIN job_current AS jc  WHERE jc.salary>(앤디의 연봉 쿼리) -->(SELECT jc.salary FROM my_contacts mc NATURAL JOIN job_current jc      WHERE email='andy@weatheroram.com'); 문제) 1. job_current 테이블에서...

* 서브쿼리는 언제나 SELECT 문 하나입니다. * 서브쿼리는 항상 괄호 안에 있습니다. * 서브쿼리는 세미콜론이 없습니다. * 세미콜론은 항상 전체 쿼리의 마지막에 위치합니다. * 서브쿼리는 쿼리문의 네 군데에 나올 수 있습니다. SELECT절,열들의 하나로 열리스트를 선택하는 경우, FROM절 HAVING절 *서브쿼리는 SELECT뿐 아니라 INSERT,DELETE, UPDATE와 도 함께 사용 할 수 있습니다. * 대부분의 경우, 내부 쿼리는 하나의 값만을 반환합니다. 즉, 한 행희 한열이죠. 외부쿼리는 그 값을 열의 모든값과 비교하는데 사용합니다. * IN을 사용하는 경우는 예외이다. 예) SELECT mc.first_name, mc.last_name, mc.phone, jc.title      FROM job_current AS jc NATURAL JOIN my_contacts AS mc      WHERE        jc.title IN( 'Cook','Hairdresser','Waiter','Web desigenr'); ** SELECT열에 대한 별명을 사용 할 수 있다. 예) SELECT mc.first_name AS firstname ,mc.last_name AS lastname               mc.phone AS phone, jc.title AS jobtitle      FROM job_current AS jc NATURAL JOIN my_contacts AS mc      WHERE jobtitle IN (SELECT title FROM job_listings);

primary key(pk) : 테이블에서 대표되는 key.즉 키에 해당하는 컬럼은 unique해야 되고(여기에서 unique index랑 혼돈되나 보네요..) not null이여야 됩니다.(null은 대표되는 key가 될수 없습니다.) 그래서 연관관계에 사용될수 있는 key입니다.  unique index : 테이블 컬럼에서 unique한 컬럼만 잡을수 있는 index입니다. 해당컬럼을 조회조건으로 가장 빠르게 조회할수 있게 index를 잡으며 무결성체크에도 사용됩니다.  예로 회원번호와 아이디가 있을 경우 회원번호를 pk로 잡고 아이디를 유니크인덱스로 잡아놓으면 아이디의 중복등록도 방지되고 아이디로 조회했을때 유니크인덱스를 타기 때문에 빠르게 조회가 됩니다.

*파티셔닝의 제약사항 -파티셔닝 된 테이블은 스토리지 엔진이 같아야 한다. -외부키 제약은 사용 할 수 없다 -FULLTEXT인덱스를 사용할 수없다. - GIS칼럼을 사용할 수 없다. *장/단점 1)장점 -대량의 데이터 저장 -테이블을 분할 할 수 있기 때문에 많은 양의 데이터를 저장 할 수 있다. -부하 경감 -해당 디스크에 남아있는 분할데이터를 받을 확률이 높기 때문에 캐시히트율도 높아진다. - 집계함수(SUM/COUNT)가 병렬처리가 가능해 속도향상을 가져온다. 2)단점  분할 방법의 정의, 관리, 그것을 취급하는 애플리케이션 측의 구현, 조사 비용 등이 증가한다. 7 |  파티션 테이블 테이블을 생성할 때 파티션으로 테이블을 만들 수 있다. 파티션이라 함은 테이블에 있는 특정 컬럼 값을 기준으로 데이터를 분할해 저장해 놓는 것이다. 이때 논리적인 테이블은 1개지만, 물리적으로는 분할한 만큼 파티션이 만들어져 입력되는 컬럼 값에 따라 분할된 파티션별로 데이터가 저장된다. 파티션 테이블을 만드는 목적은 대용량 테이블의 경우 데이터 조회 시 효율성과 성능을 높이기 위한 것이다. 예를 들어, 샘플 스키마에 매출정보가 있는 sales 테이블은 총 91만여 건의 데이터가 담겨 있다. 이 테이블에서 특정 데이터를 찾으려면 91만 건을 모두 뒤져봐야 한다. 물론 인덱스를 사용하면 좀더 낫겠지만 그렇더라도 91만여 건을 읽는다는 것은 변함없다. 이때 테이블에 있는 판매일자(sales_date)와 판매월(sales_month) 컬럼을 이용해 조회할 때의 성능을 높여 보자. 판매월별로 데이터를 분할해 놓고 데이터 조회 시 특정 월을 조건으로 걸면 전체 데이터를 뒤져보지 않아도 된다. 만약 2000년 11월에 판매된 특정 상품 판매데이터를 검색한다고 하면, ❶  파티션이 없는 경우:  91만여 건을 모두 뒤져 조건에 맞는 데이터를 걸러낸다. ❷...

 GROUP BY 절과 HAVING 절의 특징    - GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용    - 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.    - GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용불가    - 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행)    - WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.   - HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.   - GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.   - HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.  일부 데이터베이스의 과거 버전에서 GROUP BY 절에 명시된 칼럼의 순서대로 오름차순 정렬을 자동으로 정렬(비공식적인 지원이었음)하는 경우가 있었으나, 원칙적으로 관계형 데이터베이스 환경에서는 ORDER BY 절을 명시해야 데이터 정렬이 수행된다.  ANSI/ISO 기준에서도 데이터 정렬에 대한 내용은 ORDER BY 절에서만 언급되어있지,  GROUP BY 절에는 언급되어 있지 않다. 예) 잘못된 쿼리-수정전 SELECT POSITION                           포지션    ...

*크로스 조인:  한 테이블의 모든 행과 다른 테이블의 모든 행이 결합 * 내부조인: 동등조인  ->동등 조인은 같은지를 테스트하는 내부 조인입니다.  예)   SELECT boys.boy,toys.toy    FROM boys     INNER JOIN       toys   ON boys.toy_id= toys.toy_id; *내부조인: 비동등 조인  ->비동등 조인은 같지 않은 모든 행들을 반환합니다. 예)  SELECT boy.boy,toys.toy   FROM boys   INNER JOIN    toys   ON boys.toy_id<>toys.toy_id   ORDER BY boys.boy; *내부 조인: 자연조인(동등조인과 같은 결과가 발생 할 수 있다) ->자연조인은 두 테이블에 같은 이름의 열이 있을 때만 동작한다.  SELECT boys.boy,toys.toy   FROM boys   NATURAL JOIN    toys;

같은 결과를 내는 여러가지 방법. 1. CREATE TABLE profession (  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,  profession varchar(20) );  INSERT INTO profession(profession)   SELECT profession FROM my_contacts   GROUP BY profession   ORDER BY profession 2.   CREATE TABLE profession AS    SELECT profession FROM my_contacts    GROUP BY profession    ORDER BY profession ALTER TABLE profession ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id); 3. CREATE TABLE profession (  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,  profession varchar(20) )AS  SELECT profession FROM my_contacts  GROUP BY profession  ORDER BY profession