|
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. |
|
|
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 |
|
|
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
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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 |
red
arrows to the right of the text.
