Chapter two

Relational Model
 Contents
• Introduction
• Terminologies of relations
• Characteristics of relations
• Relational Constraints
• Type of keys
• Relational Integrity
• Key constraints
• Referential constraints
 Introduction
• The relational model is a structured approach to
managing data in a database which uses concept
the mathematical relations.
• It organizes data into tables, also known as relations,
which consist of rows and columns.
• The model was first proposed by Dr. E.F. Codd of
IBM Research in 1970.
• This model is widely used in database systems due
to its simplicity and efficiency.
• Current popular RDBMS SQL server and
Access(Microsoft), DB2 and Informix(IBM`).
 Terminologies in the Relational Model

• basic terms :
• Relation: A table with rows and columns used to
represent data.
• Tuple(row): A single row in a relation,
representing a single record.
• Attribute(column): A column in a relation,
representing a specific data field.
 Cont.…
• Cardinality: The number of tuples (rows) in a relation. It's
a static property of a relation, defining its potential size.
• Degree(arity): The number of attributes (columns) in a
relation schema.
• Relation schema: is made up of relation name and a list
of attributes that describes a relation

• A displayed schema is called a schema diagram

which displays the structure of each record type but not
the actual instances of records.
• We call each object in the schema such as STUDENT or
COURSE a schema construct.
 Cont.….

• Domain: The set of possible values that an attribute can

hold and the value should be Atomic Values (indivisible).
• It essentially defines the data type and constraints for a
particular column in a table. Example
• In this table:

• The domain of instructor_ID might be defined as a set of

unique integer values.
• The domain of FirstName and LastName could be defined as
character strings of a certain length.
• The domain of Salary could be defined as a range of decimal
numbers, potentially with a currency symbol.
 Cont.…

• Relation state (relation instance):

• The data in the database at a particular moment
in time is called a database state or relation state.
• It is also called the current set of occurrences or
instances in the database.
Cont.…
 Cont.….
• Relationships:
• A connection between two relations based on a
common attribute.
• Primary Key: A unique identifier for each tuple in
a relation.
• Foreign Key: A reference to the primary key of
another relation, establishing a link between the
two.
 Cont.…

• Operations with relations:

• Select: Retrieves a subset of tuples from a relation based
on a specific condition.
• Project: Retrieves a subset of attributes from a relation.
• Join: Combines two relations based on a related
attribute.
• Union: Combines two relations with the same attributes,
eliminating duplicates.
• Intersection: Retrieves tuples that exist in both of two
relations.
• Difference: Retrieves tuples that exist in one relation but
not the other.
 Cont.…

• Other Important Terms:

• Entity: A real-world object or concept that can be
represented in a database.
• Entity-Relationship (ER) Model: A graphical
representation of entities and their relationships.
• Normalization: The process of organizing data to
reduce redundancy and improve data integrity.
 Cont.…
• Data Anomalies
• Data anomalies are inconsistencies or errors that
can arise in a database due to poor design or
improper data manipulation.
• These anomalies can lead to data corruption, loss
of data integrity, and inaccurate information.
• There are three primary types of data anomalies:
• Insertion Anomalies
• Deletion Anomalies
• Update Anomalies
 Characteristics of Relations

• Unique Name: Each relation must have a distinct

name to differentiate it from other relations in
the database.
• Unique Attributes: Each attribute within a
relation must have a unique name. This ensures
clarity and avoids ambiguity.
• Atomic Values: Each cell in a relation can hold
only one atomic value. An atomic value is
indivisible, meaning it cannot be further broken
down into smaller components.
 Cont..
• Order Independence:
• Row Order: The order of rows (tuples) within a
relation is not significant. This means that rearranging
the rows does not change the meaning of the relation.
• Column Order: Similarly, the order of columns
(attributes) is not significant. The meaning of a
relation remains the same even if the columns are
reordered.
• No Duplicate Rows: A relation cannot contain
duplicate rows. Each row must represent a unique
entity or record.
 Cont.…

• Additional Considerations of characteristics:

• Primary Key: A relation can have a primary key,
which is a unique identifier for each row.
• Foreign Key: A foreign key is a reference to the
primary key of another relation, establishing a
link between the two relations.
• Data Types: Each attribute in a relation has a
specific data type (e.g., integer, string, date) that
defines the kind of values it can hold.
 Relational Constraints

• Relational constraints are rules or restriction

imposed on data in a relational database to
maintain data integrity and consistency.
• These constraints help ensure that data is
accurate, reliable, and adheres to specific
business rule.
• Types of Relational Constraints:
• Domain Constraints, Unique Constraints, Not
Null Constraints, Check Constraints.
 Cont.…
• Domain Constraints
• Specify the allowable data types and ranges for each
attribute.
• Ensure that data entered into a column conforms to the
defined domain.
• Example: A DateOfBirth column might be constrained to
a valid date range. And DateOfBirth int(10)
• Unique Constraints:
• Enforce uniqueness on a specific attribute or set of
attributes.
• Prevents duplicate values within a column or a
combination of columns. Example: An Email column in
an Employees table might be unique.
 Cont.….
• Not Null Constraints:
• Specify that certain attributes cannot have null values.
• Ensures that essential information is always provided.
Example:
• A student_ID column in a student table might be
marked as NOT NULL. student_ID int (11) NOT NULL
• Check Constraints:
• Define specific conditions that must be met by
attribute values.
• Can be used to validate data based on complex rules.
• Example: A Salary column might be constrained to be
greater than a minimum value.
 Type of keys

• In a relational database, keys play a crucial role in

maintaining data integrity and establishing
relationships between tables.
• Primary Key: One of the candidate keys is chosen as
the primary key. It uniquely identifies each tuple in a
relation and cannot contain null values.
• Foreign Key: A reference to the primary key of another
relation. It establishes a relationship between two
tables and ensures data consistency and referential
integrity.
• Composite key: is a combination of two or more
columns in a table that uniquely identifies each row.
It's used when a single attribute alone cannot uniquely
identify a row.
 Cont.…
• Super Key: A set of attributes that uniquely
identify a tuple (row) in a relation. It may Null
values.
• Name cannot be a super key it self because
many people can have the same name. Example:
• In general:
– Any key is a superkey
(but not vice versa)
– Any set of attributes
that includes a key is a
superkey
 Cont.…
• Candidate Key
• A minimal super key, meaning it contains the
fewest number of attributes necessary to
uniquely identify a tuple.
• A relation can have multiple candidate keys.
• Alternate Key: Candidate keys that are not
chosen as the primary key are called alternate
keys.
• They can also uniquely identify tuples but are
not the primary identifier.
 Relational Integrity

• Relational integrity refers to the accuracy,

consistency, and validity of data within a relational
database.
• It ensures that the data is free from errors,
inconsistencies, and anomalies.
• Key Components of Relational Integrity:
• Entity Integrity
• Referential Integrity
• Domain Integrity
• User-Defined Integrity
• Maintaining Relational Integrity
 Cont.…
• Entity Integrity: Ensures that each tuple (row) in
a relation has a unique primary key value.
• Prevents duplicate records and maintains data
consistency.
• Referential Integrity: Maintains consistency
between related tables by enforcing relationships
between primary and foreign keys.
• Ensures that foreign key values refer to existing
primary key values.
• Prevents data anomalies like dangling references.
 Cont.…
• Domain Integrity: Defines the allowable data
types and ranges for each attribute.
• Ensures that data entered into a column
conforms to the defined domain.
• User-Defined Integrity:
• Enforces specific business rules or constraints
that are not covered by the standard integrity
rules.
• Example: A rule that ensures a customer's credit
limit is not exceeded.
 Cont.…

• Maintaining Relational Integrity:

• Database Design: A well-designed database
schema with appropriate primary and foreign
key constraints is crucial.
• Data Validation: Input validation and data
cleaning techniques can help prevent invalid
data from entering the database.
• Regular Monitoring and Maintenance:
Monitoring the database for anomalies and
inconsistencies, and taking corrective actions as
needed.
 Key constraints

• Key constraints an attribute that can uniquely Identify

each tuple in a relation is called a key.
• A superkey specifies that no two tuples can have the
same value
• Every relation has at least one superkey set of all
attributes
• Candidate keys: set of attributes that uniquely identify
the tuple in a relation.
• Constraints an null values: specifies whether null
values are permitted or not (NOT NULL)
• Entity integrity constraint: states that no primary key
value can be null.
 Referential constraint

• Referential integrity constraint: specified between 2

relations. States that a tuple in one relation that refers
to another relation must refer to an existing in that
relation
• Referencing Relation: is a relation that references
another relation.
• Contains a foreign key, which is a field that references
the primary key of the referenced relation.
• Referenced Relation: is a relation that is being
referenced by another relation.
• Contains the primary key, which is a unique identifier
for each tuple in the relation.
 Thank you
For
Your attention !!
 Chapter 3
Conceptual Database Design
E-R modeling