Relational Model

Chapter Outline

 Relational Model Concepts

 Relational Model Constraints and relational database schemas

 Update operations, transactions, and dealing with constraint

violations
Relational Model Concepts
 Represents data as a collection of relations

 Table of values

 Row - Represents a collection of related data values

 Table name and column names

 Interpret the meaning of the values in each row

 For example: STUDENT table

 each row represents facts about a particular student entity.

 The column names—Name, Student_number, Class, and

Major— specify how to interpret the data values in each row
 All values in a column are of the same data type.
 In the formal relational model terminology,
 a row is called a tuple
 a column header is called an attribute
 table is called a relation
Domains, Attributes, Tuples, and Relations
 Domain
 A domain D is a set of atomic values
 atomic - each value in the domain is indivisible
 method of specifying a domain is to specify a data type from
which the data values forming the domain are drawn.
 It is also useful to specify a name for the domain, to help in
interpreting its values
 Examples :
Usa_phone_numbers: The set of ten-digit phone numbers
valid in the United States.
 Names: The set of character strings that represent names
of persons.
 Social_security_numbers: The set of valid nine-digit Social
Security numbers.
 A data type or format is also specified for each domain
 For example, the data type for the domain
Usa_phone_numbers can be declared as a character string of
the form (ddd) ddd-dddd, where each d is a numeric digit and
the first three digits form a valid telephone area code.
Relation schema
 used for describing the structure of a relation

 A relation schema R, denoted by R(A1, A2, ...,An), is made up of

a relation name R and a list of attributes A1, A2, ...,An

 R is called the name of this relation

 Each attribute Ai is the name of a role played by some domain

D in the relation schema R.

 D is called the domain of Ai and is denoted by dom(Ai)

 The degree (or arity) of a relation is the number of attributes n

of its relation schema
 Example: A relation of degree seven, which stores information
about university students
STUDENT(Name, Ssn, Home_phone, Address, Office_phone,
Age, Gpa)
- Using the data type of each attribute, the definition is
sometimes written as:
STUDENT(Name: string, Ssn: string, Home_phone: string,

Address: string, Office_phone: string, Age: integer, Gpa:

real)
- domains for some of the attributes of the STUDENT relation:
dom(Name) = Names; dom(Ssn) = Social_security_numbers;
dom(HomePhone)=USA_phone_numbers,
dom(Office_phone) = USA_phone_numbers,
Relation (or relation state)
 A relation (or relation state) r of the relation schema by
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 values t =<v1, v2, ...,

vn>, where each value vi, 1 ≤i<≤n, is an element of dom

(Ai) or is a special NULL value.

Characteristics of Relations

1. Ordering of Tuples in a Relation

2. Ordering of Values within a Tuple and an Alternative

Definition of a Relation

3. Values and NULLs in the Tuples

4. Interpretation (Meaning) of a Relation

1.Ordering of Tuples in a Relation

 Relation defined as a set of tuples

 a relation is not sensitive to the ordering of tuples

2. 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

 An alternative definition of a relation can be given, making the

ordering of values in a tuple unnecessary
 According to this definition of tuple as a mapping, a tuple can be
considered as a set of (<attribute>, <value>) pairs, where each
pair gives the value of the mapping from an attribute Ai to a value
vi from dom(Ai)
 The ordering of attributes is not important, because the attribute
name appears with its value.
 Example :consider the relation student(rollno,name,age)
 Tuple t=<1,Raju,30>

or
 Tuple t=<(age,34),(name,Harsha),(rollno,2)>
3. Values and NULLs in the Tuples
 Each value in a tuple is atomic
 NULL values, which are used to represent the values of
attributes that may be unknown or may not apply to a tuple
 For example some STUDENT tuples have NULL for their office
phones because they do not have an office
 Another student has a NULL for home phone
 In general, we can have several meanings for NULL values, such
as value unknown, value exists but is not available, or
attribute does not apply to this tuple (also known as value
undefined).
4.Interpretation (Meaning) of a Relation
 The relation schema can be interpreted as a declaration or a
type of assertion.
 For example, the schema of the STUDENT relation asserts that,
in general, a student entity has a Rollno ,Name and Age.
 Each tuple in the relation can then be interpreted as a
particular instance of the assertion.
 For example, the first tuple asserts the fact that there is a
STUDENT whose Name is Vinay, Rollno is 3 and Age is 32.
Relational Model Notation

 Relation schema R of degree n is 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
 In general, the name of a relation schema such as STUDENT also
indicates the current set of tuples in that relation
 An attribute A can be qualified with the relation name R to
which it belongs by using the dot notation R.A—for example,
STUDENT.Name or STUDENT.Age.
Relational Model Constraints and Relational Database Schemas

Constraints

 Restrictions on the actual values in a database state

 Derived from the rules in the miniworld that the database represents

 Three main categories:

1. inherent model-based constraints or implicit constraints

- inherent in the data model

2. schema-based constraints or explicit constraints

- can be directly expressed in schemas of the data model

3. application-based or semantic constraints or business rules

- cannot be directly expressed in the schemas

- Expressed and enforced by application program

Domain Constraints

 specify that within each tuple, the value of each attribute A

must be an atomic value from the domain dom(A)
 Typically include:
 Numeric data types for integers and real numbers

 Characters

 Booleans

 Date, time, timestamp

 Other special data types
Key Constraints and Constraints on NULL Values
 No two tuples can have the same combination of values for all
their attributes.
 There are other 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
 Suppose that we denote one such subset of attributes by SK; then
for any two distinct tuples t1 and t2 in a relation state r of R, we
have the constraint that:

t1[SK]≠ t2[SK]

 such set of attributes SK is called a superkey of the relation

schema R
 superkey
- specifies a uniqueness constraint that 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
- satisfies two properties:
1. Two distinct tuples in any state of the relation cannot
have identical values for (all) the attributes in the key
2. It is a minimal superkey—that is, a superkey from which
we cannot remove any attributes and still have the
uniqueness constraint in condition 1 hold
 Example: Consider the STUDENT relation

 The attribute set {Ssn} is a key of STUDENT because no two

student tuples can have the same value for Ssn
 Any set of attributes that includes Ssn—for example, {Ssn,
Name, Age}—is a superkey
 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
 In general, any superkey formed from a single attribute is also a
key
 A key with multiple attributes must require all its attributes
together to have the uniqueness property
 Candidate key
- a relation schema may have more than one key. In this case,

each of the keys is called a candidate key

- For example, the CAR relation has two candidate keys:

License_number and Engine_serial_number

 Primary key of the relation

- Designated among candidate keys

- Underline attribute
 Other candidate keys are designated as unique keys and are
not underlined
 Another constraint on attributes specifies whether NULL
values are or are not permitted.
 For example, if every STUDENT tuple must have a valid, non-
NULL value for the Name attribute, then Name of STUDENT is
constrained to be NOT NULL.
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 R and such that the ri relation states

satisfy integrity constraints specified in IC

 Example: relational database schema

COMPANY = {EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS,

PROJECT, WORKS_ON, DEPENDENT}

Figure : Schema diagram for the COMPANY relational database schema.
Figure :One possible database state for the COMPANY relational database
schema.
Figure :One possible database state for the COMPANY relational database
schema.
 Invalid state

- Does not obey all the integrity constraints

 Valid state

- Satisfies all the constraints in the defined set of integrity

constraints IC
 Integrity constraints are specified on a database schema and
are expected to hold on every valid database state of that
schema.
 In addition to domain, key, and NOT NULL constraints, two
other types of constraints are considered part of the relational

model:

- entity integrity and

- referential integrity.
Integrity, Referential Integrity and Foreign Keys
 Entity integrity constraint

- states that no primary key value can be NULL

 Referential integrity constraint

- Specified between two relations

- Maintains consistency among tuples in two relations

- Informally - a tuple in one relation that refers to another relation
must refer to an existing tuple in that relation
- For example COMPANY database, the attribute Dno of EMPLOYEE
gives the department number for which each employee works;
hence, its value in every EMPLOYEE tuple must match the
Dnumber value of some tuple in the DEPARTMENT relation.
 Foreign key

- 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

 In the definition, R1 is called the referencing relation and R2 is
the referenced relation.
 A foreign key can refer to its own relation

- For example, the attribute Super_ssn in EMPLOYEE refers to

the supervisor of an employee; this is another employee,

represented by a tuple in the EMPLOYEE relation

- Hence, Super_ssn is a foreign key that references the

EMPLOYEE relation itself

- The tuple for employee ‘John Smith’ references the tuple

for employee ‘Franklin Wong,’ indicating that ‘Franklin
Wong’ is the supervisor of ‘John Smith.’
 We can diagrammatically display referential integrity
constraints by drawing a directed arc from each foreign key to
the relation it references.
 For clarity, the arrowhead may point to the primary key of the
referenced relation
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
State constraints(static constraints)
- Define the constraints that a valid state of the database must
satisfy
Transition constraints(dynamic constraints)
- Define to deal with state changes in the database
Update Operations, Transactions, and Dealing with
Constraint Violations
 The operations of the relational model can be categorized into
retrievals and updates
 There are three basic operations that can change the states of
relations in the database:

1.Insert - one or more new tuples in a relation

2. Delete- delete tuples

3. Update (or Modify)- change the values of some attributes in

existing tuples
 Whenever these operations are applied, the integrity constraints
specified on the relational database schema should not be
violated.
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
 Domain constraints
- if an attribute value is given that does not appear in the
corresponding domain or is not of the appropriate data type
 Key constraints
- if a key value in the new tuple t already exists in another tuple
in the relation r(R)
 Entity integrity
- if any part of the primary key of the new tuple t is NULL
 Referential integrity
- if the value of any foreign key in t refers to a tuple that does
not exist in the referenced relation
Operation:

Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, NULL, ‘1960-04-05’, ‘6357 Windy

Lane, Katy, TX’, F, 28000, NULL, 4>

Result: This insertion violates the entity integrity constraint

(NULL for the primary key Ssn), so it is rejected

Operation:

Insert <‘Alicia’, ‘J’, ‘Zelaya’, ‘999887777’, ‘1960-04-05’, ‘6357

Windy Lane, Katy, TX’, F, 28000, ‘987654321’, 4>

Result: This insertion violates the key constraint because

another tuple with the same Ssn value already exists in
the EMPLOYEE relation, and so it is rejected.
Operation:

Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘677678989’, ‘1960-04-05’, ‘6357

Windswept, Katy, TX’, F, 28000, ‘987654321’, 7>

Result: This insertion violates the referential integrity constraint

specified on Dno in EMPLOYEE because no corresponding
referenced tuple exists in DEPARTMENT with Dnumber =
7.

Operation:

Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘677678989’, ‘1960-04-05’, ‘6357

Windy Lane,Katy, TX’, F, 28000, NULL, 4>

Result:This insertion satisfies all constraints, so it is acceptable.

 If an insertion violates one or more constraints, the default
option is to reject the insertion
 it would be useful if the DBMS could provide a reason to the
user as to why the insertion was rejected
 An attempt to correct the reason for rejecting the insertion
The Delete Operation

 violate only referential integrity

 occurs if the tuple being deleted is referenced by foreign keys from

other tuples in the database

Operation:

Delete the WORKS_ON tuple with Essn = ‘999887777’ and Pno =10.
Result: This deletion is acceptable and deletes exactly one tuple.

Operation:

Delete the EMPLOYEE tuple with Ssn = ‘999887777’.

Result: This deletion is not acceptable, because there are tuples in
WORKS_ON that refer to this tuple. Hence, if the tuple in
EMPLOYEE is deleted, referential integrity violations will
result.
Operation:

Delete the EMPLOYEE tuple with Ssn = ‘333445555’

Result: This deletion will result in even worse referential integrity

violations, because the tuple involved is referenced by
tuples from the EMPLOYEE, DEPARTMENT, WORKS_ON, and
DEPENDENT relations.
 Several options are available if a deletion operation causes a
violation
1.restrict - is to reject the deletion
2.cascade, is to attempt to cascade (or propagate) the
deletion by deleting tuples that reference the tuple that is
being deleted
3.set null or set default - is to modify the referencing attribute
values that cause the violation; each such value is either set
to NULL or changed to reference another default valid tuple
The Update Operation

 used to change the values of one or more attributes in a tuple

(or tuples) of some relation R
 specify a condition on the attributes of the relation to select the
tuple (or tuples) to be modified
Operation:
Update the salary of the EMPLOYEE tuple with Ssn =
‘999887777’ to 28000.
Result: Acceptable.
Operation:
Update the Dno of the EMPLOYEE tuple with Ssn = ‘999887777’
to 7.
Result: Unacceptable, because it violates referential integrity.
Operation:

Update the Ssn of the EMPLOYEE tuple with Ssn =

‘999887777’ to ‘987654321’.
Result: Unacceptable, because it violates primary key constraint
by repeating a value that already exists as a primary key in
another tuple; it violates referential integrity constraints because
there are other relations that refer to the existing value of Ssn
The Transaction Concept
A transaction is an executing program that includes some
database operations, such as reading from the database, or
applying insertions, deletions, or updates to the database
 At the end of the transaction, it must leave the database in a
valid or consistent state that satisfies all the constraints
specified on the database schema
 A single transaction may involve any number of retrieval
operations and any number of update operations.
 These retrievals and updates will together form an atomic unit
of work against the database
 For example, a transaction to apply a bank withdrawal will
typically read the user account record, check if there is a
sufficient balance, and then update the record by the
withdrawal amount.
Question bank
1. Define the following terms as they apply to the relational model of data:

i) domain ii) attribute iii) n-tuple iv) relation schema v) relation state

vi) degree of a relation vii) relational database schema

viii) relational database state.

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

3. Discuss the various reasons that lead to the occurrence of NULL values

in

relations.

4. Discuss the characteristics of relations

5. Discuss the various restrictions on data that can be specified on a

relational database in the form of constraints.

6. Suppose that each of the following Update operations is applied directly
to the company database state. Discuss all integrity constraints violated by
each operation, if any, and the different ways of enforcing these
constraints.
a. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle
Rd,Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.
b. Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT.
c. Insert <‘Production’, 4, ‘943775543’, ‘2007-10-01’> into DEPARTMENT.
d. Insert <‘677678989’, NULL, ‘40.0’> into WORKS_ON.
e. Insert <‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘spouse’> into
DEPENDENT.
f. Delete the WORKS_ON tuples with Essn = ‘333445555’.
g. Delete the EMPLOYEE tuple with Ssn = ‘987654321’.
h. Delete the PROJECT tuple with Pname = ‘ProductX’.
i. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with
Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively.
j. Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn =
‘999887777’ to ‘943775543’.
k. Modify the Hours attribute of the WORKS_ON tuple with Essn =
Questions from university Question paper

1. Describe the characteristics of relations with suitable example

for each
---- 8 Marks (dec’17/ jan’18)
----- 6 Marks(dec’18/ jan’19)
2. Define the following :
i) Relation state
ii) Relation schema
iii) Arity
iv) Domain
----- 4 Marks(dec’18/ jan’19)