|
Entity;
term used in logical database modeling for something the user wants
to track. Typically an object such as a customer or product but also
events such as sales or purchases. Entities can often be determined
by examining descriptions of the database's functions and extracting
the nouns. |
Back Top |
|
Entity integrity;
ensuring that every row in a table is unique. Setting a primary key
will guarantee this. Required for a truly relational database. |
Back Top |
|
Field; By example a customers table would
have one record for each customer. Then each record would be broken
down into fields each containing a single element of information such
as first name or city. A field is analagous to a column in a table or
an attribute of a logical entity. |
Back Top |
|
Foreign key;
a key in a table that refers to the primary key in another table.
Example: Deptid in
an EMPLOYEES table that refers to a DEPARTMENTS table with a primary
key of deptid. |
Back Top |
|
Functional
dependency;
term used in normalization theory. The dependency of a column on one
or more other columns. In table EMPLOYEES below the name is
functionally dependent on the empno. In table LABOR the hours worked
column is functionally dependent on both empno and wono. In a
properly designed table all non-key columns should be functionally
dependent only on the primary key. (Leaving aside the alternate
keys). |
Back Top |
|
HAS-A Relationship; an
ordinary association between two entities such as invoices have line
items or employees have dependendents. Contrast with IS-A
below. The relationships in our little sample tables below are of
this flavor. |
Back Top |
|
ID-Dependent
Entity;
an entity that is entirely dependent on another for its existence
such as a dorm room or an invoice line-item. In such cases the
primary key of the parent table becomes part of the primary key of
the child (or at least a foreign key if a surrogate primary is used)
and is a foreign key to the parent as well. In the sample tables
below the LABOR table has a composite primary key with two columns.
Each column is also a foreign key to the corresponding parent table. |
Back Top |
|
Identifying
Relationship;
a relationship between a strong and weak entity wherein the very
existence of the weak entity depends on the strong one. For example a
dorm with id-dependent dorm rooms. This fact is used by many CASE
tools to migrate the primary key from the parent table to the child.
In our sample tables below, EMPLOYEES and LABOR have an identifying
relationship. (WORK_ORDERS and LABOR). Contrast with a non-identifying
relationship. |
Back Top |
|
IE (Information
Engineering) Notation; probably the most common notation
convention for creating a logical database model. It's the one with
the little crow's feet. Entities are represented by squares. You draw
a solid line connecting two related entites. On the many side(s)
place a symbol that looks like, well, a crow's foot. The vertical bar
means madatory, the circle optional.
|
Back Top |
|
Index;
a physical structure in a relational database that works sort of
like an index in a book. It can be used to speed searching and
sorting. An often overlooked use of indexes is to enforce the
uniqueness of values in a column, phone numbers for example. In
access you specify whether or not you want a column indexed and if so
whether or not duplicates are allowed. Allow duplicates for foreign
keys and search columns. For primary keys and columns of unique
values use no duplicates. |
Back Top |
|
Inner Join;
a type of join in which only rows with matching join columns from
both tables are returned. For example in the sample tables an inner
join on Departments and Employees on deptID would return two rows,
one for Assembly and one for Casting. This is the default join in
Access and most databases. Click the red arrow for an example in Microsoft Access. |
|
|
Input Mask; a template
applied by form generators to an input field to control how typed
characters are interpreted. Here are the input masks, after Duffy,
for Access.
|
0 |
Digit only; entry required; no + or - |
|
9 |
Digit or space; entry required; no + or - |
|
# |
Digit or space; entry optional; + and - allowed |
|
L |
Letter only; entry required |
|
? |
Letter only; entry optional |
|
A |
Letter or digit; entry required |
|
a |
Letter or digit; entry optional |
|
& |
Any character or space; entry required |
|
C |
Any character or space; entry optional |
|
< |
Convert characters to lower case |
|
> |
Convert characters to upper case |
|
! |
Fill mask from right to left when characters on the
left are optional. |
|
\ |
Following character is interpreted as is. sort of like
the UNIX escape. |
Don't over do it with input masks. In this era of
increased globalization input masks for things like ZIP (postal)
codes and phone numbers can be counter productive. |
Back Top |
|
Integrity; it is
important that a database guarantee the integrity of its information.
The three types of integrity are domain,
entity, and referential. |
Back Top |
|
Intermediate Table,
see intersection table. |
Back Top |
INTERSECT;
An SQL set operation statement which retrieves the results of two or more SELECT
queries that have matching column values. Say for example there is a work_orders_foreign table in our sample
database which contains records for work orders being worked on offshore. We could use the INTERSECT
operator to retrieve the records being worked both domestically and offshore as shown below. The records retrieved would have the same Wono and Description.
SELECT
*
FROM
work_orders
INTERSECT
SELECT
*
FROM
work_orders_foreign;
Unfortunately,
Access does not provide a Wizard or Designer view to do this. You
have to use the SQL View. |
Back Top |
|
Intersection Table,
a third, intermediate table created to implement a many-to-many
relationship between two entities. Also called a junction,
intermediate or resolution table. The primary keys from the two
entities are placed in the intersection table as foreign keys.
Commonly these two form a composite primary key. The LABOR
table is an example. |
Back Top |
|
IS-A Relationship; an
association between two entites wherein one is a type of the other.
For example a mechanic is a type of employee. Employee is the
super-type and mechanic the sub-type. The sub-type inherits the
atrributes of the parent super-type and would have its own unique
attributes as well. In this case emloyee might have name, address,
hire date and salary. Mechanic would have these as well, plus perhaps
licenses and certifications. When implemented
the common attributes would be columns in an employees table.
Mechanic licenses and certications would be columns in a mechanics
table. This table would have the same primary key or a foreign key to employees. |
Back Top |
|
Jet SQL; the
variant of SQL implemented by the MS Access Jet (Joint Engine
Technology) database engine. |
Back Top |
|
Junction table;
see intersection table. |
Back Top |
|
Left Outer Join;
outer is optional. A type of join which selects all rows with
matching join columns plus all non matched rows from the first table.
Missing values are filled with nulls. In our sample tables
a left join of DEPARTMENTS and EMPLOYEES on DeptID would return three
rows. For the refurbishment department the employee name, etc. would
be null. To create a left outer join in MS Access right click on the
relationship line between the two tables in the Relationship or Query
Designer. Select EDIT
RELATIONSHIP --> JOIN TYPE.
The example SQL code would be:
Select
dept.*, emp.*
from
departments dept
left
outer join employees emp
on
dept.deptid = emp.deptid;
This is ANSI standard syntax (and JetSQL). An older
but very common form would look like like this:
Select
dept.*, emp.*
from
departments dept, employees emp
where
dept.deptid = emp.deptid (+);
The above is Oracle. SQL Server would use...
where
dept.deptid *= emp.deptid
Both are non standard. In all these example we have
used shorthand to refer to table names. These shorthand names are
called aliases or correlation names. Create them right after the
table name in the FROM clause. In the MS Access Query Designer right
click on the table and select PROPERTIES. |
Back Top |
|
<Linking
Column;
term sometimes used in MS Access to refer to the common column over
which a join is performed. For example joining the LABOR table and
the EMPLOYEES table over the empno column in the sample tables.
Usually this is a primary key in one table and a foreign key in the
other. Also called the join column. |
Back Top |
|
Linking
Table;
another term (perhaps more common, even) for Intersection
Table. |
Back Top |
|
Maketable Query;
a type of Microsoft Access query that builds a table
"on-the-fly" from an existing table or query. The SQL
equivalent is a select...into (SQL Server) or create table....as
select... (Oracle, MySQL). |
Back Top |
|
Many-to-1
Relationship;
a 1-to-many looked at the other way around. Sometimes called a
lookup. Your choice whether or not you consider this a different type
of relationship. In MS Access a many-to-1 query is called an
AutoLookup Query. It automatically fills values from the one side
when the foreign key in the many side changes. Very handy for
automatically filling in product description, price, etc. in an order
items sub-form. |
Back Top |
|
Many-to-many
relationship; a relationship between two entities in
which each row in one table can be related to more than one row in
the other. Our example is EMPLOYEES and WORK-ORDERS. Each work order
may be worked on by multiple employees and each employee may have
worked on more than one work order. This type of relationship cannot
be directly implemented in a relational database. It requires a third
intersection table (which
see). In the sample cost accounting tables
below this is the LABOR tickets table. |
Back Top |
|
Meta
Data;
data about data. Relational databases (and other tools) must keep
track of the length, type, display titles, etc. of the columns and other objects in the
database. This information is called Meta Data. Desktop databases
such as MS Access also often store application meta data which
define form and report properties, etc. Meta data is typically stored
in internal tables within the database itself and can be queried like
any other data. |
Back Top |
|
Multi-valued subquery;
a subquery that returns a list of values. In the example below the
subquery returns a list of employee numbers which are then searched
by the IN operator. Example: find employees with a labor record in
the LABOR table (i.e., employees that have done work).
Select
name
from
employees
where
empno in (select empno from labor); |
Back Top |
