Wave top

Glossary of Microsoft Access, SQL and Database Terms

View of Laguna Coast

Group
Training

Individual
Training

Programming & Consulting

What's New!

FAQ's

Site Map

Home

About Us

Catalog

Resources

Contact Us

Glossary of Database Terms - Page 3

Page 1 (1-9, A-D) | Page 2 (E-M)

Sample Tables | 1-9 | A-D |E-M | Natural Join | Natural Key | Negative Subquery | Non-identifying Relationship | Normalization | NULL | ODBC | | OLAP | OLTP | One-to-Many Relationship | One-to-One Relationship | Optionality | Orphan Record | Parent Table | Permanent Link | PL/SQL | Primary Key | Query | RAID | Recursive Join | Recursive Relationship | Referential Integrity | Relation | Relationship | Required Field | Resolution Table | Row | Second Normal Form | Secondary Key | Self Join | Single-valued Subquery | Snapshot | SQL | Straight Join | Strong Relationship | Structured Key | Subquery | Subselect | Surrogate Key | T-SQL | Table Alias | Table Constraint |Table Validation | Technical Key | Third Normal Form | Transact SQL | Transaction | Transient Link | Tuple | Uncorrelated Subquery | Unbound Form | UNION | Update Query | Validation Rule | VBA | View | Weak Relationship | X | Y | Z | Sample Tables

Notation Conventions: primary keys underlined, foreign keys italicized, table name all caps. Sample definition of a relation using this common relational notation...

EMPLOYEES (EmpNo, FirstName, LastName, DeptID, MgrNo)


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).

Back Top

Click to see an example of an MS Access validation rule

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


In additon to any general comments feel free to suggest a new term and definition.

Sample Tables (primary keys underlined, foreign keys italicized) abstracted from a cost accounting database...

Employees

EmpNo

Name

DeptID

MgrNo

11

Bill

A1

Null

12

Mary

C1

11

aa

aaaa

aa

aa

Departments

DeptID

Description

A1

Assembly

C1

Casting

R1

Refurbishment

Labor

Wono

EmpNo

WorkHrs

A1

11

10.0

A1

12

20.0

Work_Orders

Wono

Description

A1

Acme Strip Connector

B2

Boeing Wire Harness


HTML Comment Box is loading comments...

Access sample tables...
Microsoft Access Sample Databases & SQL Scripts

Receive course announcements and news. Sign Up Today!





Email Marketing by VerticalResponse
Course Catalog

 


Database Glossary Page 3

Page 1(1-9, A-D) |Page 2(E-M)

Copyright 2020 D.H.D'Urso & Associates
P.O. Box 6142, Laguna Niguel, CA 92607 949-408-1350
Serving: Orange, Los Angeles, San Diego, Riverside, San Bernardino, Imperial and Ventura counties and beyond.