Hollywood Tutorial

An example of creating an interface using queries, rather than direct table references. This is how we built the Hollywood Web demo, which answers questions about Oscar-winners.

Sometimes the straight-forward approach is not the best approach. Here is an example which shows why there are many options built-in to Access ELF that let you customize the way an interface is set up.

The Hollywood database looks simple enough, at first glance.


All we want to do is create an interface that lets people ask questions about which movies, which actors, and which actresses have won Academy Awards (Oscars) and which others have been nominated for these awards. There are two main problems. One is that people will use the word "who" referring to either movies or people, for instance: Who won for Best Actor in 1954?" or "Who won Best Picture in 1994?"

Another problem is that each entry in the Awards table has a Status field which is either "Nominee" or "Winner". Now if we ask "Who won such and such..." it makes sense to return the entries with "Winner". But if we ask "Who was nominated for such and such..." we want to see, not only the Nominees, but also the Winner!

These aren't big problems, but they're typical of the small adjustments you'll need to make when applying our software to your own databases. In fact, there are many ways to solve these problems, since ELF is highly programmable. In this case, we'll start by substituting custom-built queries for the tables in this database. First we define a ScreenArtists query:

This corresponds to the SQL
SELECT DISTINCTROW Movies.MovieName, Artists.Artist AS ArtistName, AWARDS.AwardYear, AWARDS.Category, AWARDS.Status, Movies.Director FROM (Artists RIGHT JOIN AWARDS ON Artists.[Artist ID] = AWARDS.[Artist ID]) LEFT JOIN Movies ON AWARDS.[Movie ID] = Movies.[Movie ID];

Now we create a second query called OscarWinners, exactly the same as the first except that it filters on the Status, showing only the Winners.

This corresponds to the SQL
SELECT DISTINCTROW Movies.MovieName, Artists.Artist AS ArtistName, AWARDS.AwardYear, AWARDS.Category, AWARDS.Status, Movies.Director FROM (Artists RIGHT JOIN AWARDS ON Artists.[Artist ID] = AWARDS.[Artist ID]) LEFT JOIN Movies ON AWARDS.[Movie ID] = Movies.[Movie ID] WHERE (((AWARDS.Status)="Winner"));

The interesting part about the query is the JOIN that connects Awards to Artists. The arrow indicates that it's defined as Include ALL from AWARDS and only those records from 'Artists' where the joined fields are equal. This is because the records in the AWARDS table that relate to "Best Picture" don't have an entry for ArtistID, only one for MovieID. (The other JOIN is defined the same way, but that appears to be done out of someone's love of symmetry; it makes no difference if it's defined as a normal INNER JOIN, since every AWARD record has a MovieID and so does every Movie record!)

One point is that now both recordsets show both a MovieName and an ArtistName; of course, the ArtistName will be blank when displaying nominees and winners for "Best Picture". We've pretty much made sure that ELF will consider the MovieName the "key" of each query by putting it in the first position. So every answer will show the movie involved. Showing the Artist will happen naturally whenever someone asks a "who"-type question, because ELF knows that an "Artist" is a type of Person. (To verify this, enter artist in the Lexicon Lookup window and click Reference. The definition of artist is coded with an "m" for "mankind".)

Now we run the Access ELF analysis on this database, choosing Analyze Queries and selecting a Custom Analysis. In the Access ELF Custom Analysis window we make sure to select ONLY the two queries ScreenArtists and OscarWinners. A neat feature of Access ELF is that you can select a query without selecting the underlying table that it gets data from. In that case, ELF actually runs the query and enters all the data returned by the query into its lexicon.

When the Analysis is done, let's try a test question, who won for Best Picture in 1954? The SQL generated is
SELECT DISTINCTROW ScreenArtists.ArtistName , ScreenArtists.AwardYear , ScreenArtists.MovieName FROM ScreenArtists WHERE ( ScreenArtists.Category = "Best Picture" and ( ( ( ScreenArtists.AwardYear >= #01/01/54# and ScreenArtists.AwardYear < DateAdd ( "YYYY" , 1 , #01/01/54# ) ) ) ) ) ;
and the answer is
ArtistName     AwardYear     MovieName
                          1954          On the Waterfront
                          1954          Three Coins in the Fountain
                          1954          Seven Brides for Seven Brothers
                          1954          Country Girl
                          1954          Caine Mutiny

Well, this is wrong!


We solved the problem about showing both Movie and Artist, but forgot the part about deciding when to show Winners and when to show all Nominees. But now this is an easy fix. All we have to do is to convince ELF to generate a reference to the OscarWinners query at some times, and the ScreenArtists query otherwise. We'll use the word "won" as a trigger.

Open the Phrase definition editor (click "ABC" on the Toolbar) and add an entry:
When I Type: "won"          I Really Mean: was OscarWinners_ . ArtistName

Notice that in order to make reference to a data set, you need to append an underscore to it; this applies to both tables and queries. This definition will substitute a reference to the ArtistName field of the OscarWinners query every time a user submits a question with the word "won"

Once we enter this definition, we can immediately test it. Phrase definitions are fully interactive; you don't need to re-run an Analysis each time you add one! Now the answer to who won for Best Picture in 1954? is returned correctly as:
ArtistName     AwardYear     MovieName
                          1954          On the Waterfront
with the generated SQL being
SELECT DISTINCTROW OscarWinners.ArtistName , OscarWinners.AwardYear , OscarWinners.MovieName FROM OscarWinners WHERE ( ( OscarWinners.Category = "Best Picture" and ( ( OscarWinners.AwardYear >= #01/01/54# and OscarWinners.AwardYear < DateAdd ( "YYYY" , 1 , #01/01/54# ) ) ) ) ) ;

Pretty amazing, this is all that's needed to handle a variety of the most common (and even some uncommon) questions on this database. For example,
Which pictures had more than one winner for Best Director?
Count_Of ArtistName          MovieName
          2                                West Side Story
SELECT DISTINCTROW OscarWinners.MovieName , OscarWinners.ArtistName FROM OscarWinners WHERE ( ( OscarWinners.status = "Winner" and OscarWinners.Category = "Best Director" ) ) ; SELECT DISTINCT count ( [elfQ1].ArtistName ) AS [Count_Of ArtistName] , [elfQ1].MovieName FROM [elfQ1] group by [elfQ1].MovieName HAVING Not IsNull([elfQ1].MovieName) ; SELECT DISTINCT [elfQ2].* FROM [elfQ2] WHERE [elfQ2].[Count_Of ArtistName] > 1 ; SELECT DISTINCT [elfQ3].* FROM [elfQ3] , OscarWinners , [elfQ3] INNER JOIN OscarWinners ON [elfQ3].MovieName = OscarWinners.MovieName WHERE ( ( OscarWinners.status = "Winner" and OscarWinners.Category = "Best Director" ) ) order by [elfQ3].MovieName ;
Turns out this is the only co-directed film to ever win Best Picture. It's amazing what you can learn!

Now how did we pick that exact subsitution phrase "was OscarWinners_ . ArtistName"? Access ELF is highly syntax-sensitive, meaning you can't just type in keywords as if it were an Internet search engine. The word "won" is an inflected verb, and plays an important part in the question. If we substitute just "OscarWinners_ . ArtistName" for "won", we're really asking Access ELF to answer the question: who OscarWinners_ . ArtistName for Best Picture in 1954? and it won't be able to.

This brings us to the last little wrinkle in this implementation, and gives us a chance to show off another neat feature of the Phrases facility. What if we type in a very similar question: who won Best Picture in 1954? (exactly the same, but without "for"). Now the substitution defined above leads to: who was OscarWinners_ . ArtistName Best Picture in 1954? This is pretty close, but it turns out that ELF doesn't like this either, it wants something like "for Best Picture" or "where Best Picture" etc. And if we add the "for" right into the substitution, then we've got a problem with the original question, which then comes out who was OscarWinners_ . ArtistName for for Best Picture in 1954? -- the "for for" stops ELF cold. There's nothing to do but add another rule to handle this case:
When I Type: "won {Category}"
I Really Mean: was OscarWinners_ . ArtistName for {1}

This Phrase definition uses the new "data-aware" feature of the Phrase facility. (For complete details on this, see the Phrase Usage topic.) This allows us to specify a field name as part of the input pattern -- here, the Category field. On the substitution side, whatever fills this slot in the actual question will be substituted where the {1} marker appears. So in this case, the rule changes who won Best Picture in 1954? into who was OscarWinners_ . ArtistName for Best Picture in 1954?

This refinement is a special feature of the context-sensitive phrase facility, which is triggered by using quotes (or square brackets) in the When I Type: part of the definition. It wasn't strictly necessary to use the quotes in the first case (since that one didn't use context-sensitivity at all). But if we make this second definition, we do need to use quotes in the first one as well. The reason is that context-sensitive substitution has a lower priority than general-purpose substitutions. If they both use the same word (eg. "won"), then the context-sensitive rule (which fires first) will defer to the general-purpose rule (which fires later). By default, the two types of rules fire in alphabetical order on the trigger part of the definition; if you need to override this firing order, you can enter arbitrary ordering values in the Sort Key field.