Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Data Management Foundations, D426 > Terms > Flashcards

Terms Flashcards

1
Q

Data

A

Data is numeric, textual, visual, or audio information that describes real-world systems.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

analog

A

Historically, data was mostly analog, encoded as continuous variations on various physical media.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

digital

A

Today, data is mostly digital, encoded as zeros and ones on electronic and magnetic media.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

database

A

A database is a collection of data in a structured format. In principle, databases can be stored on paper or even clay tablets. In practice, however, modern databases are invariably stored on computers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

database system / database management system / DBMS

A

A database system, also known as a database management system or DBMS, is software that reads and writes data in a database. Database systems ensure data is secure, internally consistent, and available at all times. These functions are challenging for large databases with many users, so database systems are complex.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

query language

A

A query language is a specialized programming language,
designed specifically for database systems.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

database application

A

A database application is software that helps business users interact with database systems.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

database administrator

A

A database administrator is responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

database designer

A

A database designer determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. Since these priorities often conflict, database design is technically challenging.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

database programmer

A

A database programmer develops computer programs that utilize a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

database user

A

A database user is a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

transaction

A

A transaction is a group of queries that must be either completed or rejected as a whole. Execution of some, but not all, queries results in inconsistent or incorrect data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

architecture

A

The architecture of a database system describes the internal
components and the relationships between components.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

query processor

A

The query processor interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

query optimization

A

The query processor performs query optimization to ensure the most efficient instructions are executed on the data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

storage manager

A

The storage manager translates the query processor instructions into low-level file-system commands that modify or retrieve data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

indexes

A

The storage manager uses indexes to quickly locate data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

transaction manager

A

The transaction manager ensures transactions are properly
executed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

log

A

The log is a file containing a complete record of all inserts, updates, and deletes processed by the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

catalog / data dictionary

A

The catalog, also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

relational database

A

A relational database stores data in tables, columns, and rows,
similar to a spreadsheet.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

SQL

A

SQL stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

big data

A

The growth of the internet in the 1990s generated massive volumes of online data, called big data, often with poorly structured or missing information.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

NoSQL

A

The newer non-relational systems are called NoSQL, for ‘not only SQL’, and are optimized for big data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Open source

A

Open source software is software that anyone can inspect, copy, and modify with no licensing fee.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

query

A

A query is a command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

query language

A

A query language is a computer programming language for writing database queries.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

CRUD

A

The four common queries are sometimes referred to as CRUD operations, an acronym for Create, Read, Update, and Delete data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Structured Query Language / SQL

A

Structured Query Language, or SQL, is the standard query language of relational database systems.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

statement

A

An SQL statement is a database command, such as a query that inserts, selects, updates, or deletes data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

INSERT

A

INSERT inserts rows into a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

SELECT

A

SELECT retrieves data from a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

UPDATE

A

UPDATE modifies data in a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

DELETE

A

DELETE deletes rows from a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

CREATE TABLE

A

The SQL CREATE TABLE statement creates a new table by
specifying the table and column names.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

data type

A

Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Database design

A

A database design is a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

analysis

A

The analysis phase specifies database requirements without regard to a specific database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

ER diagrams

A

Entities, relationships, and attributes are depicted in ER diagrams

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

logical design

A

The logical design phase implements database requirements in a specific database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

key

A

A key is a column used to identify individual rows of a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

table diagram

A

The logical design is depicted in a table diagram.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

schema

A

The logical design, as specified in SQL and depicted in a table diagram, is called a database schema.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

physical design

A

The physical design phase adds indexes and specifies how tables are organized on storage media.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

data independence

A

The principle that physical design never affects query results is
called data independence.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

application programming interface / API

A

An application programming interface, or API, is a library of procedures or classes that links a host programming language to a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

MySQL

A

MySQL is a leading relational database system sponsored by Oracle.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

root account

A

The root account, the administrative account that has full control of MySQL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

MySQL Command- Line Client

A

MySQL Command-Line Client is a text interface included in the MySQL Server download. The Command-Line Client allows developers to connect to the database server, perform administrative functions, and execute SQL statements.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

error code

A

MySQL Server returns an error code and description when an SQL statement is syntactically incorrect or the database cannot execute the statement.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

MySQL Workbench

A

MySQL Workbench is installed with MySQL Server and allows
developers to execute SQL commands using an editor.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

database model

A

A database model is a conceptual framework for database
systems, with three parts:

Data structures that prescribe how data is organized.
Operations that manipulate data structures.
Rules that govern valid data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

relational model

A

The relational model is a database model based on a tabular data structure. The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. The data structure, operations, and rules are standardized in SQL, the universal query language of relational databases.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

big data

A

The rise of the internet in the 1990s generated big data, characterized by unprecedented data volumes and rapidly changing data structures

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

set

A

A set is an unordered collection of elements enclosed in braces.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

tuple

A

A tuple is an ordered collection of elements enclosed in parentheses.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

table

A

A table has a name, a fixed tuple of columns, and a varying set of rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

column

A

A column has a name and a data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

row

A

A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the column’s data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

data type

A

A data type is a named set of values, from which column values are drawn.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

relational algebra

A

These operations are collectively called relational algebra and are the theoretical foundation of the SQL language.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

Relational rules

A

Relational rules are part of the relational model and govern data in every relational database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

Business rules

A

Business rules are based on business policy and specific to a
particular database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

constraints

A

Relational rules are implemented as SQL constraints and enforced by the database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

statement

A

An SQL statement is a complete command composed of one or more clauses.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

clause

A

A clause groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

Data Defnition Language

A

Data Definition Language (DDL) defines the structure of the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

Data Query Language (DQL)

A

retrieves data from the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

Data Manipulation Language

A

Data Manipulation Language (DML) manipulates data stored in a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

Data Control Language

A

Data Control Language (DCL) controls database user access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

Data Transaction Language

A

Data Transaction Language (DTL) manages database transactions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

database system instance

A

A database system instance is a single executing copy of a database system. Personal computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

CREATE DATABASE
DatabaseName

A

CREATE DATABASE DatabaseName creates a new database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

DROP DATABASE
DatabaseName

A

DROP DATABASE DatabaseName deletes a database, including all tables in the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

USE DatabaseName

A

USE DatabaseName selects a default database for use in subsequent SQL statements.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

SHOW DATABASES

A

SHOW DATABASES lists all databases in the database system
instance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

SHOW TABLES

A

SHOW TABLES lists all tables in the default database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

SHOW COLUMNS
FROM TableName

A

SHOW COLUMNS FROM TableName lists all columns in the TableName table of the default database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

SHOW CREATE TABLE
TableName

A

SHOW CREATE TABLE TableName shows the CREATE TABLE statement for the TableName table of the default database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

table

A

A table has a name, a fixed sequence of columns, and a varying set of rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

column

A

A column has a name and a data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

row

A

A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column’s data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

cell

A

A cell is a single column of a single row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
84
Q

empty table

A

A table without rows is called an empty table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

data independence

A

Rule 4 is called data independence.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
86
Q

CREATE TABLE

A

The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
87
Q

DROP TABLE

A

The DROP TABLE statement deletes a table, along with all the
table’s rows, from a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
88
Q

ALTER TABLE

A

The ALTER TABLE statement adds, deletes, or modifies columns on an existing table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
89
Q

data type

A

A data type is a named set of values from which column values are drawn.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
90
Q

Integer

A

Integer data types represent positive and negative integers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
91
Q

Decimal

A

Decimal data types represent numbers with fractional values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
92
Q

Character

A

Character data types represent textual characters.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
93
Q

Date and time

A

Date and time data types represent date, time, or both. Some date and time data types include a time zone or specify a time interval.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
94
Q

Binary

A

Binary data types store data exactly as the data appears in memory or computer files, bit for bit.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
95
Q

Spatial data type

A

Spatial data types store geometric information, such as lines, polygons, and map coordinates

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
96
Q

Document

A

Document data types contain textual data in a structured format such as XML or JSON.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
97
Q

signed

A

A signed number may be negative.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
98
Q

unsigned

A

An unsigned number cannot be negative.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
99
Q

operator / operands

A

Arithmetic operators compute numeric values from numeric operands.
Comparison operators compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types.
Logical operators compute logical values from logical operands.
.
unary A unary operator has one operand.

binary A binary operator has two operands.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
100
Q

expression

A

An expression is a string of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. The value of an expression may be any data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
101
Q

operator precedence

A

Operators in an expression are evaluated in the order of operator precedence, shown in the table below. Operators of the same precedence are evaluated from left to right. Regardless of operator precedence, expressions enclosed in parentheses are evaluated before any operators outside the parentheses are applied.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
102
Q

SELECT / FROM

A

The SELECT statement selects rows from a table. The statement has a SELECT clause and a FROM clause. The FROM clause specifies the table from which rows are selected. The SELECT clause specifies one or more expressions, separated by commas, that determine what values are returned for each row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
103
Q

result table

A

The SELECT statement returns a set of rows, called the result
table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
104
Q

LIMIT

A

MySQL has a LIMIT clause that limits the number of rows returned by a SELECT statement.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
105
Q

condition

A

A condition is an expression that evaluates to a logical value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
106
Q

WHERE

A

A SELECT statement has an optional WHERE clause that specifies a condition for selecting rows. A row is selected when the condition is TRUE for the row values. A row is omitted when the condition is either FALSE or NULL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
107
Q

NULL

A

NULL is a special value that represents either unknown or inapplicable data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
108
Q

NOT NULL

A

The NOT NULL constraint prevents a column from having a NULL value. Statements that insert NULL, or update a value to NULL, are automatically rejected. NOT NULL follows the column name and data type in a CREATE TABLE statement.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
109
Q

IS NULL / IS NOT NULL

A

Instead, the IS NULL and IS NOT NULL operators must be used to select NULL values. Value IS NULL returns TRUE when the value is NULL. Value IS NOT NULL returns TRUE when the value is not NULL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
110
Q

truth tables

A

The value of logical expressions containing NULL operands is
defined in truth tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
111
Q

INSERT

A

The INSERT statement adds rows to a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
112
Q

INSERT INTO

A

The INSERT INTO clause names the table and columns where data is to be added. The keyword INTO is optional.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
113
Q

VALUES

A

The VALUES clause specifies the column values to be added

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
114
Q

DEFAULT

A

The optional DEFAULT keyword and default value follow the column name and data type in a CREATE TABLE statement. The column is assigned the default value, rather than NULL, when omitted from an INSERT statement

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
115
Q

UPDATE

A

The UPDATE statement modifies existing rows in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
116
Q

SET

A

The UPDATE statement uses the SET clause to specify the new column values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
117
Q

DELETE

A

The DELETE statement deletes existing rows in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
118
Q

FROM

A

The FROM keyword is followed by the table name whose rows are to be deleted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
119
Q

TRUNCATE

A

TRUNCATE statement deletes all rows from a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
120
Q

primary key

A

A primary key is a column, or group of columns, used to identify a row.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
121
Q

simple primary key

A

A simple primary key consists of a single column.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
122
Q

composite primary key

A

A composite primary key consists of multiple columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
123
Q

Minimal

A

All primary key columns are necessary for uniqueness. When any column is removed, the resulting simple or composite column is no longer unique.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
124
Q

PRIMARY KEY

A

The PRIMARY KEY constraint in a CREATE TABLE statement
names the table’s primary key. The PRIMARY KEY constraint ensures that a column or group of columns is always unique and non-null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
125
Q

auto-increment column

A

An auto-increment column is a numeric column that is assigned an automatically incrementing value when a new row is inserted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
126
Q

AUTO_INCREMENT

A

The AUTO_INCREMENT keyword defines an auto-increment
column. AUTO_INCREMENT follows the column’s data type in a CREATE TABLE statement.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
127
Q

foreign key

A

A foreign key is a column, or group of columns, that refer to a
primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
128
Q

Referential integrity

A

Referential integrity requires foreign key values must either be
NULL or match some value of the referenced primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
129
Q

FOREIGN KEY / REFERENCES

A

A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords.

130
Q

fully NULL

A

A fully NULL foreign key is a simple or composite foreign key in
which all columns are NULL.

131
Q

RESTRICT

A

RESTRICT rejects an insert, update, or delete that violates

132
Q

SET NULL

A

SET NULL sets invalid foreign keys to NULL.

133
Q

SET DEFAULT

A

SET DEFAULT sets invalid foreign keys to the foreign key default value.

134
Q

CASCADE

A

CASCADE propagates primary key changes to foreign keys.

135
Q

ON UPDATE / ON DELETE

A

Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint. ON UPDATE and ON DELETE are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE.

136
Q

constraint

A

constraint is a rule that governs allowable values in a database.
Constraints are based on relational and business rules, and implemented with special keywords in a CREATE TABLE statement. The database automatically rejects insert, update, and delete statements that violate a constraint.

137
Q

column constraint

A

A column constraint appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single column.

138
Q

table constraint

A

A table constraint appears in a separate clause of a CREATE
TABLE statement and governs values in one or more columns.

139
Q

UNIQUE

A

The UNIQUE constraint ensures that values in a column, or group of columns, are unique.

140
Q

CHECK

A

The CHECK constraint specifies an expression on one or more columns of a table. The constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL.

141
Q

CONSTRAINT

A

Table constraints may be named using the optional CONSTRAINT keyword, followed by the constraint name and declaration.

142
Q

IN

A

The IN operator is used in a WHERE clause to determine if a value matches one of several values

143
Q

BETWEEN

A

The BETWEEN operator provides an alternative way to determine if a value is between two other values. The operator is written
value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue.

144
Q

LIKE

A

The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

145
Q

BINARY

A

The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword.

146
Q

DISTINCT

A

The DISTINCT clause is used with a SELECT statement to return only unique or ‘distinct’ values.

147
Q

ORDER BY

A

The ORDER BY clause orders selected rows by one or more
columns in ascending (alphabetic or increasing) order.

148
Q

DESC

A

The DESC keyword with the ORDER BY clause orders rows in descending order.

149
Q

function / argument

A

A function operates on an expression enclosed in parentheses,
called an argument, and returns a value. Usually, the argument is a simple expression, such as a column name or fixed value. Some functions have several arguments, separated by commas, and a few have no arguments at all.

150
Q

aggregate function

A

An aggregate function processes values from a set of rows and returns a summary value.

151
Q

COUNT()

A

COUNT() counts the number of rows in the set

152
Q

MIN()

A

finds the minimum value in the set.

153
Q

MAX()

A

MAX() finds the maximum value in the set.

154
Q

SUM()

A

SUM() sums all the values in the set.

155
Q

AVG()

A

AVG() computes the arithmetic mean of all the values in the set.

156
Q

GROUP BY

A

The GROUP BY clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group.

157
Q

HAVING

A

The HAVING clause is used with the GROUP BY clause to filter group results.

158
Q

join / left table / right table

A

A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result

159
Q

AS

A

To simplify queries or result tables, a column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword.

160
Q

join clause

A

A join clause determines how a join query handles unmatched
rows.

161
Q

INNER JOIN

A

INNER JOIN selects only matching left and right table rows

162
Q

FULL JOIN

A

FULL JOIN selects all left and right table rows, regardless of
match.

163
Q

ON

A

The ON clause specifies the join columns

164
Q

LEFT JOIN

A

LEFT JOIN selects all left table rows, but only matching right table rows.

165
Q

RIGHT JOIN

A

RIGHT JOIN selects all right table rows, but only matching left
table rows.

166
Q

outer join

A

An outer join is any join that selects unmatched rows, including
left, right, and full joins

167
Q

UNION

A

The UNION keyword combines the two results into one table

168
Q

equijoin

A

An equijoin compares columns of two tables with the = operator.

169
Q

non-equijoin

A

A non-equijoin compares columns with an operator other than =, such as < and >.

170
Q

self-join

A

A self-join joins a table to itself

171
Q

cross-join

A

A cross-join combines two tables without comparing columns

172
Q

CROSS JOIN

A

A cross-join uses a CROSS JOIN clause without an ON clause

173
Q

subquery / nested query / inner query

A

A subquery, sometimes called a nested query or inner query, is a query within another SQL query.

174
Q

correlated

A

A subquery is correlated when the subquery’s WHERE clause
references a column from the outer query

175
Q

alias

A

An alias is a temporary name assigned to a column or table.

176
Q

AS

A

The AS keyword follows a column or table name to create an alias

177
Q

EXISTS

A

Correlated subqueries commonly use the EXISTS operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.

178
Q

NOT EXISTS

A

The NOT EXISTS operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected

179
Q

flattening

A

Replacing a subquery with an equivalent join is called flattening a query.

180
Q

view table / view query

A

A view table is a table name associated with a SELECT statement, called the view query.

181
Q

CREATE VIEW

A

The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table.

182
Q

base table

A

A table specified in the view query’s FROM clause is called a base table.

183
Q

materialized view

A

A materialized view is a view for which data is stored at all times.

184
Q

WITH CHECK OPTION

A

When WITH CHECK OPTION is specified, the database rejects
inserts and updates that do not satisfy the view query WHERE clause.

185
Q

relational algebra

A

In his original paper on the relational model, E. F. Codd introduced formal operations for manipulating tables. Codd’s operations, called relational algebra, have since been refined and are the theoretical foundation of SQL.

186
Q

select operation

A

The select operation selects table rows based on a logical
expression. The select operation is written as and is equivalent to
SELECT * FROM Table WHERE expression.

187
Q

project operation

A

The project operation selects table columns. The project operation is written as and is equivalent to
SELECT Column1, Column2, … FROM Table

188
Q

product operation

A

The product operation combines two tables into one result. The result includes all columns and all combinations of rows from both tables. The product operation is written as
and is equivalent to

SELECT * FROM Table1 CROSS JOIN Table2.

189
Q

join

A

The join operation, denoted with a “bowtie” symbol, is written as and is identical to a product followed by a select:

The join operation is equivalent to
SELECT * FROM Table1 INNER JOIN Table2 ON expressi
theta join Because of theta notation, the join operation is sometimes called a theta join.

190
Q

Compatible tables

A

Compatible tables have the same number of columns with the same data types. Column names may be different.

191
Q

union

A

The union operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table. The union operation is written as and is equivalent to

SELECT * FROM Table1 UNION SELECT * FROM Table2.

192
Q

Intersect

A

Intersect operates on two compatible tables and returns only rows th appear in both tables. The intersect operation is written as and is equivalent to

SELECT * FROM Table1 INTERSECT SELECT * FROM Table

193
Q

difference

A

The difference operation removes from a table all rows that
appear in a second compatible table. The difference operation is written as and is equivalent to

SELECT * FROM Table1 MINUS SELECT * FROM Table2.

194
Q

rename operation

A

The rename operation specifies new table and column names.

195
Q

aggregate operation

A

The aggregate operation applies aggregate functions like SUM(), AVG aggregate operation is written as and is equivalent to

SELECT GroupColumn, Function(Column) FROM Table GR

196
Q

query optimizer / query execution plan

A

A query optimizer converts an SQL query into a sequence of low- level database actions, called the query execution plan. The query execution plan specifies precisely how to process an SQL statement.

197
Q

cost

A

The cost of an operation is a numeric estimate of processing time.
The cost estimate usually combines both storage media access and computation time in a single measure.

197
Q

entity-relationship model

A

An entity-relationship model is a high-level representation of data requirements, ignoring implementation details.

198
Q

entity

A

An entity is a person, place, product, concept, or activity. relationship

199
Q

entity-relationship diagram / ER diagram

A

An entity-relationship diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes.

200
Q

glossary / data dictionary / repository

A

A glossary, also known as a data dictionary or repository, documents additional detail in text format.

201
Q

entity type

A

An entity type is a set of things. Ex: All employees in a company.

202
Q
A
203
Q

relationship type

A

A relationship type is a set of related things. Ex: Employee-
Manages-Department is a set of (employee, department) pairs, where the employee manages the department.

204
Q

attribute type

A

An attribute type is a set of values. Ex: All employee salaries.

205
Q

entity instance

A

An entity instance is an individual thing. Ex: The employee Sam
Snead.

206
Q

relationship instance

A

A relationship instance is a statement about entity instances. Ex: “Maria Rodriguez manages Sales.” .

207
Q

attribute instance

A

An attribute instance is an individual value. Ex: The salary $35,000.

208
Q

Analysis

A

Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details.

209
Q

Logical design

A

Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system.

210
Q

Physical design

A

Physical design adds indexes and specifies how tables are
organized on storage media.

211
Q

Cardinality

A

cardinality In entity-relationship modeling, cardinality refers to maxima and minima of relationships and attributes.

212
Q

Relationship maximum

A

is the greatest number of instances of one entity that can relate to a single instance of another entity.

213
Q

singular / plural

A

A related entity is singular when the maximum is one and plural when the maximum is many.

214
Q

Relationship minimum

A

Relationship minimum is the least number of instances of one entity that can relate to a single instance of another entity.

215
Q

optional / required

A

A related entity is optional when the minimum is zero and required when the minimum is one

216
Q

Attribute maximum

A

Attribute maximum is the greatest number of attribute values that can describe each entity instance. Attribute maximum is usually specified as one (singular) or many (plural).

217
Q

Attribute minimum

A

Attribute minimum is the least number of attribute values that can describe each entity instance. Attribute minimum is usually specified as zero (optional) or one (required).

218
Q

unique attribute

A

Each value of a unique attribute describes at most one entity
instance.

219
Q

identifying attribute

A

An identifying attribute is unique, singular, and required.

220
Q

identify

A

Identifying attribute values correspond one-to-one to, or identify, entity instances.

221
Q

strong entity

A

A strong entity has one or more identifying attributes

222
Q

weak entity / identifying relationship
/ identifying entity

A

weak entity does not have an identifying attribute. Instead, a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. Cardinality of the identifying entity is 1(1).

223
Q

subtype entity / supertype entity

A

A subtype entity is a subset of another entity type, called the supertype entity.

224
Q

IsA relationship

A

A supertype entity identifies its subtype entities. The identifying relationship is called an IsA relationship.

225
Q

Similar entities

A

Similar entities are entities that have many common attributes and relationships.

226
Q

partition

A

A partition of a supertype entity is a group of mutually exclusive subtype entities.

227
Q

partition attribute

A

Each partition corresponds to an optional partition attribute of the supertype entity

228
Q

crow’s foot notation

A

Variations in cardinality conventions are common. One popular convention, called crow’s foot notation, depicts cardinality as a circle (zero), a short line (one), or three short lines (many). The three short lines look like a bird’s foot, hence the name “crow’s foot notation”.

229
Q

subject area

A

Decompose a complex model into a group of related entities, called a subject area.

230
Q

independent / dependent

A

Refer to strong entities as independent and weak entities as dependent

231
Q

Unifed Modeling Language / UML

A

Unified Modeling Language, or UML, is commonly used for software development. Software data structures are similar to database structures, so UML includes ER conventions.

232
Q

IDEF1X

A

IDEF1X stands for Information DEFinition version 1X. IDEF1X became popular, in part, due to early adoption by the United States Department of Defense.

233
Q

Chen notation

A

Chen notation appeared in an early ER modeling paper by Peter Chen. Chen notation is not standardized but often appears in literature and tools.

234
Q

strong table

A

A strong entity becomes a strong table. The primary key must be unique and non-NULL, and should be stable, simple, and meaningless. Single-column primary keys are best, but if no such column exists, a composite primary key may have the required properties.

235
Q

artifcial key

A

An artificial key is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.

236
Q

subtype table

A

A subtype entity becomes a subtype table .

237
Q

weak table

A

A weak entity becomes a weak table

238
Q

First, second, and third normal form

A

depends on Column A depends on column B means each B value is related to at most one A value.

239
Q

functional dependence

A

Dependence of one column on another is called functional dependence.

240
Q

Multivalued dependence / join dependence

A

Multivalued dependence and join dependence entail dependencies between three or more columns.

241
Q

Redundancy

A

Redundancy is the repetition of related values in a table.

242
Q

Normal forms

A

Normal forms are rules for designing tables with less redundancy

243
Q

first normal form

A

Every cell of a table contains exactly one value. A table is in first normal form when, in addition, the table has a primary key.

244
Q

second normal form

A

A table is in second normal form when all non-key columns depend on the whole primary key.

245
Q

third normal form

A

Informally, a table is in third normal form when all non-key
columns depend on the key, the whole key, and nothing but the key. A formal definition appears elsewhere in this material.

246
Q

Boyce-Codd normal form candidate key / Minimal

A

A candidate key is a simple or composite column that is unique and minimal. Minimal means all columns are necessary for uniqueness.

247
Q

non-key

A

A non-key column is a column that is not contained in a candidate key.

248
Q

third normal form

A

A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique. Columns A and B may be simple or composite

249
Q

Boyce-Codd normal form

A

A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite.

250
Q

Normalization

A

Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form.

251
Q

depends on

A

Column A depends on column B when each B value is related to at most one A value. A and B may be simple or composite columns.

252
Q

Boyce-Codd normal form

A

In a Boyce-Codd normal form table, if column A depends on column B, then B must be unique.

253
Q

Denormalization

A

Denormalization means intentionally introducing redundancy by merging tables.

254
Q

Access time

A

Access time is the time required to access the first byte in a read or write operation.

255
Q

Transfer rate

A

Transfer rate is the speed at which data is read or written, following initial access

256
Q

Volatile memory

A

Volatile memory is memory that is lost when disconnected from power.

257
Q

Non-volatile memory

A

Non-volatile memory is retained without power

258
Q

Main memory / random-access memory (RAM)

A

Main memory, also called random-access memory (RAM), is the primary memory used when computer programs execute.

259
Q

Flash memory / solid- state drive (SSD)

A

Flash memory, also called solid-state drive (SSD), is less expensive and higher capacity than main memory.

260
Q

Magnetic disk / hard- disk drive (HDD)

A

Magnetic disk, also called hard-disk drive (HDD), is used to store large amounts of data.

261
Q

sectors

A

Magnetic disk groups data in sectors, traditionally 512 bytes per sector but 4 kilobytes with newer disk formats.

262
Q

pages

A

Flash memory groups data in pages, usually between 2 kilobytes and 16 kilobytes per page.

263
Q

block

A

Databases and file systems use a uniform size, called a block, when transferring data between main memory and storage media.

264
Q

row-oriented storage

A

To minimize block transfers, relational databases usually store an entire row within one block, which is called row-oriented storage.

265
Q

column-oriented / columnar storage

A

In column-oriented storage, also called columnar storage, each block stores values for a single column only.

266
Q

table structure

A

A table structure is a scheme for organizing rows in blocks on storage media.

267
Q

heap table

A

In a heap table, no order is imposed on rows.

268
Q

sorted table / sort column

A

In a sorted table, the database designer identifies a sort column that determines physical row order.

269
Q

hash table

A

In a hash table, rows are assigned to buckets.

270
Q

bucket

A

A bucket is a block or group of blocks containing rows.

271
Q

hash key

A

A hash key is an identifier generated by a hash function to determine the storage location of data in a database. It ensures efficient and uniform data distribution across storage units, optimizing retrieval and load balancing.

The hash key is a column or group of columns, usually the primary key.

272
Q

hash function

A

The hash function computes the bucket containing the row from the hash key.

273
Q

modulo function

A

The modulo function is a simple hash function with four steps.

274
Q

dynamic hash function

A

A dynamic hash function automatically allocates more blocks to the table, creates additional buckets, and distributes rows across all buckets. With more buckets, fewer rows are assigned to each bucket and, on average, buckets contain fewer linked blocks.

275
Q

Table clusters / multi- tables

A

Table clusters, also called multi-tables, interleave rows of two or more tables in the same storage area.

276
Q

cluster key

A

Table clusters have a cluster key, a column that is available in all interleaved tables.

277
Q

single-level index

A

A single-level index is a file containing column values, along with pointers to rows containing the column value.

278
Q

multi-column index

A

In a multi-column index, each index entry is a composite of values from all indexed columns. In all other respects, multi-column indexes behave exactly like indexes on a single column.

279
Q

table scan

A

A table scan is a database operation that reads table blocks directly, without accessing an index.

280
Q

index scan

A

An index scan is a database operation that reads index blocks sequentially, in order to locate the needed table blocks.

281
Q

Hit ratio / filter factor / selectivity

A

Hit ratio, also called filter factor or selectivity, is the percentage of table rows selected by a query.

282
Q

binary search

A

In a binary search, the database repeatedly splits the index in two until it finds the entry containing the search value

283
Q

primary index / clustering index

A

A primary index, also called a clustering index, is an index on a sort column.

284
Q

secondary index / nonclustering index

A

A secondary index, also called a nonclustering index, is an index that is not on the sort column.

285
Q

dense index

A

A dense index contains an entry for every table row.

286
Q

sparse index

A

A sparse index contains an entry for every table block.

287
Q

multi-level index

A

A multi-level index stores column values and row pointers in a hierarchy.

288
Q

fan-out

A

The number of index entries per block is called the fan-out of a multi-level index.

289
Q

branch

A

Each path from the top-level block to a bottom-level block is called a branch.

290
Q

balanced / imbalanced

A

Multi-level indexes are called balanced when all branches are the same length and imbalanced when branches are different lengths.

291
Q

B+tree

A

All indexed values appear in the bottom level. Pointers to table blocks appear only in the bottom level. Since some indexed values also appear in higher levels, values are occasionally repeated in the index.

292
Q

B-tree

A

B-tree. If an indexed value appears in a higher level, the value is not repeated at lower levels. Instead, a pointer to the corresponding table block appears in the higher level along with the value.

293
Q

hash index

A

In a hash index, index entries are assigned to buckets.

294
Q

bucket

A

A bucket is a block or group of blocks containing index entries

295
Q

hash function

A

The bucket containing each index entry is determined by a hash function, which computes a bucket number from the value of the indexed column.

296
Q

bitmap index

A

A bitmap index is a grid of bits

297
Q

physical index

A

A single- or multi-level index normally contains pointers to table blocks and is called a physical index.

298
Q

logical index

A

A logical index is a single- or multi-level index in which pointers to table blocks are replaced with primary key values.

299
Q

function index

A

In a function index, the database designer specifies a function on the column value. Index entries contain the result of the function applied to column values, rather than the column values.

300
Q

tablespace

A

A tablespace is a database object that maps one or more tables to a single file.

301
Q

fragmented

A

As files are updated, blocks become scattered, or fragmented, across many tracks.

302
Q

partition

A

A partition is a subset of table data. One table has many partitions that do not overlap and, together, contain all table data.

303
Q

horizontal partition

A

A horizontal partition is a subset of table rows

304
Q

vertical partition

A

A vertical partition is a subset of table columns

305
Q

shard

A

Like a partition, a shard is a subset of table data, usually a subset of rows rather than columns. Unlike partitions, which are stored on different storage devices of a single computer, shards are stored on different computers of a distributed database.

306
Q

partition expression / partition columns

A

To partition a table, the database administrator specifies a partition expression based on one or more partition columns. The partition expression may be simple, such as the value of a single partition column, or a complex expression based on several partition columns. Rows are assigned to partitions in one of the following ways.

307
Q

range partition

A

A range partition associates each partition with a range of partition expression values. The VALUES LESS THAN keywords specify the upper bound of each range. The MAXVALUE keyword represents the highest column value, and VALUES LESS THAN MAXVALUE specifies the highest range. Each partition is explicitly named by the database administrator.

308
Q

list partition

A

A list partition associates each partition with an explicit list of partition expression values using the VALUES IN keywords. Like a range partition, each partition is explicitly named.

309
Q

hash partition

A

A hash partition requires a partition expression with positive integer values. The database administrator specifies the number of partitions, N, and partitions are automatically named p0 through p(N-1). The partition number for each row is computed as: (partition expression value) modulo N.

Data is divided based on a hash function applied to the key. Each key is hashed, and the result determines the partition where the data will be stored. This ensures an even distribution of data across partitions.

310
Q

key partition

A

A key partition is similar to a hash partition, except the partition expression is determined automatically by the database.

311
Q

Logical design

A

Logical design specifies tables, columns, and keys. The logical design process is described elsewhere in this material.

312
Q

Physical design

A

Physical design specifies indexes, table structures, and partitions. Physical design affects query performance but never affects query results.

313
Q

storage engine / storage manager

A

A storage engine or storage manager translates instructions generated by a query processor into low-level commands that access data on storage media. Storage engines support different index and table structures, so physical design is dependent on a specific storage engine.

314
Q

CREATE INDEX

A

The CREATE INDEX statement creates an index by specifying the index name and table columns that compose the index.

315
Q

DROP INDEX

A

The DROP INDEX statement deletes a table’s index

316
Q

SHOW INDEX

A

The SHOW INDEX statement displays a table’s index.

317
Q

EXPLAIN

A

The EXPLAIN statement generates a result table that describes how a statement is executed by the storage engine.

318
Q

slow query log

A

The MySQL slow query log is a file that records all long-running queries submitted to the database.

319
Q

Architecture

A

Architecture describes the components of a computer system and the relationships between components.

320
Q

Tools

A

Tools interact directly with database users and administrators, and send queries to the query processor.

Data Management Foundations, D426 (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions