Basic Database

Data Models: Relational Model

Posted on by HKT

The relational model was discovered by Dr. E. F. Codd. The relational model is an abstract theory of data that is based on the mathematical concept of relations. In relational database, information is stored in tabular form or in tables. So, it consists of collection of tables. We use mathematical terms in relational model. The relational model is concerned with three aspects of data: data structure, data integrity, and data manipulation.

1. Definition of Relation

Given a collection of sets D1, D2, …, Dn, (not necessarily distinct), R is a relation on those n sets if it is a set of ordered n-tuples <d1, d2, …, dn> such that d1 belongs to D1, d2 belongs to D2, …, dn belongs to Dn. Sets D1, D2, …, Dn are the domains of R. The value of n is the degree of  R.

2. Data Structure of Relational Database

Relational model uses simple tables instead of complex tree and network structures to simplify the user’s view of the database. It is a collection of tables in which data is stored. A table is a matrix of a series of row and column intersections. Following are the basic data structures used in  relational  database.

  1. Relation (Entity set in E-R model) : In relational model name of table is known as It consists  of  all  possible  tuples.
  2. Tuple (Entity in E-R model) : Single row of any relation is known as tuple.
  3. Attributes (Same in E-R model) :  These are the  characteristics of  any relation.
  4. Domain (Same in E-R model) : Domain is the set of all permitted values or information about any For example in relation Employee, domain for attribute Emp- Name is (Deepak, Vinay, Gaurav, Rajiv).
  5. Tuple variable (Value in E-R model) : Tuple variable is the part of tuple or row, which is information or  data  stored in  relation  for  any attribute.

  1. Degree : Number of columns in any relation is known as its degree.
  2. Cardinality : Number of rows in any relation is known as its cardinality.

Types of Attributes : Attributes type discussed in E-R model are also applicable here.

  • Keys : A key is a single attribute or a set of attributes of a relation which can uniquely identify a record within the relation. A key may be Primary key, Supper key, Candidate key, Foreign key Keys discussed in E-R model are also applicable here.
  • Database instance : Database instance shows the data of database at a particular instance of time.
  • Relation schema : Relation schema is the logical design of relations of any It gives the way of representation of relations or you can say that notations used for relations in database.
  • Relation instance : It is related with the values in It shows the data stored in relations at a  particular  instance  of  time.

3. Integrity Constraints

These are the rules or constraints applied to the database to keep data stable, accurate or consistent. To keep database consistent we have to follow some rules known as integrity rules or integrity constraints.

1.       Entity Integrity Rule (Integrity Rule 1)

Primary key or a part of it in any relation cannot be null. Suppose A be the attribute in relation  R  which  is  taken  as  primary key  then A must  not  be  null.

2.       Referential Integrity Rule (Integrity Rule 2)

A foreign key can be either null or it can have only those values which are present in the primary  key  with  which  it  is  related.

Suppose A be the attribute in relation R1, which is also the primary key in relation R2, then value  of A in  R1  is  either  null  or  same  as  in  relation  R2.

3. Domain Constraints

The restrictions which we applied on domain are known as domain constraints. These restrictions are applied to every value of attribute. By following these constraints you can keep consistency in database. These restrictions include data types (integer, varchar, char, time format, date format etc.), size of variable, checks (like value not null etc.) etc.

Ex. create table employee

(Eid        char (4),

Name     char (20),

Age        integer (2),

Salary      integer,

primary key      (Eid),

Check      (age>18))

4.       Key Constraints

In any relation R, if attribute A is primary key then A must have unique value or you can say that primary key attribute must have unique value.

Duplicate values in  primary key are  invalid.

5.       Tuple Uniqueness Constraints

In any relation R, all tuples in relation R must have distinct values. In other words Duplicate tuples  within  a  single relation  are  not  allowed.

 Example. Consider  the two  relations given  below

Given that A is the primary key of R, D is the primary key of S and there is a referential integrity between  S.A and  R.A. Determine  all  integrity  constraints that  are  violated.

Sol.
  • The primary key of R contains the NULL value and the value ‘a1’ is This violates the entity  integrity  constraint  in  the  relation  R.
  • In the primary key of S, the value ‘d1’ is This violates the entity integrity constraint in the  relation  S.
  • The foreign key A contains the value ‘a2’. This value is not available in the parent key R.A. This violates the  referential integrity  constraint in the  relation S.

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Database Management System, 2nd Edition-University Science Press (2017)

Leave a Reply

Your email address will not be published. Required fields are marked *