Top bar

Glossary of Microsoft Access, SQL and Database Terms

Map of the Laguna Niguel and San Juan Capistrano area

Home

About Us

Price List/Catalog

Resources

Contact Us

Page 2 (E-M) | Page 3 (N-Z)

This glossary is intended to correlate to the software classes taught in Southern California (and elsewhere) by Orange Coast Database Associates Database Training, Design and Programming. It will continue to be updated over time. We are currently adding examples which can be seen by clicking the red arrows to the right of the text.

Sample Tables | 1st Normal Form | 1-to-1 Relationship | 1-to-Many Relationship | 2nd Normal Form | 3rd Normal Form | Abstract Key | Action Query | Active Server Page | ActiveX Data Object | ADO | Aggregate Functions | Alias | Alternate Key | Append Query | Artificial Key | ASP | AutoNumber |Binary Relationship | Bound Form | Bridge Table | Candidate Key | Cardinality | Cascade Delete | Cascade Update | Calculated Field | CASE | Child Table | Column Alias | Composite Entity | Composite Key | Compound Query | Computed ColumnConcatenated Key | Concatenation | Correlation Name | Covered Query | Cursor | DAO | Data Mart | Data Type | DDL | Degree | Delete Query | Denormalization | Derived Table | Determinant | Domain | Domain Integrity | DROP | Dynaset | E-M | N-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)


1st Normal Form, a table is in first normal form if all attributes are atomic (single-valued) and all rows are unique. The following (very simplistic) table would violate 1st normal form.

EmpNo

Name

City

Phone

01

Smith

Nashua

544-1814(H), 544-1832(W)

02

Jones

Merrimack

533-1212

The phone column has an entry with too many values. Either an additional column should be added or phone numbers should go in a separate table with a foreign key reference to this table.

Back Top

2nd Normal Form, a table is in 2nd normal form if it is in 1st and there are no partial key dependencies. An example (where we are tracking part quantities by warehouse) that violates this follows.

PartNo

Loc

Description

Qty

01

W1

Flanges

10

01

W2

Flanges

20

02

W2

Washers

30

The description depends only on PartNo, whereas stock quantity depends on PartNo and Loc(ation). Description belongs in a separate table with PartNo as the primary key. Otherwise, duplicate description information has to be entered.

Back Top

3rd Normal Form; a table is in 3rd normal form if it is in 2nd and has no transient dependencies. An example somewhat similar to the above that violates this is:

WorkOrder

PartNo

PartDescr

A1

223

Bronze Fitting

A2

124

Machine Screw

A3

233

Bronze Fitting

The description can be determined from the PartNo which in turn follows from the key, WorkOrder. PartDescr belongs in a separate table with PartNo as the primary key. Otherwise duplicate data is recorded and you cannot insert the fact that a certain part has a certain description without having a work order.

Back Top

Abstract key; see Surrogate key.

Back Top

Action query; a type of MS Access query that updates a table. There are four basic types: 1) an Append Query which adds records to a table, 2) an Update Query which updates values in one or more fields, 3) a Delete Query which removes one or more rows and 4) a Maketable query which creates a new table.

When creating an action query in Access we recommend first designing the query as a select query to make sure it retrieves the correct records. Then convert it to the appropriate action query.

Back Top

Active Server Page (ASP); a dynamic web page that contains embedded scripting; usually VB Script. The VB Script can manipulate an Access (or other) database through an ADO object and either an OLE DB or ODBC connection. Recognized by .asp extension.

Back Top

ActiveX Data Object (ADO); a MIcrosoft ActiveX object that provides for access to and manipulation of various data sources. Data sources can range from text files to spreadsheets to databases and many more. Typically used in conjunction with programming and/or embedded scripting.

Back Top

Aggregate function; functions that take a total or average. They operate on a whole table or a group of records. The aggregates functions in Access are: are SUM, AVG, MIN, MAX, COUNT, VAR, STDEV, FIRST and LAST. For example:

Select sum(hours)
from labor

To see an example of total hours grouped by employee in the MS Access Query Designer click the red arrow to the right.

Back Top

Sum Aggregate Function

 

Alias; a shorthand name for a table or column. Allows you to substitute a new table in a query just by changing the table name in the FROM clause. This works because the column references will all be qualified by the alias name. Create a table alias by following the table reference in the FROM clause with the alias:

Select l.workhrs
from labor as l
Note: "as" is optional.

To create a new column name follow the column name with the alias like so:

Select l.workhrs as [Hours Worked]
from labor as l

Back Top

Click to see an example of a table alias in MS Access

Alternate key: candidate key(s) other than the one chosen as primary.

Back Top

Append query; an Access action query that adds records to a table. Behind the scenes it creates an SQL INSERT statement. Click on the red arrow to the right to see an example of an append query that adds emplyees reporting to manager 12 to a table of temp employees.

Back Top

Click to see an example of an append query in MS Access

Artificial key: see Surrogate key.

Back Top

AutoNumber; an Access field type wherein the values are automatically incremented each time a new record is created. Typically used for Artificial or Surrogate Primary keys. Example: invoice numbers.

Back Top

Binary relationship; a relationship between two entities such as employees and departments; by far the most common in relational database design. Other types involving three (ternary) or more are possible but much less often seen. They are rarely discussed at any length in textbooks, although Toby Teorey does treat ternary relationships. An example would be projects, skills and employees.

Back Top

Bound form; an Access (or other client application) form that is tied to an underlying table or query called the recordsource. The fields in the form are filled automatically from the corresponding columns in the database. In Microsoft Access a form may be bound to only one recordsource. Thus, you need to use a query or subforms to visually present data from more than one table. The Form Wizard will do this for you if you select fields from more than one table.

Back Top

Bridge table; another synonym for intersection table.

Back Top

Calculated field; a field which is not in an underlying table but is calculated via a query expression. It forms a new column in the result set. For example price * quantity as extension. This applies to each row selected. An aggregate function, which see above, is not a calculated field. Although it does calculate, it does not add a column to the result set.

Calculated fields of a slightly different sort can also be added to Access reports and forms by placing the expression preceded by an = sign in the control source of a text box. For example: =[rate] * [hours]

Back Top

Candidate key; a key that uniquely identifies rows in a table. It could potentially serve as the primary key. There can be more than one candidate key and a candidate key may be composed of more than one column.

Back Top

Cardinality; refers to the number of instances of an entity in a relationship. Usually expressed as one-to-one, one-to-many or many-to-many but could be specific as in 1 team to 5 players. 1 and 5 are the cardinality.

Back Top

Cascade delete; specifies how foreign key references are to be treated when the corresponding row in the parent table is deleted. To cascade deletes means to delete all corresponding child rows.

The SQL syntax is ...ON DELETE CASCADE added to the foreign key constraint clause.

Back Top

Cascade update; specifies how foreign key references are to be treated when the primary key in the corresponding parent table row is changed. Cascade updates will change the foreign keys references in the child table(s) to the new value.

The SQL syntax is ON UPDATE CASCADE

Back Top

CASE; Computer Aided Software Engineering. For our purposes database modeling tools such as ERwin, Visio, Embarcadero, etc. Typically, databases are initially constructed with a CASE tool rather than writing out the DDL scripts by hand.

Back Top

Child Table; the table on the many side of a one-to-many relationship.

Back Top

Click to see an example of a child table.

Column Alias; see alias

Back Top

Composite entity; an entity created to represent a many to many relationship between two entities. The composite entity has many to 1 relationships to the existing entities and a composite identifier consisting of the two identifiers from the original entities. In short, the logical analog of the intersection table. An example would be a Line-Items entity representing the relationship between Purchase_Orders and Parts.

Back Top

Composite key; a key composed of more than one column. Sometimes called a concatenated key. To create a composite key in MS Access hold down the <ctrl> key and select all the desired fields then click the Toolbar Primary Key Button. To create the composite primary key in the LABOR table using Access DDL do as follows:

Create table labor(
wono char(2),
empno char(2),
start datetime,
end datetime,
hours single,
primary key(wono, empno))

To join tables with composite keys you join over all columns. Say, for example there was an OVERTIME table structured the same as the LABOR table. To join the two tables the SQL would look like:

Select ...
from labor as l
inner join overtime as o
on l.wono = o.wono
and l.empno = o.empno

In the MS Access Query Designer you would drag both primary key fields to the corresponding foreign keys in the other table. Note: in the example above l and o are aliases, which see above.

Back Top

Compound query; another term for a UNION query.

Back Top

Computed column; another term for calculated field.

Back Top

Concatenated key; see composite key. Another definition is a key concatenated from two or more columns strung together (concatenated) as in "first_name" + "last_name". (Note: in Access the "+" would be an "&". The SQL standard is "||".

Back Top

Concatenation; process of "adding" two (or more) strings such as first name and last name. In standard SQL and Oracle the operator is ||. In SQL Server a +. In Access you can use either & or +, but the two have a subtly different meaning. The & treats a NULL as the empty string. Remember, NULL + anything is NULL! NULL & somestring is somestring, which is probably what you want.

Back Top

Correlation name; another, but less common term for table alias.

Back Top

Covered query; a type of query which retrieves only fields that have been indexed. Can be very fast.

Back Top

Cursor; CURent Set Of Records. Like a dynaset or recordset. However, can be created and read from on the client or the server using specific commands in the SQL language.

Also used to mean the current record pointer into a recordset. Example: "with the cursor on the 5th record..."

Back Top

DAO; Data Access Object; a middleware object used in Visual Basic or Microsoft Access to retrieve and manipulate recordsets in a database. Being phased out in favor of ADO but still widely used in the Microsoft Access community.

Back Top

Data Mart; subset of a Data Warehouse. Typically established to serve reporting purposes for a specific function or department.

Back Top

Data type; the type of data - numeric, text, etc., that a field contains. Click the red arrow to the right to see the definitions of the various data types in Microsoft Access.

Back Top

Show me the Access data type definitions

DDL; Data Definition Language. The subset of SQL used to create and define database structures. Examples are the CREATE and ALTER statements. To implement a DDL statement in MS Access use Query --> SQL Specific --> Data Definition while the Query Designer is open. Alternatively, simply type them while in SQL View.

Back Top

Degree; the number of participating entities in a relationship. The degree of the relationship between employees and labor tickets is two. Between projects, skills, and employees is three. The former is also called a binary relationship, the latter a ternary. Binary relationships are the most common.

Back Top

Delete Query; an Access action query that deletes records from a table. Behind the scenes it creates an SQL DELETE statement.

Back Top

Denormalization; sometimes for performance reasons a normalized database needs to be denormalized to some degree. This should be done carefully with awareness of the consequnces on data integrity and modification. A classic example would be city, state and zip. A fully normalized design would have a table of zip-to-city, state. However, this would cause an additional join to be done to print customer addresses.

Back Top

Derived Table; temporary table created on the fly via a subquery. Example:

Select e.name
from (select * from employees where mgrno = '1001') as e

Back Top

Determinant; a column or set of columns that determines the value of another column. For example EmployeeID determines EmployeeName. A properly normalized table does not contain any non-key determinants.

Back Top

Domain; the set of allowable values for a column such as currency amounts, integers > 0, numbers between 4.5 and 5.5, dates, etc. A couple examples from the sample tables: Work Order "Numbers" must be two characters of which the first is an alpha. Employee Number must be whole numbers greater than 1000.

Back Top

Domain integrity; ensuring that values entered into a database are within the allowed domain. Done via datatypes and validation rules or check constraints. Click the red arrow to the right to see an example.

Back Top

Click to see an example of an MS Access validation

Drop; the SQL statement used to remove an object such as a table, view or index from a databases. Note that delete is not used for this purpose. It removes records. Drop is used in Oracle, SQL Server, etc. There is little use for it in MS Access.

Back Top

Dynaset; when MS Access executes a query the results are held in RAM as a sort of temporary table. This is a dynaset. It can be operated on just like a table.

Back Top


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

Employees

EmpNo

Name

DeptID

MgrNo

1001

Bill

A1

Null

1002

Mary

C1

1001

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


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

Receive course announcements and news. Sign Up Today!





Email Marketing by VerticalResponse
Course Catalog

 



Related Glossaries
Heath Information IT (HIT) Terms from PC Magazine | Dr. Mike's Database Glossary | SQL Strings Database Glossary


Database Glossary Page 1

Page 2(E-M) | Page 3 (N-Z)

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.

 Contact Info. | FAQ's | Site Map | Back | Home | Top