Relational Databases Flashcards
(198 cards)
1
Q
CONSTRAINT
A
Optional keyword followed by the constraint named and declaration
2
Q
CHECK
A
Specifies an expression on one ore more columns of a table. Constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL
3
Q
UNIQUE
A
Constraint that ensures values in a column, or group of columns, are unique
4
Q
Table Constraint
A
governs values in one or more columns
5
Q
Column Constraint
A
Govern values in a single column
6
Q
Constraint
A
ruled that governs allowable values in a database. Constraints are based on relation and business rules, and implemented with special keywords in a CREATE TABLE statement.
7
Q
ON UPDATE/ON DELETE
A
Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint
8
Q
CASCADE
A
propagates primary key changes to foreign keys
9
Q
SET DEFAULT
A
Sets invalid foreign keys to the foreign key default value
10
Q
SET NULL
A
Sets invalid foreign keys to a NULL
11
Q
RESTRICT
A
Rejects an insert, update or delete that violates referential integrity
12
Q
Referential Integrity
A
Relational Rule that requires foreign key values are either fully NULL or match some primary key value
13
Q
Fully NULL
A
Foreign key is a simple or composite foreign key in which all columns are NULL
14
Q
Foreign Key/References
A
Constraint is added to a CREATE TABLE statement with the FORIEGN KEY and REFERENCES keywords.
15
Q
Referential integrity
A
Requires a foreign key values must either be NULL or match some value of the referenced primary key.
16
Q
Foreign Keys
A
A Column or group of columns that refer to a primary key
17
Q
AUTO_INCREMENT
A
Defines an auto-increment column. Command follows the columns data type in a CREATE TABLE statement
18
Q
Auto-increment Colum
A
Numeric column that is assigned an automatically incrementing value when a new row is inserted
19
Q
PRIMARY KEY
A
Constraint in a CREATE TABLE statement names the tables primary key.
20
Q
Composite Primary Key
A
Primary key consisting of multiple columns
21
Q
Simple Primary Key
A
A primary key that consist of a single Column
22
Q
Primary Key
A
A Column, or group of columns, used to identify a row
23
Q
TRUNCATE
A
Statement that deletes all rows from a table
24
Q
FROM
A
Keyword is followed by the table now whose rows are to be deletedT
25
DELETE
Statement that deletes existing rows in a table
26
SET
Statements use the SET clause to specify the new column values
27
Update
Modifies existing rows in a table
28
Default
Optional keyword that follow the column name and date type in CREATE TABLE statement. Column is assigned the default value, rather than NULL, when omitted form an INSERT statement
29
VALUES
Specifies the column values to be added
30
INSERT INTO
names the table and columns where data is to be added. INTO keyword is optional
31
INSERT
Statement adds a row to a table
32
Truth Tables
Value of logical expressions containing NULL Operands is defined in a truth table
33
IS NULL/IS NOT NULL
Operators must be used to select NULL values.
34
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 date type in a CREATE TABLE statement.
35
NULL
Special value that represents either unknown or inapplicable data
36
WHERE
SELECT statement has an option 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
37
Condition
Expression that evaluates to a logical value
38
LIMIT
Clause that limits the number of rows returned by a SELECT statement.
39
Result table
SELECT statement returns a set of rows, called the result table
40
SELECT/FROM
Statements selects rows from a table. Statement has a SELECT clause and a FROM clause. FROM clause specifies the table from which rows are selected. SELECT clause specifies one or more expression, separated by commas, that determine what values are returned for each row.
41
Precedence of Operators
-(unary), ^, * / % , + - (binary), = != < > <= >= , NOT, AND, OR
42
Operator Precedence
Operators in an expression are evaluated in the order of operator precedence. Parentheses are always evaluated first
43
Expression
String of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. Value of the expression may be any data type
44
Binary Operator
Has two operands
45
Unary Operator
Has more than one operand
46
Logical Operator
Computes logical values from logical operands
47
Comparison operator
Computes logical values TRUE or FALSE. Operands may be numeric, character, and other data types
48
Arithmetic operator
Computes numeric values from numeric operands
49
Operator/Operands
Symbol that computes a value from one or more other values
50
Unsigned
Number cannot be negative
51
Signed
Number may be negative
52
Document
Data types contains textual data in a structured format such as XML or JSON
53
Spatial
Data type that stores geometric information, such as lines, polygons, and map coordinates
54
Binary
Data type that stores data exactly as the data appears in memory or computer files, bit for bit
55
Date and time
data type that represents date, time, or both. Some date and time data types include a time zone or specify a time interval
56
Character
Data type that represents textual characters
57
Decimal
Data type represents numbers with fractional values
58
Integer
Represent positive and negative integers
59
Data Type
named set of values from which column values are drawn
60
ALTER TABLE
Statement adds, deletes, or modifies columns on an existing table
61
DROP TABLE
Statement that deletes a table, along with all the table's rows, from a database
62
CREATE TABLE
Statement creates a new table by specifying the table name, column names, and column data types
63
Data Independence
Rule 4 is called Data indepenedence
64
Empty table
Table without rows is called an empty table
65
Cell
Single column of a single row
66
Row
Unnamed sequence of values. Each value corresponds to a column and belongs to the columns data type
67
Column
has a name and a data type
68
Table
named, fixed sequence of columns, and a varying set of rows
69
SHOW CREATE TABLE TableName
Shows the CREATE TABLE statement for the TableName table of the default database
70
SHOW COLUMNS FROM TableName
Lists all columns in the TableName table of the default database
71
SHOW TABLES
Lists all tables in the default database
72
SHOW DATABASES
Lists all databases in the database system instance
73
USE Databasename
Selects a default database for use in subsequent SQL statements
74
DROP DATABASE DatabaseName
Deletes a database, including all tables in the database
75
CREATE DATABASE DatabaseName
Creates a new Database with name of DatabaseName
76
Database System Instance
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.
77
Data Transaction Language
Manages database transactions
78
Data Control Langauge
Controls database user access
79
Data Manipulation Language
manipulates data stores in a database
80
Data Query Language
Retrieves data from the database
81
Data Definition Language
Defines the structure of the database
82
Clause
Clause groups SQL keywords like SELECT, FROM, and WHERE with table names like City, Column names like Name, and conditions like Population >10000.
83
Statement
complete commmand composed of one or more clauses
84
SQL
High-level computer language for storing, manipulating, and retrieving data
85
Constraints
Rules implemented as SQL constraints and enforced by the database system
86
Business Rules
Rules based on business policy and specific to a particular database
87
Relational Algebra
Operations collectivelly called Relational Algebra and are the theoretical foundations of the SQL Language
88
Data Type
Named set of values, from which column values are drawn
89
Row
Unnamed tuple of values. Each value corresponds to a column and belongs to the columns data type
90
Column
Has a name and a data type
91
Table
Has a name, a fixed tuple of columns, and a varying set of rows
92
Tuple
Ordered collection of elements enclosed in parentheses
93
Set
Unordered collection of elements enclosed in braces
94
Big Data
Rise of the internet lead to generating unprecedented data volumes and rapidly changing data structures
95
Relational Model
Database model based on a tabular data structure. Data structure, operations, and rules are standardized in SQL
96
Rules
Govern Valid Data
97
Operations
Manipulate Data structures
98
Data Structures
Prescribe how data is organized
99
Database Model
Conceptual Framework for database systems
100
Optional keyword followed by the constraint named and declaration
CONSTRAINT
101
Specifies an expression on one ore more columns of a table. Constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL
CHECK
102
Constraint that ensures values in a column, or group of columns, are unique
UNIQUE
103
governs values in one or more columns
Table Constraint
104
Govern values in a single column
Column Constraint
105
ruled that governs allowable values in a database. Constraints are based on relation and business rules, and implemented with special keywords in a CREATE TABLE statement.
Constraint
106
Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint
ON UPDATE/ON DELETE
107
propagates primary key changes to foreign keys
CASCADE
108
Sets invalid foreign keys to the foreign key default value
SET DEFAULT
109
Sets invalid foreign keys to a NULL
SET NULL
110
Rejects an insert, update or delete that violates referential integrity
RESTRICT
111
Relational Rule that requires foreign key values are either fully NULL or match some primary key value
Referential Integrity
112
Foreign key is a simple or composite foreign key in which all columns are NULL
Fully NULL
113
Constraint is added to a CREATE TABLE statement with the FORIEGN KEY and REFERENCES keywords.
Foreign Key/References
114
Requires a foreign key values must either be NULL or match some value of the referenced primary key.
Referential integrity
115
A Column or group of columns that refer to a primary key
Foreign Keys
116
Defines an auto-increment column. Command follows the columns data type in a CREATE TABLE statement
AUTO_INCREMENT
117
Numeric column that is assigned an automatically incrementing value when a new row is inserted
Auto-increment Colum
118
Constraint in a CREATE TABLE statement names the tables primary key.
PRIMARY KEY
119
Primary key consisting of multiple columns
Composite Primary Key
120
A primary key that consist of a single Column
Simple Primary Key
121
A Column, or group of columns, used to identify a row
Primary Key
122
Statement that deletes all rows from a table
TRUNCATE
123
Keyword is followed by the table now whose rows are to be deletedT
FROM
124
Statement that deletes existing rows in a table
DELETE
125
Statements use the SET clause to specify the new column values
SET
126
Modifies existing rows in a table
Update
127
Optional keyword that follow the column name and date type in CREATE TABLE statement. Column is assigned the default value, rather than NULL, when omitted form an INSERT statement
Default
128
Specifies the column values to be added
VALUES
129
names the table and columns where data is to be added. INTO keyword is optional
INSERT INTO
130
Statement adds a row to a table
INSERT
131
Value of logical expressions containing NULL Operands is defined in a truth table
Truth Tables
132
Operators must be used to select NULL values.
IS NULL/IS NOT NULL
133
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 date type in a CREATE TABLE statement.
NOT NULL
134
Special value that represents either unknown or inapplicable data
NULL
135
SELECT statement has an option 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
WHERE
136
Expression that evaluates to a logical value
Condition
137
Clause that limits the number of rows returned by a SELECT statement.
LIMIT
138
SELECT statement returns a set of rows, called the result table
Result table
139
Statements selects rows from a table. Statement has a SELECT clause and a FROM clause. FROM clause specifies the table from which rows are selected. SELECT clause specifies one or more expression, separated by commas, that determine what values are returned for each row.
SELECT/FROM
140
-(unary), ^, * / % , + - (binary), = != < > <= >= , NOT, AND, OR
Precedence of Operators
141
Operators in an expression are evaluated in the order of operator precedence. Parentheses are always evaluated first
Operator Precedence
142
String of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. Value of the expression may be any data type
Expression
143
Has two operands
Binary Operator
144
Has more than one operand
Unary Operator
145
Computes logical values from logical operands
Logical Operator
146
Computes logical values TRUE or FALSE. Operands may be numeric, character, and other data types
Comparison operator
147
Computes numeric values from numeric operands
Arithmetic operator
148
Symbol that computes a value from one or more other values
Operator/Operands
149
Number cannot be negative
Unsigned
150
Number may be negative
Signed
151
Data types contains textual data in a structured format such as XML or JSON
Document
152
Data type that stores geometric information, such as lines, polygons, and map coordinates
Spatial
153
Data type that stores data exactly as the data appears in memory or computer files, bit for bit
Binary
154
data type that represents date, time, or both. Some date and time data types include a time zone or specify a time interval
Date and time
155
Data type that represents textual characters
Character
156
Data type represents numbers with fractional values
Decimal
157
Represent positive and negative integers
Integer
158
named set of values from which column values are drawn
Data Type
159
Statement adds, deletes, or modifies columns on an existing table
ALTER TABLE
160
Statement that deletes a table, along with all the table's rows, from a database
DROP TABLE
161
Statement creates a new table by specifying the table name, column names, and column data types
CREATE TABLE
162
Rule 4 is called Data indepenedence
Data Independence
163
Table without rows is called an empty table
Empty table
164
Single column of a single row
Cell
165
Unnamed sequence of values. Each value corresponds to a column and belongs to the columns data type
Row
166
has a name and a data type
Column
167
named, fixed sequence of columns, and a varying set of rows
Table
168
Shows the CREATE TABLE statement for the TableName table of the default database
SHOW CREATE TABLE TableName
169
Lists all columns in the TableName table of the default database
SHOW COLUMNS FROM TableName
170
Lists all tables in the default database
SHOW TABLES
171
Lists all databases in the database system instance
SHOW DATABASES
172
Selects a default database for use in subsequent SQL statements
USE Databasename
173
Deletes a database, including all tables in the database
DROP DATABASE DatabaseName
174
Creates a new Database with name of DatabaseName
CREATE DATABASE DatabaseName
175
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.
Database System Instance
176
Manages database transactions
Data Transaction Language
177
Controls database user access
Data Control Langauge
178
manipulates data stores in a database
Data Manipulation Language
179
Retrieves data from the database
Data Query Language
180
Defines the structure of the database
Data Definition Language
181
Clause groups SQL keywords like SELECT, FROM, and WHERE with table names like City, Column names like Name, and conditions like Population >10000.
Clause
182
complete commmand composed of one or more clauses
Statement
183
High-level computer language for storing, manipulating, and retrieving data
SQL
184
Rules implemented as SQL constraints and enforced by the database system
Constraints
185
Rules based on business policy and specific to a particular database
Business Rules
186
Operations collectivelly called Relational Algebra and are the theoretical foundations of the SQL Language
Relational Algebra
187
Named set of values, from which column values are drawn
Data Type
188
Unnamed tuple of values. Each value corresponds to a column and belongs to the columns data type
Row
189
Has a name and a data type
Column
190
Has a name, a fixed tuple of columns, and a varying set of rows
Table
191
Ordered collection of elements enclosed in parentheses
Tuple
192
Unordered collection of elements enclosed in braces
Set
193
Rise of the internet lead to generating unprecedented data volumes and rapidly changing data structures
Big Data
194
Database model based on a tabular data structure. Data structure, operations, and rules are standardized in SQL
Relational Model
195
Govern Valid Data
Rules
196
Manipulate Data structures
Operations
197
Prescribe how data is organized
Data Structures
198
Conceptual Framework for database systems
Database Model
