Top bar

Home

About Us

Catalog

Resources

Contact Us

Glossary of database Terms - Page 2

Page 1(1-9, A-D) | Page 3 (N-Z)

Sample Tables | 1-9 | A-D |Entity | Entity Integrity | Field | First Normal Form | Foreign Key | Functional Dependency | HAS-A Relationship | ID-Dependent Entity | Identifying Relationship | IE Notation | Index|Inner Join | Input Mask | Integrity | Intermediate Table | INTERSECT |Intersection Table | IS-A Relationship | Jet SQL | Junction Table | K | Left Join | Left Outer Join | Linking Column | Linking Table | MakeTable Query | Many-to-1 Relationship | Many-to-Many Relationship | Meta Data| Multi-valued Subquery | 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)


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.

Back Top

Inner join 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


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


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 2

Page 1(1-9, A-D) | 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