The relational model represents the database as a
collection of relations.
When a relation is thought of as a table of values,
each row in the data corresponds to the real world
entity or relationship.
In formal relational model terminology:
A row is called a tuple.
A column header is called an attribute.
The table is called a Relation.
A Domain D is a set of atomic values.
Atomic: the values are indivisible.
A common method of specifying the domain is to
specify a data type from which the data values
forming the domain are drawn.
Example:
India_Mob_Numbers: Set of 10 digit mobile
numbers valid in India.
Name: Set of character strings that represent
names of a person.
Dept names or dept codes.
Domain is also specified in terms of data type and
formats.
Domain for India_Mobile_Numbers can be
declared as data type character string of the form
+dd-dddddddddd.
Dates have various formats such as year, month,
date formatted as yyyy-mm-dd, or as dd mm,yyyy
etc.
The domain is thus given a name, data type and
format.
The Schema (or description) of a Relation:
Denoted by R(A1, A2, .....An)
R is the name of the relation
The attributes of the relation are A1, A2, ..., An
The degree of the relation is the number of
attributes of its relation schema.
Example:
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
CUSTOMER is the relation name
Defined over the four attributes: Cust-id, Cust-
name, Address, Phone#
Each attribute has a domain or a set of valid
values.
For example, the domain of Cust-id is 6 digit
numbers.
Using data types of each attribute, the definition is
sometimes written as:
CUSTOMER( Cust-Id: integer, Cust-Name:
string, Address: string, Phone number: string)
Domains can be specified as:
dom(Cust-id)= Customer_Id
dom(Cust-name)= Customer_Name
dom(Address)= Customer_Address
dom(Phone number)=India_Phone_Number
Customer_Id: Set of 6 digit numbers.
Customer_Name: Set of character strings that
represents name of the customers.
Customer_Address: Set of character strings that
represents where the customer resides.
India_Phone_Number: Set of 10 digit mobile
numbers valid in India.
A relation r of the relation schema R(A1, A2, .....An)
also denoted as r(R), is a set of n-tuples
r={t1,t2,…..,tm}.
Each n-tuple t is an ordered set of values
(enclosed in angled brackets ‘< … >’) i.e
t=<v1,v2…….vn>
Each value is derived from an appropriate domain.
A row in the CUSTOMER relation is a 4-tuple and
would consist of four values, for example:
<632895, "John Smith", "101 Main St. Atlanta, GA
30332", "(404) 894-2000">
A relation is a set of such tuples (rows)
The ith value in a tuple t, which corresponds to the
attribute Ai, is referred to as t[Ai] or t.Ai.
The relation state is a subset of the Cartesian
product of the domains of its attributes
each domain contains the set of all possible
values the attribute can take.
Example: attribute Cust-name is defined over the
domain of character strings of maximum length 25
dom(Cust-name) is varchar(25)
The role these strings play in the CUSTOMER
relation is that of the name of a customer.
Formally,
Given R(A1, A2, .........., An)
r(R) dom (A1) X dom (A2) X ....X dom(An)
R(A1, A2, …, An) is the schema of the relation
R is the name of the relation
A1, A2, …, An are the attributes of the relation
r(R): a specific state of relation R – this is a set of
tuples (rows)
r(R) = {t1, t2, …, tn} where each ti is an n-tuple
ti = <v1, v2, …, vn> where each vj element-of
dom(Aj)
Let R(A1, A2) be a relation schema:
Let dom(A1) = {0,1}
Let dom(A2) = {a,b,c}
Then: dom(A1) X dom(A2) is all possible
combinations:
{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }
The relation state r(R) dom(A1) X dom(A2)
For example: r(R) could be {<0,a> , <0,b> , <1,c> }
this is one possible state (or “population” or
“extension”) r of the relation R, defined over A1 and
A2.
It has three 2-tuples: <0,a> , <0,b> , <1,c>
Informal Terms Formal Terms
Table Relation
Column Header Attribute
All possible Column Domain
Values
Row Tuple
Table Definition Schema of a Relation
Populated Table State of the Relation
1. Ordering of Tuples in a Relation
A relation is defined as a set of tuples.
Elements in a set have no order among them.
Hence, Tuples in a relation do not have any
particular order.
2. Ordering of Values within a Tuple and an
Alternative Definition of a Relation
A relation schema R(A1, A2, .........., An) is a set
o attributes and a relation state r(R) is a finite
set of mappings r={t1,t2,……,tm}, where each
tuple ti is a mapping from R to D, where D is the
union of attribute domains i.e D=dom (A1) X
dom (A2) X ....X dom(An).
t[Ai] must be in the dom(Ai).
A tuple can be considered as a set of
(<attribute>,<value>) pairs, where each pair
gives the value of the 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.
3. Values and Null Values in the Tuples
All values are considered atomic (indivisible).
Composite and multi-valued attributes are not
allowed.
NULL is used to represent the values of
attributes that may be unknown or may not
apply to a tuple.
4. Interpretation of Relation
The relation schema can be interpreted as a
declaration or a type of assertion.
Each tuple in the relation can then be interpreted
as a fact of a particular instance of the assertion.
A relation schema R of degree n is denoted by
R(A1,A2,......An).
The uppercase letters Q,R,S denote relation names.
The lowercase letters q,r,s denote relation states.
The letters t, u, v denote tuples.
STUDENT: Name of Relation Schema also indicates
the current set of tuples. Whereas, STUDENT(Name,
SSN.....) refers to the relation schema.
R.A = A is the attribute belonging to relation R.
Each n-tuple t is an ordered set of values
(enclosed in angled brackets ‘< … >’) i.e
t=<v1,v2…….vn>
The ith value in a tuple t, which corresponds to the
attribute Ai, is referred to as t[Ai] or t.Ai.
Three types of constraints:
1. Inherent Model based constraints or implicit
constraints
2. Schema based constraints or explicit
constraints
3. Application based or semantic constraints or
business rules
Constraints that are inherent in the data model.
Example: the relation cannot have duplicate tuples
is an model based constraint.
Constraints that can be directly expressed in the
schemas of the data model by specifying them in
the DDL.
Example: Domain constraints, key constraints,
constraints on NULLs, entity integrity constraints
and referential integrity constraints.
It specifies that within each tuple , the value of each
attribute A must be an atomic value from the
domain dom(A).
The data types include standard numeric data
types for integers and real numbers, Characters ,
Booleans, fixed length strings and variable length
strings, date, time, timestamp etc.
All the tuples in a relation must be distinct.
No two tuples can have the same combination of
values for all their attributes.
Suppose that we denote subset of attributes by SK,
then for any two distinct tuples t1 and t2 in a
relation state r(R)
t1[SK] ≠ t2[SK]
SK is called a SuperKey
SK specifies a uniqueness constraint.
A key k of a relation schema R is a super-key with
an additional property that removing any attribute
A from K leaves the set of attribute K’ that is not a
super-key of R anymore.
Key satisfies 2 properties:
1. Two distinct tuples in any state of the relation
cannot have identical values for the attributes in
the key.
2. It is a minimal super key.
A key is a super key but not vice versa
Primary Key and Candidate Key:
A relation schema may have more than one key.
Each of the keys is called a candidate key.
Example: CAR relation can have License number
and Engine Serial Number as candidate keys.
One of the candidate key is designated as the
primary key.
Primary key is the candidate key whose values
are used to identify the tuples in the relation.
The other candidate keys are designated as
unique keys.
NOT NULL Constraint:
This constraint specifies whether NULL values are
or not permitted.
Relational Database Schema:
A set S of relation schemas that belong to the
same database.
S is the name of the whole database schema
S = {R1, R2, ..., Rn}
R1, R2, …, Rn are the names of the individual
relation schemas within the database S
Followingslide shows a COMPANY database
schema with 6 relation schemas
A database state that does not obey all the integrity
constraints is called not valid.
A database state that satisfies all the constraints in
the defined set of integrity constraints is called a
valid state.
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.
t[PK] null for any tuple t in r(R)
If PK has several attributes, null is not allowed
in any of these attributes
Note: Other attributes of R may be constrained
to disallow null values, even though they are not
members of the primary key.
Entity integrity constraints are specified on
individual relations.
The referential integrity constraints is specified
between two relations and is used to maintain the
consistency among the tuples in the 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 t1[FK] = t2[PK].
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:
The attributes in FK have the same domain as the
primary key PK of R2.
A value of FK in a tuple t1 of the current state
r(R1) either occurs as a value of PK for some
tuple t2 in the current state r2(R2) or is NULL.
A referential integrity constraint can be displayed
in a relational database schema as a directed arc
from R1.FK to R2.
Semantic Integrity Constraints
Not a part of DDL
Example: Salary of an employee should not
exceed his managers salary.
Such constraints can be specified within the
application or using general purpose constraint
specification language.
Mechanisms called triggers and assertions can
be used in SQL through CREATE TRIGGER or
CREATE ASSERTION statements.
There are 3 basic operations that can change the
states of relations in the database:
Insert: inserts a new record
Delete: delete old data
Update( or Modify) : used to change the values
of some attributes in existing tuples.
Insert operation provides a list of attribute values
for a new tuple t that is to be inserted into a relation
R.
It can violate any of the four types of constraints
Domain Constraints: can be violated if an
attribute value is given that does not appear in the
corresponding domain.
Key Constraints: can be violated if a key value in
the new tuple t already exists in another tuple.
Entity Integrity: can be violated if any part of the
primary key of the new tuple t is NULL.
Referential Integrity: can be violated if the value
of any foreign key in t refers to a tuple that does not
exist in the referenced relation
If an Insertion violates one or more constraints, the
default option is to reject the insertion.
Delete operation can only violate referential
integrity constraint.
This occurs when then tuple being deleted is
referenced by foreign keys from other tuples in the
data.
Can be remedied by several actions: RESTRICT,
CASCADE, SET NULL
RESTRICT option: reject the deletion
CASCADE option
SET NULL option: set the foreign keys of the
referencing tuples to NULL
One of the above options must be specified during
database design for each foreign key constraint.
The update or modify operation is used to change
the values of one or more attributes in a tuple of
some relation R.
Updating an attribute that is neither part of a
primary key nor part of a foreign key usually
causes no problems.
UPDATE may violate domain constraint and NOT
NULL constraint on an attribute being modified
Any of the other constraints may also be violated,
depending on the attribute being updated:
Updating the primary key (PK):
Similar to a DELETE followed by an INSERT
Need to specify similar options to DELETE
Updating a foreign key (FK):
May violate referential integrity
Updating an ordinary attribute (neither PK
nor FK):
Can only violate domain constraints
A transaction is an executing program that includes
some database operations, such as reading from
the database, or applying insertions, deletions, or
updating 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.
Consider the following relations for a database that
keeps track of student enrollment in courses and
the books adopted for each course:
STUDENT(SSN, Name, Major, Bdate)
COURSE(Course#, Cname, Dept)
ENROLL(SSN, Course#, Grade)
BOOK_ADOPTION( Course#, Book_ISBN)
TEXT(Book_ISBN, Book_Title, Publisher, Author)
Draw a relational schema diagram specifying
the foreign keys for this schema.
