Physical Database Design Sally Enterprise Logical Design RECIPE

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, Sales. Person, 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이다

아래는 지금까지 설명한 방법을 적용하여 정리된 물리적인 DB의 테이블과 속성필드명이다) RECIPE (Name, Sugar, Lemon, Water) Key: Name PITCHER (Number, Date, Recipe_used) Key: Number SALESPERSON (Name) Key: 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 DATES LEMON_COUNT F_NAMES L_NAMES S_NAMES N_NAMES PITCHER_NUMBERS QUARTS RECIPE_NAMES SPORTS_NAMES TIMES 숫자 99. 99; 설탕의 양 숫자문자 YYMMDD 10 미만의 양의 정수 CHAR(10); 고객의 이름 CHAR(20); 고객의 성 CHAR(20); 영업사원의 이름 CHAR(20); 고객의 별칭 500 미만의 양의 정수 숫자 9. 99; 물과 제조된 레모네이드의 양 CHAR(10) ‘ 축구’, ‘미식축구’, ‘테니스’, ‘농구’, ‘스키’ 중 하나 HH. MM HH 는 0~23 사이의 정수문자, MM 은 0~59사이의 정수 문자. Domains

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