Chapter 5 Flashcards
The Relational Data Model and Relational Database Constraints
The relational Model of Data is based on the concept of a _____.
Relation
T/F: The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations.
true.
A _________ is a mathematical concept based on the ideas of sets.
Relation
Informally, a relation looks like a _____ of values
table
A relation typically contains a set of ______.
rows
The data elements in each row represent certain facts that correspond to a real-world ________ or ________.
entity, relationship.
In the formal model, rows are called ________.
tuples
Each column has a ________ that gives an indication of the meaning of the data items in that column.
column header
In the formal model, the column header is called an __________.
attribute name (or just attribute)
Determine which is the attributes, tuples, and relation name in the following figure:
Each row has a value of a data item (or set of items) that uniquely identifies that row in the table called the ________.
Key of a Relation
Sometimes row-ids or sequential numbers are assigned as keys to identify the rows in a table, these are called ________.
artificial keys or surrogate keys
The __________ of a Relation Denoted by R(A1, A2, …..An). Where R is the _______ of the relation, and the ________ of the relation are A1, A2, …, An.
schema (or description), name, attributes.
(ex:
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
CUSTOMER is the relation name.
attributes: Cust-id, Cust-name, Address, Phone#.
)
T/F: Each attribute has a domain or a set of valid values.
True, For example, the domain of Cust-id is 6 digit numbers.
A ______ is an ordered set of values (enclosed in angled brackets ‘< … >’). In which, each value is derived from an appropriate ______.
tuple, domain.
A row in the CUSTOMER relation:
<632895, “John Smith”, “101 Main St. Atlanta, GA 30332”, “(404) 894-2000”>
is called a 4-tuple as it has _________.
4 values.
T/F: A relation is a set of such tuples (rows).
true.
T/F: A domain also has a data-type or a format defined for it.
true, for example:
the USA_phone_numbers may have a format: (ddd)ddd-dddd where
each d is a decimal digit. or Dates have various formats such as year, month, date formatted
as yyyy-mm-dd, or as dd mm,yyyy etc.)
T/F: A domain has a logical definition.
True, Example: “USA_phone_numbers” are the set of 10 digit phone numbers valid in the U.S.
The ________ designates the role played by a domain in a relation.
attribute name (used to interpret the meaning of the data elements corresponding to that attribute. Example: The domain Date may be used to define two attributes named “Invoice-date” and “Payment-date” with different meanings.)
The _________ is a subset of the Cartesian product of the domains of its attributes.
relation state.
(each domain contains the set of all possible values the attribute can take.)
T/F: each domain contains the set of all possible values the attribute can take.
True.
The relation state r(R) is a subset of dom (A1) X dom (A2) X ….X dom(An).
A1, A2, …, An are the _______ of the relation.
R(A1, A2, …, An) is the ________ of the relation.
r(R) is ___________ of relation R – this is a set of tuples (rows).
r(R) = {t1, t2, …, tn} where each ti is an ______.
ti = <v1, v2, …, vn> where each vj is an element-of ______.
attributes, schema, a specific state (or “value” or “population”), n-tuple, dom(Aj).
T/F: Let R(A1, A2) be a relation schema where dom(A1) = {0,1}, dom(A2) = {a,b,c}, and all the possible combinations of dom(A1) X dom(A2) is {<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }
then r(R) could be {<0,a> , <0,b> , <1,c> }.
true, this is one possible state (or “population” or “extension”) r of the relation R, defined over A1 and A2.
T/F: The tuples in the relation state r(R) are ordered/in tabular form.
false, The tuples are not considered to be ordered, even though they appear to be in the tabular form
T/F: Ordering of attributes in a relation schema R (and of values within each tuple). This representation may be called as “self-describing”.
true.
T/F: All values in a tuple are considered divisible.
false, they are considered atomic (indivisible).
T/F: Each value in a tuple must be from the domain of the attribute for that column.
True, If tuple t = <v1, v2, …, vn> is a tuple (row) in the relation state r of R(A1, A2, …, An), Then each vi must be a value from dom(Ai).
A special _______ value is used to represent values that are unknown or not available or inapplicable in certain tuples.
null
T/F: We refer to component values of a tuple t by: t[Ai] or t.Ai.
true, IT is the value vi of attribute Ai for tuple t.
t[Au, Av, …, Aw] refers to the _________ of
t containing the values of attributes Au, Av, …, Aw, respectively in t.
subtuple
__________ determine which values are permissible and which are not in the database.
constraints.
_____________ constraints, _________ constraints, and __________ constraints are the three main types of constraints.
Inherent or Implicit, Schema-based or Explicit, Application based or semantic.
___________ constraints are based on the data model itself. (E.g., relational model does not allow a list as a value for any attribute.)
Inherent or Implicit
___________ constraints are expressed in the schema by using the facilities provided by the model. (E.g., max. cardinality ratio constraint in the ER model.)
Schema-based or Explicit
___________ constraints are
beyond the expressive power of the model and must be
specified and enforced by the application programs.
Application based or semantic
T/F: Constraints are conditions that must hold on ALL valid
relation states.
true.
_______ constraints, _______ constraints, and _________ constraints are the three main types of explicit schema-based constraints that can be expressed in a relational model.
key, entity integrity, referential integrity.
Another schema-based constraint is the ______ constraint, in which every value in a tuple must be from the domain of its attribute. (or it could be null, if allowed for that attribute)
domain
The ________ of R is a set of attributes SK of R with the condition that is no two tuples in any valid relation state r(R) will have the same value for SK.
superkey
The _______ of R is “minimal” superkey.
key
T/F: A Key is a Superkey and vice versa.
fasle, but not vice versa.
T/F: A key is a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey (does not possess the superkey uniqueness property.)
true.
Any set of attributes that includes a key is a _____.
superkey.
If a relation has several candidate keys, one is chosen arbitrarily to be the ________.
primary key
(Choose as primary key the smallest of the candidate keys (in terms of size))
T/F: the primary key value provides the tuple identity.
true
T/F: The primary key is used to reference the tuple from another tuple.
true.
A set S of relation schemas that belong to the same database is the _________.
S is the name of the whole ________.
S = {R1, R2, …, Rn} and a set IC of integrity constraints.
R1, R2, …, Rn are the names of the individual ________ within the database S.
Relational Database Schema, database schema, relation schemas.
A ______________ DB of S is a set of relation states DB = {r1, r2, …, rm} such that each ri is a state of Ri and such that the ri relation states satisfy the integrity constraints specified in IC.
relational database state
A ____________ is sometimes called a relational database snapshot or instance.
relational database state.
A database state that does not meet the constraints is an _______ state.
invalid
T/F: The relational database state is a union of all the individual relation states.
true
T/F: Each relation will have many tuples in its current relation state.
true.
T/F: Whenever the database is changed, a new state arises.
true.
T/F: Basic operations for changing the database: INSERT a new tuple in a relation, DELETE an existing tuple from a relation, MODIFY an attribute of an existing tuple.
true.
________ integrity is the primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R).
entity
(This is because primary key values are used to identify the
individual tuples.)
T/F: Other attributes of R may be constrained to disallow null values, even though they are not members of the primary key.
true.
__________ integrity is a constraint involving two relations and is used to specify a relationship among tuples in two relations (the _______ relation, and the _______ relation).
referential, referencing, referenced.
Tuples in the referencing relation R1 have attributes _____________ that reference ____________ of the referenced relation R2.
FK (called foreign key attributes), the primary key attributes PK.
T/F: A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.
True.
The value in the foreign key column (or columns) FK of the the referencing relation R1 can be either: a _______, or a value of an existing ________.
null, primary key PK in the referenced relation R2.
T/F: A Null FK in R1 should not be a part of its own primary key.
true
T/F: Each relation schema can be displayed as a row of attribute names.
true.
Determine a FK and PK with a referential intergrity constraint in the following relational database schema figure:
FK: ESSN of DEPENDENT
PK: Ssn of EMPLOYEE
_____________ constrains is based on application semantics and cannot be expressed by the model per se.
Semantic Integrity Constraints
T/F: A constraint specification language may have to be
used to express the Semantic Integrity Constraints.
true.
T/F: Keys, Permissibility of Null values, Candidate Keys
(Unique in SQL), Foreign Keys, Referential Integrity etc.
are expressed by the CREATE TABLE statement in SQL.
True.
T/F: Integrity constraints should be not violated by the update operations.
true.
T/F: Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints.
true
In case of integrity violation, several actions can be taken such as…
- Cancel the operation that causes the violation (RESTRICT or REJECT option).
- Perform the operation but inform the user of the violation.
- Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option).
- Execute a user-specified error-correction routine.
Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, null, ‘1960-04-05’, ‘676 Katy Lane, TX’, F, 28000, null, 4> into EMPLOYEE.
this operation violates which integrity?
Insertion violates entity integrity constraint (PK is null).
T/F: INSERT and DELETE may violate any of the constraints.
False, DELETE may violate only referential integrity.
If the primary key value of the tuple being deleted is referenced from other tuples in the database. This causes a _________ integrity violation.
referential
T/F: A referential integrity violation by DELETE Can be remedied by several actions: RESTRICT, CASCADE, SET NULL, SET TO DEFAULT.
true.
RESTRICT option: reject the deletion.
CASCADE option: delete the referencing tuples too.
SET NULL option: set the foreign keys of the referencing tuples
to NULL.
SET TO DEFAULT: set the foreign keys of the referencing tuples
to DEFAULT value.
Delete the EMPLOYEE tuple with SSN = ‘999887777.’’
Which integrity constraint does this operation violate?
Operation violates referential integrity constraint because there is a tuple in WORK_ON that references this tuple.
Delete the WORKS_ON tuple with ESSN = ‘999887777’’ and PNO = 10.
Which integrity constraint does this operation violate?
Operation does not violate any constraint.
UPDATE may violate ______ constraint and ________ constraint on an attribute being modified.
domain, NOT NULL.
T/F: Any constraints may also be violated, depending on the attribute being updated.
true.
Update the Salary of the EMPLOYEE tuple with SSN = ‘999887777’ to 27000
Which integrity constraint does this operation violate?
Operation does not violate any constraint.
Update the SSN of the EMPLOYEE tuple with SSN = ‘999887777’ to ‘987654321’
Which integrity constraint does this operation violate?
Update violates key constraints and referential integrity constraint.
Update the DNO of the EMPLOYEE tuple with SSN = ‘999887777’ to 7
Which integrity constraint does this operation violate?
Update violates referential integrity constraint.
Insert <‘Alicia’, ‘J’, ‘Zane’, ‘999887777’, ‘1960-04-05’, ‘674 Windy Lane, TX’, F, 28000, ‘987654’, 4> into EMPLOYEE
Which integrity constraint does this operation violate?
Insertion violates key constraint (duplicate PK).
Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘677678’, ‘1960-04-05’, ‘676 Katy Lane, TX’, F, 28000, ‘987654’, 7> into EMPLOYEE
Which integrity constraint does this operation violate?
Insertion violates referential integrity constraint (FK references a PK
that does not exist).
