3/12/2020

Prepared by Shimi Biju

RELATIONAL MODEL

Prepared by Shimi Biju

RELATIONAL DATA MODEL

 The most popular data model in DBMS is the Relational
Model.
 It is more scientific a model than others.

 The main highlights of this model are −

 Data is stored in tables called relations.

 Relations can be normalized.

 In normalized relations, values saved are atomic values.

 Each row in a relation contains a unique value.

 Each column in a relation contains values from a same

domain.
 This model is simple and it has all the properties and
capabilities required to process data with storage efficiency. 2

Prepared by Shimi Biju

1
 3/12/2020

RELATIONAL DATA MODEL (CONT.)

 Tables
 Tuple:- A single row of a table, which contains a single record for
that relation is called a tuple.
 Relation instance :- A finite set of tuples in the
relational database system represents relation instance
 Relation schema:- A set of attributes is called a relation schema
 Relation key:- They are used to establish and identify
relationships between tables and also to uniquely identify any
record or row of data inside a table. 3
 Attribute domain: It is the set of values allowed in an attribute
Prepared by Shimi Biju

ENTITY-RELATIONSHIP MODEL
 Entity-Relationship (ER) Model is based on the notion of real-
world entities and relationships among them. While formulating
real-world scenario into the database model, the ER Model creates
entity set, relationship set, general attributes and constraints.
 ER Model is best used for the conceptual design of a database. ER
Model is based on −
 Entities and their attributes.
 Relationships among entities.

Prepared by Shimi Biju

2
 3/12/2020

ENTITY SET
 An entity is a “thing” or “object” in the real world that is
distinguishable from all other objects. For example, each
person in an enterprise is an entity.
 An entity has a set of properties, and the values for some set
of properties may uniquely identify an entity. For instance, a
person may have a person-id.
 An entity set is a set of entities of the same type that share the
same properties, or attributes.
 An entity is represented by a set of attributes. Attributes are
descriptive properties possessed by each member of an entity
set.
 The designation of an attribute for an entity set expresses that
the database stores similar information concerning each entity
in the entity set;
5

Prepared by Shimi Biju

ATTRIBUTES
 For each attribute, there is a set of permitted values, called
the domain, or value set, of that attribute.
 The domain of attribute course id might be the set of all text
strings of a certain length.
 An attribute, as used in the E-R model, can be characterized
by the following attribute types. Entities are represented by
means of rectangles.
 Rectangles are named with the entity set they represent.
Every ellipse represents one attribute and is directly
connected to its entity (rectangle).

Prepared by Shimi Biju

3
 3/12/2020

ATTRIBUTES (CONT.)
 Simple and
Composite attributes

 Single-valued and
multivalued
attributes

Prepared by Shimi Biju

ATTRIBUTES (CONT.)
 Derived Attribute

Prepared by Shimi Biju

4
 3/12/2020

CARDINALITY
 In terms of data models, cardinality refers to the relationship between two
tables
 Mapping cardinalities, or cardinality ratios, express the number of entities
to which another entity can be associated via a relationship set. Mapping
cardinalities are most useful in describing binary relationship sets, although
they can contribute to the description of relationship sets that involve more
than two entity sets.

 One to one. An entity in A is associated with at most one entity in B, and an

entity in B is associated with at most one entity in A. For example, a
relationship between person and passport table is one to one because a person
can have only one passport and a passport can be assigned to only one
person.

Prepared by Shimi Biju

 One to many. An entity in A is associated with any

number (zero or more) of entities in B. An entity in B,
however, can be associated with at most one entity in A.
 For example, relationship between customer and order
table is one to many because a customer can place many
orders but a order can be placed by a single customer
alone.

10

Prepared by Shimi Biju

5
 3/12/2020

CARDINALITY (CONT)
 Many to one. An entity in A is associated with at most one
entity in B. An entity in B, however, can be associated with
any number (zero or more) of entities in A.

 For example, relationship between student and university is

many to one because a university can have many students
but a student can only study only in single university at a
time.

11

Prepared by Shimi Biju

 Many to many. An entity in A is associated with any

number (zero or more) of entities in B, and an entity in B
is associated with any number (zero or more) of entities
in A.
 For example, relationship between student and course
table is many to many because a student can take many
courses at a time and a course can be assigned to many
students.

12

Prepared by Shimi Biju

6
 3/12/2020

KEYS
Keys are very important part of Relational database. They are used to
establish and identify relation between tables. They also ensure that each
record within a table can be uniquely identified by combination of one or
more fields within a table.

 Primary Key
A primary key is a candidate key that is most appropriate to be the main reference key for the
table. As its name suggests, it is the primary key of reference for the table and is used
throughout the database to help establish relationships with other tables. As with any
candidate key the primary key must contain unique values, must never be null and uniquely
identify each record in the table.
Customer Name First Name Last Name
1 Sally Thompson
2 Sally Henderson
3 Harry Henderson
4 Sandra Wellington 13

Prepared by Shimi Biju

KEYS (CONT.)
Foreign Keys
 Foreign key are columns that point to primary key columns.
 Example, OrderNo is the primary key of the table ORDERS below and
CustomerNo is a foreign key that points to the primary key in the CUSTOMERS
table.
Order Employee Customer Supplier Price Item
No No No
1 1 42 Harrison $235 Desk
2 4 1 Ford $234 Chair
3 1 68 Harrison $415 Table
4 2 112 Ford $350 Lamp
5 3 42 Ford $234 Chair
6 2 112 Ford $350 Lamp
7 2 42 Harrison $235 Desk 14

Prepared by Shimi Biju

7
 3/12/2020

KEYS (CONT.)
 The only true requirement of the column at which a foreign key
points is that it must contains unique values.

 Relational Integrity Rules

 Entity integrity

Referential integrity.

 Null or Unknown Value

 Null represents a value for an attribute that is currently unknown or
is not applicable for this tuple.
 A null can be taken to mean the logical value 'unknown'.

 Null is not the same as a zero numeric value or a text string filled
with spaces; zeros and spaces are values, but a null represents the
absence of a value. Therefore, nulls should be treated differently 15
from other values.
Prepared by Shimi Biju

KEYS (CONT.)
 Candidate Key

 A candidate is a subset of a super key. A candidate key is a single field or

the least combination of fields that uniquely identifies each record in the
table.
 Every table must have at least one candidate key but at the same time can
have several.
 It is an attribute or set of attribute that can act as a primary key for a table
to uniquely identify each record in that table.

16

Prepared by Shimi Biju

8
 3/12/2020

KEYS (CONT.)
 Super Key

 A Super key is any combination of fields within a table that uniquely

identifies each record within that table. It is defined as a set of
attributes within a table that uniquely identifies each record within a
table. Super Key is a superset of Candidate key.

17

Prepared by Shimi Biju

KEYS (CONT.)
Alternative or Secondary Key
A table may have one or more choices for the primary key.
 Those not selected are known as secondary keys or
alternative keys.
 For example in the table showing candidate keys above we
identified two candidate keys, studentId and firstName +
lastName.
 The studentId would be the most appropriate for a primary
key leaving the other candidate key as secondary or
alternative key.
 It should be noted for the other key to be candidate keys,
we are assuming you will never have a person with the
same first and last name combination. As this is unlikely 18
we might consider fistName+lastName to be a suspect
candidate key Prepared by Shimi Biju

9
 3/12/2020

ENTITY INTEGRITY RULE

 In order to understand the concept of primary key and null, consider the
following of STUDENT database

 Now record 3 and record 5 are not distinguishable as there Rollno is Null
and it is not possible to exactly locate one of the students as he is having
common name, class and marks with some other student. Record number 3
and 5 violates the rule of primary key because the Rollno is primary key,
all the value of attributes are unique but their primary key entries are
NULL and this violates the integrity rule 1, so these records are not
19
allowed in RDBMS.

Prepared by Shimi Biju

REFERENTIAL INTEGRITY
 Referential integrity is a property of data stating that all its references are
valid. In the context of relational databases, it requires that if a value of one
attribute of a relation references a value of another attribute, then the
referenced value must exist..
 Consider the following database to understand the referential
integrity rule:

20

Prepared by Shimi Biju

10
 3/12/2020

E-R DIAGRAM
 ER-Diagram is a visual representation of data that describes how data
is related to each other. E-R diagram can express the overall logical
structure of a database graphically. E-R diagrams are simple and
clear—qualities that may well account in large part for the
widespread use of the E-R model. Such a diagram consists of the
following major components:
 In E-R Diagram, an entity is represented using rectangles. Consider
an example of an Organisation. Employee, Manager, Department,
Product and many more can be taken as entities from an Organisation.

21

Prepared by Shimi Biju

E-R DIAGRAM (CONT.)

 Weak Entity
 Weak entity is an entity that
depends on another entity.
Weak entity doen't have key
attribute of their own. Double
rectangle represents weak
entity.

 Attribute
 An Attribute describes a
property or characteristic of an
entity. For example, Name,
Age, Address etc can be
attributes of a Student. An
attribute is represented using
22
eclipse.
Prepared by Shimi Biju

11
 3/12/2020

E-R DIAGRAM (CONT.)

 Key Attribute
 Key attribute represents the
main characterstic of an
Entity. It is used to represent
Primary key. Ellipse with
underlying lines represent
Key Attribute.
 Composite Attribute

 An attribute can also have

their own attributes. These
attributes are known
as Composite attribute.
23

Prepared by Shimi Biju

RELATIONSHIP
 A relationship is an association among several entities. For example, we
can define a relationship that associates with entity.
 We can define the relationship set takes to denote the association between a
student and the course sections in which that student is enrolled.
 The association between entity sets is referred to as participation; that is,
the entity sets E1, E2,..., En participate in relationship set R.
 A relationship instance in an E-R schema represents an association between
the named entities in the real-world enterprise that is being modeled.

24

Prepared by Shimi Biju

12
 3/12/2020

E-R DIAGRAM (CONT.)

 Relationship
A Relationship describes
relations between entities.
Relationship is represented
using diamonds.
 Binary Relationship

 One to One : This type

of relationship is rarely
seen in real world.

25

Prepared by Shimi Biju

THE ENHANCED ER MODEL

 As the complexity of data increased in the late 1980s, it

became more and more difficult to use the traditional ER
Model for database modelling.
 Hence some improvements or enhancements were made
to the existing ER Model to make it able to handle the
complex applications better.
 Hence, as part of the Enhanced ER Model, along with
other improvements, three new concepts were added to
the existing ER Model, they were:
 Generalization

 Specialization
27
 Aggregration
Prepared by Shimi Biju

13
 3/12/2020

GENERALIZATION
 Generalization is a bottom-up approach in which two
lower level entities combine to form a higher level entity.
In generalization, the higher level entity can also
combine with other lower level entities to make further
higher level entity.
 It's more like Superclass and Subclass system, but the
only difference is the approach, which is bottom-up.
Hence, entities are combined to form a more generalised
entity, in other words, sub-classes are combined to form
a super-class.

28

Prepared by Shimi Biju

GENERALIZATION

For example, Saving and Current account types entities can be

generalized and an entity with name Account can be created, which 29
covers both.

Prepared by Shimi Biju

14
 3/12/2020

GENERALIZATION

STUDENT and FACULTY can be generalized to a higher level

entity called PERSON. In this case, common attributes like
P_NAME, P_ADD become part of higher entity (PERSON) and
specialized attributes like S_FEE become part of specialized 30
entity (STUDENT).
Prepared by Shimi Biju

SPECIALIZATION

 Specialization is opposite to Generalization. It is a top-

down approach in which one higher level entity can be
broken down into two lower level entity.
 In specialization, a higher level entity may not have any
lower-level entity sets, it's possible.


31

Prepared by Shimi Biju

15
 3/12/2020

32

Prepared by Shimi Biju

EMPLOYEE entity in an Employee management system can be

specialized into DEVELOPER, TESTER etc. In this case, common
attributes like E_NAME, E_SAL etc. become part of higher entity
(EMPLOYEE) and specialized attributes like TES_TYPE become part of 33
specialized entity (TESTER).
Prepared by Shimi Biju

16
 3/12/2020

AGGREGRATION
 Aggregration is a process when relation between two
entities is treated as a single entity.

The relationship between Center and Course together, is acting as an Entity, which
is in relationship with another entity Visitor. Now in real world, if a Visitor or a 34
Student visits a Coaching Center, he/she will never enquire about the center only
or just about the course, rather he/she will ask enquire about both.
Prepared by Shimi Biju

AGGREGRATION

Employee working for a project may require some machinery. So,

REQUIRE relationship is needed between relationship WORKS_FOR and
entity MACHINERY. Using aggregation, WORKS_FOR relationship with
its entities EMPLOYEE and PROJECT is aggregated into single entity 35
and relationship REQUIRE is created between aggregated entity and
MACHINERY. Prepared by Shimi Biju

17
 3/12/2020

CODD’S RULES
 Dr Edgar F. Codd, after his extensive research on the
Relational Model of database systems, came up with
twelve rules of his own, which according to him, a
database must obey in order to be regarded as a true
relational database.
 These rules can be applied on any database system that
manages stored data using only its relational capabilities.
This is a foundation rule, which acts as a base for all the
other rules.

36

Prepared by Shimi Biju

RULE 1: INFORMATION RULE

 The data stored in a database, may it be user data or

metadata, must be a value of some table cell. Everything
in a database must be stored in a table format.

37

Prepared by Shimi Biju

18
 3/12/2020

RULE 2: GUARANTEED ACCESS RULE

 Every single data element (value) is guaranteed to be

accessible logically with a combination of table-name,
primary-key (row value), and attribute-name (column
value). No other means, such as pointers, can be used to
access data.

38

Prepared by Shimi Biju

RULE 3: SYSTEMATIC TREATMENT OF NULL VALUES

 The NULL values in a database must be given a

systematic and uniform treatment. This is a very
important rule because a NULL can be interpreted as one
the following − data is missing, data is not known, or
data is not applicable.

39

Prepared by Shimi Biju

19
 3/12/2020

RULE 4: ACTIVE ONLINE CATALOG

 The structure description of the entire database must be

stored in an online catalog, known as data dictionary,
which can be accessed by authorized users. Users can
use the same query language to access the catalog which
they use to access the database itself.

40

Prepared by Shimi Biju

RULE 5: COMPREHENSIVE DATA SUB-LANGUAGE RULE

 A database can only be accessed using a language having

linear syntax that supports data definition, data
manipulation, and transaction management operations.
This language can be used directly or by means of some
application. If the database allows access to data without
any help of this language, then it is considered as a
violation.

41

Prepared by Shimi Biju

20
 3/12/2020

RULE 6: VIEW UPDATING RULE

 All the views of a database, which can theoretically be

updated, must also be updatable by the system.

42

Prepared by Shimi Biju

RULE 7: HIGH-LEVEL INSERT, UPDATE, AND DELETE

RULE

 A database must support high-level insertion, updation,

and deletion. This must not be limited to a single row,
that is, it must also support union, intersection and minus
operations to yield sets of data records.

43

Prepared by Shimi Biju

21
 3/12/2020

RULE 8: PHYSICAL DATA INDEPENDENCE

 The data stored in a database must be independent of the

applications that access the database. Any change in the
physical structure of a database must not have any
impact on how the data is being accessed by external
applications.

44

Prepared by Shimi Biju

RULE 9: LOGICAL DATA INDEPENDENCE

 The logical data in a database must be independent of its

user’s view (application). Any change in logical data
must not affect the applications using it. For example, if
two tables are merged or one is split into two different
tables, there should be no impact or change on the user
application. This is one of the most difficult rule to apply.

45

Prepared by Shimi Biju

22
 3/12/2020

RULE 10: INTEGRITY INDEPENDENCE

 A database must be independent of the application that

uses it. All its integrity constraints can be independently
modified without the need of any change in the
application. This rule makes a database independent of
the front-end application and its interface.

46

Prepared by Shimi Biju

RULE 11: DISTRIBUTION INDEPENDENCE

 The end-user must not be able to see that the data is

distributed over various locations. Users should always
get the impression that the data is located at one site
only. This rule has been regarded as the foundation of
distributed database systems.

47

Prepared by Shimi Biju

23
 3/12/2020

RULE 12: NON-SUBVERSION RULE

 If a system has an interface that provides access to low-

level records, then the interface must not be able to
change the system and bypass security and integrity
constraints.
 Let's say you define a non-null constraint on a column.
Can you bypass the RDBMS (by, say, using a low-level
Oracle-provided utility API) to insert a null in that
column? If so, you've violated that rule..

48

Prepared by Shimi Biju

INTEGRITY CONSTRAINTS

 Integrity constraints are a set of rules. It is used to

maintain the quality of information.
 Integrity constraints ensure that the data insertion,
updating, and other processes have to be performed in
such a way that data integrity is not affected.
 Thus, integrity constraint is used to guard against
accidental damage to the database.

49

Prepared by Shimi Biju

24
 3/12/2020

TYPES OF INTEGRITY CONSTRAINT

50

Prepared by Shimi Biju

1. DOMAIN CONSTRAINTS

 Domain constraints can be defined as the definition of a

valid set of values for an attribute.
 The data type of domain includes string, character,
integer, time, date, currency, etc.
 The value of the attribute must be available in the
corresponding domain.
Example:

51

Prepared by Shimi Biju

25
 3/12/2020

2. ENTITY INTEGRITY CONSTRAINTS

 The entity integrity constraint 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.
Example:

52

Prepared by Shimi Biju

3. REFERENTIAL INTEGRITY CONSTRAINTS

 A referential integrity constraint is specified between two

tables.
 In the Referential integrity constraints, if a foreign key in
Table 1 refers to the Primary Key of Table 2, then every
value of the Foreign Key in Table 1 must be null or be
available in Table 2.

53

Prepared by Shimi Biju

26
 3/12/2020

3. REFERENTIAL INTEGRITY CONSTRAINTS

54

Prepared by Shimi Biju

4. KEY CONSTRAINTS
 Keys are the entity set that is used to identify an entity within
its entity set uniquely.
 An entity set can have multiple keys, but out of which one key
will be the primary key.
 A primary key can contain a unique and null value in the
relational table.

Example:

55

Prepared by Shimi Biju

27