DATABASE

MANAGEMENT
IT126 - INFORMATION MANAGEMENT
RELATIONAL DATA
MODEL
Nowadays, the relational model is the essential data model
for commercial data processing applications, which achieved
its primary position because of its simplicity, which makes the
job of the programmer easy, in contrast to earlier data models
such as the network model or the hierarchical model.

The Relational Database Management System (RDBMS) has

become the leading data-processing software in use
nowadays with approximated new license sales of between
US$6 billion and US$10 billion per year. This software
signifies the second generation of DBMSs and is based on
the relational data model proposed by Mr. E. F. Codd in
the year 1970.
The relational model is the
theoretical basis of relational
databases, which is a technique or
way of structuring data using
relations, which are grid-like
mathematical structures consisting of
columns and rows. Codd proposed
the relational model for IBM, but the
idea became extremely vital and
prominent that his work would
become the basis of relational
databases. You might be very familiar
with the physical demonstration of a
relation in a database - which is
known as a table.
OBJECTIVES
The relational model' s objectives were specified as follows:
To allow a high degree of data independence, application programs
must not be affected by alterations to the internal data
representation, mostly by changes to file organizations or access
paths.
To provide considerable grounds for dealing with data semantics,
reliability, and redundancy problems. In particular, Codd' s theory for
the relational model introduced the concept of normalized relations,
were relations that have no repeating groups, and the process is
called normalization.
To allow the expansion of set-oriented data manipulation languages
RELATIONAL MODEL TERMS
The entity set which does not have sufficient
attributes to form a primaryIn general, a
relation schema consists of a directory of
attributes and their corresponding domain.

Relation: A relation is a table with columns

and rows.
Attribute: An attribute is a named column
of a relation.
Domain: A domain is the set of allowable
values for one or more attributes.
Tuple: A tuple is a row of a relation.
DATABASE SCHEMA
A database schema defines the
structure and organization of data
within a database. It outlines how
data is logically stored, including
the relationships between different
tables and other database objects.

The schema serves as a blueprint

for how data is stored, accessed,
and manipulated, ensuring
consistency and integrity
throughout the system.
DATABASE SCHEMA
Key points about a database schema:

It defines how data is logically organized, including

tables, fields, and relationships.
It outlines the relationships between entities, such as
primary and foreign keys.
It helps resolve issues with unstructured data by
organizing it in a clear, structured way.
Databas e schemas guide how data is accessed, modified,
and maintained.
DATABASE SCHEMA

It can be categorized into three

parts.

These are:
Physical Schema
Logical Schema
View Schema
TYPES OF DATABASE
SCHEMAS
Physical Database Schema

A physical schema defines how data is stored in the

storage system, including the arrangement of files,
indices and other storage structures. It specifies the
actual code and syntax needed to create the database
structure. Essentially, it determines where and how the
data is stored in the physical storage medium.
Logical Database Schema

A logical database schema defines the

logical structure of the data, including
tables, views, relationships, and integrity
constraints. It describes how data is
organized in tables and how the attributes
of these tables are connected.

Using Entity-Relationship (ER) modeling,

the logical schema outlines the
relationships between different data
components. It also defines integrity
constraints to ensure the quality of data
during insertion and updates.
View Database Schema

The view schema is the highest level of abstraction

in a database, focusing on how users interact with
the database. It defines the interface through which
users can access and manipulate data, without
needing to understand the underlying storage
mechanisms.
A database can have multiple view schemas, also
known as subschemas, each providing a different
perspective of the data. These schemas describe
only a part of the database.
ENTITY -
RELATIONSHIP
MODEL
ENTITY RELATIONSHIP
MODEL
One of the most challenging phases of database design is the fact that
designers, programmers, and end-users tend to view data and its use in
various forms. Unfortunately, unless all the database learners gain a
common understanding that reflects how the enterprise operates but the
design you may produce will fail to meet the users' requirements. To ensure
that you get a precise understanding of the nature of the data and how the
enterprise uses it, you need to have a universal model for interaction that is
non-technical and free of ambiguities and easily readable to both technical
as well as non-technical members. So the ER (Entity Relationship) Model
was designed and developed and is represented by an ER diagram.
 ENTITY RELATIONSHIP DIAGRAM

When a company asks you to make them a working,

functional Database Management System (DBMS)
which they can work with, there are certain steps to
follow.
 1. Gathering information: This could be a written
document that describes the system in question with
a reasonable amount of detail.

2. Producing ERD: ERD or Entity Relationship Diagram

is a diagrammatic representation of the description
we have gathered about the system.
3. Designing the database: Out of the ERD we have
created, it is very easy to determine the tables, the
attributes which the tables must contain and the
relationship among these tables.

4. Normalization: This is a process of removing

different kinds of impurities from the tables we have
just created in the above step.
 ENTITY RELATIONSHIP NOTATION

There is no standard for representing data objects in

ER diagrams. Each modeling methodology uses its own
notation.
All notational styles represent entities as rectangular
boxes and relationships as lines connecting boxes.
Each style uses a special set of symbols to represent
the cardinality of connection.
 COMPONENTS OF ER DIAGRAM
ER Model consists of Entities, Attributes, and
Relationships among Entities in a Database System.
ERD BASIC SYMBOLS
 WHAT IS ENTITY?
ENTITY - may be an object with a physical existence –
a particular person, car, house, or employee – or it
may be an object with a conceptual existence – a
company, a job, or a university course.

2 Types of Entity:

Strong Entity - is a type of entity that has a key Attribute. Strong

Entity does not depend on other Entity in the Schema. It has a
primary key, that helps in identifying it uniquely

Weak Entity - depend on some other entity type. They don't have
primary keys, and have no meaning in the diagram without their
parent entity.
EXAMPLE
A company may store the information of dependents (Parents,
Children, Spouse) of an Employee. But the dependents can’t exist
without the employee. So Dependent will be a Weak Entity Type
and Employee will be Identifying Entity type for Dependent,
which means it is Strong Entity Type .

The relationship between the weak entity type and its

identifying strong entity type is called identifying relationship
and it is represented by a double diamond.
 WHAT IS ATTRIBUTE?
ATTRIBUTE - are the properties that define the entity
type.
 TYPES OF ATTRIBUTE
Key Attribute - The attribute which uniquely
identifies each entity in the entity set

Composite Attribute - An attribute composed

of many other attributes

Multivalued Attribute - An attribute consisting

of more than one value for a given entity.

Derived Attribute - An attribute that can be

derived from other attributes of the entity type
 WHAT IS CARDINALITY?
CARDINALITY - The number of times an entity of an
entity set participates in a relationship set

Different types of Cardinality using CHEN’S NOTATION:

One-to-One: When each entity in each entity set

can take part only once in the relationship

Example: Let us assume that a male can marry one

female and a female can marry one male.
 One-to-Many: where each entity can be related to more
than one entity and the total number of tables that can
be used in this is 2.

Example: Let us assume that one surgeon department

can accommodate many doctors. So the Cardinality
will be 1 to M. It means one department has many
Doctors.

Many-to-One: When entities in one entity set can take part only
once in the relationship set and entities in other entity sets can
take part more than once in the relationship set.

Example: Let us assume that a student can take only

one course but one course can be taken by many
students.
Many-to-Many: when entities in all entity sets can take
part more than once in the relationship cardinality.

Example: Let us assume that a student can take more

than one course and one course can be taken by many
students.
 HOW TO PREPARE AN ERD
Step 1: Let us take a very simple example and we try
to reach a fully organized database from it. Let us
look at the following simple statement:

A boy eats an ice cream.

This is a description of a real word activity, and we

may consider the above statement as a written
document (very short, of course).
 HOW TO PREPARE AN ERD

Step 2: Now we have to prepare the ERD. Before

doing that we have to process the statement a little.
We can see that the sentence contains a subject
(boy), an object (ice cream) and a verb (eats) that
defines the relationship between the subject and
the object. Consider the nouns as entities (boy and
ice cream) and the verb (eats) as a relationship.
 HOW TO PREPARE AN ERD

Step 2: But can only entities have attributes?

Certainly not. If we want then the relationship must
have their attributes too. These attribute do not
inform anything more either about the boy or the ice
cream, but they provide additional information about
the relationships between the boy and the ice cream.
 HOW TO PREPARE AN ERD

Step 3: We are almost complete now. If you look

carefully, we now have defined structures for at least
three tables.

We now have information about the boy, about the ice

cream he has eaten and about the date and time when
the eating was done.
- EOF