Relational Data Model

• Represents data as a collection of relations, also known as tables.
• Table of values.
  • Has a table name (relation name).
  • Has rows (tuples).
    • Each represents a collection of related data values.
    • Each typically corresponds to a real-world entity or relationship.
  • Has column names (attributes).
    • Each interprets the meaning of values in the row.

Domain

• Domain D is a set of atomic values.
• Atomic implies each value is indivisible.
• Specifying a domain requires data type specification.

Relation Schema R

• Denoted by R(A1, A2, ...,An).
• Consists of a relation name R and a list of attributes A1, A2, ..., An.

Attribute A

• A name of a role played by some domain D in the relation schema R.

Degree (or arity) of a Relation

• The number of attributes n of its relation schema.

Relation

• Also known as relation state.
• It is a set of n-tuples r = {t₁, t2, ..., tm}.
• Each n-tuple t: An ordered list of n values t = <v1, v2, ..., vn>.
  • Each value v₁, where 1 ≤ i ≤ n is an element of dom(A₁) or a special NULL value.

Mathematical Relation

• A relation can be described as a mathematical relation of degree n on the domains dom(A₁), dom(A2), ..., dom(An).
• It is a subset of the Cartesian product of the domains that define R:
  • r(R) ⊆ (dom(A₁) × dom(A2) × ... × dom(An)).

Characteristics of Relations

• Ordering of tuples in a relation does not matter.
  • Defined as a set of tuples which have no specific order.
• Ordering of values within a tuple is also generally not significant.
  • The order of attributes and values is not critically important.

Alternative Definition of a Relation

• A Tuple can be considered as a set of (, ) pairs.
• Each pair gives the value of the mapping from an attribute A; to a value v; from dom(A;).
• The first definition is used because attributes and values within tuples are conventionally ordered.

Values and NULLs in Tuples

• Each value in a tuple is atomic (indivisible).
• Composite and multivalued attributes are not allowed in the flat relational model, adhering to the First Normal Form assumption.
• Multivalued attributes must be represented by separate relations.
• Composite attributes: Represented only by simple component attributes in basic relational model.

NULL Values

• Used to represent values of attributes that are unknown or do not apply to a tuple.
• NULL values can have different meanings:
  • Value is unknown.
  • Value exists but is not available.
  • Attribute does not apply to this tuple (also known as value undefined).

Relational Model Notation

• Uppercase letters Q, R, S denote relation names.
• Lowercase letters q, r, s denote relation states.
• Letters t, u, v denote tuples.
• The name of a relation schema is, for example, STUDENT.
  • This indicates the current set of tuples in that relation.
• Notation, for example,: STUDENT(Name, Ssn, ...).
  • Is used to refer only to the relation schema.
• Attribute A can be qualified with the relation name R to which it belongs, using the dot notation R.A.

Relational Model Constraints

• Restrictions are actual values in a database state.
• Restrictions are derived from the rules which used in the database represents.
• Restrictions are divided into Domain Constraints, Key Constraints, Entity Integrity Constraint, Referential Integrity Constraint, and others.

Domain Constraints

• Check the datatypes for integers and real numbers, characters, booleans, fixed-length strings, variable-length strings, date, time, timestamp, money, and other special data types.

Key Constraints

• No two tuples in any state should have the same combination of values for all their attributes.

Superkey

• No two distinct tuples in any state r of R can have the same value for SK (unique value).

Key

• A minimal superkey of R.
• Removing any attribute A from K leaves a set of attributes that is not a superkey of R any more.

Key Satisfies Two Properties

• Two distinct tuples in any state of relation cannot have identical values for (all) attributes in key.
• Minimal superkey cannot remove any attributes and still have uniqueness constraint in above condition hold.

Candidate Key

• A relation schema may have more than one key.

Primary Key

• The key that is the underlined attribute, and has unique keys in a relation.

Integrity Restrictions

• Entity Integrity Constraint: No primary key value can be NULL.
• Referential Integrity Constraint: Specified between two relations which maintains consistency among tuples in two relations.
• These restrictions mean the following. A row in a certain relationship when associated (refers) to the other relationship must indicate the row exists in this relationship.

Foreign Key Rules

• Rules include that the attributes in FK have the same domain(s) as the primary key attributes PK.
• Value of FK in a tuple t₁ of the current state r₁(R₁) either occurs as a value of PK for some tuple t₂ in the current state r₂(R2) or is NULL.
• Diagrammatically display referential integrity constraints directed the arc from each foreign key to the relation it references.
• All integrity constraints should be specified on relational database schema.

Foreign Keys

• Dashed lines are placed under foreign keys.

Other Types of Constraints

• Functional Dependency Constraint: Establishes a functional relationship between two sets of attributes X and Y. The value of X determines a unique value of Y.
• State Constraints: Define the constraints that a valid state of the database must satisfy.
• Transition Constraints: Define the constraints to deal with state changes in the database.

Update Operations

• Operations of the relational model can be categorized into retrievals and updates.
• Basic operations that change the states of relations in the database: insert, delete.
• Update: Necessary to specify a condition on attributes of relation, and to select the tuple (or tuples) to be modified.

Update If Attributes Not Part of a Primary Key Nor of a Foreign Key

• Usually, Update causes no problems.
• Updating a primary/foreign key has similar issues as with Insert/Delete.

Relational Database Schema

• Is the set of relation schemas S = {R1, R2, ..., Rm}.
• The set of integrity constraints is IC.

Relational Database State

• Is the set of relation states DB = {r1, r2, ..., rm}.
• Each r; is a state of R, and such that the r; relation states satisfy integrity constraints specified in IC.
• Invalid state does not obey all the integrity constraints.
• Valid state satisfies all the constraints in the defined set of integrity constraints IC.

Insert Operation

• Provides a list of attribute values for a new tuple t that is to be inserted into a relation R.
• Can violate any of the four types of constraints.
• If an insertion violates one or more constraints, the default option is to reject the insertion.

Delete Operation

• Can violate only referential integrity.
• If tuple being deleted is referenced by foreign keys from other tuples:
  • Restrict, reject the deletion.
  • Cascade, propagate the deletion by deleting tuples that reference the tuple that is being deleted.
  • Set null or set default, modify the referencing attribute values that cause the violation.

Transaction

• Executing program and includes some database operations.
• Must leave the database in a valid or consistent state.

Online Transaction Processing (OLTP) Systems

• Execute transactions at rates that reach several hundred per second.