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.
No comments:
Post a Comment