Physical Database Design

Sally Enterprise Logical Design 요약 RECIPE (Name, Sugar, Lemon, Water, Where_used) Key: Name Note: Where_used는 이 RECIPE를 사용한 PITCHER tuple 이다; multi valued PITCHER (Number, Date, Recipe_used, Where_sold) Key: Number Note: 1. Where_sold는 이 PITCHER를 주문한 ORDER tuple이다; multi valued 2. Current_Pitcher와 Amount_left는 class 속성이다. SALESPERSON (Name, Past_amount_sold) Key: Name Note: Past_amount_sold는 ORDER클래스의 Amount속성값이다; Multi valued CUSTOMER (F_name, L_name, Family, Past_amount_purchased, Total_amount) Key: (F_name, L_name) Note: Family는 같은 L_name을 갖는 CUSTOMER tuple이다; multi valued Past_amount_purchased는 ORDER의 amount 속성값이다; multi valued Total_amount는 SUM(Past_amount_purchased)로 계산되는 값이다. ORDER (Customer, Pitcher, SalesPerson, Date, Time, Amount) Key: (Customer, Pitcher, Time) Note: 1. Customer는 F_name과 L_name으로 이루어진다. 2. ORDER는 두개의 PITCHER로부터 이루어질 수 있으며 이 경우 multi valued이다 BIG_BUYER (attributes of CUSTOMER, Nickname, Sport) Key: (F_name, L_name Note: Sport 는 3개까지 허용되는 multi valued이다

RECIPE 의 Where_used 속성은 PITCHER tuple이고 여러 개의 PTICHER에 사용될 수 있으므로 여러 번 반복될 수 있다(multi valued로 표현되어 있음). PITCHER 에는 ORDER tuple인 Where_sold와 , RECIPE tuple인 Recipe_used를 가지고 있다. 또한 PITCHER에는 세 개의 클래스 속성(Total_produced, Active_Pitcher, Amount_left)가 있다.(앞 페이지에서는 생략됨) SALESPERSON 에는 ORDER tuple의 Amount속성이 있다. 같은 SALESPERSON이 여러 개의 ORDER를 처리할 수 있으므로 이 또한 multi valued이다. CUSTOMER 의 Family_members 속성은 그 자체가 다른 CUSTOMER tuple이고 multi valued이다. 또한 Past_amount_purchased는 Amount 속성을 가지고 있는 ORDER의 반복이고 Totalamount는 ORDER의 Amount 속성의 총 합과 같다. ORDER는 다른 클래스의 tuple을 포함하고 있지 않다. 그러나 반복적인 요소는 있다. 하나의 ORDER가 두 개의 PITCHER에서 이루어 졌을 경우(남은 양이 모자라서 새로 제조한 경우) Pitcher가 두 번 반복될 수 있다. BIG_BUYER 는 CUSTOMER의 확장으로서 Nick_name과 Favorite_sport가 최대 세 번 반복될 수 있다.

포함된 다른 Tuple과 반복속성의 제거 RECI_PITCH 관계 DB(Relational DB)에서는 Multi valued 속성이 Tuple(행)에 허용되지 않는다. 또한 하나의 Tuple에 다른 Tuple을 포함하는 것이 허용된다면 두 개의 Tuple이 서로를 재귀적으로 포함하게 될 것이므로 이것도 물리적인 DB에서는 허용될 수 없다. 이 문제를 해결하기 위하여 RECIPE와 PITCHER를 다음과 같이 하나의 Relation으로 join하여 나타낼 수 있다. (Where_sold 속성은 테이블의 크기를 줄이기 위하여 생략됨) RECI_PITCH Name Sugar Lemon Water Where_used Number Date Recipe_used A 2 4 3 101 101 190731 A A 2 4 3 104 104 190801 A A 2 4 3 107 107 190804 A B 3 3 2 102 107 190802 B B 3 3 2 105 105 190803 B Equi Join형태의 테이블

RECI_PITCH PITCHER RECIPE 앞 페이지의 테이블에서 중복된 데이터를 제거한 형태의 단순화된 테이블은 아래와 같다. Name Sugar Lemon Water Number Date A 2 4 3 101 180731 A 2 4 3 104 180801 A 2 4 3 107 180804 B 3 3 2 107 180802 B 3 3 2 105 180803 RECI_PITCH Natural Join형태의 테이블 이 테이블이 필요하기는 하지만 정규화된 형태의 작은 테이블로 나누고 필요한 시점에 Join연산을 하용하여 다시 구성할 수 있도록 하는 것이 더 바람직하다. 아래는 위의 테이블을 두 개의 작은 테이블로 나뉜 것을 보여주고 있다. Number Date Recipe_used 101 180731 A 104 180801 A 107 180804 A 102 180802 B 105 180803 B PITCHER Name Lemon Sugar Water A 2 4 3 B 3 3 2 RECIPE

SELECT PITCHER.Number, PITCHER,Date FROM PITCHER, RECIPE 논리적 설계로부터 얻은 첫 번 째 테이블(RECI_PITCH)은 다음 DML(SQL)을 통하여 두 개의 테이블을 Join하면 다시 재 구성할 수 있다. SELECT PITCHER.Number, PITCHER,Date FROM PITCHER, RECIPE WHERE PITCHER.Recipe_used = RECIPE.Name 예를 들어서 레몬이 3개 들어간 레시피를 사용한 Pitcher의 번호와 제조 날짜를 추출하려면 다음 SQL을 사용할 수 있다. SELECT * FROM PITCHER, RECIPE WHERE PITCHER.Recipe_used = RECIPE.Name AND RECIPE.Lemon = 3 102 180802 105 180803 Tuple 다른 tuple이 반복되는 것을 제거하는 방법을 요약하면 다음과 같다. ① 1 대 다 관계에서 자식의 입장에 있는 클래스의 tuple에서 부모 tuple을 부모 tuple의 Key로 대체한다. ② 부모 클래스의 tuple에서 반복되는 자식 tuple을 모두 제거한다. ③ 두 클래스의 관계에 따른 tuple을 얻기 위하여서는 부모의 키 값을 사용하여 Join한다.

앞에서 설명한 방법은 RECIPE와 PITCHER 사이에서 뿐 아니라. PITCHER와 ORDER, SALESPERSON과 ORDER, CUSTOMER와 ORDER 사이에서도 사용될 수 있다. 이 들이 모두 1대 다 관계이기 때문이다. 이와 같이 테이블을 정리해 나가는 과정에서 항상 두 Relation의 속성 값의 제약(Interrelation constraints)을 생각해 보아야 한다. 예를 들어서 PITCHER의 Where_used속성 값은 RECIPE에 존재하지 않는 Name을 사용할 수 있는가? 혹은 RECIPE에는 PITCHER에서 한번도 사용하지 않은 tuple도 존재할 수 있는가? 등이다. PITCHER의 Where_used에 존재하지 않는 RECIPE의 Name이 사용될 수 없는 것이 당연하므로 다음과 같은(혹은 다른 표현을 사용하여) 이 제약을 기록해 두어야 한다. PITCHER[Recipe_used] SUBSET OF RECIPE[Name]

아래는 지금까지 설명한 방법을 적용하여 정리된 물리적인 DB의 테이블과 속성필드명이다) RECIPE (Name, Sugar, Lemon, Water) Key: Name PITCHER (Number, Date, Recipe_used) Key: Number SALESPERSON (Name) CUSTOMER (F_Name, L_Name) Key: (F_name, L_name) ORDER (Cust_f_name, Cust_l_name, Pitcher, Salesperson, Date, Tie, Amount) Key: Cust_f_name, Cust_l_name, Pitcher, Time) NICKNAME (Cust_f_name, Cust_l_name, Nickname) Key: (Cust_f_name, Cust_l_name) CUST_SPORT (Cust_f_name, Cust_l_name, Favorite_sport) Key: (Cust_f_name, Cust_l_name, Favorite_sport) CLASS_ATTRIBUTE (Relation_name, Attribute_name, Value) Key: (Relation_name, Attribute_name) Relation of Sally Enterprises Schema 3

PITCHER [Recipe_used] SUBSET OF RECIPE [Name] ORDER [Pitcher] SUBSET OF PITCHER [Number] ORDER [Cust_f_name, Cust_l_name] SUBSET OF CUSTOMER [F_name, L_name] ORDER [Salesperson] SUBSET OF SLAESPERSON[Name] NICKNAME [Cust_f_name, Cust_l_name] SUBSET OF CUSTOMER [F_name, L_name] CUST_SPORT[Cust_f_name, Cust_l_name] SUBSET OF CUSTOMER [F_name, L_name] Relation간의 제약사항 기술 Domain Name Format and meaning CUPS 숫자 99.99; 설탕의 양 DATES 숫자문자 YYMMDD LEMON_COUNT 10 미만의 양의 정수 F_NAMES CHAR(10); 고객의 이름 L_NAMES CHAR(20); 고객의 성 S_NAMES CHAR(20); 영업사원의 이름 N_NAMES CHAR(20); 고객의 별칭 PITCHER_NUMBERS 500미만의 양의 정수 QUARTS 숫자 9.99; 물과 제조된 레모네이드의 양 RECIPE_NAMES CHAR(10) SPORTS_NAMES ‘축구’, ‘미식축구’, ‘테니스’, ‘농구’, ‘스키’ 중 하나 TIMES HH.MM HH는 0~23 사이의 정수문자, MM은 0~59사이의 정수 문자. Domains

Attributes Domain RECIPE.Name RECIPE_NAMES RECIPE.Sugar CUPS RECIPE.Lemon LEMON_COUNT RECIPE.Water QUARTS PITCHER.Number PITCHER_NUMBERS PITCHER.Date DATES PITCHER.Recipe_used RECIPE_NAMES SLAESPERSON.Name S_NAMES CUSTOMER.F_name F_NAMES CUSTOMER.L_name L_NAMES ORDER.Cust_f_name F_NAMES ORDER.Cust_l_name L_NAMES ORDER.Pitcher PITCHER_NUMBERS ORDER.SalesPerson S_NAMES ORDER.Date DATES ORDER.Time TIMES ORDER.Amont QUARTS NICKNAME.Cust_f_name F_NAMES NICKNAME.Cust_l_name L_NAMES NICKNAME.Nickname N_NAMES CUST_SPORT.Cust_f_name F_NAMES CUST_SPORT.Cust_l_name L_NAMES CUST_SPORT.Favorite_sport SPORT_NAMES 속성과 속성의 Domain