Databases Glossary

Question 1

ACID vs BASE

Answer 1

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties for reliable transaction processing, common in traditional SQL databases. BASE (Basically Available, Soft state, Eventually consistent) is a model for distributed systems that prioritizes availability over strict consistency, common in NoSQL.

Question 2

attribute (field)

Answer 2

A property or characteristic of an entity. In a relational table, this corresponds to a column.

Question 3

backup

Answer 3

A copy of data taken and stored elsewhere so that it may be used to restore the original after a data loss event.

Question 4

Candidate key

Answer 4

An attribute or a set of attributes that can uniquely identify a tuple in a relation. A relation can have multiple candidate keys.

Question 5

Cartesian product

Answer 5

A relational algebra operation that combines every tuple from one relation with every tuple from another relation.

Question 6

CRUD operations

Answer 6

The four basic functions of persistent storage: Create, Read, Update, and Delete.

Question 7

DBMS, rDBMS, oDBMS, gDBMS

Answer 7

DBMS: Database Management System. A software system for creating and managing databases. rDBMS: Relational DBMS. oDBMS: Object-oriented DBMS. gDBMS: Graph-oriented DBMS.

Question 8

discriminator

Answer 8

An attribute of a weak entity that, combined with the primary key of the owning entity, uniquely identifies a weak entity instance. Also known as a partial key.

Question 9

entity (E/R diagram)

Answer 9

A real-world object or concept with an independent existence that is to be represented in the database (e.g., a person, a car, a course).

Question 10

Equijoin

Answer 10

A type of join that combines tuples from two relations where the values of a common attribute are equal. It is a subset of the Cartesian product.

Question 11

foreign key

Answer 11

An attribute or set of attributes in a relation that refers to the primary key of another (or the same) relation, enforcing referential integrity.

Question 12

index

Answer 12

A data structure used to improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

Question 13

intersection

Answer 13

A relational algebra operation that returns all tuples that are present in both of two specified relations.

Question 14

multi-valued attribute

Answer 14

An attribute that can hold multiple values for a single entity instance (e.g., a ‘phone_numbers’ attribute for a person).

Question 15

natural, cross, equi, outer, inner, left, right joins

Answer 15

Types of join operations. Natural: joins on all common columns. Cross: Cartesian product. Equi: joins on equality. Inner: returns only matching rows. Outer (Left, Right, Full): returns matching rows plus non-matching rows from one or both tables.

Question 16

natural key

Answer 16

A candidate key that is formed of attributes that exist in the real world and have a logical relationship to the entity, such as a vehicle’s VIN or a person’s social security number.

Question 17

predicate

Answer 17

The conditional part of a query, typically in the WHERE clause, that specifies the criteria for selecting rows.

Question 18

Primary Key

Answer 18

A candidate key chosen by the database designer to uniquely identify tuples within a relation. It cannot contain null values.

Question 19

project, select, groupby

Answer 19

SQL/Relational Algebra operations. Select: retrieves rows (tuples). Project: retrieves columns (attributes). Group By: arranges identical data into groups.

Question 20

query

Answer 20

A request to a DBMS for retrieving, modifying, inserting, or deleting data.

Question 21

record

Answer 21

A collection of related data items treated as a single unit. Synonymous with a tuple or a row in a relational database.

Question 22

Referential Integrity

Answer 22

A property ensuring that a foreign key value must match an existing primary key value in the referenced table or be null.

Question 23

relational, relation (table)

Answer 23

A relation is a set of tuples (rows), representing data in a two-dimensional table with columns (attributes).

Question 24

relation - databases, weak relation

Answer 24

A relation that contains a weak entity. It cannot exist on its own and depends on another ‘strong’ relation.

Question 25

relation - maths, ternary relation

Answer 25

In mathematics, a set of ordered n-tuples. A ternary relation involves three sets.

Question 26

schema

Answer 26

The structure of a database, describing the entities, their attributes, and the relationships among them. It is the 'blueprint' of the database.

Question 27

semantic dependence

Answer 27

The meaning of an attribute's value is dependent on the value of another attribute.

Question 28

semi-structured data

Answer 28

Data that does not conform to a strict data model but has some organizational properties, like tags, that make it easier to analyze. Examples include JSON and XML.

Question 29

SQL

Answer 29

Structured Query Language. A standard language for managing and manipulating data in a relational database.

Question 30

sub-entity

Answer 30

A subgrouping of an entity type that has distinct attributes from other subgroupings. Also known as a subtype or subclass.

Question 31

superkey

Answer 31

Any set of attributes that, taken collectively, uniquely identifies a tuple within a relation. A candidate key is a minimal superkey.

Question 32

synthetic key

Answer 32

An artificial key created by the database designer when no suitable natural key exists. Often an auto-incrementing integer. Also known as a surrogate key.

Question 33

transaction

Answer 33

A single, logical unit of work consisting of one or more operations. Transactions must be atomic, consistent, isolated, and durable (ACID).

Question 34

tuple (record)

Answer 34

A single row in a relational table, representing a single instance of an entity.

Question 35

union

Answer 35

A relational algebra operation that combines the results of two queries and removes duplicate tuples.

Question 36

update

Answer 36

A CRUD operation that modifies existing data in a database.

Question 37

value

Answer 37

The actual data stored within an attribute (field) for a specific tuple (record).

Question 38

value atomicity

Answer 38

A principle of the relational model stating that the value stored at the intersection of each row and column must be indivisible or atomic.

Question 39

view

Answer 39

A virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table, but does not store the data itself.

Question 40

weak entity

Answer 40

An entity that cannot be uniquely identified by its own attributes and relies on the primary key of an associated 'strong' or 'owning' entity.

Question 41

weak entity and discriminator

Answer 41

A weak entity is identified by the combination of the primary key of its owning entity and its own discriminator (or partial key).

Question 42

big data

Answer 42

Data that is too big to fit in main memory/primary store/core.

Question 43

distributed system

Answer 43

A system whose components are located on different networked computers, which communicate and coordinate their actions by passing messages to one another.

Question 44

disk drive

Answer 44

A secondary storage device for recording, storing, and retrieving digital information using rotating platters coated with magnetic material.

Question 45

filesystem

Answer 45

The methods and data structures that an operating system uses to keep track of files on a disk or partition; that is, the way the files are organized on the disk.

Question 46

heap

Answer 46

An area of dynamically-allocated memory, contrasted with the stack. Used for data structures that can grow or shrink in size during program execution.

Question 47

primary store (main memory) vs secondary store

Answer 47

Primary store (e.g., RAM) is volatile, faster, and smaller, directly accessible by the CPU. Secondary store (e.g., disk drive, SSD) is non-volatile, slower, and larger.

Question 48

SSD

Answer 48

Solid-State Drive. A non-volatile storage device that uses integrated circuit assemblies to store data persistently, typically using flash memory.

Question 49

volatile

Answer 49

A type of memory that loses its content when the computer's power is turned off. For example, RAM.

Question 50

arity

Answer 50

The number of attributes in a relation or the number of arguments in a function or operation.

Question 51

one-to-many, many-to-one, one-to-one, many-to-many

Answer 51

Cardinality constraints describing the relationship between two entities. (e.g., One-to-many: one student can enroll in many courses).

Question 52

transitive, commutative, associative

Answer 52

Mathematical properties of operators. Associative: (a+b)+c = a+(b+c). Commutative: a+b = b+a. Transitive: If a=b and b=c, then a=c.

Question 53

JSON and or XML

Answer 53

JSON (JavaScript Object Notation) and XML (eXtensible Markup Language) are text-based formats for representing semi-structured data.

Question 54

key/value pair, key/value store

Answer 54

A data model where data is stored as a collection of key-value pairs. The key serves as a unique identifier. Common in NoSQL databases like Redis.

Question 55

path-oriented query

Answer 55

A query that navigates a hierarchical or graph-like data structure to retrieve elements. XPath for XML is a common example.

Question 56

identifier, unique identifier

Answer 56

An attribute or a set of attributes whose values uniquely identify an entity instance or a tuple in a relation.

Question 57

composition (of functions or relations)

Answer 57

An operation that combines two functions or relations by applying one to the result of the other.

Question 58

domain set and range set of a function

Answer 58

The domain is the set of all possible input values for a function, while the range is the set of all possible output values.

Question 59

three-valued logic (true/false/null)

Answer 59

A logic system used in SQL databases to handle comparisons involving NULL values, where the result can be true, false, or unknown (represented as NULL).

Question 60

regular expression

Answer 60

A sequence of characters that specifies a search pattern in text.

Question 61

graph (directed, node/vertice, edge/arc)

Answer 61

A data structure consisting of nodes (or vertices) that represent entities, and edges (or arcs) that represent connections or relationships between them. A directed graph has edges with direction.

Question 62

serialising (marshalling or pickling)

Answer 62

The process of converting a data structure or object into a format (like a string or byte stream) that can be stored or transmitted and reconstructed later.

Question 63

shredded

Answer 63

The process of decomposing semi-structured data, like XML documents, into a set of relational tables for storage in an RDBMS.

Question 64

sharded

Answer 64

A type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called data shards. It is a form of horizontal partitioning.

Question 65

OLAP vs OLTP

Answer 65

OLTP (Online Transaction Processing) handles a large number of short, atomic transactions (e.g., banking). OLAP (Online Analytical Processing) involves complex queries over large amounts of data for analysis and business intelligence.

Question 66

normal form

Answer 66

A property of a relational schema that indicates its quality and freedom from certain types of data redundancy and update anomalies. Common forms are 1NF, 2NF, 3NF, BCNF.

Question 67

fixed-point

Answer 67

In a process of iterative computation, a state that does not change with further iterations.

Question 68

data redundancy

Answer 68

The condition created within a database in which the same piece of data is held in two separate places, leading to potential inconsistencies.

Question 69

scalar reduction

Answer 69

An operation that processes a set of values to return a single summary value, such as SUM(), COUNT(), or AVG().