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