15 August 2009

🛢DBMS: Deadlocks (Definitions)

 "A situation which arises when two users, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece of data. The SQL Server detects deadlocks, and kills one user’s process." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

"A situation that arises when two users, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each user waits for the other to release his or her lock. SQL Server detects deadlocks and kills one user’s process, returning error code 1205." (Patrick Dalton, "Microsoft SQL Server Black Book", 1997)

"A condition that arises when two or more transactions are waiting for one another to release locks." (Peter Gulutzan & Trudy Pelzer, "SQL Performance Tuning", 2002)

"A situation in SQL Server 2000 when two users each have a lock on one piece of data and they attempt to acquire a lock on the other’s piece of data. Each user would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"A state in which two users or processes cannot continue processing because they each have a resource that the other needs." (Thomas Moore, "EXAM CRAM™ 2: Designing and Implementing Databases with SQL Server 2000 Enterprise Edition", 2005)

"Occurs when multiple connections that hold locks are waiting for locks to be released that are held by the other transactions, that is, the transactions are waiting for each other to release locks." (Sara Morganand & Tobias Thernstrom , "MCITP Self-Paced Training Kit : Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 - Exam 70-442", 2007)

"A specific type of locking problem when concurrent processes are competing for locks. For example, program1 holds a lock on A and is waiting for a lock on B; program2 holds a lock on B and is waiting for a lock on A." (Craig S Mullins, "Database Administration: The Complete Guide to DBA Practices and Procedures" 2nd Ed, 2012)

"A deadlock occurs when two or more user processes each have a lock on a separate page or table and each wants to acquire a lock on the other process’s page or table. The transaction with the least accumulated CPU time is killed and all of its work is rolled back." (Sybase)

"A situation in which two or more users are waiting for data locked by each other." (Oracle)

"A situation where different transactions are unable to proceed, because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither one ever releases the locks it holds." (MySQL)

"Unresolved contention for the use of resources." (IBM)



🛢DBMS: Lock/Locking (Definitions)

"The process of restricting access to resources in a multi-user environment to maintain security and prevent concurrent access problems. SQL Server automatically applies locks to tables or pages." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

"SQL Server issues locks to prevent users from interfering with each other's work." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"A restriction on access to a resource in a multiuser environment. SQL Server locks users out of a specific record, field, or file automatically to maintain security or prevent concurrent data manipulation problems." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"A method the DBMS uses to prevent concurrent transactions from interfering with one another. Physically, a lock is one of three things: a latch, a mark on the wall, or a RAM record." (Peter Gulutzan & Trudy Pelzer, "SQL Performance Tuning", 2002)

"A method of ensuring concurrency. Locking enables users to temporarily "check out" an object, preventing other users from changing the object, for the purpose of ensuring consistency." (Thomas Moore, "EXAM CRAM™ 2: Designing and Implementing Databases with SQL Server 2000 Enterprise Edition", 2005)

"SQL Server uses locks to prevent multiple users from modifying the same data at the same time." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)

"A mechanism used by a concurrent system to prevent data anomalies by isolating transactions from each other." (Marilyn Miller-White et al, "MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance 70-444", 2007)

"A lock is an access restriction placed on part of a database to prevent other users or processes from viewing or modifying data as it is being viewed or modified by one process. Locks can be placed on rows, pages, extents, tables, or databases." (Darril Gibson, "MCITP SQL Server 2005 Database Developer All-in-One Exam Guide", 2008)

"MongoDB uses locks to ensure that concurrency does not affect correctness. MongoDB uses read locks, write locks and intent locks. For more information, see What type of locking does MongoDB use?." (MongoDb, "Glossary", 2008)

"Used to control access to part of the database. For example, while one user updates a row, the database places a lock on the row so other users cannot interfere with the update. Different databases may lock data by rows, table, or disk page." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"The processing of giving a transaction exclusive rights to view and/or update a database element to prevent problems that arise with interleaved transaction execution." (Jan L Harrington, "SQL Clearly Explained 3rd Ed. ", 2010)

"A DBMS function used to ensure the integrity of data. When a database resource is locked by one process, another process is not permitted to change the locked data. Locking is necessary to enable the DBMS to facilitate the ACID properties of transaction processing." (Craig S Mullins, "Database Administration: The Complete Guide to DBA Practices and Procedures" 2nd Ed, 2012)

"A restriction on access to a resource in a multiuser environment." (Microsoft, "SQL Server 2012 Glossary", 2012)

"A means of preventing uncommitted changes made by one application process from being perceived by another application process and for preventing one application process from updating data that is being accessed by another process. A lock ensures the integrity of data by preventing concurrent users from accessing inconsistent data. A means of serializing a sequence of events or serializing access to data." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

"The process by which a DBMS restricts access to a row in a multiuser environment. The DBMS usually sets a bit on a row or the physical page containing a row that indicates the row or page is locked." (Microsoft) 

12 August 2009

🛢DBMS: Open Database Connectivity (Definitions)

"A set of drivers provided by different manufacturers that allows client applications to connect to database servers without regard to native database language. These drivers interpret data requests and translate those requests into a format that the database understands." (Patrick Dalton, "Microsoft SQL Server Black Book", 1997)

"A database-neutral API for Microsoft Windows applications. ODBC is designed to enable any database vendor to write a driver that supports the API." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"A database-material application programming interface (API) aligned with the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standards for a database call-level interface (CLI). ODBC supports access to any database for which an ODBC driver is available." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"A data access application programming interface (API) that supports access to any data source for which an ODBC driver is available. ODBC is aligned with the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standards for a database Call Level Interface (CLI)." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"A set of standards that allow applications that are not dependent on any one specific database to process SQL statements against any database that supports SQL." (Bob Bryla, "Oracle Database Foundations", 2004)

"A data access application programming interface (API) that supports access to any data source for which an ODBC driver is available. ODBC is aligned with the American National Standards Institute (ANSI) and International Standards Organization for (ISO) standards for a database Call Level Interface (CLI)." (Thomas Moore, "EXAM CRAM™ 2: Designing and Implementing Databases with SQL Server 2000 Enterprise Edition", 2005)

"Interface used to provide access to data sources that provide an ODBC driver. The driver is a software file that provides instructions for how to access the data source." (Sara Morganand & Tobias Thernstrom , "MCITP Self-Paced Training Kit : Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 - Exam 70-442", 2007)

"A data access API that supports access to any data source for which an ODBC driver is available." (Jim Joseph, "Microsoft SQL Server 2008 Reporting Services Unleashed", 2009)

"Database middleware developed by Microsoft to provide a database access API to Windows applications." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"A call-level interface (CLI) for interacting with databases. ODBC provides routines to allocate and deallocate resources, control connections to the database, execute SQL statements, obtain diagnostic information, control transaction termination, and obtain information about the implementation." (Craig S Mullins, "Database Administration: The Complete Guide to DBA Practices and Procedures" 2nd Ed., 2012)

"A standard database access technology developed by Microsoft, the purpose of which is to allow accessing any DBMS from any application, regardless of which DBMS is managing the data." (Craig S Mullins, "Database Administration: The Complete Guide to DBA Practices and Procedures" 2nd Ed., 2012)

09 August 2009

🛢DBMS: NoSQL (Definitions)

"An umbrella term for non-relational data stores, hence the name. These stores sacrifice ACID transactions for greater scalability and availability." (Dean Wampler, "Functional Programming for Java Developers", 2011)

"A set of technologies that created a broad array of database management systems that are distinct from relational database systems. One major difference is that SQL is not used as the primary query language. These database management systems are also designed for distributed data stores." (Marcia Kaufman et al, "Big Data For Dummies", 2013)

"A class of database management systems that consist of non-relational, distributed data stores. These systems are optimized for supporting the storage and retrieval requirements of massive-scale data-intensive applications." (IBM, "Informix Servers 12.1", 2014)

"A database that doesn’t adhere to relational database structures. Used to organize and query unstructured data." (Jason Williamson, "Getting a Big Data Job For Dummies", 2015)

"Any of a class of database management systems that reject the limitations and drawbacks dictated by, or associated with, the relational model. NoSQL products tend to specialize in a single or limited number of areas, such as high-performance processing, big data (giga-record systems), diverse data types (video, pictures, mathematical models), documents, and so on. Their specialized focus often requires deemphasizing other areas such as data consistency and backup and recovery." (George Tillmann, "Usage-Driven Database Design: From Logical Data Modeling through Physical Schmea Definition", 2017)

"In general, NoSQL databases provide a mechanism for storage and retrieval of data modeled in means other than the tabular relations used in relational databases." (Prashant Natarajan et al, "Demystifying Big Data and Machine Learning for Healthcare", 2017)

"NoSQL means 'not only SQL' or 'no SQL at all'. Being a new type of non-relational databases, NoSQL databases are developed for efficient and scalable management of big data." (Zongmin Ma & Li Yan, "Towards Massive RDF Storage in NoSQL Databases: A Survey", 2019)

"A broad term for a set of data access technologies that do not use the SQL language as their primary mechanism for reading and writing data. Some NoSQL technologies act as key-value stores, only accepting single-value reads and writes; some relax the restrictions of the ACID methodology; still others do not require a pre-planned schema." (MySQL, "MySQL 8.0 Reference Manual Glossary")

"A NoSQL database is distinguished mainly by what it is not - it is not a structured relational database format that links multiple separate tables. NoSQL stands for 'not only SQL', meaning that SQL, or structured query language is not needed to extract and organize information. NoSQL databases tend to be more diverse and flatter than relational databases (in a flat database, all data is contained in the same, large table)." (Statistics.com)

"NoSQL is a database management system built for the complexities of working with Big Data. Unlike SQL, NoSQL does not store data in a relational format." (Xplenty) [source]

"No-SQL (aka not only SQL) database systems are distributed, non-relational databases designed for large-scale data storage and for massively-parallel data processing across a large number of commodity servers." (IBM) 

"NoSQL is short for 'not only SQL'. NoSQL databases include mechanisms for storage and retrieval of data based on means other than the tabular relations used in relational databases." (Idera) [source]

"sometimes referred to as ‘Not only SQL’ as it is a database that doesn’t adhere to traditional relational database structures. It is more consistent and can achieve higher availability and horizontal scaling." (Analytics Insight)

23 July 2009

🛢DBMS: Session (Definition)

"In Oracle, a single connection of an authenticated Oracle user to a database for a period of time. A given user may have several sessions running at the same time. Sessions may be long (as when a developer connects to Oracle via SQL*Plus) or short (as when the Oracle web gateway produces a single web page)." (Bill Pribyl & Steven Feuerstein, "Learning Oracle PL/SQL", 2001)

"In English Query, a sequence of operations performed by the English Query engine. A session begins when a user logs on and ends when the user logs off. All operations during a session form one transaction scope and are subject to permissions determined by the logon username and password." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"A block of time during which a user interacts with a database." (Jan L Harrington, "SQL Clearly Explained" 3rd Ed., 2010)

"A period of time when a connection is active and communication can take place. For the purpose of data communication between functional units, session also refers to all the activities that take place during the establishment, maintenance, and release of the connection." (Microsoft, "SQL Server 2012 Glossary", 2012)

"A logical or virtual connection between two stations, software programs, or devices on a network that allows the two elements to communicate and exchange data for the duration of the session." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

17 July 2009

🛢DBMS: Checkpoint (Definitions)

"The point at which all data pages that have been changed are guaranteed to have been written to the database device." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

"Issued at intervals to run through the transaction log and verify that all committed transactions at that point are physically written back to the database." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"An event in which the database engine writes dirty buffer pages to disk. Dirty pages are pages that have been modified, but the modifications have not yet been written to disk. Each checkpoint writes to disk all pages that were dirty at the last checkpoint and still have not been written to disk. Checkpoints occur periodically based on the number of log records generated by data modifications, or when requested by a user or a system shutdown." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"This is an entry that SQL Server records in a transaction log when it copies transactions from the log to the datafile." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)

"An event in which the database engine writes pages that have been modified, but the modifications have not yet been written to disk. Checkpoints can occur periodically based on the number of log records generated by data modifications or when requested by a user or a system shutdown. A point that you can return to directly if the package should fail past that point. Complex packages will often have multiple checkpoints to reduce the amount of work that will need to be redone if a failure occurs." (Victor Isakov et al, "MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (70-444) Study Guide", 2007)

"An event in which the Database Engine writes dirty buffer pages to disk. Each checkpoint writes to disk all the pages that were dirty at the last checkpoint and still have not been written to disk." (Microsoft, "SQL Server 2012 Glossary", 2012)

"The process of storing a set of data or system state that is in a known consistent or safe state so that it can be later restored by a rollback operation if the system fails or the data is corrupted." (O Sami Saydjari, "Engineering Trustworthy Systems: Get Cybersecurity Design Right the First Time", 2018)

"A point at which the database manager records internal status information in the log; the recovery process uses this information if the subsystem abnormally terminates." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

🛢DBMS: One-to-One Relationship (Definitions)

"A single instance of one entity is associated with a single instance of another entity." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"A relationship between two tables in which a single row in the first table can be related only to one row in the second table, and a row in the second table can be related to only one row in the first table. This type of relationship is unusual." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"A relationship type between tables where one row in a given table is related to only one or zero rows in a second table. This relationship type is often used for subtyping. For example, an EMPLOYEE table may hold the information common to all employees, while the FULLTIME, PARTTIME, and CONTRACTOR tables hold information unique to full-time employees, part-time employees, and contractors, respectively. These entities would be considered subtypes of an EMPLOYEE and maintain a one-to-one relationship with the EMPLOYEE table." (Bob Bryla, "Oracle Database Foundations", 2004)

"Occurs when one row or thing of an entity is associated with only one row or thing of another. One-to-one relationships are uncommon in the real world." (Thomas Moore, "EXAM CRAM™ 2: Designing and Implementing Databases with SQL Server 2000 Enterprise Edition", 2005)

"The relationship between two tables dictated by having one record in each table, and not more than one record in either table, related back to the other table." (Gavin Powell, "Beginning Database Design", 2006)

"A relationship between two tables in which a single row in the first table can be related to only one row in the second table, and a row in the second table can be related to only one row in the first table." (S. Sumathi & S. Esakkirajan, "Fundamentals of Relational Database Management Systems", 2007)

"Used in a relational database to denote that a single row in the parent table can be related to only one row in the related child table and that a row in the child table can be related to only a single row in the referenced parent table." (Victor Isakov et al, "MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (70-444) Study Guide", 2007)

"Occurs when one record in a table corresponds to exactly one record in another table." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"One of three types of relationships (associations among two or more entities) that are used by data models. In a 1:1 relationship, one entity instance is associated with only one instance of the related entity." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"A link between two entities in which the cardinality of both sides of the relationship is one." (Craig S Mullins, "Database Administration", 2012)

"A relationship between two entities in a database such that each instance of an entity is related to no more than one instance of the other entity." (Jan L Harrington, "Relational Database Design and Implementation" 3rd Ed., 2009)

16 July 2009

🛢DBMS: Referential Integrity (Definitions)

"The rules governing data consistency, specifically the relationships among the primary keys and foreign keys of different tables. SQL Server addresses referential integrity with user-defined triggers." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

"When a table has relationships with other tables, they are linked on a field (or group of fields). Referential integrity ensures that the copy of the key field kept in one table matches the key field in the other." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"An integrity mechanism that ensures that vital data in a database, such as the unique identifier for a given piece of data, remains accurate and usable as the database changes. Referential integrity involves managing corresponding data values between tables when the foreign key of a table contains the same values as the primary key of another table." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"Mandatory condition in a data warehouse where all the keys in the fact tables are legitimate foreign keys relative to the dimension tables. In other words, all the fact key components are subsets of the primary keys found in the dimension tables at all times." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit 2nd Ed ", 2002)

"A state in which all foreign key values in a database are valid." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"A method employed by a relational database system that enforces one-to-many relationships between tables." (Bob Bryla, "Oracle Database Foundations", 2004)

"A feature of some database systems that ensures that any record stored in the database is supported by accurate primary and foreign keys." (Sharon Allen & Evan Terry, "Beginning Relational Data Modeling" 2nd Ed., 2005)

"The facility of a DBMS to ensure the validity of predefined relationships." (William H Inmon, "Building the Data Warehouse", 2005)

"A process (usually contained within a relational database model) of validation between related primary and foreign key field values. For example, a foreign key value cannot be added to a table unless the related primary key value exists in the parent table. Similarly, deleting a primary key value necessitates removing all records in subsidiary tables, containing that primary key value in foreign key fields. Additionally, it follows that preventing the deletion of a primary key record is not allowed if a foreign key exists elsewhere." (Gavin Powell, "Beginning Database Design", 2006)

"The assurance that a reference from one entity to another entity is valid. If entity A references entity B, entity B exists. If entity B is removed, all references to entity B must also be removed." (Pramod J Sadalage & Scott W Ambler, "Refactoring Databases: Evolutionary Database Design", 2006)

"Relational database integrity that dictates that all foreign key values in a child table must have a corresponding matching primary key value in the parent table." (Marilyn Miller-White et al, "MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance 70-444", 2007)

"The referential integrity imposes the constraint that if a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null." (S. Sumathi & S. Esakkirajan, "Fundamentals of Relational Database Management Systems", 2007)

"A set of rules, enforced by the database server, the user’s application, or both, that protects the quality and consistency of information stored in the database." (Robert D Schneider & Darril Gibson, "Microsoft SQL Server 2008 All-in-One Desk Reference For Dummies", 2008)

"Requires that relationships among tables be consistent. For example, foreign key constraints must be satisfied. You cannot accept a transaction until referential integrity is satisfied." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"A constraint on a relation that states that every non-null foreign key value must match an existing primary key value." (Jan L Harrington, "Relational Database Design and Implementation" 3rd Ed., 2009)

"A constraint in a SQL database that requires, for every foreign key instance that exists in a table, that the row (and thus the primary key instance) of the parent table associated with that foreign key instance must also exist in the database." (Toby J Teorey, ", Database Modeling and Design" 4th Ed, 2010)

"A constraint on a relation that states that every non-null foreign key value must reference an existing primary key value." (Jan L Harrington, "SQL Clearly Explained" 3rd Ed., 2010)

"In a relational database, the quality of a table that all its associations are with real instances of other tables." (David C Hay, "Data Model Patterns: A Metadata Map", 2010)

"Refers to two relational tables that are directly related. Referential integrity between related tables is established if non-null values in the foreign key field of the child table are primary key values in the parent table." (Paulraj Ponniah, "Data Warehousing Fundamentals for IT Professionals", 2010)

"A condition by which a dependent table’s foreign key must have either a null entry or a matching entry in the related table. Even though an attribute may not have a corresponding attribute, it is impossible to have an invalid entry." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed, 2011)

"In data management, constraints that govern the relationship of an occurrence of one entity to one or more occurrences of another entity. These constraints may be automatically enforced by the DBMS. For instance, every purchase order must have one and only one customer. If the relationship is represented using a foreign key, then the foreign key is said to reference a file or entity table where the identifier is from the same domain. Having referential integrity means that IF a value exists in the foreign key of the referencing file, then it must exist as a valid identifier in the referenced file or table." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"Through the specification of appropriate referential constraints, RI guarantees that an acceptable value is always in each foreign key column." (Craig S Mullins, "Database Administration", 2012)

"Refers to the accuracy and consistency of records, and the assurance that they are genuine and unaltered." (Robert F Smallwood, "Information Governance: Concepts, Strategies, and Best Practices", 2014)

"The process of relating data together in a disciplined manner" (Daniel Linstedt & W H Inmon, "Data Architecture: A Primer for the Data Scientist", 2014)

"A requirement that the data in related tables be matched, so that an entry in the 'many' side of the relationship (the foreign key) must have a corresponding entry in the “one” side of the relationship (the primary key)." (Faithe Wempen, "Computing Fundamentals: Introduction to Computers", 2015)

"Refers to the accuracy and consistency of records, and the assurance that they are genuine and unaltered." (Robert F Smallwood, "Information Governance for Healthcare Professionals", 2018)

"The state of a database in which all values of all foreign keys are valid. Maintaining referential integrity requires the enforcement of a referential constraint on all operations that change the data in a table where the referential constraints are defined." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

"A rule defined on a key in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value)." (Oracle, "Oracle Database Concepts")

"A state in which all foreign key values in a database are valid. For a foreign key to be valid, it must contain either the value NULL, or an existing key value from the primary or unique key columns referenced by the foreign key." (Microsoft Technet)

"The technique of maintaining data always in a consistent format, part of the ACID philosophy. In particular, data in different tables is kept consistent through the use of foreign key constraints, which can prevent changes from happening or automatically propagate those changes to all related tables. Related mechanisms include the unique constraint, which prevents duplicate values from being inserted by mistake, and the NOT NULL constraint, which prevents blank values from being inserted by mistake." (MySQL, "MySQL 8.0 Reference Manual Glossary")

🛢DBMS: Online Analytical Processing [OLAP] (Definitions)

"A technology that uses multidimensional structures to provide rapid access to data for analysis. The source data for OLAP is commonly stored in data warehouses in a relational database." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

[analytical processing:] "A general term that encompasses data warehousing and OLAP. Analytical processing produces information for management decisions. Contrasts with operational processing." (Microsoft Corporation, "Microsoft SQL Server 7.0 Data Warehouse Training Kit", 2000)

"The capability to view data in different ways, organizing the data by various dimensions to perform analysis, query, and reporting interactively." (Margaret Y Chu, "Blissful Data ", 2004)

[analytical processing:] "using the computer to produce an analysis for management decision, usually involving trend analysis, drill-down analysis, demographic analysis, profiling, and so forth." (William H Inmon, "Building the Data Warehouse", 2005)

"A database designed to support analysis for decision making in an organization." (Reed Jacobsen & Stacia Misner, "Microsoft SQL Server 2005 Analysis Services Step by Step", 2006)

"The ability for a user to 'drill down' on various data attributes in order to gain a more detailed view of the data. Such analysis enables a user to view different perspectives of the same data in order to facilitate decision making. OLAP is part of the broader category of business intelligence." (Jill Dyché & Evan Levy, "Customer Data Integration: Reaching a Single Version of the Truth", 2006)

"Tools that provide different ways of summarizing multidimensional data." (Glenn J Myatt, "Making Sense of Data: A Practical Guide to Exploratory Data Analysis and Data Mining", 2006)

"Process whereby raw data is stored in a multidimensional format so that it can be analyzed easily by decision-makers." (Sara Morganand & Tobias Thernstrom , "MCITP Self-Paced Training Kit : Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 - Exam 70-442", 2007)

"A data mining approach for performing multi-dimensional queries." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"A collection of common business analysis functions that are difficult to perform directly with SQL. Some of the specific functions that fall under the OLAP umbrella include time series comparisons, ranking, ratios, penetration, thresholds, and contribution to report or to the whole data population. Most business intelligence tools provide this type of functionality. The capabilities can be implemented in a variety of different data storage mechanisms." (Laura Reeves, "A Manager's Guide to Data Warehousing", 2009)

"A query service that overlays a data warehouse by creating and maintaining a set of summary views (automatic summary tables, or ASTs) to enable quick access to summary data." (Toby J Teorey, "Database Modeling and Design 4th Ed", 2010)

"An approach to database design that focuses on analytical activities such as viewing data in various aggregations, slicing and dicing data to meet different criteria, and grouping data." (Ken Withee, "Microsoft Business Intelligence For Dummies", 2010)

"An approach to quickly answer multidimensional analytical queries." (Martin Oberhofer et al, "The Art of Enterprise Information Architecture", 2010)

"Systems that contain read-only data that can be queried and analyzed much more efficiently than OLTP application databases." (Linda Volonino & Efraim Turban, "Information Technology for Management 8th Ed", 2011)

"A type of computer processing that provides analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data." (Craig S Mullins, "Database Administration", 2012)

"This technique for analyzing business data uses cubes, which are like multidimensional pivot tables in spreadsheets. OLAP tools can perform trend analysis and enable drilling down into data. They enable multidimensional analysis, such as analyzing by time, product, and geography." (Daniel Linstedt & W H Inmon, "Data Architecture: A Primer for the Data Scientist", 2014)

"OLAP is software for manipulating multidimensional data from a variety of sources that has been stored in a data warehouse. The software can create various views and representations of the data. OLAP software provides fast, consistent, interactive access to shared, multidimensional data." (Ciara Heavin & Daniel J Power, "Decision Support, Analytics, and Business Intelligence" 3rd Ed., 2017)

"The process of collecting data from one or many sources; transforming and analyzing the consolidated data quickly and interactively; and examining the results across different dimensions of the data by looking for patterns, trends, and exceptions within complex relationships of that data." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

15 July 2009

🛢DBMS: Online Transaction Processing [OLTP] (Definitions)

"A database management system representing the state of a particular business function at a specific point in time. An OLTP database is typically characterized by having large numbers of concurrent users actively adding and modifying data." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"A data processing system designed to record all of the business transactions of an organization as they occur. An OLTP system is characterized by many concurrent users actively adding and modifying data." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"Any software capability that applies transactional updates and inquiries interactively." (Margaret Y Chu, "Blissful Data", 2004)

"A relational database system used to manage the day-to-day operations of an organization." (Reed Jacobsen & Stacia Misner, "Microsoft SQL Server 2005 Analysis Services Step by Step", 2006)

"The operational processes for executing a business activity while the customer or end user waits for the execution to complete. One example of OLTP would be an automated teller transaction." (Evan Levy & Jill Dyché, "Customer Data Integration", 2006)

"A data-processing system designed to record all the business transactions of an organization as they occur. An OLTP system is characterized by many concurrent users actively adding and modifying data. Typically, OLTP systems perform large numbers of relatively small transactions." (Jim Joseph et al, "Microsoft® SQL Server™ 2008 Reporting Services Unleashed", 2009)

"Online transaction processing (OLTP) systems are the fundamental systems used to run the business. These are also called operational systems or operational applications. They are often used as sources of data for the data warehouse." (Laura Reeves, "A Manager's Guide to Data Warehousing", 2009)

"A data-processing system designed to record all the business transactions of an organization as they occur. An OLTP system is characterized by many concurrent users actively adding and modifying data. Typically, OLTP systems perform large numbers of relatively small transactions." (Jim Joseph, "Microsoft SQL Server 2008 Reporting Services Unleashed", 2009)

"An approach to database design that focuses on data transactions  in particular inserting, updating, and deleting data." (Ken Withee, "Microsoft Business Intelligence For Dummies", 2010)

"Class of systems that facilitate and manage transaction-oriented applications." (Martin Oberhofer et al, "The Art of Enterprise Information Architecture", 2010)

"A transaction processing system where transactions are executed as soon as they occur." (Linda Volonino & Efraim Turban, "Information Technology for Management 8th Ed", 2011)

"A type of computer processing in which the computer responds immediately to user requests. Each request is a transaction. The opposite of transaction processing is batch processing." (Craig S Mullins, "Database Administration", 2012)

"A type of interactive application in which requests that are submitted by users are processed as soon as they are received. Results are returned to the requester in a relatively short period of time." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

Online transaction processing (OLTP) is a mode of processing that is characterized by short transactions recording business events and that normally requires high availability and consistent, short response times. This category of applications requires that a request for service be answered within a predictable period that approaches 'real time'. Unlike traditional mainframe data processing, in which data is processed only at specific times, transaction processing puts terminals online, where they can update the database instantly to reflect changes as they occur. In other words, the data processing models the actual business in real time, and a transaction transforms this model from one business state to another. Tasks such as making reservations, scheduling and inventory control are especially complex; all the information must be current. (Gartner)

🛢DBMS: Many-to-Many Relationship (Definitions)

"Multiple instances of one entity are associated with one or more instances of another entity." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"A relationship between two tables in which rows in each table have multiple matching rows in the related table. Many-to-many relationships are maintained by using a third table called a junction table." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"A logical data relationship in which the value of one data element can exist in combination with many values of another data element, and vice versa." (Ralph Kimball & Margy Ross, "The Data Warehouse Toolkit" 2nd Ed., 2002)

"A relationship type between tables in a relational database where one row of a given table may be related to many rows of another table, and vice versa. Many-to-many relationships are often resolved with an intermediate associative table." (Bob Bryla, "Oracle Database Foundations", 2004)

"This type of relationship occurs when many rows or things in an entity (many instances of an entity) are associated with many rows or things in another entity. This type of relationship is not uncommon in the real world. SQL Server doesn't actually allow direct implementation of many-to-many relationships; nevertheless, you can do so by creating two one-to-many relationships to a new entity." (Thomas Moore, "EXAM CRAM™ 2: Designing and Implementing Databases with SQL Server 2000 Enterprise Edition", 2005)

"A relationship where one object of one type may correspond to many objects of another type and vice versa. For example, one COURSE may include many STUDENTs and one STUDENT may be enrolled in many COURSEs. Normally you implement this kind of relationship by using an intermediate table that has one-to-many relationships with the original tables." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"A relationship between two entities in a database such that each instance of the first entity can be related to many instances of the second and each instance of the second entity can be related to many instances of the first." (Jan L Harrington, "Relational Database Design and Implementation" 3rd Ed., 2009)

"A relationship where an occurrence of each entity class may be associated with one or more occurrences of the other entity class." (David C Hay, "Data Model Patterns: A Metadata Map", 2010)

"One of three types of relationships (associations among two or more entities) in which one occurrence of an entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"A link between two entities in which the cardinality of both sides of the relationship is multiple." (Craig S Mullins, "Database Administration: The Complete Guide to DBA Practices and Procedures", 2012)

"A relationship between two tables in which one row in one table can relate to many rows in another table." (Microsoft, "SQL Server 2012 Glossary", 2012)

13 July 2009

🛢DBMS: Restore (Definitions)

"To restore an entire database and transaction log, database file(s), or a transaction log from a backup." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"This is the process of bringing a database back to a stable condition after a disaster." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)

[point-in-time restore:] "Recovering only the transactions within a log backup committed before a specific point in time, instead of recovering the entire backup." (Victor Isakov et al, "MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (70-444) Study Guide", 2007)

"The process of reinstating archived information onto your database server." (Robert D. Schneider and Darril Gibson, "Microsoft SQL Server 2008 All-In-One Desk Reference For Dummies", 2008)

"A multi-phase process that copies all the data and log pages from a specified backup to a specified database (the data-copy phase) and rolls forward all the transactions that are logged in the backup (the redo phase). At this point, by default, a restore rolls back any incomplete transactions (the undo phase), which completes the recovery of the database and makes it available to users." (Microsoft, "SQL Server 2012 Glossary", 2012)

"To rebuild a damaged or corrupted database or table space from a backup image produced with the backup database utility." (Sybase, "Open Server Server-Library/C Reference Manual", 2019)

09 July 2009

🛢DBMS: Rollback (Definitions)

"A Transact-SQL statement used with a user-defined transaction (before a commit transaction has been received) that cancels the transaction and undoes any changes that were made to the database." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

"Rollback of a user-specified transaction to the last savepoint inside a transaction or to the beginning of a transaction." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"Terminates a transaction so that all resources updated within a transaction revert to the original state before the transaction started." (Atul Apte, "Java Connector Architecture: Building Custom Connectors and Adapters", 2002)

"The point in a transaction when all updates to any resources involved in the transaction are reversed." (Kim Haase et al, "The J2EE Tutorial", 2002)

"To remove the updates performed by one or more partially completed transactions. Rollbacks are required to restore the integrity of a database after an application, database, or system failure." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"Rolling back a transaction means you are undoing the actual transaction you are currently in." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)

"This command undoes any database changes not yet committed to the database using the COMMIT command." (Gavin Powell, "Beginning Database Design", 2006)

"A DBMS recovery technique that aborts active applications and attempts to reinstate the state of the database prior to initiating the applications active at the time the database failed." (S. Sumathi & S. Esakkirajan, "Fundamentals of Relational Database Management Systems", 2007)

"A process that reverts writes operations to ensure the consistency of all replica set members." (MongoDb, "Glossary", 2008)

"Undoes changes performed within a transaction before the transaction is committed." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"An operation that returns the database to a previous state. The transaction can be rolled back completely, canceling a pending transaction, or to a specified point. Rollbacks allow the database to be restored to a valid state if invalid operations are performed or after the database server fails." (John Goodson & Robert A Steward, "The Data Access Handbook", 2009)

"A SQL command that restores the database table contents to their original condition (the condition that existed after the last COMMIT statement)." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"To undo the database statements performed prior to a commit of the transaction." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"An operation that ends a current transaction and cancels all the recent changes to the database until the previous checkpoint/ commit point." (Adam Gordon, "Official (ISC)2 Guide to the CISSP CBK" 4th Ed., 2015)

"The process of restoring a set of data or process state to a previous consistent state saved through a checkpoint operation." (O Sami Saydjari, "Engineering Trustworthy Systems: Get Cybersecurity Design Right the First Time", 2018)

🛢DBMS: Fifth Normal Form (Definitions)

"A table is in 5NF if it is in 4NF and contains no related multi-valued dependencies." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"A table is in fifth normal form (5NF) if and only if there are no possible lossless decompositions into any subset of tables; in other words, if there is no possible lossless decomposition, then the table is in 5NF." (Toby J Teorey, ", Database Modeling and Design" 4th Ed., 2010)

"Specifies that every join dependency for the entity must be a consequence of its candidate keys." (Craig S Mullins, "Database Administration", 2012)

08 July 2009

🛢DBMS: Fourth Normal Form (Definitions)

"A relation is in fourth normal form if it is in BCNF and contains no multivalued dependencies." (S. Sumathi & S. Esakkirajan, "Fundamentals of Relational Database Management Systems", 2007)

"A table is in 4NF if it is in BCNF and contains no unrelated multi-valued dependencies." (Rod Stephens, "Beginning Database Design Solutions", 2008)

"A table is in fourth normal form (4NF) if and only if it is at least in BCNF and if whenever there exists a nontrivial multivalued dependency of the form X->>Y, then X must be a superkey in the table." (Toby J Teorey, ", Database Modeling and Design 4th Ed", 2010)

"In relational theory, the fourth of Dr. Codd’s constraints on a relational design: No column within a primary key may be completely dependent on another column within the same primary key." (David C Hay, "Data Model Patterns: A Metadata Map", 2010)

"A table is in 4NF when it is in 3NF and contains no multiple independent sets of multivalued dependencies." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management" 9th Ed., 2011)

"An entity is in fourth normal form (4NF) if and only if it is in 3NF and has no multiple sets of multivalued dependencies." (Craig S Mullins, "Database Administration", 2012)

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.