Module 3 - Relational Database Model.pdf - 40 Data Base...

40
Data Base Management systems
Notes
Amity Directorate of Distance & Online Education
ISSUE-RETURN (Student-id, Book_id, Date_iss, Date_ret)
Book relation contains a list of books available in the library. Each book is assigned a
unique book id. So, we can take it as a primary key attribute. Similarly all the members of
the library are stored in STUDENT relation, which has a student_id attribute as primary key.
Now to issue any book to a student it is necessary that book must be there in Book relation
and student must be a member of the library. So any attempt to insert any wrong book or
wrong student_id should be avoided. This will be enforced by referential integrity by using
FK constraint in RDBMS if student_id and book_id attributes of ISSUE_RETURN relation
are made as the foreign key with respect to the student _id attribute of STUDENT and
book_id attribute of BOOK relation respectively then any invalid entry in ISSUE_RETURN
relation will not be allowed. Moreover if there are some records in ISSUE_RETURN
relation for a particular student or a book then that student or book can not be deleted
from STUDENT or BOOK tables.
Domain Constraints: It specifies that the value of each attribute X must be an atomic
value from the domain of X. The data type associate with domains includes integer,
character, string, date, time etc. An attribute value must be available in the corresponding
domain. For example
Employee
Empno Name Age
1 Arun 19
2 Varun 21
3 Tarun 17
4 Vaibhav A
Not allowed because Age is integer attribute
Tuple Uniqueness Constraints: Relation is destined as a set of tuples. By definition,
all elements of a set are distinct. Hence, all tuples in a relation must also be distinct. For
example
Employee
Empno Name Age
1 Arun 8
2 Varun 0
3 Tarun 2
1 Arun 8
Not allowed
Key Constraints: Primary key must have unique value in the relational table. For
example, if Empno is primary key then,
Employee
Empno Name Age
101 Anurag 48
102 Sashi 50
103 Suresh 52
101 Harpreet 48
Not allowed

Data Base Management systems
41
Notes
Amity Directorate of Distance & Online Education
3.5 Operations on Relation
We can use the following two operations on a relation
(a) Retrieval of information
(b) Update of data.
Retrieval of information we can do by using Relational Algebra which is explained
in next section. Updation of data includes the insertion of data, updation (change of old
values) and deletion of data. All these update operation require the execution of integrity
constraints, that is the integrity constraints which are assigned for the various attributes
in the relation should not be violated.
Insert operation means a new tuple is to be added in the relation with a list of attributes
values. It can violate any of the earlier described integrity constraints (entity integrity etc.).