Download
1 / 39
390 likes | 407 Views
Chapter 4: Intermediate SQL. Chapter 4: Intermediate SQL. 조인 표현식 (Join Expressions) 뷰 (Views) 무결성 (Integrity Constraints). 조인 릴레이션 ( Joined Relations). 죠인 연산은 두 릴레이션을 취해 또 다른 릴레이션을 결과로 돌려준다. 죠인 연산은 주어진 조건 하에 두 릴레이션내의 튜플들이 매칭되는 결과만을 포함하는 카티션 곱 연산이다 . 또한 조인 결과에 어떤 애트리뷰트가 나타날지를 지정한다.
E N D
Chapter 4: Intermediate SQL • 조인 표현식 (Join Expressions) • 뷰 (Views) • 무결성 (Integrity Constraints)
조인 릴레이션(Joined Relations) • 죠인 연산은 두 릴레이션을 취해 또 다른 릴레이션을 결과로 돌려준다. • 죠인 연산은 주어진 조건 하에 두 릴레이션내의 튜플들이 매칭되는 결과만을 포함하는 카티션 곱 연산이다. 또한 조인 결과에 어떤 애트리뷰트가 나타날지를 지정한다. • 이들 부가적인 연산은 일반적으로 from절 내의 부 질의 표현식으로 사용된다. • select *from instructor natural join teaches;
조인 연산 – Example • Relation course • Relation prereq • Observe that prereq informationis missing for CS-315 and courseinformationis 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_idfrom instructor, teacheswhere instructor.ID = teaches.ID; • select name, course_idfrom instructor natural join teaches; • select name, course_idfrom instructor join teaches using(ID);
Outer Join • 정보 손실을 피하기 위한 조인 연산의 확장. • 조인을 계산하고 다른 릴레이션의 튜플과 그 값이 일치하지 않는 어떤 릴레이션의 튜플들을 죠인의 결과에 추가한다. • 널 값을 사용한다.
Left Outer Join • coursenatural left outer joinprereq
Right Outer Join • coursenatural right outer joinprereq
Full Outer Join • coursenatural full outer joinprereq
Joined Relations • 조인 연산은 두 릴레이션을 취해 또 다른 릴레이션을 결과로 돌려준다. • 이들 부가적인 연산은 일반적으로 from절 내의 부 질의 표현식으로 사용된다. • 조인 조건 (Join condition)- 두 릴레이션내의 어떤 튜플들이 부합하고 조인 결과에 어떤 애트리뷰트가 나타날지를 정한다. • 죠인 유형 (Join type)- 다른 릴레이션의 어떤 튜플과 부합하지 않는 튜플들을 어떻게 취급할 것인가(조인 조건에 근거해)를 정한다.
Joined Relations – Examples • select* from course (inner) join prereq oncourse.course_id = prereq.course_id • select * from course, prereqwhere course.course_id = prereq.course_id • What is the difference between the above, and a natural join? • course left outer join prereq oncourse.course_id = prereq.course_id
Joined Relations – Examples • course natural right outer join prereq • coursefullouter join prereq using(course_id)
뷰(Views) • 어떤 경우에는, 시스템의 모든 사용자에게 DB의 전체적인 논리적 모델을 공개하는 것이 바람작하지 않을 수 있다. • 어떤 사람이 강사의 ID, 이름, 학과명을 알고자 하는 경우 (단 급여는 필요 없음), 다음과 같은 질의를 작성하여야 한다. select ID, name, dept_namefrom instructor • 뷰 (view)는 어떤 사용자의 뷰로부터 어떤 데이터를 숨기는 방법을 제공한다. • 뷰는 DB의개념 모델에 해당하지 않으며, 오직 사용자에게 보이는 목적의 가상 릴레이션 (virtual relation)을 의미한다.
뷰 정의 • 뷰를 생성하려면 다음과 같은 명령을 사용한다. create view v as < query expression > 여기서: - <질의 표현식>은 적법한 표현식이다. - 뷰명은 v로 표현된다. • 일단 뷰가 정의되면, 뷰를 생성하는 가상 릴레이션을 참조하는데 뷰명이 사용될 수 있다. • 뷰 정의는 질의 표현식을 평가함으로써 새로운 릴레이션을 생성하는 것과는 다르다. 대신, 뷰 정의는 뷰를 사용하는 질의내에 대치될 표현식을 저장한다.
Example Views • A view of instructors without their salarycreate view faculty asselect ID, name, dept_namefrom instructor • Find all instructors in the Biology departmentselect namefrom facultywhere dept_name = ‘Biology’ • Create a view of department salary totalscreate view departments_total_salary(dept_name, total_salary) as select dept_name, sum (salary)from instructorgroup by dept_name;
다른 뷰 정의를 이용한 새로운 뷰 정의 • create view physics_fall_2009as select course.course_id, sec_id, building, room_numberfrom course, sectionwhere course.course_id = section.course_idand course.dept_name = ’Physics’and section.semester = ’Fall’and section.year = ’2009’; • create view physics_fall_2009_watson as select course_id, room_numberfrom physics_fall_2009where building= ’Watson’;
뷰 확장 • 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, buildingfrom instructor, departmentwhere instructor.dept_name= department.dept_name; • insert intoinstructor_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 nullbudget 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 departmenton 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 studentwhere 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
Taylor Figure 4.07
