Some facts you need to know about relational tables

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.

Please see the video for knowing what is data, database, and DBMS.

What is meant by degree of a relation in relational model?

What is arity in relational model?

Define cardinality of a relation in relational model

What is cardinality

Define schema and instance

Difference between domain and atomic domain