⚠️ Information - reference

모든 내용은 https://db-book.com/ 의 도서를 참고했음.
본문의 RDB 스키마를 참고할 것.

Basis of SQL

Integrity Constraints

• not null
• primary key
• unique
• check

not null

name varchar(20) not null
budget numeric(12,2) not null

primary key

primary key (course_id, sec_id, semester, year)

unique

unique(A_1, A_2, ... , A_n)
: 후보키 설정을 목적으로 하며, 튜플을 구분 가능하게 하는 속성을 의미
Ex. 주민등록번호라는 column을 unique로 설정 가능
+) null 허용

check

check (semester in ('Fall', 'Winter', 'Spring', 'Summer')) // semester는 주어진 4개의 단어만 지녀야 함.

Referential Integrity (참조 무결성)

foreign key (dept_name) references department [(dept_name)] // []: Optional

Cascading in Referential Integrity

on {delete | update} [option]

• restrict (default value)
  : No change
• cascade
  : together
• set null
  : fill with null
• set default
  : fill with user-defined value

---

Types

Built-in Data Type

Large-Object Type

User-Defined Type

Domains

---

Intermediate SQL

JOIN

Danger of Natural Join

• with ON Clause
  : where clause에 포함될 수 있는 내용 (predicate)
• with using Clause
  table의 attributes를 사용

Outer Join

left outer join

full outer join

Views

---

Authorization

Privileges

• Read
• Insert
• Update
• Delete
• all

Grant

• 자신이 가지고 있는 권한을 타인에게 부여 가능하다

-- grand <privilege list> on <relation or view> to <user list>
grant select on department to Amit, Satoshi

Revoke

-- revoke <privilege list> on <relation or view> from <user list>
revoke select on student from U1, U2, U3

Role

• example

create role instructor;
grant instructor to Amit;

• Chain of Roles

• create role dean;
• grant instructor to dean;
• grant dean to Satoshi;

• Other Authorization Features
  p 4.60

---

Advanced SQL

SQL Functions

create function dept_count (dept_name varchar(20))
    returns integer
    begin
    declare d_count integer;
        select count (*) into d_count
        from instructor
        where instructor.dept_name = dept_name
    return d_count;
end

SQL Procedures

프로시저: 특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 서브 프로그램
(return value가 없는 함수라고 생각하면 좋을듯)

create procedure dept_count_proc (in dept_name varchar(20),
								  out d_count integer)
begin
  select count(*) into d_count // out에 저장
  from instructor
  where instructor.dept_name = dept_count_proc.dept_name // 해당 프로시저의 param을 . 으로 받아옴
end

⚠️ param 받는 방식 유의

• Usage

declare d_count integer;
call dept_count_proc('Physics', d_count);

Assertions

• using check function

create assertion credits_earned_constraint check
(not exists (select ID
			 from student
             where tot_cred <> (select coalesce(sum(credits),0)
			 					from takes natural join course
             					where student.ID = takes.ID and
             	   					  grade is not null and
                   					  grade <> 'F')
           	)
)

Trigers

create trigger credits_earned after update of takes on (grade) // takes 테이블의 grade 정보가 update되면 실행되는 트리거
referencing new row as nrow // 변경 후 튜플
referencing old row as orow // 변경 전 튜플

for each row // 모든 튜플에 대해서
// 새로 삽입된 학점이 F거나 Null이 아니고, 변경 전 학점이 유효하지 않을 때
when nrow.grade <> 'F' and nrow.grade is not null 
	and (orow.grade = 'F' or orow.grade is null)

begin atomic
  update student // student 테이블 update
  set tot_cred= tot_cred + // 기존 tot_cred에
  	 // 학생의 수강 수업ID와 같은 ID에 해당하는 수업의 학점을 뽑아와
  	 (select credits
      from course
      where course.course_id= nrow.course_id)
  where student.id = nrow.id; // 학번 조회 (학번이 일치할 때)
end;