Database Management System: Overview of DBMS
ADVANCED DATABASES
Objectives
To cover an overview of following
concepts
Introduction
Application of DBMS
Advantages/Disadvantages of DBMS
Characteristics of DBMS
Users
DBMS Architecture
DBMS Data Models
Entity Relationship Model
Database Schema
Data Independence
Introduction
Database is collection of interrelated data where
data can be easily accesses, managed and
updated. For example, consider office department in school maintains details of the student such as name of the student, age, address, class etc. These all records are related with student stored in a
database.
Database management System is collection of data
and set of programs to access the data. It allows creation, definition and manipulation of database. DBMS provides protection and security to database. Some examples for popular dbms are MySQL,
Oracle, Sybase, Microsoft Access and IBM DB2.
Application of DBMS
Banking: Banks uses database for customer
accounts, loans and other transactions.
Airlines: Airlines uses online database for
reservation, arrival and departure of the planes ,schedule information etc..
Education: Schools, colleges and universities use
database for course details ,registration, examination details ,result etc.
Credit card transactions: It is used to keep track
of purchases on credit cards to generate statements.
Telecommunications: Telecommunication
department uses database to store information about telephone numbers, details of calls,
generating bills etc.
E-commerce: It uses online databases for buying
and selling of products, online transaction processing, internet marketing etc.
Finance: It uses database for storing information
such as sales, stocks, bonds etc.
Sales: it uses databases for storing product details,
transaction and customer details.
Human resources: Organizations use databases
for storing information about employees , salaries, tax, salary checks etc.
5
Advantages of DBMS
Controlling Data Redundancy: if database have
more than one records of same type then it is called data redundancy. In DBMS, all data is stored in one single database file and that recorded data is
placed in the database.
Data Sharing: Data can be shared by authorized
users of the organization among multiple users. Many users can be authorized to access the same data simultaneously. The remote users also can share the same data.
Enforcing Data Integrity: In database approach,
data integrity is much easier. In database, data is stored in the tables. A single database contains
multiple tables which is easy to retrieve and update the data.
Data Security: Data security is the protection of the
database from unauthorized access. The DBMS
ensures access of the database through authorized channel. To ensure security, DBMS provides security such as by giving user name and passwords.
Ease of application development: The
programmer needs to develop applications according to user specification. The other issues such as data integrity, security etc are handled by DBMS itself which makes application development easier.
Multiple user interfaces: DBMS provides different
types of user interfaces such as application program interfaces, graphical user interfaces which includes form style and menu driven interfaces. Form style interface provides user interaction with forms and menu driven interface provides user interaction with list of options called as menus.
7
Backup and recovery: Most of the DBMS’s
provide backup and recovery subsystems which creates automatically backup of data from
hardware and software failures and restores the data if required.
Data Independence: Data Independence keeps
data separated from all programs that make use of it. In DBMS, database and application programs are separated from each other and we can easily change the database structure without modifying the application program.
Reduced Maintenance: It can be easily
maintainable due to centralized nature of the system.
8
Disadvantages of DBMS
Cost of Data Conversion: When computer file
based system replaced with database system, it must be converted to database file. It’s difficult to convert data file into database, so for this we have to take help of database designers along with
application programmers and also we should take help of software which costs lot of money for
developing the software.
Complexity: Database system creates additional
complexity and requirements. DBMS is extremely complex piece of software. The operation of
database system with several users and databases is quite costly and demanding.
Cost of Hardware and Software: To run DBMS
software, we need high speed of data processor and large memory size and DBMS software also too high.
Size: DBMS is large piece of software due to its
complexity and breadth functionality. It occupies large space of disk and large memory to run the efficiently.
Cost of staff training: DBMS is complex
database system which is required to train the users all levels including programming,
application development etc for which
organization has to pay lot of amount for training staff to run the DBMS.
Higher impact of failure : In most of the
organizations, all data stored in a single
database. Since all users and applications rely on the availability of the DBMS. If database is
damaged due to electric failure or database corruption data may lost forever.
10
Characteristics of DBMS
It reduces complex relationships between data.
Enforces user defined rules and ensures data sharing. It provides different interfaces which user can
manipulate the data.
Integrated database helps in understanding relationship
between data stored in different applications.
It has automatic backup and recovery procedures. Provides clear and logical view of process that
manipulates data.
Changes made in the database schema should not affect
the other levels.
Controls data redundancy and control over security.
It contains ACID properties which stands for Atomicity,
Consistency, Isolation and Durability which maintains data in healthy state in case of failure.
Users
DBMS is used by many users for various purposes.
In large organizations, many people are involved in design, use and maintenance of database with hundreds of users. The people whose job is to maintain day-to-day use of large database, we call them as actors on the scene. Some of them are described as follows:
Database Administrators(DBA): In database
environment, the primary resource is database itself and the secondary resource is DBMS and
related software. Administrating these resources is the responsibility of the database administrator. The DBA is responsible for authorizing access to the
database, system license, monitoring its use, acquiring software and hardware resources as needed.
Database Designers: these are responsible for
identifying the data to be stored in a database for choosing structure to the database to represent the
data. Database designers work on the design part of the database. Database designers communicate with
database users in order to understand their requirements and create design as per user’s
requirements. They decide what data should be kept and in what format data should be there, these are all
decided by database designers. They interact with users and develop views of the database that meet
requirements of users. 13
End Users: End users are the people whose job is to
access the database for querying, updating and
generating the reports. There are several categories of end users: Casual end users access the database but they may need different information each time. Parametric end users make up sizable portion of the database and users. Sophisticated end users include engineers, scientists, business analysts and others familiarize with DBMS to implement their applications to meet their complex requirements. Standalone
users maintain database by using readymade
program packages which provide menu based or graphic based interfaces.
14
DBMS Architecture
The basic client/server architecture was developed to deal
with large number of PCs, work stations, database servers, web servers and other components are connected with
network. The client/server architecture consists of many PCs and work stations which are connected via network .A client in this framework is a user machine which provides user interface and local processing.
The second approach i.e. two-tier architecture was taken
by some object oriented DBMSs, where DBMS were
divided between client and server in more integrated way. The server level may include data storage on disk pages, recovery and buffering of disk pages and other functions. The client level may include user interface, interaction with programming language compiler, recovery across multiple servers and other functions. It is simplicity and compatibility with existing system
Three-tier Architecture
Three tier architecture adds intermediate
layer between the client and server as
shown below.
An end user sits on client tier i.e. web interface which is
used by client to communicate with server through intermediate layer. All views are generated by
applications which reside in the application tier.
The middle layer sometimes called application server or
sometimes web server depending on the application. This tier is uses business roles that are used to access data from the server. For user application tier works as view of the database. The intermediate tier accepts requests from the client, processes the request and sends the commands to database server.
The database tier includes data management services
which gives result for client via middle tier. Security is the best in the three tier architecture because the
middle tier protects the database tier. 17
DBMS Data Models
Data Model is collection of concepts that can be used to
describe the structure of the database. It describes relationship between different parts of the data. Data Models can be categorize according to types of
concepts that describe structure of the database.
Conceptual Data Model: It provides concepts that are
close to many users which perceive the data.
Physical Data Model: It provides concepts that
describe how data is stored in the computer.
Representational Data Model: It can hide details
some details of data storage but it can be implemented on computer system directly.
Conceptual data model
is also known as high level data model that
provide relationship between different entities. Conceptual data model is created by combining requirements from various sources like business analysts, business documents and end users. It is first step in constructing data model and which provides representation of business of the
organization. It describes structure of the
database and gives information of about main subject area of the organization. It comprises
relationship between subject area and entities in the subject area.
19
Physical data model is also known as low level
data model that describe how data is stored in the computer by representing information such as
record formats, record orderings and access paths. It is used to create relationship between tables. It includes tables, columns, primary key, foreign key and relationship between tables. Database
performance, physical storage etc are the important parameters of the physical model. An access path is structure that makes search for particular database records. It includes storage allocation of database system. An index is example of access path that allows direct access to data using index keyword.
20
Representational model is also known as
implementation data model used frequently in traditional DBMSs. This model includes widely
used relational data models such as network and hierarchical models. Representational model
represent data by using record structures and
hence are sometimes called record based data models .
Another new level called object data model
group (ODMG) is representational data model which is closer t conceptual data model. They are particularly used in software engineering domain.
21
Entity Relationship
Model
It is graphical representation of entities and their relationships
to each other. An entity is a real world object that is
distinguishable from other objects. It is frequently used for the conceptual design of database applications and much database design tools employ its concepts. It works around real world
entity and association among them. It is relies on concepts of entities, attributes and relationships. It represents pictorial representation of entities and relationship between entities in the format called ER diagram. ER model describes data as
entities, attributes and relationships as follows:
Entity: It represents real world object ,such as employee or
project that is described in the database.
Attribute: It represents property of the entity, such as
employee name, address or salary.
Relationship: It represents an association between two or
more entities, for example , relationship between a employee and a project.
Entity
An entity is real world object with an independent
existence. It may be object with a physical
independence such as person, car, employee etc or it may be an object with conceptual existence such as company, job etc.
Example for Entity
Attributes
The properties of a entity is called as attributes,
consider above example, STUDENT is an entity with properties name, student_number, class and
student_address.
Types of Attributes:
Simple attribute: The attributes which are not
divisible are called simple attributes. For example, phone number is atomic value of 10 digits.
Composite attributes: They can be divided into
smaller parts which represent more basic attributes with independent attributes. For example, address can be divided into street, city, state, zip.
Single valued attribute: This attribute contain
single value for particular entity. For example, age is single valued attribute of a person.
Multivalued attribute: It contain more than one
values. For example, a person may contain more than one degree, phone number etc.
Derived attribute: This attribute do not exist in
physical database, but values are derived from other attributes present in the database. For example, age and birth date are attributes of a person. Age can be derived from data of birth.
Null value: An entity may not have applicable value
for an attribute. For example, apartment number applies to addresses that are in the apartment building and not to other type’s residences.
25
Relationship
Relationship exists between two database tables
when one table has foreign key that references to
primary key of the other table and it is also associates between entities.
Following are types of relationships between tables. one-to-one relationship: In this type, a row in table
one-to-many relationship: In this type, a row in
table A can have more matching rows in table B but row in table B can have only one matching row in table A.
many-to-many relationship: In this type, a row
in table A can have many matching rows in table B and vice versa.
27
Relational Model
The relational model represents the database as
collection of relations. It is most popular data model in DBMS. In the relational model, all data must be stored in the form of tables and each relation
consists of rows and columns. When a relation is
thought of set of table values, each row in the table represents collection of related data values.
In the relational model, each row in the table
represents real world entity or relationship. In this model, a row is called a tuple, a column header is called attribute and table is called a relation. Each
row contains unique value and each column contains values from a same domain.
Database Schema
The description of database is called the database
schema which is specified during database design and is not expected to change frequently. A
displayed schema is called schema diagram. In
relational database, the schema defines the tables, fields and relationships between fields and tables.
The schemas are generally stored in the data
dictionary. The term often used to refer graphical representation of the database structure.
Schema diagram for database
Database schema is divided into two categories :
Physical Database Schema: It is used to describe how
data is stored in the secondary storage using database management system such as Oracle, RDBMS, Sybase etc.
Logical Database Schema: It is data model expressed in
terms of data management technology. It consists of table, attributes, their types and the constraints on the
attributes if any. 30
Database Instance
The data in the database at particular moment is called
database state. It is also called database instance. It is snapshot of database. It is also used to describe
database environment including RDBMS software, table structure and other functionality. It is most commonly used when administrators describe multiple instances of the same database.
Logical data independence: It is capacity to
change the conceptual schema without having to change external schemas or application programs. Logical data is data about database which stores information about how data is managed inside. We may change the conceptual schema to expand the database ,to change constraints, or to reduce the database .In the last, external schema refer to only the remaining data should not be affected.
Physical data independence: It is capacity to
change the internal schema without having to change the conceptual schema. Physical data independence is the power to change the
physical data without impacting the schema or logical data. Physical data independence exists in most databases in which the exact location of
data on disk, hardware details of storage
encoding, compression, placement, merging of records and so on are hidden from the user.
32
Entity sets, Entity types, keys and
Relationship types
Entity type is collection of entities that have same
attributes. Each entity type is described by its name and attributes. Entity is an instance of entity type.
Types of Entities :
Weak Entity: It is an entity that cannot be identified by its
attributes. Its existence is depends on strong entity. It is indicated by double rectangle in the ER diagram. It
contains partial key is represented by dashed underline.
Strong Entity: It is an entity which has its own existence.
It is represented by rectangle and contains primary key represented by underline. The primary key is the one of its attributes which uniquely identifies its member.
An entity set is collection of all entities of a particular
entity type in the database. For example, all persons having an account at a bank.
Keys are important part of relational database. They are
used to identify relation between tables.
Super key: It is set of attributes that identifies uniquely
each record within relational database management system table.
Candidate key: it6 is defined as set of fields from which
primary key can be selected and can uniquely identify any database record without referring to any other data.
Primary key: It is a key that uniquely identify each record
in a table. Only one candidate key can be primary key. 34
Relationship types defines set of association among
entity types. e.g. teaching.
Degree of Relationship Type: It is number of
participating entity types. A relationship of degree one is called unary e.g. student, A relationship of degree two is called binary e.g. student, teachers, A relationship of degree three is called ternary e.g. student, teacher,
subject.
Recursive Relationship: Each entity type that
participates in a relationship type plays particular role in the relationship. A same entity participates more than once in different roles. E.g. Department plays role of department or employer.
35
