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