[데이터베이스] join, 제약조건 실습

# 조인식 join

자연 조인(natural join) , 두 테이블 간의 연관이 있어야 함.

join .. on : on은 조인된 릴레이션에 대한 조건을 정할 수 있다. where과 같은 기능

select *
from student join takes on student.ID=takes.ID

--> 같은 결과
select *
from student, takes
where student.ID=takes.ID

--> 모두 같은 결과
select *
from course join prereq on course.course_id = prereq.course_id

select *
from course inner join prereq on course.course_id = prereq.course_id

select *
from course, prereq
where course.course_id = prereq.course_id

 default 는 inner join

 

# 외부 조인 outer join

left outer join

right outer join

full outer join

select *
from course join prereq on course.course_id = prereq.course_id
order by course.course_id

select *
from course left join prereq on course.course_id = prereq.course_id
order by course.course_id

join과 left join의 결과 비교

그냥 join에서 선수과목이 있는 것들을 제외하고는 NULL로 표시됨

(distinct를 통해 선수과목이 겹치는 과목들이 21개가 있음. 따라서 join하고 결과가 221 행이 나올 것임)

join 완벽정리 블로그 https://pearlluck.tistory.com/46

DB JOIN 정리(INNER/LEFT/RIGHT/OUTER)

 

# 뷰 views

논리적 모델의 일부로서가 아니고 가상적인 릴레이션으로서 사용자에게 보이는 릴레이션을 뷰(view)라고 한다.

views 뷰는 선언만 할 뿐 데이터를 가지고 있지 않다   ** 중요 **

create view faculty as   --> faculty 뷰 생성
select ID, name, dept_name
from instructor

create view departments_total_salary(d_name, total_salary) as 
select dept_name , sum(salary)
from instructor
group by dept_name

select dept_name , sum(salary)
from instructor
group by dept_name

와 같은 결과가 나오고 칼럼 이름은 d_name, total_salary로 정해지게 됨.

 

- physics_fall_2006 뷰 생성 : 2006년 가을 학기에 물리학과에서 제공한 모든 수업의 정보와 수업이 이루어진 건물과 강의실 번호의 목록을 가지고 있는 뷰

create view physics_fall_2006 as
    select course.course_id, sec_id, building, room_number
	from course, section
	where course.course_id=section.course_id
	      and course.dept_name = 'Physics'
		  and section.semester='Fall'
		  and section.year='2006'

뷰로 뷰를 생성가능

create view physics_fall_2006_Taylor as
select *
from physics_fall_2006
where building = 'Taylor'

select *
from physics_fall_2006_Taylor

--> 각 학과별 모든 교수의 연봉의 합
create view departments_total_salary(dept_name, total_salary) as
   select dept_name, sum(salary)
   from instructor
   group by dept_name

create view physics_fall_2009_watson as
     select course_id, room_number
     from physics_fall_2009
     where building= 'Watson';

--> 같은 sql. 점점 확장된다고 보면 됨
create view physics_fall_2009_watson as
(select course_id, room_number
from (select course.course_id, building, room_number
      from course, section
      where course.course_id = section.course_id
            and course.dept_name = 'Physics'
            and section.semester = 'Fall'
            and section.year = '2009')
where building= 'Watson')

# view 생성 시 고려사항

view를 남용하지 말 것!!

 

# 트랜잭션

commit work

rollback work

 

## 무결성 제약조건 Integrity constraints

데이터 변경이 데이터 일관성(consistency)에 손실을 초래하지 않음을 보장하는데 이용.

무결성 제약조건은 데이터베이스 손상방지.

ex)
교수 이름은 null 이 될 수 없다.
서로 다른 교수가 같은 ID를 가질 수 없다.
course 릴레이션에서 모든 학과 이름은 department 릴레이션의 학과 이름과 일치해야 한다.
학과의 예산은 $0.00보다 항상 커야 한다.

무결성이 발생할때 : insert, delete, update할 시

meta data : Data about data --> Data Dictionary 사전 (System Catalog)

 

# 단일 릴레이션에서의 제약조건

• primary key --> not null 개체무결성제약조건
• not null
• unique
• check (<predicate>)

# unique

테이블 변경하기 :

instructor 테이블 구조

alter table instructor

add unique(name)

 

unique는 내가 원하는대로 동작하지 않을 수 있음

# check

check(P)절은 릴레이션의 모든 투플에 의해 만족되는 술어 P를 명세한다.

명시된 조건들을 만족하는 것을 보장. 더 강력한 타입 시스템.

section 테이블 구조

학기 조건 : 계절 중 하나여야 한다.

year의 조건 : 1702년 이상 2099년 이하

 

check(P)절은 릴레이션의 모든 투플에 의해 만족되는 술어 P를 명세한다.

명시된 조건들을

# unique옵션 삭제해보기

alter table course
add constraint title_UNI unique(title, course_id)

alter table course
drop constraint title_UNI

 

# 개체 무결성 제약조건

존재하는 사실은 유일하게 식별돼야 함.

- primary key

 

# 참조 무결성 references integrity

존재하지 않는 사실은 참조하면 안됨.

* foreign키가 primary키에 꼭 매칭되지 않아도 된다.

 

강좌를 지우면 교수도 지워야하고, 수강한 학생들도 삭제된다.

한 사실이 없어짐에 따라서, 줄줄이 연관된 사실이 사라진다. 존재하지 않는 사실은 참조하면 안되므로.

 

# on delete cascade

# on update cascade

 

# cascade의 작업 : set null, set default, restrict 제한하다

예를 들어, on delete restrict라고 되어있으면 지우는 것에 제약을 둬서 삭제를 제한하게하는 작업

insert할때도 개체무결성 제약조건에 해당함.

on delete NO ACTION

 

# sql 날짜와 시간 타입 date, time, timestamp

date: (4개의 숫자를 가지는) 년도, 월, 일로 구성되는 날짜

   예 : date ‘2005-7-27’

time: 그날의 시간, 시, 분, 초

   예 : time ‘09:00:30’ time ‘09:00:30.75’

timestamp: date 와 time 의 조합

   예 : timestamp ‘2005-7-27 09:00:30.75’

 

# create type

새로운 타입을 만든다. 

create type Dollars as numeric (12,2)

create type Dollars as numeric (12,2) not null

 

# Domains

sql에서는 다루기에는 좀 큰 개념

 

# 대형 객체 타입 Large-Object Types

create table A (
    C1 int,
	C2 BLOB // CLOB  --> 코드화,이진데이터 // 문자데이터
	)

 

# 인덱스 index

Index 왜? --> 검색성능을 향상시키기 위해서

create index studentID on student(ID)
--> 인덱스 제거하기
drop index student.studentID