Saturday, April 4, 2015

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










No comments:

Post a Comment