[DB #06] Basic SQL (Structural Query Language) - Definition and Data Type

이석환·2023년 10월 20일
Database
0

Database

목록 보기
7/12
post-thumbnail

Background

  • tuple calculus 유래
  • Originally called SEQUEL (Structured English Query Language)
  • 현재, RDMS의 표준

1. SQL DATA DEFINITION AND DATA TYPES

1-1. Schema and Catalog Concepts in SQL

  • SQL schema
    • Called database in some systems
    • 동일한 DB에 속하는 table + relation을 그룹화하는 기능
    • schema name(E.g. university)로 식별된다.
      • schema를 소유하고 있는 user or account를 나타내는 authorization identifier가 존재한다.
      • schema 내의 각 요소에 대한 descriptor도 포함한다.
  • Schema elements(constructs) include
    • Table(relations in the formal terms), constraints, views, domains, and other consturcts
  • SQL에 statement는 일반적으로 semicolon으로 끝난다.
    • 이건 Oracle에는 적용되지만, RDBMS에서 항상은 아니다.

  • How to create a schema ?
    • CREATE SCHEMA
      • schema의 모든 element의 정의를 포함할 수 있다.
      • [EXAMPLE] CREATE SCHEMA COMPANY AUTHORIZATION 'Joshua'
  • 모든 User가 schema와 schema element를 만들 수 있는 권한이 있는 건 아니다.
    • DBA에게 권한이 있다.
  • Catalog
    • a named collection of schemas
    • 항상 특별한 schema를 포함하고 있다. (called INFORMATION_SCHEMA)
      • catalog에 모든 schema의 정보와 이러한 schema들의 모든 element descriptors를 제공한다.
    • Integrity constraints는 relations가 동일한 catalog에 존재하는 경우에 정의된다.
    • 동일한 schema에서 schemasms xmrwjd elements를 공유할 수 있다.
      • type/domain definitions
    • 다른 schema 참조 시에 catalog를 사용한다.

1-2. The CREATE TABLE Command in SQL

  • Specofoes (defines) a new relation, say R
    • Giving R to a name
    • R의 attribute, their types(domains), and initial constraints를 정의한다.
      • attribute는 처음으로 정의되어진다.
      • 각 attribute는 name, data type, constraints(NOT NULL, UNIQUE)가 주어진다.
  • Key, entity integrity, and referential integrity constraints는 attribute declaration 이후에 정의될 수 있다.
    • constraints는 추후에 ALTER TABLEStaement를 통해 추가될 수 있다.
  • Can optionally specify "schema"
    • CREATE TABLE COMPANY.EMPLOYEE ... (explicit)
    • Or simply, CREATE TABLE EMPLOYEE ... (implicit)

Base tables(base relations)
CREATE TABLE

  • Tables(relations) and its rows(tuples)은 physically and actually하게 DBMS에 의해 FILE로써 생성되고 저장된다.

Virtual relations(views)
CREATE VIEW

  • 어떤 physical file과 일치하거나 일치하지 않을 수 있다.
    • view에 속한 relevant tuples는 그들이 materilized(물리적 구체화)가 되지 않는 이상 view가 참조할 때만 계산된다.
  • Why needed ?
    • Space-saving
      • but, 계산 시간이 오래 걸리면 table화가 이득일 때가 있다.
    • light computation
    • complex join
      • -> simple table
    • 권한 분리를 위함

1-3. Recall the COMPANY Relational Schema

1-4. One Possible Database State


1-5. SQL CREATE TABLE Data Definition Statements for Defining a COMPANY Schema


  • Some foreign keys may cause errors. Why ?
    • Caicular references (순환 참조) 발생
      • E.g. EMPLOYEE table의 Super_ssn
    • Or, 참조할 Table이 아직 생성되지 않았을 때 발생
      • EMPLOYEE table의 DNO는 DEPARTMENT을 참조하고 있지만, 아직 DEPARTMENT가 생성되지 않은 경우
  • How to handle ?
    • ALTER TABLE
    • 만약 필요하다면 primary key는 후에 정의할 수 있다.

1-6. Attribute Data Types and Domains in SQL

1-6-1. Numeric data types

  • Integer numbers of various size : INTEGER, INT, and SMALLINT
    • In Oracle, NUMBER (DECIMAL로 대체 가능)
  • Floating-point (real) numbers: FLOAT, or REAL, and DOUBLE
    PRECISION
  • For formatted numbers(정형화된 숫자) can be declared by using DECIMAL(i, j) (or
    DEC(i, j)or NUMBERIC(i, j))
    • i: the precision, the total number of decimal digits
    • j: the scale, or the number of digits after the decimal point.
      • The default for scale: 0, the default for precision: implementation-specific.
    • [EXAMPLE] DEC(6,2) = 1234.56

1-6-2. Character-string data types

  • Fixed length : CHAR(n), CHARACTER(n)
    • n : 최대 문자 길이
    • 왼쪽부터 문자를 채운다.
    • 빈 공간은 공백
  • Variable length : VARCHAR (n), CHAR VARYING(n), CHARACTER VARYING(n)
    • n은 4K(4096 bytes)까지로 한정되어 있다.
    • CLOB(CHARACTER LARGE OBJECT): "documents"같이 대량의 문자열 데이터를 저장하는 데 사용
      • SIZE : Kilobytes (K), megabytes (M), or gigabytes (G)
      • [EXAMPLE] CLOB (20M) : up to 20MBytes

1-6-3. Bit-string data types

  • Fixed length : BIT(n)
    • n : the maximum number of bits, 4K(4096 bytes)로 한정되어 있다.
    • 값을 지정하기 위해, 다음과 같은 포맷을 따른다.
      • B10101
      • Why ?
        • 일반 문자열과 구분짓기 위해 B로 bit임을 나타내고, ``안에 있는 값을 사용한다.
  • Varying lenght : BIT VARYING(n)
    • BLOB(BINARY LARGE OBJECT): 실행가능한 코드나 java object와 같이 매우 큰 binary 값을 위해 사용
      • SIZE : Kilobytes (K), megabytes (M), or gigabytes (G)
      • [EXAMPLE] BLOB (20M) : up to 20MBytes

1-6-4. Boolean, Date, Time

  • Boolean
    • Values of TRUE or FALSE or NULL
  • Date
    • Ten positions (10자리)
      • Components : YEAR, MONTH, and DAY in the form YYYY-MM-DD
      • E.g. DATE ‘2014-09-27’
        • In Oracle, to_date() 함수를 이용하여 전달받은 인자를 날짜형으로 변환한다.
  • *Time
    • At least eight positions (최소 8자리)
      • Components : HOUR, MINUTE, and SECOND in the form HH:MM:SS
      • E.g., TIME '09:12:47'
  • Date and Time data types에서 유효한 days와 times만 accept될 수 있다.
    • '<', '>' 로 date/time 비교 연산이 가능하다.
    • e.g., '2018-10-02' < start_date

1-6-5. Timestamp, Interval

  • Timestamp

    • Includes the DATE and TIME fields
      • seconds(초)의 소수점 이하 자릿수에 대해 최소 6자리를 저장할 수 있다.
      • Optional WITH TIME ZONE (시간대 정보를 포함한 날짜 및 시간 값을 저장할 수도 있고 안 할 수도 있다)
      • E.g. TIMESTAMP 2014-09-27 09:12:47.648302

  • INTERVAL

    • Data, Time, Timestamp의 절대값을 증가 또는 감소시키는데 사용할 수 있는 상대값을 정의
    • time의 기간을 저장하는 데에 사용한다.
      • E.g., INTERVAL '40' MONTH = INTERVAL '3-4' YEAR TO MONTH (3년 4개월)
      • YEAR TO MONTH : 연과 월의 기간을 저장하는 type
      • DAY TO SECOND : 일과 시간, 분, 초를 저장하는 type

1-6-6. Domain

  • 공통의 Domain을 지정한다.
  • column마다 data type을 특정하는 것 대신에 선언될 수 있다.
    • 특정 열이나 속성의 데이터 유형을 정의할 때 해당 도메인을 참조해서 동일한 데이터 유형이 여러 열에서 사용될 때 유용하며, 데이터 유헝을 한 번 정의하고 나면 여러 곳에서 재사용할 수 있다.
  • Name은 attribute specification와 함께 사용될 수 있다.
  • Advantages
    • domain에 대한 data type을 바꾸는 걸 더 쉽게 해준다.
      • [EXAMPLE] postal code
    • schema 가독성을 증가시킨다.
  • [EXAMPLE] (equivalent to CREATE TYPE in Oracle 11g/12/19c)
    • - CHAR, NUMBER와 같은 basic data types 대신에 사용한다.
    • EMPLOYEE의 Super_ssn, DEPARTMENT의 Mgr_ssn, WORKS_ON의 Essn, DEPENDENT의 Essn와 같이 동일한 data type을 가지는 attribute에서 재사용할 수 있다.

1-6-7. Type

  • 객체를 저장하는 용도로 사용
  • UDTs(user defined types)를 생성할 때 사용될 수 있다.
  • 객체 지향 application을 지원한다.
  • command에서 사용할 수 있다. (supported in Oracle)
  • CREATE TYPE 구문으로 생성한다.

2. SPECIFYING CONSTRAINTS IN SQL

2-1. Specifying Constraints in SQL

  • As a reminder, basic constraints

    • Key constraint : A primary key value CANNOT be duplicated
      • 중복될 수 없다.
    • Entity intergrity constraints
      • NULL일 수 없다.
    • Referential integrity constraints
      • foreign key는 참조하는 table의 PK를 가져야 한다.
      • 또는 NULL값을 가져야 한다.

  • SQL에서 이러한 constraint를 특정하는 방법

    • USE appropriate reserved words
      • PRIMARY KEY (Key constraints + UNIQUE)
      • NOT NULL (Entity integrity constraints)
      • FOREIGN KEY (Referential integrity constraints)

2-2. Specifying “Attribute” Constraints

  • Default value of an attribute
    • DEFAULT clause
    • NULL
      • 특정 attribute에 대해서는 적용되지 않는다.
      • [EXAMPLE] Primary key에 NOT NULL 사용이 안 됨.
    • CHECK clause
      • Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 10)
      • Age INT NOT NULL CHECK (Age >= 0 and Age < 65)
      • DBMS가 제약조건에 대한 충족 여부를 확인해 준다. -> 조건에 만족하면 insert가능
      • schema-based constraint에 의해 지원된다.

2-3. Specifying "Key" Constraints

  • PRIMARY KEY clause
    • relation의 PK를 구성하는 하나 이상의 attribute를 특정한다.
  • UNIQUE clause
    • relation model에서 candidate key로 물리는 alternate(secondary) keys를 특정한다.
    • 중복되면 안 되는 attribute이다.

2-4. Specifying “Referential Integrity” Constraints

  • FOREIGN KEY clause
    • Default operation : Reject update (including delete/insert) on violation
    • Attach referntial triggered action clause (참조 트리거 액션 절 구문 추가)
    • Options include : SEL NULL | CASCADE | SET DEFAULT
      • 오라클은 SET DEFAULT를 지원하지 않는다.
    • SET NULL or SET DEFALUT를 처리하는 과정은 ON DELETE and ON UPDATE와 동일하다.
    • CASCADE는 참조하는 모든 tuple을 삭제한다.
      • 일관성을 유지하는 것이 중요한 relationship relation에 적절하다.

2-5. Giving Names to Constraints

  • By CONSTRAINT keyword
    • Name a (specific) constraint : useful for later altering
    • [EXAMPLE]
      • CONSTRAINT reg_no_u UNIQUE(Starte,NUmber)
      • dno CONSTRAINT fk_drptno REFERENCES Department (Dnumber)

2-6. Specifying Constraints on Tuples Using CHECK

  • relation에 있는 각 tuple에 추가적인 constraints를 적용하려면 CHECK를 사용하여 특정하면 된다.
  • CHECK는 CREATE TABLE문 끝에 위치한다.
    • 각 개별 tuple에 적용한다. (row-based constraints)
      • tuple이 삽입 or 수정될 때마다 check 된다.

2-7. Example of Specifying Constraints

Reference
Database System Concepts | Abraham Silberschatz
데이터베이스 시스템 7th edition

profile
이석환
반갑습니다.
이전 포스트

[DB #05] Relational Database Design by ER-to-Relational Mapping

다음 포스트

[DB #07] Basic SQL (Structural Query Language) - Query

0개의 댓글

관련 채용 정보