Example Schema Movie Database Schema Movie title year
Example Schema Movie Database Schema Movie (title, year, length, in. Color, studio. Name, producer. C#) Movie. Star (name, address, gender, birthdate) Stars. In (movietitle, movieyear, starname) Movie. Exec (name, address, cert#, net. Worth) Studio (name, address, pres. C#) 4
SQL에서 릴레이션 스키마의 정의 (계속) u 테이블 선언: CREATE TABLE table-name – CREATE TABLE » 릴레이션 이름; 애트리뷰트, 애트리뷰트 타입들의 리스트 CREATE TABLE Movie. Star ( name CHAR(30), address VARCHAR(255), gender CHAR(1), birthdate DATE ); u 테이블 삭제: DROP table-name DROP R; 8
SQL에서 릴레이션 스키마의 정의 (계속) u 디폴트 값 » 구체적인 값이 주어지지 않았을 때는 NULL 값이 사용 – 키워드 DEFAULT 와 특정 값 기술 가능 CREATE TABLE Movie. Star ( name CHAR(30), address VARCHAR(255), gender CHAR(1) DEFAULT ‘? ’, birthdate DATE DEFAULT DATE ‘ 0000 -00 -00’); ALTER TABLE Movie. Star ADD phone CHAR(16) DEFAULT ‘unlisted’; 10
SQL에서 릴레이션 스키마의 정의 (계속) (예) Stars. In(movie. Title, movie. Year, star. Name) (Q 1) SELECT movie. Title, movie. Year FROM Stars. In WHERE star. Name = s; (Q 2) SELECT star. Name FROM Stars. In WHERE movie. Title = t AND movie. Year = y; (I 1) INSERT INTO Stars. In VALUES(t, y, s); 14
데이터베이스 변경 l 데이터베이스 변경(modification) – INSERT, DELETE, UPDATE u 삽입 INSERT INTO R(A 1, … , An) VALUES (v 1, … , vn); » 키워드 INSERT INTO, » 릴레이션 이름 R, 괄호로 묶인 애트리뷰트들의 리스트 » 키워드 VALUES, 그리고 튜플 수식 (예) INSERT INTO Star. In(movie. Title, movie. Year, star. Name) VALUES(‘The Maltese Falcon’, 1942, ‘Sydney Greenstreet’); 18
데이터베이스 변경 (계속) (예) INSERT INTO Stars. In VALUES(‘The Maltese Falcon’, 1942, ‘Sydney Greenstreet’); (예) Movie 릴레이션에는 언급되고 있지만, Studio 릴레이션에는 나타 나지 않은 모든 스튜디오를 Studio(name, address, pres. C#) 릴레이션 에 추가하라. INSERT INTO studio(name) SELECT DISTINCT studio. Name FROM Movie WHERE studio. Name NOT IN(SELECT name FROM Studio); F 추가된 튜플의 address와 pres. C# 애트리뷰트에는 NULL 값이 사용 19
데이터베이스 변경 u (계속) 삭제 DELETE FROM R WHERE <조건>; » 키워드 DELETE FROM » 릴레이션 이름, 예를 들어 R, » 키워드 WHERE, 그리고 조건 (예) DELETE FROM Stars. In WHERE movie. Title = ‘The Maltese Falcon’ AND movie. Year = 1942 AND star. Name = ‘Sydney Greenstreet’; 20
SQL에서의 단순 질의 n (계속) SELECT 절의 항목 – 산술 연산자 적용 가능 – 상수 사용 가능 SELECT title, length*0. 0167 AS duration, ‘hrs. ’ AS in. Hours FROM Movie WHERE studio. Name = ‘Disney’ AND year = 1990; 25 title duration Pretty Woman 1. 98334 in. Hours hrs.
SQL에서의 단순 질의 l NULL 값이 사용된 진리표 x 36 (계속) y x AND y x OR y NOT x TRUE FALSE UNKNOWN FALSE TRUE UNKNOWN UNKNOWN FALSE UNKNOWN UNKNOWN
둘 이상의 릴레이션을 포함하는 질의 u SQL 에서 카티션 프로덕트와 조인 – 둘 이상의 릴레이션을 결합하는 간단한 방법 » FROM 절에 각 릴레이션을 나열 (예) Movie (title, year, length, in. Color, studio. Name, producer. C#), Movie. Exec (name, address, cert#, net. Worth) ¡ 영화 Star Wars 의 제작자의 이름을 찾아라. SELECT name FROM Movie, Movie. Exec WHERE title = ‘Star Wars’ AND producer. C# = cert# 40
둘 이상의 릴레이션을 포함하는 질의 (계속) LET the tuple variables in the FROM clause range over relations R 1, R 2, … , Rn; FOR each tuple t 1 in relation R 1 DO 내포된 루프에 따른 FOR each tuple t 2 in relation R 2 DO 알고리즘. . . FOR each tuple tn in relation Rn DO IF the where clause is satisfied when the values from t 1, t 2, … , tn are substituted for all attribute references THEN evaluate the attributes of the select clause according to t 1, t 2, … , tn and produce the tuple of values that results. 44
둘 이상의 릴레이션을 포함하는 질의 (계속) n 관계 대수로의 변환 – FROM 절의 릴레이션들로 카티션 프로덕트 – WHERE 절의 조건으로 선택 연산자 적용 – SELECT 절의 애트리뷰트들로 프로젝션 SELECT name, address FROM Movie, Movie. Exec WHERE title = ‘Star Wars’ AND producer. C# = cert# ð p name, address (s title = ‘Star Wars’ 46 AND producer. C# = cert# (Movie × Movie. Exec))
둘 이상의 릴레이션을 포함하는 질의 (계속) u 질의들의 합집합, 교집합, 차집합 – 키워드: UNION, INTERSECT, EXCEPT (예) 재산이 $10, 000 보다 많고 영화 임원인 모든 여자 스타 들의 이름과 주소를 찾아라. (SELECT name, address FROM Movie. Star WHERE gender = ‘F’ ) INTERSECT (SELECT name, address FROM Movie. Exec WHERE net. Worth > 10000000); 47
부질의 (계속) (예) Movie(title, year, length, in. Color, studio. Name, producer. C#) Stars. In(movie. Title, movie. Year, star. Name) Movie. Exec(name, address, cert#, net. Worth) Harrisonford 가 출연한 영화들의 제작자들의 이름을 찾아라. SELECT name FROM Movie. Exec 중복(duplicates) 없음 WHERE cert# IN (SELECT producer. C# FROM Movie WHERE (title, year) IN (SELECT movie. Title, movie. Year FROM Stars. In WHERE star. Name = ‘Harrison Ford’)); 53
부질의 (계속) (예 – 계속) *내포된 질의는 하나의 select-from-where 문으로 변환 가능 SELECT name FROM Movie. Exec, Movie, Stars. In WHERE cert# = producer. C# AND title = movie. Title AND year = movie. Year AND star. Name = ‘Harrison Ford’; 중복 발생 가능 – FROM 절: 주 질의나 부질의에 사용된 릴레이션들 – WHERE 절: IN은 조인(Join)으로 대체 54
부질의 (계속) (예 – 계속) 중복 발생 (H. F. , t 1, y 1) (t 1, y 1, c 1) (H. F. , t 2, y 2) (t 2, y 2, c 1) Stars. In 55 Movie (c 1, n 1) Movie. Exec
부질의 (계속) u FROM 절에 부질의 사용 – 부질의의 결과 릴레이션으로 튜플 변수 선언 » 이 튜플 변수를 WHERE 절의 조건에 사용 (예) Harrisonford 가 출연한 영화의 제작자를 찾아라. SELECT Name FROM Movie. Exec, (SELECT producer. C# FROM Movie, Stars. In WHERE title = movie. Title AND year = movie. Year AND starname = ‘Harrison Ford’) Prod WHERE cert# = Prod. producer. C#; 57
부질의 (계속) u SQL 조인 수식 (Join expressions) » 조인 수식은 그 자체가 질의일 수도 있고, 또는 » 부질의로도 사용될 수 있다. – CROSS JOIN: 카티션 프로덕트 Movie CROSS JOIN Stars. In – JOIN … ON: 세타 조인 Movie JOIN Stars. In ON title = movie. Title AND year = movie. Year; – NATURAL JOIN: 자연 조인 Movie. Star NATURAL JOIN Movie. Exec 58
부질의 (계속) *FROM 절의 조인 수식 SELECT title, year, star. Name FROM Movie JOIN Stars. In ON title = movie. Title AND year = movie. Year; 59
부질의 (계속) (예) Movie. Star(name, address, gender, birthdate) Movie. Exec(name, address, cert#, net. Worth) Movie. Star NATURAL FULL OUTER JOIN Movie. Exec; name address Mary T. Moore Tom hanks George Lucas 9/9/99 Maple St. ‘F’ 8/8/88 Cherry Ln. ‘M’ Oak Rd. NULL gender birthdate cert# 12345 NULL 23456 networth $100… NULL $200… 스타지만 임원이 아닌 (예: Tom hanks), 또는 임원이지만 스타가 아닌(예: George Lucas) 사람들에 대한 정보도 함께 얻는다. 61
부질의 (계속) (예) Movie FULL OUTER JOIN Stars. IN ON title = movie. Title AND year = movie. Year » On 조건을 만족하지 않는 Movie 튜플과 Stars. In 튜플들도 NULL이 적 절히 사용되어 결과에 포함 63
릴레이션 전체를 다루는 연산 u (계속) 합집합, 교집합, 차집합에서의 중복 » 합집합, 교집합, 차집합 연산들은 기본적으로 중복을 제거 – 중복 제거 방지: 키워드 ALL » R UNION ALL S (SELECT title, year FROM Movie) UNION ALL (SELECT movie. Title AS title, movie. Year AS year FROM Stars. In); » R INTERSECT ALL S » R EXCEPT ALL S 66
릴레이션 전체를 다루는 연산 (예) SELECT COUNT(*) (계속) * 는 튜플 전체를 나타낸다. FROM Movie. Exec; * * 는 집단값 연산자 중 COUNT에만 사용할 수 있는 용법 (예) SELECT COUNT(DISTINCT name) FROM Movie. Exec; (예) SELECT AVG(net. Worth) FROM Movie. Exec; 68
뷰 (계속) n 기본 릴레이션과 뷰를 포함한 질의 (예) Paramount 에서 제작한 영화에 출연한 스타들을 찾아라. SELECT DISTINCT star. Name FROM Paramount. Movie, Stars. In WHERE title = movie. Title AND year = movie. Year; n 여러 릴레이션들로 정의된 뷰 (예) 영화 제목과 그 영화의 제작자로 이루어진 뷰. CREATE VIEW Movie. Prod AS SELECT title, name FROM Movie, Movie. Exec WHERE producer. C# = cert#; 77
뷰 (계속) u 애트리뷰트 이름의 변경 – 뷰에서 애트리뷰트들의 이름을 새로 부여할 수 있다. CERATE VIEW Movie. Prod(movie. Title, prod. Name) AS SELECT title, name FROM Movie, Movie. Exec WHERE producer. C# = cert# 78
뷰 (계속) (예) 뷰 Paramount. Movie에 다음과 같은 튜플을 삽입한다고 하자. INSERT INTO Paramount. Movie VALUES(‘Star Trek’, 1979) – studio. Name 애트리뷰트가 뷰의 애트리뷰트에 포함되어 있지 않으므 로, 뷰에 삽입되는 튜플이 Movie 테이블에 반영될 때 studio. Name값에 는 NULL이 들어간다. 이 튜플은 Paramount. Movie의 조건을 만족하지 않는다. 즉, Paramount. Movie 뷰에 나타나지 않는다. – 따라서 뷰 Paramount. Movie는 다음과 같이 수정되어야 한다: CREATE VIEW Paramount. Movie AS SELECT studio. Name, title, year FROM Movie WHERE studio. Name = ‘Paramount’; 80
뷰 정의 (계속) (예) 갱신가능 뷰로부터 튜플을 삭제 DELETE FROM Paramount. Movie WHERE title LIKE ‘%Trek%’; (예) 갱신가능 뷰에 대한 갱신 UPDATE Paramount. Movie SET year = 1979 WHERE title = ‘Star Trek the Movie’; n 뷰의 삭제 DROP VIEW Paramount. Movie; 81
참고: 뷰의 갱신 가능성 Movie(title, year, length, in. Color, studio. Name, producer. C#) Movie. Exec(name, address, cert#, net. Worth) CREATE VIEW Movie. Prod AS SELECT title, name FROM Movie, Movie. Exec WHERE producer. C# = cert#; l 다음과 같은 튜플을 뷰 Movie. Prod에 삽입하려 한다고 하자: (‘Greatest Show on Earth’, ‘cecil B. De. Mille’) – Movie와 Movie. Exec의 키는 NULL이면 안 된다. – 조인이 이루어지는 애트리뷰트들에 NULL 값이 들어간다. » 두 NULL 값은 동일하지 않음에 주목하라. 82
뷰 (계속) CREATE VIEW Paramount. Movie AS SELECT title, year 뷰 Paramount에 대한 질의 FROM Movie WHERE studio. Name = ‘Paramount’; π title, year σ studio. Name = 84 ‘Paramount’ 이 뷰를 정의하는 질의의 수식 트리
뷰 (계속) SELECT title FROM Paramount. Movie WHERE year = 1979; π title σ year=1979 Paramount. Movie 질의에 대한 수식 트리 π title σ year=1979 π title, year σ studio. Name = ‘Paramount’ Movie 기본 테이블을 사용한 질의의 수식 트리 85 π title σ year=1979 AND studio. Name = Movie 단순화된 질의 ‘Paramount’
- Slides: 86