Section 1 - Relational Databases

What is a field associated with?

A field is a tuple position, which is a column.

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.

What is a tuple associated with?

A tuple is a finite sequence of values, which is a row.

If a non primary key column has all unique values which columns depend on it?

All of them including the primary key

What is an attributes associated with?

An attribute is a tuple position, which is a column.

As a practical matter, ____ normal form is most important. It eliminates all dependencies on columns that are not unique, and all associated redundancy.

Boyce-Codd

The definition of third normal form applies to non-key columns only, which allows for occasional redundancy. ____ _____ normal form applies to all columns and eliminates this redundancy

Boyce-Codd

A table is in ___ _____ normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite. This definition is identical to the definition of third normal form with the term 'non-key' removed

Boyce-Codd Boyce-Codd normal form is considered the gold standard of table design. Although fourth and fifth normal forms remove additional types of redundancy, these redundancies are uncommon and of little practical concern.

_____ rules are relational rules specific to a particular database and application

Business

What are these an example of? 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.

Business rules

______ propagates primary key changes to foreign keys.

CASCADE CASCADE behaves differently for primary key updates and deletes. 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.

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.

Sometimes multiple columns are necessary to identify a row. A _____ primary key consists of multiple columns

Composite Composite primary keys are denoted with parentheses. Ex: (ColumnA, ColumnB).

T or F ? A foreign key may not refer to a primary key in the same table.

F In the EmployeeManager table, the Manager foreign key refers to the ID primary key.

T or F? B → A means each value of A relates to at most one value of B.

F. B → A means A depends on B. Each value of B relates to at most one value of A. Ex: FareClass → BoardingZoneNumber means each fare class has one boarding zone. 5) FareClass depends on BoardingZoneNumber.

T or F In a first normal form table, non-key columns depend only on the primary key.

F. In a first normal form table, non-key columns may depend on other non-key columns.

Foreign keys do not obey the same rules as primary keys: They are...

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.

To prohibit NULL, a column can be designated ____ _____ in SQL

NOT NULL

Whenever arithmetic or comparison operators have one or more NULL operands, the result is ____

NULL The key words IS NULL return true when compared to NULL and are used instead of = to select rows with NULLs

Must a table have at least one row?

No. A table without rows is called an empty table.

____ ______ 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 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.

____ is a special value that represents missing data. It represents either 'unknown' or 'inapplicable'. It is not the same as zero for numeric data types or blanks for character data types

Null

____ rejects an insert, update, or delete that violates referential integrity.

RESTRICT RESTRICT is applied when no action is specified.

Databases can automatically correct referential integrity violations with any of four actions, which are specified in SQL when creating a table with a foreign key:

RESTRICT rejects an insert, update, or delete that violates referential integrity. SET NULL sets invalid foreign keys to NULL. SET DEFAULT sets invalid foreign keys to a default primary key value, specified in SQL. CASCADE propagates primary key changes to foreign keys.

_____ is the repetition of related values in a table.

Redundancy Redundancy is the repetition of related values, such as (222, Elvira Yin). When related values are 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 version is uncertain.

_____ _______ requires that all foreign key values must either be fully NULL or match some primary key value.

Referential integrity

_____ rules, also known as integrity rules, are logical constraints that ensure data is valid and conforms to business policy.

Relational

____ ______ sets invalid foreign keys to a default primary key value, specified in SQL.

SET DEFAULT The value specified for SET DEFAULT must be a valid primary key.

_____ ____ sets invalid foreign keys to NULL.

SET NULL SET NULL cannot be used when a foreign key is not allowed NULL values.

Redundant table example

Since PassengerNumber is not unique, 222 appears in multiple rows. Since PassengerName depends on PassengerNumber, passenger 222 is always named Elvira Yin. Therefore, (222, Elvira Yin) appears in multiple rows.

_____ rules are relational rules that govern data in every relational database.

Structural

What are these an example of? 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.

Structural rules

T or F ? Several foreign keys may refer to the same primary key.

T In the DepartmentStaff table, the Manager and Assistant foreign keys both refer to ID, the primary key of Employee.

T or F? A foreign key that refers to a composite primary key must also be composite.

T. In the HealthPlan table, (EmployeeID, DependentNumber) is a composite foreign key that refers to the Family table's primary key.

Tables obey three structural rules which are

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.

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.

An ______ function operates on numeric values from multiple rows, including only rows selected by the WHERE clause

aggregate SUM, which returns the total of selected values. AVG, which returns the average of selected values. MAX, which returns the largest selected value. MIN, which returns the smallest selected value.

Each tuple position is called an _____

attribute. Each attribute has a name that is unique within the relation. Since each tuple position has a domain, each attribute also has a domain Ex: In the Grocery relation, the first, second, and third positions might be named Quantity, FruitType, and OrganicCertification, with domains Integers, DictionaryWords, and LogicalValues, respectively.

A _____ key is a simple or composite column that is unique and minimal

candidate 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 _____ is a single column of a single row. In relational databases, each cell contains exactly one value.

cell

A _____ is a set of values of the same type. Each column has a name, different from other column names in the table.

column

states that rows and columns of a table have no inherent order

data independence

In addition to a name, each column has a ___ ____, which defines the format of the values stored in each row

data type

Multivalued dependence and join dependence entail ...

dependencies between three or more columns. Multivalued and join dependencies are complex, uncommon, and primarily of theoretical interest. Fourth and fifth normal forms eliminate multivalued and join dependencies, respectively, and associated redundancy. Fourth and fifth normal forms are not discussed in this material.

Column A ____ ______ column B means each B value is related to at most one A value. Columns A and B may be simple or composite. 'A depends on B' is denoted 'B → A'

depends on

A _____ is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE

domain

pair of related values

fact

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

first Thus, a table with no duplicate rows is in first normal form.

In this material, an empty circle (○) precedes _______ keys in table examples.

foreign

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

foreign Non-NULL foreign key values must match some value of the primary key.

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

fully NULL

Dependence of one column on another is formally called ____ _______

functional dependence

In a ______ primary key, all columns are necessary for uniqueness. When any column is removed from a ______ composite primary key, the resulting simple or composite column is no longer unique

minimal

A ___ ____ column is a column that is not contained in a candidate key (remember candidate keys can be more than one column combined)

non-key

An ______ is a symbol that computes a value from one or more other values, called _____

operator, operands 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.

A ____ _____ is a column, or group of columns, used to identify a row

primary key

In this material, a solid circle (●) precedes the ____ _____

primary key

A _____ is a named set of tuples, all drawn from the same sequence of domains. Ex: The relation below is named Grocery and contains three tuples.

relation

The _______ model 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

The relational model stipulates a set of operations on tables, collectively called _____ ________

relational algebra.

A _____i s a set of values, one for each column.

row

A table is in _____ normal form when all non-key columns depend on the whole primary key. In 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

A ___ is a collection of values, or elements, with no inherent order

set

A _____ is a collection of data organized as columns and rows.

table

Informally, a table is in _____ normal form when all non-key columns depend on the key, the whole key, and nothing but the key.

third

More Formal Definition: A table is in ____ 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. Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key.

third

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