C175
Referential integrity can be violated in four ways:
A primary key is updated. A foreign key is updated. A row containing a primary key is deleted. A row containing a foreign key is inserted.
If the CHECK expression does not evaluate to _________(for NULL values), the constraint is violated.
TRUE or UNKNOWN
The _______operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.
LIKE
The _______ clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order..
ORDER BY
Inventor: E. F. Codd Initial release: 1980s Objectives: Productivity and simplicity; Transactional applications Ex Databases: MySQL, Oracle, SQL Server, DB2
Relational
___________, also known as integrity rules, are logical constraints that ensure data is valid and conforms to business policy.
Relational rules
A __________ is a column, or group of columns, that refer to a primary key.
foreign key
A ______ selects all left and right table rows, regardless of match.
full join
Data Transaction Language (DTL)
manages database transactions.
Data Manipulation Language (DML)
manipulates data stored in a database.
A _______________ is a view for which data is stored at all times.
materialized view
SET DEFAULT
sets invalid foreign keys to a default primary key value, specified in SQL.
The relational model is a database model based on mathematical principles, with three parts:
1. A data structure that prescribes how data is organized. 2. Operations that manipulate data structures. 3. Rules that govern valid relational data.
Composite primary keys obey three rules:
1. Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is unique within (ID, Number). 2. Columns may not contain NULL. 3. Composite primary keys must be minimal.
Foreign keys do not obey the same rules as primary keys:
1. Foreign key values may be repeated. Ex: Sales and Marketing have the same manager. 2. Foreign key values may be NULL. Ex: Technical support currently has no manager. 3. Non-NULL foreign key values must match some primary key value.
Primary keys obey two rules:
1. Values must be unique within the column. This rule ensures that each value identifies at most one row. 2. Values may not be NULL. This rule ensures that each value identifies at least one row.
The relational data structure and operations are based on ____ theory.
A set is a collection of values, or elements, with no inherent order. Sets are denoted with braces. Ex: {apple, banana, lemon} is the set containing three kinds of fruit. Since sets have no order, {apple, banana, lemon} is the same set as {lemon, banana, apple}.
SET DEFAULT
sets invalid foreign keys to a default primary key value.
_______ is the process of gathering and documenting database requirements. The requirements are not dependent on any specific database system. Analysis goes by other names, such as conceptual design.
Analysis
SHOW CREATE TABLE
shows the CREATE TABLE statement for a given table.
______ data types store data exactly as the data appears in memory or computer files, bit for bit. The database manages binary data as a series of zeros and ones. Common binary data types include BLOB, BINARY, VARBINARY, and IMAGE.
Binary
A _________ number is a number that may be negative.
signed
_____________ are relational rules specific to a particular database and application. Example business rules include:
Business rules Unique column values — in a particular column, values may not be repeated. No missing values — in a particular column, all rows must have known values. Delete cascade — when a row is deleted, automatically delete all related rows.
The _________ constraint specifies an expression that limits the range of a column's values. Ex: CHECK (Salary > 20000) ensures the Salary is greater than 20,000.
CHECK
_______ data types represent textual characters. Common character data types include CHAR, a fixed string of characters, and VARCHAR, a string of variable length up to a specified maximum size.
Character
The ___________ keyword with the ORDER BY clause orders rows in descending order.
DESC
The ________ clause is used with a SELECT statement to return only unique or 'distinct' values.
DISTINCT
Most databases support dropping constraints using an ALTER TABLE statement with DROP CONSTRAINT ConstraintName. However, MySQL requires __________ ConstraintName for a UNIQUE constraint and DROP CHECK ConstraintName for a CHECK constraint.
DROP INDEX
The _________ constraint is used in a CREATE TABLE statement to specify a column's default value when no value is provided.
DEFAULT
The _________statement deletes existing rows in a table
DELETE
__________ is a broad term for the process that generates database specifications in SQL. The term covers three phases - analysis, logical design, and physical design.
Database design
__________data types represent numbers with fractional values. Decimal data types vary by number of digits after the decimal point and maximum size. Common decimal data types include FLOAT and DECIMAL.
Decimal
______ data types contain textual data in a structured format such as XML or JSON.
Document
In the relational data structure, which components are named?
Domain, relation, and attribute are all named. Tuples are not named.
Where are duplicate column names allowed?
Duplicate column names are not allowed within the same table, but are allowed in different tables. Programmers can distinguish identical column names in different tables by adding the table name as a prefix to a column name.
% matches any number of characters.
Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat".
_ matches exactly one character.
Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot".
Inventor: IBM Initial release: 1960s Objectives: Speed and storage Ex Databases:IMS
Hierarchical
The __________ statement adds rows to a table. The ______ statement includes the INTO and VALUES clauses:
INSERT
What does the principle of data independence state?
In relational databases, query results are not dependent on physical storage. This allows database administrators to tune storage for optimal performance at any time.
_____ data types represent positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.
Integer
A _____ can compare any columns with comparable data types. Most often, a foreign key of one join table is compared to the primary key of another.
Join
__________ results in SQL specifications for tables, columns, and keys in a specific database system.
Logical design
The ________ constraint is used in a CREATE TABLE statement to prevent a column from having a NULL value.
NOT NULL
When a row is inserted into a table, an unspecified value is assigned _______by default.
NULL
Inventor: Charles Bachman Initial release: 1960s Objectives: Speed and storage Ex Databases: IDMS
Network
Inventor: Google, Amazon, and others Initial release: 2000s Objectives: Big data, Analytic applications Ex Databases: MongoDB, Redis, Cassandra, Neo4j
NoSQL models
________ results in SQL specifications for index and table structures. In a relational database, index and table structures affect query performance but not query results. This principle is called data independence.
Physical design
View tables have several advantages:
Protect sensitive data Save complex queries Save optimized queries
The_______ statement deletes all rows from a table. _______ is nearly identical to a DELETE statement with no WHERE clause except for some small differences that depend on the database system. Ex: In MySQL, A __________statement resets the table's auto-increment values back to 1, but a DELETE statement does not.
TRUNCATE
____________ requires that all foreign key values must either be fully NULL or match some primary key value.
Referential integrity
______ data types store geometric information, such as lines, polygons, and map coordinates. Examples include POLYGON, POINT, and GEOMETRY. Spatial data types are relatively new and consequently vary greatly across database systems.
Spatial
__________ are relational rules that govern data in every relational database. The relational model stipulates a number of structural rules, such as:
Structural rules Unique primary key — all tables should have a column with no repeated values, called the primary key and used to identify individual rows. Unique column names — different columns of the same table must have different names. No duplicate rows — no two rows of the same table may have identical values in all columns.
______ data types represent date, time, or both. Some time data types include a time zone or specify a time interval. Some time data types represent an interval rather than a point in time. Common time data types include DATE, TIME, DATETIME, and TIMESTAMP.
Time
A table's primary key always has unique values, but values in other columns may contain duplicates. The ________ constraint ensures that all column values are unique.
UNIQUE
The ________ statement modifies existing rows in a table. The UPDATE statement uses the SET clause to specify the new column values.
UPDATE
When _____________ is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.
WITH CHECK OPTION
An________ is a descriptive property of an entity.
attribute
A table specified in the view query's FROM clause is called a ________
base table
The UNIQUE constraint may be applied to a single column or to multiple columns. A constraint that is applied to a single column is called a _____________.A constraint that is applied to multiple columns is called a _____________?
column-level constraint table-level constraint
All joins compare _____ from two tables.
columns
A simple primary key consists of a single column. A _________consists of multiple columns
composite primary key
Data Control Language (DCL)
controls database user access.
A _________combines two tables without comparing columns. A cross-join uses the CROSS JOIN keywords without a WHERE or ON clause. As a result, all possible combinations of rows from both tables appear in the result.
cross-join
Data Definition Language (DDL)
defines the structure of the database.
A_______ is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE.
domain
An_____ is a person, place, product, concept, or activity.
entity
An ________ diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. Entities are drawn as rectangles. Relationships are drawn as lines connecting rectangles. Attributes appear as additional text within an entity rectangle, under the entity name.
entity-relationship
An _________ is a high-level representation of data requirements, ignoring implementation details.
entity-relationship model
An _______ compares columns of two tables with the = operator.
equijoin
Columns can be excluded in the SELECT clause of a view query. Rows can be excluded in the WHERE clause of a view query. If a user is authorized to access the view but not the base table, the user cannot see __________?
excluded columns or rows.
An________ selects only matching left and right table rows.
inner join
A _________ selects all left table rows, but only matching right table rows.
left join
SHOW COLUMNS
lists columns available in a specific table named by a FROM clause.
SHOW DATABASES
lists databases available in the database system.
SHOW TABLES
lists tables available in the currently selected database.
In a ___________, all columns are necessary for uniqueness. When any column is removed from a minimal composite primary key, the resulting simple or composite column is no longer unique.
minimal primary key
A _________ compares columns with an operator other than =, such as <, >.
non-equijoin
A view is a table and can appear in any SELECT statement. Since a view query is a SELECT statement, view queries can reference_____?
other view tables.
An _________is any join that selects unmatched rows, including left, right, and full joins
outer join
All table columns, except ___________, may contain NULL values by default.
primary keys
CASCADE
propagates primary key changes to foreign keys.
CASCADE
propagates primary key changes to foreign keys. If a primary key is deleted, rows containing matching foreign keys are deleted. If a primary key is updated, matching foreign keys are updated to the same value.
RESTRICT
rejects an insert, update, or delete that violates referential integrity.
RESTRICT
rejects an insert, update, or delete that violates referential integrity. RESTRICT is applied by default when no action is specified.
A_______ is a named set of tuples, all drawn from the same sequence of domains.
relation
All ____________result in a table. The result table is not stored in the database but has the same structure as stored tables.
relational operations
A ________ is a statement about two entities.
relationship
ON DELETE
responds to an invalid primary key deletion. Ex: Deleting a primary key 1234 that is used in a foreign key.
ON UPDATE
responds to an invalid primary key update. Ex: Updating a primary key 1234 to 5555 when 1234 is used in a foreign key.
Data Query Language (DQL)
retrieves data from the database.
A _________selects all right table rows, but only matching left table rows.
right join
The most commonly used terms in relational database are ?
row, column, and table. Occasionally tuple, relation, attribute and record, file, field are also used.
The database converts a user query against a view to a merged query and then executes the merged query. The performance is the______as if the user entered the merged query.
same
In a __________, a table is joined to itself. A self-join can compare any columns of a table, as long as the columns have comparable data types. If a foreign key and the referenced primary key are in the same table, a self-join might compare key columns.
self-join
SET NULL
sets an invalid foreign key value to NULL.
SET NULL
sets invalid foreign keys to NULL.
A______ is a finite sequence of values, each drawn from a fixed domain. Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues).
tuple
An _________ number is a number that cannot be negative.
unsigned
A __________ is a table name associated with a SELECT statement, called the view query
view table