Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

Computer Science Engineering (CSE): Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

The document Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE) is a part of the Computer Science Engineering (CSE) Course Database Management System (DBMS).
All you need of Computer Science Engineering (CSE) at this link: Computer Science Engineering (CSE)

Terminology

  • Relational Model: Relational model represents data in the form of relations or tables.
  • Relational Schema: Schema represents the structure of a relation. e.g.; Relational Schema of STUDENT relation can be represented as:
  • STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE)
  • Relational Instance: The set of values present in a relation at a particular instance of time is known as relational instance as shown in Table 1 and Table 2.
  • Attribute: Each relation is defined in terms of some properties, each of which is known as an attribute. For Example, STUD_NO, STUD_NAME etc. are attributes of relation STUDENT.
  • Domain of an attribute: The possible values an attribute can take in a relation is called its domain. For Example, domain of STUD_AGE can be from 18 to 40.
  • Tuple: Each row of a relation is known as tuple. e.g.; STUDENT relation given below has 4 tuples.
  • NULL values: Values of some attribute for some tuples may be unknown, missing or undefined which are represented by NULL. Two NULL values in a relation are considered different from each other.

Table 1 and Table 2 represent relational models having two relations STUDENT and STUDENT_COURSE

Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

Codd Rules

Codd rules were proposed by E.F. Codd which should be satisfied by relational model.

  • Foundation Rule: For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities.
  • Information Rule: Data stored in Relational model must be a value of some cell of a table.
  • Guaranteed Access Rule: Every data element must be accessible by table name, its primary key and name of attribute whose value is to be determined.
  • Systematic Treatment of NULL values: NULL value in database must only correspond to missing, unknown or not applicable values.
  • Active Online Catalog: The structure of database must be stored in an online catalogue which can be queried by authorized users.
  • Comprehensive Data Sub-language Rule: A database should be accessible by a language supported for definition, manipulation and transaction management operation.
  • View Updating Rule: Different views created for various purposes should be automatically updatable by the system.
  • High-level insert, update and delete rule: Relational Model should support insert, delete, update etc. operations at each level of relations. Also, set operations like Union, Intersection and minus should be supported.
  • Physical data independence: Any modification in the physical location of a table should not enforce modification at application level.
  • Logical data independence: Any modification in logical or conceptual schema of a table should not enforce modification at application level. For example, merging two tables into one should not affect application accessing it which is difficult to achieve.
  • Integrity Independence: Integrity constraints modified at database level should not enforce modification at application level.
  • Distribution Independence: Distribution of data over various locations should not be visible to end-users.
  • Non-Subversion Rule: Low-level access to data should not be able to bypass integrity rule to change data.

GATE Question
Q. Given the basic ER and relational models, which of the following is INCORRECT? [GATE CS 2012]
(a) An attribute of an entity can have more than one value
(b) An attribute of an entity can be composite
(c) In a row of a relational table, an attribute can have more than one value
(d) In a row of a relational table, an attribute can have exactly one value or a NULL value
Ans: (c)
Solution: In the relation model, an attribute can’t have more than one value. So, option C is the answer.

Different Types of Keys in Relational Model
Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

Candidate Key: The minimal set of attribute which can uniquely identify a tuple is known as candidate key. For Example, STUD_NO in STUDENT relation. 

  • The value of Candidate Key is unique and non-null for every tuple.
  • There can be more than one candidate key in a relation. For Example, STUD_NO is candidate key for relation STUDENT.
  • The candidate key can be simple (having only one attribute) or composite as well. For Example, {STUD_NO, COURSE_NO} is a composite candidate key for relation STUDENT_COURSE.
  • No of candidate keys in a Relation are nC(floor(n/2)),for example if a Relation have 5 attribute i.e. R(A,B,C,D,E) then total no of candidate keys are 5C(floor(5/2))=10.

Note: In Sql Server a unique constraint that has a nullable column, allows the value ‘null‘ in that column only once. That’s why STUD_PHONE attribute as candidate here, but can not be ‘null’ values in primary key attribute.

Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc. 

  • Adding zero or more attributes to candidate key generates super key.
  • A candidate key is a super key but vice versa is not true.

Primary Key: There can be more than one candidate key in relation out of which one can be chosen as the primary key. For Example, STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys). 

Alternate Key: The candidate key other than the primary key is called an alternate key. For Example, STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_PHONE will be alternate key (only one out of many candidate keys). 

Foreign Key: If an attribute can only take the values which are present as values of some other attribute, it will be a foreign key to the attribute to which it refers. The relation which is being referenced is called referenced relation and the corresponding attribute is called referenced attribute and the relation which refers to the referenced relation is called referencing relation and the corresponding attribute is called referencing attribute. The referenced attribute of the referenced relation should be the primary key for it. For Example, STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.
It may be worth noting that unlike, Primary Key of any given relation, Foreign Key can be NULL as well as may contain duplicate tuples i.e. it need not follow uniqueness constraint.
For Example, STUD_NO in STUDENT_COURSE relation is not unique. It has been repeated for the first and third tuples. However, the STUD_NO in STUDENT relation is a primary key and it needs to be always unique and it cannot be null.

The document Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE) is a part of the Computer Science Engineering (CSE) Course Database Management System (DBMS).
All you need of Computer Science Engineering (CSE) at this link: Computer Science Engineering (CSE)

Related Searches

Viva Questions

,

Extra Questions

,

Exam

,

Sample Paper

,

Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

,

Previous Year Questions with Solutions

,

shortcuts and tricks

,

mock tests for examination

,

pdf

,

Semester Notes

,

ppt

,

practice quizzes

,

Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

,

past year papers

,

Introduction to Relation Model Notes | Study Database Management System (DBMS) - Computer Science Engineering (CSE)

,

Important questions

,

Free

,

study material

,

Objective type Questions

,

video lectures

,

MCQs

,

Summary

;