Database Management System [BCS403] ]
Module 2
Chapter 1: The Relational Data Model
Introduction
The relational data model was first introduced by Ted Codd of IBM Research in 1970 in a classic
paper (Codd 1970), and it attracted immediate attention due to its simplicity and mathematical
foundation. The model uses the concept of a mathematical relation which looks somewhat like a
table of values as its basic building block, and has its theoretical basis in set theory and first-order
predicate logic.
The first commercial implementations of the relational model became available in the early 1980s,
such as the SQL/DS system on the MVS operating system by IBM and the Oracle DBMS. Since
then, the model has been implemented in a large number of commercial systems. Current popular
relational DBMSs (RDBMSs) include DB2 and Informix Dynamic Server (from IBM), Oracle and
Rdb (from Oracle), Sybase DBMS (from Sybase) and SQLServer and Access (from Microsoft). In
addition, several open source systems, such as MySQL and PostgreSQL, are available.
1.1 Relational Model Concepts
The relational model represents the database as a collection of relations. Informally, each relation
resembles a table of values or, to some extent, a flat file of records. It is called a flat file because
each record has a simple linear or flat structure.
When a relation is thought of as a table of values, each row in the table represents a collection of
related data values. A row represents a fact that typically corresponds to a real-world entity or
relationship. The table name and column names are used to help to interpret the meaning of the
values in each row.
For example, in STUDENT relation because 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, based on the column each value is in. 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, and the table is called a relation. The data type describing the types of values that can
appear in each column is represented by a domain of possible values.
https://vtucode.in
, Database Management System [BCS403]]
1.1.1 Domains, Attributes, Tuples, and Relations
Domain
The preceding are called logical definitions of domains. 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)ddddddd, where each d is a numeric (decimal)
digit and the first three digits form a valid telephone area code. The data type for
Employee_ages is an integer number between 15 and 80.
Attribute
An 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).
Tuple
Mapping from attributes to values drawn from the respective domains of those attributes. Tuples
are intended to describe some entity (or relationship between entities) in the miniworld
Example: a tuple for a PERSON entity might be
{ Name -- --> Male, Age --> 25 }
Relation
A named set of tuples all of the same form i.e., having the same set of attributes.
https://vtucode.in
, Database Management System [BCS403] ]
Relation schema
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. 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). A relation
schema is used to describe a relation; R is called the name of this relation.
The degree (or arity) of a relation is the number of attributes n of its relation schema. A relation
of degree seven, which stores information about university students,would contain seven
attributes describing each student. as follows:
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 = {t 1, t2, ..., t m}. Each n-tuple t is an ordered list of n values t =<v1, v2, ...,
vn i) or is a special NULL value. The ith
value in tuple t, which corresponds to the attribute Ai, is referred to as t[Ai ] or t. Ai .
The terms relation intension for the schema R and relation extension for a relation state r(R)
are also commonly used.
https://vtucode.in
, Database Management System [BCS403] ]
1.1.2 Characteristics of Relations
1. Ordering of Tuples in a Relation
A relation is defined as a set of tuples. Mathematically, elements of a set have no order
among them; hence, tuples in a relation do not have any particular order. Tuple ordering is
not part of a relation definition because a relation attempts to represent facts at a logical or
abstract level. Many tuple orders can be specified on the same relation.
2. Ordering of Values within a Tuple and an Alternative Definition of a Relation
The order of attributes and their values is not that important as long as the correspondence
between attributes and values is maintained. An alternative definition of a relation can be
given, making the ordering of values in a tuple unnecessary. In this definition A relation
schema R(A1, A2, ...,An), set of 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.
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.
3. Values and NULLs in the Tuples
Each value in a tuple is atomic. NULL values 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 of asserts that, in general, a student entity has a Name,
Ssn, Home_phone, Address, Office_phone, Age, and Gpa. 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 Benjamin Bayer, Ssn is 305-61-2435, Age is
19, and so on.
An alternative interpretation of a relation schema is as a predicate; in this case, the values in
each tuple are interpreted as values that satisfy the predicate.
https://vtucode.in
Module 2
Chapter 1: The Relational Data Model
Introduction
The relational data model was first introduced by Ted Codd of IBM Research in 1970 in a classic
paper (Codd 1970), and it attracted immediate attention due to its simplicity and mathematical
foundation. The model uses the concept of a mathematical relation which looks somewhat like a
table of values as its basic building block, and has its theoretical basis in set theory and first-order
predicate logic.
The first commercial implementations of the relational model became available in the early 1980s,
such as the SQL/DS system on the MVS operating system by IBM and the Oracle DBMS. Since
then, the model has been implemented in a large number of commercial systems. Current popular
relational DBMSs (RDBMSs) include DB2 and Informix Dynamic Server (from IBM), Oracle and
Rdb (from Oracle), Sybase DBMS (from Sybase) and SQLServer and Access (from Microsoft). In
addition, several open source systems, such as MySQL and PostgreSQL, are available.
1.1 Relational Model Concepts
The relational model represents the database as a collection of relations. Informally, each relation
resembles a table of values or, to some extent, a flat file of records. It is called a flat file because
each record has a simple linear or flat structure.
When a relation is thought of as a table of values, each row in the table represents a collection of
related data values. A row represents a fact that typically corresponds to a real-world entity or
relationship. The table name and column names are used to help to interpret the meaning of the
values in each row.
For example, in STUDENT relation because 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, based on the column each value is in. 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, and the table is called a relation. The data type describing the types of values that can
appear in each column is represented by a domain of possible values.
https://vtucode.in
, Database Management System [BCS403]]
1.1.1 Domains, Attributes, Tuples, and Relations
Domain
The preceding are called logical definitions of domains. 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)ddddddd, where each d is a numeric (decimal)
digit and the first three digits form a valid telephone area code. The data type for
Employee_ages is an integer number between 15 and 80.
Attribute
An 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).
Tuple
Mapping from attributes to values drawn from the respective domains of those attributes. Tuples
are intended to describe some entity (or relationship between entities) in the miniworld
Example: a tuple for a PERSON entity might be
{ Name -- --> Male, Age --> 25 }
Relation
A named set of tuples all of the same form i.e., having the same set of attributes.
https://vtucode.in
, Database Management System [BCS403] ]
Relation schema
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. 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). A relation
schema is used to describe a relation; R is called the name of this relation.
The degree (or arity) of a relation is the number of attributes n of its relation schema. A relation
of degree seven, which stores information about university students,would contain seven
attributes describing each student. as follows:
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 = {t 1, t2, ..., t m}. Each n-tuple t is an ordered list of n values t =<v1, v2, ...,
vn i) or is a special NULL value. The ith
value in tuple t, which corresponds to the attribute Ai, is referred to as t[Ai ] or t. Ai .
The terms relation intension for the schema R and relation extension for a relation state r(R)
are also commonly used.
https://vtucode.in
, Database Management System [BCS403] ]
1.1.2 Characteristics of Relations
1. Ordering of Tuples in a Relation
A relation is defined as a set of tuples. Mathematically, elements of a set have no order
among them; hence, tuples in a relation do not have any particular order. Tuple ordering is
not part of a relation definition because a relation attempts to represent facts at a logical or
abstract level. Many tuple orders can be specified on the same relation.
2. Ordering of Values within a Tuple and an Alternative Definition of a Relation
The order of attributes and their values is not that important as long as the correspondence
between attributes and values is maintained. An alternative definition of a relation can be
given, making the ordering of values in a tuple unnecessary. In this definition A relation
schema R(A1, A2, ...,An), set of 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.
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.
3. Values and NULLs in the Tuples
Each value in a tuple is atomic. NULL values 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 of asserts that, in general, a student entity has a Name,
Ssn, Home_phone, Address, Office_phone, Age, and Gpa. 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 Benjamin Bayer, Ssn is 305-61-2435, Age is
19, and so on.
An alternative interpretation of a relation schema is as a predicate; in this case, the values in
each tuple are interpreted as values that satisfy the predicate.
https://vtucode.in
