Intermediate SQL[3]

임승섭·2023년 4월 16일
0

Database system

목록 보기
12/22

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;

0개의 댓글