CodeDrive

Lecture 3

Basic

• Overview of CNNs
  • A special case of MLP
  • Commonly apply to visual object or 2D array input
  • Unified feature extractor and classifier in one network
  • Filter kernel = weights in between layers
  • Feature map = filtered outputs

• How can compute Feature Map Size?

  

  • i = input size, k = filter size(kernel), s = stride, o = output feature map size

  • if o<i, can occur some problem

    • then, we can use Zero Padding

      

• At same points, we can apply multiple filters. We call it "channel"

Two Characteristics of CNNs

1. Local Connectivity - image is locally correlated
2. Weight Sharing- overcome enormous weights problem

Local Connectivity

• MLP : Weights are fully connected, ie. MLP use all weights
• CNNs : One neuron will connect to filter size chunk , thus only have 5x5x3 weights

Receptive Field(RF)

How much a convolution window "see" on it's input image or part of the image that is visible to one filter at a time

In upper figure, dark pink square in left light pink square(32x32x3) is Receptive Filed.

It means "Receptive Filed size == filter size"

• How the later feature map "see" the image?

Weight Sharing

• MLP has different weights for every single neuron.
  • Total # of weights = (filter size) x (Input_w x Input_h x # of feature maps) = too many!
• CNN has same weight for every single feature map
  • Total # of weights = (filter size) x # of feature maps = small than MLP

Max Pooling

Pooling does resize the activation map from convolution to get new layer.

• Output size (o)
• Input size (i)
• Pooling window size (k)
• Stride (s)

Fully Connected (FC) layer

• FC layer started right after the last convolution or pooling layer
• Flatten pooling layer = input layer of MLP
• Last FC layer = output layer of ConvNet. It uses several tasks like classification.

Summary

Typical ConvNet process : [CONV layer - ReLU-Pooling] x N - [FC-ReLU] x M - FC - (softmax or sigmoid)

• Parameter in CNN

  • Conv layer
    • Filter window size (Odd number)
    • Number of Filters (Power of 2)
    • Stride
    • # of Conv layers
  • Pooling layer
    • Pooling window size
    • stride
    • # of pooling layer
  • Fc layer
    • # of hidden nodes
    • # of FC layer
  • BP algorithm
    • Learning rate, batch number, momentum coefficient, L2 coefficient

  Feature Normalization in CNN

  Batch Normalization(BN)

  • Using mean and variance of each feature, Normalize each feature in batch
  • normalize feature map of each channel over a batch sample

  Layer Normalization(LN)

  • Using mean and variance of each feature in input, normalize each input in batch
  • applicable to dynamic network and recurrent network(good)
  • normalize entire feature map

  

  Instance Normalization

  • It is similar with LN but not applicable to MLP and RNN. It is best for CNNs.
  • Effective for style transfer and image generation tasks using GAN
  • Compute mean and variance each sample in each channel

  Group Normalization

  • It is similar with Instance Normalization but not applicable to MLP and RNN. It is best for CNNs.
  • Effective for object detection, video classification (It is effective in memory problem)
  • Compute mean and variance each sample in each group that channels are grouped by group size g.
  • In image, The center of image and edge of image do not have equal meaning.
    • Thus, We can get flexibility if we compute differently each channels.
    • Also, Image's channel is not independent. If we use some channels nearby pivot channel, we can apply normalization at large region.

Storage and Computational Complexity

• c_i-1 = previous layer channel number
• c_i = previous layer channel number
• m_i-1 = previous feature map size
• m_i = previous feature map size
• k = filter size

# weights for layer i = (k x k x c_i-1) xc_i

# memory of layer i = m_i x m_i x c_i

# FLOPs = (kxk) x(m_i x m_i) x (c_i-1 x c_i)

CNNs Variants

Legends

LeNet 5 (1998)

• This architecture has become the standard
  • Stacking ConvNet and pooling
  • ending network with one or more fc.

AlexNet(2012)

• Apply RelU, pooling, data augmentation, dropout, Xavier initialization

VGGNet - 16 (2014)

• use only 3x3 sized filter.
• Before VGGNet, they use large filter. But 3x3 sized filter can result same output if use mulitple filter compared with large sized filter.

Effects of 3x3 sized filter

1. Decrease number of weights
   • If we have an input which has size [50x50x3] and 5x5 sized filter, # of weight is 5x5x3 = 75
   • If 3x3 sized filter, # of weight is (3x3x3)+(3x3x1) = 27 + 9 = 36
     • 2 consecutive 3x3 filters are same as 5x5 filter
2. Increase non-linearity
   • Each convolution has ReLU. 3x3 sized filter model has more non-linear function like ReLU than large sized filter model.

GoogleNet Inception-V1 (2014)

• 22 layers and 5 Million weights in total

• GoogleNet contains Inception Module that has parallel towers of convolutions with different filters, followed by concatenation, which captures different features at 1x1, 3x3 and 5x5.

  • In Inception Module, use 1x1 filter before 5x5 filter or 3x3 filter because if we don't use 1x1 sized filter, will get too many weights!

• We call 1x1 filter "bottleneck structure".

  • 1x1 convolutions are used for dimensionality reduction to remove computational bottlenecks
  • 1x1 convolutions add nonlinearity within a convolution

• Auxiliary classifier

  • Deeper network layers, higher likelihood of vanishing gradient.
  • Encourage discrimination in the lower stages
  • It only uses in training time.

ResNet-50 (2015)

• Skip connections/identity mapping can make deep network
• use batch normalization
• Remove FC layer and replace with GAP(Global Average Pooling)

Motivation

• When networks go deeper, a degradation problem has been exposed.
  • 56-layer's result is worse than 20-layer
  • But deep layer model has good results in many times.
    • So, need some techniques to make deep-layer model

Why ResNet works?

• use identity mappings to approximate "shallow network"

  

• Advantages of Skip Connection

  • Reduce optimization difficulty of deeper networks
  • Alleviate gradient vanishing problem
  • Reuse lower level feature

Xception (2016)

• An adaptation from Inception where the Inception modules have been replaced with depthwise separable convolution(DS-convolution layers)
  • Xception has same number of parameters as Inception

Inception - ResNet-V2 (2016)

• Converting Inception modules to Residual Inception blocks

ResNext-50 (2017)

• Adding of parallel towers/branches/paths within each module.

DenseNet (2017)

• Dense blocks where each layer is connected to every other layer in feedforward fashion
  • Alleviates vanishing gradient

Squeeze and Extraction - SENet (2017)

• Squeeze and Extraction can do feature recalibration to feature maps from origin networks.
• It is not a complete network, but a wrapper. Thus, we can use it with ResNet, ResNext, Inception etc.
• Improve channel interdependencies at almost no computational cost.

Squeeze

• Get a global understanding of each channel by squeezing the feature maps to a single numeric value.
  • use GAP(Global Average Pooling)

Extraction

• Feature Recalibration to computer channel-wise dependencies.
  • using Fully connected layer and non-linear function.

Lecture 2

• Generalization
• Speed of Convergence for Training and Learning quality

Performance Generalization

Neural network는 두 가지 중요한 점을 충족시켜야 한다.

1. training data를 통해서 학습하기 때문에, training data에 대해서는 정확하게 분류해야 한다.
2. learning process에 대해서 generalize하기 위해서 이전에 보지 못한 데이터(test data)에 대해서도 잘 분류해야 한다.

복잡한 결정 경계선(면)을 가지고 있다고 해서 이 것이 좋은 generalization performance를 가지고 있다고 하지 않는다.

실험 과정에서는 다음과 같이 진행된다.

1. data set을 Learning set(85%)과 test set(15%)으로 구분한다.
2. Learning set을 training set(70%)와 validation set(15%)로 구분을 한다.
3. training set으로 network을 훈련을 하고, training set과 validation set으로 성능을 평가한다.

만약 training error가 높다면 underfitting이 발생한 것이고, training error가 낮지만 validation error가 높으면 overfitting, 둘 다 낮은 수치가 나온다면 generalization이 잘 일어난 것을 의미한다.

위에서 했던 것에서 더 발전시켜서 3번을 서로 다른 구조로 진행한다.

4. 3번의 여러 구조에서 over and underfit이 아닌 best network을 고른다.
   • 다른 model인 것이 아니라 parameter tuning을 하는 것
5. 이를 learning set으로 다시 훈련을 한다.
6. test set으로 generalization을 테스트 한다.

• three subset을 바꿔가면서 N번 반복하는 것을 N-fold cross validation protocol이라고 한다.
• 하지만 각 parameter별로 best value가 다르다면...?

Parameter search

1. training set으로 다른 parameter들을 train하고, validation으로 평가한다.
2. 각 parameter에 대해서 error_val을 기록한다.
3. training set과 validation set을 reshuffle을 해서 1과 2를 반복한다.
4. 3번을 n times 반복한다.(n-fold CV)
5. n-fold로 부터 얻은 error_val 평균내고, 작은 평균 error_val을 가진 것들을 모아서 best parameter set을 만든다.

• Parameter searching 전략에는 다음 두 가지가 있다.
  • Grid Search
    • q개의 파라미터가 있고, 각 파라미터당 p개의 value가 생긴다면 이 경우에 대해서 모두 탐색하는 것을 의미한다.
    • mp^q개다.
  • Random Search
    • parameter value들을 random combination으로 진행한다.
    • grid search보다 좋은 점은 each parameter마다 unique한 value를 얻을 수 있기 때문에 좋다??

Generalization

generalization을 위해서 적절한 모델을 고르는 것도 중요하지만 다른 방법들에는 어떠한 것이 있을 까?

1. Enrich Training data

적은 데이터를 가지고 있으면, 모델은 쉽게 수렴할 수 있다.(small training error) 적은 데이터를 모델이 전부 외어버린다고 생각하면 된다. 하지만 절대적인 정보의 양이 부족하기 때문에 나쁜 generalization을 가진다.(large training error)

거대한 데이터라면 모델이 이를 학습하기 위해서 더 많은 노력을 해야 한다. (higher training error) 대신 정보의 양이 충분해졌기 때문에 더 좋은 generalization 성능을 가질 것이다.(lower test error)

즉, 질이 좋고 많은 양의 데이터를 사용해야 overfitting을 방지할 수 있다. 하지만, 이러한 데이터를 구하는 것은 많은 비용이 들고, 힘들다.

그래서 인공적으로 training data를 늘리는 방법을 사용하는데, 이를 Data augmentation이라고 부른다. (translation, rotation, stretching, shearing, lens distortions...)

2. Early stopping

훈련을 진행할 때, 언제 멈추는 것이 좋을까? 훈련을 계속해서 진행을 한다면 zero training error를 달성할 수 있을 것이다. 하지만 이는 train data에 overfitting 된 상태이기 때문에 test error는 매우 높을 것이다. 따라서 validation error를 기준으로 early stopping 할 포인트를 구한다.

3. Ensemble

여러 Network를 동시에 훈련을 시키고, 이들의 average를 사용하는 방법이다.

4. Regularization

network의 complexity를 줄이는 방법이다.

• L2(weight decay)

  

  • 각각의 벡터에 대해 항상 unique한 값을 반환

• L1

  

  • 특정 feature가 없어도 같은 값을 반환
  • feature selection이 가능하다.
  • Sparse model이 적합하다.
  • 미분 불가능한 점이 있기 때문에 gradient에 주의해야 한다.

• Max norm constraints

  • weight vector에 upper bound를 설정하는 것이다. (미분값도 제한된다.)
  • learning_rate를 크게 잡더라도 explode 하지 않는다.

• Dropout

  • 위에서 소개한 정규화 방법들과 같이 사용할 수 있다. random 하게 hidden node와 input node를 제거하고(off하고) 이들은 weight를 업데이트 하지 않는다.
  • 이는 training 과정에서만 적용되고, prediction에서는 dropout을 사용하지 않는다.

Speed of Convergence

• batch training
• Momentum term
• weight initialization
• learning rate
• activation functions
• feature normalization and scaling
• batch normalization(BN)

일반적인 BP(backpropagation)은 saddle point나 local minimum에 약하다는 단점이 있다.

• momentum term
  • gradient update에 관성을 부여한다는 아이디어다.
  • 기존 BP는 다음 식에 따라서 업데이트 된다.

    • 

  • momemtum term은 이전 weight update 한 값을 일부 제해서 더해준다.

    • 

    • a가 0이라면 일반적인 backpropagation과 동일하다.
    • a가 1이라면 gradient descent는 완전히 무시되고, mometum에 의해서만 업데이트 된다.
    • 보통 0.9나 0.95를 많이 사용한다.
• weight Initialization
  • 초기에 weight를 어떻게 init하는지에 따라서 성능이 바뀐다. 적절한 값을 사용해야 한다.
  • Xavier Initialization을 사용한다.
• Learning rate
  • 적절한 learning rate를 골라야 한다.
    • 너무 작게 고르면 늦게 수렴할 수 있다.
    • 너무 크게 고르면 발산 할 수 있다.
  • Adagrad, RMSprop, ADAM, ...
• Activation function
  • simoid나 tanh같은 경우에는 미분값을 자주 곱할수록 작아지기 때문에 gradient vanishing 문제가 발생한다.
  • 따라서 ReLU를 사용한다.
    • 장점
      • simple하고, positive region에서는 stable하다.
      • 수렴속도도 따르다.
      • sparsifcaiton을 허용한다.
    • 단점
      • gradient가 0보다 작은 값이 많다면 Dying 할수도 있다.
        • Leaky ReLU, Parametric ReLU와 같은 걸로 해결 가능
• Feature Normalization
  • 각 feature들의 range가 다르기 때문에 normalization을 수행한다.
  • Max-min normalization
  • Z score normalization
  • PCA - large dataset에는 부적합
• Batch Normalization (BN)
  • 각 layer마다 input의 distribution이 달라지는 것을 막기 위함이다.
  • pre-activation, post-activation에 각각 normalizing을 수행한다.
  • 이는 mini-batch 단위로 수행히 가능하고, 각각 독립적으로 수행할 수 있다.
  • prediction을 위해서는 re-adjust해야 하고, r과 b도 알아야 한다.
  • 장점
    • high learning rate가 가능하다.
    • deep network에 사용 가능하다.
    • weight init에 민감하지 않다.
  • 단점
    • Batch number가 커야 한다.
    • dynamic network 구조나 recurrent network에 적합하지 않다.

Summary

• good convergence를 가장 우선적으로 생각해야 하지만 good generalization을 보장하진 않는다.
• convergence의 속도를 향상시키기 위해서 여러 방법들을 사용할 수 있다.

Lecture1

• Multiple Layer Perceptron(MLP)
• Loss Function
  • Mean Square Error
• Backpropagation Algorithms
  • Stochastic Gradient Descent(SGD)
  • Batch Gradient Descent(BGD)
  • Mini-batch GD

Multiple Layer Perceptron

하나의 Neuron은 이전 Neuron들의 값과 weight 간의 곱의 합들을 구한 뒤 activation function인 f를 통과해서 y를 만들어 낸다.

layer들은 이러한 Neuron들을 여러 개 가지고 있는 것을 의미하고, 이러한 layer가 여러 개 존재한다면 이를 Multiple Layer Perceptron(MLP)라고 부른다.

Input layer는 x_i 만큼의 입력값이 존재하고, Hidden Layer가 앞서 말한 연산을 수행한다. Output Layer는 하나의 값을 계산하기 때문에 하나의 Neuron이 존재한다.

Activation function

각 hidden node에 적용되는 activation function은 다음과 같은 것들이 있고, 이들의 대한 설명은 다음에 기회가 있으면 하도록 한다.

• Sigmoid Function
• 
• Hyperbolic Tangent
• 
• Rectifier Linear(ReLU)
• 

Usage

MLP는 Classification, Regression 등에서 사용될 수 있다.

Loss Function For MLPs

Loss Function은 참값과 예측값과의 차이를 의미하기 때문에 MLP에서 Supervised Learning 학습 방법에서 나타난다.

그림으로 프로세스를 보면 다음과 같이 설명할 수 있다.

In Regression

MLP의 output layer에서 Linear function을 사용한다. loss function은 각 output error의 square들의 합으로 구성된다. 이를 Mean Square Error(MSE)라고 부르고, 식으로 표현하면 다음과 같다.

In Classification

MLP의 output layer에서 Softmax function을 사용한다. softmax는 Hard max function과는 다르게 exp()함수를 사용하는데, 그 이유는 더 큰 logit을 가지고 있는 값에 더 큰 값을 부여하기 위함이다. Loss function은 Average Cross-Entropy (ACE) loss를 사용한다.

t_i는 true label, f(s)_i는 softmax output을 의미한다.

Backpropagation

Gradient Descent는 weight를 기울기 반대 방향으로 업데이트 하면서 Optimal weight를 찾아가는 과정이다. 이 때 기울기 반대 방향으로 업데이트 할 때, learning rate를 적절한 값으로 설정해야 한다. 만약 너무 learning rate가 작다면 local minimum에 빠질 수 있고, 훈련속도가 너무 느릴 수 있다. 하지만 크다면 shooting이 발생할 수 있어 발산할 수도 있다.

역전파 하는 과정을 그림과 함께 보면 다음과 같이 표현할 수 있다.

위 그림과 같은 간단한 Two-Hidden layers MLP가 존재한다고 하자. 이 때, x는 input, y는 output을 의미하며 f 함수는 sigmoid라고 가정하자.

Forward

우선 x로부터 y를 얻기 위해 forwarding 과정을 거친다. x는 빨간 선을 따라서 y를 얻을 수 있다.

예측값 y를 얻으면 실제값 z와 비교해서 Error를 얻을 수 있다.

Backpropagation

이제 Error 오차를 얻었으니 각 노드에서 온 값들이 얼마나 영향을 주었는지 거꾸로 탐색하며 Error의 정도를 측정한다.

Forward되는 과정에서 w를 통하여 y로 도달했기 때문에 Error 또한 weight만큼의 영향을 주었다고 생각할 수 있다. 이 때, w_i,j는 i에서 j로 향하는 weight를 의미한다. 위 그림과 같은 과정을 input x 까지 계속해서 반복한다.

Weights Updating

Backpropagation 과정을 거쳤으면 각 weight별로 얼만큼의 에러를 발생했는지 확인했기 때문에 다음 예측을 정확히 하기 위해서 weight를 변경해야 한다. 이전 단계에서 구했던 Error를 편미분 하여서 weight를 update하도록 한다.

BP Training

1. Stochastic GD (SGD)
   • 각 single instance에 대해서 local error를 계산하고(like SE or CE) gradient를 계산한다.
   • solution에 도달하기까지 많은 시간이 걸린다.
   • 하지만 빠르다
2. Batch GD (BGD)
   • 전체 데이터를 가지고 global error를 계산한다.(like MSE or ACE) global error gradient를 계산해서 한번에 업데이트 한다.
   • 많은 계산량을 요구한다.(그래서 느리다.)
   • 소규모 훈련 데이터에 사용가능하다.
3. Mini-batch GD
   • 1번과 2번의 장점을 합친 것이다.
   • B=1이면 SGD가 되고, B=Q가 되면 BGD가 된다.

데이터베이스에서 보안과 권한관리를 하는 것은 매우 중요하다. 데이터베이스의 보안과 권한관리가 미약해서 손실된다면 데이터베이스를 소유한 조직의 운용데 큰 타격을 입기 때문이다. 따라서 권한이 없는 사람들이 함부로 데이터베이스에 접근을 하지 못하도록 하고, 일부 사용자들에게만 적절한 수준의 권한을 허가할 수 있는 기능을 가지고 있어야 할 것이다.

따라서 데이터베이스에는 이를 위해서 접근제어와 보안 및 권한 관리에 대한 기능을 제공하고 있다.

보안기법

데이터베이스에서 제공하는 보안 기법에는 크게 두 가지가 있다.

첫번째는 임의 보안 기법이다.

임의 보안 기법은 사용자들에게 특정 릴레이션, 투플, 또는 애트리뷰트를 지정된 모드로 접근할 수 있는 권한을 허가하고 취소하는 기법이다. 대부분의 상용 관계 DBMS에서 사용되는 기법으로 시스템 카탈로그에 누가 권한을 허가받았고 취소당했는가를 유지한다.

두번째는 강제 보안 기법이다.

강제 보안 기법은 데이터와 사용자들을 다양한 보안 등급으로 분류하고 해당 조직마다 적합한 보안 정책을 적용한다. 하지만 대부분의 상용 관계 DBMS는 이러한 보안 기법을 제공하지 않는다.

이러한 보안을 계속해서 유지하기 위해서는 데이터베이스를 관리하는 데이터베이스 관리자가 필요하다. 이 사람은 권한을 부여하거나 취소를 하고 사용자가 데이터베이스에 가한 모든 연산들을 기록할 수 있다. 만약 권한이 없는 사용자가 데이터베이스를 갱신했다는 의심을 들면 데이터베이스 감사를 실시할 수 있다.

권한 관리

사용자에게 권한을 주는 권리에는 다음과 같은 것들이 있다.

권한 허가

서로 다른 객체들에 대해서 다양한 권한들이 존재한다. 객체를 생성한 사람(소유한)은 모든 권한을 가지며 이 사람은 자신이 소유한 임의의 객체에 대해서 특정 권한을 GRANT문을 사용해서 다른 사용자에게 역할이나 권한을 허가할 수 있다.

GRANT 권한[(애트리뷰트들의 리스트)] ON 객체 TO {사용자|역할|PUBLIC} [WITH GRANT OPTION];

권한 취소

다른 사용자에게 허가한 권한을 취소하기 위해서는 REVOKE문을 사용한다. 또한 어떤 사용자가 다른 사용자에게 허가했던 권한을 취소한다면 권한을 취소 당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 연쇄적으로 취소된다.

REVOKE {권한들의 리스트|ALL} ON 객체 FROM {사용자|역할|PUBLIC};

역할

여러 사용자들에 대한 권한 관리를 단순화하기 위해서 역할을 사용한다.

역할은 사용자에게 허가할 수 있는 연관된 권한들의 그룹으로서 이름을 가진다. 각 사용자는 여러 역할에 속할 수 있으며 여러 사용자들이 동일한 역할을 허가받을 수 있다. 또한 어떤 역할과 연관된 권한들에 변화가 생기면 그 역할을 허가받은 모든 사용자들은 자동적으로 즉시 변경된 권한들을 가지게 된다.

뷰

관계 데이터베이스 시스템의 뷰는 다른 릴레이션으로부터 유도된 릴레이션이다. 뷰를 사용함으로써 복잡한 질의를 간단하게 표현할 수 있고, 데이터 독립성을 높히기 위해서 사용한다.

 ANSI/SPARC 3단계 아키텍처에서 외부 뷰 라는 개념이 있었는데 지금 설명하는 뷰는 다른 개념이다. 하나의 가상 릴레이션을 의미하며 기본 릴레이션에 대한 SELECT문의 형태로 정의된다. 

View에는 3가지 종류가 있다.

• Static view - base relation이 바뀌어도 view는 그대로 남아 있다. 변경시 수동으로 업데이트 해야 한다.

• Dynamic view - base relation이 바뀐다면 view도 같이 바뀐다.

• Materialized view - 위 view들이 논리적으로 실행되는 것과는 다르게 query 결과를 별도의 공간에 저장하고, query가 실행될 때 미리 저장된 결과를 출력함으로써 성능을 향상 시킨다.

  • 어느 시점의 select문의 결과를 기본 릴레이션 형태로 저장해서 이를 사진을 찍은 것과 같아 snapshot이라고도 한다.

"CREATE VIEW 뷰이름[애트리뷰트] AS SELECT문 [WITH CHECK OPTION]" 과 같이 뷰를 정의한다.

뷰의 장점

1. 뷰는 복잡한 질의를 간단하게 표현할 수 있도록 한다. 뷰가 특정한 시점(조건)의 SELECT문을 정의한 것이므로 복잡하게 검색하는 질의를 뷰로 정의해두면 뷰는 SELECT하면 같은 값을 얻을 수 있다.

2. 뷰는 데이터 무결성을 보장하는데 활용된다. 처음 뷰를 정의할 때 설정한 조건을 기준으로 뷰를 갱신가능(추가, 수정)하다. 단 WITH CHECK OPTION을 명시했다고 가정한다. 따라서 뷰에 대한 갱신을 하면 기본 릴레이션에 대한 갱신으로 변환된다. 단 릴레이션의 기본 키가 포함되지 않은 뷰이다.

3. 뷰는 데이터 독립성을 제공한다. 뷰는 데이터베이스의 구조가 바뀌더라도 기존의 질의를 다시 작성할 필요가 없다. 예를 들어 하나의 릴레이션이 있고 이를 뷰로 정의했다고 하자. 알고보니 이 릴레이션을 두 개의 릴레이션으로 분해할 필요가 생겼고, 분해했다고 가정하자. 그러면 기존의 릴레이션으로 접근하던 질의는 분해된 릴레이션으로 바꿔야 하지만 뷰로 접근하도록 했다면 바꿀 필요가 없다.

4. 뷰는 같은 데이터라도 여러 가지 뷰를 제공할 수 있다. 특정한 시점의 SELECT문이라고 생각하면 되므로 여러가지 조건으로 뷰를 생성했다면 같은 데이터라도 서로 다른 뷰가 된다.

트랜잭션

 트랜잭션은 동시성 제어를 지원하는데 동시성 제어란 동시에 수행되는 트랜잭션들이 데이터베이스에 미치는 영향이 순차적으로 수행할 때와 동일하도록 보장을 하는 것이다. 데이터베이스는 기본적으로 많은 사용자들이 동시에 접근하기 때문에 이를 보장할 필요가 있기 때문이다. 혹은 트랜잭션은 회복의 특징도 가지고 있는데 데이스베이스를 갱신하는 도중에 시스템이 고장 나도 일관성을 유지하는 것이다.

트랜잭션의 특성 (ACID)

원자성(Atomicity)

 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않아야 함(all or noting)을 의미한다. 중간에 다운되어서 트랜잭션을 완료하지 못하였다면 취소를 하고, 완료된 트랜잭션은 재수행함으로써 원자성을 보장한다. 원자성은 DBMS의 회복과 연관되어 있다.

일관성(Consistency)

 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에도 일관된 상태를 가진다. 일관성은 DBMS의 무결성 제약조건과 동시성 제어와 연관되어 있다.

고립성(Isolation)

 한 트랜잭션이 데이터를 갱신하는 동안에 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 한다. 이를 확인하는 방법은 다수의 트랜잭션이 동시에 수행하더라도 결과는 어떤 순서로 수행한 것과 같아야 한다. 고립성은 DBMS의 동시성 제어와 연관되어 있다.

지속성(Durability)

 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 시스템이 고장나도 손실되지 않는다. 지속성은 DBMS의 회복과 연관되어 있다.

완료(commit)와 철회(abort)

트랜잭션의 완료는 트랜잭션에서 변경하려는 내용이 데이터베이스에 완전하게 반영되었음을 의미하고 SQL상 COMMIT WORK이다. 트랜잭션의 철회는 변경하려는 내용이 일부만 반영된 경우에는 원자성을 보장하기 위해 트랜잭션 수행 전 상태로 되돌리는 것이다. SQL상 ROLLBACK WORK이다.

동시성 제어

대부분의 DBMS들은 다수 사용자용으로 설계되어 있고 여러 사용자들이 동시에 동일한 테이블을 접근하기도 한다. 따라서 성능을 높이기 위해 동시에 질의를 수행하는 것이 필수적이며, 부정확한 결과가 발생하지 않도록 해야한다. 동시성 제어를 설명하기에 앞서 필요한 개념의 정의다.

1. 직렬 스케줄

- 여러 트랜잭션들의 집합을 한 번에 한 트랜잭션씩 차례대로 수행한다.

2. 비직렬 스케줄

- 여러 트랜잭션들을 동시에 수행한다.

3. 직렬가능(serializable)

- 비직렬 스케줄의 결과가 어떤 직렬 스케줄의 수행 결과와 동등하다.

문제점

 만약 동시성 제어를 하지 않고 다수의 트랜잭션을 동시에 수행한다면 다음과 같은 문제가 발생할 수 있다.

1. 갱신 손실: 수행 중인 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어버려서 갱신이 무효가 되는 것이다.

위와 같은 예시에서 X 값에 대해서 갱신 손실이 발생하게 되었다. 두 개의 트랜잭션에서 각각 X를 읽었고, 값을 갱신하였다. 하지만 T1에서 먼저 갱신을 했고 (X=X-100000-> 200000) T1이 쓰기 전에 그 다음 T2에서 갱신(X=X+50000-> 350000)을 했다. 따라서 같은 X의 데이터가 서로 다른 값을 가지게 되었고, T1이 write 한 다음에 T2가 write(덮어버렸다.)해서 갱신 손실이 발생하게 되었다.

2. 오손 데이터 읽기: 완료되지 않은 트랜잭션이 갱신한 데이터를 읽는 것이다.

 T1의 값을 COMMIT하기 전에 T2에서 갱신한 데이터를 접근해버려서 오손 데이터 읽기가 발생했다.

3. 반복할 수 없는 읽기: 한 트랜잭션이 동일한 데이터를 두 번 읽었으나 서로 다른 값을 읽게 되는 것이다.

 T2에서 AVG(balance)를 두 번 수행했는데 그 사이에 balance의 값의 갱신이 있었다. 따라서 반복할 수 없는 읽기 문제가 발생했다.

로킹(locking)

 데이터 항목을 로킹하는 개념은 동시에 수행되는 트랜잭션들의 동시성을 제어하기 위해서 가장 널리 사용되는 기법이다. 로크는 데이터베이스 내의 각 데이터 항목과 연관된 하나의 변수이다. 트랜잭션에서 데이터 항목을 접근할 때 로크를 요청하고, 접근을 끝낸 후에 로크를 해제한다. 

2단계 로킹 프로토콜

 로크를 요청하는 것과 로크를 해제하는 것이 2단계로 이루어지는 것이다. 로크 확장하는 단계가 지난 후에 로크 수축 단계에 들어가게 되는 것이다.

팬텀 문제

 두 개의 트랜잭션이 동일한 애트리뷰트드에 대해 갱신하는 작업을 수행한다고 하자. 그런데 하나의 트랜잭션에서 투플을 삭제하거나 삽입한다면 T1와 T2가 같은 SELECT를 하더라도 결과가 다르게 나타나게 되는데 이를 팬텀 문제라고 부른다. 즉 알 수 없는 값이 삭제되었거나 추가된 현상을 의미한다.

릴레이션 정규화

부주의한 데이터베이스 설계는 데이터 중복을 야기해서 갱신 이상을 유발한다. 따라서 정규화를 통해서 주어진 릴레이션 스키마를 함수적 종속성과 기본 키를 기반으로 분석하여, 원래의 릴레이션을 분해해 중복과 세 가지 갱신 이상을 최소화한다.

갱신 이상

1. 수정 이상

 반복된 데이터 중에 일부만 수정하면 데이터의 불일치가 발생한다.

ex) 만일 어떤 강좌의 이름이 바뀔 때 이 강좌를 수강하는 일부 학생 투플에서만 강좌 이름을 변경한다면 데이터 불일치 상태에 빠지게 된다.

2. 삽입 이상

 불필요한 정보를 함께 저장하지 않고는 어떤 정보를 저장하는 것이 불가능하다.

ex) 만일 새로운 강좌를 개설했는데 아직 학생을 한 명도 배정하지 않았다면 이 강좌에 관한 정보를 입력할 수 없다.

3. 삭제 이상

 유용한 정보를 함께 삭제하지 않고는 어떤 정보를 삭제하는 것이 불가능하다.

ex) 만약 강좌를 수강하고 있는 학생이 단 한 명이 있는데, 이 학생에 관한 투플을 삭제한다면 강좌에 관한 정보도 릴레이션에서 삭제된다.

릴레이션 분해

따라서 이러한 갱신이상을 해결하기 위해서 하나의 릴레이션을 두 개 이상의 릴레이션으로 나눈다. 릴레이션을 분해할 때에는 함수적 종속성에 관한 지식을 기반으로 하며 추후 다시 원래의 릴레이션을 다시 구할 수 있어야 한다.

함수적 종속성

정규화를 할 때 가장 중요한 이론이다. 릴레이션의 애트리뷰트들의 의미로부터 결정된다. 실세계에 대한 지식과 응용의 의미를 기반으로 어떠한 함수적 종속성이 있는지 파악해야 한다.

결정자

어떤 애트리뷰트의 값이 다른 애트리뷰트의 값을 고유하게 결정할 수 있을 때 이를 결정자라고 부르며 이를 A가 B를 결정한다 와 같이 말한다. (A->B와 같이 표기한다.)

위와 같은 학생 릴레이션이 있다고 가정하자. 그러면 다음과 같은 결정자들이 있다.

학번 -> 이름 , 학번 -> 주소, 학번 ->전화번호, 학과 번호-> 학과이름

함수적 종속성

만약 애트리뷰트 A가 애트리뷰트 B의 결정자이면 B가 A에 함수적으로 종속한다고 말한다. 즉, 각 A 값에 대해 반드시 한 개의 B 값이 대응된다는 것이다.

ex) 학번이 이름, 주소, 전화번호의 결정자이므로, 이름, 주소, 전화번호는 학번에 함수적으로 종속한다.

1. 완전 함수적 종속성 (FFD : Full Functional Dependency)

 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하면서 애트리뷰트 A의 어떠한 진부분 집합에도 함수적으로 종속하지 않으면 애트리뷰트 B가 애트리뷰트 A에 완전하게 함수적으로 종속한다고 말한다. 즉, 애트리뷰트 B는 릴레이션 내 결정자들에 의해 결정되는 것임을 의미한다. (여기서 A는 복합 애트리뷰트이다.)

2. 부분 함수적 종속성

부분 함수적 종속성은 완전 함수적 종속성이 아닌 함수적 종속성들을 의미한다.

3. 이행적 함수적 종속성

 한 릴레이션의 애트리뷰트 A, B, C가 주어졌을 때 애트리뷰트 C가 이행적으로 A에 종속한다.(A->C)는 것의 필요충분조건은 A->B ^ B->C 가 성립하는 것이다. 즉 3단 논법을 만족하는 함수적 종속성을 의미한다.

정규화

정규형의 종류에는 제1 정규형, 제2정규형, 제3정규형, BCNF, 제4정규형, 제 5정규형이 있으나 일반적으로 BCNF까지만 고려한다.

제1정규형

한 릴레이션 R이 제1 정규형을 만족할 필요충분조건은 릴레이션 R의 모든 애트리뷰트가 원자값만을 가진다는 것이다. 즉 애트리뷰트에 반복 그룹이 나타나지 않는다면 제1 정규형을 만족한다.

하지만 제1 정규형을 진행해도 갱신 이상이 존재할 수 있다. 아래 그림의 학생 릴레이션은 모든 애트리뷰트가 원자값을 가지므로 제 1정규형을 만족한다. 이 릴레이션의 기본 키는 (학번, 과목 번호)이다.

1. 수정 이상

- 한 학과에 소속한 학생 수만큼 그 학과의 전화번호가 중복되어 저장되므로 여러 학생이 소속된 학과의 전화번호가 변경되었을 때 모든 학생들의 투플에서 전화번호를 수정하지 않으면 수정 이상이 발생한다.

2. 삽입 이상

- 한 명의 학생이라도 어떤 학과에 소속되지 않으면 학과에 관한 투플을 삽입할 수 없다. 학번이 기본 키의 구성요소인데 엔티티 무결성 제약조건에 의해 기본 키에 널 값을 넣을 수 없기 때문이다.

3. 삭제 이상

- 어떤 학과에 소속된 마지막 학생을 삭제한다면 이 학생이 소속된 학과의 정보도 삭제된다.

why? 기본 키에 대한 부분 함수적 종속성이 있기 때문에 갱신 이상이 발생한다.

제2 정규형

제1 정규형을 만족하는 릴레이션에 대해서 어떤 후보 키에도 속하지 않는 애트리뷰트들이 R의 기본 키에 완전하게 함수적으로 종속하는 것을 의미한다. 따라서 위의 갱신 이상을 해결하기 위해서 학생 릴레이션을 학번 1, 수강 릴레이션으로 분해할 수 있다.

 따라서 위와 같이 분해한다면 아래와 같은 기본 키가 학번인 학생 1 릴레이션이 생기게 될 것이다. 하지만 제2정규형을 진행해도 아직 갱신 이상이 남아있다.

1. 수정 이상

- 여러 학생이 소속된 학과의 전화번호가 변경된다면 그 학과에 속한 모든 학생들의 투플에서 전화번호를 수정해야 일관성이 유지된다.

2. 삽입 이상

- 어떤 학과가 신설해서 소속 학생이 없다면 정보를 입력할 수 없다.

3. 삭제 이상

- 어떤 학과에서 마지막 학생의 투플이 삭제된다면 학과의 전화번호도 함께 삭제된다.

why? 학생1 릴레이션에 이행적 종속성이 존재하기 때문에 갱신 이상이 발생한다.

제3 정규형

 한 릴레이션 R이 제2 정규형을 만족하면서, 키가 아닌 모든 애트리뷰트가 R의 기본키에 이행적으로 종속하지 않는 것을 의미한다. 따라서 학생 1에 존재하는 이행적 종속성을 해결하기 위해서 학생 2, 학과 릴레이션으로 분해한다.

 이제 학생 릴레이션에서는 더 이상 갱신 이상이 발생하지는 않으므로 정규화 작업이 필요하지 않다. 하지만 제2 정규형에서 생성된 수강 릴레이션에서 갱신 이상이 발생되므로 이 릴레이션에 대해서는 추가 정규화 작업이 필요하게 된다.

 수강 릴레이션에서 각 학생은 여러 과목을 수강할 수 있고, 각 강사는 한 과목만 가르치게 된다. 이 릴레이션의 기본 키는 (학번, 과목)이다. 키가 아닌 강사 애트리뷰트가 기본 키에 완전하게 함수적으로 종속하므로 제2 정규형을 만족하고, 강사 애트리뷰트가 기본 키에 직접 종속하므로 제3 정규형도 만족한다.

따라서 이 릴레이션에는 아래와 같은 함수적 종속성이 존재한다.

(학번, 과목) -> 강사, 강사-> 과목

1. 수정 이상

- 여러 학생이 수강 중인 어떤 과목의 강사가 변경되었을 때 그 과목을 수강하는 모든 학생들의 투플에서 강사를 수정해야 한다.

2. 삽입 이상

- 어떤 과목을 신설해서 아직 수강하는 학생이 없으면 어떤 강사가 그 과목을 가르친다는 정보를 입력할 수 없다. 이 역시도 엔티티 무결성 제약조건에 의해 기본 키를 구성하는 애트리뷰트에 널 값을 넣을 수 없다는 이유 때문이다.

3. 삭제 이상

- 어떤 과목을 이수하는 학생이 한 명밖에 없는데 이 학생의 투플을 삭제하면 그 과목을 가르치는 강사에 관한 정보도 함께 삭제된다.

why? 수강 릴레이션에서 키가 아닌 애트리뷰트가 다른 애트리뷰트를 결정하기 때문에 갱신 이상이 발생한다.

BCNF

 릴레이션 R이 제3 정규형을 만족하고, 모든 결정자가 후보 키여야 한다. 수강 릴레이션에서 강사는 후보 키가 아님에도 불구하고 과목을 결정하기 때문에 문제가 발생했던 것이다.

 BCNF를 하기 위해서 키가 아니면서 결정자 역할을 하는 애트리뷰트(강사)와 그 결정자에 함수적으로 종속하는 애트리뷰트(과목)를 하나의 테이블에 넣는다. 이 릴레이션에서는 강사가 기본 키가 된다. 그다음에는 기존 릴레이션에 결정자(강사)를 남겨서 기본 키의 구성요소가 되도록 한다. (-> 외래 키가 된다)

위의 과정들을 요약을 하면 다음과 같다.

정규화의 장점은 정규화를 진행할수록 중복이 감소하고, 갱신 이상도 감소된다. 정규화가 진전될수록 무결성 제약조건을 시행하기 위해 필요한 코드의 양이 감소된다.

하지만 높은 정규형을 만족한다고 해서 릴레이션 스키마가 최적이 되는 것은 아니다. 분해되기 전의 릴레이션의 내용이 필요하다면 조인의 필요성이 증가하기 때문에 더 안 좋아질 수도 있다.

ER 스키마를 관계 모델의 릴레이션으로 사상

ER 모델을 릴레이션들로 사상하는 7개의 단계로 이루어진 알고리즘을 통해서 사상한다.

단계 1: 정규 엔티티 타입과 단일 값 애트리뷰트

ER 스키마의 각 정규 엔티티 타입 E에 대해 하나의 릴레이션 R을 생성한다.

E에 있던 단순 애트리뷰트들을 릴레이션 R에 모두 포함시킨다.

복합 애트리뷰트는 복합 애트리뷰트를 구성하는 단순 애트리뷰트들만 포함시킨다.

E의 기본 키가 릴레이션의 R의 기본 키가 된다.

단계 2: 약한 엔티티 타입과 단일 값 애트리뷰트

ER 스키마에서 소유 엔티티 타입 E를 갖는 각 약한 엔티티 타입 W에 대하여 릴레이션 R을 생성한다.

소유 엔티티 타입에 해당하는 릴레이션의 기본 키를 약한 엔티티 타입에 해당하는 릴레이션에 외래 키로 포함시킴

약한 엔티티 타입의 부분 키와 소유 엔티티 타입의 외래 키의 조합으로 기본 키를 구성한다.

단계 3: 2진 1:1 관계 타입

관계 타입 R에 대하여, R에 참여하는 엔티티 타입에 대응되는 릴레이션 S와 T를 찾음

S와 T 중에서 한 릴레이션을 선택하고, 만일 S를 선택했따면 T의 기본 키를 S의 외래 키로 포함시킨다.

보통 관계 타입에 완전하게 참여하는 릴레이션을 S 릴레이션으로 선택한다. 관계 타입 R이 가지고 있는 단순 애트리뷰트들은 S에 대응되는 릴레이션에 포함시킨다.

두 엔티티 타입이 R에 완전하게 참여할 때는 하나의 릴레이션으로 합치는 방법도 가능하다.

단계 4: 정규 2진 1:N 관계 타입

관계 타입 R에 대하여 N측의 참여 엔티티 타입에 대응되는 릴레이션 S를 찾는다. 1측의 엔티티 타입에 대응되는 릴레이션 T의 기본 키를 S에 외래 키로 포함시킨다.

S의 기본 키를 T의 외래 키로 포함시키면 정보의 중복이 발생한다.

단계 5: 2진 M:N 관계 타입

관계 타입 R에 대해서는 릴레이션 R을 생성한다.

참여 엔티티 타입에 해당하는 릴레이션들의 기본 키를 릴레이션 R에 외래 키로 포함시키고, 이들의 조합이 R의 기본키가 된다.

단계 6: 3진 이상의 관계 타입

3진 이상의 관계 타입 R에 대하여 릴레이션 R을 생성하고, R에 참여하는 모든 엔티티 타입에 대응되는 릴레이션들의 기본 키를 R의 외래 키로 포함 시킨다. 만약 R에 참여하는 엔티티 타입들의 카디날리티가 1:N:N 이면 1의 릴레이션의 기본 키를 참조하는 외래 키를 제외한 나머지 외래 키들의 조합이 릴레이션 R의 기본키가 된다.

단계 7: 다치 애트리뷰트

각 다치 애트리뷰트에 대하여 릴레이션 R을 생성한다.

다치 애트리뷰트를 애트리뷰트로 갖는 엔티티 타입이나 관계 타입에 해당하는 릴레이션의 기본 키를 릴레이션 R에 외래 키로 포함시킨다.

데이터베이스 설계

 데이터베이스 설계는 요구사항 분석 - 개념적 설계 - DBMS의 선정 - 논리적 설계 - 스키마 정제 -물리적 설계 - 보안 설계 -구현 단계로 이루어져 있다.

 개념적 데이터베이스 설계는 실제로 데이터베이스를 어떻게 구현할 것인가와는 독립적으로 정보 사용의 모델을 개발하는 과정이다. 사용자의 요구사항으로부터 개념적 스키마가 만들어지며 실세계의 엔티티, 관계, 프로세스, 무결성 제약조건을 나타내는 추상화 모델을 구축하며 주로 엔티티 - 관계(ER) 모델을 사용한다. 엔티티는 서로 구분이 되면서 조직체에서 데이터베이스에 나타내려는 객체를 의미한다.

 DBMS 선정은 기술적인 요인, 정치적인 요인, 경제적인 요인 등을 검토한 후에 DBMS를 선정한다.

 논리적 설계는 개념적 스키마에 알고리즘을 적용해서 논리적 스키마를 생성한다. 논리적 스키마를 나타내기 위해 관계 데이터 모델을 사용하는 경우에는 ER모델로 표현된 개념적 스키마를 관계 데이터베이스 스키마로 사상한다. 또한 더 좋은 스키마로 변환하기 위해서 정규화 과정을 적용한다.

 물리적 설계는 처리 요구사항들을 만족시키기 위해 저장 구조와 접근 경로 등을 결정한다. 응답 시간, 트랜잭션 처리율 등을 기준으로 성능을 평가한다.

ER 모델

 데이터베이스 설계를 용이하기 위해서 제안되었으며, 계속해서 이 모델이 강화되어서 현재는 EER(Enhanced Entity Relationship) 모델이 데이터베이스 설계 과정에 널리 사용되고 있다. 기본적인 구문으로 엔티티, 관계, 애트리뷰트가 있고, 기타 구문으로는 카디날리티 비율, 참여 제약조건 등이 있다.

엔티티

 엔티티는 독립적으로 존재하면서 고유하게 식별이 가능한 실세계의 객체를 의미하며 엔티티 타입은 동일한 애트리뷰트들을 가진 엔티티들의 틀이고, 엔티티 집합은 동일한 애트리뷰트들을 가진 엔티티들의 모임이다. 엔티티 타입으로는 크게 강한 엔티티 타입과 약한 엔티티 타입이 있다.

1. 강한 엔티티 타입은 독자적으로 존재하며 엔티티 타입 내에서 자신의 키 애트리뷰트를 사용하여 고유하게 엔티티들을 식별할 수 있는 타입이다.

2. 약한 엔티티 타입은 키를 형성하기에 충분한 애트리뷰트들을 가지지 못한 엔티티 타입으로 이 엔티티 타입이 존재하려면 소유 엔티티 타입이 존재해야하며 소유 엔티티 타입의 키 애트리뷰트를 결합해야만 약한 엔티티 타입을 식별할 수 있다. ER 다이어 그램에서 이중선 직사각형으로 표기하며 부분 키는 점선 밑줄을 그어서 표시한다.

ER모델에서 엔티티는 직사각형으로 표시한다.

애트리뷰트

 하나의 엔티티는 연관된 애트리뷰트들의 집합으로 설명된다. 엔티티는 독립적인 의미를 가지지만 애트리뷰트는 독립적인 의미를 가지지 않는다. ER 모델에서 타원형으로 나타내며 기본키의 경우에는 밑줄을 그어준다. 애트리뷰트와 엔티티 타입은 실선으로 연결한다.

1. 단순 애트리뷰트

 더 이상 다른 애트리뷰트로 나눌 수 없는 애트리뷰트로 ER 다이어그램에서 실선 타원으로 표현한다. ER 다이어그램에서 대부분의 애트리뷰트는 단순 애트리뷰트인 경우가 많다. 대부분의 단순 애트리뷰트는 단일 값 애트리뷰트이다. 단일 값 애트리뷰트는 각 엔티티마다 정확하게 하나의 값을 가지는 애트리뷰트를 의미한다. 예를 들면 학생의 학번 애트리뷰트는 어떠한 학생도 두 개 이상의 학번을 가지지 않으므로 단일 값 애트리뷰트이다.

2. 복합 애트리뷰트

 두 개 이상의 애트리뷰트로 이루어진 애트리뷰트로 동일한 엔티티 타입이나 관계 타입에 속하는 애트리뷰트들 중에서 연관된 것을 모아놓은 것이다.

이 외에도 다치 애트리뷰트, 저장된 애트리뷰트, 유도된 애트리뷰트가 있다.

관계와 관계 타입

관계는 엔티티들 사이에 존재하는 연관이나 연결로서 두 개 이상의 엔티티 타입들 사이의 사상으로 생각할 수 있다. 요구사항에서 동사가 ER 다이어그램에서 관계로 표현되며 다이아몬드로 표기한다. 관계 타입은 관계의 특징을 기술하는 애트리뷰트들을 가질 수 있으며, 키 애트리뷰트를 가지지 않는다. 

 관계와 관계 타입에는 차수와 카디날리티라는 것이 있다. 차수란 관계로 연결된 엔티티 타입들의 개수를 의미하며 주로 2진 관계가 흔하다. 카디날리티 비율은 한 엔티티가 참여할 수 있는 관계의 수를 나타낸다. 흔히 1 : 1, 1:N, M:N으로 구분을 한다.

1. 1:1 관계

 E1의 각 엔티티가 정확하게 E2의 한 엔티티와 연관되고, E2의 각 엔티티가 정확하게 E1의 한 엔티티와 연관되었을 경우

ex) 각 학생에 대해 최대한 한 개의 노트북이 있고, 각 노트북에 대해 최대 한 명의 학생이 있다면 학생과 노트북 간의 관계는 1 : 1 관계이다.

2. 1:N 관계

 E1의 각 엔티티가 E2의 임의의 개수의 엔티티와 연관되고, E2의 각 엔티티는 정확하게 E1의 한 엔티티와 연관되면 이 관계를 1 : N 관계라고 하며 실세계에서 가장 흔히 나타나는 관계이다.

ex) 각 학생은 한 명의 지도교수님을 가지고 있지만, 지도 교수님은 여러 학생들을 가지고 있다.

3. M:N 관계

 한 엔티티 타입에 속하는 임의의 개수의 엔티티가 다른 엔티티 타입에 속하는 임의의 개수의 엔티티와 연관될 경우 M:N 관계이다.

ex) 각 학생은 여러 강의를 수강할 수 있고, 각 강의는 여러 명의 학생들을 가질 수 있으므로 M : N 관계이다.

 카디날리티 비율의 최솟값과 최댓값을 관계와 엔티티를 연결하는 실선 위에 (min, max) 형태로 표기한다. 어떤 관계 타입에 참여하는 각 엔티티 타입에 대하여 min은 이 엔티티 타입 내의 각 엔티티는 적어도 min번 관계에 참여함을 의미한다. max는 이 엔티티 타입 내의 각 엔티티는 최대한 max 번 관계에 참여함을 의미한다.

min = 0은 어떤 엔티티가 반드시 관계에 참여해야 할 필요는 없음을 의미하고, max=*는 어떤 엔티티가 관계에 임의의 수만큼 참여할 수 있음을 의미한다.

 이와 비슷한 개념으로 전체 참여와 부분 참여가 있다. 전체 참여는 어떤 관계에 엔티티 타입 E1의 모든 엔티티들이 관계 타입 R에 의해서 어떤 엔티티 타입 E2의 어떤 엔티티와 연관되는 것을 의미한다. 부분 참여는 어떤 관계에 엔티티 타입 E1의 일부 엔티티만 참여하는 것을 의미한다. 약한 엔티티 타입은 항상 관계에 전체 참여로 표시되며, 전체 참여는 ER 다이어그램에서 이중 실선으로 표시된다. 

 위의 내용들에서 사용한 표기법으로 수십 개 이상의 애트리뷰트를 설명한다면 매우 불편하므로 새발 표기법이라는 것을 사용한다.

1. 다음 테이블 인스턴트 차트를 기반으로 DEPT 테이블을 생성하십시오.

CREATE TABLE dept(ID Number(7),NAME VARCHAR2(25));
describe dept;

2.DEPARTMENT 테이블의 데이터를 DEPT 테이블에 추가하십시오.

insert into dept(id , name)
select department_id,department_name from departments;

3. 다음 테이블 인스턴스 차트를 기반으로 emp 테이블을 생성하십시오.

Create table emp(ID Number(7),LAST_NAME VARCHAR2(25),FIRST_NAME VARCHAR2(25),DEPT_ID NUMBER(7));

4.EMP 테이블의 LAST_NAME 열의 최대 길이를 50으로 수정하십시오.

ALTER TABLE emp
modify (last_name varchar2(50));

5.EMP 테이블을 삭제하십시오.

drop table emp;

6. EMPLOYEES 테이블 구조를 기반으로 EMPLOYEES2 테이블을 생성하십시오.  EMPLOYEE_ID,first_name,last_name,salary 및  department_id 열만 포함시키고 새 테이블의 열 이름을 각각 ID,First_Name,Last_name,salary 및 dept_id로 지정하십시오.

 
create table EMPLOYEES2(ID number(6),FIRST_NAME varchar(20),LAST_NAME varchar(25),SALARY number(8,2),DEPT_ID number(4));

7. employees2 테이블의 이름을 emp로 변경하십시오.

RENAME employees2 to emp;

8.EMP 테이블에서 FIRST_NAME 열을 삭제하십시오.

ALTER TABLE emp
drop (first_name);

1. EMP 테이블의 ID 열에 테이블 레벨의 PRIMARY KEY 제약 조건을 추가. 제약 조건 이름은 my_emp_id_pk로 지정하시오.

 
alter table emp
add constraint "my_emp_id_pk" primary key(id);

2. ID 열을 사용하여 DEPT 테이블에 PRIMARY_key 제약 조 건을 생성. 제약 조건 이름은 my_dept_id_pk로 지정하시오.

alter table dept
Add (constraint "my_dept_id_pk" primary key(id));

3.EMP 테이블에 DEPT_ID 열을 추가. 존재하지 않는 부서 에 사원이 배정되지 않도록 외래 키 참조를 EMP 테이블에 추가. 제약 조건 이름은 my_emp_dept_id_fk로 지정하시오.

alter table emp
add (constraint "my_emp_dept_id_fk" foreign key(dept_id) references dept(id));

4.User_constaints 뷰를 질의하여 제약 조건이 추가되었는지 확인하시오. (EMP, DEPT 테이블만 질의)

select constraint_type,constraint_name, search_condition
from user_constraints
where table_name= 'emp' or table_name= 'dept';

5.EMP 테이블을 수정하여 십진 자릿수 2, 소수점 이하 자릿수 2인 NUMBER 데이터 유형의 COMMISSION 열을 추가하시오. 커미션 값이 0보다 크도록 커미션 열에 제약 조건을 추가하시오.

alter table emp
add (COMMISSION number(2,2));
alter table emp
add (constraint "commission_ck" check (commission>0));

1.모든 사원의 급여 최고액, 최저액, 총액 및 평균액을 표시하십시오. 열레이블을 각각 Maximum, Minimum, Sum 및Average로 지정하고 결과를 정수로 반올림하십시오.

 
select MAX(SALARY) as Maximum ,MIN(SALARY) as Minimum,SUM(SALARY) as "SUM" ,round(AVG(SALARY),0) as Average from employees;

2.1번을 수정하여 각 업무 유형별로 표시하십시오.

select job_id ,MAX(SALARY) as Maximum ,MIN(SALARY) as Minimum,SUM(SALARY) as "SUM" ,round(AVG(SALARY),0) as Average from employees group by job_id;

3.업무가 동일한 사원 수를 표시하는 질의를 작성하십시오.

select job_id,count(*)as "COUNT" from employees group by job_id;

4.관리자는 나열하지 말고 관리자 수를 확인하십시오. 열 레이블은 Number of Managers로 지정하십시오.

select count(*) as "Number of Managers" from employees group by manager_id;

5.최고 급여와 최저 급여의 차액을 표시하는 질의를 작성하고 열 레이블을 DIFFERENCE로 지정하십시오.

select MAX(salary)-Min(salary) as "DIFFERENCE" from employees;

6.관리자 번호 및 해당 관리자에 속한 사원의 최저 급여를 쵸시하십시오. 관리자를 알 수 없는 사원 및 최저 급여가 $6,000 미만인 그룹은 제외시키고 결과를 급여에 대한 내림차순으로 정렬하십시오.

select manager_id,min(salary) from employees where manager_id is not null and salary>6000 group by manager_id order by min(salary) desc;

7.각 부서에 대해 부서 이름, 위치, 사원 수, 부서 내 모든 사원의 평균 급여를 표시하는 질의를 작성하고, 열 레이블을 각각 Name,Locaton, Number of People 및 Salary로 지정하십시오. 평균 급여는 소수점 둘째 자리로 반올림하십시오.

select d.department_name as "Name", d.location_id as "Location", count(e.employee_id) as "Number of People",round(AVG(e.salary),2) as "Salary" from employees e,departments d
where e.department_id=d.department_id group by d.department_name,d.location_id;

8.총 사원 수 및 2005,2006,2007,2008년에 입사한 사원 수를 표시하는 질의를 작성하고 적합한 열 머리글을 작성하십시오.

 
select count(*) as "TOTAL", sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2005',1)) as "2005",sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2006','1')) as "2006" ,sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2007','1')) as "2007" ,sum(DECODE(TO_CHAR(hire_date,'YYYY'),'2008','1')) as "2008" from employees;

9.업무를 표시한 다음 해당 업무에 대해 급여 총액과 부서 별 급여(20,50,80,90)의 총액을 각각 표시하는 형렬 질의를 작성하고 각 열에 적합한 머리글을 지정하십시오.

select job_id as "Job",NVL(sum(DECODE(department_id,20,salary)),0) as "DEPT20" ,NVL(sum(DECODE(department_id,50,salary)),0) as "DEPT50",NVL(sum(DECODE(department_id,80,salary)),0)as "DEPT80",NVL(sum(DECODE(department_id,90,salary)),0) as "DEPT90",sum(salary)as "TOTAL" from employees group by job_id;

1. Zlotkey와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

select last_name,hire_date from employees where department_id =(select department_id from employees where last_name= 'Zlotkey') and not (last_name = 'Zlotkey');

2. 급여가 평균 급여보다 많은 모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하고 결과를 급여에 대해 오름차순으로 정렬하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(salary) from employees) order by salary;

3. 이름에 u가 포함된 사원과 같은 부서에서 일하는 모든 사원의 사원번호와 이름을 표시하는 질의를 작성하십시오.

select employee_id,last_name from employees where department_id in (select distinct department_id from employees where last_name like '%u%');

4.부서 위치 ID가 1700인 모든 사원의 이름, 부서 번호 및 업무 ID를 표시하십시오.

select e.last_name,e.department_id,e.job_id from employees e,departments d where e.department_id=d.department_id and d.location_id = (select distinct location_id from departments where location_id=1700);

5.King에게 보고하는 모든 사원 이름과 급여를 표시하십시오.

select last_name,salary from employees where manager_id in (select employee_id from employees where last_name= 'King');

6.Executive 부서에 모든 사원에 대한 부서 번호, 이름 및 업무 ID를 표시하십시오.

select department_id,last_name,job_id from employees where department_id =(select department_id from departments where department_name= 'Executive'); 

7.평균 급여보다 많은 급여를 받고 이름에 u가 포함된 사원과 같은 부서에서 근무하는 모든 사원의 번호, 이름 및 급여를 표시하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(salary) from employees) and department_id in (select distinct department_id from employees where last_name like '%u%');

8.미국 내에서 근무하는 사원들의 평균급여보다 많은 급여를 받는 사원의 번호, 이름 및 급여를 표시하십시오.

select employee_id,last_name,salary from employees where salary >=(select avg(e.salary) from employees e,departments d where d.location_id in (select distinct location_id from locations where country_id= 'US'));

9.부서 별로 최고 급여를 받는 사원의 번호, 이름, 급여 및 부서 번호를 표시하고 부서 번호에 대해 오름 차순 정렬을 하시오.

 
select employee_id,last_name,salary, department_id from employees where (department_id,salary) in (select department_id,max(salary) from employees group by department_id) order by department_id;

10. From 절을 사용하여 9번 질의를 재 작성하시오.

select e1.employee_id, e1.last_name, e1.salary , e1.department_id
from   employees e1 inner join (select department_id, max(salary) as maxSalary
                                from   employees
                                group by department_id) e2 on e1.salary = e2.maxSalary and e1.department_id = e2.department_id order by e1.department_id;

11.사원이 한 명 이상 존재하는 부서의 번호 및 부서 이름을 표시하시오. (exists 키워드 사용)

select department_id,department_name from departments d where exists (select department_id from employees where department_id=d.department_id);

12. 다음을 참고하여 급여를 가장 적게 받는 사원 5명에 대한 정보를 표시하시오.

select rownum,employee_id,last_name,salary from (select employee_id,last_name,salary from employees order by salary) where rownum <=5; 

1.employees 테이블을복사하여cpy_emp테이블을생성하시오.describe문을사용하여employees와cpy_emp의스키마가동일한지확인하기바랍니다.

Create table cpy_emp as select * from employees;
describe cpy_emp;
describe employees;*/

2.다음 예제 데이터의 첫 번째 데이터 행을 cpy_emp 테이블에 추가하십시오. Insert 절에 열을 나열하지 마십시오.

Insert into cpy_emp
values(300,'Ralph','Patel','Rpatel',NULL,sysdate,'SA_MAN',NULL,NULL,NULL,NULL);

3.위의 목록에 있는 예제 데이터의 두 번째 행을 cpy_emp 테이블에 추가하십시오. 이번에는 Insert 절에 열을 명시적으로 나열하십시오.

 
Insert into cpy_emp(employee_id,first_name,last_name,email,hire_date,job_id)
values(301,'Dancs','Betty','Bdancs',sysdate,'SA_REP');

4.301번 사원의 Last_name을 Drexier로 변경하십시오.

update cpy_emp set last_name= 'Drexler' where last_name= 'Betty';

5. 300, 301번 사원의 나머지 사원 정보를 Vance Jones 와 동일하게 변경하시오.

update cpy_emp
set phone_number=(select phone_number from employees where first_name= 'Vance' and last_name= 'Jones'),
salary=(select salary from employees where first_name= 'Vance' and last_name= 'Jones'),
commission_pct=(select commission_pct from employees where first_name= 'Vance' and last_name= 'Jones'),
manager_id=(select manager_id from employees where first_name= 'Vance' and last_name= 'Jones'),
department_id=(select department_id from employees where first_name= 'Vance' and last_name= 'Jones')
where employee_id= '301' or employee_id= '300';

6.급여가 3000미만인 모든 사원의 급여를 3000으로 변경하십시오.

update cpy_emp
set salary=3000
where salary<3000;

7.Dancs Drexler 사원의 정보를 삭제하십시오.

delete from cpy_emp
where last_name= 'Drexler' and first_name= 'Dancs';