TOPIC 2: THE RELATIONAL DATA MODEL

Introduction to Data Modeling:

• A data model is a picture or description which shows how the data is to be arranged to achieve a
given task.
• It is a clear model which specifies how the data items are arranged in a given model.
• Some data models which gives a clear picture which shows the manner in which the data records
are connected or related within a file structure. These are called structural data models.
• DBMS organize and structure data so that it can be retrieved and manipulated by different users
and application programs.
• The data structures and access techniques provided by a particular DBMS are called its data
model.
• A data model determined both the personality of a DBMS and the applications for which it is
particularly well suited.

Data Modeling and Data Models

The Importance of Data Models

• Facilitate interaction among the designer, the applications programmer, and the end user
• End users have different views and needs for data
• Data model organizes data for various users
• Data model is a conceptual model - an abstraction
• It’s a graphical collection of logical constructs representing the data structure and relationships
within the database.
Cannot draw required data out of the data model
An implementation model would represent how the data are represented in the database.

Data Model Basic Building Blocks Terminology

• Entity: anything about which data are to be collected and stored
• Attribute: a characteristic of an entity
• Relationship: describes an association among entities
One-to-many (1:M) relationship
Many-to-many (M:N or M:M) relationship
One-to-one (1:1) relationship
• Constraint: a restriction placed on the data
 Characteristics of Data in a Database

The data in a database should have the following features:

• Shared: Data in a database are shared among different users and applications.
• Persistence: Data in a database exist permanently in the sense the data can live beyond the
scope of the process that created it.
• Validity/Integrity/Correctness: Data should be correct with respect to the real world entity that
they represent.
• Security: Data should be protected from unauthorized access.
• Consistency: Whenever more than one data element in a database represents related real-world
values, the values should be consistent with respect to the relationship.
• Non-redundancy: No two data items in a database should represent the same real-world entity.
• Independence: The three levels in the schema (internal, conceptual and external) should be
independent of each other so that the changes in the schema at one level should not affect the
other levels.

Advantages of using database approach:

• Single repository of data is maintained

• All users access the data from the same resource

• Quick retrieval of data

• Reduce application development time

• Flexibility in change of database structure

• Enforce standardization

• Up-to-date information availability

• Authorized access security of data

• Enforce integrity constraints and business rules

• Provide backup and recovery procedure

Evolution of Data Implementation Models

Types of data model

There are four different types of data models

• Flat file database models

• Hierarchical databases
• Network databases
• Relational databases
• Object oriented databases
Flat file database models

A flat file data model consists of one or more readable files normally stored in a text format.
Information in these files is stored in fields. Every flat-file database is different because companies
store different data and companies have different needs. After a flat-file has been created and data
has been stored in these files, a method must be devised in order to retrieve the data, create new
records, update records or delete records.

Empno FirstName MidName Surname Gender Age Country

E0345 Timothy James Robert Male 35 USA

E0767 Fred Mathew Wekesa Male 34 USA

E0783 Odhiambo Oluoch Otieno Male 31 Kenya

E0299 Denis Musau Mutie Male 32 Kenya

E0852 Janeffer Njeri Kamau Female 32 Kenya

E0920 Kellen Ruth Wambugu Female 33 Kenya

E0218 Kelly Johnson Samson Male 34 England

E0830 Henry Geoffrey Hesky Male 35 England

E0666 Erastus James Jacob Male 34 England

E0909 Daniel Peterson Job Male 33 USA

E0606 Peter William Matekwei Male 32 Kenya

E0981 James Griffith Ronald Male 33 USA

Flat-file databases are created and used to migrate data from one database implementation to
another particularly in relational databases.
Disadvantages of flat-file model
1) Do not promote a structure in which data can easily be related.
2) It is difficult to control data duplication
3) The physical location of the data field within the file must be known
4) A program must be developed to manage the data.
Examples of applications you can use to develop flat-file database word processors, spreadsheets.
Hierarchical databases

• Hierarchical Databases is most commonly used with mainframe systems.

• It is one of the oldest methods of organizing and storing data and it is still used by some
organizations for making travel reservations.
• A hierarchical database is organized in pyramid fashion, like the branches of a tree extending
downwards.
• In this model, related fields or records are grouped together so that there are higher-level records
and lower-level records, just like the parents in a family tree sit above the subordinated children.
• Based on this analogy, the parent record at the top of the pyramid is called the root record.
• A child record always has only one parent record to which it is linked, just like in a normal family
tree.
• In contrast, a parent record may have more than one child record linked to it. Hierarchical
databases work by moving from the top down.
• A record search is conducted by starting at the top of the pyramid and working down through the
tree from parent to child until the appropriate child record is found. Furthermore, each child can
also be a parent with children underneath it.
Network databases

• Network databases are similar to hierarchical databases by also having a hierarchical structure.
There are a few key differences, however.
• Instead of looking like an upside-down tree, a network database looks more like a cobweb or
interconnected network of records. In network databases, children are called members and
parents are called owners.
The most important difference is that each child or member can have more than one parent (or
owner).

• Similar to hierarchical databases, network databases are principally used on mainframe

computers.
• Since more connections can be made between different types of data, network databases are
considered more flexible. However, two limitations must be considered when using this kind of
database.
• Similar to hierarchical databases, network databases must be defined in advance. There is also a
limit to the number of connections that can be made between records.
Relational databases
• Pre-relational models depended upon being able to determine explicitly where and how individual
records were stored.
• Early relational proponents argued that the relational data model viewed information logically
rather than physically, but this is not quite correct.
• Earlier data models associated the logical and physical aspects of information together; logically-
related information was stored in physical proximity within a data file. The relational data model
first separated the logical from the physical aspects.
• The relational data model looks at information as an unordered collection of
"relations."
• Each relation is populated with unordered "tuples" of the same unordered "field" structure.
• Fields may only contain values of a well-defined ("atomic") domain or the null value. The
unordered aspect needs to be emphasized. For expository purposes, relations are often viewed
as "tables".
• The tuples constitute the "rows" of the table; values for a specific field constitute "columns".
However, the "table data model" tends to impose a very non-relational ordering on both tuples and
fields. Relations are an abstraction of how data is stored; tables are just one of many possible
implementations.
• Some of the relational terms are crafted to emphasize the distinction between logical and physical
features, to avoid confusing one concept with another. However, vocabulary leakage from other
disciplines has sprinkled into the conversation of relational proponents.
• There is a strong tendency to refer to an individual tuple/row as a "record" because collections of
fields in other models are called records. "Attribute" is often used synonymously with field.
• To be sure, "unordered" implies neither "chaotic" nor "random". Relations and Fields are named
uniquely and identified easily. Distinguishing between tuples is more subtle since the order is not
pre-defined.
• Rather than depending upon relative (as in hierarchy) or absolute (as in network) locations, tuples
may only be differentiated according to their contents.
• Consequently, duplicate tuples are not permitted within a single relation. Even more strongly,
distinct tuples must have a unique "key" (some combination of a relation's named fields).
• The set of minimal keys includes one "primary key"; the rest are "candidate keys". Within a tuple,
references to other tuples are expressed as a "foreign key," which should contain the values of the
referenced tuple's primary key.
• Relational theory provides a firm mathematical foundation for data management. Set theory could
be applied to relations using relational algebraic operations (union, intersection, join, projection,
etc.).
Certain fields may be designated as keys, which mean that searches for specific values of that field
will use indexing to speed them up.

EmpNo EmpName Age DeptNo EmpNo DeptName

E0023 Dennis 23 D011 E0034 Human

Resource
E0034 Jacob 23 D012 E0056 Marketing

E0056 Janet 22 D012 E0023 Marketing

In the above model there are two relations Emplyoyee relation and department relation. The attribute
EmpNo is used to link the two relations.
Object oriented databases
• A data model is a logic organization of the real world objects (entities), constraints on them, and
the relationships among objects. A DB language is a concrete syntax for a data model. A DB
system implements a data model.
• A core object-oriented data model consists of the following basic object-oriented concepts:
(1) Object and object identifier: Any real world entity is uniformly modeled as an object
(associated with a unique id: used to pinpoint an object to retrieve).
(2) Attributes and methods: Here every object has a state (the set of values for the attributes
of the object) and a behavior (the set of methods - program code - which operate on the state
of the object). The state and behavior encapsulated in an object are accessed or invoked
from outside the object only through explicit message passing.
An attribute is an instance variable, whose domain may be any class: user-defined or
primitive. A class composition hierarchy (aggregation relationship) is orthogonal to the
concept of a class hierarchy. The link in a class composition hierarchy may form cycles.

(3) Class: a means of grouping all the objects which share the same set of attributes and
methods. An object must belong to only one class as an instance of that class (instance-of
relationship). A class is similar to an abstract data type. A class may also be primitive (no
attributes), e.g., integer, string, Boolean.
(4) Class hierarchy and inheritance: derive a new class (subclass) from an existing class
(superclass). The subclass inherits all the attributes and methods of the existing class and
may have additional attributes and methods. single inheritance (class hierarchy) vs. multiple
inheritance (class lattice).
• Example: Consider a scenario player described by playerNo, PlayerName and Age plays for
one and only one club. A club is described by clubNo, ClubName . A club plays several fixtures
in a season. A fixture is described by fixtureNo, Date,venue and time .Each fixture involves two
clubs only. Draw an object database model for this scenario.Operations to be performed on
this database include add, search, update and delete of records.

Fixture
Player Club
FixtureNo
PlayerNo 1..1 ClubNo 1..*
Date
PlayerName ClubName
Venue
Age *..1 Add()
2..0
Add()
Add() Delete()
Delete()
Delete() Search()
Search()
Search() Update()
Update()
Update()
Advantages and Disadvantages of Data Models
Hierarchical Model
Advantages
1) Simplicity: Since the database is based on the hierarchical structure, the relationship between
the various layers is logically simple.
2) Data Security: Hierarchical model was the first database model that offered the data security
that is provided by the DBMS.
3) Data Integrity: Since it is based on the parent child relationship, there is always a link between
the parent segment and the child segment under it.
4) Efficiency: It is very efficient because when the database contains a large number of 1:N
relationship and when the user require large number of transaction.
Disadvantages
1) Implementation complexity: Although it is simple and easy to design, it is quite complex to
implement.
2) Database Management Problem: If you make any changes in the database structure, then you
need to make changes in the entire application program that access the database.
3) Lack of Structural Independence: there is lack of structural independence because when we
change the structure then it becomes compulsory to change the application too.
4) Operational Anomalies: Hierarchical model suffers from the insert, delete and update anomalies,
also retrieval operation is difficult.
Network Model
Advantages
1) Conceptual Simplicity: just like hierarchical model it also simple and easy to implement.
2) Capability to handle more relationship types: the network model can handle one to one1:1
and many to many N: N relationship.
3) Ease to access data: the data access is easier than the hierarchical model.
4) Data Integrity: Since it is based on the parent child relationship, there is always a link
between the parent segment and the child segment under it.
5) Data Independence: The network model is better than hierarchical model in case of data
independence.
Disadvantages
1) System Complexity: All the records have to maintain using pointers thus the database
structure becomes more complex.
2) Operational Anomalies: In network model large number of pointers is required so insertion,
deletion and updating more complex.
 3) Absence of structural Independence: there is lack of structural independence because
when we change the structure then it becomes compulsory to change the application too.
Relational Model
Advantages
1) Conceptual Simplicity: Relational model is conceptually is simpler than both hierarchical and
network models.
2) Structural Independence: In the Relational model, changes in the structure do not affect the data
access.
3) Design Implementation: the relational model achieves both data independence and structural
independence.
4) Ad hoc query capability: the presence of very powerful, flexible and easy to use capability is one
of the main reasons for the immense popularity of the relational database model.
Disadvantages
1) Hardware overheads: The relational database systems hide the implementation complexities
and the physical data storage details from the user. For doing this, the relational database
system need more powerful hardware computers and data storage devices.
2) Ease of design can lead to bad design: The relational database is easy to design and use.
The user needs not to know the complexities of the data storage. This ease of design and use
can lead to the development and implementation of the very poorly designed database
management system.

The Development of Data Models

The Network Model Components
• Concepts still used today:
Schema: Conceptual organization of entire database as viewed by the database administrator
Sub-schema: Database portion “seen” by the application programs
Data management language (DML): Defines the environment in which data can be managed
Data definition language (DDL): Enables the administrator to define the schema components
The Relational Model
• Developed by E.F. Codd (IBM) in 1970 • Relational models were considered impractical in the
1970’s.
• Model was conceptually simple at expense of computer overhead
• Relational table is purely logical structure
How data are physically stored in the database is of no concern to the user or the designer
This concept is the source of a real database revolution
Relational Table
• A Relational table is a purely logical structure
How data are physically stored in the database is of no concern to the user or the designer.
• Stores a collection of related entities
Resembles a file
• Table (relations)
Matrix consisting of a series of row/column
• intersections
Each row in a relation is called a tuple
Related to each other by sharing a common entity characteristic
The Relational Model Components

• Relational data management system (RDBMS)

Performs same functions provided by hierarchical model, but hides complexity from the user
• Relational schema/diagram
Visual representation of relational database’s entities, attributes within those entities, and
relationships between those entities
• Relational diagram
Representation of entities, attributes, and relationships
• Relational table stores collection of related entities.
The Relational DBMS Application

• SQL-based relational database application involves three parts:

User interface: Allows end user to interact with the data
▪ Set of tables stored in the database
 ▪ Each table is independent from another
▪ Rows in different tables are related based on common values in common
attributes
SQL “engine”
▪ Executes all queries
Newer Data Models: Object/Relational

• Extended relational data model (ERDM)

Semantic data model developed in response to increasing complexity of applications
Includes many of OO model’s best features
Often described as an object/relational database management system (O/RDBMS)
Primarily geared to business applications
Newer Data Models: XML

• The Internet revolution created the potential to exchange critical business information
• Dominance of Web has resulted in growing need to manage unstructured information
• In this environment, Extensible Markup Language (XML) emerged as the de facto standard
• Current databases support XML
XML: the standard protocol for data exchange among systems and Internet services
The Future of Data Models
• Hybrid DBMSs
Retain advantages of relational model
Provide object-oriented view of the underlying data
• SQL data services – ‘Cloud Computing’
Store data remotely without incurring expensive hardware, software, and personnel
costs
Companies operate on a “pay-as-you-go” system
Exercise: Design an hierarchical database using the information below.
Parent record Child record
John Mutua
Harison James
Hannah Mutuku
John Mutua KAX 456Y Toyota, KAX 457Q Nissan
Harison James KAW 400T Toyota
Hannah Mutuku KBG 789P Toyota,
KBG 789P Toyota Kshs 500000
KAW 400T Toyota Kshs 500000
KAX 456Y Toyota Kshs 600000
KAX 457Q Nissan Kshs 700000
Business rules
• Business rules are the rules that are created to affect the way your business works. Usually, these
are rules that involve employees or staff and are rules that specify what they can and cannot do.
• A great example of a business rule involves marriages. For many companies, a boss is not
allowed to marry an employee or an accountant at a company is usually not allowed to marry
another accountant.
• In this case, the accountants are not allowed to be married because there is a more likely chance
that the spouses can change financial information and then cover for one another.
• These rules are intended to prevent disruption in a company or business.
• Business Rules are used every day to define entities, attributes, relationships and constraints.
• Usually though they are used for the organization that stores or uses data to be an explanation of
a policy, procedure, or principle.
• The data can be considered significant only after business rules are defined, without them it’s just
records, but to a business they are the characteristics that are defined and seen by the company.
• Business Rules help employees focus on and implement the actions within the organizations
environment.
• Some things to think about when creating business rules are to keep them simple, easy to
understand, keep them broad so that everyone can have a similar interpretation. To be considered
true, business rules must be in writing and kept up to date.
• Identifying business rules are very important to the database design. Business rules allow the
creator to develop relationship participation rules and constraints and to create a correct data
model.
• They also allow the creators to understand business processes, and the nature, role and scope of
the data.
• They are a communication tool between users and creators, and they also help standardize the
company’s view of the data.
• It is important to keep in mind that some business rules cannot be modeled.
• Business Rules give the proper classification of entities, attributes, relationships, and constraints.
• Sources of business rules are managers, policy makers, department managers, written
documentation, procedures, standards, operation manuals, and interviews with end users.
Some examples of business rules:
Departments------offers ------- Course
Course----------generates ------- Class
Professor --------teaches ------- Class
• There are several protocols to the way business rules are written. Not every protocol has to be
followed, but in general, a well-written set of business rules consist of having a unique identifier,
 describes one and only one concept, are written in plain language, are written, and are from a
single source.
• In terms of a unique identifier, business rules should come with an identifier that may consist of
the rule number and the department it affects. An example would be ‘BRacc01’. In this case, this
business rule (BR) is directly related to the accounting department.
• Another important aspect of business rules consist of how the rules are shared within the
company.
• A protocol for business rules that many follow is that the business rules are written down.
However, with many businesses sharing information directly over the internet, some are opting to
place their business rules online in company blogs, wikis, and websites.
• This shares the business rules with all employees faster and easier. In relation to how business
rules are shred, it is very important that business rules are written in plain language.
• If business rules are written at a high level language, there is an increased chance that not every
person will understand what the business rules cover or what is acceptable and what is not.