Chapter Two

Relational Data Model

1
 Agenda (chapter three)
 Overview/Introduction
 The Hierarchical Model
 The Network Model
 The Relational Model
 Terminologies,
 Relational Algebra,
 Properties of Relation,
 views and
 Relational Integrity Rules

2
 Data Models: Overview
 A specific DBMS has its own specific Data Definition
Language,
 however this type of language is too low level to
describe the data requirements of an organization in a
way that is readily understandable by a variety of users.
 We need a higher-level language.
 Such a higher-level is called data-model.

3
 Cont…
 Data Model
 A set of concepts to describe the structure of a database,
and certain constraints that the database should obey.
 Data model is a description of the way that data is stored in
a database.

 Data model helps to understand the relationship

between entities and to create the most effective
structure to hold data.
4
 Cont…
 Data Model is a collection of tools or concepts for
describing
 Data
 Data relationships
 Data semantics
 Data constraints
 These concepts will be implemented on various DBMS
Software

5
 DBMS software Options
Enterprise RDMS
Consumer
 Oracle
 Microsoft Excel
 IBM/DB2
- Limit of 65,536 Rows
 MS SQL-server
 Microsoft Access
 Sybase
 FileMaker Pro  Informix
 MySQL (Open Source)  Lotus Notes
 Postgres (Open  MySQL (Open Source)
Source)  Postgres (Open Source)

6
7
 Cont…
 A data model is a collection of tools or concepts for
describing data, the meaning of data, data relationships,
and data constraints.
 Within the history of database systems we have
 the first generation data models
 Hierarchical Model
 Network Model
 The second generation data models
 Relational Model
 The third generation data models
 Object Oriented Data Models

8
 Cont…
 Data model Evolution
 File Systems
 Hierarchical Model (Tree-based)
 Network Model (Graph-based)
 Relational Model
 Object Oriented Model
 Object/Relational Model

9
 Evolution of Data models
Systems

10
 Cont…
 The main purpose of Data Model is to represent the data
in an understandable way.
 Many data models available, falling in either of
the following Categories
 Object-based data models
 Record-based data models
 Physical data models

11
 Cont…
 Record-based Data Models
 Consist of a number of fixed format records.
 Each record type defines a fixed number of fields,
 Each field is typically of a fixed length.
 There are three major types
 Hierarchical Data Model (old)
 Network Data Model (old)
 Relational Data Model

12
 Hierarchical Model
 The simplest data model
 Record type is referred to as node or segment
 The top node is the root node
 Nodes are arranged in a hierarchical structure as sort
of upside-down tree
 A parent node can have more than one child node

13
 Hierarchical Model

14
 Network Model
 Allows record types to have more than one parent
unlike hierarchical model
 A network data models sees records as set members
 Each set has an owner and one or more members

15
 Networked Data Model

16
 Relational Data Model
 Developed by Dr. Edgar Frank Codd in 1970
(famous paper, 'A Relational Model for Large Shared
Data Banks')
 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

17
 Relation/Table

18
 A Relational model…Example

19
 Terminologies
 Relation
 Two dimensional table
 Stores information or data in the form of tables  rows
and columns
 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

20
 Cont…
 Domain
 A set of allowable values for one or more attributes
 Example for an attribute we may define domain as “ character:
size 1, value M or F”
 Tuple
 Is a row of a relation
 Are extension (state) of a relation which changes over time

21
 Cont…
 Degree
 The degree of a relation is the number of attribute it contains
 Binary, Ternary, n-ary..
 Is a property of the intension (schema) of the relation
 Cardinality
 The cardinality of a relation is the number of tuples it contains
 Is a property of the extension of the relation

22
 Cont..
 Relational database
 A collection of normalized relations with distinct relation names
 Consists of relations that are appropriate structured
 This appropriateness is what we call normalization (later on this, chapter
5)
 The rows represent records (collections of information about
separate items)
 The columns represent fields (particular attributes of a record)

23
 Cont…
Alternative terminologies

Formal terms Alternative 1 Alternative 2

Relation Table File

Tuple Row Record

Attribute Column Field

24
 Cont…
 Records are related by the data stored jointly in the fields
of records in two tables or files. (Logical Relationship)
 The related tables contain information that creates the
relation
 The tables seem to be independent but are related some
how.(logically by data values)
 No physical consideration of the storage is required by the
user
 Many tables are merged together to come up with a new
virtual view of the relationship

25
 Cont…
 Conducts searches by using data in specified columns of one
table to find additional data in another table (concept of
foreign key/attribute)
 In conducting searches, a relational database matches
information from a field in one table with information in a
corresponding field of another table to produce a third table that
combines requested data from both tables

26
 Properties of a relation
 Everything is a Table/Relation
 A relation has a unique name
 Each cell in a relation contains exactly one atomic (single)
value
 Each attribute has a distinct name
 The values of an attribute are all from the same domain
 Each tuple is distinct; there are no duplicate tuples
 The order of attributes and tuples has no significance

27
 Cont…
 A table is either a BASE TABLES (Named Relations) or
VIEWS (Unnamed Relations)
 Only Base Tables are physically stored
 VIEWS are derived from BASE TABLES with SQL
instructions like:
 [SELECT .. FROM .. WHERE .. ORDER BY]
 A relational database is the collection of tables
 Each entity in one table

28
 Cont…
 Uniqueness: each row of a table is uniquely identified by a
primary key composed of one or more columns:
 Uniqueness feature to reduce duplicated data
 One or a group of columns, that can uniquely identify a row
in a table is called a candidate key
 A relation can have more than one candidate key
 Rules on Primary Key;
 Entity integrity rule -states that no component of
the primary key may contain a null value.

29
 Cont…
 Cross Referencing: A column or combination of columns
that matches the Primary Key (or Candidate Key) of another
table is called a Foreign Key.
 used to cross-reference from one table to the other.
 Rule on Foreign Key
 the Referential Integrity Rule - states that, a value
for a foreign key in a table must match the primary
key value of another table in the database, otherwise it
must be NULL.

30
 Key constraints (Relational Key)
 If tuples are needed to be unique in the database, then we
need to make each tuple distinct.
 To do this we need to have relational keys that uniquely
identify each relation.
 Super Key: an attribute or set of attributes that uniquely identifies a
tuple within a relation.

 Candidate Key: a super key such that no proper subset of that

collection is a Super Key within the relation.
 (super key containing minimum number of attributes necessary for
unique identification)

31
 Cont…
 For example, Given an employee table, consisting of the
columns employeeID, name, job, and departmentID, we
could use the employeeID in combination with any or all
other columns of this table to uniquely identify a row in the
table.
 Examples of superkeys in this table would be
 {employeeID, Name},
 {employeeID, Name, job}, and
 {employeeID, Name, job, departmentID}.

32
 Cont…
 In a real database we don't need values for all of those
columns to identify a row.
 We only need, per our example, {employeeID}.
 This is a minimal Superkey – that is, a minimal set of
columns that can be used to identify a single row.
 The Minimal Superkey is called Candidate Key
 So, employeeID is a candidate key for the Employee table

33
 Cont…
 A candidate key has two properties:
 Uniqueness
 Irreducibility (the number of attributes can not be reduced)
 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 (Composite Candidate Key).

34
 Cont…
 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 primary key (candidate key) of
some relation.
 A foreign key is a link between different relations to create the view
or the unnamed relation

35
 Relational Integrity
 Domain Integrity: No value of the attribute should be
beyond the allowable limits

 Entity Integrity: In a base relation, no attribute of a

Primary Key can assume a value of NULL

36
 Examples:
• Domain definitions enforce domain integrity constraints

37
 Cont…
 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

 Enterprise Integrity: Additional rules specified by the

users or database administrators of a database are
incorporated
 Example restriction on a number of workers in a given branch office
(branch as a relation)

38
 Referential integrity constraints

Referential
integrity
constraints are
drawn via arrows
from dependent to
parent table

39
 Rules
 Referential Integrity–rule states that any foreign key value MUST match a primary key
(candidate key) value in the relation of the other side or the foreign key can be null.
 For example: Delete Rules
 Restrict–don’t allow delete of “parent” side if related rows exist in “dependent” side
 Cascade–automatically delete “dependent” side rows that correspond with the
“parent” side row to be deleted
 Set-to-Null–set the foreign key in the dependent side to null if deleting from the
parent side  not allowed for weak entities

40
 Schema for four relations

Primary Key
Foreign Key
(implements 1:N relationship
between customer and order)

Combined, these are a composite

primary key (uniquely identifies the
order line)…individually they are
foreign keys (implement M:N
relationship between order and product)

41
 SQL table definitions

Referential
integrity
constraints are
implemented with
foreign key to
primary key
references

42
 Relational Views
 Relations are perceived as a Table from the users’ perspective.
 Actually, there are two kinds of relation in relational
database.
 The two categories or types of Relations are Named and
Unnamed Relations.
 The basic difference is on how the relation is created, used
and updated:

43
 Cont…
 Base Relation
 A Named Relation corresponding to an entity in the conceptual
schema, whose tuples are physically stored in the database.
 View (Unnamed Relation)
 A View is the dynamic result of one or more relational operations
operating on the base relations to produce another virtual relation
that does not actually exist as presented.

44
 View …
 So a view is virtually derived relation that does not
necessarily exist in the database but can be produced upon
request by a particular user at the time of request.

 The virtual table or relation can be created from single or

different relations by extracting some attributes and records
with or without conditions.

45
 Cont…
 Purpose of a view
 Hides unnecessary information from users:
 since only part of the base relation are included
 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.
 Update on view with aggregation and summary is not allowed.
 Since aggregation and summary results are computed from a base relation and does
not exist actually.
46
 End Of Chapter Two

47