Transcript POPULATION SELECTION CONCEPT

POPULATION SELECTION
CONCEPT
People in
Database
Selection Rules
or Criteria
Selected Population of
Individuals who
Satisfy Rules
THE SELECTED
POPULATION
• May be used in letter generation.
• May be used in some Banner reports.
– Example: Student schedules
• Is required in some Banner reports.
• May be used for “simple” reports.
Examples
• General Person
– Find the number of (or get a list of) person
records with missing birth dates.
• Recruiting
– Find the number of (or get a list of) recruits
from a particular high school.
More Examples
• Admissions
– Find the number of applicants of a particular
gender or ethnicity.
• Location Management
– Find the number of students requesting housing
in a particular dormitory.
More Examples
• General Student
– Find the number of students in a particular
major.
• Accounts Receivable
– Find the number of students who have been
charged a parking fine.
And More Examples
• Faculty Load
– Find the number of faculty members who are
teaching more than 15 credits.
• Academic History
– Find the number of students graduating with
honors.
Know Your Data
• Where are the data entered on Banner
forms?
• How do the Banner forms correspond to the
Oracle tables in the database?
• What are the relationships between data in
different tables?
For Example
• If you want to find the number of female
applicants, you need to know
– How is an applicant entered in Banner?
– How is a person’s gender entered in Banner?
• It takes time and experience to gain
knowledge of the system.
Know the Tables
• Normally, a table corresponds to a block of
a Banner form.
• The naming convention for tables is similar
to that for forms.
– Seven characters
Table Names
• 1st character - System
• 2nd character - Module
• 3rd character - Type of table
– Base, Repeating, and Virtual (or View)
• 4th - 7th characters Abbreviation of table purpose
Examples
• SPBPERS is the base table corresponding to
the General Person Form–SPAPERS.
• SPRIDEN is the repeating table
corresponding to the identification
information block of the Identification
Form–SPAIDEN.
Examples (continued)
• SPRADDR is the repeating table
corresponding to the address information
block of SPAIDEN.
• SPVADDS is a virtual table (or view)
created to give current name and address
information.
Finding Table Names
• Use Dynamic Help from the Banner form.
• Consult the Banner Student Technical
Reference Manual.
• Print the Data Element Dictionary from
GURPDED.
Visualizing a Table
• Think of a table as a grid having rows and
columns–similar to a spreadsheet.
• Each row of the grid corresponds to a
record.
• Each column of the grid corresponds to a
field in the record.
A Simplified View of SPBPERS
PIDM GEN- CONFIBIRTH
DER DENTIALITY DATE
12345
12346
12347
12348
12349
F
F
M
F
M
Y
Y
04-Jul-81
06-Nov-75
03-Dec-72
15-Mar-68
ETHNIC CITIZEN- MARITAL
CODE SHIP
STATUS
1
2
1
2
Y
Y
Y
N
Y
S
S
M
D
SQL Basics
• Standard Query Language
• Population Selection uses the SQL select
statement.
SQL Select Statement
• Select __________
• From __________
• Where __________
PIDM
table name(s)
criteria
Select
• In a population selection you can select only
PIDMs. (A Personal IDentification Master
is an internal identification number that is
created when person and non-person
records are entered on SPAIDEN.)
From
• The From clause indicates which table in
the database contains the information being
used in the selection.
– The From clause must include the table used in
the Select clause.
– The From clause also must include any other
table(s) involved in the Where clause.
Example–SQL Select
• Select SPRIDEN_PIDM
• From SPRIDEN
• Where SPRIDEN_LAST_NAME = ‘Smith’
Another Example
•
•
•
•
Select SPRIDEN_PIDM
From SPRIDEN, SPBPERS
Where SPRIDEN_LAST_NAME = ‘Smith’
and SPBPERS_SEX = ‘F’
HIERARCHY OF TERMS
USED IN
POPULATION SELECTION
AND LETTER GENERATION
APPLICATION
SELECTION
IDENTIFIER
Creator ID
Population Selection
VARIABLE
OBJECT
(optional)
Creator ID
Letter Generation
FORMS USED IN
POPULATION SELECTION
• GLRAPPL--Define application and
enter global rules (if any)
• GLRSLCT--Establish selection identifier
and enter selection rules
• GLBDATA--Apply the selection rules to
selection a population
• GLAEXTR or GLIEXTR--View the
population