Natural join;
a join using a slightly simpler syntax with no ON predicate. Not often seen. There is no ON predicate as the join operation will match on columns with the same names in the two tables. See example below. There are variations for natural left, etc. The default is inner. There is a further variation if the two names are not the same.
select
fname, lname, description
from
employees natural join departments; |
Back Top |
|
Natural key; a
primary key established on a column with some natural meaning.
Examples might be Social Security Number or Driver's License. |
Back Top |
|
Negative subquery;
a form of multi-valued subquery. Common in commercial practice. In
the example below the subquery returns a list of deptids which are
then searched by the NOT IN operator. Example: find employees with a
department not in the DEPARTMENTS table (i.e., unassigned employees).
select
fname, lname
from
employees
where
deptid not in (select deptid from departments); |
Back Top |
|
NULL;
sounds like it should mean NIL, or nothing but it doesn't. It means
missing or unknown. To find a record with a NULL you have to use the
IS NULL operator. If in the example tables below you searched for all
employees who did not have a MgrNo = 11 you would get no rows back!
Why? Because employee 11 has a NULL MgrNo. Therefore it is not
known whether or not 11's MgrNo is equal to your search criteria.
Try it. |
Back Top |
|
Non-identifying relationship; Relationship
between two independent (strong) entities. Some CASE tools identify
these relationships with a dotted line. An example would be a
relationship between customers and invoices. Contrast with an identifying
relationship. |
Back Top |
|
Normalization; the
process of decomposing a poorly structured table into several smaller
well structured tables. There are several levels of normalization
(1st normal form, 2nd, 3rd, Boyce-Codd which is an improved 3rd
level, 4th, 5th and Domain Key/Normal). Third normal or Boyce-Codd is
a common goal. |
Back Top |
|
ODBC;
Open Database Connectivity
- a middleware standard that allows a client computer program to
connect to different data sources (Excel, text, most all relational
databases) with little, if any, reprogramming. Data sources are
defined via a DSN (Data Source Name) set up on the client
(in a 3 tier architecture this would be the web server). Microsoft
Access can readily take advantage of ODBC data sources via linked
tables. You can create DSN's via the Control Panel on Windows systems. |
Back Top |
|
OLAP;
OnLine
Analytical
Processing
- Analysis of on-line data via various analytical tools such as Brio
or Excel pivot tables. Often requires extracting relational data into
a multi-dimensional cube with pre-aggregated summary information. A
database used for OLAP would be read-intensive with extensive history information. |
Back Top |
|
OLTP;
OnLine
Transaction Processing
- Bread and butter operational database with frequent updates. OLTP
databases are widespread throughout business. The term transaction
comes from the fact that these database applications process
transactions such as updating inventory, handling an ATM cash
withdrawal, charging a bill to a customer, etc. |
Back Top |
|
One-to-many relationship; a
relationship between two entities in which each row in the first
(parent) can be related to more than one row in the second (child).
An example would be an EMPLOYEE parent table with primary key EmpNo,
and a child DEPENDENTS table with a foreign key EmpNo
referencing the EMPLOYEE table. Referential integrity
can be used to ensure child records are not inserted without a valid parent.
The foreign key always goes in the table on the many side. |
Back Top |
|
One-to-one relationship;
a relationship between two entities or tables in which each row in
one is related to only one row in the other. An example would be an
Employee Table and an Employee Benefits table, each with a primary
key of employee number. Sometime two tables with the same primary key
indicates the two should really be combined. In this case we want two
separate tables as the EMPLOYEE table might be frequently accessed
whereas only a special HR application would access the
EMPLOYEE_BENEFITS table.
To create a one-to-to-one relationship in MS Access
set the foreign key to indexed - yes (no duplicates) before drawing
the relationship line. |
Back Top |
|
Optionality;
refers to whether or not an entity must participate in a
relationship. In a relationship between customers and invoices, for
example, an invoice might be required (optionality mandatory) to
refer to a customer; conversely a customer could be a lead with an
invoice optional (not required). ERD drawing tools usually allow you
to denote the end of the relationship line next to the entity as
optional or mandatory. |
Back Top |
|
Orphan
Record; a child record with a foreign key
to a parent record that has been deleted or had its primary key
changed. This can happen when referential integrity is not enforced.
It is an "orphan" because it no longer has a corresponding
"parent" record. |
Back Top |
|
Parent
Table; the table on the one-side of
a one-to-many relationship. |
Back Top |
|
Permanent Link;
a relationship between two tables established in the Access
relationship designer. Get to it from Tools-->Relationships
on the menus. Once the link between the two tables is established it
is permanent and other Access components such as designers and
wizards can automatically use the relationship. The other type of
link is the transient, which see below. |
Back Top |
|
PL/SQL; Procedural Language/SQL.
the extended version of SQL with procedural constructs used in
Oracle databases. The extensions were originally derived primarily
from the ADA programming language. |
Back Top |
|
Primary Key;
the column or columns used to uniquely identify every row in a
table. Example: EmpNo in the EMPLOYEES table.
Truly relational tables must have a primary key. (See SQL
Server Magazine article with information on designing
primary keys). In a nutshell primary keys should not change, should
have no intrinsic meaning and, to paraphrase Einstein, be as small as
possible but no smaller. |
Back Top |
|
Query;
a question. A database operation that retrieves data from a
database. For example, finding all customers with orders greater than
$1,000 in the month of January. Often used (somewhat loosely) to mean
any operation expressed in the SQL language. Access has three basic
ways to create queries (in ascending order of power and flexibility):
The Simple Query Wizard
The Query Designer
SQL View
All allow you to save the definition of the query for
later use. |
Back Top |
|
RAID;
Redundant
Array
of Inexpensive
Disks.
Hard drive system with multiple disks allowing for various levels of
reliability and recoverability. |
Back Top |
|
Recursve
Join;
same as self
join below. |
Back Top |
|
Recursive Relationship;
a relationship wherein an entity is related to itself. A
one-to-many example would be a students entity related to itself via
a "rooms with" relationship. See self
join below for a many-to-many example; see Uncorrelated
Subquery for another. |
Back Top |
|
Referential Integrity;
refers to the integrity of the foreign key references in a
database. All foreign keys should refer to valid primary keys in
other tables. In our sample cost accounting database
one could not enter a labor ticket without a valid work order and
employee. An attempt to do so would cause a referential integrity
error. Most relational databases, including MS Access, can enforce this. |
Back Top |
|
Relation;
not to be confused with relationship. A relation is the relational
database theory term for a table. In fact, the term relational
database means a database of relations. All rows are unique and all
values are atomic. |
Back Top |
|
Relationship; an
association between two or more entities such as that between
DEPARTMENTS and EMPLOYEES. Also between one entity and itself in a recursive
relationship. The number of entities in the relationship is the degree.
Most commonly encountered relationships are binary, involving
exactly two entities. Relationships of degrees greater than two do
occasionally occur and are more complex to deal with. |
Back Top
|
|
Required Field;
a field that must be filled in at the time the record is created.
The database will not let you create a record without supplying a
value. Contrast with "an important field that has to be filled
in sooner or later". This is the terminology used in MS Access.
Other terms are mandatory and NOT NULL (SQL Term). |
Back Top |
|
Resolution Table;
yet another term for intersection
table. |
Back Top |
|
Right
Outer Join; a type of join which selects all rows with matching
join columns plus all non matched rows from the second table. Missing
values are filled with nulls. In our sample tables
a right join of EMPLOYEES and DEPARTMENTS on DeptID would return
three rows. For the third row values of EmpNo through MgrNo would be null. |
Back Top |
|
Row;
same as record. A relational table is composed of rows and columns.
Each row must be uniquely identifiable through a primary
key.
A row contains all the information about one instance of an entity.
In the sample tables below the EMPLOYEES table contains 2 rows. |
Back Top |
|
Secondary key; a key
used to speed searches and retrieval. Not necessarily unique.
Example: the state column in a CUSTOMERS table address fields.
Typically, these are implemented in Access by specifying the field as
indexed and selecting duplicates(yes/no) as appropriate. |
Back Top |
|
Self Join;
yes, a table can be joined to itself. A common
example would be an employee table with primary key EmpNo. The table
would also contain a foreign key MgrNo that referenced the EmpNo in
the same table. A join of EMPLOYEES to itself
could list all employees and their manager. Create a self join in the
MS Access Query Designer by adding the same table twice. Reset the
alias property of the second copy to Managers and join on
Employee.MgrNo = Manager.EmpNo.
The above is an example of a self join in a recursive
one-to-many relationship. Joins can also implement a recursive
many-to-many relationship. Still with us? This requires an intersection
table between the original table and its copy. Assume an
employee can have more than one manager as in matrix management. The
intersection table, say REPORTING_RELATIONSHIPS, could be:
REPORTING_RELATIONSHIPS(EmpNo, MgrNo, Reporting_Relationship)
The Jet SQL
to list employees with their managers would then appear as follows:
select
employees.empno, employees.name,
reporting_relationships.reporting_relationship, managers.name
from
(employees inner join reporting_relationships
on
employees.empno = reporting_relationships.empno)
inner join employees as managers
on
reporting_relationships.mgrno = managers.empno; |
Back Top |
|
Single-valued subquery;
a subquery that returns a single value that can be used in
comparisons. See Uncorrelated Subquery
for an example. |
Back Top |
|
Snapshot; a read only recordset. Used
when you are processing a recordset but don't need to update its
data. Faster for this purpose than a dynaset. |
Back Top |
|
SQL; Structured Query
Language. The lingua franca of relational databases.
Virtually all relational databases store and retrieve data via SQL.
The most common commercially implemented standard is SQL92 although
SQL99 has been released. |
Back Top |
|
Straight join;
Straight join?! Yes. Used in MySQL to force tables to be joined in
the exact order listed in the SQL statement FROM clause. |
Back Top |
|
Strong
relationship; same as identifying
relationship. Not to be confused with strong entity. |
Back Top |
|
Structured key;
another term for composite key. |
Back Top |
|
Subquery; a query inside
a query. Used as a part of one of the clauses in another query.
Example: find employees with a department not in the DEPARTMENTS table.
select
fname, lname
from
employees
where
deptid not in (select deptid from departments);
The subquery is enclosed in parentheses. Most joins
can be expressed as subqueries and vice-versa. In the MS Access Query
Designer place the subquery in the appropriate row in the QBE grid.
(Criteria row in the example above). Subqueries come in three basic
flavors: single-valued, multi-valued,
and correlated. The above subquery is multi-valued.
Subqueries are not restricted to select statements.
They can also be used in update statements, having clauses, etc. |
Back Top |
|
Subselect;
another term for Subquery. However, I prefer to restrict the
definition to subqueries that appear in the select list. As an
example the following query retrieves employee names, their salaries
and the average of all salaries.
select
name, salary, (select avg(salary) from employees)
from
employees;
Not all databases support this. |
Back Top |
|
Surrogate key;
a key generated by the application. It has no meaning in and of
itself. Example: Invoice_Number
for an INVOICES table. It could be generated by the MS Access auto
number facility. |
Back Top |
|
T-SQL; abbreviation for Transact
SQL. |
Back Top |
|
Table
Alias; see Alias |
Back Top |
|
Table Constraint; a constraint
that applies to an entire record or row, not just one field in a
table. To compare one field against another, for example, you must
use a table constraint. In SQL, these are clauses in the CREATE and
ALTER statements. In MS Access right click on the table title in
design mode and go to properties. The validation rule there applies
to the whole row and you can reference multiple fields. |
Back Top |
|
Table Validation; see Table Constraint. |
Back Top |
|
Technical Key; yet
another term for surrogate key. |
Back Top |
|
Transact
SQL;
extended SQL dialect used by SQL Server and Sybase. |
Back Top |
|
Transaction;
A series of one or more SQL statements treated as a single, atomic
unit of work. The effects of a transaction can be rolled back or
committed as a whole. A banking example: deduct from savings and then
add to checking. |
Back Top |
|
Transient link; a
temporary relationship between two tables created by the Access query
designer that lasts only for the duration of the query. |
Back Top |
|
Tuple;
relational theory term for row. Relations
have tuples, tables have rows, files have records. Avoid this term
around the office. |
Back Top |
|
Uncorrelated Subquery;
A type of subquery that does not reference its outer query. The
following example retrieves all labor tickets with hours worked
greater than the average hours worked.
select
*
from
labor
where
workhours > (select avg(workhours) from labor);
In the Access Query Designer the subquery can be
placed in the Criteria Row. |
Back Top |
|
Unbound form; A form which does not
have an associated data or record source. Often used to collect
parameters for a report. Say the from and to dates. |
Back Top |
|
UNION;
An SQL set operation statement which combines the results of two or more SELECT
queries. Say for example there is a labor_history table in our sample
database which contains archived records. We could use the UNION
statement to combine the records from the current labor table and the
history table as follows...
select
*
from
labor
union
select
*
from
labor_history;
Unfortunately,
Access does not provide a Wizard or Designer view to do this. You
have to use the SQL View. |
Back Top |
|
Update
Query;
a query which updates one or more existing columns. The Access Query
Designer creates an SQL UPDATE statement. The example:
update
employees
set
deptid = "R1"
where
empno = "12";
transfers Mary
to the Refurbishment department. Without a where clause the update
statement will operate on all rows. |
Back Top |
|
Validation
Rule;
MS Access rule used to enforce domain
integrity.
I.e to ensure column values are valid. Examples: make sure all
amounts are positive, dates are greater than or equal to today, etc.
In an enterprise database you would use an SQL CHECK constraint such
as CHECK (hours > 0). |
|
|
View; a virtual table.
Created with a CREATE VIEW statement in SQL or via a saved query in
MS Access. |
Back Top |
|
VBA; Visual Basic for
Applications. The internal programming language used by MS Access.
Similar to its big brother Visual Basic. |
Back Top |
|
Weak Relationship;
Same as a non-identifying relationship (which
see). Not to be confused with weak entity. |
Back Top |
