Basic Relational Model
References:
Chapter 3: The Relational Data Model &
Relational Database Constraints
From Elmasri and avathe; Pearson ew
International Edition, 6th Edition
1

Outline
• The Relational Data Model and
Relational Database Constraints
• Relational Model Constraints and Relational Database Schemas
• Update Operations, Transactions, and Dealing with Constraint Violations

2

The Relational Data Model and
Relational Database Constraints
• Relational model
– First commercial implementations available in early 1980s
– Has been implemented in a large number of commercial systems

• Hierarchical and network models
– Preceded the relational model

3

Relational Model Concepts
• Represents data as a collection of relations
• Table of values
– Row
• Represents a collection of related data values
• Fact that typically corresponds to a real-world entity or relationship
• Tuple

– Table name and column names
• Interpret the meaning of the values in each row attribute 4

Relational Model Concepts (contd)

5

Domains, Attributes,
Tuples, and Relations
• Domain D
– Set of atomic values

• Atomic
– Each value indivisible

• Specifying a domain
– Data type specified for each domain

6

Domains, Attributes,
Tuples, and Relations (contd)
• Relation schema R
– Denoted by R(A1, A2, ...,An)
– Made up of a relation name R and a list of attributes, A1, A2, ..., An

• Attribute Ai
– Name of a role played by some domain D in the relation schema R

• Degree (or arity) of a relation
– Number of attributes n of its relation schema
7

Domains, Attributes,
Tuples, and Relations (contd)
• Relation (or relation state)
– Set of n-tuples r = {t1, t2, ..., tm}
– Each n-tuple t >
• Ordered list of n values t =
• Each value vi, 1 ≤ i ≤ n, is an element of dom(Ai) or is a special NULL value

8

Domains, Attributes, Tuples, and Relations (contd)
• Relation (or relation state) r(R)
– Mathematical relation of degree n on the domains dom(A1), dom(A2), ..., dom(An)
– Subset of the Cartesian product of the domains that define R:
• r(R) ⊆ (dom(A1) × dom(A2) × ... × dom(An))

9

Domains, Attributes, Tuples, and Relations (contd)
• Cardinality
– Total number of values in domain

• Current relation state
– Relation state at a given time
– Reflects only the valid tuples that represent a particular state of the real world

• Attribute names
– Indicate different roles, or interpretations, for the domain
10

Characteristics of Relations
• Ordering of tuples in a relation
– Relation defined as a set of tuples
– Elements have no order among them

• Ordering of values within a tuple and an alternative definition of a relation
– Order of attributes and values is not that important – As long as correspondence between attributes and values maintained
11

Characteristics of Relations
(contd)
• Alternative definition of a relation
– Tuple considered as a set of (, ) pairs – Each pair gives the value of the mapping from an attribute Ai to a value vi from dom(Ai)

• Use the first definition of relation
– Attributes and the values within tuples are ordered
– Simpler notation
12

Characteristics of Relations (contd)

Characteristics of Relations
(contd)
• Values and NULLs in tuples
– Each value in a tuple is atomic
– Flat relational model
• Composite and multivalued attributes not allowed
• First normal form assumption

– Multivalued attributes
• Must be represented by separate relations

– Composite attributes
• Represented only by simple component attributes in basic relational model
14

Types of Attributes (1)
• Simple
– Each entity has a single atomic value for the attribute. For example, SSN or Sex.

• Composite
– Attribute may be composed of several components.
For example:
• Address (Apt#, House#, Street, City, State, ZipCode, Country), or
• Name (FirstName, MiddleName, LastName).

• Composition may form a hierarchy where some components are themselves composite.

• Multi-valued
– An entity may have multiple values for that attribute. For example, Color of a CAR or PreviousDegrees of a
STUDENT.
• Denoted as {Color} or {PreviousDegrees}.
Slide 3- 15

Types of Attributes (2)
• In general, composite and multi-valued attributes may be nested arbitrarily to any number of levels, although this is rare.
– For example, PreviousDegrees of a STUDENT is a composite multi-valued attribute denoted by
{PreviousDegrees (College, Year, Degree,
Field)}
– Multiple PreviousDegrees values can exist
– Each has four subcomponent attributes:
• College, Year, Degree, Field
Slide 3- 16

Example of a Composite Attribute

Slide 3- 17

Characteristics of Relations
(contd)
• NULL values
– Represent the values of attributes that may be unknown or may not apply to a tuple
– Meanings for ULL values
• Value unknown
• Value exists but is not available
• Attribute does not apply to this tuple
(also known as value undefined)

18

Characteristics of Relations
(contd)
• Interpretation (meaning) of a relation
– Assertion
• Each tuple in the relation is a fact or a particular instance of the assertion

– Predicate
• Values in each tuple interpreted as values that satisfy predicate

19

Relational Model Notation
• Relation schema R of degree n
– Denoted by R(A1, A2, ..., An)

• Uppercase letters Q, R, S
– Denote relation names

• Lowercase letters q, r, s
– Denote relation states

• Letters t, u, v
– Denote tuples
20

Relational Model Notation
• Name of a relation schema: STUDENT
– Indicates the current set of tuples in that relation • Notation: STUDENT(Name, Ssn, ...)
– Refers only to relation schema

• Attribute A can be qualified with the relation name R to which it belongs
– Using the dot notation R.A
21

Relational Model Notation
• n-tuple t in a relation r(R)
– Denoted by t =
– vi is the value corresponding to attribute Ai

• Component values of tuples:
– t[Ai] and t.Ai refer to the value vi in t for attribute Ai
– t[Au, Aw, ..., Az] and t.(Au, Aw, ..., Az) refer to the subtuple of values from t corresponding to the attributes specified in the list 22

Relational Model Constraints
• Constraints
– Restrictions on the actual values in a database state – Derived from the rules in the miniworld that the database represents

• Inherent model-based constraints or implicit constraints
– Inherent in the data model

23

Relational Model Constraints (contd)
• Schema-based constraints or explicit constraints – Can be directly expressed in schemas of the data model • Application-based or semantic constraints or business rules
– Cannot be directly expressed in schemas
– Expressed and enforced by application program
24

Domain Constraints
• Typically include:
–
–
–
–
–
–
–
–

Numeric data types for integers and real numbers
Characters
Booleans
Fixed-length strings
Variable-length strings
Date, time, timestamp
Money
Other special data types
25

Key Constraints and Constraints on NULL Values
• No two tuples can 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

• Key
– Superkey of R
– Removing any attribute A from K leaves a set of attributes K that is not a superkey of R any more
26

Key Constraints and Constraints on NULL Values (contd)
• 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

27

Key Constraints and Constraints on NULL Values (contd)
• Candidate key
– Relation schema may have more than one key

• Primary key of the relation
– Designated among candidate keys
– Underline attribute

• Other candidate keys are designated as unique keys
28

Key Constraints and Constraints on
NULL Values (contd)

Relational Databases and
Relational Database Schemas
• Relational database schema S
– Set of relation schemas S = {R1, R2, ..., Rm}
– Set of integrity constraints IC

• Relational database state
– Set of relation states DB = {r1, r2, ..., rm}
– Each ri is a state of Ri and such that the ri relation states satisfy integrity constraints specified in IC
30

Relational Databases and Relational
Database Schemas (contd)
• Invalid state
– Does not obey all the integrity constraints

• Valid state
– Satisfies all the constraints in the defined set of integrity constraints IC

31

Integrity, Referential Integrity, and Foreign Keys
• Entity integrity constraint
– No primary key value can be NULL

• Referential integrity constraint
– Specified between two relations
– Maintains consistency among tuples in two relations 32

Integrity, Referential Integrity, and Foreign Keys (contd)
• Foreign key rules:
– The attributes in FK have the same domain(s) as the primary key attributes PK
– 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

33

Integrity, Referential Integrity, and Foreign Keys (contd)
• Diagrammatically display referential integrity constraints
– Directed arc from each foreign key to the relation it references

• All integrity constraints should be specified on relational database schema

34

Other Types of Constraints
• Semantic integrity constraints
– May have to be specified and enforced on a relational database
– Use triggers and assertions
– More common to check for these types of constraints within the application programs

35

Other Types of Constraints
(contd)
• Functional dependency constraint
– Establishes a functional relationship among two sets of attributes X and Y
– 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 to deal with state changes in the database 36

Update Operations, Transactions, and Dealing with Constraint
Violations
• 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 (or Modify)
37

Example COMPANY Database
• We need to create a database schema design based on the following (simplified) requirements of the COMPANY Database:
– The company is organized into
DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager.
A department may have several locations.
– Each department controls a number of
PROJECTs. Each project has a unique name, unique number and is located at a single location.
Slide 3- 38

COMPANY Database (Contd.)
– We store each EMPLOYEE’s social security number, address, salary, sex, and birthdate.
• Each employee works for one department but may work on several projects.
• We keep track of the number of hours per week that an employee currently works on each project.
• We also keep track of the direct supervisor of each employee. – Each employee may have a number of
DEPENDENTs.
• For each dependent, we keep track of their name, sex, birthdate, and relationship to the employee.
Slide 3- 39

40

41

42

The 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 – Default option is to reject the insertion

43

The 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 44

The Update Operation
• Necessary to specify a condition on attributes of relation
– Select the tuple (or tuples) to be modified

• If attribute not part of a primary key nor of a foreign key
– Usually causes no problems

• Updating a primary/foreign key
– Similar issues as with Insert/Delete
45

The Transaction Concept
• Transaction
– Executing program
– 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
46

Summary
• Characteristics differentiate relations from ordinary tables or files
• Classify database constraints into:
– Inherent model-based constraints, explicit schema-based constraints, and applicationbased constraints

• Modification operations on the relational model: – Insert, Delete, and Update
47