A TSQL2 tutorial.pdf

(51 KB) Pobierz

A TSQL2 Tutorial

R. T. Snodgrass, I. Ahn, G. Ariav, D. Batory, J. Clifford,

C. E. Dyreson, R. Elmasri, F. Grandi, C. S. Jensen,

W. Käfer, N. Kline, K. Kulkarni, T. Y. C. Leung,

N. Lorentzos, J. F. Roddick, A. Segev, M. D. Soo, and

S. M. Sripada

1 Introduction

This tutorial presents the primary constructs of the consensus temporal query lan-

guage TSQL2 via a media planning scenario.

Media planning

is a series of deci-

sions involved in the delivery of a promotional message via mass media.

We will follow the planning of a particular advertising campaign. We intro-

duce the scenario by identifying the marketing objective. The media plan involves

placing commercials, and is recorded in a temporal database. The media plan must

then be evaluated; we show how TSQL2 can be used to derive information from the

stored data. We then give examples that illustrate storing and querying indetermi-

nate information, comparing multiple versions of the media plan, accommodating

changes to the schema, and vacuuming a temporal database of old data.

2 The Context

When Miller Brewing Company introduced Lite Beer, it revolutionized the beer in-

dustry. Once thought of as weak and feminine, low-calorie beer became the drink

of choice for many consumers in the most lucrative market—males who drink large

quantities of beer

. Not to miss a proﬁtable opportunity, Anheuser-Busch intro-

duced Bud Light, which had fewer calories than its regular beer, Budweiser. The

Miller’s commercials, involving various sports celebrities arguing “Less Filling” versus “Tastes Great,”

were critical in achieving this positioning.

321

322

THE TSQL2 QUERY LANGUAGE

new beer was intended to compete with Lite Beer from Miller

From a promotional standpoint, there were two major tasks: to link the new

brand to the established image of its ﬂagship brand, Budweiser, and to differentiate

Bud Light from Lite Beer from Miller.

Anheuser-Busch was committed to giving Bud Light the support necessary

to become a leading contender in the light beer category. But to compete with the

phenomenonally successful Lite Beer, it would be necessary to achieve high brand

awareness, and quickly. This would require the promotional media plan to achieve

broad reach and high impact

Commercials during the Super Bowl (the professional U.S. football champi-

onship game) met these requirements admirably. The Super Bowl is among the

most watched television events of each year

To ensure high impact, the commercials mirror the football game. A team

composed of bottles of Budweiser “competes” against a team composed of bottles

of Bud Light, with one commercial each quarter of the game. It was hoped that the

series of four commercials would be highly memorable and generate high excite-

ment, and would help to link the Bud Light brand to Budweiser in the consumers’

minds. Another beneﬁt was its potential for promotional tie-ins.

Anheuser-Busch faced the challenge of integrating the Super Bowl commer-

cials for Bud Light with ongoing promotion for both Budweiser and for Bud Light,

which involved heavy TV coverage. This would involve generating a comprehen-

sive media plan.

media plan

is a set of speciﬁc media objectives (in this case, increase brand

awareness of Bud Light), and speciﬁc media strategies that determine which spots

should be shown when and on which vehicles

. While media planning involves all

media, including radio, print, in-store promotions, etc., this scenario only concerns

television. We show how the development of a media plan for Bud Light marketing

can be supported by TSQL2.

You may recall the TV commercials. A person walks up to a bar and asks for a “light.” Instead of getting

a beer, he gets a spotlight, or a candle, or a match, making a pun on the word “light.” In another series of

commercials, the original ‘party animal,’ Spuds MacKenzie, was created to personify the attitude of the Bud

Light drinker.

Reach

is the percentage of the target market that is exposed to the message. A broad reach is a high reach

across a large, undifferentiated target market.

The Super Bowl has been the occasion for several highly memorable ads, which many believe was ini-

tiated by Apple Corporation’s “1984” ad on the Apple II computer, recently voted the best ad of the decade,

and the subsequent “Lemmings” commercial of 1985 introducing the Apple MacIntosh computer. The Mas-

terLock ad in which a riﬂe bullet is shot into a lock is aired only one time a year, during the Super Bowl, yet

many people still recall seeing that ad.

vehicle

refers a speciﬁc carrier within a medium category, such as a speciﬁc TV show; “Star Trek: The

Next Generation” is a television vehicle.

A TSQL2 TUTORIAL

323

3 Recording the Media Plan in a Temporal Database

Most of the tables needed for media planning will be time-varying tables. One table

will record the costs of commercials.

CREATE TABLE NBCShows

(ShowName CHARACTER ( 30 ) NOT NULL,

InsertionLength INTERVAL SECOND,

Cost INTEGER)

AS VALID STATE YEAR ( 2 ) TO NBCSeason ;

ShowName

is the name of a program on NBC (NBC is a television network).

In-

sertionLength

is the duration of a commercial (termed an

insertion)

shown

during the program, and

Cost

is the price in dollars of the advertisement.

This statement differs from an SQL-92 statement in the

AS VALID

clause.

This construct identiﬁes the

NBCShows

table as a

valid-time state table,

recording

information that changes in reality. Such tables contain rows that are timestamped

valid-time elements,

which are sets of

periods,

which are themselves anchored

durations of time. We’ll give an example shortly of the contents of this table.

This statement mentions three

granularities,

which are partitionings of the

time line.

SECOND

and

YEAR

are two granularities available in SQL-92.

NBC-

Season

partitions each year into 3 distinct seasons, which start at different times in

different years

. TSQL2 allows the database administrator to deﬁne new

calendars,

which provide one or more granularities. Calendars can also be made available by

the DBMS vendor or a third party.

Each period (interval, datetime) has an associated

range,

the maximum time

that can be represented, and an underlying granularity. Here we specify that the

granularity be

NBCSeason.

We specify a range of 100 years, via the syntax “YEAR

( 2 )”,

which indicates 10

years. At three seasons a year, periods can be repre-

sented in a 32-bit word.

Commercials in the media plan are recorded in the table.

INSERT INTO NBCShows

VALUES (’Roseanne’, INTERVAL ’30’ SECOND, 251000)

VALID TIMESTAMP ’Spring Season 1994’ ;

For the row that is inserted, the value of the

InsertionLength

is 30 sec-

onds

. The timestamp is speciﬁed as a single season. Note that this literal is in-

terpreted by a calendar, in this case, the

NBC_calendar.

TSQL2 permits limited

Networks choose the start of each season very carefully. Some start early, to give their new shows more

prominence; others delay the start believing that audiences are larger later in the year.

For the purpose of this tutorial, we assume that

Roseanne

is on NBC (it is an ABC show).

Roseanne

one of the most expensive shows on which to advertise, at over half a million dollars a minute. In comparison,

the CBS show

60 minutes

costs $314,000 a minute, CBS’s

Late Show with David Letterman

costs $64,000 a

minute, and CNN prime time costs only $2,400 a minute.

324

THE TSQL2 QUERY LANGUAGE

extensibility, in terms of user-deﬁned calendars and granularities. Presumably NBC

provided this calendar, for use in media-planning activities.

The literal

INTERVAL ’30’ SECOND,

which has an underlying granular-

ity of

SECOND,

is interpreted by the

SQL92

calendar, which is provided for con-

formity with the SQL-92 standard.

Figure 1 shows a portion of the rows of this table.

ShowName

’Roseanne’

’Super Bowl’

InsertionLength

’30’ SECOND

’60’ SECOND

Cost

251000

1800000

Valid Time

’Spring Season 1994’

Figure 1: Two Rows of the

NBCShows

Table

The media plan itself speciﬁes a collection of commercials (insertions) into

each vehicle over a period of time, and may also include speciﬁc individual inser-

tions.

CREATE TABLE NBC_FB_Insertion

(GameName CHARACTER ( 30 ),

InsertionWindow INTERVAL FootballSegment,

InsertionLength INTERVAL SECOND ( 3, 0 ),

CommercialID CHARACTER ( 30 ) )

AS VALID EVENT YEAR ( 2 ) TO HOUR AND TRANSACTION ;

This table records a particular insertion purchase, for a particular football game

broadcast in a particular hour on NBC. Commercials for football games are of-

ten sold for particular game quarters

. The

InsertionWindow

speciﬁes which

quarter the commercial is to appear in, and is relative to the start of the game. For

this, we use another user-deﬁned granularity, speciﬁc to the kind of game, rather

than the network (there might be other granularities for games with halves, such

as basketball, or games with commercials only at the half, such as soccer). Here,

the

InsertionLength

has an underlying granularity of

SECOND,

and a range

of 10

1000 seconds.

The

clause indicates that this is a

bitemporal event table,

with both valid-

time support (the timestamp indicates which day the show airs) and transaction-time

support. The

EVENT

reserved word indicates that rows are timestamped with sets

of datetimes (speciﬁcally,

DATEs),

rather than periods, as in the previous example.

Here the timestamps are to the underlying granularity of

HOUR,

with a range of

The Super Bowl is sold in terms of the four quarters, as well as the pregame, half-time, and postgame

shows. Ironically, pricing is not determined by quarters. Advertisers generally prefer the ﬁrst quarter, as

the audience is most attentive then, but those slots are grabbed early by big advertisers. Recently, NBC sold

overtime options, which were to be used only if the game went into overtime. The reasoning of the advertisers

is that the viewers will be maximally attentive during that time, though there is also the probability that the

commercial will not run.

A TSQL2 TUTORIAL

325

100 years (requiring only one 32-bit word). The

transaction time

is the time the

fact is stored in the database. In this case, the table supports multiple versions of

the media plan. The DBMS supplies the range and underlying granularities for

transaction timestamps.

Anheuser-Busch purchased four one-minute commercials for the 1994 Super

Bowl, at $1,800,000 each, as well as a short commercial for Bud Ice Draft, for a

total cost of $7,650,000, see Figure 2.

GameName

’Super

Bowl’

InsertionWindow

’First Quarter’

’Second Quarter’

’Third Quarter’

’Fourth Quarter’

’Third Quarter’

CommercialID

’Naked reverse good for TD’

’Basher is ejected’

’Wind reverses Light pass’

’Blimps move Bud Bowl to bar’

’Frosty Bottle’

InsertionLength

’60’

’15’

SECOND

...

Valid Time

’1994-01-30

13’

Figure 2: The

NBC_FB_Insertion

Table

The

football

calendar interprets the literal

INTERVAL ’Second Quar-

ter’;

the datetime literal

’1994-01-30 13’

is interpreted by the

SQL92

cal-

endar. The DBMS supplies the transaction time when rows are inserted or updated.

The transaction time is not shown in the table, due to lack of space.

4 Evaluating the Media Plan

Once a media plan is in place, it must be evaluated. We provide some representa-

tive queries, chosen to illustrate novel features of TSQL2 which are useful in this

application.

One question that arises is how well the media plans for the two beer brands

have been integrated. Too many commercials in a single show does not increase the

effective reach (although it does increase frequency, which is linked to memorabil-

ity).

Example 1

List those football games broadcast by NBC that have two or more

commercials.

SELECT N.GameName

FROM NBC_FB_Insertion AS N N2

Plik z chomika:

finka15

A TSQL2 tutorial.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: