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
