[데이터베이스] #1~6 정리 (데이터베이스 소개 ~ 조인 테이블)

목차

1. 데이터베이스 소개
   1. 데이터베이스 시스템 이점
   2. 데이터 추상화 및 데이터 모델
   3. 데이터베이스 시스템
2. 관계형 데이터 모델
   
   1. 관계형 데이터 모델
3. SQL I
   1. 데이터베이스 언어
   2. DDL SQL
   3. DML SQL
4. SQL II
   1. Select
   2. 널 값
5. 오라클 실습 (개념)
   1. DBA가 사용자 생성하기
   2. 첫 DB 만들기
6. SQL III
   1. 집계 함수
   2. 조인 테이블

 

---

1. 데이터베이스 소개

1. 데이터베이스 시스템 이점

• 데이터 추상화 제공
• 데이터 접근의 용이성 제공
• 데이터 중복 및 불일치성에 대한 제어 용이
• 데이터 무결성 제약조건 유지 용이
• 갱신 원자성 제공
• 다수 사용자 동시성 제어
• 데이터 보호
• 데이터 백업 및 회복

2. 데이터 추상화 및 데이터 모델

• 인스턴스 = 데이터의 값(객체, 값)
• 스키마 = 데이터의 구조

📌 3단계 데이터 추상화

1. 물리적 스키마
2. 논리적 스키마
3. 뷰 스키마

📌 데이터 독립성

• 물리적 데이터 독립성: 논리적 스키마 변화 없이 물리적 스키마 변화
• 논리적 데이터 독립성: 뷰 스키마 변화 없이 논리적 스키마 변화

데이터 모델

• 관계형 데이터 모델 (일반적으로 인지하는 모델)
• 객체지향 데이터 모델
• 객체관계형 데이터 모델 (상용 모델)
• 네트워크 데이터 모델
• 계층 데이터 모델

ER 다이어그램

개체와 관계성으로 mini-world 표현

3. 데이터베이스 시스템

개념적 분리

• 질의어 처리기: 질의어 처리, 권한 관리, 인증 등
• 저장 관리자: 데이터베이스 및 데이터 사전 관리, 트랜잭션 관리 등

📌 데이터 사전

메타데이터(데이터에 대한 데이터) 관리

SQL 언어를 활용하여 접근

• 스키마 데이터
• 제약 조건 데이터
• 접근 권한 데이터 등

트랜잭션

• 동시성 제어
• 복구

데이터베이스 시스템

• ~1970: 화일 시스템
• 70~80: 네트워크 데이터 모델, 계층 데이터 모델
• 80~: 관계형 데이터 모델, 객체지향 데이터베이스 시스템
• 90~: 객체 관계형 데이터 모델
• 00~: XML 데이터 모델
• 10~: 대용량 저장 시스템, NOSQL(Not Only SQL)

 

---

 

2. 관계형 데이터 모델

관계형 데이터 모델

데이터베이스를 아래 두 가지로 표현

• 관계(relation)
• 무결성 제약

📌 Key

터플을 식별하는 수 있도록 하는 속성의 집합

• 슈퍼 키 - 터플을 식별 가능한 속성의 집합(set)
• 후보 키 - 유일성을 유지하면서 가장 적은 속성의 집합(set)
• 주 키(Primary Key) - 실질적으로 사용하는 키

참조 무결성

• 외래 키: 참조하는 속성
• 외래 키 값은 참조받는 관계의 주 키 값 or 널(null)만 가능

관계 대수

• σ: select(선택)
• Π: project(투영)
• ∪: union(합집합)
• −: set difference(차집합)
• ×: Cartesian product(곱집합)
• ρ: rename(재명명)
• ∩: intersection(교집합)
• ←: assignment(할당)
• ⋈: natural join(자연 조인)
• ⟗: outer join(외부 조인)
• ÷: division(나눔)

 

---

3. SQL I

1. 데이터베이스 언어

DDL, DML, DCL

• DDL: 데이터베이스 스키마 조작
• DML: 데이터베이스 인스턴스 조작
• DCL: 스키마, 인스턴스를 제외한 객체 조작

절차적, 비절차적 언어

SQL은 비절차적 언어

요청 데이터 선언 o, 구하는 방법 x

2. DDL SQL

도메인 타입

• char(n)
• varchar(n)
• int
• smallint
• numeric(p, d)
• real, double precision
• float

Create

스키마 생성

Create table professor (
	pID		char(5) primary key,  #주 키 지정
	name		varchar(20) not null, #값 null 불가능
	deptName	varchar(20),
	salary		numeric(8,2),
	gender		char(1),
	constraint myForeignKey foreign key (deptName) references departmemt),
	#외래 키 선언, 이름 지정
	check gender in ('M', 'F') #속성 값 'F', 'M'만 가능
);

무결성 제약 → 주 키 지정, not null, 외래 키 선언 등

DROP

스키마 삭제

ALTER

스키마 변경

• 속성 추가/제거
• 테이블 제약사항 수정

3. DML SQL

• select - 검색
• insert - 입력
• delete - 삭제
• update - 갱신

Insert

터플 입력

Inesrt into course values ('437', 'Databases', 'CS', 4);

Insert into professor
select * from professor;

Delete

Delete from professor

Delete from professor
where deptName in (select deptName
                  from department
                  where building = 'Vision Hall');

Update

Update professor
set salary = (case
                    when salary > 7000 then salary * 1.03
                    else salary * 1.05
              end);

Update student S
set S.totalCredit = (select sum(credit)
                    from takes natural join course
                    where S.sID = takes.sID
                    and grade <> 'F'
                    and grade is not null);
Update student
set totalCredit = 0
where totalCredit is null;

 

---

4. SQL II

1. Select

select A1, A2, ..., An	//속성(column)
from R1, R2, ..., Rn		//관계(relation)
where P									//명제(predicate)
group by <grouping addtibutes>
having <conditions>
order by <ordering attributes>

select

투영할 속성 선택

• * 기호: 모든 속성 의미
• distinct 키워드: 터플 중복 제거
• all 키워드: 터플 중복 허용 (기본값)

where

터플이 만족하여야 하는 조건 명시

• and, or, not, = 연산 가능
• between: 값 구간 의미(경계 값 포함)

from

질의에 관련 있는 테이블 나열

• R1, R2: R1 × R2 (카티시안곱)
• join 연산 가능

자연 조인 - natural join

• 기본 조인에서 공통 속성을 하나만 표시
• 원치 않는 조인 연산이 일어나지 않도록 주의 필요
• using 키워드: 명시한 속성만으로 조인 연산 수행

재명명 - as

Select sID, name, salary/12 monthSalary
from professor

Select distin T.name
from professor as T, professor as S
where T.salary > S.salary and S.deptName = 'CS';

스트링 연산

• _: 길이 1인 임의 스트링
• %: 임의 길이인 임의 스트링
• \%: %를 기호로 인식

터플 정렬

order by deptName desc, name

집합 연산

• union: 합집합 (터플 개수: n+m)
• intersect: 교집합 (터플 개수: min(n, m))
• except: 차집합 (터플 개수: max(0, n-m))

(select cID from teaches where year = 2009)
union #all 키워드 추가 시 중복 터플 포함
(select cID from teaches where year = 2010)

6. 널 값

• is null
• is not null
• null 값은 비교 불가
• null = null은 false

 

---

5. 오라클 실습 (개념)

1. DBA가 사용자 생성하기

Create user C##hodori identified by tooshytotell
default TABLESPACE users
temporary TABLESPACE temp;

오라클을 사용하기 위해 첫 번째로 DBA로 시스템에 접속하여 사용자를 생성 + 권한 부여

사용자 C##hodori, 암호 tooshytotell 생성

사용자 계정은 항상 C##으로 시작

Grant connect, resource to C##hodori;

C##hodori에게 connect, resource 권한 부여

connect와 resource는 권한 다수 개를 묶어 놓은 role임

 

2. 첫 DB 만들기

테이블 만들기

Create table firstTable
	(id		char(13) primary key,
	name		varchar(30),
	height		n)
Insert into firstTable('11', 'Hong Gildong', 165.3);

손쉬운 명령어

Create table myFirstTemp as select * from firstTable;
//firstTable과 똑같은 (임시적인 )테이블 생성
Describe user_constraint;
Describe table_name; //to see schema
Select * from tab;   //to see all tables available
Show user;           //to see who am I

외래키와 데이터 입력

Create table temp2 (
	name	char(10) primary key,
	dept	char(10),
constraint c1 foreign key (dept) references temp1);

 

c1은 외래키 제약의 이름

사용자가 제약 이름을 명시하지 않으면 시스템이 자동적으로 이름을 부여

사용자는 데이터 사전을 검색하여 이를 확인 가능

---

6. SQL III

1. 집계 함수

집계함수

• avg
• min
• max
• sum
• count //distinct 키워드: 존재하는 속성 값의 개수

group by

Select deptName, avg(salary)
from professor
group by deptName;

having

Select deptName, avg(salary)
from professor
group by deptName
having avg(salary) > 6900;

널 값과 집계함수

• 집계함수는 널 값을 무시하고 계산
• 모든 값이 null이라면 null 반환
• count 함수 일 경우 0 반환
• 그룹 생성 시 null 기준 그룹 생성

2. 조인 테이블

• inner join
• full outer join
• left outer join
• right outer join

앞에 natural을 추가하여 공통 속성 중복 제거 가능

using 키워드 사용 시 자동 natural join 생성