Database and Data Modeling

By Mr K.Motsi
Database Management System
• Database Management System or DBMS in short refers to the
technology of storing and retrieving usersí data with utmost efficiency
along with appropriate security measures. This tutorial explains the
basics of DBMS such as its architecture, data models, data schemas,
data independence, E-R model, relation model, relational database
design, and storage and file structure and much more.
Why to Learn DBMS
• Traditionally, data was organized in file formats. DBMS was a new concept then, and all the research
was done to make it overcome the deficiencies in traditional style of data management. A modern
DBMS has the following characteristics −

• Real-world entity − A modern DBMS is more realistic and uses real-world entities to design its
architecture. It uses the behavior and attributes too. For example, a school database may use students
as an entity and their age as an attribute.

• Relation-based tables − DBMS allows entities and relations among them to form tables. A user can
understand the architecture of a database just by looking at the table names.

• Isolation of data and application − A database system is entirely different than its data. A database is
an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS
also stores metadata, which is data about data, to ease its own process.
Why to Learn DBMS
• Less redundancy − DBMS follows the rules of normalization, which splits a relation when
any of its attributes is having redundancy in values. Normalization is a mathematically rich
and scientific process that reduces data redundancy.

• Consistency − Consistency is a state where every relation in a database remains consistent.

There exist methods and techniques, which can detect attempt of leaving database in
inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of
data storing applications like file-processing systems.

• Query Language − DBMS is equipped with query language, which makes it more efficient to
retrieve and manipulate data. A user can apply as many and as different filtering options as
required to retrieve a set of data. Traditionally it was not possible where file-processing
system was used.
Applications of DBMS
• Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information.

• Mostly data represents recordable facts. Data aids in producing information, which is based on facts. For example, if we have data about
marks obtained by all students, we can then conclude about toppers and average marks.

• A database management system stores data in such a way that it becomes easier to retrieve, manipulate, and produce information.
Following are the important characteristics and applications of DBMS.

• ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability (normally shortened as ACID). These
concepts are applied on transactions, which manipulate data in a database. ACID properties help the database stay healthy in multi-
transactional environments and in case of failure.

• Multiuser and Concurrent Access − DBMS supports multi-user environment and allows them to access and manipulate data in parallel.
Though there are restrictions on transactions when users attempt to handle the same data item, but users are always unaware of them.

• Multiple views − DBMS offers multiple views for different users. A user who is in the Sales department will have a different view of
database than a person working in the Production department. This feature enables the users to have a concentrate view of the
database according to their requirements.
Applications of DBMS
• Security − Features like multiple views offer security to some extent
where users are unable to access data of other users and
departments. DBMS offers methods to impose constraints while
entering data into the database and retrieving the same at a later
stage. DBMS offers many different levels of security features, which
enables multiple users to have different views with different features.
For example, a user in the Sales department cannot see the data that
belongs to the Purchase department. Additionally, it can also be
managed how much data of the Sales department should be displayed
to the user. Since a DBMS is not saved on the disk as traditional file
systems, it is very hard for miscreants to break the code.
Overview
• Database is a collection of related data and data is a collection of facts
and figures that can be processed to produce information.

• Mostly data represents recordable facts. Data aids in producing

information, which is based on facts. For example, if we have data
about marks obtained by all students, we can then conclude about
toppers and average marks.

• A database management system stores data in such a way that it

becomes easier to retrieve, manipulate, and produce information.
DBMS - Architecture
• The design of a DBMS depends on its architecture. It can be centralized or decentralized or
hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier
architecture divides the whole system into related but independent n modules, which can be
independently modified, altered, changed, or replaced.

• In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and
uses it. Any changes done here will directly be done on the DBMS itself. It does not provide handy
tools for end-users. Database designers and programmers normally prefer to use single-tier
architecture.

• If the architecture of DBMS is 2-tier, then it must have an application through which the DBMS can
be accessed. Programmers use 2-tier architecture where they access the DBMS by means of an
application. Here the application tier is entirely independent of the database in terms of
operation, design, and programming.
Database system architecture:
• The three-schema architecture introduces a multi-level architecture where each level represents one
abstraction level - in 1978 the “standard” architecture (ANSI/SPARC architecture) for databases was
introduced.
• It consists of3 levels where each level introduces one abstraction layer and has a schema thatdescribes how
representations should be mapped to the next lower abstraction level:
• 1) The internal level or internal schema - describes storage structures and access paths for the physical
database. Abstraction level: files, index files etc. Is usually defined through the data definition language (DDL)
of the DBMS.
• 2) Conceptual level or conceptual schema - an abstract description of the physical database. Constitute one,
for all users, common basic model of the logical content of the database. This abstraction level corresponds to
“the real world”: object, characteristics, relationships between objects etc. The schema is created in the DDL
according to a specific data model.
• 3) External level, external schemas, or views - a typical DB has several users with varying needs, demands,
access privileges etc. External schemas describes different views of the conceptual database with respect to
what different user groups would like to/are allowed to se. Some DBMS ̃Os have a specific language for view
definitions (else the DDL is used).
Database system architecture:

• Physical data independence: the possibility to change the internal

schema wit-hout influencing the conceptual schema. E.g. the effects
of a physical reorgani-zation of the database, such as adding an access
path, is eliminated.Logical data independence: the possibility to
change the conceptual schemawithout influencing the external
schemas (views). E.g. add another field to aconceptual schema.
Database system architecture:

• Physical data independence: the possibility to change the internal

schema without influencing the conceptual schema. E.g. the effects of
a physical reorganization of the database, such as adding an access
path, is eliminated.
• Logical data independence: the possibility to change the conceptual
schema without influencing the external schemas (views). E.g. add
another field to a conceptual schema.
File Systems Vs Database
Systems
Traditional File Systems
• Each file is independent of other file
• Integration can be done only by writing individual program for each
application
• Any change to the data requires modifying all the programs that uses
the data
• Because each file is hard-coded with specific information like data
type, data size etc
• Identified on a trial-and-error basis
• Creates, processes and disseminates its own files.
• Inventory and payroll generate separate files and do not communicate
with each other.
• Simple to operate
• Better local control
• Data of the organization is dispersed throughout the functional sub-
systems
Example for File Systems
Disadvantages of Traditional File
System
• Data Redundancy
• Data Inconsistency
• Lack of Data Integration
• Program Dependence
• Data Dependence
• Limited Data Sharing
• Poor Data Control
• Problem of Security
• Data Manipulation Capability is Inadequate
• Needs Excessive Programming
DBMS
Example for DBMS
Advantages of Database Systems
• Controlled redundancy
• Data consistency
• Program data independence
• Sharing of data
• Enforcement of standards
• Improved data integrity
• Improved security
• Data access is efficient
• Conflicting requirements can be balanced
• Improved backup and recovery facility
• Minimal program maintenance
• Data quality is high
• Good data accessibility and responsiveness
• Concurrency control
• Economical to scale
• Increased programmer productivity
Disadvantages of Database Systems
• Complexity increases
• Requirement of more disk space
• Additional cost of hardware
• Cost of conversion
• Need of additional and specialized manpower
• Need for backup and recovery
• Organizational conflict
• More installational and management cost
3-tier Architecture
• A 3-tier architecture separates its tiers from each other based on the
complexity of the users and how they use the data present in the
database. It is the most widely used architecture to design a DBMS.
3-tier Architecture
• Database (Data) Tier − At this tier, the database resides along with its query processing languages.
We also have the relations that define the data and their constraints at this level.

• Application (Middle) Tier − At this tier reside the application server and the programs that access
the database. For a user, this application tier presents an abstracted view of the database. End-users
are unaware of any existence of the database beyond the application. At the other end, the
database tier is not aware of any other user beyond the application tier. Hence, the application layer
sits in the middle and acts as a mediator between the end-user and the database.

• User (Presentation) Tier − End-users operate on this tier and they know nothing about any
existence of the database beyond this layer. At this layer, multiple views of the database can be
provided by the application. All views are generated by applications that reside in the application
tier.

• Multiple-tier database architecture is highly modifiable, as almost all its components are
independent and can be changed independendly.
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.

Entity-Relationship Model

Entity − An entity in an ER Model is a real-world entity having properties called attributes. Every attribute is defined by its
set of values called domain. For example, in a school database, a student is considered as an entity. Student has various
attributes like name, age, class, etc.

Relationship − The logical association among entities is called relationship. Relationships are mapped with entities in
various ways. Mapping cardinalities define the number of association between two entities.

Mapping cardinalities −
one to one
one to many
many to one
many to many
Relational Model
• The most popular data model in DBMS is the Relational Model. It is
more scientific a model than others. This model is based on first-order
predicate logic and defines a table as an n-ary relation.
Relational Model
• 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.
Distributed DBMS (DDBMS)
A distributed DBMS (DDBMS) is a DBMS where different parts of the
database is located at different geographic locations (i.e. in several
different computers or clusters) but where each part of the database is
managed by local instances of the same DDBMS.
A parallel DBMS (PDBMS) is a single DBMS running on a single cluster
where the PDBMS engine automatically decides how data is distributed and
queries parallelized.
A federated DBMS (FDBMS) is a middleware DBMS that combines data
from several different autonomous underlying DBMSs. SQL queries to the
FDBMS are more or less automatically translated into queries to the
underlying DBMSs.
The best Distributed systems and what factors
does one consider to choose a particular
• There is best is no best DS the same problem can be tackled with many different designs and
architectural styles
• Choice depends often on extra-functional requirements:
• Costs (resource usage, development effort needed)
• Scalability (effects of scaling work complexity and available resources)
• Performance (e.g. execution time, response time, latency..)
• System Architecture and Networking
• Reliability
• Fault tolerance
• Maintainability (extending system with new components)
• Usability (ease of configuration and usage...)
• Reusability