ALMIS Database Fundamentals

1
ALMIS Database Fundamentals
2
Topics

• ALMIS Database History
• Table Layout
• How to read a table definition
• Table constraints and Triggers
• Core Tables
• Table Load Order
• Structure Changes

3
ALMIS IS

• Americas
• Labor Market
• Information System

4
The ALMIS System is

• A Joint effort between the Employment and
  Training Administration of the U.S. Department of
  Labor and the States to
• - conduct research, - develop products, and
  
• - deliver Information
• to job seekers, employers, economic developers,
  planners and other users.

5
What is the ALMISDatabase?

• In Computer Terminology
• The ALMIS Database is a Normalized, Relational
  Database Structure, Developed for the Storage and
  Maintenance of Labor Market, Economic,
  Demographic and Occupational Information.

6
History of the ALMIS Database
ALMIS Database Version 1.0 Originated from
Two Existing Databases
_NOICCs Occupational Labor Market Information
Database or OLMID
_North Carolinas Human Resource Information
System Database
7
History continues

• - August 1996 Work Began on the ALMIS Database
  structure
• - April 1997 ALMIS Database Structure Version
  1.0 Delivered to States
• - Spring 1997 Formation of the ALMIS Database
  Maintenance Consortium
• - Summer 1998 Definition and Release of Core
  Tables under the P/Y 1998 One Stop LMI Grant

8
What Kinds of Information are Included in the
ALMIS Database?

• Some of the Types of Information Which Can be
  Found in an ALMIS Database Include
• - Population - Employment Projections
• - Tax Information - Mass Layoffs
• - Income - Labor
  Force Estimates
• - Employment - Sales Tax
• - Government Transfer Payments
• and
• - much more, such as...

9
ALMIS Database Structure

• The table layout for the ALMIS Database
• Lookup tables
• Data tables
• Crosswalk tables
• Tables of standard field values
• Administrative tables

10
Common Relational Database Terms

• A Database is a structured collection of data,
  stored in tables
• A Table is a collection of records related to one
  another
• A Primary Key is a field or combination of fields
  that uniquely identifies each record in a table
• A Foreign Key is when a value in one table
  matches the primary key of another table

11
Common Relational Database Terms Continued

• A Data Table is a table consisting of a
  collection of data records
• A Look Up Table is a foreign key table
• A CrossWalk Table is a table that relates coding
  structure in the database
• An Administrative Table is a table that contains
  information on the data and tables in the ALMIS
  Database

12
(No Transcript)
13
Load Order
14
ALMIS Database Structure ChangesVersion 1.1 to
2.3

• Number of Tables Added 36
• Number of Tables Changed 42
• Number of Tables Deleted 11
• Number of Tables Unchanged 72

15
New Table Elements in 2.3 from 1.1

• For Data Tables That Have Changed
• CES benchmark, suppression
• Empdb (several fields changed)
• ESDATA stattype, esorders, newapp, referrals
• INDPRJ nchg, growcode
• INDUSTRY firms, m1emp, m2emp, topempav
• LABFORCE benchmark
• LICHIST licnumtyp
• MLSCLAIM claimtyp
• MLSEVENT dwvertype
• OESWAGE (entire table) - new core table

16
New Table Elements in 2.3 from 1.1continued

• For Data Tables That Have Changed
• OCCPRJ nchg, growcode
• PROGRAMS compltyp, progtitle, url
• SALES salestyp
• STFIRMS lat, long, naicscode, axnaicscd
• STINDPRJ nchg, growcode
• STOCCPRJ nchg, growcode
• UICLAIMS claimtyp, indcode occcode, agegroup,
• racethn, gender
• URL (entire table)-new core table
• WAGE periodyr, periodtype, period, respondent,
  empcount

17
Triggers

• A trigger is a fragment of code that you tell the
  server to run before or after a table is
  modified. A trigger has the power to
• INSERT
• UPDATE
• DELETE

18
Why have Triggers?

• Why have the Occcode Indcode tables?
• Can not have conditional foreign keys as
• was happening in the case of the multi-code
  fields in many of the ALMIS DB tables.
• Can not have a foreign key field length
• that is different than the primary key
• it is referencing.

19
Trigger Example
Action Insert new soccode 959595
Soccode table
Trigger fires!
Action Automatically inserts new soccode
959595
Occcode table
20
Core Data Tables

• Income
• Empdb
• CES
• Industry
• Labforce
• Populatn
• Database Administrator may opt to populate one
  or both of these data tables

Wage Indprj Occprj Iomatrix Income License Oeswag
e
21
ALMIS Resource Center

• Connecticut
• Florida
• Georgia
• Illinois
• Iowa
• Massachusetts
• Montana

• Nebraska
• Nevada
• North Carolina
• Oregon
• South Carolina
• Wisconsin
• ETA
• NCSC

22
How to contact ARC

• ALMIS Resource Center
• c/o Employment Security Commission
• Of North Carolina
• 700 Wade Avenue
• Raleigh, North Carolina 27611
• Email almis_at_ncmail.net
• WWW.almisdb.org