Intermediate SQL[3]

Transaction

• A transaction consists of a sequence of query and/or update statements and is a "unit" of work

• transaction은 끝날 때 다음 중 하나로 끝나야 한다

  • Commit work
    : The updates peformed by the transaction become permanent in the database.
  • Rollback work
    : All the updates performed by the SQL statements in the transaction are undone

    즉, 완전히 수행 완료 되거나, 다시 원상태로 돌아가거나 둘 중 하나다.
    ex). ATM에서 돈 인출하다가 기계가 죽어. 현금은 안나오고 통장에서 돈은 나가는 상황이 발생하면 안된다. -> 초기 상태로 돌아가야지

• Atomic transaction
  : either fully executed or rolled back as if it never occurred

• 또한 동시에 발생하는 transaction에도 각각 순서를 매겨주어야 한다.
  : sequential isolation

Integrity Constraints

• database에 대한 사고를 막는다. database에 대해 허가된 변화가 data consistency(일관성)을 잃어버리게 하지 못하게 한다

• example

  • A checking account must have a balance greater than $10,000.00
    (10,000불 이상을 항상 유지하게 한다)
  • A salary of a bank employee must be at least $4.00 an hour
    (시급이 4불 이상이어야 한다.. 너무 야박하네)
  • A customer must have a (non-null) phone number

• 무결성 제약조건, Integrity Constraints는 이론적으로 DB에 아무 형태로든 사용 가능하다. 보통 적당히 허용한다(?)

---

Constraints on a Single Relation

Not Null

Declare name and budget to be not null

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

Unique

unique(A1, A2, ..., Am)

• A1, ..., Am의 attribute들이 candidate key를 형성하도록 한다
• Candidate key는 null이 될 수 있다. (in contrast to primary keys)

Check

• check(P) : relation의 모든 tuple이 P를 무조건 만족해야 한다

Ensure that semester is one of fall, winter, spring or summer

create table section
	(course_id 	varchar(8),
     sec_id 	varchar(8),
     semester	varchar(6),
     year		numeric(4, 0),
     building 	varchar(15),
     room_number varchar(7),
     time_slot_id varchar(4),
     primary key(course_id, sec_id, semester, year),
     check(semester in ('Fall', 'Winter', 'Spring', 'Summer') ) )

---

Referential Integrity

• (foreign key constraint)가 referential integrity의 special case라고 생각하면 된다

• 주어진 속성들의 모임에 대해 한 relation에서 등장한 값이, 다른 relation의 특정 속성 모임에서 등장하도록 한다
  (Ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.)

• Let A be a set of attributes.
  Let R and S be two relations that contain attributes A,
  and where A is the primary key of S.
  A said to be a foreign key of R
  if for any values of A appearing in R these values also appear in S

• foreign key는 create table statement에서 지정될 수 있다.

foreign key (dept_name) references department

// dept_name이라는 속성은 department의 primary key 역할을 한다.
// foreign key가 여러개 될 수도 있다.

Cascading Actions in Referential Integrity

• referential integrity가 위반되었을 때, 보통 프로시저는 그 action을 reject한다.
• 아니면, 다 수정한다
• 아니면, null값으로 하거나, default값으로 설정한다

• 예를 들어, department에서 특정 학과 이름을 수정 or 삭제하고 싶은데,
  (삭제의 경우) department table에 존재하지 않으면

1. 삭제를 reject한다
2. 이쪽 table에 와서 그 학과 tuple들을 다 수정/삭제한다
3. 학과 이름을 null로 하거나 default값으로 설정ㅎ나다

create table course
	(...
     dept_name varchar(20),
     foreign key (dept_name) references department
     	on delete cascade
        on update cascase, // 2번에 대한 내용
     ...)

---

Integrity Constraints Violation During Transactions

create table person(
	ID char(10),
    name char(40),
    mother char(10),
    father char(10),
    primary key ID,
    foreign key father references person,
    foreign key mother references person)

• How to insert a tuple without causing constraint violation?
  만약 father, mother 정보 없는 tuple을 insert하려고 하면 문제가 생긴다.
  (foreign key constraint : 그 table에 이미 있어야 한다)
  • inserting person하기 전에, 그 person의 father mother를 먼저 insert한다
  • 아니면, father mother를 초기에 null로 설정하고, 모든 person들을 insert한 후에, update한다. (만약 father mother가 not null로 선언되어있으면 불가능하다)
  • 아니면, defer constraint checking

---

Complex Check Conditions

• check 절에 있는 구문은 subquery를 포함한 아무 구문이나 들어올 수 있다.

check (time_slot_id in (select time_slot_id from time_slot) )

// section relation에 있는 각 tuple의 time_slot_id는
// time_slot relation의 time slot의 identifier 역할을 한당.

• 이러한 condition은 section relation에 insert나 modify가 발생할 때도 체크되어야 하지만,
  time_slot relation에 변화가 있을 때에도 체크되어야 한다.

---

Assertions

• database가 항상 만족해야 하는 조건을 표현한다

For each tuple in the student relation, the value of the attribute tot_cred must equal the sum of credits of courses that the student has completed successfully

An instructor cannot teach in two different classrooms in a semester in the same time slot

• 이러한 조건들이 assertion으로 표현된다.

create assertion <assetion-name> check (<predicate>);

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

---

Types

Built in Data Types in SQL

• date : Dates, containing a (4 digit) year, month and date
  date '2023-3-25'

• time : Time of day, in hours minutes and seconds
  time '09:00:30'
  time '09:00:30.75'

• timestamp : date plus time of day
  timestamp '2005-7037 09:00:30.75'

• interval : period of time
  interval '1' day

  • date/time/timestamp 에 더해질 수 있고,
  • date/time/timestamp 를 뺄 수 있다.

Large-Object Types

• Large object(photo, video, CAD file, etc.)는 large object로 저장된다

• blob : binary large object

  • large collection of uniterpreted binary data (whose interpretation is left to an application outside of the database system)
  • binary data의 collection이고, 별도의 level에서 해석(interprete)된다.

• clob : character large object

  • large collection of character data

• query가 large object를 리턴할 때, large object 자체를 리턴하기보단, 그 주소(pointer)가 리턴된다.

User-Defined Types

• 새로운 type을 정의한다

create type Dollars as numeric(12, 2) final

// final 의미 : 더 이상 얘 가지고 다른 type 정의할 수 없다.

create table department
	(dept_name varchar(20),
     building varchar(15),
     budget Dollars);

• 새로운 domain을 저장한다. (type과 비슷하다)
  domain에서는 constraint(제약 조건)를 추가할 수 있다. (type과의 차이점)

create domain person_name char(20) not null

create domain degree_level varchar(10)
	constraint degree_level_test
    	check (value in ('Barchelors', 'Masters', 'Doctorate'));

---

Index Creation

• 많은 query들은 tabl에 기록된 아주 일부만 reference한다
• 특정 value로 모든 기록을 읽도록 하는 건 아주 비효율적이다
• An index on an attribute of a relation 은 db가 효율적으로 tuple들을 찾을 수 있도록 도와준다. 모든 tuple을 훑지 않아도.
  일종의 자료구조라고 생각하면 된다.

create index <name> on <relation-name> (attribute);

create table student
	(ID varchar(5),
     name varchar(20) not null,
     dept_name varchar(20),
     tot_cred numeric(3, 0) default 0,
     primary key(ID) )

create index studentID_index on student(ID)

• 이렇게 하면, 아래 query는 요구되는 record를 찾기 위해 index를 사용한다. student의 모든 record를 탐색하지 않고.

select *
from student
where ID = '12345'

---

Authorization

권한 on parts of the database

• Read
  : allows reading, but not modification of data

• Insert
  : allows insertion of new data, but not modification of existing data

• Update
  : allows modification, but not deletion of data

• Delete
  : allows deletion of data

• 이러한 권한은 일종의 특권(privilege)이다.

권한 to modify the database schema

• Index
  : allows creation and deletion of indices

• Resources
  : allows creation of new relations

• Alteration
  : allows addition or deletion of attributes in a relation

• Drop
  : allows deletion of relations

---

Authorization Specification

• grant는 권한을 부여하는 데 사용되는 statement이다

grant <privilege list> on <relation or view> to <user list>

• <user list> :
  • a user id
  • public, 모든 valid 이용자들
  • a role, user의 그룹. 나중에 배운다

grant select on department to Amit, Satoshi

// department에서 select할 수 있는 권한을 부여한다.

• view에 대한 권한을 주는 것은, 실제 underlying relation에 권한을 주는 걸 의미하는게 아니다. just view에 대한 권한이다.

---

Privileges in SQL

• select
  : allows read access to relation, or the ability to query using the view

• insert
  : the ability to insert tuples

• update
  : the ability to update using the SQL update statement

• delete
  : the ability to delete tuples

• all privileges
  : used as a short form for all the allowable privileges

---

Revoking Authorization

• 권한을 철회/회수한다

revoke <privilege list> on <relation or view> from <user list>

revoke select on student from U1, U2, U3

• <privilege list>는 all이 될 수 있다. 모든 권한을 회수하고 싶을 때.
• <revokee-list>이 public을 포함한다면, 모든 user가 회수당한다 except those granted it explicity.

• 만약 다른 grantee에게 같은 privilege를 두 번 부여받았으면, 한 번 회수되어도 하나가 남아있다.
• 다른 privilege로부터 만들어진(depend on) privilege는 그게 revoke되면 같이 revoke된다.

---

Role

• user들의 group

• A role is a way to distinguish among various users as far as what these users can access/update in the database

create a role <name>

create role instructor

• role이 만들어졌다면, 특정 user들을 그 role에 집어넣을 수도 있다

grant <role> to <users>

// users를 role에 포함시킨다

create role instructor;
grant instructor to Amit;

// role에 권한을 주면 그 member들이 모두 권한을 받는다
grant select on takes to instructor;

// role은 user 뿐만 아니라, 다른 role에도 부여할 수 있다
create role teaching_assistant
grant teaching_assistant to instructor;
// 이렇게 하면, instructor는 teachint_assistant의 모든 privilege를 상속받는다

// Chain of roles
create role dean;
grant instructor to dean;
grant dean to Satoshi;
// Satoshi는 dean에도 속하고, instructor에도 속한다
// instructor의 privilege가 바뀌면 dean의 previlege도 바뀐다.

---

Authorization on View

여기 내용부터 잘 이해가 안된다..

create view geo_instructor as
	(select *
     from instructor
     where dept_name = 'Geology');
     
grant select on geo_instructor to geo_staff
// geo_instructor(view)에 대한 select(권한)을 geo_staff에게 준다

// Suppose that a geo_staff member issues
select *
from geo_instructor;

• 이 상황에서,
  • geo_staff does not have permissions on instructor?
  • Creator of view did not have some permissions on instructor?

---

Other Authorization Features

• references privilege to create foreign key

grant referenct (dept_name) on department to Mariano;

• 이게 왜 필요할까?

• transfer of privileges

grant select on department to Amit with grant option;

// 다른 사람들에게 준 select 권한도 다 회수한다
revoke select on department from Amit, Satoshi cascade;

// 얘네한테만 회수하고 다른 사람들은 내버려 둔다
revoke select on department from Amit, Satoshi restrict;