Teorey J., Lightstone S., Nadeau T. Database Modeling and Design: Logical Design

Подождите немного. Документ загружается.

(a)

(b)

Supplier [SuppID]

Street ZipcodeStateCity

Phone

Name

YearEstablished

Supplier [SuppID]

Street Zipcode

Address

StateCity

PhoneName

YearEstablished

Figure 9.10 Two schema

designs: (a) a design with

inadequate grouping, and

(b) a better design.

(b)

Employee [EmpID]

Sex

Phone

Name

JobClass

(a)

Employe [EmpID]

Female

Faculty

Staff

Male

Phone

Name

Administrator

Figure 9.11 Two schema

designs: (a) a design with

improperly promoted data,

and (b) a better design.

172 Chapter 9 XML AND WEB DATABASES

attributeValue. With such a table you can represent

anything you wish! But note that attributeName is a

column that stores as values information that is better

represented as real names of attributes. See Figure 9.12.

Text

XML is very similar to HTML and therefore is great for

representing text. XML tags provide a means for structuring

text documents. A very simple, completely unstructured doc-

ument could have just a start and an end tag, with thousands

of words of text in between. Of course, the typical XML docu-

ment has much more structure than this. However, there still

could be large amounts of text between any pair of tags.

In short, a pure text document is represented in XML as

a set of nested tags, just as a database is. In the case of a

document, the tags may represent document constructs,

such as chapter, section, paragraph, etc., whereas in a data-

base, the tags represent schema elements as we saw above.

Thus, there is an opportunity to merge text documents

and structured databases. It is straightforward to have an

arbitrarily complex document be a schema element any-

where in an XML database. In fact, it is just as straightfor-

ward to have an arbitrarily complex database be a

component of an XML document that otherwise contains

text. XML even permits the two types of elements to be inter-

mixed, leading to arbitrarily deep nestings of documents in

databases in documents. See

Figure 9.13.

Let us consider how this works in practice, by looking at a

couple

of examples at different points in the

spectrum of

objectID attributeName attributeValue

123 Title Iliad

123 Author Homer

123 Price 22

135 Title Macbeth

135 Author Shakespeare

135 Price 17

135 Year 1605

Figure 9.12 A triple store

showing demoted metadata.

Chapter 9 XML AND WEB DATABASES 173

balancing text and structure. For our first example, consider

a structured database with an address field. In a typical

relational database design, this address field may be a single

variable-length character string. With XML, there is an

opportunity to break down the address into its components,

each as a separate XML element. Note that the flexibility of

XML, where some elements may be absent, is important

here—one address may have an apartment number, for

example, and another may not. Because of these differences

in the expected structure of addresses, it is painful to pull out

individual components of a street address for a relational

database. Indeed, this decomposition is rarely done beyond

<SALES-REPORT Date¼”Jan 11, 2011”>

<To> Bertha the Boss </To>

<From> Wally Worker </From>

There was a healthy uptick in our sales last quarter, across all product categories, as you will see from

the table below.

<Product-Category>

<Name> Twist Ties </Name>

<Previous-Quarter-Sales> 220 </Previous-Quarter Sales>

</Product-Category>

<Product-Category>

<Name> Shoestrings < /Name>

<Previous-Quarter-Sales>200 </Previous-Quarter Sales>

<Footnote> Previous quarter sales do not include sales of Cowlick Corp., which we have since

acquired, and whose sales are included in the fourth quarter numbers. </Footnote>

</Product-Category>

<Product-Category>

<Name> Bungee Cords </Name>

<Sa

les> 770 </Sales>

<Previous-Quarter-Sales> 740 </Previous-Quarter Sales>

</Product-Categor

<Table>

Whereas we expect continued growth in the shoestring category this quarter, on account of our recent

acquisition of Cowlick Corp., whether we see growth in other product categories will depend primarily

on the state of the overall economy.

</Report>

</SALES-REPORT>

Figure 9.13 An XML document with nested data.

174 Chapter 9 XML AND WEB DATABASES

city, state, ZIP code, and countr y—street addresses are

almost always stored as a single string. With this decomposi-

tion in place in XML, we are able to ask queries about street

addresses that would be very difficult otherwise. For exam-

ple, with an address represented as in Figure 9.14, one can

issue queries such as “Find persons

who live in apartment

number 23 (of their building or housing complex).”

For a second example, consider a bibliographic database

that includes abstracts of articles. The abstract is

a text

“field” that may have additional document structure inter-

nal to it, such as multiple paragraphs, a keyword list, and

so on. The primary way in which this case differs from the

previous one (the example about street addresses) is that

the quantity of text is much greater. In consequence,

<Name> Maria Eder </Name>

<Street> Gartenweg </Street>

<Locality> Rafing </Locality>

<City> Pulkau </City>

<Country> Austria </Austria>

</Address>

<Name> LIM Chee-Seung </Name>

<Building> Acacia Building</Building>

<Street> Kennedy Road </Street>

<Locality> W

an Chai </Locality>

<Country> China </Country>

</Address>

<Addr

ess>

<Name> John Smith </Name>

<Street> Downing Street </Street>

<City> London < /City>

<Country> United Kingdom </Country>

</Address>

</InternationalFriendsAddresses>

Figure 9.14

A heterogeneous list of

addresses, each

decomposed into multiple

XML elements, all within a

single XML document .

Chapter 9 XML AND WEB DATABASES 175

selection queries are unlikely to specify the whole text field

or even large parts of it. Rather, we may specify a few query

terms and want “relevant” text snippets to be found. This is

how information retrieval works, and this query paradigm

is ver y different from that of a database, where a query is

defined as the satisfaction of a logic predicate with a very

well-defined answer set. Putting these two query paradigms

together is hard, and is the subject of continuing research.

XML Data in an RDBMS

Relational database management systems (RDBMSs) are

ubiquitous, and XML data is often stored in an RDBMS. The

simplest way to do this is to treat the entire XML document

as a single BLOB: a large data object of which the inner details

are not interpreted by the relational database. The drawback

of this setup is that the entire XML object has to be retrieved

from the database and into an XML data management system

before any data from it can be accessed. This can be expen-

sive, particularly when the size of the XML stored is large.

One attempt to manage size could be to divide the XML

database to be stored into multiple XML documents, each

stored in a separate BLOB. But then, we would still need to

extract labels or indexing attributes to be able to determine

which BLOBs to access in response to any query.

With object-relational capabilities, one can define a user-

defined XML “type” and build suitable user-defined functions

(UDFs) with it. This way, some of the difficulties mentioned

above can be ameliorated. Nonetheless, since UDFs remain

second-class citizens in a relational database, this sort of

implementation usually still leaves a lot to be desired.

The typical way to store XML data in an RDBMS is to

“shred” it into records that can be stored in relational tables.

Each data element and each attribute in the XML becomes

an attribute value in the relational representation. Each

relational record represents a set of related elements and

attributes. Our challenge is to create sets of relational records

with identical structures, which can be represented as a

table, given the heterogeneity and repetition so common in

XML. The design considerations we face are similar to those

for object-relational mapping, as discussed in Chapter 8.

See the example shown in

Figure 9.15.

176 Chapter 9 XML AND WEB DATABASES

<Book>

<Name> Crown </Name>

<Street> 3 Canyon Drive < /Street>

</Address>

</Publisher>

<Name> Bonzo </Name>

<Institution> Ringling Brothers </Institution>

</Author>

</Book>

<Book>

<Title> Database Design </Title>

ame> Elsevier </Name>

<Street> 5 Cloudy Way </S

treet>

<City> Paradise, MI </City>

</Address>

</Publisher>

<Name> Teorey </Name>

<Institution> University of Michigan </Institution>

</Author>

<Name> Lightstone </Name>

</Author>

</Book>

</Library>

ISBN Title PubName PubAddrStreet PubAddrCity PubAddrZip

123 A Fun Book Crown 3 Canyon Drive Hell, MI 48169

258 Database Design Elsevier 5 Cloudy Way Paradise, MI 49768

ISBN authorName authorInstitution

123 Bonzo Ringling Bros.

258 Teorey U. Michigan

258 Lightstone IBM

Figure 9.15 An XML document and its relational representation.

Web-Based Applications

Databases often power websites today. The Web is the

medium through which many databases are accessed.

In this section, we will first present an over view of how data

is organized on the Web, and then discuss some of the design

considerations when databases are placed on the Web.

An Overview of HTTP

The Internet makes it possible for computers around

the world to communicate with one another. Using these

facilities, the first services constructed included email and

file transfer. In 1994, Tim Berners-Lee built a system con-

necting files on the Internet in a new way—this is what

has grown into “the Web” today.

The central notion of the Web is that there is asymmetry

between servers and clients. There are Web servers that

have information resources (files, Web pages, database

query results) that they can provide to remotely located

Web clients. How precisely this transfer is orchestrated is

specified in the HyperText Transfer Protocol (HTTP).

HTTP is a request/response protocol (Figure 9.16).

The

protocol begins with a request from the

client to the server,

followed by a response from the server to the client. A typ-

ical request from the client is to ask the server for a

resource, to which a typical response is for the server to

provide the resource. There is no way for the server to ini-

tiate communication using HTTP.

Client

Server

Request

Response

Internet

Figure 9.16 An overview of

HTTP: A request from the

client gets a response from

the server.

178 Chapter 9 XML AND WEB DATABASES

Communication in each direction includes a header,

with all sorts of useful fields, and an optional body, with

the actual payload. HTTP specifies the fields that may be

included in the header, and the exact format in which to

specify each field. The body is just treated as a bucket of

bits by HTTP: It is not manipulated in any way. The client

and server each implement HTTP, by which we mean that

they write out headers in the manner specified by HTTP

and also read and interpret HTTP headers.

HTTP defines eight methods, or types of requests that

can be made. The GET method is by far the one used most

often. The client specifies the resource of interest to it, in a

request that looks something like this:

GET /foo.html HTTP/1.1

Referer: http://www.google.com/search?q¼webþdatabases

The server, in response, will send the file foo.html back,

if indeed such a file exists at the server. The response also

includes, among other things, a status code field in its

header—for example, “200 OK” or “404 Not found.”

Thus, the response may have a header that looks as

follows, along with a body that comprises the requested

resource (foo.html):

HTTP/1.1 200 OK

Server: Apache/2.2.11 (Unix)

Content-Type: text/html

GET has some limitations—for example, no more than

1024 bytes may be used. The POST method is similar to

GET, except that it has a body in addition to the request

header. Whenever copious or complex information is to

be sent as part of the request, POST is the method used.

The flexibility afforded by having a separate body part

comes at a cost: Storing POST requests is problematic,

since just the header does not suffice. Similarly, POST

requests are difficult to link to or bookmark. Note that

the POST method is also a request that receives a response,

similar to the response for GET. In other words, even

though the name of the method is POST, it also can be

used to “get” a remote resource.

Since the HTTP protocol is asymmetric, different pieces

of software implement the client and the server. The HTTP

Chapter 9 XML AND WEB DATABASES 179

client protocol is implemented by Web browsers, such as

Internet Explorer, Firefox, and Chrome. Web browser soft-

ware usually has a great deal of additional functionality:

HTTP itself is a simple protocol, and a basic client imple-

mentation is only a very small part of the code in these

browsers.

Web server software implements the HTTP server proto-

col. The Apache Web server is the leading Web server today,

and is perhaps the best-known example of open-source

software. Internet Information Ser vices, by Microsoft, is

another popular Web server.

Web server software runs on machines that are also called

Web servers. In consequence, there is some ambiguity when

people speak of Web servers. When “a server is unreachable,”

it could be the machine or the software that is meant; in

many cases, the difference may not be material. Since we

are primarily concerned with software in this book, when-

ever we say “Web server” we mean the Web server software.

Resources

The unit of access in HTTP is a resource, which is most

often just an HTML file. However, in general, a resource can

be any unit of transfer. Indeed, HTTP requests often are for

resources that are images, remotely invoked Web services,

outputs of dynamically executed programs, and so on. Of

course, for a resource to be requested and transferred, it must

first be identified. This leads to the following, somewhat

circular, definition for a resource: A resource is any object

on the Web that has a Uniform Resource Identifier (URI).

Resources on the Web are typically identified by means

of a Uniform Resource Locator (URL). (We will say more

about URI versus URL in a moment.) A URL is of the form

http://server:port/path#fragment?search.

The server part of the URL may be specified as a human-

understandable

host name or as a network-understandable

IP address

. An Internetwide domain name service (DNS)

is used to translate a server machine name, such as

www.umich.edu,

to a numeric IP address, which may

look

something like 135.22.87.1. A Web browser can take a URL,

find the server specification part of it, and have that

converted to an IP address using DNS.

180 Chapter 9 XML AND WEB DATABASES

The IP address identifies the computer to connect to,

just as a street address identifies a house in the real world

(Figure 9.17). A single computer will usually support

dozens of port

s. Think of each port as a separate door lead-

ing to a separate apartment in the building identified by

the IP address. By convention, particular ports are used

for particular applications. Thus, port 25 is for email

(SMTP), port 53 is for DNS, and so on. By convention, port

80 is for HTTP, and port 443 for HTTPS (a secure variant of

HTTP that is beyond the scope of discussion in this book).

However, a computer can be configured to have additional

ports that can accept HTTP requests. Numbers frequently

chosen for these additional ports are 8000 and 8080.

Typically, the path specifies a file name, and this is the

file

to be retrieved and returned. What

if the URL specifies

a directory? The server may have a convention in this case.

Typically, it will look for a file called i ndex.htm or index

.html, and return this file if it exists. If not, perhaps the

server should provide a directory listing. Exactly which file

to look for, and what else to do, is completely up to the

way the Web server has been configured: Neither the HTTP

protocol nor the definition of a URL will specify this.

The fragment specification merely points to a portion of

the resource being retrieved. In a typical implementation,

with HTML, the entire resource is still obtained, while the

fragment of focus is just identified. In other words, the

server does nothing differently with this specification.

Due to this, browsers may frequently not send the frag-

ment specification to the server. Rather, they may keep this

Client

Server

Request

Response

Internet

DNS

Server Name

Server IP

Address

Figure 9.17 Address lookup

in HTTP. The DNS query

loop happe ns before the

client can send its request

to the server.

Chapter 9 XML AND WEB DATABASES 181