Chapter Two

Relational Data Model

1
 Outline
 Terminologies
 Relational Constraints
 Relational Integrity
 Key constraints
 Referential constraints
 Relational languages and views
 Relational DBMS

2
 Relational model
 was proposed by Dr. Edgar F. Codd in 1970 at IBM.

 It became the foundation of modern database systems

because of its simplicity, flexibility, and use of mathematical
concepts (set theory and first-order predicate logic)

 all data is logically structured within relations

 The greatest strength of the relational model is the simple

logical structure that it forms

 A relational database consists of a collection of tables, each

having a unique name

3
 Basic concepts
 Data is stored in relations (tables).

 Each relation is made up of tuples and attributes

 The schema defines the structure of a relation (attribute

names and data types)

 The instance is the actual data at a specific time.

 Operations are performed on relations using relational

algebra or SQL

4
 Relational model terminologies
 Relation: a relation is a table with columns and rows.
 Attribute: an attribute is a named column of a relation
 (e.g., Name, Address)

 Domain: is a set of allowable values for a column,

defining its data type and any associated constraints
 Tuple: a tuple is a row of a relation.

 Degree: the number of attributes in the relation

 Cardinality: the number of tuples the relation has

 Schema: the description of a relation, including its attributes,

their domains, and the keys
5
 Relational model terminologies
 Relation Schema: the structure of the relation and
represents the name of the relation with its attributes
E.g. STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is the

relation schema for STUDENT

 Relation Instance: the set of tuples of a relation at a
particular instance of time is called a relation instance
 It can change whenever there is an insertion, deletion or
update in the database

6
Cont’d …

7
 Characteristics of the Relational

Model
Data Representation  data stored in rows and columns

 Atomic Values each cell holds a single, indivisible value.

 Unique Keys primary key ensures unique identification of each

record.

 Attribute Domain each attribute has a defined data type and valid
range

 Data Independence  schema changes don’t affect the application

layer.

 Relational Operations  supports selection, projection, join, union,

intersection.

 Data Consistency  constraints maintain accuracy and reduce 8

 Building Blocks of Relational Data
Model
The building blocks of the relational data model are:

 Entities: real world physical or logical object

 Attributes: properties used to describe each entity or real

world object
 Simple Vs Composite attribues

 Single-valued Vs. multi-valued attributes

 Stored vs. Derived Attribute

 Relationship: the association(link, connection) between

entities.

 Constraints: rules that should be obeyed while

9
 Types of Attributes
a. 1. Simple (Atomic) Vs Composite Attributes

 Simple : contains a single value (not divided into sub parts).

E.g. Age, weight, marriage status

 Composite: the attributes can be divided into smaller subparts,

which represent more basic attributes with independent meaning.

E.g. Name (FN,LN) and address (Kebele, Zone, Wereda,City)

b. Single-valued Vs multi-valued Attributes

 Single-valued: have only single value (the value may change but
has only one value at one time)

 E.g. Name, Sex, IdNo.

10
 Types of Attributes …

 Multi-Valued: having a set of values for the single entity

 May have lower and upper bounds on the number of values
allowed for each individual entity

 E.g. Address, College Degree, Phone-numbers, Skills

c. Stored vs. Derived Attribute

 Stored : not possible to derive or compute. E.g. Name, Address

 Derived: the value may be derived (computed) from the values of

other attributes.

 E.g. Age (current year –year of birth),

 Length of employment (current date- started date), G.P.A (grade

point/credit hours ) 11
 Types of Attributes …

 Null Values:- applies to attributes which used for don't know or not
applicable

 e.g. Phone number, middle name

 Default value:- is a predefined value automatically assigned to a

column when no value is provided during data insertion

 It helps maintain data consistency and reduces the chance of

NULL values

 E.g department =“software”

12
 Relationship Types
 the type of relationship between two tables in an ERD (either
identifying or non-identifying);

 Identifying Relationship → Strong link; child fully depends on

parent; parent’s key is part of child’s key. E.g. dep’t
&employee

 Non-Identifying Relationship → Weak link; child has its own

key; parent’s key only appears as a foreign key. E.g. customer
& order

 The number of entities participating in a relationship is called

the DEGREE of the relationship.
 UNARY/RECURSIVE RELATIONSHIP: Single entity
 BINARY RELATIONSHIPS: Two entities associated 13
 Relationship Types …
 Cardinality of a Relationship is the number of instances of one
entity that can be associated with a single instance of another
entity in a relationship.

 It represents the range of participation between entities are:

 ONE-TO-ONE, e.g. Building - Location,

 ONE-TO-MANY, e.g. hospital - patient,

 MANY-TO-ONE, e.g. Employee - Department

 MANY-TO-MANY, e.g. Author - Book

14
 Types of Keys in the Relational Model

 Primary Key: uniquely identifies each tuple in a relation.

 It must contain unique values and cannot have NULL

values.
 Candidate Key: is a set of attributes that can uniquely identify a
tuple in a relation.
 It can have Null value

 Super Key: is a set of attributes that can uniquely identify a tuple

 Foreign Key: is a set of attributes in a table that refers to the

primary key of another table

 Composite Key: primary key having two or more attributes is

called composite key
15

 Relational Constraints/Integrity Rules
 Rational Constraints are the rules that force DBMSs to check that
data satisfies the semantics
 are rules that ensure the accuracy, consistency, and validity of
data in a database.
 Domain Integrity: No value of the attribute should be beyond
the allowable limits

 e.g. the Employee ID (EID) must be unique or employee

Birthdate in the range [Jan 1, 1950, Jan 1, 2000]

16
 Relational Constraints/Integrity Rules

 Entity Integrity: states that primary key value can't be null.

 This is because the primary key value is used to identify

individual rows in relation and if the primary key has a null
value, then we can't identify those rows.

 A table can contain a null value other than the primary key
field

 E.g.

17
 Relational Constraints/Integrity Rules

 Referential Integrity: is specified between two relations and is

used to maintain the consistency among tuples in the 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

 E.G. Referential integrity constraint in the customer/order

database of the company:
 Customer(CustID, CustName)
 Order(OrderID, CustID, OrderDate)

18
 Relational Constraints/Integrity Rules
a. Key Constraints
 Ensure that each record (row) in a table can be uniquely
identified

 Primary Key: the candidate key that is selected to identify

tuples uniquely within the relation.

 The entire set of attributes in a relation can be considered as a

primary key in a worst case

 Foreign Key: an attribute, or set of attributes, within one

relation that matches the candidate key of some relation.

 A foreign key is a link between different relations to create the

view or the unnamed relation

19
 Relational Database Management System
(RDBMS)
 an RDBMS is a framework for organizing and managing data
based on a rigorous mathematical model known as the relational
model
 The fundamental principles are:
1. Structured Data: all data is stored in simple, two-dimensional
structures called tables (or relations)
 Each table is composed of rows (records or entities) and
columns (attributes or properties of entities).
2. Data Integrity: the system enforces rules to ensure the accuracy
and reliability of data
 This includes primary keys and foreign keys
3. Set-Based Operations: is designed to process data in sets,
meaning operations can work on multiple rows of data 20
 Database Languages

 are specialized sets of commands and instructions used to

define, manipulate and control data within a database.
 Each language type plays a distinct role in database
management, ensuring efficient storage, retrieval and security
of data

21
 Views
 a view is a virtual table derived from the result-set of an SQL
query.
 It does not store data physically but presents a customized and
simplified perspective of the data stored in one or more underlying
base tables
 Key Characteristics of Views
 Virtual Table: dynamically retrieve data from the underlying tables
every time they are queried
 Derived from tables: is always derived from one or more existing
tables in the database
 Customized data presentation: allow users to see a specific subset
of columns or rows from a table, or a combination of data from
multiple tables
22
 Views…
 Security Mechanism: used to restrict access to sensitive data only
showing users they are authorized to see, while hiding other
columns or rows.
 simplify complex data: by hiding the technical details - users see a
simple table while the system handles all the complicated joins
and calculations behind the scenes
 E.g. CREATE VIEW HighAchievers AS
SELECT StudentID, StudentName, Grade
FROM Students
WHERE Grade > 90;

23
Thank
You!

24