[Database] Relational Model & Basic SQL | Week 2

Q. 만약 Null value가 겹쳐도 PK가 불가능할까?

A. 불가능

 

Relational Query Languages

 

Structured query language(SQL)과 같은 표현력을 갖는 순수한 언어를 Relational Query Language라고 합니다. 그 중 Relational operators를 사용하는 Relational algebra를 살펴보려합니다.

 

Relational operators는 시용자 쿼리를 표현할 수 있는 6개의 간단한 관계 연산자를 말합니다.

 

Selection of tuples (σ)

 

모든 row(tuple)들을 살피면서 조건에 맞는 row를 선택하여 table로 출력해줍니다. 다른 연산자들과는 달리 조건에 맞는 row를 선택하는 것이기 때문에 출력 table이 모든 attributes들을 갖습니다. 또한 다양한 비교연산자들과 조건연산자들을 사용할 수 있습니다.

Selection of tuples

 

Projection of attributes (∏)

 

relation r의 일부 attribute들을 선택하고 싶은 경우 해당 연산자를 사용합니다. 이는 SQL과 달리 중복을 허용하지 않습니다.

Projection of attributes

 

Union of two relations (∪)

 

두 relations들의 합집합을 구합니다. 이 또한 projection과 마찬가지로 중복을 허용하지 않습니다.

Union of two relations

 

Set difference of two relations (-)

 

두 relations들의 차집합을 구합니다. 참고로 r-s와 s-r의 결과 table은 다릅니다.

Set different of two relations

 

Set Intersection of two relations (∩)

 

두 relations들의 교집합을 구합니다.

Set intersection of two relations

 

Joining two relations - Cartesian Product (x)

 

두 relations들에 존재하는 tuple들의 모든 조합을 table로 출력합니다. 따라서 계산 overhead가 매우 큰 연산입니다. 이때 만약 relation r,s가 공통된 attribute를 갖는다면 Cartesian product x는 두 attribute를 서로 다른 attribute로 간주하여 계산합니다. 예를 들어, relation r,s에 공통된 속성 B가 존재한다면, 각각의 속성을 r.B, s.B도 간주하여 table을 만듭니다.

Joining two relations - Cartesian Product (x)

 

Joining two relations - Natural Join (..)

 

Joining two relations - Natural Join

 

두 relation r, s에 대해서, 공통된 속성 B, D이 있다면 이들을 모아 하나의 table로 출력합니다. cartesian product x와 달리 두 relaiton들에 대해 겹치는 속성 B, D를 같은 속성으로 간주하고 joining합니다.

 

The Rename Operation

 

이전 연산자들을 통해 나온 결과 table E에 대해서 이름을 정하는 연산자입니다. 새로운 이름 x, 출력 테이블 E라고 할때, rename연산자를 ρx(E)로 표현합니다. 다른 rename operation 표현으로, x대신 (attr1, attr2, ..., attrN)으로 나타낼 수 있습니다.

The rename operation

 

Example Queries in Relational Algebra

 

examples...

 

Chapter 3. Introduction to SQL

 

Data Definition Language

 

SQL은 Data-definition language(DDL)로 다음을 포함합니다:

• The schema for each relation
  
  • relation에 대한 schema를 정의합니다.
• The domain of values associated with each attribute
  • 각 속성에 연결된 값들의 domain을 정의합니다.
• Integrity constraints
  • 무결성 조건들을 설정할 수 있습니다.
• And as we will see leter, also other information such as
  • The set of indicies to be maintained for each relations
    • 각각의 관계들을 구성하는 인덱스 구조를 설정할 수 있습니다.
  • Security and authorization information for each relation
    • 각각의 관계의 정보들에 대한 보안과 권한을 설정할 수 있습니다.
  • The physical storage structure of each relation on disk
    • 디스크에 존재하는 각각의 관계들의 실질적인 저장 구조를 설정할 수 있습니다.

 

Domain types in SQL

 

SQL을 통해 attribute들의 domain type을 설정할 수 있습니다.

• char(n): 고정된 길이의 character string입니다. 사용자는 최대 길이 n을 설정할 수 있습니다.
• varchar(n): 가변 길이의 character string입니다. 사용자는 최대 길이 n을 설정할 수 있습니다. 만약 n을 넘지 않는다면 n-byte를 차지하지 않고 실제 string길이만큼의 저장공간만을 사용합니다.
• int: integer
• smallint: Small integer
• numeric(p,d): 고정 소수점 표기법입니다. 총 p자리 digiti을 허용하며, d자리 소수점 이하부분의 정확도를 갖는 소수로 설정합니다. 예를 들어, numeric(8,2)는 123456.78 입니다.
• real: floating point number
• float(n): floating point number. 사용자는 부동 소수점에서 가수부분을 담당하는 bit수를 n으로 설정할 수 있습니다.

https://learn.microsoft.com/ko-kr/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver16

float 및 real(Transact-SQL) - SQL Server

 

Create Table Construct

 

SQL을 통해 relation을 생성하려면 다음과 같은 command가 필요합니다:

create table instructor(
    ID char(5),
    name varchar(20),
    dept_name varchar(20),
    salary numeric(8,2));

 

create table 을 통해 table을 만듭니다.

이때 instructor 는 생성하는 table의 이름입니다. 그 아래로 각각 attribute들과 domain type을 적어줍니다.

 

Integrity Constraints in Create Table

 

Integrity constraint들의 종류는 primary key (A1, ..., An)와 foreign key (Am, ..., An) references r, 그리고 not null이 있습니다. SQL은 해당 integrity 조건들을 만족시키기 위해 database가 업데이트될 때마다 확인합니다.

create table instructor(
    ID char(5),
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8,2),
    primary key (ID),
    foreign key (dept_name) references department);

 

이때 primary key를 설정함은 index처리를 위해서이고, foreign key는 foregin key에서 사용하는 attr의 데이터가 reference하는 table에 존재해야한다는 제약조건을 주기 위함입니다(즉, 아무 dept_name이나 허용하지 않겠다는 의미). 만약 reference하는 테이블의 attr이름과 만들려는 테이블의 attr의 이름이 다르다면 따로 명시해줘야합니다.

 

Updates to tables

 

table을 업데이트하는 SQL문법입니다:

• Insert: table에 tuple을 삽입하는 문법입니다. 이때 삽입하는 테이블의 attr순서와 동일한 attr순서를 갖는 튜플을 삽입해야합니다. 만약 그렇지 않으려면 attr의 이름을 명시해줘야합니다.
  
  • ex. insert into instructor values ('10211', 'Smith', 'Biology', 66000);
• Delete: 해당 테이블에 존재하는 모든 튜플을 삭제합니다. 단 테이블은 남아있습니다. 만약 일부 튜플만을 삭제하고자 하면 where을 뒤에 붙여야합니다.
  • ex. delete from student
• Drop table: 데이블 전체를 삭제합니다.
  • ex. drop table r
• Alter: table의 attr을 수정합니다.
  • alter table r add A D: 테이블 r에 D 도메인 타입을 갖는 속성 A를 추가합니다. 모든 튜플들의 새롭게 추가된 속성값은 null로 초기화됩니다.
  • alter table r drop A: 테이블 r에 존재하는 속성 A를 삭제합니다. 하지만 속성 A와 연관되어있는 테이블이 많을 수 있으므로 조심해서 사용해야합니다.

 

Basic Query Structure

 

일반적인 SQL문은 다음과 같습니다:

select A1, A2, ..., An
from r1, r2, ..., rm
where P

 

먼저 r1, r2, ..., rm에서 ,는 cartesian product를 의미하며 r1, r2, ..., rm의 테이블들을 하나의 테이블로 joining한 후, P 조건에 맞는 tuple들을 선택합니다. 그 후 A1, A2, ..., An에 해당하는 속성들만을 포함하여 결과 테이블을 만듭니다.

 

이때, SQL은 대소문자를 구분하지 않습니다. 즉 NAME = Name = name입니다.

 

또한 SQL은 relational algebra와 달리 중복을 허용합니다. 만약 중복을 허용하지 않고 select를 수행하려면 distinct를 추가해야합니다. 만약 중복 허용을 명시적으로 표시하고자 한다면 all을 추가합니다.

select distinct dept_name
from instructor

select all dept_name
from instructor

 

만약 모든 attr들을 선택하고자 한다면 모든 속성들의 이름을 적어도 되지만, *만 적어도 충분합니다.

select *
from instructor

 

참고로 attr위치에 리터럴값을 적는다면, '437'이라는 하나의 행, 하나의 열로 이루어진 테이블이 출력됩니다. 또한 어떤 테이블에 대해 attr위치에 리터럴 값을 적는다면 해당 테이블의 tuple수만큼의 행을 갖는 리터럴 값으로 이루어진 테이블을 출력합니다.

 

The Where Clause

 

where절은 결과 table이 반드시 지켜야하는 조건을 명시해줍니다.

 

다음과 같은 질의가 있는 경우 다음과 같은 SQL을 필요로 합니다:

To find all names of instrutors in Comp. Sci dept

select name
from instructor
where dept_name = 'Comp. Sci.'

 

이때 where절에는 and, or, not과 같은 논리 연산자나 <, <=, >, ... 등등의 비교 연산자를 사용할 수 있습니다.

그래서 다음과 같은 질의가 있는 경우 다음과 같은 SQL을 필요로 합니다:

To find all names of instructors in Comp. Sci. dept with salary > $70,000

select name
from instructor
where dept_name = "Comp. Sci.' and salary > 70000

 

The From Clause

 

from절은 질의와 관련된 관계들을 이어주는 역할을 수행합니다.

이때 사용하는 ,는 cartesian product를 의미합니다.

 

만약 사용하는 속성이 from에서 사용하는 두 관계에서 공통적으로 나타나는 속성이라면, cartesian product가 그랬듯이 관계의 이름을 따서 renaming됩니다(e.g., instructor.ID).

 

그래서 다음과 같은 질의가 있는 경우 다음과 같은 SQL을 필요로 합니다:

Find the names of all instructors in the Art department who have taught some course and the course_id(Given: instructor (ID, name, dept_name, salary), teaches (ID, course_id, dept_name))

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and instructor.dept_name = 'Art'

 

결과적으로 필요한 attr.은 name과 course_id이고, 이 두 attr.은 서로 다른 테이블의 속성이기 때문에 joining이 필요합니다. 이때 joining의 방법으로 natural joining도 있는데 왜 계산 비용이 높은 cartesian을 사용했을까?

 

만약 겹치는 attr.이 name밖에 없었다면 가능했을 수도 있지만, dept_name도 겹치기 때문에 natural joining을 사용하면 안됩니다.

 

The Rename Operation

 

SQL은 테이블이나 속성을 as절을 통해 renaming가능합니다(참고로 테이블을 renaming하려면 alter table table_name rename old_name to new_name을 통해 가능합니다).

 

예를 들어 급여를 12로 나눠서 월급을 하나의 attr로 사용하고 싶은 경우 다음과 같은 SQL을 사용합니다:

select ID, name, salary/12 as monthly_salary
from instructor

 

as 키워드를 빼고 old_name new_name으로 할 수도 있지만, 이는 권장하지 않습니다.

 

String Operations

 

SQL은 다양한 string관련 패턴을 제공합니다.

• percent(%): %는 공백을 포함한 임의의 문자열을 의미합니다.
• underscore(_): _는 공백을 포함하지 않는 하나의 문자를 의미합니다.
• like: like 연산자를 통해 패턴을 사용할 수 있습니다.

 

그래서 다음과 같은 질의가 있는 경우 다음과 같은 SQL을 사용해야 합니다:

Find the names of all instructors whose name includes the substring "dar"

select name
from instructor
where name like '%dar%'

 

만약 패턴으로서가 아닌 진짜 문자 %나 _를 사용하고 싶다면 \를 사용해야합니다(\%, \_).

 

패턴 매칭에 대한 예시입니다:

• 'Intro%' 는 Intro로 시작하는 임의의 문자열을 의미합니다.
• '%Comp%' 는 Comp가 들어있는 임의의 문자열을 의미합니다.
• '___' 는 공백을 포함하지 않는 임의의 문자 3개를 의미합니다.
• '___%' 는 최소 3개의 문자가 있어야함을 의미합니다.

 

또한 SQL은 다양한 문자열 연산자를 제공합니다.

• concatenation (using "||")
• 대소문자로 변경
  • UPPER(), LOWER()
• 문자열의 길이나 부분 문자열 추출 등..
  • LENGTH(), SUBSTRING(str, position, length)

 

Ordering the Display of Tuples

 

order by연산자를 통해 기본 오름차순을 기준으로 튜플들을 정렬할 수 있습니다.

select distinct name
from instructor
order by name

 

만약 명시적으로 오름차순 정렬을 나타내고자 한다면 asc를 내림파순 정렬을 하고싶으면 desc를 명시해주면 됩니다(e.g., order by name desc).

 

또한 여러 개의 속성들을 기준으로 정렬할 수도 있습니다. 이때는 왼쪽의 속성을 기준으로 먼저 정렬 후 오른쪽의 속성으로 정렬을 진행합니다(e.g., order by dept_name, name).

 

Where Clause Predicates

 

SQL은 between이라는 비교연산자를 사용합니다. 예를 들어 $90,000과 $100,000사이의 연봉을 갖는 튜플들을 찾고싶을 때 between연산자를 사용하지 않으면 >= 90000 and <= 100000 이지만, between 연산자를 사용하면 다음과 같이 표현할 수 있습니다.

select name
from instructor
where salary between 90000 and 100000

 

또한 SQL은 튜플 비교도 가능합니다.

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology')

 

Set Operations

 

집합 연산자(union, intersect and except)도 사용할 수 있습니다.

이때 집합 연산자는 기본적으로 중복을 허용하지 않습니다. 만약 중복을 허용하고 싶다면 all을 붙여야합니다(union all, intersect all and except all).

 

그래서 다음과 같은 질의가 있는 경우 다음과 같은 SQL을 사용합니다:Fine courses that ran in Fall 2017 or in Spring 2018

(select course_id from section where sem = 'Fall' and year = 2017)
union
(select course_id from section where sem = 'Spring' and year = 2018)

 

Fine courses that ran in Fall 2017 but not in Spring 2018

(select course_id from section where sem = 'Fall' and year = 2017)
except
(select course_id from section where sem = 'Spring' and year = 2018)

 

Null values

 

not null로 domain을 설정하지 않았다면 null value는 모든 attr.에 들어갈 수 있는 값입니다.

null은 데이터가 없다는 것을 의미하지 않고 데이터가 있지만 그 값을 모른다로 접근하는 것이 옳습니다.

 

그래서 null + 5의 결과는 null입니다. 왜냐하면 알 수 없는 값에 5를 더해도 그 값은 알 수 없기 때문입니다.

이러한 null의 정의 때문에 논리 연산자나 비교 연산자에서 다음과 같은 결과를 출력합니다:

 

하지만 is null은 유의미한 결과를 도출합니다. 그 값이 알 수 없는 값인지 아닌지는 알 수 있기 때문입니다.

Find the names of all instructors whose salary is null

select name
from instructor
where salary is null

 

Aggregate Functions

 

Aggregation Functions

 

위 그림에 있는 집계 함수(Aggregate Function)들 말고도 다양한 집계 함수를 SQL에서 사용할 수 있습니다.

 

다음과 같은 질의가 있는 경우 다음과 같은 SQL이 필요합니다:

Find the average salary of instructors in the Computer Science dapartment

select avg(salary)
from instructor
where dept_name = 'Comp. Sci.'

 

Find the total number of instructors who teach a course in the Spring 2018 semester

select count(distinct ID)
from instructor
where semester = 'Spring' and year = 2018

 

Find the number of tuples in the course relation

select count(*)
from course

 

Aggregate Functions - Group By

 

group by연산자를 통해 공통으로 묶고 싶은 속성들을 묶어 연산을 진행합니다.

Find the average salary of instructors in each department

select dept_name, avg(salary)
from instructor
group by dept_name

Group by

 

Group by

 

만약 select를 하는 attr.가 group by를 통해 나눠놓은 테이블당 하나로 집계가 되지 않는 속성인 경우 error가 발생할 수 있습니다. 위 그림과 같은 경우에 Job으로 group by를 한 후 select mfd_id를 한다면 error가 발생할 것입니다(avg(mfd_id)는 가능함. 하나의 데이터로 집계되니까).

 

만약 group by로 뭉쳐놓은 테이블에 대해 조건을 걸고 싶다면 having절을 사용하면 됩니다.

Find the names and average salaries of all departments whoes average salary is greater than 42000

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000

 

한편 where절 또한 조건을 건다는 점에서 having절과 유사한데,  having절은 어떤 것이 다를까요?

 

where절의 경우 group by를 하기 전에 조건을 적용한다면, 

having절의 경우 group by를 한 후 조건을 적용한다는 점이 다릅니다. 일반적으로 group by를 하는 경우에는 having절을 사용합니다.

 

Null values and Aggregates

 

만약 모든 급여의 합을 구하고 싶은 경우

select sum(salary)
from instructor

 

로 구할 수 있는데, 이때 salary attr.에 null value가 있다면 집계함수의 결과로는 적용되지 않습니다.

 

count(*)를 제외한 모든 집계 함수들은 null value를 무시합니다. count(*)은 테이블의 전체 튜플의 계수를 알려준다는 점에서 null value를 무시하지 않습니다.