This glossary covers the database terms and terms used for all kinds of databases. It does not cover terms that apply to certain systems or databases.
The ACID model of database design confirms data integrity through atomicity , consistency , isolation, and robustness:
- Atomic Assistance:Every transaction in a database must follow the “all or not” rule, which means that if any part of the transaction fails, the entire operation will fail.
- Consistency: each database transaction must follow all the rules defined in the database; not all transactions that violate these rules are allowed.
- Isolation: Each database transaction occurs independently of any other transaction. For example, if several transactions are submitted at the same time, the database prevents interference between them.
- Resilience: Each database transaction is permanently in the event of any database failure using backups or other means.
A database attribute is a attribute of a database entity . Simply put, an attribute is a column in a database table that is known per se as an entity.
Databases use authentication to ensure that only authorized users can access the database or certain aspects of the database. For example, administrators can be allowed to enter or edit data, while regular employees can only view data. Authentication is done with usernames and passwords.
The BASE model has been developed as an alternative to the ACID model to meet the needs of noSQL databases where the data is not structured in the same way as required in relational databases. Its main principles are Basic Availability, Soft State and Eventual Consistency:
- Basic Availability: The database is up and running, sometimes copying data that is distributed across multiple servers is supported.
- Soft Status: In ACID’s rigorous fight against the continuity model, this principle confirms that data does not always have to be consistent and that a single database or developer is responsible for each consistency that takes effect.
- Possible consistency: with some undefined visions, the database arrives consistently.
A database constraint is a set of rules that defines valid data. There are several types of restrictions. The main limitations are:
- Different constraints: The field must contain a unique value in the table.
- CONTROL RESTRICTIONS: The field can only contain specific data types and even certain allowable values.
- DEFAULT constraints: the field contains a default value if it does not have an existing value; this excludes the NULL value.
- PRIMARY KEY Limitations: The primary key must be unique.
- EXTERNAL EFFECTS Restrictions: The foreign key must match the existing primary key in another table.
Database Management System (DBMS)
DBMS is software that manages all aspects of database operation, data storage and validation to ensure compliance with data integrity rules, and the provision of data entry and manipulation forms. The Relational Database Systems Management System (RDBMS) implements the relational model of tables and the relationships between them.
The unit is a database just a table. This is described using the Entity-Relationship Diagram, which is a type of graph that shows the relationships between tables in databases.
The functional dependency constraint helps ensure the validity of the data and exists when one attribute specifies another value, described as A -> B, meaning that the value of A determines the value of B or that B is functionally dependent on A. For example, in a university table containing all students records, there may be a functional relationship between the student ID and the student name, i.e. the value of the name is determined by the unique student ID.
An index is a data structure that helps speed up database queries for large data sets. Database developers create an index of specific columns in a table . The index holds the values in the column, but refers only to the rest of the data in the table, and can be searched efficiently and quickly.
The key is the database area, which aims to uniquely identify the record. Keys help enforce data integrity and avoid duplication. The main types of keys used in the database are candidate keys, primary keys are foreign keys.
- Candidate Keys: A set of columns that can identify any record each time and from which a primary key has been selected.
- Master Keys: The key selected to uniquely identify a table record. This key cannot be NULL.
- Foreignkeys: A key that connects a record to another table record. The foreign key of a table must be the primary key of another table.
To normalize a database, design your tables (relationships) and columns (attributes) in a way that ensures data integrity and avoids duplication. The primary level of normality is the first normal form (1NF), the second normal form (2NF), the third normal form (3NF), and the Boyce-Codd normal form (BCNF).
NoSQL is a database model designed to store unstructured data such as emails, social media posts, video, or images. Instead of providing SQL and a strict ACID model to ensure data integrity, NoSQL follows a less stringent BASE model. The NoSQL database schema does not use tables to store data; rather, it may use key / value designs or graphs.
The value NULL is often confused with the temporary meaning “none” or zero; but it actually means “unknown.” If the value of the field is NULL, it is a placeholder with an unknown value. The Structured Query Language (SQL) uses the IS NULL and IS NOT NULL operators to test null values.
A database query is how users interact with a database. It is usually written in SQL and can be either a selected query or an action query. The selected query requires data from the database; the action request modifies, updates, or adds data. Some databases provide forms that hide the semantics of the query, allowing users to easily obtain information without understanding SQL.
A database schema is the design of the tables, columns, relationships, and constraints that make up a database . Schemas are usually described using an SQL CREATE extract.
A stored procedure is a pre-compiled query or SQL statement that can be shared between multiple programs and users in multiple database management systems. Stored procedures improve efficiency, help ensure data integrity, and increase productivity.
Structured query language
Structured query language, or SQL, is the most commonly used language for accessing database data. The Data Manipulation Language (DML) contains a subset of the most commonly used SQL statements and includes SELECT, INSERT, UPDATE, and DELETE.
A trigger is a stored procedure that is set to occur for a specific event, usually to change table data. For example, the launch button may work for logging, collecting statistics, or calculating a value.
A database view is a filtered set of data displayed to the end user to hide the complexity of the data and enhance the user experience. A view can combine data from two or more tables and contain a subset of information.