1 Chapter2. XML Structures for Existing Databases Database 연구실 석사 1 학기 편선경.

1

Chapter2. XML Structures for Existing Databases

Database 연구실

석사 1 학기편선경

2

XML 로 데이터 표현 이유 Sharing business data with other systems Interoperability with incompatible

systems Exposing legacy data to applications that

use XML Business-to-business transactions Object persistence using XML Content syndication

3

Migrating a Database to XML Rule1. Choose the Data to Include Rule2. Create a Root Element Rule3. Model the Content Tables Rule4. Modeling Nonforeign Key Columns Rule5. Add ID Attributes to the Elements Rule6. Representing Lookup Tables Rule7. Adding Element Content to Root elements Rule8. Adding Relationships through Containment Rule9. Adding Relationships using IDREF/IDREFS Rule10. Add missing Elements Rule11. Remove Unwanted ID Attributes

ShipMethod

ShipMethodType

Description

Invoice

CustomerKey

InvoiceKey

InvoiceNumber

TrackingNumber

OrderDate

ShipDate

ShipMethodType

Customer

CustomerKey

Name

Address

City

State

PostalCode

Part

PartKey

PartNumber

Name

Color

Size

LineItem

InvoiceKey

PartKey

Quantity

Price

MonthlyPartTotal

Month

Year

PartKey

VolumnShipped

PriceShipped

MonthlyTotal

Month

Year

VolumnShipped

PriceShipped

MonthlyCustomerTotal

Month

Year

CustomerKey

VolumnShipped

PriceShipped

lookup

5

Rule1. Choose the Data to Include Business requirements

What the data is going to be used for How it is going to be used

ex) monthly invoice total 과 customer-by-customer

breakdown 에 관한 정보를 회계사 사무실로 보내야 할 경우

(Customer, MonthlyTotal, MonthlyCustomerTotal)

6

Rule2. Create a Root Element Add the root element to our DTD Declare any attributes of that

element Root element’s names should

describe their content<!ELEMENT SalesData EMPTY>

<!ATTLIST SalesData

Status (NewVersion | UpdateVersion | CourtesyCopy) #REQUIRED>

7

Rule3. Model the Content Tables(1) Content table

contain a set of records (for example, all the customer addresses for a certain company)

Lookup table contain a list of ID-description pairs (for example, ShipMethod)

8

Rule3. Model the Content Tables(2)

<!ELEMENT SalesData EMPTY>

<!ATTLIST SalesData


<!ELEMENT Invoice EMPTY>

<!ELEMENT Customer EMPTY>

<!ELEMENT Part EMPTY>

<!ELEMENT MonthlyTotal EMPTY>

<!ELEMENT MonthlyPartTotal EMPTY>

<!ELEMENT LineItem EMPTY>

9

Rule4. Modeling Nonforeign Key Columns !ATTLIST 로 표현하고 , 각각의

attribute 에서 CDATA type 을 선언해 준다 .

Data 의 null 값 허용 여부에 따라서 다음과 같이 선언한다 .

Does the column allow NULLS? Elements Attributes

Allows NULLS Use the ? Suffix Declare as #IMPLIED

Doesn’t allow NULLS Use no suffix Declare as #REQUIRED




<!ATTLIST SalesData



<!ATTLIST Invoice

InvoiceNumber CDATA #REQUIRED

TrackingNumber CDATA #REQUIRED

OrderDate CDATA #REQUIRED

ShipDate CDATA #REQUIRED


<!ATTLIST Customer

Name CDATA #REQUIRED

Address CDATA #REQUIRED

Citiy CDATA #REQUIRED

State CDATA #REQUIRED

PostalCode CDATA #REQUIRED


<!ATTLIST Part

PartNumber CDATA #REQUIRED


Color CDATA #REQUIRED

Size CDATA #REQUIRED


<!ATTLIST MonthlyTotal

Month CDATA #REQUIRED

Year CDATA #REQUIRED

VolumnShipped CDATA #REQUIRED

PriceShipped CDATA #REQUIRED

<!ELEMENT MonthlyCustomerTotal EMPTY>

<!ATTLIST MonthlyCustomerTotal




<!ATTLIST LineItem

Quantity CDATA #REQUIRED

Price CDATA #REQUIRED

11

Rule5. Add ID Attributes to the Elements Root element 를 제외한 각각의

element 에 ID attribute 를 더한다 . Element 의 이름뒤에 ID 를 붙이고 ,

IDtype 과 #REQUIRED 를 선언한다 .


<!ATTLIST SalesData



<!ATTLIST Invoice

InvoiceID ID #REQUIREDInvoiceID ID #REQUIRED






<!ATTLIST Customer

CustomerID ID #REQUIREDCustomerID ID #REQUIRED





PostalCode CDATA #REQUIRED>


<!ATTLIST Part

PartID ID #REQUIREDPartID ID #REQUIRED




Size CDATA #REQUIRED



MonthlyTotalID ID #REQUIREDMonthlyTotalID ID #REQUIRED







MonthlyCustomerTotalID ID #REQUIREDMonthlyCustomerTotalID ID #REQUIRED




<!ATTLIST LineItem

LineItemID ID #REQUIREDLineItemID ID #REQUIRED



13

Rule6. Representing Lookup Tables (Handling Foreign Key) foreign key 가 발견되는 테이블을 나타내는

element 에 attribute 를 만든다 . attribute 이름은 foreign key 가 참조하는

테이블명과 같게 하고 , foreign 가 null 을 갖지 않으면 #REQUIRED, 그 외에는 #IMPLIED 로 선언한다 .

lookup table 안의 모든 description column 은 사람이 알아볼 수 있는 형식으로 하여 enumerated list type 으로 선언한다 .

14

Rule6. Representing Lookup Tables (Handling Foreign Key)

ShipMethod Description

1 US Postal Service

2 Federal Express

3 UPS

….


<!ATTLIST Invoice

InvoiceID ID #REQUIRED





ShipMethod (USPS | FedEx | UPS) #REQUIRED>ShipMethod (USPS | FedEx | UPS) #REQUIRED>


….

15

Rule7. Adding Element Content to Root elements (Handling Foreign Key)

표현하고자 하는 정보의 형태를 모델화하는 각각의 테이블에 대하여 root element 에 element content 를 더한다 .

<!<!ELEMENT SalesData (Invoice*, MonthlyTotal*)>ELEMENT SalesData (Invoice*, MonthlyTotal*)>

<!ATTLIST SalesData



…..

16

Rule8. Adding Relationships through Containment (Handling Foreign Key)

정의된 각각의 relation 에 대하여 일대일 또는 일대다 관계이면 parent element 의 element content 로서 child element 를 더한다 .

If the relation is Set the multiplicity to

One-to-one ?

One-to-many *

<!ELEMENT SalesData (Invoice*, MonthlyTotal*)>

<!ATTLIST SalesData


<!ELEMENT Invoice (LineItem*)><!ELEMENT Invoice (LineItem*)>

<!ATTLIST Invoice






ShipMethod (USPS | FedEx | UPS) #REQUIRED>

…..

<!ELEMENT MonthlyTotal (MonthlyCustomerTotal*, MonthlyPartTotal*)><!ELEMENT MonthlyTotal (MonthlyCustomerTotal*, MonthlyPartTotal*)>


MonthlyTotalID ID #REQUIRED





……

18

Rule9. Adding Relationships using IDREF/IDREFS (Handling Foreign Key)

relation 이 다대일이거나 parent 가 하나이상일 경우 relation 의 parent쪽의 element 에 IDREF 또는 IDREFS 의 attribute 를 더한다

<!ELEMENT SalesData (Invoice*, MonthlyTotal*)>

<!ATTLIST SalesData


<!ELEMENT Invoice (LineItem*)>

<!ATTLIST Invoice






ShipMethod (USPS | FedEx | UPS) #REQUIRED

CustomerIDREF IDREF #REQUIRED>CustomerIDREF IDREF #REQUIRED>


…..



MonthlyCustomerTotalID ID #REQUIRED



CustomerIDREF IDREF #REQUIRED>CustomerIDREF IDREF #REQUIRED>


<!ATTLIST MonthlyPartTotal

MonthlyPartTotalID ID #REQUIRED



PartIDREF IDREF #REQUIRED>PartIDREF IDREF #REQUIRED>


<!ATTLIST LineItem

LineItemID ID #REQUIRED



PartIDREF IDREF #REQUIRED>PartIDREF IDREF #REQUIRED>

20

Rule10. Add Missing Elements <customer> 나 <part> 처럼 참조되는

element 를 root element 의 content element 로서 끝에 * 과 함께 더해준다 .

<!ELEMENT SalesData (Invoice*, Customer*, Part*,Customer*, Part*, MonthlyTotal*)>

<!ATTLIST SalesData


<!ELEMENT Invoice (LineItem*)>

…..

21

Rule11. Remove Unwanted ID Attributes XML Structure 에서 다른 경우에

IDREF 또는 IDREFS 에 의해 참조되지 않은 ID attribute 를 제거한다 .

즉 , InvoiceID, LineItemID, MonthlyPartTotalID, MonthlyTotalID, MonthlyCustomerTotalID attribute를 제거한다 .

이 과정을 마치면 최종적인 structure 를 완성하게 된다 .

<!ELEMENT SalesData (Invoice*, Customer*, Part*, MonthlyTotal*)>

<!ATTLIST SalesData


<!ELEMENT Invoice(LineItem*)>

<!ATTLIST Invoice





ShipMethod (UPS | FedEx | UPS) #REQUIRED

CustomerIDREF IDREF #REQUIRED>


<!ATTLIST Customer

CustomerID ID #REQUIRED





PostalCode CDATA #REQUIRED>


<!ATTLIST Part

PartID ID #REQUIRED




Size CDATA #REQUIRED>

<!ELEMENT MonthlyTotal (MonthlyCustomerTotal*, MonthlyPartTotal*)>










CustomerIDREF IDREF #REQUIRED>


<!ATTLIST MonthlyPartTotal



PartIDREF IDREF #REQUIRED>


<!ATTLIST LineItem



PartIDREF IDREF #REQUIRED>

An Example XML Document

<?xml version=“1.0”?>

<!DOCTYPE SalesData SYSTEM http://myserver/xml/ch03_ex01.dtd>

<SalesData Status=“Newversion”>

<Invoice InvoiceNumber=“1”

TrackingNumber=“1”

OrderDate=“01012000”

ShipDate=“07012000”

ShipMethod=“FedEx”

CustomerIDREF=“Customer2”>

<LineItem Quantitiy=“2”

Price=“5”

PartIDREF=“Part2”/>

</Invoice>

<Customer CustomerID=“Customer2”

Name=“BobSmith”

Address=“2AnyStreet”

City=“Anytowm”

State=“AS”

PostalCode=“ANYCODE” />

<Part PartID=“Part2”

PartNumber=“13”

Name=“Winkle”

Color=“Red”

Size=“10”/>

<MonthlyTotal Month=“January”

Year=“2000”

VolumnShipped=“2”

PriceShipped=“10”>

<MonthlyCustomerTotal VolumnShipped=“5”

PriceShipped=“25”

CustomerIDREF=“Customer2”/>

<MonthlyPartTotal VolumnShipped=“8”

PriceShipped=“40”

PartIDREF=“Part2”/>

</MonthlyTotal></SalesData>

http://myserver/xml/ch03_ex01.dtd






1 Chapter2. XML Structures for Existing Databases Database 연구실 석사 1 학기 편선경.

Documents

Transcript of 1 Chapter2. XML Structures for Existing Databases Database 연구실 석사 1 학기 편선경.