7주차 배운점 느낀점 - KEY, CONSTRAINT, 릴레이션, 데이터 무결성

7주차

 

강사님이 데이터베이스 다룰때 sql을 다닥다닥 칠줄알아야한다고 하셨다. 자주치는 연습을 해야겠다.
데이터베이스를 만들고 테이블 생성, DROP, INSERT와같은 데이터 정의어, 조작어를 배웠다. where, distinct, any 와같은 연산자를 사용해 SELECT를 사용하는 실습을했다.
주로 테이블 키와 관련된 개념을 배웠다.
데이터를 쪼개는 이유는 재사용성, 확장성을 위해서라고 하셨다. 잘 쪼개서 관계 잘 맺어야한다.

 

배운점

 

CREATE

 

primary key 이렇게 하는게 깔끔해서 추천

 

mysql> CREATE table customer(
    -> customer_id varchar(20) not null,
    -> name varchar(20) not null,
    -> age int,
    -> grade varchar(10) not null,
    -> job varchar(30),
    -> point int default 0,
    -> primary key(customer_id));

 create table product(
    -> product_id char(3) not null,
    -> product_name varchar(20),
    -> amount int,
    -> price int,
    -> p_company varchar(20),
    -> primary key(product_id),
    -> check(amount >=0 and amount <= 10000));

 

INSERT

insert into customer
    -> values('apple','정소화',20,'gold','학생',1000);

 

commit 반드시 해줄 것!

 commit;

 

SQL의 데이터 정의 기능

테이블 생성 CREATE, 테이블 구조의 변경 ALTER, 테이블 삭제 DROP

 

CREATE TABLE

 

[ ]의 내용은 생략이 가능
SQL 문은 세미콜론(;)으로 문장의 끝을 표시
SQL 문은 대소문자를 구분하지 않음, 안에 있는 값은 대소문자 구분
UNIQUE 값들이 들어올때 그 속성의 값을 중복되서는 안된다.
PRIMARY KEY 는 UNIQUE + NOT NULL
CONSTRAINT 제약조건

• 테이블을 구성하는 각 속성의 이름, 데이터 타입, 기본 제약 사항 정의
• 기본키 정의
• 대체키 정의
• 외래키 정의
• 데이터 무결성을 위한 제약조건 정의

테이블을 구성하는 각 속성의 데이터 타입을 선택한 후에 널 값 허용
여부와 기본 값 필요 여부를 결정

 

NOT NULL:
속성이 널 값을 허용하지 않음을 의미하는 키워드
예) 고객아이디 VARCHAR(20) NOT NULL

 

DEFAULT:
속성의 기본 값을 지정하는 키워드
예) 적립금 INT DEFAULT 0 – 예) 담당자 VARCHAR(10) DEFAULT ‘서유미’
문자열이나 날짜 데이터는 작은따옴표로 묶어서 표현

 

속성 데이터 타입

 

키의 정의

 

PRIMARY KEY:

 

기본키를 지정하는 키워드
참조 무결성 제약조건 유지를 위한 키
예) PRIMARY KEY(고객아이디) – 예) PRIMARY KEY(주문고객, 주문제품)

 

UNIQUE:

 

대체키를 지정하는 키워드
대체키로 지정된 속성의 값은 유일성을 가지며, 기본키와 달리 널 값이 허용됨
예) UNIQUE(고객이름)
NULL 가능, null허용 안허용에 따라 super키, unique

 

FOREIGN KEY:

 

다른 객체 주소값저장이라 생각하셈, 다른테이블의 primary키 참조
외래키를 지정하는 키워드
외래키가 어떤 테이블의 무슨 속성을 참조하는지
REFERENCES 키워드 다음에 제시
참조 무결성 제약조건 유지를 위해 참조되는 테이블에서
투플 삭제 시 처리 방법을 지정하는 옵션

 

ON DELETE NO ACTION : 투플을 삭제하지 못하게 함
ON DELETE CASCADE : 관련 투플을 함께 삭제함
ON DELETE SET NULL : 관련 투플의 외래키 값을
NULL로 변경함
ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경함

 

 

중복성 최소화 하기 위해!!
ON DELETE NO ACTION : 부서 테이블의 투플을 삭제하지 못하게 함
ON DELETE CASCADE : 사원 테이블에서 홍보부에 근무하는 정소화 사원 튜플도 함께 삭제
ON DELETE SET NULL : 사원 테이블에서 정소화 사원의 소속부서 속성 값을 NULL로 변경
ON DELETE SET DEFAULT : 사원 테이블에서 정소화 사원의 소속부서 속성 값을 기본
값으로 변경

 

데이터 무결성 제약조건의 정의
CHECK
해당 범위안에 데이터만 입력이 가능
테이블에 정확하고 유효한 데이터를 유지하기 위해 특정
속성에 대한 제약조건을 지정
CONSTRAINT 키워드와 함께 고유의 이름을 부여할 수도 있음
예) CHECK(재고량 >= 0 AND 재고량 <= 10000) – 예) CONSTRAINT CHK_CPY CHECK(제조업체 = ‘한빛제과’)
CHK_CPY: 제약조건 이름 지정, 이름 조회하면 빠르게 처리할 수 있다.

 

테이블 변경 : ALTER TABLE 문

 

새로운 속성 추가

 

고객 테이블에 가입날짜 속성을 추가해보자

 alter table customer add joindate date;

 

기존 속성 삭제

alter table customer drop column joindate;

 

만약, 삭제할 속성과 관련된 제약조건이나 참조하는 다른 속성이
존재한다면?
속성 삭제가 수행되지 않음
관련된 제약조건이나 참조하는 다른 속성을 먼저 삭제해야 함

 

제약 조건 추가

 

원래 이름 넣어도 되고 안넣어도 되는데 이름 필수적으로 넣으셈 !!
20세 이상 고객만 가입할 수 있다는 데이터 무결성 제약조건 추가

 

alter table customer add constraint overage check(age>=20);

 

18세 넣으니까 거절당함

 

기존 제약조건의 삭제

alter table customer drop contraint overage;
alter table customer drop check overage;

 

테이블 삭제

 

만약, 삭제할 테이블을 참조하는 테이블이 있다면?
테이블 삭제가 수행되지 않음
관련된 외래키 제약조건을 먼저 삭제해야 함

 drop table delivery;

 

SELECT 문

 

LIKE를 이용한 검색

 

고객 테이블에서 성이 김 씨인 고객의 고객이름, 나이, 등급, 적립금을 검색해보자

 

select name, age, grade, point from customer where name like('김%');

 

집계 함수를 이용한 검색

 

제품 테이블에서 제조업체의 수를 검색해보자
p_company별 갯수 출력

select count(p_company) as "제조업체 수" from product group by p_company;

 

스키마: 데이터 딕셔너리
스키마 정보를 갖고 있고, 메타 데이터라고도 함

 

릴레이션(relation) : 행과 열로 구성된 테이블

튜플들이 모인거,
원소 한 개씩 선택하여 만들어진 것(매핑)으로 이 원소들이 관계(relationship)를 맺음 -> 튜플을 이룬다.

 

관계(relationship)

 

외래키 key

릴레이션 내에서 생성되는 관계 : 릴레이션 내 데이터들의 관계
릴레이션 간에 생성되는 관계 : 릴레이션 간의 관계

 

릴레이션 스키마

 

속성(attribute) : 릴레이션 스키마의 열
도메인(domain) : 속성이 가질 수 있는 값의 집합(유니크, not null.,)
차수(degree) : 속성의 개수

속성 = 컬럼
스키 틀에 실제 값들 : 인스턴스

 

스키마의 표현

 

릴레이션 이름(속성1 : 도메인1, 속성2 : 도메인2, 속성3 : 도메인3 …)
예) 도서 (도서번호, 도서이름, 출판사, 가격) • 도서 (도서번호:integer, 도서이름:char(40), 출판사:char(40), 가격:integer))

 

릴레이션 인스턴스

 

실제값
투플(tuple) : 릴레이션의 행
카디날리티(cardinality) : 투플의 수
→ 투플이 가지는 속성의 개수는 릴레이션 스키마의 차수와 동일하고, 릴레이션 내의 모든 투플들은 서로 중복되지 않아야 함

릴레이션 특징

• 속성은 단일 값을 가진다
  각 속성의 값은 도메인에 정의된 값만을 가지며 그 값은 모두 단일 값이여야 함/ 동 호수입력 정해져있다. , 남 녀 입력,
• 속성은 서로 다른 이름을 가진다
  속성은 한 릴레이션에서 서로 다른 이름을 가져야만 함.
• 한 속성의 값은 모두 같은 도메인 값을 가진다
  한 속성에 속한 열은 모두 그 속성에서 정의한 도메인 값만 가질 수 있음. n 속성의 순서는 상관없다
• 속성의 순서가 달라도 릴레이션 스키마는 같음.
  예) 릴레이션 스키마에서 (이름, 주소) 순으로 속성을 표시하거나 (주소, 이름) 순으로 표시하여도 상관없음.
• 릴레이션 내의 중복된 투플은 허용하지 않는다
  하나의 릴레이션 인스턴스 내에서는 서로 중복된 값을 가질 수 없음. 즉 모든 투플은 서로 값이 달라야 함. primary key
• 투플의 순서는 상관없다
  독립적인 매핑 구조이기때문에 순서가 상관 x
  투플의 순서가 달라도 같은 릴레이션임. 관계 데이터 모델의 투플은 실제적인 값을 가지고 있으며 이 값은 시간이 지남에 따라 데이터의 삭제, 수정, 삽입에 따라 순서가 바뀔 수 있음.

릴레이션 특징에 위배

개체 무결성 위반: 동일한 튜플 중복

관계 데이터 모델

 

관계 데이터 모델은 데이터를 2차원 테이블 형태인 릴레이션으로 표현함. 릴레이션에 대한 제약조건(constraints)과 관계 연산을 위한 관계대수(relational algebra)를 정의함

제약조건: primary key, unique, default, not null ..
관계대수: crud

릴레이션은 릴레이션 스키마와 릴레이션 인스턴스로 구성된다.
릴레이션 스키마를 릴레이션 내연(Relation Intension)이라고 한다.
릴레이션 인스턴스 외연.
릴레이션의 스키마는 정적인 성질을 가진다.
릴레이션 인스턴스는 동적인 성질을 가진다

 

무결성 제약조건

데이터베이스는 데이터 저장 시
일관성을 유지
중복 최소화
데이터의 신뢰성을 유지한다.

데이터의 삽입, 삭제, 수정 시 (안전장치)여러가지 제약조건이 따른다.

 

키(속성)

특정 튜플을 식별(Primary key)
키 릴레이션 간의 관계 맺는데 사용 (Foreign key)

특정 투플을 식별할 때 사용하는 속성 혹은 속성의 집합
릴레이션은 중복된 투플을 허용하지 않기 때문에 각각의 투플에 포함된 속성들 중 어느 하나(혹은 하나 이상)는 값이 달라야 함. 즉 키가 되는 속성(혹은 속성의 집합)은 반드시 값이 달라서 투플들을 서로 구별할 수 있어야 함
키는 릴레이션 간의 관계를 맺는 데도 사용

 

슈퍼키
투플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합
투플을 유일하게 식별할 수 있는 값이면 모두 슈퍼키가 될 수 있음
=> 튜플마다 다른거!!

(고객 릴레이션 예)

• 고객번호 : 고객별로 유일한 값이 부여되어 있기 때문에 투플을 식별할 수 있음.
• 이름 : 동명이인이 있을 경우 투플을 유일하게 식별할 수 없음.
• 주민번호 : 개인별로 유일한 값이 부여되어 있기 때문에 투플을 식별할 수 있음.
• 주소 : 가족끼리는 같은 정보를 사용하므로 투플을 식별할 수 없음.
• 핸드폰 : 한 사람이 여러 개의 핸드폰을 사용할 수 있고 반대로 핸드폰을 사용하지 않는 사람이 있을 수 있기 때문에 투플을

고객 릴레이션은 고객번호와 주민번호를 포함한 모든 속성의 집합이 슈퍼키가 됨. => 주민번호 땜에 다 묶을 수 있다.
EX) (주민번호), (주민번호, 이름), (주민번호, 이름, 주소), (주민번호, 이름, 핸드폰),
(고객번호), (고객번호, 이름, 주소), (고객번호, 이름, 주민번호, 주소, 핸드폰) 등

 

후보키
투플을 유일하게 식별할 수 있는 속성의 최소 집합
(주문 릴레이션 예)

• 고객번호 : 한 명의 고객이 여러 권의 도서를 구입할 수 있으므로 후보키가 될 수 없음. 고객번호가 1인 박지성 고객은 세 번의 주문 기록이 있으므로 투플을 유일하게 식별할 수 없음.
• 도서번호 : 도서번호가 2인 ‘축구아는 여자’는 두 번의 주문 기록이 있으므로 투플을 유일하게 식별할 수 없음.
  한명은 고객은 여러번 주문할 수 있다. (다 대 1관계)
  => 도서번호와 고객번호를 묶어!! // 같은사람이 같은거 주문안한다는 제약

두 개 조합하면 후보키가 될 수 있다.
주문 릴레이션의 후보키는 2개의 속성을 합한 (고객번호, 도서번호)가 됨. 참고로 이렇게 2개 이상의 속성으로 이루어진 키를 복합키(composite key)라고 함

같은사람이 같은거 주문한다는 제약이 없으면 고객번호와 도서번호가 후보키가 될수없다. => 조건에 따라 키는 변경될수 있다.

 

기본키
여러 후보키 중 하나를 선정하여 대표로 삼는 키
후보키가 하나뿐이라면 그 후보키를 기본키로 사용하면 되고 여러 개라면 릴레이션의 특성을 반영하여 하나를 선택하면 됨

• 릴레이션 내 투플을 식별할 수 있는 고유한 값을 가져야 함.
• NULL 값은 허용하지 않음.
• 키 값의 변동이 일어나지 않아야 함.
• 최대한 적은 수의 속성을 가진 것이라야 함.
• 향후 키를 사용하는 데 있어서 문제 발생 소지가 없어야 함.

릴레이션 스키마를 표현할 때 기본키는 밑줄을 그어 표시함
릴레이션 이름(속성1, 속성2, …. 속성N)
EX) 고객(고객번호, 이름, 주민번호, 주소, 핸드폰)
도서(도서번호, 도서이름, 출판사, 가격)

 

대리키

 

기본키가 보안을 요하거나, 여러 개의 속성으로 구성되어 복잡하거나, 마땅한 기본키가 없을 때는 일련번호 같은 가상의 속성을 만들어 기본키로 삼는 경우가 있음. 이러한 키를 대리키(surrogate key) 혹은 인조키(artificial key)라고 함.
대리키는 DBMS나 관련 소프트웨어에서 임의로 생성하는 값으로 사용자가 직관적으로 그 값의 의미를 알 수 없음.
성능향상을 위해 운영되는키, 꼭 없어도 됨

주문번호: 대리키

대체키
대체키(alternate key)는 기본키로 선정되지 않은 후보키를 말함.
고객 릴레이션의 경우 고객번호와 주민번호 중 고객번호를 기본키로 정하면 주민번호가 대체키가 됨.
기본키로 될 수 있는 가능성이 있다.

 

외래키
다른 릴레이션의 기본키를 참조하는 속성을 말함. 다른 릴레이션의 기본키를 참조하여 관계 데이터 모델의 특징인 릴레이션 간의 관계(relationship)를 표현함.
관계 데이터 모델의 릴레이션 간의 관계를 표현함.

• 다른 릴레이션의 기본키를 참조하는 속성임.
• 참조하고(외래키) 참조되는(기본키) 양쪽 릴레이션의 도메인은 서로 같아야 함.
• 참조되는(기본키) 값이 변경되면 참조하는(외래키) 값도 변경됨.
• NULL 값과 중복 값 등이 허용됨.
• 자기 자신의 기본키를 참조하는 외래키도 가능함.
• 외래키가 기본키의 일부가 될 수 있음.
  

  

외래키 사용 시 참조하는 릴레이션과 참조되는 릴레이션이 꼭 다른 릴레이션일 필요는 없음. 즉 자기 자신의 기본키를 참조할 수도 있음.
자기 자신정보 알고싶으면 자기 자신 join해줘야함

키 포함관계
!! 중요

데이터 무결성(integrity, 無缺性)
• 데이터베이스에 저장된 데이터의 일관성과 정확성을 지키는 것을 말함.

 

1.도메인 무결성 제약조건
• 도메인 제약(domain constraint)이라고도 하며, 릴레이션 내의 투플들이 각 속성의 도메인에 지정된 값만을 가져야 한다는 조건
• SQL 문에서 데이터 형식(type), 널(null/not null), 기본 값(default), 체크(check) 등을 사용하여 지정할 수 있음.

 

2.개체 무결성 제약조건
기본키로 기억하셈
• 기본키 제약(primary key constraint)이라고도 함.
• 릴레이션은 기본키를 지정하고 그에 따른 무결성 원칙 즉, 기본키는 NULL 값을 가져서는 안 되며 릴레이션 내에 오직 하나의 값만 존재해야 한다는 조건임.

 

3.참조 무결성 제약조건
• 외래키 제약(foreign key constraint)이라고도 하며, 릴레이션 간의 참조 관계를 선언하는 제약조건
• 자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 도메인이 동일해야 하며, 자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다는 것

 

UNIQUE 제약조건(unique constraint, 유일성 제약조건, 고유성 제약조건)
실제 DBMS에서는 위에서 설명한 세 가지 무결성 제약조건과 함께 UNIQUE 제약조건도 사용
UNIQUE 제약조건은 속성의 모든 값은 서로 같은 값이 없어야 한다는 것
이는 릴레이션 내의 각각의 투플을 유일하게 식별할 수 있는 속성들의 집합으로 볼 수 있음
UNIQUE 제약조건은 기본키 제약과는 달리 NULL 값을 허용

 

1. 1
2. 1
3. (1)
   R: A // 구별할 수있는 최소 컬럼갯수 1개 : A - 바로 기본키 될 수 있음
   S: CD, CE // 구별할 수 있는 최소 컬럼개수 2개 : 기본키 둘중 선택해야함
   (2)
   R: A // 기본키
   S: CD // 둘 중 하나 선택 , CE 는 대체키가 됨

sql 파일 파워쉘로 적용시키기

cmd
dir \ -> c:로갈 것
cd datasource\employees
mysql -u root -p
dir
source employees.sql

 

워크밴치 서버 정보

server status

 

워크밴치 주석처리

 -- select emp_no from employees;
 
 /*employees의 데이터베이스를 이용한 쿼리문을
연습하고있씁니다.*/

 

띄어쓰기 있는 테이블 ` 로 묶을 수있다. 가급적 사용하지마.

create table `my Test1`; 

 

원래는 이렇게 데이터베이스 이름.테이블 으로 검색해야함

select * from employees.titles;

 

현재 데이터베이스의 모든 테이블의 정보를 출력하세요

show table status;

 

테이블의 이름만 간단히 보려면

show tables;

 

employees 테이블의 열이 무엇이 있는지 출력하세요

desc employees;

 

employees 테이블로부터 first_name 과 성별을 출력하시오

select first_name as '이름', gender as '성별' from employees

 

글자수

 

MYSQL 8.0 부터 UTF-8로 설정되어있다. 한,영어 글자 수 신경쓸 필요없다.
char(8) 여덟글자하고싶으면 8글자 뭐 고민안해도됨

 

-- 1. sqldb 선택하고, 사용자 중 김경호씨 정보를 출력하세요

USE sqldb;
SELECT * FROM usertbl WHERE name = '김경호';

 

-- 2. 1970년 이후에 출생하고 신장이 182이상인 사람의 아이디와 이름을 조회하세요

SELECT userID, name FROM usertbl WHERE height >=182 AND birthYear >1970;

 

-- 3. 키가 180~183 인 사람의 아이디와 이름과 키를 조회하세요 (비교 연산자와 between 연산자 두가지 모드 작성)

SELECT userID, name, height FROM usertbl WHERE height BETWEEN 180 AND 183;
SELECT userID, name, height FROM usertbl WHERE height >= 180 AND height <183;

 

-- 4. 지역이 경남과 경북인 사람의 정보를 조회하세요 (논리 연산과 in 연산자 사용하여 두가지 모드로 작성)

SELECT * FROM usertbl WHERE addr IN ('경남','경북');
SELECT * FROM usertbl WHERE addr = '경남' OR addr = '경북';

 

-- 5. 이름이 '김'으로 시작하느 사람의 이름과 키를 조회하세요

SELECT name, height FROM usertbl WHERE name LIKE '김%';

 

-- 6. 이름이 종신인 사람의 이름과 키와 지역을 조회하세요

SELECT name, height FROM usertbl WHERE name LIKE '_종신';

 

SubQuery (서브쿼리: 하위 쿼리)

 

쿼리문 안에 또 쿼리문이 들어 있는 것
단일행 서브쿼리: 넘겨오는 값이 하나밖에 없다.

-- 김경호 보다 키가 크거나 같은 사람의 이름과 키를 출력
SELECT name, height FROM usertbl WHERE name = '김경호';
SELECT name, height FROM usertbl WHERE height > 177;

SELECT name, height FROM usertbl
WHERE height > (SELECT height FROM usertbl WHERE name = '김경호');

 

다중행 서브쿼리: 두 개 이상 반환됨 (173, 170)

any = some 연산자 사용
=> 170 이상 다 출력 , any, some 는 서브쿼리 결과중 한가지만 만족해도됨

 

select name, height from usertbl 
where height >= any (select height from usertbl where addr = '경남');

 

all 사용
=> 173이상 출력, all은 모두 만족해야됨

 

select name, height from usertbl 
where height >= all (select height from usertbl where addr = '경남');

 

= 사용
=> 170, 173 출력

select name, height from usertbl 
where height = any (select height from usertbl where addr = '경남');

 

IN 사용
=> 같은의미 170, 173 출력

where height IN (select height from usertbl where addr = '경남');

 

ORDER BY

 

항상 마지막에 나옴
원하는 순서대로 정렬 하여 출력 : ORDER BY
결과물에 대해 영향이 없다. 결과가 출력되는 순서를 조절하는 구문

오름차순: ASC(기본) , 내림차순 : DESC

먼저 가입한 순서대로 회원의 이름과 가입일을 조회

SELECT name, mDate FROM usertbl ORDER BY mDate ASC;

여러 정렬 조건 제시, 키가 큰 순서대로 정렬하되, 만약 키가 같다면 이름 순으로 정렬하여 출력하세요

SELECT name, height FROM usertbl ORDER BY height DESC, name ASC;

 

DISTINCT

 

입사일이 오래된 사원 5명의 사원번호, 입사일을 조회

limit 사용 => 정렬후 5명

select emp_no, hire_date from employees order by hire_date asc limit 5;

 

시작 0 , 끝 5 => limit 5랑 똑같음

limit 0,5;

 

 

회고

 

key 와 관련해서 학교에서도 수업하고, 정보처리기사 공부할때도 봤다. 각각의 개념만 외웠었고 정확히 구분을 하지는 못했다. 두리뭉실하게 알고 있었는데 각각 키들을 구분하고 오늘 개념을 제대로 잡은 듯 하다.

 

본문에 있는 키 포함관계 사진이 도움이 많이 됐다. 식별할 수있는 최소키를 포함해 식별할 수 있는 집합은 슈퍼키이고, 그 중 최소갯수 집합이 후보키이다. 그 중 기본키를 정할 수 있고 그 후 대체키를 정의할 수 있다.

 

이제야 알아서 부끄럽지만 , 지금이나마 제대로 알게되어서 다행이다. 좋은 양질의 데이터베이스 수업이었다.