The Relational Data Model and Relational Database Constraints

1
The Relational Data Model and Relational
Database Constraints
2
Outline

• Relational Model Concepts
• Characteristics of Relations
• Relational Model Constraints
• Key Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Update Operations on Relations .

3
Relational Model Concepts

• Basis of the model
• The relational model of data is based on the
  concept of a Relation.
• A Relation is a mathematical concept based on the
  ideas of sets.
• The strength of the relational approach to data
  management comes from the formal foundation
  provided by the theory of relations.
• We review the essentials of the relational
  approach in this chapter.

4
Formal Definitions

• Domain
• A set of atomic values
• Given with a name, data type, and format
• Attribute
• The name of a role played by some domain D
• D is called the domain of Ai dom(Ai)

5
Formal Definitions

• Relation schema
• Defined over attributes A1, A2, , An
• Denoted R(A1,A2, ,An)
• n is the degree (arity) of the relation
• N-tuple
• An ordered list of n values
• Denoted t ltv1,v2,,vngt
• vi is an element of some domain Ai, denoted tAi

6
Formal Definitions

• Relation
• A relation (or relation state) r of R(A1,A2,,An)
  is a set of n-tuples r t1,t2,,tm.
• Each value vi is an element of dom(Ai).
• Denoted r(R)

7
Example

• CUSTOMER (Cust-id, Cust-name, Address, Phone)
• CUSTOMER is a relation defined over the four
  attributes Cust-id, Cust-name, Address, Phone,
• Each attribute has a domain or a set of valid
  values. E.g., the domain of Cust-id is 6 digit
  numbers.
• lt632895, "John Smith", "101 Main St. Atlanta, GA
  30332", "[phone]"gt, for instance, is a
  tuple belonging to the CUSTOMER relation.
• A relation (table) may be regarded as a set of
  tuples (rows).
• Attributes of the relation correspond to columns
  in the table.

8

• Figure 5.1 The attributes and tuples of a
  relation STUDENT.

9
Mathematical Relation

• Relation r(R) where R(A1, A2, ..., An)
• Mathematical relation of degree n on the domains
  dom(A1), dom(A2), , dom(An).
• A subset of the Cartesian product of the domains
• r(R) ? dom(A1) dom(A2) .... dom(An)
• The Cartesian product specifies all possible
  combinations of values from the underlying
  domains
• dom(A1) dom(A2) .... dom(An)
• r(R) is a set of valid tuples.

10
Example

• Let S1 0,1, S2 a,b,c.
• Let r(R) ? S1 S2
• For example r(R) lt0.agt, lt0,bgt, lt1,cgt
• r(R) a specific "value" or population of R.
• R is also called the intension of a relation
• r is also called the extension of a relation

11
Definition Summary

• Informal Terms Formal Terms
• Table Relation
• Column Attribute/Domain
• Row Tuple
• Values in a column Domain
• Table Definition Relation Schema
• Populated Table Extension

12
Characteristics of Relations

• Ordering of tuples
• Tuple ordering is not part of a relation
  definition
• Tuples are not considered to be ordered, even
  though they appear to be in the tabular form.
• Logical orders can be specified, only to
  facilitate functions such as searching.
• Ordering of attributes
• Attributes in R(A1, A2, ..., An) and the values
  in tltv1,v2, ..., vngt are considered to be
  ordered.
• Alternative definition of relation is possible

13

• Figure 5.2 The relation STUDENT from Figure 5.1,
  with a different order of tuples.

14
Characteristics of Relations

• Values in a tuple
• All values are considered atomic (indivisible).
• Multivalued attributes must be represented by
  separate relations.
• Composite attributes are represented only by
  their simple component attributes.
• A special null value is used to represent values
  that are unknown or inapplicable to certain
  tuples.

15
Characteristics of Relations

• Interpretation of a relation
• Relations represent facts about entities and
  relationships
• How to converse EER constructs to relations
• Notation
• Relation name Q, R, S
• Relation states q, r, s
• Tuples t, u, v
• tAi vi (the value of attribute Ai for tuple
  t).
• tAu,Av,...,Aw refers to the subtuple of t
  containing the values of attributes Au, Av, ...,
  Aw, respectively.

16
Relational Model Constraints

• Categories
• Inherent model-based constraints
• e.g. no duplicate tuples
• Schema-based constraints
• Expressed in the schema
• Application-based constraints
• Must be checked within application programs
• Data dependencies
• Used in the design of a relational database

17
Relational Model Constraints

• Constraints
• Conditions that must hold on all valid relation
  instances.
• Types of constraints
• Domain constraints
• Key constraints
• Entity integrity constraints
• Referential integrity constraints

18
Domain Constraints

• Domain constraints
• The value of each attribute A must be an atomic
  value from dom(A)
• Typical data types integers, real numbers,
  characters, booleans, strings

19
Key Constraints

• Superkey of R
• A set of attributes SK of R, that specifies a
  uniqueness constraint
• No two tuples in any valid relation instance r(R)
  will have the same value for SK. That is, for
  any distinct tuples t1 and t2 in r(R), t1SK ?
  t2SK.
• Key of R
• A "minimal" superkey that is, a superkey K such
  that removal of any attribute from K results in a
  set of attributes that is not a superkey.

20
Key Constraints

• Example The CAR relation schema
• CAR(State, Reg, SerialNo, Make, Model, Year)
• has two keys Key1 State, Reg, Key2
  SerialNo, which are also superkeys. SerialNo,
  Make is a superkey but not a key. 
• If a relation has several candidate keys, one is
  chosen arbitrarily to be the primary key. The
  primary key attributes are underlined.

21

• Figure 5.4 The CAR relation with two candidate
  keys
• LicenseNumber and EngineSerialNumber.

22
Entity Integrity

• Relational Database Schema
• A set S of relation schemas that belong to the
  same database. S is the name of the database.
• S R1, R2, ..., Rn
• Entity Integrity
• The primary key attributes PK of each relation
  schema R in S cannot have null values in any
  tuple of r(R). This is because primary key values
  are used to identify the individual tuples.
• tPK ? null for any tuple t in r(R)

23
Figure 5.5 Schema diagram for the COMPANY
relational database schema the primary keys are
underlined.
24
Figure 5.6 One possible relational database state
corresponding to the COMPANY schema.
25
(No Transcript)
26
Referential Integrity

• A constraint involving two relations (the
  previous constraints involve a single relation).
• Used to specify a relationship among tuples in
  two relations the referencing relation and the
  referenced relation.
• Tuples in the referencing relation R1 have
  attributes FK (called foreign key attributes)
  that reference the primary key attributes PK of
  the referenced relation R2. A tuple t1 in R1 is
  said to reference a tuple t2 in R2 if t1FK
  t2PK.
• A referential integrity constraint can be
  displayed in a relational database schema as a
  directed arc from R1. FK to R2.

27
Figure 5.7 Referential integrity constraints
displayed on the COMPANY relational database
schema diagram.
28
Update Operations

• Operations
• INSERT, DELETE, MODIFY form a new relation.
• Applying algebraic operators (relational algebra)
• Defining the new relation (relational calculus)
• Constraints
• Update operations should not violate integrity
  constraints
• Several update operations may have to be grouped
  together.
• Updates may propagate to cause other updates
  automatically. This may be necessary to maintain
  integrity constraints.

29
Constraint Violations

• In case of integrity violation
• Cancel the operation that causes the violation
  (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