Relational Data
Model
August 20, 25, 2025
Data Models: Categories
High level: from the user perspective
– Entity, attribute, relationships (E-R diagrams)
Low level: how data stored in computer
– For computer specialists/DBMS implementer
– Physical data model: Record formats, record orderings, access paths
Representational (implementation) model
– Meant for end user
– With some of the data organizational information
– Can be implemented directly in a computer
– e.g., Relational data model
– Out of fashion: Network data model and Hierarchical data model
2
Data Models: Operations
Used for specifying database retrievals and updates by
referring to the constructs of the data model
Include
• basic model operations (e.g., generic insert, delete,
update), and
• user-defined operations (e.g., compute_student_gpa,
update_inventory)
3
Schemas versus Instances
Database Schema: also called intension
– Includes description of the database structure, data types, and
the constraints on the database
Database State: also called extension
– Refers to the content of a database at a moment in
time
– Also called database instance (or occurrence or
snapshot)
• e.g., record instance, table instance, entity instance
Valid State: A state that satisfies the structure and
constraints of the database
4
Example: University Database
DB Schema
Distinction?
Schema Constructs
A DB Instance
5
Relational Model
Proposed in 1970 by E.F. Codd (IBM), first commercial
system in 1981-82
Several commercial products (DB2, ORACLE, MS SQL
Server, SYBASE, INFORMIX)
Free open-source implementations (MySQL, PostgreSQL)
Dominant for developing database applications
SQL relational standards: SQL-89 (SQL1), SQL-92
(SQL2), SQL-99, SQL3, …
NoSQL data models: emerged in the late 2000s as the
cost of storage dramatically decreased (Amazon
DynamoDB, MongoDB, CouchDB)
6
Relational Model
Data organized and stored in 2D tables called relations
Relations - flat files
Relational DB - “flat” arrangements of data
Tabular representation: most natural way to represent
data to a nonprogramming user
Any representation can be reduced to a group of 2D flat
files with some redundancy
Tables must be set up in such a way that no information
about the associations between data items is lost
7
Relational Model
The relations (tables) are rectangular arrays
which can be described mathematically
8
Relational Model
The relations (tables) are rectangular arrays
which can be described mathematically
9
Relational Model
The relations (tables) are rectangular arrays
which can be described mathematically
10
Relational Model
The relations (tables) are rectangular arrays
which can be described mathematically
column homogeneous
…
11
Relational Model
Uniqueness of Rows and Columns
12
Relational Model
Uniqueness of Rows and Columns
13
Relational Model
Order of Rows and Columns
14
Relational Model
Linkage of Tables
15
Relational Model
16
Relational Model - Terminology
Table (Relation): conventional sequential file
– files that obey certain constraints
Rows of a table (Tuple): records of the file
Column (Attribute): fields of the records
Domain: pool of values from which the actual values
appearing in a given column are drawn
Domain may not be explicitly recorded in the DB as an
actual set of values, but it is defined in the appropriate
schema and has a name of its own
17
Relational Model - Characteristics
Simple, easy to understand
All information in the DB (both entities and
relationships) is represented in a single uniform
manner (in the form of tables)
DML: set of operators provided to manipulate
data that is represented in relational form
The uniformity of data representation leads to a
corresponding uniformity in the operator set
18
Relations
Degree of a relation: Value n in the set of
ordered n-tuples < d1, d2, .. dn >
Cardinality of a relation: number of tuples in a
relation Degree = 5, Cardinality = 4
All rows distinct
19
Relations
Domain and Attributes
– An attribute represents the use of a domain within a relation
– To emphasize this distinction we may give attribute names that
are distinct from those of the underlying domain
– A simple domain is one in which all elements are atomic
All relations are required to satisfy the condition
– Each attribute value in each tuple is atomic (i.e., non-
decomposable so far as the system is concerned)
At every row-and-column position in the table there always
exists precisely one value, never a set of values.
The possibility of null values - allowed
20
Keys
Combination of all attributes - unique identification property
Why?
Remember relation is a set
Usually there are subsets of attributes in a relation R with the
property that no two tuples of R should have the same values
for this combination of attributes
Any such set of attributes is called a superkey
A superkey of a relation schema R = {A1, A2, A3,….. An } is
a set of attributes S R with the property that no two tuples
t1 and t2 in any legal relation state r of R will have t1[S]=t2[S]
Every relation has at least one super key
21
Keys
Key: Minimal Super Key
– A super key from which we can not remove any attributes
and still have the uniqueness constraint hold
– Key is nonredundant - needs to be carefully chosen
Candidate Key and Primary Key
Alternate Key or Secondary key
22
Keys
Composite Primary Key
23
Keys
Foreign Key
24
Creating a Relation
Design the record format for a table, considering:
– the order of the fields;
– the name of each field;
– the data type for each field.
• Text (also referred to as Character or Alphanumeric)
• Number (integer, or real with a specified number of
decimal points)
• Date (in a number of different formats)
• Yes/No (or True/False)
• Memo (for writing notes about a client, for example)
25
Importance of Data types
Certain field types are automatically validated when the user
enters data
– If a field is defined as currency or numeric, no non-numeric
characters allowed
If you have specified the field as a text field, no automatic
validation is possible
A telephone number should not be numeric because it may
require a space or hyphen
If the field will be involved in any type of calculation, it
MUST be defined as numeric (or date, or currency)
The length specified for a text field should also be chosen
carefully and documented for future reference
26
Integrity Rules
Primary key: as an identifier for tuples in a relation
Interpretation of tuples
– Tuples represent entities in the real world, and the primary key
really serves as a unique identifier for those entities
– For ex: the tuples in the “STUDENT” relation represent
individual students, and the values of the “Roll No.” attribute
actually identify those students, not just the tuples that
represent them
This interpretation leads us to impose some rules
27
Integrity Rule 1: Entity Integrity
No component of a primary key value may be
null
Rationale behind this rule:
– The primary key value is used to identify individual tuples
in a relation
– Having null values for the primary key implies that we
can not identify/distinguish some tuples
The key constraint and entity integrity constraints are
specified on individual relations
28
Integrity Rule 2: Referential Integrity
Referential integrity constraint - specified b/w two relations
to maintain the consistency among tuples of two relations
Informally, the referential integrity constraint states that a
tuple in one relation that refers to another relation must refer
to an existing tuple in that relation
Let D be a primary domain, and let R1 be a relation with an
attribute A that is defined on D
– Then at any given time, each value of A in R1 must be
• Either Null or Equal to V
• Where V is the primary key value of some tuple in some relation
R2 (R1 and R2 are not necessarily distinct) with primary key
defined on D
29
Integrity Rule 2: Referential Integrity
30
Integrity Rule 2: Referential Integrity
31
Integrity Rule 2: Referential Integrity
32
Integrity Rule 2: Referential Integrity
33
Integrity Rule 2: Referential Integrity
Example: consider the relation Employee AND Department
Can Foreign key refer to its own relation ?
Employee
FNAME MNAME LNAME BDATE ADD SEX SAL SUPER DNO
SSN SSN
Department
DNAME DNUMBER MGRSSN MGRSTARTDATE
The attribute SUPERSSN in Employee refers to the supervisor of
an employee
Supervisor of an employee is another employee, who is
represented by a tuple in the Employee relation
Hence SUPERSSN is a foreign key that references the Employee
relation itself 34
Semantic Integrity Constraints
Many other constraints are possible in theory
Example:
– for the SUPPLIER relation it may be a constraint that if the city
is Delhi then the status value must be 20
– The salary of an employee should not exceed the salary of the
employee’s supervisor
– The maximum number of hours an employee can work on all
projects per week is 56
35
Update Operations on Relations
INSERT a tuple.
DELETE a tuple.
MODIFY a tuple.
Integrity constraints should not be violated by the
update operations.
Several update operations may have to be grouped
together.
Updates may propagate to cause other updates
automatically. This may be necessary to maintain
integrity constraints.
36
Update Operations on Relations
In case of integrity violation, several actions can be
taken:
– Cancel the operation that causes the violation
(RESTRICT or REJECT option)
– Trigger additional updates so the violation is corrected
(CASCADE option, SET NULL option)
– Execute a user-specified error-correction routine
37
Update Operations: Various Situations
Insert Delete
– Domain – Referential integrity
– Key Update
– Referential integrity – Primary key
– Entity integrity – Foreign key
– An ordinary attribute
38
Possible Violations - Insert Operation
INSERT may violate any of the constraints:
– Domain constraint:
• if one of the attribute values provided for the new tuple is
not of the specified attribute domain
– Key constraint:
• if the value of a key attribute in the new tuple already
exists in another tuple in the relation
– Referential integrity:
• if a foreign key value in the new tuple references a primary
key value that does not exist in the referenced relation
– Entity integrity:
• if the primary key value is null in the new tuple
39
Possible Violations - Insert Operation
40
Possible Violations - Delete Operation
DELETE may violate only referential integrity:
– If the primary key value of the tuple being deleted is
referenced from other tuples in the database
• Can be remedied by several actions:
– RESTRICT option: reject the deletion
– CASCADE option: propagate the new primary key
value into the foreign keys of the referencing tuples
– SET NULL option: set the foreign keys of the
referencing tuples to NULL
– One of the above options must be specified during database
design for each foreign key constraint
41
Possible Violations - Delete Operation
42
Possible Violations - Delete Operation
RESTRICT
43
Possible Violations - Update Operation
UPDATE may violate domain constraint and NOT
NULL constraint on an attribute being modified
Any of the other constraints may also be violated,
depending on the attribute being updated:
– Updating the primary key (PK):
• Similar to a DELETE followed by an INSERT
• Need to specify similar options to DELETE
– Updating a foreign key (FK):
• May violate referential integrity
– Updating an ordinary attribute (neither PK nor FK):
• Can only violate domain constraints
44
Possible Violations - Update Operation
45
Example
Consider the following database schema with instances
See how to perform
operations on this
schema
Need to check
violation of integrity
constraints
46
Example
Insert <Lotus, DB10 > in Makes
Yes
47
Example
Insert <Mitesh, 652> in Owns
No
Violation of
referential integrity
A car with 652 serial
number does not
exist in Car relation.
48
Example
Insert <XY30, 2001, 867, red > in Car
No
Violation of
referential integrity
A car with model
XY30 does not exist
in Makes relation.
49
Example
Delete <Hyundai, AB99> from Makes
No
Violation of referential
integrity
AB99 is referred to in
relation Car.
What are the options if you still want to delete ??
50
Example
Update MN20 model of Ferrari as MN11 in Makes
No.
Violation of referential
integrity
Still want to update: make the corresponding update in rel. Car also
51
Example
Insert <Meeta, Null> in Owns
No
Violation of entity
integrity.
52
