Some useful facts that you need to know about relational tables in relational model / Relational model keywords and their equivalent keywords / Relational model basics
Some facts
about Relations (Tables) in Relational model
The information you may need to know
about a relation and its components with equivalent simple terms and examples
are given below;
RELATIONAL MODEL TERMS AND THEIR EQUIVALENT TERMS
Relational terms
|
Equivalent terms
|
Relation
|
Table
|
Tuple
|
Row, record
|
Attribute
|
Column,
field
|
Cardinality (in SQL)
|
Number of tuples
|
Degree
|
Number of
attributes
|
Primary key
|
Unique
identifier
|
Domain
|
Set of
permitted values
|
Atomic
Domain
|
Set of indivisible
permitted values
|
Schema
|
Logical design
of the database
|
Instance
|
The snapshot
of a database at a given instant of time
|
Relation <----> Table
Example:
Regno
|
Name
|
Phone
|
10BS0123
|
Madhavan
|
9965235412
|
10BC0234
|
Jerry
|
8569326541
|
11BM0023
|
Malar
|
9658236542
|
11BC0003
|
Kumar
|
9653268954
|
Table 1 - STUDENT
The above table shows a relation
STUDENT with 3 attributes (Regno, Name, and Phone) and 4 records.
Tuple <----> Row or Record
It is used to represent every
individual entity/row/record.
Example:
In table 1, there are 4 records. For
example, (‘10BS0123’, ‘Madhavan’, 9965235412) is one record/row/tuple.
Attribute <----> Column or Field
It is used to represent all the
values stored in that particular column.
Example:
In table 1, there are 3 columns
namely Regno, Name, and Phone.
Cardinality (in SQL) <----> Number of rows
It is the number of
records/tuples/rows stored in the table currently. The cardinality of STUDENT
table is 4.
Degree <----> Number of columns
It is the number of
columns/attributes/fields of a table. For example, the degree of table STUDENT
is 3.
Primary key <----> Unique identifier
It is used to uniquely identify
the individual records/rows/tuples at any time. For table STUDENT, Regno is the
primary key. A primary key attributes/columns/field are permitted to store
unique and Not Null values.
Domain <----> Set of permitted values
Domain is the set of permitted
values for a particular column/attribute. It is to ensure the meaning of a
single column. For example, in table STUDENT the permitted values for Regno
column is set of valid register numbers of students.
Consider one more example table
STUDENT_SPORTS given below; assume that this table is used to store all the students
who are registered for some sports.
Regno
|
Name
|
Sports
|
10BS0123
|
Madhavan
|
Athlete
|
10BC0234
|
Jerry
|
Snooker
|
11BM0023
|
Malar
|
Basket ball
|
11BC0003
|
Kumar
|
High jump
|
For table STUDENT_SPORTS, the set
of permitted values for Regno column is set of students’ register numbers of
students who registered for some sports. It does not mean all the students
register numbers.
The domain of Sports attribute is
set of valid sports/games that are offered.
Atomic Domain <----> Indivisible domains
If the values that are stored as
part of a domain are not divisible into different values, that domain is called
atomic.
For example, in STUDENT_SPORTS
the attribute Sports contains indivisible values in it. That is we cannot break
the values into multiple different values in turn that are meaningful. Let us
take the value ‘Basket ball’ for instance. Though it is divisible into two
values ‘Basket’ and ‘Ball’, they would mean the basket and the ball, but not a
game ‘basket ball’. Such values are called indivisible values.
PROPERTIES OF RELATIONS
- There are no duplicate tuples – We don’t insert a record, for example, (‘10BS0123’, ‘Madhavan’, ‘Athlete’) two times.
- Tuples are unordered (not sorted) – We don’t store the records in any particular order, say, ascending order or descending order.
- Attributes are unordered – The attributes a table can be in any order.
- All attribute values are atomic – We demand the permitted values for any attribute should be indivisible.
KINDS OF RELATIONS
- Base relations: The real relations. Called "base table" in SQL.
- Views: The virtual relations. A view is a named, derived relation. This can be derived from another relation or set of relations.
- Snapshots: A snapshot is a real, not virtual, named derived relation.
- Query results: The final output relation from a specified query. It may not be named and has no permanent existence. We call them as intermediate relations if we are about to use them in deriving something else further.
- Temporary relations: A non-permanent named derived relation.