CIS275 - Chapter 2: Relational Databases Flashcards
A _____ is a conceptual framework for database software.
database model
The _____ is not optimized for big data.
relational model
Many non-relational databases have come to market since 2000, optimized for big data and are collectively called _____.
NoSQL databases
NoSQL stands for _____ and encompasses a variety of database models.
‘Not only SQL’
The _____ is a database model based on mathematical principles, with three parts:
A data structure that prescribes how data is organized.
Operations that manipulate data structures.
Rules that govern valid relational data.
relational model
A ___ is a collection of values, or elements, with no inherent order.
set
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}.
A _____ is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE.
domain
A _____ is a finite sequence of values, each drawn from a fixed domain.
tuple
Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues).
A _____ is a named set of tuples, all drawn from the same sequence of domains.
relation
Ex: The relation below is named Grocery and contains three tuples.
In the relational model, each tuple position is called an _____ and given a unique name.
attribute
Ex: In the Grocery relation, the first, second, and third positions might be named Quantity, FruitType, and OrganicCertification.
Domain, tuple, relation, and attribute =
Data type, row, table, and column
The relational model stipulates a set of operations on tables, collectively called _____.
relational algebra
Like the relational data structure, relational operations are based on set theory.
combines two tables with identical columns into one table.
Union
removes all rows of one table from another table.
Difference
eliminates one or more columns of a table.
Projection
selects a subset of rows of a table.
Selection
combines two tables with different columns into one table.
Join
lists all possible combinations of rows of two tables.
Product
_____, also known as integrity rules, are logical constraints that ensure data is valid and conforms to business policy.
Relational rules
_____ are relational rules that govern data in every relational database.
Structural rules
The relational model stipulates a number of structural rules, such as:
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 be have identical values in all columns.
_____ are relational rules specific to a particular database and application.
Business rules
Example business rules include:
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.
A _____ is a collection of data organized as columns and rows.
table
A table must have at least one column and any number of rows. A table without rows is called an empty table.
A _____ is a set of values of the same type. Each column has a name, different from other column names in the table.
column
A ____ is a set of values, one for each column.
row
A ____ is a single column of a single row. In relational databases, each ____ contains exactly one value.
(same word in both fields)
cell
In addition to a name, each column has a_____, which defines the format of the values stored in each row.
data type
_____data types represent positive and negative integers. Several _____ data types exist, varying by the number of bytes allocated for each value.
(same word)
Integer
Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.
_____data types represent numbers with fractional values.
Decimal
Decimal data types vary by number of digits after the decimal point and maximum size. Common decimal data types include FLOAT and DECIMAL.
_____data types represent textual characters.
Character
Common character data types include CHAR, a fixed string of characters, and VARCHAR, a string of variable length up to a specified maximum size.
_____data types represent date, time, or both.
Time
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.
_____data types store data exactly as the data appears in memory or computer files, bit for bit.
Binary
The database manages binary data as a series of zeros and ones. Common binary data types include BLOB, BINARY, VARBINARY, and IMAGE.
_____data types store geometric information, such as lines, polygons, and map coordinates.
Spatial
Examples include POLYGON, POINT, and GEOMETRY. Spatial data types are relatively new and consequently vary greatly across database systems.
_____data types contain textual data in a structured format such as XML or JSON.
Document
Data types
Relational databases support many different data types. Most data types fall into one of the following categories:
Tables obey three rules:
- Tables are normalized — exactly one value exists in each cell.
- No duplicate column names — duplicate column names are not allowed in one table. However, the same column name can appear in different tables.
- No duplicate rows — no two rows may have identical values in all columns.
In addition to the three rules, relational databases obey the principle of _____, which states that rows and columns of a table have no inherent order.
data independence
Although values in rows and columns are stored sequentially on a storage device, such as a disk drive, the sequence is arbitrary and does not affect the results of a database query.
a special value that represents missing data.
Null value
NULL represents either ‘unknown’ or ‘inapplicable’.
NULL is not the same as zero for numeric data types or blanks for character data types.
a symbol that computes a value from one or more other values (called operands).
operator
Arithmetic operators, such as +, -, *, and /, compute numeric values from numeric operands.
Comparison operators, such as , and =, compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types.
Logical operators, AND, OR, and NOT, compute logical values from logical operands.
arithmetic functions
returns the total of selected values.
returns the average of selected values.
returns the largest selected value.
returns the smallest selected value.
SUM
AVG
MAX
MIN
SUM, AVG, MIN, and MAX ignore NULL values.
a column, or group of columns, used to identify a row.
primary key
Primary keys obey two rules:
Values must be unique within the column. This rule ensures that each value identifies at most one row.
Values may not be NULL. This rule ensures that each value identifies at least one row.
A unique column that is not the primary key is called an:
alternate key
Collectively, the primary key and all alternate keys of a table are called
candidate keys
A _____ key consists of a single column.
simple primary
A _____ key consists of multiple columns.
composite primary key
Composite primary keys are denoted with parentheses. Ex: (ColumnA, ColumnB).
Composite primary keys obey three rules:
Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is unique within (ID, Number).
Columns may not contain NULL.
Composite primary keys must be minimal.
In a _____, all columns are necessary for uniqueness.
minimal primary key
Ex: (ID, Number, Relationship) is not minimal. Since (ID, Number) is unique, Relationship is unnecessary.
A _____ is a column or group of columns, that refers to a primary key.
foreign key
Foreign keys do not obey the same rules as primary keys:
Foreign key values may be repeated. Ex: Sales and Marketing have the same manager.
Foreign key values may be NULL. Ex: Technical support currently has no manager.
Non-NULL foreign key values must match some primary key value.
Referential integrity actions:
rejects an insert, update, or delete that violates referential integrity.
RESTRICT
Referential integrity actions
sets invalid foreign keys to NULL.
SET NULL
Referential integrity actions
sets invalid foreign keys to a default primary key value, specified in SQL.
SET DEFAULT
Referential integrity actions
propagates primary key changes to foreign keys.
CASCADE
a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.
join
The tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types.
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
A _____ selects all right table rows, but only matching left table rows.
right join
A ____ selects all left and right table rows, regardless of match.
full join
An _____ is any join that selects unmatched rows, including left, right, and full joins.
outer join
An _____ compares columns of two tables with the = operator. Most joins are _____ , including the examples above.
(same word)
equijoin
A _____ compares columns with an operator other than =, such as .
non-equijoin
In the example below, a non-equijoin selects all buyers along with properties priced below the buyer’s maximum price.
In a _____, a table is joined to itself.
self-join
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.
An ____ is a temporary name assigned to a column or table.
alias
In the example below, A is the left table’s alias, and B is the right table’s alias. A.Name is the Name column of the left table, representing the employee. B.Name is the Name column of the right table, representing the employee’s manager. The result shows employees along with each employee’s manager.
A _____ combines two tables without comparing columns.
cross-join
A cross-join has no WHERE or ON clause. As a result, all possible combinations of rows from both tables appear in the result.
In the example below, all configurations of iPhone models and memory appear, along with total price.
A _____ is a table name associated with a SELECT statement, called the _____.
view table
view query
The _____ statement creates a view table and specifies the view name, query, and, optionally, column names.
CREATE VIEW
A table specified in the view query’s FROM clause is called a _____.
base table
A _____ is a view for which data is stored at all times.
materialized view
Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed. To avoid the overhead of refreshing views, MySQL and many other databases do not support materialized views.
Advantages of views: protect sensitive data.
Explain
A table may contain sensitive data.
Ex: The Employee table contains compensation columns such as Salary and Bonus. A view can exclude sensitive columns but include all other columns. Authorizing users and programmers access to the view but not the underlying table protects the sensitive data.
Advantages of views: save complex queries.
Explain
Complex or difficult SELECT statements can be saved as a view. Database users can reference the view without writing the SELECT statement.
Advantages of views: Save optimized queries.
Explain
Often, the same result table can be generated with equivalent SELECT statements. Although the results of equivalent statements are the same, performance may vary. To ensure fast execution, the optimal statement can be saved as a view and distributed to database users.
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
- Two employees are in department 51, Sales.
- CREATE VIEW specifies new names for SalesEmployee columns.
- SalesEmployee view only includes employees in department 51.
- The user query inserts a new employee in department 80 into the view table.
- The new employee is inserted into Employee but is not in department 51 and does not appear in the view table.
WITH CHECK OPTION prevents inserts and updates that do not satisfy the view query WHERE clause.
In a database, a ___ is a pair of related values, and ___ is the repetition of a fact.
fact
redundancy
Redundancy causes database management problems. When a fact is updated or deleted, all copies must be changed, which makes queries slow and complex. If copies are not updated or deleted uniformly, the copies become inconsistent and the correct fact is uncertain.
- The fact that passenger number 222 is named Elvira Yin is repeated.
- The fact that first class fares board first is repeated.
_____ are rules for designing tables with less redundancy.
Normal forms
Normal forms are numbered, first normal form through fifth normal form. An additional normal form, named Boyce-Codd normal form, is considered an improved version of third normal form. The six normal forms comprise a sequence, with each normal form allowing less redundancy than the prior normal form.
Column A ____ column B means each B value is related to at most one A value. For a table to be in _____, all non-key columns must depend on the primary key.
depends on
first normal form
In a relational database, all tables with a primary key are in first normal form, as illustrated in the animation below.
For a table to be in _____, all non-key columns must depend on the whole primary key.
second normal form
n other words, a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is automatically in second normal form.
- PassengerName depends on PassengerNumber. Dependence on part of the primary key causes repetition of (222, Elvira Yin).
- Removing PassengerName from Booking eliminates redundancy. Booking is now in second normal form.
- PassengerName moves to the Passenger table. The Passenger table has no redundancies, since PassengerName depends on the whole primary key.
A _____ is a simple or composite column that is unique and minimal.
candidate key
Minimal means all columns are necessary for uniqueness. A table may have several candidate keys. The database designer designates one candidate key as the primary key.
A _____ column is a column that is not contained in a candidate key.
non-key
Ex: The Employee table contains columns ID, SocialNumber, and Name:
- ID and SocialNumber are candidate keys, since both are unique and minimal.
- (ID, Name) is unique but not minimal, since Name is not necessary for uniqueness. Therefore (ID, Name) is not a candidate key.
- Name is a non-key column.
A table is in _____ if, whenever a non-key column A depends on column B, then B is unique.
third normal form
In this definition, columns A and B may be simple or composite. Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key.
The definition of third normal form states that all non-key dependencies are on a unique column. The definition generalizes the informal description, above, which states that all non-key dependencies are on the primary key.
The definition of third normal form applies to non-key columns only, which allows for occasional redundancy.
A table is in _____ if, whenever column A depends on column B, then B is unique. This definition is identical to the definition of third normal form with the term ‘non-key’ removed.
Boyce-Codd normal form
A _____ is any simple or composite column that is unique. Unlike candidate keys, superkeys are not necessarily minimal.
superkey
An alternative definition of Boyce-Codd normal form replaces unique with superkey: A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is a superkey. This definition is equivalent to the definition above.
