q 1 Fname Minit Dno4 EMPLOYEE Lname Ssn

  • Slides: 31
Download presentation

q 예제 1 Fname Minit : σDno=4 (EMPLOYEE) Lname Ssn Bdate Address Sex Salary

q 예제 1 Fname Minit : σDno=4 (EMPLOYEE) Lname Ssn Bdate Address Sex Salary Super_ssn 987654321 Dno Alicia J Zelaya 999887777 1968 -01 -19 3321 Castle, Spring, TX F 25000 Jennifer S Wallace 987654321 1941 -06 -20 291 Berry, Bellaire, TX F 43000 888665555 4 Ahmad V Jabbar 987987987 1969 -03 -29 980 Dallas, Houston, TX M 25000 987654321 4 q 예제 2: σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary > 30000) (EMPLOYEE) 4

Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno 5 John B Smith

Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno 5 John B Smith 123456789 1965 -01 -09 731 Fondren, Houston, TX M 30000 333445555 Franklin T Wong 333445555 1955 -12 -08 638 Voss, Houston, TX M 40000 888665555 5 Ramesh K Narayan 666884444 1962 -09 -15 M 38000 333445555 5 Joyce A English 453453453 1972 -07 -31 5631 Rice, Houston, TX F 25000 333445555 Fname 975 Fire. Oak, Humble, TX Lname Salary John Smith 30000 Franklin Wong 40000 Ramesh Narayn 38000 Joyce English 25000 5

l 결과 릴레이션에 나타나는 애트리뷰트들의 이름을 재명명 (rename)할 수도 있다. TEMP ←σDno=5 (EMPLOYEE) R(First_name,

l 결과 릴레이션에 나타나는 애트리뷰트들의 이름을 재명명 (rename)할 수도 있다. TEMP ←σDno=5 (EMPLOYEE) R(First_name, Last_name, Salary) ← Fname, Lname, Salary(TEMP) 또는 Temp 2 ← Fname, Lname, Salary(TEMP) R← (First_name, Last_name, Salary) (TEMP 2)

q 예) DEPARTMENT×EMPLOYEE DEPARTMENT DNAME DNUMBER MGRSSN EMPLOYEE …. . . FNAME. . .

q 예) DEPARTMENT×EMPLOYEE DEPARTMENT DNAME DNUMBER MGRSSN EMPLOYEE …. . . FNAME. . . SSN …. . . DNO Research 5 333445555 …. . . John …. . . 123456789. . . 5 Research 5 333445555 …. . . Franklin. . . 333445555 …. . . 5 …… …… …. . . Research 5 333445555 …. . . James . . . 888665555. . . … 1 Administration 4 987654321 …. . . John . . . 123456789. . . … 5 …… …… …… Administration …… Headquarters …… 4 …… 1 …… …… …. . . 987654321 …… Jennifer …… 987654321 …… …… 888665555 …. . . James …… …… 4 …… …. . 888665555. . . … 1

q σMgr_ssn=Ssn(DEP_EMP) Dname Dnumber Mgr_ssn …. . . Fname. . . Ssn …… Dno

q σMgr_ssn=Ssn(DEP_EMP) Dname Dnumber Mgr_ssn …. . . Fname. . . Ssn …… Dno Research 5 333445555 …. . . Franklin. . . 333445555 …. . . 5 Administration 4 987654321 …… Jennifer …… 987654321 …… 4 Headquarters 1 888665555 …. . . James 1 . . . 888665555. . . …

q 예제: 모든 여자 사원들에 대하여 그들의 부양 가족들의 이 름 검색 FEMALE_EMPS ←

q 예제: 모든 여자 사원들에 대하여 그들의 부양 가족들의 이 름 검색 FEMALE_EMPS ← σSex=‘F’(EMPLOYEE) EMPNAMES ← Fname, Lname, Ssn(FEMALE_EMPS) EMP_DEPENDENTS ← EMPNAMES× DEPENDENT ACTUAL_DEPENDS ← σSsn=Essn(EMP_DEPENDENTS) RESULT ← Fname, Lname, Dependent_name(ACTUAL_DEPENDS)

l 예제: 모든 부서의 이름과 그 부서의 관리자의 이름 검색 DEPT_MGR ← DEPARTMENT Mgr_ssn=Ssn.

l 예제: 모든 부서의 이름과 그 부서의 관리자의 이름 검색 DEPT_MGR ← DEPARTMENT Mgr_ssn=Ssn. EMPLOYEE RESULT ← Dname, Lname, Fname(DEPT_MGR) l 조인 연산은 실렉트가 따라오는 카티션 프로덕트와 유사 TEMP_DEPT_MGR ← DEPARTMENT× EMPLOYEE DEPT_MGR ← σMgr_ssn=Ssn(TEMP_DEPT_MGR)

l EQUIJOIN q 조인 조건에서 동등 비교(equality comparison) 만 을 사용하는 조인 q EQUIJOIN

l EQUIJOIN q 조인 조건에서 동등 비교(equality comparison) 만 을 사용하는 조인 q EQUIJOIN 사용 예제: 모든 DEPARTMENT의 이름과 그 관리자의 이름을 검색하라: T ← DEPARTMENT Mgr_ssn=Ssn EMPLOYEE RESULT ← ΠDname, Fname, Lname(T)

COMPANY schema EMPLOYEE FNAME MINIT LNAME SSN BDATECOMPANY schema EMPLOYEE FNAME MINIT LNAME SSN BDATE
Select 22 DNO4 EMPLOYEE SALARY30000 EMPLOYEE DNO4 ANDSelect 22 DNO4 EMPLOYEE SALARY30000 EMPLOYEE DNO4 AND
Select 22 DNO4 EMPLOYEE SALARY30000 EMPLOYEE DNO4 ANDSelect 22 DNO4 EMPLOYEE SALARY30000 EMPLOYEE DNO4 AND
ERD 12 Fname Minit ER Lname Number NERD 12 Fname Minit ER Lname Number N
ERD 12 Fname Minit ER Lname Number NERD 12 Fname Minit ER Lname Number N
Fname Minit Lname Number N Address Sex WORKSFORFname Minit Lname Number N Address Sex WORKSFOR
Fname Minit Lname Number N Address Sex WORKSFORFname Minit Lname Number N Address Sex WORKSFOR
ERD 12 Fname Minit ER Lname Number NERD 12 Fname Minit ER Lname Number N
O p of Q 18 SSN Fname LnameO p of Q 18 SSN Fname Lname
17 1 SQL 1 SQL SELECT LNAME FNAME17 1 SQL 1 SQL SELECT LNAME FNAME
public class Student private String AM Fname Lnamepublic class Student private String AM Fname Lname
La Medicina Legale nel SSN per il SSNLa Medicina Legale nel SSN per il SSN
CDM and SSN A winning combination SSN andCDM and SSN A winning combination SSN and
CREATE TABLE 16 SQL99 CREATE TABLE EMPLOYEE FNAMECREATE TABLE 16 SQL99 CREATE TABLE EMPLOYEE FNAME
13 1 COMPANY DB DB EMPLOYEE ENAME SSN13 1 COMPANY DB DB EMPLOYEE ENAME SSN
9 1 COMPANY DB DB EMPLOYEE ENAME SSN9 1 COMPANY DB DB EMPLOYEE ENAME SSN
13 1 COMPANY DB DB EMPLOYEE ENAME SSN13 1 COMPANY DB DB EMPLOYEE ENAME SSN
EMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER DEPARTMENT DNAMEEMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER DEPARTMENT DNAME
6 EMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER DEPARTMENT6 EMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER DEPARTMENT
EMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER DEPARTMENT DNAMEEMPLOYEE ENAME SSN BDATE ADDRESS DNUMBER DEPARTMENT DNAME
9 1 COMPANY DB DB EMPLOYEE ENAME SSN9 1 COMPANY DB DB EMPLOYEE ENAME SSN
Harris Employee Services Portal Employee Services Portal EmployeeHarris Employee Services Portal Employee Services Portal Employee
Employee Engagement What is Employee Engagement Employee EngagementEmployee Engagement What is Employee Engagement Employee Engagement
SIUC NEW EMPLOYEE ORIENTATION EMPLOYEE BENEFITS 1 EMPLOYEESIUC NEW EMPLOYEE ORIENTATION EMPLOYEE BENEFITS 1 EMPLOYEE