가전제품 이것저것 이야기

1. 개체(Entity) : 현실 세계에서 물리적 / 추상적으로 존재하는 실체

2. 개체집합 : 동일한 특성을 갖는 개체들의 모임

ex) 

3. 속성(Attribute) : 개체의 특성, 관계형 데이터 모델의 필드와 같은 개념

** 필드와 속성의 차이점

1) 필드(관계형 데이터 모델) : 원자값만 허용됨

2) 속성(개체관계 모델) : 다중값 속성(하나의 속성이 여러 개의 값을 가질 수 있음), 복합 속성(하나의 속성이 여러 개의 속성으로 구성됨)

다중값 속성 -> family_member 속성에는 여러 가족의 이름이 포함됨

복합 속성 -> address 속성은 세부적으로 (district, city, street) 로 구성됨

4. 관계(relationship)

: 개체 간의 대응성을 표현

5. 관계집합

ex) 소속(affiliated) 이라는 관계로 이어진 집합

1) 관계집합의 속성

: 개체집합에서 정의하는 것보다 두 개체집합의 관계에서 도출되는 속성일 때

ex) 학생이 학과에 소속되기 시작한 날짜 -> '소속' 관계집합의 속성으로 정의

2) 관계집합의 차수

: 관계집합에 참여하는 개체집합의 개수

- 이진관계: 두 개체집합 사이에 정의된 관계집합

ex) 학생 - 학과의 소속관계

- 삼진관계 : 세 개체집합 사이에 정의된 관계집합

ex) 학생 - 과목 - 교수의 수강 / 강의 관계

3) 관계의 대응수(mapping cardinality)

: 관계집합에서 각 개체들이 참여할 수 있는 대응의 개수

ex) 각 학생은 하나의 학과에만 소속될 수 있다. (N : 1)

- 일대일 대응수 (1:1)

: x->y, y->x 함수이다. (x->y 에서 x에 따른 y가 하나로 수렴하기 때문에)

ex) 각 과목(course)의 강좌(class)를 하나씩만 개설할 수 있을 때

- 일대다 대응수 (1:N or N:1)

: y->x 함수이다.

ex) 각 과목(course)에 대해 여러 강좌(class)를 개설할 수 있다고 가정할 때

- 다대다 대응수 (M:N)

: 어느 쪽으로도 함수 관계가 성립하지 않는다. 

ex) 여러 학생과 여러 강좌가 있을 수 있을 때

4) 약성 개체집합 vs 강성 개체집합

- 강성 개체집합 : 기본키 형성에 필요한 모든 속성을 갖는 개체집합

ex) course 개체집합 { course_id, title, credit } -> 기본키 존재

- 약성 개체집합 : 기본키 형성에 필요한 모든 속성을 갖지 못한 개체집합

ex) class 개체집합 { year, semester, division, classroom, enroll }

-> { year, semester, divison } 이 모여도 기본키가 되지 않음, 동일 교과목(course) 내에서만 유일함

-> course 가 존재해야 그것에 대한 class 도 존재 가능하고, 식별 가능함

- 약성 개체집합은 강성 개체집합에 항상 종속된다.

즉, 약성 개체집합은 독립적으로 존재할  수 없으며, 강성 개체집합이 존재해야 존재할 수 있다.

- 식별 관계

: 강성 개체집합와 약성 개체집합 사이의 1:1 또는 1:N 관계 ( 약성 개체집합에서는 꼭 하나의 강성 집합으로 연결이 되어야 함 )

: 약성 개체집합의 모든 개체가 식별 관계에 참여해야 함

: 강성 개체집합의 일부 개체만 식별 관계에 참여함

- 약성 개체집합의 부분키

: 강성 개체집합의 기본키 + 약성 개체집합의 부분키로 구성

ex) course 와 class 예시에서, class 개체집합의 기본키는 (course_id, year, semester, divistion)

5) 부분키

: 약성 개체관계 속성 중 강성 개체집합의 특정 개체에 대해 유일한 값을 갖는 속성들의 집합 ( 구별자 라고도 함 )

ex) course 개체집합과 class 개체집합의 관계에서, { year, semester, division }은 특정 course 에 대해 유일한 값을 갖는 class 속성이다.

6) 일반화와 세분화

: 일반화 - 여러 개체집합의 공통적인 특징을 모아 상위 개체집합을 생성

: 세분화 - 하나의 개체집합을 여러 개의 하위 개체집합으로 분류

6. 개체관계 다이어그램(ERD)

: 개념적 설계에서 개체집합과 관계집합, 속성을 도식화한 것

: 대응 관계에서 '일'인 쪽이 화살표

1) ERD 일반 예시

2) 약성 개체집합 및 식별 관계의 표현

3) 삼진관계 ERD

4) 세 개의 이진관계 ERD

5) 자기연관관계 ERD

- 역할(role) : 관계에 참여하는 두 개체를 구분

ex) 각 직원은 자신을 관리하는 상관이 존재, but 상관도 직원 개체집합에 속함,

각 상관은 여러 명의 직원을 관리할 수 있음

- M:N의 자기연관관계

ex) 한 과목은 여러 개의 선수 과목과 여러 개의 후수 과목을 가진다.

7. ERD 그려보기 실습 1 !

- 주의할 점

: year, semester, enroll에 대한 직접적인 언급이 없었는데, 필요하다고 생각이 되면 추가

: 관계 개체의 속성은 관계 하나당 생기는 속성일 때에만 설정한다.

ERD 그려보기 실습 2 !

- 주의할 점

: 약성 개체집합 설정 시, 관계집합도 약성으로 표시할 것

: 새로운 개체집합 만드는 것을 두려워하지 말 것!

1. 데이터베이스 설계 단계

1) 요구사항 분석

2) 개념적 설계 -> 개념적 스키마 생성

3) 논리적 설계 -> 논리적 스키마 생성

4) 물리적 설계

2. 개념적 설계

- 개념적 스키마 : 데이터베이스에 대한 추상적인 설계도

- 대표적인 개념적 모델 : 개체관계 모델(ERD), ER Schema 라고도 함

3. 논리적 설계 : 관계형 데이터 모델 

- 테이블 : 여러 데이터 도메인의 순서쌍(튜플)들의 집합

- 데이터베이스 : 테이블들의 집합

- 논리적 스키마 : 테이블 구조도

-> 개념적 설계 단계에서 생성된 ERD를 바탕으로 생성되는 테이블들의 집합

4. 물리적 설계

: 필드의 데이터 타입, 인덱스, 테이블 저장 방법 등 정의 (물리적 스키마 설계)

: 논리적 설계 단계에서 생성된 테이블 구조도에 따라 SQL create table 구문으로 각각의 테이블 생성

* 추가 고려 사항

: 인덱스 설정 여부, 기본키/외래키 설정 여부, 뷰 생성 등

5. 설계 과정에서 고려 사항

1) 충실성 : 현실 세계를 충분히 반영

2) 단순성 : 단순하고 이해하기 쉬운 구조로 표현

3) 중복의 최소화 : 저장공간의 효율적 사용, 데이터 일관성 유지

4) 제약조건의 표현

1. 사용자 계정 생성

CREATE USER LEE
IDENTIFED BY LEE_PASS
DEFAULT TABLESPACE USERS
QUOTA 2M ON USERS

: USERS - 오라클 설치 시 기본적으로 생성되는 테이블스페이스

2. SYSTEM 계정에서 LEE 에게 권한 부여

GRANT CREATE SESSION TO LEE;
GRANT CREATE TABLE TO LEE;

: CREATE SESSION - 데이터베이스 접속(로그인) 권한

: CREATE TABLE - 테이블 생성 권한

3. 오라클에서는 with grant option과 wit admin option 을 둘 다 지원한다.

WITH GRANT OPTION : 객체권한(DML)을 다른 사용자에게 전파할 수 있는 권한

WITH ADMIN OPTION : 시스템 권한을 전파할 때 사용

4. 오라클의 기본적인 롤(role) 종류

1) 롤 부여

2. 롤 생성하고 부여하기

3. 사용자로부터 롤을 회수

DBMS는 허가되지 않은 사용자, 즉 권한이 없는 사용자로부터의 데이터 접근을 차단한다.

1. 데이터베이스에서의 사용 권한

: 사용자가 데이터베이스 내의 특정 객체에 대해 특정 연산을 수행할 수 있는 권리

1) 특정 객체 - 테이블, 뷰, 필드, 저장 프로시저 등 데이터베이스의 구성 요소

2)특정 연산 - DML(SELECT, DELETE, INSERT, UPDATE) , DDL(CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX)

2. 권한에 따른 사용자 분류

1) 데이터베이스 관리자(DBA)

2) 객체 소유자(owner) : 생성한 객체에 대한 모든 권한 소유 (해당 객체에 대한 권한 부여나 회수도 가능)

3) 기타 사용자 : 기본적으로는 다른 사용자의 객체에 대한 어떠한 권한도 없음, 다만 객체 소유자나 DBA로부터 권한을 부여받을 수 있음

< SQL에서의 권한 제어 >

3. GRANT

: 권한을 부여하는 명령

1) 형식

<권한리스트> : SELECT, DELETE, UPDATE, INSERT, REFERENCES

2) 예시

3)

- REFERENCE 권한이란?

: 테이블을 외래키로 참조할 수 있는 권한 

: 권한이 없으면 student 테이블은 department 테이블을 참조할 수 없다.

- REFERENCE 권한 부여

: dept_id 는 테이블 department 의 기본키이다. 기본키만을 외래키로 참조할 수 있다.

4) 모든 사용자에게 권한 부여

5) 모든 종류의 권한을 하나의 명령으로 부여하는 방법

6) 부여받은 권한을 다른 사용자에게 전파할 수 있는 옵션

단, chang 은 더 이상 다른 사용자에게 권한을 부여할 수 없음

4. REVOKE

: 다른 사용자에게 부여한 권한을 회수(취소)하기 위한 명령

: 이 경우, kim 뿐만 아니라 권한을 부여한 chang으로부터 자동적으로 권한을 회수함

5. ROLE(롤)

: 권한별로 사용자 그룹을 만들어 그룹에 권한을 부여하는 방법

: 롤은 데이터베이스 관리자만 생성 가능하다.

1) 롤 생성 형식 및 예시

2) 롤 배정 형식 및 예시

3) 롤에 권한 부여 ( GRANT문의 형식과 동일 )

4) 롤에 부여된 권한 회수

5) 배정된 롤에서 사용자 배제

6) 롤 삭제

6. 뷰를 이용한 권한 제어

: 특정 테이블에서 일부 필드 혹은 일부 레코드들에 대해서 접근을 허용할 경우 이 부분들을 뷰로 정의

: kim은 junior 에 대한 접근은 가능하지만, student 테이블 전체에 대한 접근은 불가하다.

1. 무결성 제약(integrity constraint) 또는 무결성 규칙(integrity rule)

: 데이터베이스에 저장된 데이터가 현실 세계에 존재하는 정보들을 모순 없이 반영하는 성질이다.

: 데이터베이스에 저장된 데이터가 갖추어야 할 제약 조건을 항상 만족하도록 보장하는 성질이다.

예시 )

2. 무결성 제약 조건을 충족시키기 위한 방법

1) 데이터를 삽입, 삭제, 수정할 때마다 사용자, 즉 응용 프로그램이 직접 주어진 무결성 제약의 만족 여부를 검증

2) 데이터베이스 설계자가 데이터베이스가 지켜야 할 무결성 제약들을 정의

: DBMS가 자동적으로 제약 조건을 검증하여 무결성을 보장해 줌

3. 무결성 제약의 유형

[ 기본적 무결성 제약 ]

1) 기본키 무결성 제약(primary key integrity constraint)

: 기본키는 null 값을 가질 수 없으며, 기본키의 값이 동일한 레코드가 하나의 테이블에 동시에 두 개 이상 존재할 수 없다.

일반적인 방법 )

-> 사용자가 기본키를 정의하여 테이블을 생성하면 기본키 무결성 제약은 DBMS에 의해 자동적으로 검증된다.

간단한 방법 )

- 기본키가 하나의 필드로 구성될 때만 가능하다.

- 제약에 이름이 부여되지 않아 추후에 기본키를 취소하거나 변경할 수 없다는 단점이 있다.

2) 참조 무결성 제약

: 한 테이블의 레코드가 다른 테이블을 참조할 때, 참조되는 테이블에 해당 레코드가 반드시 존재하거나 null 값을 가져야 한다.

형식 ) 

예시 )

Q) 만약 테이블 생성 당시 외래키를 설정하지 않았고, 추가적으로 설정하고 싶다면?

A) ALTER 문으로 수정 가능

Q) 외래키 제약을 삭제하고 싶을 때에는?

A) alter table ~ drop constraint 문을 사용

[ 테이블의 무결성 제약 ]

- not null

- unique

- check

- default

1) NOT NULL

: 특정 필드에 대해 null 값 입력을 금지해야 하는 경우

참고 ) 기본키는 원래부터 null 값을 허용하지 않으므로 기본키로 정의된 필드에 대해서는 Not null 조건을 입력하지 않아도 됨

2) UNIQUE

: 해당 필드가 테이블 내에서 중복된 값을 갖지 않고 반드시 유일한 값을 갖도록 하는 제약 조건

** 두 개 이상의 필드에 동시 설정 가능하다.

: 두 필드 각각의 값이 유일해야 한다는 의미가 아니다. 아래의 경우에 이름과 성이 동시에 같은 경우에 대해 중복을 허용하지 않는다는 의미이다.

- unique 제약을 추가하거나 해제할 때 alter table 를 이용한다.

3) CHECK

: 각 필드의 값은 정의된 도메인에 속한 값만 허용한다.

: 필드를 정의할 때 주어진 데이터 타입 이외에도 좀 더 세부적으로 허용할 수 있는 값의 범위를 지정할 수 있다.

** 제약에 이름을 부여하지 않거나 각 필드에 대한 조건식을 분리하여 명시 가능하다.

-> 

- chek 제약을 추가하거나 해제할 때

4) DEFAULT

: 레코드를 삽입할 때, 필드에 대한 값이 정해지지 않을 경우 사전에 정해놓은 값이 입력되도록 디폴트 값을 설정

- default 제약을 추가 혹은 해제하고 싶을 때

- 오라클만의 default 설정 및 해제 방법

** 지나치게 많은 제약조건이 존재할 경우 많은 데이터가 입력될 때 문제가 생길 수 있다.

< 기타 무결성 제약 >

1) Assertion (주장)

2) 응용 프로그램에서의 무결성 제약

: 개발자가 응용 프로그램에서 직접 무결성을 보장하는 코드 작성

1. 뷰(View) 란?

기존 테이블로부터 생성(정의)되는 가상의 테이블

- 기반 테이블들에 대한 하나의 질의문(query)으로 정의된다.

( but 실제로 질의를 실행하는 것은 아님 ! 질의 그 자체가 뷰임. 물리적인 저장공간 없음)

- 뷰는 기반 테이블과 동등하게 사용된다.

< 기능 >

- 특정 사용자에게 테이블 내용 중 일부를 숨길 수 있기 때문에 데이터에 대한 접근 제한과 보안성을 높일 수 있다.

- 질의의 결과를 뷰로 만들어서 사용하면 복잡한 질의를 간단히 표현할 수 있다.

2. 뷰 생성 방법

- <SELECT문> : 뷰 생성에 사용될 질의문

- OR REPLACE 를 추가하면 <뷰이름> 과 같은 뷰가 이미 존재하는 경우, 기존의 뷰를 삭제하고 새로 생성한다.

3. 뷰 생성 권한

: 대부분의 DBMS에서 일반 사용자 계정에서는 뷰 생성 권한이 없다.

: 관리자가 특정 사용자에게 권한을 부여해야 뷰를 생성할 수 있다.

4. 직접 실습해보기

- SQLDevelper 에서 실습해 보기로 하자

1) localhost(관리자 계정) 에서 scott(사용자 계정)으로 뷰 생성 권한을 부여한다.

2) V_TAKES 라는 이름의 뷰 생성하기

참고) TAKES 테이블은 이렇게 생겼다.

3) v_takes 뷰를 이용하여 특정 학번의 학생 정보를 검색

질의결과)

5) INSERT문도 실행시켜보기

실행결과)

Q) 원래 TAKES 테이블에도 값이 추가가 될까?

A) Yes! 추가되지 않은 GRADE 열의 값은 null 이 들어간다. 아래 결과 확인

5. 읽기 전용 뷰

: INSERT, DELETE, UPDATE 와 같은 데이터 조작 언어(DML)의 사용을 막고 싶을 때에는 

WITH READ ONLY 키워드를 추가해준다. (SELECT 는 가능)

6. 뷰 삭제

7. 실습해보기 두번째

- 컴퓨터공학과 학생들의 정보만 추출하여 뷰를 생성한다.

** 문제발생) 기본키인 dept_id 가 select 되지 않았기 때문에 insert문을 실행시킬 수 없다.