Relational Model
Introduction
• Relational model introduced by Codd in 1970.
• It uses the concept of mathematical relation – concept of set theory and
predicate logic
• It represents the database as a collection of relations.
Relational Model Concepts
• Relation or table
• Attribute or field or column name
• Tuples or record
• Schema
• Instance
• Domains
• Keys
• Degree: The total number of attributes which in the relation is called the degree of the relation.
• Cardinality: Total number of rows present in the Table.
Student Relation
NAME ROLL_NO PHONE_NO ADDRESS AGE
Ram 14795 7305758992 Noida 24
Shyam 12839 9026288936 Delhi 35
Laxman 33289 8583287182 Gurugram 20
Mahesh 27857 7086819134 Ghaziabad 27
Ganesh 17282 9028293988 Delhi 40
• NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the attributes.
• The instance of schema STUDENT has 5 tuples.
• t3 = <Laxman, 33289, 8583287182, Gurugram, 20> is the third tuple in the
student table
• ROLL_NO is primary key in the table
• Degree = 5 (No of Columns)
• Cardinality = 5 (No of Rows)
Relational Model Concepts Cont.…
Domain
• It contains a set of permitted atomic values for an attribute in the table.
• We specify domain of attribute while creating a table.
• E.g.,
• Month of an year can accept all the month name or numbering from 01 to 12.
• Domain of date can accept of possible dates.
Relational Model Concepts Cont.…
Relational Model Concepts Cont.…
Keys
• An attribute or set of attributes which can identify the row ( tuple or record )
in the relation uniquely.
• It is used to access the stored data quickly and smoothly
• It is used to create relationship between different tables.
Relational Model Concepts Cont.…
Types of Keys:
• Primary Key
• Candidate Key
• Alternate Key
• Super Key
• Composite Key
• Foreign Key
• Unique Key
Relational Model Concepts Cont.…
Primary Key (PK) Employee
• It is unique and can not have NULL value EID
Name
• It is chosen by designer to maintain uniqueness at row level. SSN
• In the Employee table, EID or SSN can be a PK. DeptId
DOB
• EID is preferable because SSN is PII value and secure.
Relational Model Concepts Cont.…
Candidate Key (CK) Employee
• All the columns that qualifies for uniqueness of each row. EID
Name
• It is also called as minimal super keys. SSN
• In the Employee table, EID are SSN are CK. DeptId
DOB
• We pick one of the CKs as PK.
Relational Model Concepts Cont.…
Alternate Key
• Candidate column other than primary column is called Alternate key.
• In the Employee table, SSN is alternate key. Employee
EID
Name
SSN
DeptId
DOB
Relational Model Concepts Cont.…
Super Key
• If we add any other column with candidate key, then it becomes super key.
• One or more attributes are taken collectively to identify all other attributes
Employee
uniquely.
EID
• In the Employee table, EID+Name, SSN+DeptId are super keys. Name
SSN
DeptId
DOB
Relational Model Concepts Cont.…
Composite Key
• If a table does not have CKs, then we select two or more columns to
uniquely identify each row in a table.
Employee
• In the Employee table, Name+DOB can be used as a composite
EID
primary key Name
SSN
DeptId
DOB
Relational Model Concepts Cont.…
Foreign Key (FK)
• It is an attribute or set of attributes that references to PK of same table or
another table.
Employee Department
• DeptId is FK in Employee Table.
EID
DID
Name
Dname
SSN
DeptId
DOB
Relational Model Concepts Cont.…
Unique Key
• It is same as the PK, but the difference is with the existence of NULL value.
• It allows one value as NULL value. Employee
EID
Name
SSN
DeptId
DOB
Relational Model Constraints: Integrity
Constraint
• Relational constraints are the restrictions imposed on the database contents and
operations.
• They ensure the correctness of data in the database.
• Integrity Constraints
• Integrity constraints are a set of rules. It is used to maintain the quality of
information.
• Integrity constraints ensure that the data insertion, updating, and other processes
have to be performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the database.
Constraint Types:
• Domain Constraints
• Key Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
Relational Model Constraints Cont. …
Domain Constraints:
• Domain constraint defines the domain or set of values for an attribute.
• It specifies that the value taken by the attribute must be the atomic value
from its domain. STU_ID Name Age
S001 Akshay 20
• In the Student table, value ‘A’ is not allowed since only
S002 Abhishek 21
integer values can be taken by the age attribute.
S003 Shashank 20
S004 Rahul A
Relational Model Constraints Cont. …
Key Constraints:
• All the values of primary key must be unique.
• This student relation does not satisfy the key constraint as STU_ID Name Age
here all the values of primary key are not unique. S001 Akshay 20
S001 Abhishek 21
S003 Shashank 20
S004 Rahul 21
Relational Model Constraints Cont. …
Entity Integrity Constraints:
• It specifies that no attribute of primary key must contain a null value in any
relation.
STU_ID Name Age
• This is because the presence of null value in the primary key S001 Akshay 20
violates the uniqueness property.
S002 Abhishek 21
• This student relation does not satisfy the entity integrity
S003 Shashank 20
constraint as here the primary key contains a NULL value.
Rahul 21
Relational Model Constraints Cont. …
Referential Integrity Constraints:
• This constraint is enforced when an FK references the PK of a relation.
• It specifies that all the values taken by the FK must either be available in the relation
of the PK or be null.
• We can not insert a record into a referencing relation if the corresponding record
does not exist in the referenced relation.
• We can not delete or update a record of the referenced relation if the
corresponding record exists in the referencing relation.
Referential Integrity Constraint Violation
• Cause-01: Insertion in a referencing relation
• Cause-02: Deletion from a referenced relation
• Cause-03: Updating in a referenced relation
Relational Model
Constraints Cont.
Referential Integrity
Constraints:
• It is specified between two
tables.
• If an FK in Table 1 refers to
the PK of Table 2, then every
value of the FK in Table 1
must be null or be available in
Table 2.
Guess under which A is not allowed ??
Guess under which constraint A is not allowed
??
The following table has two attributes A and C where A is the primary key and C is the foreign
key referencing A with on-delete cascade.
A C
-----
2 4
3 4 The set of all tuples that must be additionally deleted to preserve
4 3 referential integrity when the tuple (2,4) is deleted is:
(A) (3,4) and (6,4)
5 2 (B) (5,2) and (7,2)
7 2 (C) (5,2), (7,2) and (9,5)
(D) (3,4), (4,3) and (6,4)
9 5
6 4
The following table has two attributes A and C where A is the primary key and C is the foreign
key referencing A with on-delete cascade.
A C
-----
2 4
3 4 The set of all tuples that must be additionally deleted to preserve
4 3 referential integrity when the tuple (2,4) is deleted is:
(A) (3,4) and (6,4)
5 2 (B) (5,2) and (7,2)
7 2 (C) (5,2), (7,2) and (9,5)
(D) (3,4), (4,3) and (6,4)
9 5
6 4
