Chapter 3: The Relational Database Model

What are the three well-defined components of the Relational Model?

1. A logic data structure represented by relations (Sec. 3.1, 3.2, and 3.5) 2. A set of integrity rules to enforce that the data are and remain consistent over time (Sec. 3.3, 3.6, 3.7, and 3.8) 3. A set of operations that defines how data are manipulated (Sec. 3.4)

A natural join is a three stage process, list those stages:

1. PRODUCT 2. SELECT 3. PROJECT

____ data, also known as text data or string data can contain any character or symbol not intended for mathematical manipulation.

Character

____ are incapable of producing anomalies as long as referential integrity is enforced,

Controlled redundancies

When the primary key column has no null entries, and all entries are unique is called____.

Entity integrity

A ____, is used to refer to functional dependencies in which the entire collection of attributes in the determinant is necessary for the relationship.

Full functional dependence

The value of one or more attributes determines the value of one or more other attributes.

Functional dependence

This set operator, yields only rows that appear in both tables, must be union-compatible, and cannot be used if one of the attributes in numeric (one table) and the other is character (other table)

INTERSECT

An ____ is an orderly arrangement that is used to logically access rows in a table.

Index

This set operator, all information to be combined from two or more tables when these independent tables can be linked by common attributes.

JOIN

Provides a framework in which an assertion (statement) or fact can be verified as either true or false.

Predicate logic

The ____ is an attribute or combination of attributes that uniquely defines any given row.

Primary key (PK) EX: STU_NUM, EE_ID, SSN

A ____ consists of one or more attributes that determine other attributes.

Keys

In the relational model, ____ are important because they are used to ensure that each row in a table is uniquely identifiable.

Keys

This set operator, yields values for all rows found in a table that satisfy a given condition. Yields a horizontal subset for a table.

SELECT

____ joins are especially useful when you are trying to determine what values in related tables cause referential integrity problems.

Outer

The standard notation for representation a functional dependency between STU_NUM and STU_NAME is?

STU_NUM --> STU_NAME

Is defined as a key that is used strictly for data retrieval.

Secondary key

True or False: The 1:M relationship is the relational modeling ideal?

TRUE, you can use 1:1 relationships but it should be rare..

A ____ can be thought of as a persistent representation of a logic relation.

Table

A ____ contains a group of related entity occurrences (entity set).

Table

Entity set and ____ are often used interchangeably.

Table

True or False: Functional dependence can involve a determinant that comprises more than one attribute and multiple dependent attributes

True Example: STU_NUM --> (STU_LNAME, STU_FNAME, STU_GPA)

A table row is also known as a

Tuple

This set operator, combines all rows from two tables,excluding duplicate rows, must be union-compatible and must have the same attribute characteristics.

UNION

A constraint placed on a column to ensure that no duplicate value exists in that column.

UNIQUE

Any ____ key must have the UNIQUE and NOT NULL constraints enforced.

candidate

An ____entity designed to transform a M:N relationship into two 1:m relationships. The composite entity's primary key comprises at least the primary keys of the entities that it connects. Also known as a bridge entity.

composite

The ____ dictionary contains at least all of the attribute names and characteristics for each table in the system (meta-data).

data

The word ____ in database context, indicates the use of the same name to label different attributes

homonym

A ____ table is the implementation of a composite entity.

linking

A ____ join links tables by selecting only the rows with common values in their common attribute(s).

natural

You can use ____ data to perform meaningful arithmetic procedures.

numeric

Foreign keys are used to ensure ____.

referential integrity

The condition in which every reference to any entity instance by another entity instance is valid.

referential integrity

The word ____ indicates the use of different names to describe the same attribute (ex: car and auto).

synonym

The ___ catalog also contains meta-data and can be described as a detailed system data dictionary that describes all objects w/n the database, including data about table names, the table's creator, filenames, index creators, authorized users, the # of col. in each table, etc. This is starting to replace the data dictionary because it contains the dictionary as well as additional meta-data.

system

____ data can only have a yes or no (true/false) value

Logical

The data ____ provides a detailed description of all tables in the database created by the user and designer (meta-data).

dictionary

____ integrity is created when foreign key values do not match the primary key values in related table(s).

Referential integrity

Table and ____ are often times seen as synonymous.

Relation

Which model has three well-defined components?

Relational model

True of False: A key is an attribute or group of attributes that can determine the values of other attributes.

True

True of False: Planned redundancies are common in good database design.

True

True or False: A candidate key is a type of superkey?

True

True or False: A many-to-many (M:N) relationship is not supported directly in the relational environment?

True

True or False: An index is an ordered arrangement of keys and pointers where each key points to the location of the data identified by the key.

True

True or False: Controlled redundancies are often designed as part of the system to ensure transaction speed and/or information requirements.

True

True or False: Date attributes contain calendar dates stored in a special format known as the Julian data format.

True

True or False: Foreign keys minimizes data redundancies and the chances that destructive data anomalies will develop.

True

True or False: From a conceptual point of view, an index is composed of an index key and pointers

True

True or False: Keys are determinants in functional dependencies.

True

True or False: The 1:M relationship is easily implemented in the relational model by putting the Primary key of the "1" side in the table of the "many" side as a foreign key.

True

True or False: You should avoid the use of homonyms?

True

True or False: You should avoid the use of synonyms?

True

The _____ is, in, effect, the index's reference point.

index key

An ____ join only returns matched records from the tables that are being joined.

inner

An ____ join, the matched pairs would be retained, and any unmatched values in the other table would be left null but included.

outer

This set operator, yields all rows found in one table and not in the other, subtracts one table from the other and must be union-compatible.

DIFFERENCE

The ____ operation uses one 2-col. table as the dividend and one-single column table as the divisor. The tables must have a common col.

DIVIDE

____is the state in which knowing the value of one attribute makes it possible to determine the values of others (relationships among attributes).

Determination

The ____ is the column's range of permissible values [0,4]

Domain

A ____ is used to indicate the absence of some value.

Flag

A constraint placed on a column to ensure every row in the table has a value.

NOT NULL

A ____ is the absence of any data value, and it is never allowed in any part of the primary key.

Null

Which data types do most DBMS support?

Numeric, Character, Date and Logical

This set operator, yields all possible pairs of rows from two tables.

PRODUCT

This set operator, yields all values for selected attributes and yields a vertical subset of a table.

PROJECT

A mathematical science that deals with sets, or groups of things, and is used as the basis for data manipulation in the relational model.

Set theory

A ____ is a key that can uniquely identify any row in a table.

Superkey

What key functionally determines every attribute in a row?

Superkey

In an equijoin, the comparison operator is =, if any other comparison operator is used, the join is called a ____.

Theta join

A ____, links tables on the basis of an equality condition that compares specified columns of each table.

equijoin