|
Search:
Advanced search
|
Browse by category:
|
Glossary | Contact Us |
 |
|
Glossary
|
Access
Microsoft Access is an entry-level database management software from Microsoft, which allows you to organize, access, and share information easily. Access is very user-friendly and easy to use for inexperienced users, while sophisticated enough for database and software developers.
Microsoft Access is an entry-level database management software from Microsoft, which allows you to organize, access, and share information easily. Access is very user-friendly and easy to use for inexperienced users, while sophisticated enough for database and software developers.
ACID
ACID short for Atomicity – Consistency – Isolation – Durability and describes the four properties of an enterprise-level transaction: ATOMICITY: a transaction should be done or undone completely. In the event of an error or failure, all data manipulations should be undone, and all data should rollback to its previous state. CONSISTENCY: a transaction should transform a system from one consistent state to another consistent state. ISOLATION: each transaction should happen independently of other transactions occurring at the same time. DURABILITY: Completed transactions should remain stable/permanent, even during system failure.
ACID short for Atomicity – Consistency – Isolation – Durability and describes the four properties of an enterprise-level transaction: ATOMICITY: a transaction should be done or undone completely. In the event of an error or failure, all data manipulations should be undone, and all data should rollback to its previous state. CONSISTENCY: a transaction should transform a system from one consistent state to another consistent state. ISOLATION: each transaction should happen independently of other transactions occurring at the same time. DURABILITY: Completed transactions should remain stable/permanent, even during system failure.
ADO
Short for Microsoft ActiveX Data Objects. ADO enables your client applications to access and manage data from a range of sources through an OLE DB provider. ADO is built on top of OLE DB and its main benefits are ease of use, high speed, and low memory overhead. ADO makes the task of building complex database enabled client/server and web applications a breeze.
Short for Microsoft ActiveX Data Objects. ADO enables your client applications to access and manage data from a range of sources through an OLE DB provider. ADO is built on top of OLE DB and its main benefits are ease of use, high speed, and low memory overhead. ADO makes the task of building complex database enabled client/server and web applications a breeze.
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.
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
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.
candidate key(s) other than the one chosen as primary.
Cluster Key
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster
Clusters
Group of tables physically stored together because they share common columns and are often used together is called Cluster.
Group of tables physically stored together because they share common columns and are often used together is called Cluster.
Column
Database tables are made of different columns (fields) corresponding to the attributes of the object described by the table.
Database tables are made of different columns (fields) corresponding to the attributes of the object described by the table.
COMMIT
The COMMIT command in SQL marks the finalization of a database transaction.
The COMMIT command in SQL marks the finalization of a database transaction.
Control files (Oracle)
The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information: Database information (RESETLOGS SCN and their time stamp) Archive log history Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not) Redo threads (current online redo log) Database's creation date database name current archive log mode Log records (sequence numbers, SCN range in each log) RMAN catalog Database block corruption information Database ID, which is unique to each DB
The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information: Database information (RESETLOGS SCN and their time stamp) Archive log history Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not) Redo threads (current online redo log) Database's creation date database name current archive log mode Log records (sequence numbers, SCN range in each log) RMAN catalog Database block corruption information Database ID, which is unique to each DB
Cross Join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
Cursor
Short for Current Set Of Records in some database languages. The cursor is a database object pointing to a currently selected set of records.
Short for Current Set Of Records in some database languages. The cursor is a database object pointing to a currently selected set of records.
Data
Piece of information collected and formatted in a specific way. The term data is frequently used to describe binary (machine-readable) information.
Piece of information collected and formatted in a specific way. The term data is frequently used to describe binary (machine-readable) information.
Database
A database is a collection of information organized into related tables of data and definitions of data objects. The data within a database can be easily accessed and manipulated trough computer program.
A database is a collection of information organized into related tables of data and definitions of data objects. The data within a database can be easily accessed and manipulated trough computer program.
Database Administrator
Database administrator a role or person who is responsible for the environmental aspects of a database A Database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include: Recoverability - Creating and testing Backups Integrity - Verifying or helping to verify data integrity Security - Defining and/or implementing access controls to the data Availability - Ensuring maximum uptime Performance - Ensuring maximum performance Development and testing support - Helping programmers and engineers to efficiently utilize the database. The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database design are traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.
Database administrator a role or person who is responsible for the environmental aspects of a database A Database administrator (DBA) is a person who is responsible for the environmental aspects of a database. In general, these include: Recoverability - Creating and testing Backups Integrity - Verifying or helping to verify data integrity Security - Defining and/or implementing access controls to the data Availability - Ensuring maximum uptime Performance - Ensuring maximum performance Development and testing support - Helping programmers and engineers to efficiently utilize the database. The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database design are traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.
Database Analyst
Creates reports and data models for personal use and perhaps for other people in an organization. Analysts can be data processing professionals, but are more frequently responsible for analyzing enterprise data while they perform other job functions.
Creates reports and data models for personal use and perhaps for other people in an organization. Analysts can be data processing professionals, but are more frequently responsible for analyzing enterprise data while they perform other job functions.
Database Architect
Defines the end-to-end technology and infrastructure design for a system. Also defines the vision, scope, and interoperability for the projects to implement each part of the system.
Defines the end-to-end technology and infrastructure design for a system. Also defines the vision, scope, and interoperability for the projects to implement each part of the system.
Database Developer
Developer
Developer
DB2
DB2 is a relational database management system developed by IBM. DB2 runs on a variety of platforms including Sun Solaris, Linux and Windows.
DB2 is a relational database management system developed by IBM. DB2 runs on a variety of platforms including Sun Solaris, Linux and Windows.
DBA
Database administrator a role or person who is responsible for the environmental aspects of a database
Database administrator a role or person who is responsible for the environmental aspects of a database
dBase
dBase is a popular relational database management system produced by Ashton-Tate corporation in the early 1980s. The dBase format for storing data has become industry standard and is still in use today.
dBase is a popular relational database management system produced by Ashton-Tate corporation in the early 1980s. The dBase format for storing data has become industry standard and is still in use today.
DELETE
The DELETE is a SQL command used to delete record(s) from a table in database.
The DELETE is a SQL command used to delete record(s) from a table in database.
Field
See Column definition
See Column definition
Flat File
Flat file is a data file that has no structured relationships between its records.
Flat file is a data file that has no structured relationships between its records.
Foreign Key
A foreign key is a key field (column) that identifies records in a table, by matching a primary key in a different table. The foreign keys are used to cross-reference tables.
A foreign key is a key field (column) that identifies records in a table, by matching a primary key in a different table. The foreign keys are used to cross-reference tables.
FoxPro
Visual FoxPro is a Microsoft development environment designed for database developers.
Visual FoxPro is a Microsoft development environment designed for database developers.
Index
An index is a database feature (a list of keys or keywords), allowing searching and locating data quickly within a table. Indexes are created for frequently searched attributes (table columns) in order to optimize the database performance.
An index is a database feature (a list of keys or keywords), allowing searching and locating data quickly within a table. Indexes are created for frequently searched attributes (table columns) in order to optimize the database performance.
INSERT
The INSERT is a SQL command used to add a new record to a table within a database.
The INSERT is a SQL command used to add a new record to a table within a database.
JOIN
The JOIN is a SQL command used to retrieve data from 2 or more database tables with existing relationship based upon a common attribute.
The JOIN is a SQL command used to retrieve data from 2 or more database tables with existing relationship based upon a common attribute.
Lock
Locks are used by Database management systems to facilitate concurrency control. Locks enable different users to access different records/tables within the same database without interfering with one another. Locking mechanisms can be enforced at the record or table levels.
Locks are used by Database management systems to facilitate concurrency control. Locks enable different users to access different records/tables within the same database without interfering with one another. Locking mechanisms can be enforced at the record or table levels.
Logical file names
The names that are used by Database Server to identify files within an SQL Server database.
The names that are used by Database Server to identify files within an SQL Server database.
Meta Data
Metadata is data about data. It describes what sort of data is to follow so that programs that process the data can handle it correctly.
Metadata is data about data. It describes what sort of data is to follow so that programs that process the data can handle it correctly.
MyISAM
MyISAM is the default storage engine. It is based on the older ISAM code but has many useful extensions. (Note that MySQL 5.0 does not support ISAM.) Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
MyISAM is the default storage engine. It is based on the older ISAM code but has many useful extensions. (Note that MySQL 5.0 does not support ISAM.) Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
MySQL
MySQL is an open source relational database management system. MySQL can be used on various platforms including UNIX, Linux and Windows (there are OLE DB and ODBC providers as well as .NET native provider for MySQL). MySQL is widely used as a backend database for Web applications and it' viable and cheaper alternative to enterprise database systems like MS SQL Server and Oracle.
MySQL is an open source relational database management system. MySQL can be used on various platforms including UNIX, Linux and Windows (there are OLE DB and ODBC providers as well as .NET native provider for MySQL). MySQL is widely used as a backend database for Web applications and it' viable and cheaper alternative to enterprise database systems like MS SQL Server and Oracle.
Normalization
Normalization Normalization is the process of organizing data to minimize redundancy and remove ambiguity. Normalization involves separating a database into tables and defining relationships between the tables. There are three main stages of normalization called normal forms. Each one of those stages increases the level of normalization. The 3 main normal forms are as follows: First Normal Form (1NF): Each field in a table must contain different information. Second Normal Form (2NF): All attributes that are not dependent upon the primary key in a database table must be eliminated. Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a common field, this common field information should be separated into a different table. There are 2 more normalization forms, fourth normal form (4NF) and fifth normal form (5NF), but they are rarely used. Normalization makes databases more efficient and easier to maintain.
Normalization Normalization is the process of organizing data to minimize redundancy and remove ambiguity. Normalization involves separating a database into tables and defining relationships between the tables. There are three main stages of normalization called normal forms. Each one of those stages increases the level of normalization. The 3 main normal forms are as follows: First Normal Form (1NF): Each field in a table must contain different information. Second Normal Form (2NF): All attributes that are not dependent upon the primary key in a database table must be eliminated. Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a common field, this common field information should be separated into a different table. There are 2 more normalization forms, fourth normal form (4NF) and fifth normal form (5NF), but they are rarely used. Normalization makes databases more efficient and easier to maintain.
NULL
The NULL SQL keyword is used to represent a missing value.
The NULL SQL keyword is used to represent a missing value.
ODBC
Short for Open DataBase Connectivity, a standard database access technology developed by Microsoft Corporation. The purpose of ODBC is to allow accessing any DBMS (DataBase Management System) from any application (as long as the application and the database are ODBC compliant), regardless of which DBMS is managing the data. ODBC achieves this by using a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to transform the application's data queries into commands that the DBMS understands. As we said earlier, both the application and the DBMS must be ODBC compliant meaning, the application must be capable of sending ODBC commands and the DBMS must be capable of responding back to them.
Short for Open DataBase Connectivity, a standard database access technology developed by Microsoft Corporation. The purpose of ODBC is to allow accessing any DBMS (DataBase Management System) from any application (as long as the application and the database are ODBC compliant), regardless of which DBMS is managing the data. ODBC achieves this by using a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to transform the application's data queries into commands that the DBMS understands. As we said earlier, both the application and the DBMS must be ODBC compliant meaning, the application must be capable of sending ODBC commands and the DBMS must be capable of responding back to them.
OLE DB
Short for Object Linking and Embedding Data Base. OLE DB is a set of COM-based interfaces that expose data from a range of sources. OLE DB interfaces give applications standardized access to data stored in various information sources like Relational Database Management Systems (MS SQL Server, Oracle, MySQL), small personal databases like MS Access, productivity tools like spreadsheets; plain text files, etc. These interfaces support the amount of DBMS functionality appropriate to the data store, allowing the data store to share its data.
Short for Object Linking and Embedding Data Base. OLE DB is a set of COM-based interfaces that expose data from a range of sources. OLE DB interfaces give applications standardized access to data stored in various information sources like Relational Database Management Systems (MS SQL Server, Oracle, MySQL), small personal databases like MS Access, productivity tools like spreadsheets; plain text files, etc. These interfaces support the amount of DBMS functionality appropriate to the data store, allowing the data store to share its data.
OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.
Oracle
Oracle is an enterprise relational database management system. Oracle's main rival product MS SQL Server is a low cost alternative offering the same features.
Oracle is an enterprise relational database management system. Oracle's main rival product MS SQL Server is a low cost alternative offering the same features.
Pages
A Database Table at its smallest physical level is made up from Pages which contain data from one or more rows.
A Database Table at its smallest physical level is made up from Pages which contain data from one or more rows.
Physical file names
The name used by the operating system to identify specific files.
The name used by the operating system to identify specific files.
PostgreSQL
PostgreSQL is an object-oriented open source relational database management system, which uses a subset of SQL language.
PostgreSQL is an object-oriented open source relational database management system, which uses a subset of SQL language.
Primary Key
The primary key of a relational table holds a unique value, which identifies each record in the table. It can either be a normal field (column) that is guaranteed to be unique or it can be generated by the database system itself (GUID or Identity field in MS SQL Server for example). Primary keys may be composed of more than 1 field (column) in a table.
The primary key of a relational table holds a unique value, which identifies each record in the table. It can either be a normal field (column) that is guaranteed to be unique or it can be generated by the database system itself (GUID or Identity field in MS SQL Server for example). Primary keys may be composed of more than 1 field (column) in a table.
Query
Queries are the main way to make a request for information from a database. Queries consist of questions presented to the database in a predefined format, in most cases SQL (Structured Query Language) format.
Queries are the main way to make a request for information from a database. Queries consist of questions presented to the database in a predefined format, in most cases SQL (Structured Query Language) format.
Query Optimiser
In MSSQL server an intermediate process is involved between requesting execution of a statement and its actual execution. This process is query Optimisation and is performed by the query optimiser. The Query Optimiser takes the SQL statement that is to be execute on the underlying Database and breaks it down in to its constituent parts. I then works out the optimal order for execution based on both predefined Database rules and on information it has gathered whilst running the database. The optimised query is then executed on the database.
In MSSQL server an intermediate process is involved between requesting execution of a statement and its actual execution. This process is query Optimisation and is performed by the query optimiser. The Query Optimiser takes the SQL statement that is to be execute on the underlying Database and breaks it down in to its constituent parts. I then works out the optimal order for execution based on both predefined Database rules and on information it has gathered whilst running the database. The optimised query is then executed on the database.
RDBMS
RDBMS stands for Relational Database Management System and it the term for the Database server that holds one or more relational databases
RDBMS stands for Relational Database Management System and it the term for the Database server that holds one or more relational databases
Record
The record is a complete set of information presented within a RDBMS. Records are composed of different fields (columns) in a table and each record is represented with a separate row in this table.
The record is a complete set of information presented within a RDBMS. Records are composed of different fields (columns) in a table and each record is represented with a separate row in this table.
Replication
Replication is the process by which one Database id recreated in its entirety in another physical location.
Replication is the process by which one Database id recreated in its entirety in another physical location.
ROLLBACK
The ROLLBACK is a SQL command which cancels/undoes the proposed changes in a pending database transaction and marks the end of the transaction.
The ROLLBACK is a SQL command which cancels/undoes the proposed changes in a pending database transaction and marks the end of the transaction.
SELECT
The SELECT is a SQL command, which is the primary means for retrieving data from a RDBMS.
The SELECT is a SQL command, which is the primary means for retrieving data from a RDBMS.
Self Join
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.
SQL
SQL is short for Structured Query Language and is an industry standard language used for manipulation of data in a RDBMS. There are several different dialects of SQL like, ANSI SQL, T-SQL, etc.
SQL is short for Structured Query Language and is an industry standard language used for manipulation of data in a RDBMS. There are several different dialects of SQL like, ANSI SQL, T-SQL, etc.
Stored Procedure
Stored Procedure is a set of SQL statements stored within a database server and is executed as single entity. Using stored procedures has several advantages over using inline SQL statements, like improved performance and separation of the application logic layer from database layer in n-tier applications.
Stored Procedure is a set of SQL statements stored within a database server and is executed as single entity. Using stored procedures has several advantages over using inline SQL statements, like improved performance and separation of the application logic layer from database layer in n-tier applications.
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.
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.
Table
A Table in RDBMS refers to data arranged in rows and columns, which defines a database entity.
A Table in RDBMS refers to data arranged in rows and columns, which defines a database entity.
Table Constraint
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.
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.
Transaction
Transaction is a group of SQL database commands regarded and executed as a single atomic entity.
Transaction is a group of SQL database commands regarded and executed as a single atomic entity.
Transaction Log
Each Database in a RDBMS has a Transaction Log which contains details on all the actions that have been performed on the Database so that if necessary a roll back to an earlier state can be performed without any loss of data.
Each Database in a RDBMS has a Transaction Log which contains details on all the actions that have been performed on the Database so that if necessary a roll back to an earlier state can be performed without any loss of data.
Transient link
a temporary relationship between two tables created by the Access query designer that lasts only for the duration of the query.
a temporary relationship between two tables created by the Access query designer that lasts only for the duration of the query.
Trigger
Triggers are special type of stored procedures executed automatically when certain events take place. There are different types of triggers – for update, for insert and for delete. Each trigger is associated with a single database table.
Triggers are special type of stored procedures executed automatically when certain events take place. There are different types of triggers – for update, for insert and for delete. Each trigger is associated with a single database table.
Tuple
relational theory term for row. Relations have tuples, tables have rows, files have records. Avoid this term around the office.
relational theory term for row. Relations have tuples, tables have rows, files have records. Avoid this term around the office.
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.
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.
UNION
An SQL 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.
An SQL 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.
UPDATE
The UPDATE is a SQL command used to edit/update existing records in a database table.
The UPDATE is a SQL command used to edit/update existing records in a database table.
Powered by
DBNest.com (Nest of DB Professionals)