Relational Data Model

The relational Model was proposed by Dr. Edgar Frank Codd in 1970 to
model (stores) data in the form of relations or tables.

In this model, the DBMS itself keeps track of all table relationships,
independent of hardware or outside programming languages. This makes the
use of an RDBMS (Relational DBMS) much simpler.

So, a user or an application programmer only needs to understand the

logical structure of data, not how it is physically stored.

The relational data model can define more flexible and complex relationship.

No physical consideration of the storage is required by the user.

Relational data model stores information or data in the form of tables rows
and columns. It viewed as a collection of tables called Relations equivalent
1 to
 Continued..
Relational data model stores information or data in the form of tables rows
and columns. It viewed as a collection of tables called Relations equivalent to
collection of record types.

After designing the conceptual model of the Database using ER

diagram, we need to convert the conceptual model into a relational
model which can be implemented using any RDBMS language like
Oracle SQL, MySQL, etc.

The relational model represents how data is stored in Relational

Databases.

2
 Important Terminologies
Relation is a two dimensional table (it stores information or data in the form
of rows and columns).
Consider a relation STUDENT with attributes ROLL_NO, NAME,
ADDRESS, PHONE, and AGE shown in Table below.

3
 Continued..
A row of the table is called tuple (equivalent to record).
A column of a table is called attribute (equivalent to fields).
Data value is the value of the Attribute.
Attributes can appear in any order and the relation will still be the same
relation, and therefore convey the same meaning.
Domain: a set of allowable values for one or more attributes.
It is a acceptable and valid values in a particular column.
Every attribute in a relation is defined on a domain.
Domains may be distinct for each attribute, or two or more attributes may
be defined on the same domain.

4
 Continued..
Relational Database: a collection of normalized relations with distinct
relation names. A relational database consists of relations that are
appropriately structured.
Relation schema: A relation schema represents the name of the
relation with its attributes (a named relation defined by a set of
attribute-domain name pair).
Example: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE,
and AGE) is the relation schema for STUDENT.
If a schema has more than 1 relation, it is called Relational
Schema.
5
 Continued..
Degree: The number of attributes in the relation is known as the degree
of the relation. The STUDENT relation defined above has degree 5.
A relation with only one attribute would have degree one and be
called a unary relation or one-tuple.
A relation with two attributes is called binary, one with three
attributes is called ternary, and after that the term nary is usually
used.
Cardinality: The number of tuples in a relation is known as cardinality.
The STUDENT relation defined above has cardinality 4.
Cardinality changes as tuples are added or deleted.
6
 Continued..
NULL Values: The value which is not known or unavailable is called
a NULL value.
It is represented by blank space. e.g.; PHONE of STUDENT
having ROLL_NO 4 is NULL.

7
 The basic rules that govern the relational
data model
The order of the rows does not matter

The order of the columns does not matter

Each row is unique.

Values in one column are all of the same kind.

The domains (or data types) for the values in the tuples must be
atomic.

8
 Properties of Relational Databases Model
A relation has a name that is distinct from all other relation names in the
relational schema.
All tables are LOGICAL ENTITIES. Each entity in one table.
Each cell of a relation contains exactly one atomic (single) value.
Each tuple in a relation must be unique
Each column (field or attribute) has a distinct name.
The values of an attribute are all from the same domain.
Entries with repeating groups are said to be un-normalized.
All values in a column represent the same attribute and have the same
data format.
Relational database is the collection of tables. 9
 What replaces physical pointers?
If physical pointers are removed in relational data model, how are
relations going to be linked to each other?

The answer lies in a concept of “primary key” and “foreign keys”.

A relation “A” has a “primary key”, which is a field (or a set of

fields, in the case of composite keys) allowing to uniquely identify
a record in the relation.

A relation “B” which is linked to “A” will have a field (or a set of
fields), called a foreign key, in which the primary key of the record
in “A” that is linked to the record in “B” will be stored.

10
 Constraints
In Relational Database Model , constraints are guidelines or limitations
imposed on database tables to maintain the integrity, correctness, and
consistency of the data.
This indicates that only a particular type of data may be entered into
the database or that only a particular sort of operation can be performed
on the data inside.
Constraints can be used to enforce data linkages across tables, verify
that data is unique, and stop the insertion of erroneous data.

11
 Continued..
The following can be guaranteed via constraints:

Data Accuracy: which make sure that only true data is entered into a
database. For example, a limitation may stop a user from entering a
negative value into a field that only accepts positive numbers.

Data Consistency: These constraints are able to ensure that the primary
key value in one table is followed by the foreign key value in another
table.

Data integrity: The accuracy and completeness of the data in a database

are ensured by constraints. For example, a constraint can stop a user from
putting a null value into a field that requires one.
12
Types of Constraints in RDB Model

13
 Domain constraints
In a database table, domain constraints are guidelines that specify the
acceptable values for a certain property or field.
These restrictions guarantee data consistency and aid in preventing the
entry of inaccurate or inconsistent data into the database.

Examples:
A column created as VARCHAR can take string values, but a column
specified as INTEGER can only accept integer values.
A column with the definition VARCHAR(10) may only take strings
that are up to 10 characters long.

14
 Key constraints
Key constraints are regulations that a Relational Database Model uses to
ensure data accuracy and consistency in a database.
They define how the values in a table's one or more columns are related to
the values in other tables, making sure that the data remains correct.
In Relational Database Model, there are several key constraint kinds:

1. Primary Key Constraint: A primary key constraint is an individual

identifier for each record in a database.

It guarantees that each database entry contains a single, distinct

value or a pair of values that cannot be null as its method of
identification.
15
 Continued..
2. Foreign Key Constraint: Reference to the primary key in another
table is a foreign key constraint.

It ensures that the values of a column or set of columns in one table
correspond to the primary key column(s) in another table.

16
 Entity Integrity Constraints
 A DBMS uses entity integrity constraints (EICs) to enforce rules that
guarantee a table's primary key is unique and not null.

 The consistency and integrity of the data in a database are maintained

by EICs, which are created to stop the formation of duplicate or
incomplete entries.

 EICs make a guarantee (state) that every row's primary key value is
distinct and not null. If two or more tuples had NULL for their
primary keys, we may not be able to distinguish them if we try to
reference them from other relations.

17
 Continued..
Example:
Take the "Employees" table, which has the columns "EmployeeID"
and "Name." The table's primary key is the EmployeeID column.
An EIC on this table would make sure that each row's unique
EmployeeID value is there and that it is not null.
If you try to insert an entry with a duplicate or null EmployeeID, the
database management system will reject the insertion and produce an
error.
This guarantees that the information in the table is correct and
consistent.
18
 Referential integrity constraints
 A DBMS will apply referential integrity constraints (RICs) in order to
preserve the consistency and integrity of connections between tables.

 These constraints are used to describe the behaviour of foreign keys.

 A foreign key is a key of a relation that can be referred in another

relation. It is specified between two relations to maintain the consistency
among tuples in the two relations.

 A column or collection of columns in one table that is used as a foreign

key to access the primary key of another table.

 RICs make sure there are no referential errors and that these
relationships are legitimate. 19
 Continued..
Example 1: consider the two following relations:
House (ID, Size, Address, Landlord_ID)
Landlord (Landlord ID, Name, Father_Name).
As you can see, each of these relations has a single attribute primary key, called
ID and Landlord ID respectively.
To store the relation between “House” and “Landlord” (each house is owned by
one and only one landlord).
we use the “Landlord_ID” field in “House”. “Landlord-ID” field is called a
foreign key (in the sense that it is the primary key of another relation, that is
foreign to the one where it is located), and can be seen as a logical pointer as
opposed to the physical pointers that we used in previous data models.
20
 Continued..
Example 2: consider the database, in the EMPLOYEE relation, the attribute
Dept_Num refers to the department for which an employee works; hence, we
designate Dept_Num to be a foreign key of EMPLOYEE referencing the
DEPARTMENT relation.
This means that a value of Dept_Num in any tuple t1 of the EMPLOYEE
relation must match a value of DEPARTMENT relation, or the value of
Dept_Num can be NULL if the employee does not belong to a
department or will be assigned to a department later.
For example, the tuple for employee “John Smith” references the tuple
for the “Research” department, indicating that “John Smith” works for
this department.
21
 Continued..
 Most relational DBMSs support key, entity integrity, and referential integrity
constraints. These constraints are specified as a part of data definition in the
DDL. These are:
1) Domain Integrity: No value of the attribute should be beyond the allowable limits
2) Entity Integrity: In a base relation, no attribute of a Primary Key can assume a
value of NULL
3) Referential Integrity: If a Foreign Key exists in a relation, either the Foreign
Key value must match a Candidate Key value in its home relation or the
Foreign Key value must be NULL
4) Enterprise Integrity: Additional rules specified by the users or database
administrators of a database are incorporated
22
 Summary

 The entity integrity constraint states that no primary key value can be
NULL.

 Key constraints and entity integrity constraints are specified on

individual relations.

 The referential integrity constraint is specified between two relations and

is used to maintain the consistency among tuples in the two relations.

 Referential integrity constraints typically arise from the relationships

among the entities represented by the relation schemas.

23
3.1. Database Development Life Cycle
There are several steps in designing a database system. The major
steps in database design are;
I. Planning: identifying information gap in an organization and propose a
database solution to solve the problem.

II. Analysis: concentrates more on fact finding about the problem or the
opportunity and selection of best design method.

III. Design: The phase is further divided into three sub-phases:

– Conceptual design: concise description of the data, data type,
relationship between data and constraints on the data.
– Logical design: a higher level conceptual abstraction with selected
specific data model
– Physical Design: storage and structure of the database
24
 Continued..
IV. Implementation: the testing and deployment of the designed
database for use.

V. Operation and Support: administering and maintaining the operation

of the database system and providing support to users.

25
 3.1.1 Conceptual Database Design
Conceptual design revolves around discovering and analyzing
organizational and user data requirements.

The important activities are: to identify entities, attributes,

relationships, and constraints.

And based on these identified components then develop the ER model

using ER diagrams.

The entity-relationship (ER) data model allows us to describe the data

involved in a real-world enterprise in terms of objects and their
relationships and is widely used to develop an initial database design.
26
 Continued..

ER model provides useful concepts that allow us to move from

an informal description of what users want from their database to
a more detailed and precise description that can be implemented
in a DBMS.

Entity-Relationship (ER) model is a popular high-level

conceptual data model.

27
 3.2. Developing an E-R Diagram
 Designing conceptual model for the database is not a one linear
process but an iterative activity where the design is refined again and
again.
 To identify the entities, attributes, relationships, and constraints on the
data, there are different set of methods used during the analysis phase.
 These include information gathered by:
 Interviewing end users individually and in a group
 Questionnaire survey
 Direct observation
 Examining different documents
 Analysis of requirements gathered 28
 Continued…
The basic E-R model is graphically depicted and presented for
review.

The process is repeated until the end users and designers agree
that the E-R diagram is a fair representation of the organizations
activities and functions.

 Checking for Redundant Relationships in the ER Diagram

The last phase in ER modelling is validating an ER Model

against requirement of the user.
29
3.3. Components of Entity-Relational (ER) Model
Entity-Relationship modelling is used to represent conceptual view of the
database. The main components of ER Modelling are:

1. Entities: real world physical or logical object. Entities are corresponding to

entire table, not row. It is represented by Rectangle.

2. Attributes: Represents the property used to describe an entity or a

relationship. Represented by Oval.

3. Relationships: Represents the association that exist between entities.

Represented by Diamond.

4. Constraints: Represent the constraint in the data. It is a rules that should be

obeyed while manipulating the data.
30
Components of ER representation by Symbols

31
 Continued..
Therefore, before working on the conceptual design of the
database, one has to know and answer the following basic
questions.
 What are the entities and relationships in the enterprise?
 What information about these entities and relationships should we
store in the database?
 What are the integrity constraints that hold? Constraints on each
data with respect to update, retrieval and store.
 Represent this information pictorially in ER diagrams, then map
ER diagram into a relational schema.
32
 3.3.1. The Entities
An entity may be an object with a physical existence such as:
particular person, car, house, or employee or it may be an object with a
conceptual existence such as: company, a job, or a university course.

For example, in a University database, the entities can be Professor,

Students, Courses, etc.

Entities has attributes, which can be considered as properties

describing it, for example, for Professor entity, the attributes are
Professor_Name, Professor_Address, Professor_Salary, etc.

The attribute value gets stored in the database.

33
 Continued..
Example of Entity in DBMS:

Here, Professor_Name, Professor _Address and Professor _Salary are

attributes. Professor_ID is the primary key.

34
 Entity Types and Entity sets
A database usually contains groups of entities that are similar.
For example, a company employing hundreds of employees may
want to store similar information concerning each of the
employees.
These employee entities share the same attributes, but each entity
has its own value(s) for each attribute.
An entity type defines a collection (or set) of entities that have the
same attributes.
The collection of all entities of a particular entity type in the database
at any point in time is called an entity set
35
 Continued..

 The name given to an entity should always be a singular noun

descriptive of each item to be stored in it.
 Example: student NOT students.
36
 Types of DBMS Entities
1) Strong Entity: The strong entity has a primary key. Its existence
is not dependent on any other entity. It is represented by a single
rectangle.
Ex: Professor is a strong entity and the primary key is Professor_ID.

2) Weak Entity: The weak entity in DBMS do not have a primary

key and are dependent on the parent entity
It cannot exist without the entity with which it has a relationship. It
is represented by double rectangle.

37
 Continued..
Example: Professor is a strong entity, and the primary key is Professor_ID.
However, another entity is Professor_Dependents, which is our Weak Entity.

This is a weak entity since its existence is dependent on another entity

Professor. A Professor has Dependents.
3) Associative entity: Associative entities relate the instances of several
entity types. They also contain attributes specific to the relationship
between those entity instances.

38
 Continued..
Example 2: Suppose we have two entity type EMPLOYEES and
DEPENDANT (child). The EMPLOYEE entity has attributes as
Emp_id, Emp_name, Job_title, Age, Salary, Address. And, the
DEPENDANT has attributes as Name, Age and Relation. So, Emp_id
is the key attribute of the EMPLOYEES entity type.

EMPLOYEES is a strong entity and DEPENDANT is a weak entity.

The discriminator or the partial key for the weak entity here is the
„name‟ attribute. This partial key along with the key attribute of the
EMPLOYEE helps in identifying each DEPENDANT entity uniquely.
39
 Continued..
Also, each and every DEPENDANT entity is related to one of the
EMPLOYEE entity.

All the EMPLOYEE entity may not be related to one or the other
DEPENDANT entity but all the DEPENDANT entity is related to one
or the other EMPLOYEE entity. This is called total participation.

The strong entity EMPLOYEE is used to identify the weak entity

DEPENDANT. So, there is a relationship between these two entity
type. This relationship is called an identifying relationship.

40
 Continued..
The above relationship can be represented by the E-R diagram in the following way.

 ID is the primary key (represented with a line) and the Name in Dependent entity is called Partial
Key (represented with a dotted line).
41
 3.3.2. The Attributes
Each entity has attributes. The attributes are the items of information
which characterize and describe these entities.
A particular entity will have a value for each of its attributes. The attribute
values that describe each entity become a major part of the data stored in
the database.
For example: ID, Name, Age, Salary and Home_phone can be the attributes
of the employee entity with the values “12‟‟, “Kebede", "23", “7000” and
"046-881-3456," respectively.
Null Values: NULL applies to attributes which are not applicable or which do not
have values. Value of a key attribute cannot be null.
Default value: assumed value if no explicit value.
42
 Types of Attributes
Simple (atomic) Vs. Composite attributes.
Simple attribute: contains a single value (not divided into sub parts).
 Example: Age, gender, SSN, etc.

Composite attribute: is divided into sub parts (composed of other

attributes).
For example: Address(Apt#, House#, Street, City, State, ZipCode,
Country), or Name(FirstName, MiddleName, LastName).
Composition may form a hierarchy where some components are
themselves composite.

43
 Continued..
Single-valued Vs. multi-valued attributes.

Single-valued attribute: have only single value (the value may change
but has only one value at one time). E.g.: Name, Sex, Id. No., DoB,..etc.

Multi-Valued attribute: have more than one value. E.g.: Address,

Person may have several college degrees. An Attributes are depicted by
double ellipse.

44
 Continued..
Stored vs. Derived Attribute
Stored attribute: 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), G.P.A (grade point/credit
hours).
For example: Age can be derived from date of birth, where, Age is the
derived attribute and DOB is the stored attribute.

45
 Continued..
Key Attribute: Those attributes which can be identified uniquely
in the relational table are called key attributes. E.g., Roll-No is the
key attribute because it can uniquely identify the student.

46
 Continued..
Complex attribute: If any attribute has the combining property of
multi values and composite attributes, then it is called a complex
attribute. It means if one attribute is made up of more than one
attribute and each attribute can have more than one value.

For example: Let us consider a person having multiple phone

numbers, emails, and an address.

Phone number and email are examples of multi-valued attributes

Address is an example of the composite attribute, because it can be

divided into house number, street, city, and state.

47
Continued..

48
Summary of the notation for ER- diagrams

49
 3.3.3. The Relationships
 Relationships are represented by DIAMOND shaped symbols.

 The association among the entities is called a relationship.

 For example, an employee works_at a department, a student enrolls in

a course. Here, works_at and enrolls are called the Relationships.

 Name of the Relationship is written inside the diamond box.

 All the entities (rectangles) participating in a Relationship, are

connected to it by a line. Related entities require setting of LINKS
from one part of the database to another.

50
 Continued..
 Role names are different from the names of entities forming the
relationship: one entity may take on many roles, the same role may be
played by different entities.

51
 Degree of a Relationship
 The number of entities participating in a relationship is called the DEGREE
of the relationship. Among the Degrees of relationship, the following are the
basic:
1) Unary/Recursive Relationship: Tuples/records of a Single entity are
related with each other.
2) Binary Relationship: Tuples/records of two entities are associated in a
relationship.
3) Ternary Relationship: Tuples/records of three different entities are
associated.
4) N-ary Relationship: Tuples from arbitrary number of entity sets are
participating in a relationship.
52
 Cardinality of the Relationship
 The number of instances participating or associated with a single
instance from an entity in a relationship is called the CARDINALITY of
the relationship. The major cardinalities of a relationship are:

1) One-to-One: one tuple is associated with only one other tuple.

 Example: Building – Location as a single building will be located in a single

location and as a single location will only accommodate a single Building.

2) One-to-Many: one tuple can be associated with many other tuples, but
not the reverse.

Example: Department-Student as one department can have multiple

students.
53
 Continued..
3) Many-to-One: many tuples are associated with one tuple but not the
reverse.
 Example: Employee – Department: as many employees belong to a single
department.
4) Many-to-Many: one tuple is associated with many other tuples and
from the other side, with a different role name one tuple will be
associated with many tuples.
 Example: Student – Course as a student can take many courses and a single
course can be attended by many students.
However, the degree and cardinality of a relation are different from
degree and cardinality of a relationship.
54
 Participation of an Entity set in a relationship set
Participation constraint of a relationship is involved in identifying and setting
the mandatory or option feature of an entity occurrence to take a role in a
relationship.
There are two distinct participation constraints with this respect, namely
total participation and partial participation.

55
 Continued..

 Participation of DEPARTMENT in Manages relationship with EMPLOYEE is total

since every department should have a manager.
56
 Key constraints
 A key is an attribute or set of attributes which helps us in uniquely
identifying the rows of a table.
 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
record.
 Key can be of the following types:

57
 1) Super Key
Super Key is an attribute/set of attributes that uniquely identify a tuple within
a relation. The superset of primary keys is called Super Key.

A super key or simply key is a combination of all possible attribute which can
uniquely identify the rows (tuples) in a table. It allows to have NULL values.

Example: In the given Student Table we can have the following keys as the
super key.

 {Roll_no}, {Registration_no}, {Roll_no, Registration_no}, {Roll_no, Name}, {Name,

Registration_no}, {Roll_no, Name, Registration_no} are keys which uniquely identify each
row. So, each of these keys is super key. 58
 2) Candidate Key
A candidate key is a set of keys that can uniquely identify any row in a table.

A candidate key is a minimal super key or a super key with no redundant

attribute.

It is selected from the set of the super key which means that all candidate
keys are super key.

Candidate Keys are not allowed to have NULL values.

A candidate key is a super key such that no proper subset of that collection
is a Super Key within the relation.

Example: In the above example, {Roll_no}, {Registration_no} are the only

candidate keys. 59
 3) Primary Key
The primary key is the minimal set of attributes which uniquely identifies any
row of a table. It is selected from a set of candidate keys.

Any candidate key can become a primary key.

It depends upon the requirements and is done by the DBA.

The primary key cannot have a NULL value. It cannot have a duplicate value.

Example: In the above example, ‘Roll_no’ is the primary key.

4) Alternate Key: All the candidate key which are not a primary key are
called an alternate key.
Example: In the above example, since we have made „Roll_no‟ as the Primary
Key our Alternate Key would be ‘Registration_no’.
60
 5) Foreign Key
Foreign Key is an attribute, or set of attributes, within one relation that
matches the candidate key of some relation.

The foreign key of a table is the attribute which establishes the

relationship among tables.

The foreign key is the attribute which points to the primary key of another
table. i.e. a foreign value is an attribute value that acts as a primary key in
another table.

61
 Continued..
Example: If we have two tables of Student and Course then we can
establish a relationship between these two tables using a foreign key.

The „Course_id‟ in the Student table is the foreign key as it establishes the
link between the Student and Course Table.

62
 6) Composite Key
 A composite key is a combination of multiple attributes of a table that
cannot be used as the key to identify a row individually but in combination
can define tuple uniquely.

 It acts as a primary key if there is no primary key in a table.

 Two or more attributes are used together to make a composite key.

 Example: Roll_no + Name can be combined together to access the details

of a student.

7) Unique Key: The unique key is similar to the primary key, but allows
NULL values in the column.

63