what s where using the voyager class diagrams and data dictionary with access queries n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries PowerPoint Presentation
Download Presentation
What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries

Loading in 2 Seconds...

play fullscreen
1 / 27

What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries - PowerPoint PPT Presentation


  • 235 Views
  • Uploaded on

What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries . ELSUG October 9, 2008 Cathy Salika CARLI Consortium of Academic and Research Libraries in Illinois. Voyager is a relational database. A relational database is a bunch of tables.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries' - gaius


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
what s where using the voyager class diagrams and data dictionary with access queries
What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries

ELSUG

October 9, 2008

Cathy SalikaCARLI Consortium of Academic and Research Libraries in Illinois

voyager is a relational database
Voyager is a relational database

A relational database is a bunch of tables.

A table looks like a spreadsheet.

voyager has about 400 tables
Voyager has about 400 tables

Some are things you probably expect

PATRON, ITEM, CIRC_TRANSACTION

Some are less obvious

INV_LINE_ITEM_NOTES

Some are used to look up common codes

LOCATION, FINE_FEE_TYPE

the id fields
The _ID Fields

Many tables have a sequentially assigned number in a column with “_ID” in its name.

the id fields1
The _ID Fields

Sometimes the relationship is made directly.

the id fields2
The _ID Fields

Sometimes there’s an intermediary.

starting to write a query
Starting to Write a Query
  • First you have to figure out
    • what data you need
    • what tables the data are in
    • how to correctly relate those tables
  • This is easier if you know about Voyager because you use it regularly.
starting to write a query1
Starting to Write a Query
  • A good query
    • is built a few tables at a time
    • is tested at each step of the way
    • has no extraneous tables
starting to write a query2
Starting to Write a Query

There are two tools to show you what’s in the tables and how the tables relate.

Voyager Class Diagrams

available on SupportWeb

Voyager Data Dictionary

in the Voyager Technical User’s Guide

But I recommend that you use the CARLI version of these documents.

slide12
CARLI’s Data DictionaryLists the columns in alphabetical order (except that the _ID fields come first)
carli s data dictionary tells you which fields are in unicode so you can format them properly
CARLI’s Data DictionaryTells you which fields are in UNICODE so you can format them properly
where to get them
Where to Get Them
  • CARLI’s Data Dictionary and the additional Class Diagrams
  • Are in SupportWeb, in the KnowledgeBase.
  • Search for “CARLI” and you’ll find it.
  • Will soon be in the EL Commons
  • Are on these flash drives
views
Views

Both versions of the Data Dictionary include views.

Their names end with _VW

Inside Voyager, a view is actually a query, but you can use them as if they were tables.

Some views are helpful, some are inefficient, some are WRONG.

So let’s see which are which.

good views call nos deweyclass vw lcclass vw nlmclass vw sudocclass vw udcclass vw
Good Views Call Nos. DEWEYCLASS_VWLCCLASS_VWNLMCLASS_VWSUDOCCLASS_VWUDCCLASS_VW

A call number is included in a view if the CALL_NO_TYPE in MFHD_MASTER says it should be there.

slide22
Good Views Fixed Field Data for BibsMARCBOOK_VWMARCCOMPUTER_VWMARCMAP_VWMARCMUSIC_VWMARCSERIAL_VWMARCVISUAL_VW
inefficient views circcharges vw circrenew vw fundledger vw issues vw item vw serials vw
Inefficient Views CIRCCHARGES_VW CIRCRENEW_VW FUNDLEDGER_VWISSUES_VWITEM_VWSERIALS_VW
  • Why I don’t like them
  • Some use a dozen tables or more, so queries that use them are slow.
  • You have to know too much to understand their quirks.
    • (CIRCCHARGES_VW drops charge transactions if the item has been deleted.)
  • It’s too hard to make your statistics consistent.
slide24
If you want to know what the views doand you have access to SQLPLUS on your serverand you don’t mind reading a bit of SQL…here’s how.

SQL> set heading off

SQL> set long 4500

SQL> column view_name fold_a newl

SQL> column text newl newl newl newl

SQL> select view_name, text from all_views

where owner=‘XXXDB’ order by view_name;

bad views authblob vw bibblob vw mfhdblob vw
Bad Views AUTHBLOB_VWBIBBLOB_VWMFHDBLOB_VW

They don’t work correctly for long records.

When to use them: NEVER

and now a commercial
And now, a commercial…

If you’re new to query writing in Voyager, consider attending 2 other sessions:

“Scary Queries Laid to Rest” by Jean Vik at 8:30am

“Idiomatic Access Reports” at 9:30am