DBMS - (3) Querying Relational Databases

DBMS - (3) Querying Relational Databases

Steps in creating and using a RDBMS
(1) Design schema; create using DDL
(2) 'Bulk load' initial data
(3) Repeat: execute queries and modifications

Queires in high-level language
- All students with GPA > 3.7 applying to Stanford and MIT only.
- All engineering department in CA with <500 applicants.
- College with highest average accept rate over last 5 years.

Query language의 특성
(1) Some easy to pose: some a bit harder(원하는 바를 표현하기 쉽다)
(2) Some easy for DBMS to execute efficienty: some harder(DBMS가 수행하기 성능상 유리하다)
(3) 'Query language' also used to modify data
(4) Queryies return relations('compositional', 'closed')
- compositional? 서로 다른 두개이상의 relation을 조인(join)하여 새로운 relation을 생성할 수 있음
- closed? 수학적으로 연산에 대해 닫혀있다는 뜻으로 이해

Query language
(1) Relational Algebra
- formal language
(2) SQL
- actual language

Example
문제로 주어지는 쿼리문 예시
IDs of students with GPA > 3.7 applying to stanford

Relational algebra
π(ID)σ(GPA>3.7)^Apply.college='stanford'(student⋈Apply)

SQL
Select Student.ID from Student, Apply where Student.ID=Apply.ID and Student.GPA>3.7 and Apply.college='stanford'

Reference
https://www.youtube.com/watch?v=nf1-h2GpEGc&list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3&index=3