Chapter 2 : Relational Data Model
• Developed by Dr. Edgar Frank Codd in 1970 .
•The relational Data model represents the database as a collection
of relations.
• Terminologies originates from the branch of mathematics called
set theory and relation
• Can define more flexible and complex relationship
• Viewed as a collection of tables called “Relations” equivalent to
collection of record types.
•Stores information or data in the form of tables.
Relational Database: a collection of normalized relations with
distinct relation names.
Relation Schema: a named relation defined by a set of attribute-
domain name pair Let A1, A2,…An be attributes with domain D1,
D2….Dn. Then the sets {A1:D1, A2:D2… An:Dn} is a Relation
Schema.
PREPARED BY ASHENAFI T.
A relation R, defined by a relation schema S, is a set of
mappings from attribute names to their corresponding
domains. Thus a relation is a set of n- tuples of the form
(A1:d1, A2:d2 ,…, An:dn) where d1 є D1, d2 є D2,
dn є Dn,
Eg. Student (studentId char(10), studentName char(50),
DOB date) is a relation schema for the student entity in
SQL.
Relational Database schema: a set of relation schema
each with distinct names. Suppose R1, R2,……, Rn is
the set of relation schema in a relational database then
the relational database schema (R) can be stated as: R={
R1 , R2 ,… , Rn}
PREPARED BY ASHENAFI T.
2.1 Terminologies
Key terms in relational databases, like relation (table), tuple
(row), attribute (column), domain, degree, and cardinality.
Relation: A table with rows and columns. Each row
represents a record, and each column represents an
attribute.
Tuple: A single row in a relation, representing a single
record.
Attribute: A column in a relation, representing a data field.
Domain: The set of permissible values for a given
attribute.
Relational Schema: A blueprint for the structure of a
relation, specifying the relation's name and its attributes.
Instance: A specific set of data in a relation at a given time.
Relational Database: A collection of relational schemas.
PREPARED BY ASHENAFI T.
Alternative Terminology for Relational Data Model
Formal terms Alternative 1 Alternative 2
Relation Table File
Tuple Row Record
Attribute Column Field
STUDENT(Name, SSN, HomePhone, Address,
OfficePhone, Age, GPA)
For this relation schema, STUDENT is the name of the
relation, which has seven attributes. A relation schema
is used to describe a relation
•Data value is the value of the Attribute
•The tables seem to be independent but are related
PREPARED BY ASHENAFI T.
somehow
2.2 Relational Constraints
Constraints are rules that ensure data integrity within a
relational database. are rules to maintain data consistency,
such as domain constraints, tuple constraints, Entity
integrity, Key constraints, Referential integrity, and
attribute constraints.
Types of Constraints:
Domain Constraints: Restrict values of an attribute to a
specific domain (e.g., age must be an integer between 0 and
100).
Entity Integrity Constraint: Ensures that primary key
values are unique and not null.
Referential Integrity Constraint: Ensures consistency in
relationships between tables.
PREPARED BY ASHENAFI T.
2.3 Relational Integrity:- also called relational constraints or integrity rule
Integrity rules are essential to maintain accuracy and
consistency of data in the database.
Ensuring data accuracy through entity integrity (primary
key uniqueness) and referential integrity (valid foreign key
references).
Entity Integrity: Ensures that each entity has a unique
identifier, typically enforced through primary keys.
Referential Integrity: Ensures that a foreign key in one
table refers to a valid, existing primary key in another table.
here Foreign keys create valid links between tables.
Domain Integrity: Attributes have valid data within a
defined domain.
Key Integrity: Primary keys uniquely identify each record.
PREPARED BY ASHENAFI T.
2.4 Key constraints
Unique keys in a table that help identify records, including
primary keys, candidate keys, super keys, and alternate
keys.
If tuples are need to be unique in the database, and then we
need to make each tuple distinct. To do this we need to have
relational keys that uniquely identify each relation called
key constraints.
Primary Key: A unique identifier for each record in a table;
each relation must have a primary key and must not contain
null values..
Candidate Key: A minimal set of attributes that uniquely
identifies a tuple in a relation or it is An attribute (or
combination) that can uniquely identify tuples. There can be
multiple candidate keys, but only one is chosen as the
PREPARED BY ASHENAFI T.
primary key.
Candidate Key: A minimal set of attributes that uniquely identifies
a tuple in a relation. There can be multiple candidate keys, but only
one is chosen as the primary key.
Foreign Key: An attribute (or set of attributes) in one relation that
references /links the primary key of another relation, establishing a
relationship between the two tables.
Alternate Key: Candidate keys not chosen as the primary key.
Super Key: an attribute/set of attributes that uniquely identify a
tuple within a relation.
A candidate key has two properties:
I. Uniqueness
II. Irreducibility
If a super key is having only one attribute, it is automatically a
Candidate key.
If a candidate key consists of more than one attribute it is called
Composite Key. PREPARED BY ASHENAFI T.
2.5 Referential constraints
Rules for foreign keys that link tables, ensuring relationships
between tables are valid.
Referential Integrity Constraint: Ensures that a foreign key
value in one relation matches a primary key value in another
relation.
Cascading: When a referenced record is updated or deleted,
changes cascade through dependent tables to maintain
referential integrity.
Cascading Actions: Specify actions when a referenced row in
the parent table is updated or deleted:
CASCADE: Update or delete rows in the child table
automatically.
SET NULL: Set foreign key values in the child table to null.
RESTRICT: Prevent deletion or update if it would affect child
rows. PREPARED BY ASHENAFI T.
2.6 Relational languages and views
Languages like SQL that allow interaction with the database,
and views which provide customized representations of data.
Relational Languages:
Relational Algebra: A procedural language for specifying
database queries, using operations like selection, projection,
and join.
Relational Calculus: A declarative language based on
mathematical logic for specifying queries.
SQL: The standard language used in relational databases,
which includes DDL, DML and DCL .
Relational Views:
Views: Virtual tables derived from queries on other tables.
Simplifies complex queries.
Provides security by limiting access to specific data.
PREPARED BY ASHENAFI T.
Purpose of a view
Hides unnecessary information from users: since only
part of the base relation (Some collection of attributes,
not necessarily all) are to be included in the virtual table.
Provide powerful flexibility and security: since
unnecessary information will be hidden from the user
there will be some sort of data security.
Provide customized view of the database for users: each
users are going to be interfaced with their own preferred
data set and format by making use of the Views.
A view of one base relation can be updated.
Update on views derived from various relations is not
allowed since it may violate the integrity of the database.
PREPARED BY ASHENAFI T.
2.7 Relational DBMS
Is a software that implements the relational model, providing data
storage, retrieval, integrity enforcement, and other database
functionalities.
It is also a software system that manages relational databases,
ensuring data integrity, security, and efficiency.
Examples : Oracle, MySQL, PostgreSQL, SQL Server.
Key Features/ Functions of RDBMS :
Data Storage and Retrieval: Efficiently stores and retrieves
data using relational models.
Concurrency Control: Manages multiple users accessing the
database simultaneously.
Transaction Management: Ensures database reliability by
following ACID (Atomicity, Consistency, Isolation, and
Durability) properties.
Enforcing constraints for data integrity. PREPARED BY ASHENAFI T.
Schemas and Instances and Database State
When a database is designed using a Relational data model, all
the data is represented in a form of a table. In such definitions and
representation, there are two basic components of the database.
The two components are the definition of the Relation or the
Table and the actual data stored in each table. The data definition
is what we call the Schema or the skeleton of the database and the
Relations with some information at some point in time is the
Instance or the flesh of the database.
Schemas:- describes how data is to be structured, defined at
setup/Design time (also called "metadata")
Since it is used during the database development phase, there is
rare tendency of changing the schema unless there is a need for
system maintenance which demands change to the definition of a
relation.
PREPARED BY ASHENAFI T.
Database Schema (Intension): specifies name of relation and
the collection of the attributes (specifically the Name of
attributes).
refer to a description of database (or intention)
specified during database design
should not be changed unless during maintenance
Schema Diagrams
convention to display some aspect of a schema visually
Schema Construct
refers to each object in the schema (e.g. STUDENT)
E.g.: STUNEDT (FName,LName,Id,Year,Dept,Sex)
PREPARED BY ASHENAFI T.
Instance: is the collection of data in the database at a particular
point of time (snap-shot).
Also called State or Snap Shot or Extension of the
database
Refers to the actual data in the database at a specific point
in time
State of database is changed any time we add, delete or
update an item.
Valid state: the state that satisfies the structure and
constraints specified in the schema and is enforced by
DBMS
Since Instance is actual data of database at some point in
time, changes rapidly
To define a new database, we specify its database schema to
the DBMS (database is empty)
database is initialized when we first load it with data
PREPARED BY ASHENAFI T.
Example 2. Instance of staff and branch
PREPARED BY ASHENAFI T.