CH5 The Relational Data Model and Relational Database Constraints

Question 1

Define relation schema

Answer 1

• list of attri- butes that describe the structure of a relation
• A relation schema2 R, denoted by R(A1, A2, … , An), is made up of a relation name R and a list of attributes, A1, A2, … , An. Each attribute Ai is the name of a role played by some domain D in the relation schema R.
• Ex: STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa)

Question 2

Define relation state,

Answer 2

• set of tuples that conforms to the schema
• A relation (or relation state)4 r of the relation schema R(A1, A2, … , An), also denoted by r(R), is a set of n-tuples r = {t1, t2, … , tm}. Each n-tuple t is an ordered list of n valuest=,whereeachvaluevi,1≤i≤n,is

Question 3

5.6. Discuss the characteristics of relations that make them different from ordi- nary tables and files.

Answer 3

• the first is that a relation is not sensitive to the ordering of tuples.
• The second involves the ordering of attributes in a relation schema and the corresponding ordering of val- ues within a tuple. n abstract level, since there really is no reason to prefer having one attribute value appear before another in a tuple.

Question 4

5.7. Discuss the various reasons that lead to the occurrence of NULL values in relations.

Answer 4

• val- ues in tuples and introduced NULL values to represent missing or unknown infor- mation.
• value unknown, value exists but is not available, or attribute does not apply
• NULL values should be avoided as much as possible.

Question 5

5.8. Discuss the entity integrity and referential integrity constraints. Why is each considered important?

Answer 5

• entity integrity constraint, which prohibits primary key attri- butes from being NULL. Important because it let’s that every entity is identified
• the referential integrity constraint Informally, the referen- tial integrity constraint states that a tuple in one relation that refers to another rela- tion must refer to an existing tuple in that relation or be null

Question 6

Define tuple

Answer 6

a row is called a tuple

- Each value in a tuple is an atomic value; t

Question 7

Define attribute

Answer 7

, a column header is called an attribute,

Question 8

Define domain

Answer 8

• A domain D is a set of atomic values.
• A domain is thus given a name, data type, format and additional info (measurement unit)
• logical definitions of domains. Ex: Usa_phone_numbers. The set of ten-digit phone numbers valid in the United States.
• Data type describing the types of values that can appear in each column is represented by a domain of possible values

Question 9

Why are tuples in a relation not ordered?

Answer 9

• A relation is defined as a set of tuples. Math- ematically, elements of a set have no order among them
• Tuple ordering is not part of a relation definition because a relation attempts to rep- resent facts at a logical or abstract level.

Question 10

5.3. Why are duplicate tuples not allowed in a relation?

Answer 10

• In the formal relational model, a relation is defined as a set of tuples. By definition, all elements of a set are distinct; hence, all tuples in a relation must also be distinct.

Question 11

5.4. What is the difference between a key and a superkey?

Answer 11

• Difference is that the superkey may be a minimal superkey and the key must be a minimal superkey
• Example: l). Consider the STUDENT relation of Figure 5.1. The attribute set {Ssn} is a key of STUDENT because no two student tuples can have the same value for Ssn.8 Any set of attributes that includes Ssn—for example, {Ssn, Name, Age}—is a superkey. However, the superkey {Ssn, Name, Age} is not a key of STUDENT because removing Name or Age or both from the set still leaves us with a superkey.

Question 12

Define superkey

Answer 12

subsets of attributes of a relation schema R with the property that no two tuples in any relation state r of R should have the same combination of values for these attributes.

Question 13

Define key

Answer 13

• A key k of a relation schema R is a superkey of R with the additional property that removing any attri- bute A from K leaves a set of attributes K′ that is not a superkey of R any more.
• Uniqueness property and minimal superkey

Question 14

5.5. Why do we designate one of the candidate keys of a relation to be the pri- mary key?

Answer 14

• to identify tuples in the relation.
• s somewhat arbitrary; however, it is usually better to choose a primary key with a single attribute or a small number of attributes.

Question 15

Define relational database schema

Answer 15

• A relational database schema S is a set of relation schemas S = {R1, R2, … , Rm} and a set of integrity constraints IC.
• Example: a diagram of the tables, attributes and relations

Question 16

Define relational database state

Answer 16

• A relational database schema S is a set of relation schemas S = {R1, R2, … , Rm} and a set of integrity constraints IC. A relational database state10 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. Fi
• Example: Diagram with tables filled with data

Question 17

5.9. Define foreign key. What is this concept used for?

Answer 17

• A set of attributes FK in relation schema R1 is a foreign key of R1 that references relation R2 if it satisfies the following rules:
  1. The attributes in FK have the same domain(s) as the primary key attributes PK of R2; the attributes FK are said to reference or refer to the relation R2.
  2. A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is NULL. In the former case, we have t1[FK] = t2[PK], and we say that the tuple t1 references or refers to the tuple t2.
• Notice that a foreign key can refer to its own relation.
• Used: typically arise from the relationships among the entities represented by the relation schemas.
• Example: In the EMPLOYEE relation, the attribute Dno refers to the department for which an employee works; hence, we designate Dno to be a foreign key of EMPLOYEE referencing the DEPARTMENT relation.

Question 18

5.10. What is a transaction? How does it differ from an Update operation?

Answer 18

• A transaction is an executing program that includes some database operations, such as reading from the database, or applying inser- tions, deletions, or updates to the database.
• A single transaction is different because may involve any number of retrieval operations and any number of update operations, so may contains Update operations. Therefore, a transaction is a higher concept that can contain the other.