Chapter 4: Intermediate SQL

Chapter 4: Intermediate SQL 조인 표현식 (Join Expressions) 뷰 (Views) 무결성 (Integrity Constraints) 1

조인 릴레이션 (Joined Relations) 죠인 연산은 두 릴레이션을 취해 또 다른 릴레이션을 결과로 돌려준다. 죠인 연산은 주어진 조건 하에 두 릴레이션내의 튜플들이 매칭되는 결과만을 포함하는 카티션 곱 연산이다. 또한 조인 결과에 어떤 애트리뷰트가 나타날지를 지정한다. 이들 부가적인 연산은 일반적으로 from 절 내의 부 질의 표현식으로 사용된다. select * from instructor natural join teaches;

조인 연산 – Example Relation course Relation prereq Observe that prereq information is missing for CS-315 and course information is missing for CS-347

자연 조인 (Natural Join) Natural join matches tuples with the same values for all common attributes, and retains only one copy of each common column select * from instructor natural join teaches;

Natural Join Example List the names of instructors along with the course ID of the courses that they taught. select name, course_id from instructor, teaches where instructor.ID = teaches.ID; select name, course_id from instructor natural join teaches; select name, course_id from instructor join teaches using (ID);

Outer Join 정보 손실을 피하기 위한 조인 연산의 확장. 조인을 계산하고 다른 릴레이션의 튜플과 그 값이 일치하지 않는 어떤 릴레이션의 튜플들을 죠인의 결과에 추가한다. 널 값을 사용한다.

Left Outer Join course natural left outer join prereq

Right Outer Join course natural right outer join prereq

Full Outer Join course natural full outer join prereq

Joined Relations 조인 연산은 두 릴레이션을 취해 또 다른 릴레이션을 결과로 돌려준다. 이들 부가적인 연산은 일반적으로 from 절 내의 부 질의 표현식으로 사용된다. 조인 조건 (Join condition) - 두 릴레이션내의 어떤 튜플들이 부합하고 조인 결과에 어떤 애트리뷰트가 나타날지를 정한다. 죠인 유형 (Join type) - 다른 릴레이션의 어떤 튜플과 부합하지 않는 튜플들을 어떻게 취급할 것인가(조인 조건에 근거해)를 정한다.

Joined Relations – Examples select * from course (inner) join prereq on course.course_id = prereq.course_id select * from course, prereq where course.course_id = prereq.course_id What is the difference between the above, and a natural join? course left outer join prereq on course.course_id = prereq.course_id

Joined Relations – Examples course natural right outer join prereq course full outer join prereq using (course_id)

뷰 (Views) 어떤 경우에는, 시스템의 모든 사용자에게 DB의 전체적인 논리적 모델을 공개하는 것이 바람작하지 않을 수 있다. 어떤 사람이 강사의 ID, 이름, 학과명을 알고자 하는 경우 (단 급여는 필요 없음), 다음과 같은 질의를 작성하여야 한다. select ID, name, dept_name from instructor 뷰 (view)는 어떤 사용자의 뷰로부터 어떤 데이터를 숨기는 방법을 제공한다. 뷰는 DB의 개념 모델에 해당하지 않으며, 오직 사용자에게 보이는 목적의 가상 릴레이션 (virtual relation)을 의미한다.

뷰 정의 뷰를 생성하려면 다음과 같은 명령을 사용한다. - <질의 표현식>은 적법한 표현식이다. create view v as < query expression > 여기서: - <질의 표현식>은 적법한 표현식이다. - 뷰명은 v로 표현된다. 일단 뷰가 정의되면, 뷰를 생성하는 가상 릴레이션을 참조하는데 뷰명이 사용될 수 있다. 뷰 정의는 질의 표현식을 평가함으로써 새로운 릴레이션을 생성하는 것과는 다르다. 대신, 뷰 정의는 뷰를 사용하는 질의내에 대치될 표현식을 저장한다.

Example Views A view of instructors without their salary create view faculty as select ID, name, dept_name from instructor Find all instructors in the Biology department select name from faculty where dept_name = ‘Biology’ Create a view of department salary totals 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 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 = ’2009’; create view physics_fall_2009_watson as select course_id, room_number from physics_fall_2009 where building= ’Watson’;

뷰 확장 create view physics_fall_2009_watson as Expand use of a view in a query/another view 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’;

다른 뷰를 사용해 정의한 뷰 하나의 뷰는 다른 뷰를 정의하는 표현식에 사용될 수 있다. 뷰 릴레이션 v1은 v2가 v1을 정의하고 있는 표현식에 사용된다면 뷰 릴레이션 v2에 직접 종속한다고 말한다. 뷰 릴레이션 v1은 종속 그래프내에 v2에서 v1로의 경로가 존재하면 뷰 릴레이션 v2에 종속한다고 말한다. 뷰 릴레이션 v가 그 자신에 종속하면 순환적이라 말한다.

뷰 확장 다른 뷰의 용어로 정의된 뷰의 의미를 정의하는 방법 뷰 v1을 그 자신이 뷰 릴레이션의 사용을 내포할 수 있는 표현식 e1 으로 정의된다 하자. 표현식의 뷰 확장은 다음과 같은 대치 절차를 반복한다. repeat e1에서 어떠한 뷰 릴레이션 vi 를 찾는다. 뷰 릴레이션 vi를 vi를 정의하고 있는 표현식으로 대치한다. until 더 이상의 뷰 릴레이션이 e1내에 존재하지 않음 뷰 릴레이션이 순환적이지 않은 한 이 루프는 종료할 것이다.

뷰의 갱신 faculty 뷰에 새로운 튜플을 삽입하시오. insert into faculty values (’30765’, ’Green’, ’Music’); 이 삽입문은 instructor relation 에 다음과 같은 튜플 삽입문으로 표현되어야 한다. (’30765’, ’Green’, ’Music’, null)

Some Updates cannot be Translated Uniquely create view instructor_info as select ID, name, building from instructor, department where instructor.dept_name= department.dept_name; insert into instructor_info values (’69987’, ’White’, ’Taylor’); which department, if multiple departments in Taylor? what if no department is in Taylor? 대부분의 SQL 시스템에서는 오직 단순한 뷰 갱신만을 허용한다. 복잡한 뷰에의 갱신은 변환하기가 어렵거나 불가능해 허용되지 않는다.

무결성 제약 조건 (Integrity Constraints) 무결성 제약 조건은 데이터베이스에의 권한 있는 변경이 데이터의 일관성을 잃지 않도록 보장함으로써 데이터베이스를 우발적인 손상으로부터 보호한다. A checking account must have a balance greater than $10,000.00 A salary of a bank employee must be at least $4.00 an hour A customer must have a (non-null) phone number

단일 릴레이션에 대한 무결성 제약조건 not null primary key unique check (P), where P is a predicate

Not Null 과 Unique 제약조건 not null Declare name and budget to be not null name varchar(20) not null budget numeric(12,2) not null unique ( A1, A2, …, Am) 속성 A1, A2, … Am 이 후보키를 임을 나타냄.candidate key. 후보키는 null 값을 가질 수 있음 (주키와 다름).

check 절 check (P) 여기서 P는 술어이다. Example: semester 속성은 “fall”, “winter”, “spring” “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) 주어진 애트리뷰트의 집합에 대해 한 릴레이션에 나타나는 값이 다른 릴레이션의 어떤 애트리뷰의 집합에도 또한 나타나도록 한다. Example: 만약 “Biology” 가 instructor 릴레이션 튜플에 나타나는 학과명 (department name)이라면, department 릴레이션에 “Biology”를 위한 튜플이 존재하여야 한다. 형식적 정의 - r1(R1)과 r2(R2)를 각각 주키 K1과 K2를 가진 릴레이션이라 하자. - r2 내 각각의 t2에 대해 t1[K1] = t2[]인 r1내에 튜플 t1이 존재해야 한다면, R2의 부분 집합 는 릴레이션 r1의 K1을 참조하는 외래 키이다. - 참조 무결성 제약 조건: (r2)  K1(r1)

Cascading Actions in Referential Integrity create table course ( course_id char(5) primary key, title varchar(20), dept_name varchar(20) references department ) create table course ( … dept_name varchar(20), foreign key (dept_name) references department on delete cascade on update cascade, . . . ) alternative actions to cascade: set null, set default

Built-in Data Types in SQL date: Dates, containing a (4 digit) year, month and date Example: date ‘2005-7-27’ time: Time of day, in hours, minutes and seconds. Example: time ‘09:00:30’ time ‘09:00:30.75’ timestamp: date plus time of day Example: timestamp ‘2005-7-27 09:00:30.75’ interval: period of time Example: interval ‘1’ day Subtracting a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values

인덱스 생성 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) 인덱스는 레코드 검색을 고속으로 수행하기 위하여 생성한다. e.g. select * from student where ID = ‘12345’ can be executed by using the index to find the required record, without looking at all records of student More on indices in Chapter 11

End of Chapter 4

Figure 4.01

Figure 4.02

Figure 4.03

Figure 4.04

Figure 4.05

Figure 4.07 Taylor

Figure 4.06

Figure 4.03