define: domain
D; domain of A_i = dom(A_i)
a set of atomic values;
[atomic: each value in the domain is indivisible as far as the formal relational model is concerned]
a common method of specifying a domain is to specify a data type from which the data values forming the domain are drawn;
in the formal relational model terminology: a domain of possible values represents the data type describing the types of values that can appear in each column;
a domain is given a name, data type, and format; additional info for interpreting the values of a domain can also be given
define: attribute
A; each attribute=A_i
the name of a role played by some domain D in the relation schema R;
in the formal relational model terminology: an attribute is a column header
define: n-tuple
t
each n-tuple t is an ordered list of n values t=, where each value v_i, 1 <= i <= n, is an element of dom(A_i) or is a special NULL value
define: relation schema
R; R(A_1, A_2, …, A_n)
a relation schema is made up of a relation name R and a list of attribute A_1, A_2, …, A_n.
a relation schema is used to describe a relation; R is the name of this relation
define: relation state
r; r(R)
AKA relation; AKA relation instance
a relation (or relation state) r of the relation schema R(A_1, A_2, …, A_n) is a set of n-tuples r = {t_1, t_2, …, t_m}.
define: degree of a relation
n
AKA arity
the degree or arity of the relation is the number of attributes n of its relation schema R
define: relational database schema
S
a relational database schema S is a set of relation schemas S = {R_1, R_2, …, R_m} and a set of integrity constraints IC.
define: relational database state
DB
AKA relational database snapshot AKA relational database instane
a relational database state DB of S is a set of relation state DB = {r_1, r_2, …, r_m} such that each r_i is a state of R_i and such that the r_i relation states satisfy the integrity constraints specified in IC
Why is tuple ordering not part of a relation definition?
a relation is defined as a set of tuples and mathematically, elements of a set have no order among them, so tuples in a relation do not have any particular order;
Tuple ordering is not part of a relation definition because a relation attempts to represent facts at a logical or abstract level. Many tuple orders can be specified on the same relation.
The definition of a relation does not specify any particular order: There is no preference for one ordering over another.
Why are duplicate tuples not allowed in a relation?
Duplicate tuples are not allowed in a relation because a relation is defined as a set of tuples and mathematically, sets do not contain duplicates.
A key is a superkey but not vice versa. Explain this statement with the help of an example.
A superkey SK is a set of attributes that specifies a uniqueness constraint that no two distinct tuples in any state r of R can have the same value for SK. Every relation has by default at least one superkey: the set of all its attributes.
A key k of a relation schema R is a superkey of R with the additional property that removing any attribute A from K leaves a set of attributes K’ that is not a superkey of R any more.
a key satisfies two properties:
1. uniqueness property: two distinct tuples in any state of the relation cannot have identical values for all the attributes in the key; this also applies to a superkey
2. minimality property: a key is a minimal superkey; a superkey that we can’t remove any more attrs from and still have its uniquess constraint be true; this is required for a key and optional for a superkey
ex:
|| firstname || lastname || address || sex || SSN || DOB ||
| John | Smith | 123 Main St | M | 123-45-6789 | 01-05-1970 |
| Sarah | Smith | 123 Main St | F | 555-55-5555 | 09-18-1972 |
| John | Smith | 123 Main St | M | 987-65-4321 | 06-12-2003 |
| Jill | Smith | 123 Main St | F | 012-98-456 | 06-12-2003 |
superkeys:
- {firstname, lastname, address, sex, SSN, DOB}
- {SSN, DOB, firstname}
- etc
keys:
- {SSN}
- {DOB, sex} [kinda just happens to be true here; depending on scope of DB its totally possible that this wouldn’t hold, (ex: if you had data from multiple families]
What is the difference between candidate key, primary key, and unique key?
candidate key: any key of a relation
primary key: the candidate key whose values are used to identify tuples in the relation
unique key: the remaining candidate keys that are not the primary key of a relation
Discuss the various reasons that lead to the occurrence of NULL values in relations.
NULL values are used to represent the values of attributes that may be unknown or may not apply to a tuple.
several meanings for NULL values:
- value unknown
- value exists but is not available
- attribute does not apply (AKA value undefined)
Discuss the entity integrity and referential integrity constraints. Why is each considered important?
- entity integrity: no primary key can be NULL.
This is because the primary key value is used to identify individual tuples in a relation; so if you had a NULL value for a primary key then you wouldn’t be able to identify some tuples or distinguish them (ex: if you had 2 tuples with NULL PKs in a relation).
specified on individual relations - referential integrity: states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation.
used to maintain the consistency among tuples in the relations; states that a ty
specified between two relations
if the two conditions of a FK hold then a referential integrity constraint holds between relation R_1 and R_2.
1. the attrs in FK have the same domains as the PK attrs of R2
2. a value of FK in t1 of the current state r_1(R_1) either occurs as a value of PK for some tuple t_2 in the current state r_2(R_2) (((so, t1[FK] = t2[PK] )))
or is NULL.
What are the rules that must be satisfied by the foreign key?
a foreign key specifies a referential integrity constraint between two relations;
a set of attrs FK in relation schema R_1 is a foreign key of R_1 that references relation R_2 if it satisfies the following rules:
1. the attributes in FK have the same domains as the primary key attributes PK of R_2; the attributes FK are said to reference or refer to the relation R_2
1. a value of FK in a tuple t_1 of the current state r_1(R_1) either occurs as a value of PK for some tuple t_2 in the current state r_2(R_2) or is NULL. In the former case we have t_1[FK] = t_2[PK], and we say that the tuple t_1 references or refers to the tuple t_2.
What are the basic operations that can change the states of relations in the database? Explain briefly.
INSERT: insert a list of attribute values for a new tuple into a relation
DELETE: remove a tuple from a relation
UPDATE: modify the attribute(s) of an existing tuple(s) in a relation
What types of constraints can be violated during database modifications?
- domain constraints: specify that within each tuple, the value of each attr A must be an atomic value from the domain dom(A)
- key constraints: 1) uniqueness property: two distinct tuples in any state of the relation cannot have identical values for all the attributes in the key; 2) minimality property: a superkey from which we cannot remove any attributes and still have the uniqueness constraint hold
- another constraint specifies whether NULL values are permitted or not
- entity integrity constraints: no primary key can be NULL
- referential integrity constraints: a tuple in one relation that refers to another relation must refer to an existing tuple in that relation
Which constraints can be violated during an insert operation?
How can you handle this type of violation?
- domain constraints
- key constraints
- entity integrity constraints
- referential integrity constraints
option 1 (default): restrict/reject the insertion option 2: try to correct the reason for rejecting the insertion; *can cascade back to other relations though
Which constraints can be violated during a delete operation?
How can you handle this type of violation?
- referential integrity constraints
option 1: restrict/reject the deletion
option 2: cascade: propagate deletion by deleting tuples that reference the tuple
option 3: set NULL/default: set referencing attrs to NULL or a default value; *can violate entity integrity constraints though if a referencing attr is part of a PK
Which constraints can be violated during an update operation?
How can you handle this type of violation?
- domain constraints
- key constraints
- entity integrity constraints
- referential integrity constraints
- if updating a PK attr: basically the same as delete + insert
options to fix are similar to delete:
option 1: restrict
option 2: cascade
option 3: set NULL/default
