Transcript ALMIS Database Fundamentals

ALMIS Database Fundamentals
Topics







ALMIS Database History
Table Layout
How to read a table definition
Table constraints and Triggers
Core Tables
Table Load Order
Structure Changes
ALMIS IS:
America’s
Labor Market
Information
System
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.
What is the ALMIS
Database?
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.
History of the ALMIS Database
ALMIS Database Version 1.0 Originated from
Two Existing Databases
_NOICC’s
Occupational
Labor Market
Information
Database or
OLMID
_North Carolina’s
Human Resource
Information
System Database
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
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…...
ALMIS Database Structure
The table layout for the ALMIS Database:
 Lookup tables
 Data tables
 Crosswalk tables
 Tables of standard field values
 Administrative tables
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
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
HOW TO READ A TABLE DEFINITION
Each table definition contains complete information about the structure of a table. Each
definition includes: the name of the table, a complete list of each column name and data
type, complete constraint information, and short descriptions of the columns. The
following illustration identifies each component of a table definition.
List of column
names.
Data type of
column.
Name of
table.
areatype
Constraint: Specifies whether this
column is part of the Primary Key or
participates in one or more Foreign
Keys. Numbers in this column refer
to Foreign Keys where more detailed
information can be found in the
Constraints Information section.
A table containing identifiers for the geographic type, i.e., MSA, SDA, county,
city, township, etc.
Column
stfips
Type
char(2)
areatype
char(2)
Constraint
Primary Key
1
Primary Key
areatyname
varchar2(40)
Constraints Information
1 Foreign Key references (stfipstb.stfips)
Constraints Information:
Provides complete information
on each Foreign Key used in the
table. Each note indicates
which table and columns are
being referenced.
Description
State FIPS code
The code for the geographical area the
data represent.
Descriptive title of the areatype
Short description of
the data intended for
this column.
Load Order
ALMIS Database Structure Changes
Version 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
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
New Table Elements in 2.3 from 1.1
**continued**









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
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
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.

Trigger Example
Action: Insert new
soccode “959595”
Soccode table
Trigger fires!
Occcode table
Action: Automatically
inserts new soccode
“959595”
Core Data Tables
Income
Empdb
CES
Industry
Labforce
Populatn
Wage*
Indprj
Occprj
Iomatrix
Income
License
Oeswage*
*Database Administrator may opt to populate one or both of these
data tables
ALMIS Resource Center







Connecticut
Florida
Georgia
Illinois
Iowa
Massachusetts
Montana








Nebraska
Nevada
North Carolina
Oregon
South Carolina
Wisconsin
ETA
NCSC
How to contact ARC:
ALMIS Resource Center
c/o Employment Security Commission
Of North Carolina
700 Wade Avenue
Raleigh, North Carolina 27611
Email: [email protected]
WWW.almisdb.org