Head first SQL (mySQL) 정리

  • DB
    • DB생성 : CREATE DATABASE test;
    • DB목록 조회 :  SHOW DATABASES;
    • DB삭제 : DROP DATABASE test;
  • 사용자 관리
    • DB를 사용할 사용자 계정을 추가
    • root 사용자만이 사용자를 관리
    • 사용자 추가 – id:tiger    pw:dbgood
      • create user ‘tiger’@’localhost’ IDENTIFIED BY ‘dbgood’;
      • SELECT host, user FROM user;    /*확인*/
    • 사용자 제거
      • DROP USER ‘tiger’@’localhost”;
  • 사용자 권한 관리
    • 사용자가 특정 DB를 사용할 수 있도록 권한 부여
    • 권한 추가 – id:tiger   대상DB : test
      • GRANT ALL ON test.* TO ‘tiger’@’localhost’ WITH GRANT OPTION;
    • 권한 제거
      • REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘tiger’@’localhost’;
      • FLUSH PRIVILEGES;    /*권한 변경 후 바로 적용*/
  • 테이블 만들기
    • 테이블 생성
      • 먼저 test데이터베이스 사용한다 명시해야함
      • use test;
      • CREATE TABLE user(
      • id CHAR(10) NOT NULL DEFAULT id,  //null값이 들어가면 에러남. 디폴트값에 id넣어줌
      • name CHAR(20),
      • passwd CHAR(10)
      • );
    • 테이블 조회
      • SHOW TABLES;
      • DESC user;      /*describe. */
      • SHOW CREATE TABLE user;
    •  테이블 삭제
      • DROP TABLE user;
    • 데이터 삽입
      • INSERT INTO user(id, name, passwd) VALUES(‘lee’, ‘leegood’, ‘dbgood’);
    • 데이터 확인
      • SELECT * FROM user;
      • SELECT drink_name, main FROM easy_drinks;
    • SHOW
      • SHOW COLUMNS FROM tablename;
        • 테이블의 모든 열 표시하고 데이터타입도.
      • SHOW CREATE DATABASE databasename;
        • SHOW CREATE TABLE처럼 데이터베이스를 생성하는 명령을 얻을 수 있다.
      • SHOW INDEX FROM tablename;
        • 인덱싱되어있는 열과 무슨 타입의 인덱스를 가지고 있는지를 표시.
      • SHOW WARNINGS;
        • 실제 경고메세지를 확인
  • 기타
    • 뒤에  N

 

<4. SQL-데이터 생성>

 

학습목표

  • 데이터 생성을 위한 CREATE TABLE관련 SQL을 알고 사용.
    • CREATE TABLE, 제약조건, KEY, UNIQUE
  • 데이터 생성을 위한 CREATE TABLE관련 SQL을 알고 사용
    • 단건, 대량건

CREATE TABLE

  • 테이블 만들기
    • CREATE TABLE user(
    •     id INT,
    •     passwd CHAR(10),
    •     registry_date DATE,
    •     access_hours DEC(8,1)    //정수부 4, 소수부 1자리를 갖는 5자리 숫자
    • );
  • 생성한 테이블 정보 조회하기
    • DESC user;
    • 아니면
    • SHOW CREATE TABLE tablename;

 

INSERT INTO

  • 데이터 삽입 명령
    • INSERT INTO book(id, title, publication, inventory)
    •     values(100, ‘head first SQL”, ‘한빛미디어’, 3);
    • 혹은
    • INSERT INTO book values(100, ‘head first SQL”, ‘한빛미디어’, 3);

테이블의 제약조건

  • NOT NULL : 속성값이 반드시 값이 존재해야함을 명시
    • NULL값의 의미는 정의불가(프로그래밍 언어에서 널과 다름)
  • PRIMARY KEY(id) : 레코드값 중복되지 않고 유일해야 하도록 제약
  • UNIQUE(title) : Primary key가 아닌데 유일해야 하는 속성값
  • DEFAULT 0 : 값이 입력되지 않았을 때 기본적으로 값을 0으로 함.

데이터 타입

  • MySQL 데이터 타입
    • CHAR(m) : 고정길이 문자열, m=1~255
    • VARCHAR(m) : 가변길이 문자열. m=1~255. CHAR보다 검색속도가 느리지만 공간 효율적
    • 수: TINYINT, SMALLINT, INT, UNSIGNED INT, FLOAT, DOUBLE, DECIMAL(=NUMERIC, DEC)
    • 시간 : DATE, TIME, YEAR, TIMESTAMP, DATETIME
      • TIMESTAMP: 값 미입력시 자동으로 현재시간 입력됨
    • 대용량
      • tinyBLOB/tinyText : 최대길이 255개의 문자 저장
      • BLOB/ Text : 최대길이 65535
      • midiumBLOB/mediumText : 16777215
      • LongBLOB/ LongText : 4294967295(4GB)
    • 이미지 데이터 저장, 조회
      • DROP TABLE IF EXISTS blob_table;
      • CREATE TABLE blog_table)
      •   id INT PRIMARY KEY AUTO_INCREMENT,
      •   file VARCHAR(64),
      •   img_blob MEDIUMBLOB
      • );
      • INSERT INTO blob_table(img_blob, file) values(load_file(“C://test.jpg”), ‘test.jpg’);
      • SELECT * FROM blob_table;    //BLOB데이터는 이렇게 보면 안대용!
      • SELECT img_blob INTO DUMPFILE ‘c://dump.jpg’ FROM blob_table WHERE file=’test.jpg’;
    • 대량 데이터 생성
      • 생성, 입력해야할 데이터가 수천건이 넘어도 모두 INSERT INTO 로 생성해야하나요?
        • 가능은 하지만 그렇게하진 않아요.
      • 다른 db나 텍스트 파일 형태로 있는 대량 데이터를 MySQL로 대량입력할 수 없나요?
        • 파일로 저장된 대량 데이터 입력가능, DB간 데이터 입력도 가능.
    • 형식 맞아야 한다.
      • 작은따옴표 사용: CHAR, VARCHAR, DATE, DATETIME, TIME, TIMESTAMP, BLOB
      • 안사용 : DEC, INT
      • 작은따옴표 붙이려면 백슬래시 사용.
        • INSERT INTO hi VALUES(‘Jay Jin\’s Blog);

SELECT

  • 많은 앤 중에 앤찾기!
    • 전체 테이블을 힘들게 찾지 말고 셀렉트문으로 찾아라.
    • SELECT * FROM my_contacts WHERE first_name=’Anne’;
    • SELECT doughnut, rating FROM my_contacts WHERE first_name=’Anne’;
  • 쿼리들의 결합
    • SELECT location FROM doughnut_ratings WHERE type=’plain glazed’ AND rating>5;
    • OR도 사용가능.
  • NULL찾기
    • SELECT drink_name FROM drink_info WHERE calories IS NULL;
  • 문자 일부로 검색
    • SELECT * FROM my_contacts WHERE location LIKE ‘%CA’;
      • 퍼센트 기호를 작은따옴표 안에 : location열이 CA로 끝나는 모든 값을 찾기
      • ‘_im’ : _은 단 하나의 불특정 문자를 대신하는 문자
  • BETWEEN
    • SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 AND 60;
  • IN
    • SELECT date_name FROM note WHERE rating=’good’ OR rating=’fabulous’ OR…;
    • 이걸 IN이란 키워드를 사용. 열의 값이 괄호안의 값들 중 하나와 일치하면 그 행 또는 지정된 열들이 반환.
    • …FROM note WHERE raiting IN (‘good’, ‘fabulous’, delightful’);
    • NOT IN : IN이 표시하는 집합과 일치하지 않는 행을 반환.
  • NOT
    • BETWEEN이나 LIKE와 함께도 사용가능.
      • 단, NOT이 WHERE바로 다음에 와야한다.
    • NOT IN, WHERE NOT, AND NOT…

 

DELETE

  • DELETE FROM clown_info WHERE activities = ‘dancing’;
    • WHERE조건에 맞는 모든 레코드가 테이블에서 지워짐
    • DELETE FROM your_table
      • 테이블의 모든 행을 지운다!
  • INSERT-DELETE
    • SELECT * FROM clown_info WHERE activities=’dancing’;
    • INSERT INTO clown_info VALUES(‘Zippo’,ddddd);
    • DELETE FROM clown_info WHERE activities = ‘dancing’;

UPDATE

  • WHERE조건을 만족하는 투플의 특정 속성값을 수정
    • *주의: WHERE생략시 테이블의 모든 투플의 속성값 수정
  • UPDATE doughnut SET type=’glaze’ WHERE type=’plain glazed’;
  • UPDATE doughnut SET first_col=’new’, second-col=’new2′;
  • UPDATE drink_info SET cost=cost+1 WHERE drink_name=’blue moon’ OF drink_name=’oh’;
  • UPDATE my_contacts SET state = RIGHT(location, 2);
    • state라는 새로운 열에 location열에서 두 문자를 추출해서 집어넣음

CASE

  • 기존 열의 값과 조건을 비교하여 UPDATE문을 합치긔
    • UPDATE my_table SET new_column =
    • CASE
    •   WHEN column1 = somevalue1
    •     THEN newvalue1
    •   WHEN column2 = somevalue2
    •     THEN newvalue2
    •   ELSE newvalue3
    • END;

문자 함수

  • SUBSTRING_INDEX(interests, ‘,’, 2) = ‘animals’;
    • 두 번째 콤마를 찾아서 그 앞의 모든 걸 반환
  • SUBSTR(interests, LENGTH(interest1)+2);
    • 문자열을 가져다 괄호안에 명시한 일부를 자르고 나머지를 반환
    • interest1에 있는 문자+2(콤마랑 공백) 해서 잘라서 버려버림.
  • 마지막 두 문자 추출
    • RIGHT()와 LEFT()를 사용해 열로부터 정해진 수의 문자들 선택
    • SELECT RIGHT(location, 2) FROM my_contacts;
  • 콤마 앞의 모든 문자 추출
    • 특정 문자나 문자열 앞의 모든 문자열을 반환
    • SELECT SUBSTRING_INDEX(location, ‘,’) FROM my_contacts;
  • SELECT SUBSTRING(your_string, start_pos, length)
    • sart_post에 있는 문자에서부터 시작해서 your_string의 일부를 반환. length는 반환되는 문자열의 길이.
  • SELECT UPPER(‘uSa’);
    • 문자열 uSa를 모두 대문자로 변환(<–>LOWER)
  • SELECT REVERSE(‘love’);
    • 문자열 순서 역순으로
  • SELECT LTRIM(‘    dogfood’);
    • 문자열의 앞에 있는 공백문자들을 제거한 문자열반환(<–>RTRIM)
  • SELECT LENGTH(‘san Antonio’);
    • 문자수 반환

ALTER

  • ex)
    • ALTER TABLE my_contacts
    • ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
    • DROP COLUMN start_date,
    • ADD PRIMARY KEY(id);
  • 키워드
    • FIRST: 모든 열 앞에 ㅇㅇ를 추가
    • LAST: 모든 열 뒤에 ㅇㅇ를 추가
    • FIFTH: 위치지정가능.
    • BEFORE last_name : last_name열 전에 추가.
  • 테이블 이름 바꾸기
    • ALTER TABLE projekts RENAME TO project;
  • CHANGE COLUMN
    • 이름과 데이터타입 모두 변경
    • ALTER TABLE project CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
    • ADD PRIMARY KEY(proj_id);
    • number열을 새 이름 proj_id로 변경하고 AUTO_INCREMENT, 그리고 기본키로 설정
  • 하나의 SQL문으로 두개열 변경
    • ALTER TABLE project_list
    • CHANGE COLUMN dee proj_desc VARCHAR(100),
    • CHANGE COLUMN coo con_name VARCHAR(30);
  • MODIFY
    • ALTER TABLE project_list MODIFY COLUMN prom_desc VARCHAR(120);
    • 열의 데이터 타입만 바꾸고 이름은 그대로 사용.
  • 열 제거
    • ALTER TABLE project DROP COLUMN start_date;
  • 기본키 없애기
    • ALTER TABLE table DROP PRIMARY KEY;

ORDER BY

  • 알파벳순 정렬
    • SELECT title, category, purchased
    • FROM movie_table
    • WHERE category=’family’
    • ORDER BY title, purchased DESC //DESC: 역순으로 정렬
    • LIMIT 2; //결과를 2개로 제한

연산

  • SUM
    • SELECT SUM(sales) FROM cookie_sales
    • WHERE name=’Nicole’;
  • 그룹합
    • SELECT name, SUM(sales) FROM cookie_sales
    • GROUP BY name ORDER BY SUM(sales) DESC;    //304쪽 참고
  • 기타
    • MAX(sales)   MIN(sales)
    • COUNT(sale_date)    //sale date열의 행의 수를 반환
    • AVG(salary)    //평균연봉

DISTINCT

  • ex)
    • SELECT DISTINCT sale_date
    • FROM cookie_sales
    • ORDER BY sale_date;
  • ex)
    • SELECT COUNT (DISTINCT sale_date)
    • FROM cookie_sales;

LIMIT

 

  • 오잉

AS

 

  • 새로 만드는 테이블을 SELECT의 결과로 채운다.
    • CREATE TABLE profession
    • (
    •   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    •   profession VARCHAR(20)
    • ) AS                 //SELECT의 결과를 모아 새 테이블에 넣는다.
    •   SELECT profession FROM my_contacts
    •   GROUP BY profession
    •   ORDER BY profession;
  • 별명 만들기
    • CREATE TABLE profession
    • (
    •   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    •   profession VARCHAR(20)
    • ) AS                 //SELECT의 결과를 모아 새 테이블에 넣는다.
    •   SELECT profession AS mc_prof FROM my_contacts AS ac       //AS생략가능.
    •   GROUP BY mc_prof
    •   ORDER BY mc_prof;

카티젼 조인

  • 한번에 두 테이블에 쿼리 수행. toys테이블에서 toy열과, boys테이블에서 boy열 가져오는 경우 결과값을 카티젼 결과값이라 한다.
    • SELECT t.toy, b.boy
    • FROM toys AS t CROSS JOIN boys AS b;

내부 조인

  • 조건을 사용하여 두 테이블의 레코드를 결합
  • 결합된 행들이 조건을 만족할 경우에만 열들이 반환.
    • SELECT somecolumns FROM table1
    •   INNER JOIN table 2                     //INNER안써도 됨.
    • ON somecondition;

내부조인-동등 조인

  • 같은지를 테스트하는 내부 조인
    • SELECT boys.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 INNER JOIN toys
    • ON boys.toy_id <> toys.toy_id
    • ORDER BY boys.boy;

내부조인-자연조인

  • 두 테이블에 같은 이름의 열이 있을때만 동작
    • SELECT boys.boy, toys.toy
    • FROM boys NATURAL JOIN toys;

서브 쿼리(내부쿼리)

  • 다른 쿼리에 쌓여진 쿼리
  • 비상관 서브쿼리
    • 내부쿼리 수행하고 그 값을 사용해서 외부쿼리 결과 알아냄
    • 서브쿼리가 외부쿼리의 어떤 것도 참조하지 않을때.
    • IN이나 NOT IN을 사용하여 값이 서브 쿼리에서 반환된 집합의 원소인지(아닌지)를 확인
  • 상관 서브쿼리
    • 내부 쿼리의 값이 결정되는데 외부 쿼리에 의존
  • 응답속도
    • 크로스조인, 상관서브쿼리는 느린 편
    • 조인이 서브쿼리보단 빠름

 

<10. 외부 조인, 셀프 조인, 그리고 유니온>

 

외부 조인

  • 조인되는 테이블 중 하나의 모든 행을 다른 테이블에서 일치하는 정보와 함께 반환.
  • 두 테이블 사이의 관계와 좀더 관련.
  • 왼쪽 외부조인
    • 왼쪽 테이블의 모든 행을 가져다 오른쪽테이블의 행과 비교.
    • 왼쪽 테이블과 오른쪽 테이블이 일대다 관계에 있을때 유용.
    • ex
      • SELECT g.girl, t.toy
      • FROM girls g
      • LEFT OUTER JOIN toys t
      • ON g.toy_id = t.toy_id;
    • 다른 테이블과 일치하는 것이 있는가에 상관없이 행을 반환.
      • 일치하는게 없으면 NULL
      • 왼쪽 외부조인의 결과가 NULL값이면 오른쪽 테이블에 왼쪽테이블에 상응하는 값이 없다는 의미.
Advertisements

Database basic with mySQL 강의정리

  • SW에서 데이터 중요해?
    • 객체지향 프로그래밍의 중심은 객체(데이터)
    • SW성능의 핵심은 데이터의 관리 성능
    • SW품질은 데이터 관리에 큰 영향 받음
  • 용어
    • 데이터 : 현실세계에서 관찰하거나 측정한 사실 또는 값
    • 정보 : 의사결정에 활용하기 위해 데이터를 처리한 결과물
    • 데이터베이스 : 데이터의 모임
      • 특정 조직의 다수 사용자가 공유하여 사용하도록 통합해서 저장한 운영 데이터의 집합
      • 특징
        • 자기 기술성 : DB 시스템이 데이터베이스 자체와 구조 및 제약조건 정의(메타데이터)를 가지고 있음
        • 프로그램과 데이터의 분리 : 프로그램-데이터 독립성
        • 데이터 추상화 : 데이터 모델을 사용하여 자세한 내용은 은닉하고 개념적 뷰만을 제공
        • 데이터에 대한 다중 뷰의 제공 : 관심있는 일부 데이터를 뷰로 정리 가능
        • 데이터의 공유와 다수 사용자 트랜잭션 처리
          • 다수 사용자용 DBMS
          • 트랜잭션 : DB작업의 수행 단위, ACID성질(시험이나 면접에 많이 나와)
    • DBMS : DataBase Management System
      • DB의 생성과 관리를 담당하는 소프트웨어 패키지
      • DBMS는 운영체제와 함께 중요한 시스템 소프트웨어 패키지로 분류
      • Oracle, IBM DB2, Sybase, MS-SQL, MySQL
      • 등장배경 – 파일 시스템의 문제점
        • 데이터 중복성 : 저장공간 낭비 및 데이터의 일관성과 무결성 유지 어려움
        • 데이터 종속성 : 사용하는 파일 구조를 변경하면 프로그램도 같이 변경 필요
    • 데이터베이스 시스템 : 데이터베이스 자체 + 이를 관리하는 SW(DBMS+응용프로그램)통칭
      • DBMS와 혼용하여 많이 사용
    • 메타데이터 : 데이터에 대한 데이터

 

 

<3. Database system 개념과 아키텍쳐>

 

학습목표

  • 데이터베이스 시스템의 구조와 용어들 공부하끠

데이터 모델

  • 데이터 모델
    • 데이터 추상화를 위한 도구
    • DB의 구조(데이터의 타입, 관계, 제약조건..)를 명시하기 위해 사용하는 개념들의 집합
    • 데이터의 생성, 검색, 수정, 삭제를 수행하는 연산들을 포함
  • 분류
    • 고수준 데이터 모델
      • 일반 사용자들이 이해하는 데이터 표현
      • HW독립적 ER모델 (컴 사양이 어떤지는 상관없음)
    • 표현(구현)데이터 모델
      • 일반사용자도 이해하고 컴퓨터상에 구현도 가능
      • DBMS에서 많이 사용(관계 데.모, 계층 데.모, 네트워크 데.모 모델)
    • 저수준(물리적)데이터 모델
      • 데이터가 컴에 저장되는 방식을 표현
      • HW의존적
      • 레코드 형식, 순서, 인덱스 등 (이것들 일부를 이해하는게 이 강의의 목표)
  • 데이터베이스 스키마
    • Schema : 대략적 계획, 도식…
    • DB자체에 대한 기술.
    • 자료를 저장하는 구조와 표현법을 정의. 우리가 사용하는 DB가 어떤 모습인지
  • 데이터베이스 상태
    • 특정 시점의 DB의 데이터들의 집합.
    • instance들의 집합이라고도 함.

3단계 스키마 아키텍쳐와 데이터 독립성

  • 구조
    • 내부단계–내부 스키마–물리적 저장구조 기술
    • –(물리적 데이터 독립성)–
    • 개념단계–개념 스키마–데이터베이스 구조 기술
    • –(논리적 데이터 독립성)–
    • 외부단계–외부 뷰1, 외부 뷰2…–사용자에게 데이터베이스가 보여지는 구조 기술
  • 논리적 데이터 독립성
    • 외부 스키마나 응용프로그램을 변경하지 않으면서도 개념 스키마를 변경할 수 있는 능력(성질)
  • 물리적 데이터 독립성
    • 개념 스키마를 변경하지 않으면서 내부 스키마를 변경할 수 있는 능력(성질)
    • 인덱스 구조 등의 변경에도 개념 스키마(ex-테이블구조)는 영향을 받지 않는 능력

데이터베이스 언어

  • 데이터 정의어(DDL: data definition lang)
    • 개념 스키마와 내부 스키마를 정의
    • CREATE TABLE, DROP INDEX
  • 데이터 조작어(DML: data manipulation lang)
    • 데이터를 검색, 삽입, 삭제, 수정하기 위한 조작 언어
    • 대표적 언어: SQL
  • 데이터 관리어(DCL: data control lang)
    • DB관리를 위해 사용하는 언어
    • backup, grant

 

 

 

<5. 관계형 데이터베이스 개념>

학습목표

  • 관계형 데이터베이스의 기본 요소와 관계형 데이터 모델을 이해, 데이터베이스 시스템 사용시 관련 개념 활용가능
  • 관계형 데이터 모델의 제약조건을 이해하고 데이터베이스 시스템 사용시 관련 개넘을 활용가능

관계 데이터 모델의 개념

  • 1970년에 Ted Codd.가 처음 소개
  • 오라클의 Oracle, MySQL, IBM의 DB2, MS의 SQL Server, Access
  • 용어
    • 테이블->릴레이션      행->투플        열->애트리뷰트
    • 도메인 : (애트리뷰트가 가질 수 있는) 원자값들의 집합
      • ex) 전화번호->11자리 번호들의 집합, Names : 개인 이름들의 집합.
    • 데이터 타입 : 도메인은 실제 데이터 타입으로 명시함
      • ex) string, integer, real
  • 릴레이션 스키마
    • 릴레이션 이름 R과 애트리뷰트 A들의 집합으로 R(A1, A2…An)로 표기
    • 릴레이션 스키마 R(A1, A2…An)의 투플 t
      • 값들의 (순서화된) 집합 t=<V1, V2…Vn>
    • 릴레이션 스키마 R에 대한 릴레이션(또는 릴레이션 인스턴스) r 또는 r(R)
      • 투플의 집합; r=r(R)={t1, t2…tm)
      • r(R)은 실세계의 특정 상태를 반영

관계 데이터 모델의 제약조건

  • 제약 조건 : 모든 릴레이션 인스턴스들이 만족해야하는 조건
    • a.k.a 데이터 무결성 제약조건
  • 데이터 모델 스키마에서 DDL을 통해 직접 표현 가능한 제약조건
    • 도메인 제약조건
      • 각 애트리뷰트 A의 값은 반드시 A의 도메인 dom(A)에 속하는 원자값이어야 함
    • 키 제약조건
      • 릴레이션은 투플의 집합으로 정의되므로, 모든 원소는 중복이 안됨
      • 어떤 두 투플도 릴레이션의 모든 애트리뷰트에 대해 같은 값들의 조합을 가질 수 없음
        • 슈퍼키 : 릴레이션 r의 투플들을 고유 식별 가능한 애트리뷰트의 집합
        • 키: 최소성을 만족하는 슈퍼키
        • 기본키 : 릴레이션의 여러 키(후보키)중에서 선택한 키
    • 널에 대한 제약조건
      • 애트리뷰트 값으로 널을 허용안하면 널 못가짐
    • 엔티티 무결성 제약조건(==개체 무결성 제약조건)
      • 기본키가 각 투플들을 식별하는 데 이용되기 때문에 어떠한 기본키도 널 값 못가짐
    • 참조 무결성 제약조건
      • 한 릴레이션에 있는 투플이 다른 릴레이션에 있는 투플을 참조하려면 반드시 참조되는 투플이 그 릴레이션 내에 존재해야 함
      • 하나의 릴레이션 R에서 속성 F의 값으로 다른 릴레이션 S의 기본키 P값을 참조하는 경우에 R과 S는 참조 무결성 제약조건을 가진다고 함(F는 null을 가질 수 있음)
        • ->무슨말이지???

 

<4. 좋은 테이블 설계>

용어

  • 원자적이다
    • 데이터가 충분히 쪼개어졌다.
    • 쪼갤 수 없는 가장 작은 조각으로 쪼개졌다.
  • 테이블을 정규화한다
    • 테이블들이 표준 규칙을 따르게 한다.
    • 그러면 새로운 설계자가 테이블을 이해하기 더 쉽다.
      • 이점 : 중복 데이터가 없어서 데이터베이스의 크기 줄여준다, 찾아야 할 데이터가 적어 쿼리가 더 빨라짐.
    • 제 1정규형(1NF)
      • 각 행의 데이터들은 원자적 값을 가져야 한다.
      • 각 행은 유일무이한 식별자인 기본키(primary key)를 가지고 있어야 한다.
    • 기본키
      • 각 레코드를 다른 레코드와 구분하는 열
      • NULL이 될 수 없다
      • 레코드가 삽입될 때 값이 있어야 한다.
      • 간결해야 한다.
      • 변경불가.

 

 

<7. 테이블이 여러개인 데이터베이스 설계>

용어

  • 참조키
    • 테이블의 한 열로 다른 테이블의 기본키를 가리킨다.
    • 연결되는 기본키와 다른 이름일 수도 있다.
    • 한 테이블의 열들이 다른 테이블의 열과 연결되도록 할때 사용.
    • null일 수 있다.
    • 유일할 필요가 없다.
  • 부모키, 부모 테이블
    • 참조키에서 참조하는 기본키
    • 부모키가 있는 테이블
  • 참조 무결성
    • 참조키의 값으로 부모테이블에 존재하는 키의 값만을 넣을 수 있다.
  • 참조키 제약조건
    • 제약조건 추가 안하고 그냥 다른 테이블 참조하며 참조키라고 안 하는 이유
      • 제약조건을 사용하면 부모 테이블에 존재하는 값만을 넣을 수 있다.
      • 두 테이블간의 연결을 강제하는 것.
    • my_contacts의 기본키가 interests테이블의 참조키 값으로 나오면 그 행은?
      • 지울 수 있다. 하지만 먼저 참조키의 행을 지워야 한다.
      • 행이 interest테이블에 남아있으면 느려진다->고아->느리게 한다.
  • 합성키
    • 여러 개의 열들로 구성되어 유일무이한 키를 만드는 기본키

참조키가 있는 테이블 생성

  • CREATE TABLE interests(
  • int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  • interest VARCHAR(50) NOT NULL,
  • contact_id INT NOT NULL,    //참조키
  • CONSTRAINT my_contacts_contact_id_fk    //제약조건. 참조키가 어느 테이블을 참조하는지, 키의 이름, 제약조건이 참조키(fk). 선택적인 라인이다. 사용하면 좋음
  • FOREIGN KEY (contact_id)    //참조키
  • REFERENCES my_contacts(contact_id)    //참조키가 어느 테이블을 참조하는지 표시하고, 다른 테이블의 열 이름을 표시.
  • );

테이블간의 관계

  • 일대일
    • 스키마에서 실선으로 연결
    • 부모 테이블 하나의 행이 자식 테이블 하나의 행과 연결
  • 일대다
    • 연결선: 한쪽끝에 화살표
  • 다대다
    • 여러명의 여자와 여러켤레의 신발
    • 연결 테이블 사용. 연결 테이블은 각 테이블의 키를 가지고 있다.

종속

  • 함수 종속 관계
    • T.x ->; T.y     //T라는 테이블에서 열y는 열 x에 함수적으로 종속된다.
  • 관계
    • 종속되는 열 : 다른 열이 변하면 변경
    • 독립되는 열 : 다른 열에 종속X
  • 부분적 함수 종속
    • 키가 아닌 열이 합성키의 전부가 아닌 일부에 종속되는 경우
  • 이행적 종속 관계
    • 키가 아닌 열이 다른 열의 변경을 초래

제2정규형

 

 

 

<ER모델>

학습목표

  • 데이터 모델링을 이해하고 데이터베이스 설계과정을 설명가능
  • ER데이터 모델링을 이해하고 데이터베이스 설계에 활용가능
  • 개념적 데이터 모델 설계 결과를 이용하여 논리적 데이터모델 설계가능

데이터 모델링

  • 모델링 개념: 요소 + 연산 + 제약조건
    • 구성요소 : 어떤 것들을 대상으로?(relation, tuple, attribute, value)
    • 연산 : 무슨 일들을 어떻게?(CRUD – Create Read Update Delete, insert)
    • 제약조건 : 지켜져야 할 규칙(도메인-, 키-, 널-, 엔티티무결성-, 참조무결성-)
  • 모델링 단계: 개념 – 논리 – 물리 3단계

개체관계(Entity relationship) 데이터 모델

  • 개념적 데이터모델
  • 데이터를 엔티티(개체), 관계, 애트리뷰트(속성)로 모델링함.
    • Entity
      • 실세계에서 독립적으로 존재하는 실체
      • 저장할 가치가 있는 중요 데이터를 가지고 있는 사람/사물/개념/사건
      • 사각형
      • 엔티티 타입 : 같은 애트리뷰트를 갖는 엔티티들의 집합
    • Relationship
      • 엔티티들간의 연관관계
      • 개체와 개체 사이의 의미있는 연관성
      • 관계도 애트리뷰트를 가질 수 있음
      • 마름모
      • 보통 동사형 쓴다.
      • 유형(관계차수-관계에참여하는 개체타입의 수- 기준)
        • 이항관계, 삼항관계, 순환관계
      • 유형(매핑 카디널리티 기준)
        • 매핑 카디널리티: 관계를 맺는 두 개체 집합에서, 각 개체 인스턴스가 연관성을 맺고 있는 상대 개체 집합의 인스턴스 개수
        • 일대일, 일대다, 다대다
    • 엔티티 타입
      • 같은 애트리뷰트를 갖는 엔티티들의 집합
    • Attribute
      • 엔티티를 설명하는 속성
      • 기본키 애트리뷰트: 각 개체 인스턴스를 식별하는데 사용. 밑줄.
      • 분류(값)
        • 단일값 애트리뷰트
        • 다중값 애트리뷰트(동그라미2개)
      • 분류(의미분해 가능성)
        • 단순 애트리뷰트
        • 복합 애트리뷰트(성-이름)
      • 유도 애트리뷰트
        • 기존의 다른 애트리뷰트 값으로부터 유도되어 결정되는 속성(NumOfEmp)
        • 점선으로 표시.
  • 개체-관계 다이어그램(E-R diagram)
    • 개체-관계 모델을 이용해 현실세계를 개념적으로 모델링한 결과물을 그림으로 표현
  • 관계의 참여 특성
    • 전체(필수)참여
      • 모든 개체 인스턴스가 관계에 반드시 참여해야함
      • 모든 고객은 반드시 책을 구매해야 함
      •  ERD에서 이중선으로 표시
    • 부분(선택)참여
      • 개체의 일부만 관계에 참여해도 됨
      • 책 개체의 일부만 고객 개체와의 ‘구매’관계에 참여
  • 약한 엔티티 타입
    • 자신의 키 애트리뷰트가 없는 엔티티 타입
    • 애트리뷰트 중 하나가 다른 엔티티타입(식별 엔티티타입)과 연계됨으로써 식별가능
    • 강한- : 키를 가지는 일반 엔티티타입
    • 이중사각형으로 표시. 약한 엔티티가 식별엔티티와 맺는 관계는 이중마름모로 표현

 

<DB모델링 : ER매핑에 의한 논리 설계>

학습목표

  • 개념적 데이터 모델 설계 결과를 이용하여 논리적 데이터모델을 설계가능

논리적 설계

  • 개념
    • ER다이어그램으로 표현된 개념적 구조를 DB에 저장할 형태로 표현한 논리적 구조
    • 관계 데이터 모델, 계층 데이터 모델, 네트워크 데이터모델 등이 있다.
    • 개념 데이터 모델로부터 논리 데이터 모델로 변환하는 규칙에 따라 논리데이터 모델을 생성
  • 매핑1단계: 정규엔티티->릴레이션
    • 엔티티 타입은 릴레이션으로 매핑
    • 모든 단순 애트리뷰트 포함
    • 엔티티타입의 키 중 하나를 기본키로 지정
  • 매핑2단계: 약한엔티티->릴레이션
    • 약한 엔티티 타입을 릴레이션으로 매핑
    • 모든 단순 애트리뷰트 포함
    • 소유 릴레이션의 키 속성을 포함
    • 생성된 릴레이션의 기본 키는 소유 릴레이션의 키와 약한 엔티티 타입의 부분키를 합쳐서 만든다.
  • 매핑3단계: 1:1관계->외래키
    • 외래키 접근방식
      • 한 릴레이션(S)를 선택하여 T의 기본키를 S의 외래키로 표현. S는 완전참여 릴레이션을 선택하는 것이 좋음
      • 관계타입의 모든 단순 애트리뷰트를 S에 포함시킴
      • 이 방식이 가장 유용. 다른 방식은 참조만 할것
    • 합병 릴레이션 접근방식
      • 두 릴레이션을 하나의 릴레이션으로 통합. 두 릴레이션이 모두 완전참여일때 좋음
    • 교차 참조/관계 릴레이션 접근방식
      • S와 T를 교차참조하는 제 3의 릴레이션 R생성
  • 매핑 4단계: 1:N관계->외래키
    • 외래키 접근 방식(권장)
      • N측의 릴레이션(S)를 선택하여 1측의 릴레이션 T의 기본키를 S의 외래키로 포함
      • 관계타입의 모든 단순 애트리뷰트를 S에 포함시킴
    • 교차 참조/관계 릴레이션 접근방식(권장 X)
      • S와 T를 교차 참조하는 제 3의 릴레이션 R생성
  • 매핑 5단계: M:N관계->릴레이션
    • 별도의 릴레이션(관계 릴레이션이라고 부름)으로 생성하고, 관계에 참여하는 두 릴레이션의 기본 키를 각각 참조하는 외래키로 애트리뷰트를 고성
    • 이 때 두 외래키가 관계 릴레이션의 기본키를 형성
  • 매핑 6단계: 다중키 애트리뷰트->릴레이션
    • 릴레이션 R의 다중키 애트리뷰트는 R의 기본키를 포함하는 새로운 릴레이션으로 매핑된다.
    • 새로운 릴레이션의 키는 R의 기본키와 다중키 애트리뷰트의 조합이다.
  • 매핑 7단계: N차 관계->릴레이션
    • 관계에 참여하는 n개 릴레이션들의 키들로 구성되는 관계 릴레이션으로 매핑된다.
    • 관계 릴레이션의 애트리뷰트들은 참여 릴레이션의 기본키를 참조하는 외래키들과 관계속성(들)으로 구성된다.