Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

Database Management System Tutorial
The technology utilized to effectively store and retrieve user
data while also performing the essential security procedures is
referred to as a "database management system," or simply
"DBMS
...

Why to Learn DBMS?
In the past, data was organized in file formats
...
The following characteristics are present in
modern DBMSs −
•

•

•

Real-world entity − A modern DBMS is more realistic
and uses real-world entities to design its architecture
...
For example, a
school database may use students as an entity and
their age as an attribute
...
A user can
understand the architecture of a database just by
looking at the table names
...
A database is an
active entity, whereas data is said to be passive, on
which the database works and organizes
...

• Less
redundancy − DBMS follows the rules of
normalization, which splits a relation when any of its
attributes is having redundancy in values
...

• Consistency − Consistency is a state where every
relation in a database remains consistent
...
A DBMS can
provide greater consistency as compared to earlier
forms of data storing applications like file-processing
systems
...
A user can apply as many and as
different filtering options as required to retrieve a set
of data
...

Applications of DBMS
Data is a collection of linked facts and statistics that may be
processed to provide information, and a database is a collection
of related data
...
Information that is
based on facts is produced with the use of data
...


Data is stored in a database management system in a way that
makes information retrieval, manipulation, and production
simpler
...

•

•

•

•

ACID Properties − DBMS follows the concepts
of Atomicity, Consistency, Isolation,
and Durability
(normally shortened as ACID)
...
ACID properties help the database stay
healthy in multi-transactional environments and in
case of failure
...
Though there are
restrictions on transactions when users attempt to
handle the same data item, but users are always
unaware of them
...
A user who is in the Sales department
will have a different view of database than a person
working in the Production department
...

Security − Security is offered to some extent through
features like multiple views, which prevent users from
accessing the data of other users and departments
...
Due to the

extensive variety of security mechanisms implemented
by the DBMS, several users can each view different
aspects from a different perspective
...
Additionally, the user's
view of the sales department's data may be
customized
...

DBMS - Overview
Data is a collection of linked facts and statistics that may be
processed to provide information, and a database is a collection
of related data
...
Information that is
based on facts is produced with the use of data
...

A database management system stores data in such a way that
it becomes easier to retrieve, manipulate, and produce
information
...
At the time, DBMS
was a novel idea, and extensive research was conducted to
enable it to overcome the limitations of the conventional

approach to data management
...

It uses the behavior and attributes too
...

Relation-based tables − DBMS allows entities and
relations among them to form tables
...

Isolation of data and application − A database system
is entirely different than its data
...
DBMS also
stores metadata, which is data about data, to ease its
own process
...

Normalization is a mathematically rich and scientific
process that reduces data redundancy
...
There exist
methods and techniques, which can detect attempt of
leaving database in inconsistent state
...


•

•

•

•

•

Query Language − DBMS is equipped with query
language, which makes it more efficient to retrieve and
manipulate data
...
Traditionally it was not possible where fileprocessing system was used
...
These concepts are
applied on transactions, which manipulate data in a
database
...

Multiuser and Concurrent Access − DBMS supports
multi-user environment and allows them to access and
manipulate data in parallel
...

Multiple views − DBMS offers multiple views for
different users
...
This feature
enables the users to have a concentrate view of the
database according to their requirements
...
DBMS offers methods to
impose constraints while entering data into the

database and retrieving the same at a later stage
...
For example, a user in the Sales
department cannot see the data that belongs to the
Purchase department
...
Since a DBMS is not
saved on the disk as traditional file systems, it is very
hard for miscreants to break the code
...
Some people back up their data while
others retrieve it
...

They are responsible to look after its usage and by
whom it should be used
...
Administrators also look after DBMS

•

•

resources like system license, required tools, and other
software and hardware related maintenance
...

They keep a close watch on what data should be kept
and in what format
...

End Users − End users are those who actually reap the
benefits of having a DBMS
...


DBMS - Architecture
A DBMS's architecture determines how it will be designed
...
A DBMS's
architecture can be thought of as either single tier or multitiered
...

The sole entity where the user directly sits on the DBMS and uses
it in a 1-tier design is the DBMS
...
It does not give
users any practical tools
...


If the architecture of DBMS is 2-tier, then it must have an
application through which the DBMS can be accessed
...
Here the application tier is
entirely independent of the database in terms of operation,
design, and programming
...
It is the most widely used architecture to design
a DBMS
...
We also
have the relations that define the data and their
constraints at this level
...
For a user, this application tier presents an
abstracted view of the database
...
At the other end, the database tier is not
aware of any other user beyond the application tier
...

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


DBMS - Data Models
Data models define how the logical structure of a database is
modeled
...
Data models define how data is
connected to each other and how they are processed and stored
inside the system
...
Earlier data
models were not so scientific, hence they were prone to
introduce lots of duplication and update anomalies
...
While formulating
real-world scenario into the database model, the ER Model
creates entity set, relationship set, general attributes and
constraints
...

ER Model is based on −
•
•

Entities and their attributes
...


These concepts are explained below
...
Every attribute is
defined by its set of values called domain
...
Student has various attributes like name, age,
class, etc
...
Relationships are mapped with
entities in various ways
...

Mapping cardinalities −
o one to one
o one to many
o many to one
o many to many
Relational Model
The most popular data model in DBMS is the Relational Model
...
This model is based on
first-order predicate logic and defines a table as an n-ary
relation
...

Relations can be normalized
...

Each row in a relation contains a unique value
...


DBMS - Data Schemas
Database Schema
A database schema is the skeleton structure that represents the
logical view of the entire database
...
It
formulates all the constraints that are to be applied on the data
...
It contains a descriptive detail of the database,
which can be depicted by means of schema diagrams
...


A database schema can be divided broadly into two categories −
•

Physical Database Schema − This schema pertains to
the actual storage of data and its form of storage like
files, indices, etc
...


Logical Database Schema − This schema defines all the
logical constraints that need to be applied on the data
stored
...

Database Instance
•

It is important that we distinguish these two terms individually
...
It is designed when
the database doesn't exist at all
...
A
database schema does not contain any data or information
...
It contains a snapshot of the database
...
A DBMS ensures
that its every instance (state) is in a valid state, by diligently
following all the validations, constraints, and conditions that the
database designers have imposed
...
Database
systems are designed in multi-layers as we learnt earlier
...
For example, it stores data about data, known as
metadata, to locate and retrieve data easily
...
But as a DBMS expands, it needs to change over time
to satisfy the requirements of the users
...


Metadata itself follows a layered architecture, so that when we
change data at one layer, it does not affect the data at another
level
...

Logical Data Independence
Logical data is data about database, that is, it stores information
about how data is managed inside
...

Logical data independence is a kind of mechanism, which
liberalizes itself from actual data stored on the disk
...


Physical Data Independence
All the schemas are logical, and the actual data is stored in bit
format on the disk
...

For example, in case we want to change or upgrade the storage
system itself − suppose we want to replace hard-disks with SSD
− it should not have any impact on the logical data or schemas
...
It works
around real-world entities and the associations among them
...

Entity
An entity can be a real-world object, either animate or
inanimate, that can be easily identifiable
...
All these entities have some
attributes or properties that give them their identity
...
An entity
set may contain entities with attribute sharing similar values
...
Entity sets need not be disjoint
...
All attributes have values
...

There exists a domain or range of values that can be assigned to
attributes
...
It has to be alphabetic
...

Types of Attributes
• Simple attribute − Simple attributes are atomic values,
which cannot be divided further
...

• Composite attribute − Composite attributes are made
of more than one simple attribute
...

• Derived
attribute − Derived attributes are the
attributes that do not exist in the physical database,
but their values are derived from other attributes
present in the database
...
For another
example, age can be derived from data_of_birth
...
For example − Social_Security_Number
...
For example, a person

can have more
email_address, etc
...

For example, the roll_number of a student makes him/her
identifiable among students
...

• Candidate Key − A minimal super key is called a
candidate key
...

• Primary Key − A primary key is one of the candidate
keys chosen by the database designer to uniquely
identify the entity set
...
For
example,
an
employee works_at a
department,
a
student enrolls in a course
...


Relationship Set
A set of relationships of similar type is called a relationship set
...
These
attributes are called descriptive attributes
...

Binary = degree 2
• Ternary = degree 3
• n-ary = degree
Mapping Cardinalities
•

Cardinality defines the number of entities in one entity set,
which can be associated with the number of entities of other set
via relationship set
...


•

One-to-many − One entity from entity set A can be
associated with more than one entities of entity set B
however an entity from entity set B, can be associated
with at most one entity
...


•

Many-to-many − One entity from A can be associated
with more than one entity from B and vice versa

---