Advanced Database Design

Chapter1: DBMS

Dr. Marwa Khairy

Agenda
 DBMS
 Functions
 Characteristics
 Advantages & dis- Advantages
 Architecture
 Data Models
 Data Independence
 Database Languages
Database Management System

 Database management system is a software which is used to

manage the database. For example: mysql, oracle, etc are a
very popular commercial database which is used in different
applications.
 DBMS provides an interface to perform various operations
like database creation, storing data in it, updating data,
creating a table in the database and a lot more.
 It provides protection and security to the database. In the case
of multiple users, it also maintains data consistency.
Functions of the DBMS
 Data definition: it is used for creation, modification, and
removal of definition that defines the organization of data in
the database.
 Data updating: it is used for the insertion, modification, and
deletion of the actual data in the database.
 Data retrieval: it is used to retrieve the data from the
database which can be used by applications for various
purposes.
 User administration: it is used for registering and
monitoring users, maintain data integrity, enforcing data
security, dealing with concurrency control, monitoring
performance and recovering information corrupted by
unexpected failure.
Characteristics of DBMS
 It uses a digital repository established on a server to store and manage the
information.
 It can provide a clear and logical view of the process that manipulates data.
 DBMS contains automatic backup and recovery procedures.
 It can reduce the complex relationship between data.
 It is used to support manipulation and processing of data.
 It is used to provide security of data.
 It can view the database from different viewpoints according to the
requirements of the user.
Advantages of DBMS
 Controls database redundancy: It can control data redundancy because it
stores all the data in one single database file and that recorded data is placed in
the database.
 Data sharing: In DBMS, the authorized users of an organization can share the
data among multiple users.
 Easily Maintenance: It can be easily maintainable due to the centralized nature
of the database system.
 Reduce time: It reduces development time and maintenance need.
 Backup: It provides backup and recovery subsystems which create automatic
backup of data from hardware and software failures and restores the data if
required.
 multiple user interface: It provides different types of user interfaces like
graphical user interfaces, application program interfaces
Disadvantages of DBMS
 Cost of hardware and software: it requires a high speed of
data processor and large memory size to run DBMS software.
 Size: it occupies a large space of disks and large memory to
run them efficiently.
 Complexity: database system creates additional complexity
and requirements.
 Higher impact of failure: failure is highly impacted the
database because in most of the organization, all the data
stored in a single database and if the database is damaged due
to electric failure or database corruption then the data may be
lost forever.
Applications of the DBMS
 1. Railway Reservation System
 2. Library Management System
 3. Banking
 4. Education Sector
 5. Credit card exchanges
 6. Social Media Sites
 7. Broadcast communications
 8. Accounting and Finance
 9. E-Commerce Websites
 10. Human Resource Management
 11. Manufacturing
 12. Airline Reservation System
 13. Healthcare System
 14. Security
 15. Telecommunication
DBMS Architecture
 DBMS architecture depends upon how users are connected
to the database to get their request done.
 The DBMS design depends upon its architecture.
 The basic client/server architecture is used to deal with
many pcs, web servers, database servers and other
components that are connected with networks.
 The client/server architecture consists of many pcs and a
workstation which are connected via the network.
Types of DBMS architecture
1-Tier Architecture

 In this architecture, the database is directly available to the

user. It means the user can directly sit on the DBMS and
uses it.
 Any changes done here will directly be done on the
database itself. It doesn't provide a handy tool for end users.
 The 1-Tier architecture is used for development of the local
application, where programmers can directly communicate
with the database for the quick response.
2-Tier Architecture
 The 2-tier Architecture Is Same As Basic
Client-server.
 In The Two-tier Architecture, Applications On
The Client End Can Directly Communicate
With The Database At The Server Side. For
This Interaction, Api's
Like: ODBC, JDBC Are Used.
 The User Interfaces And Application Programs
Are Run On The Client-side.
 The Server Side Is Responsible To Provide
The Functionalities Like: Query Processing
And Transaction Management.
 To Communicate With The DBMS, Client-side
Application Establishes A Connection With
The Server Side.
3-tier Architecture
 The 3-tier architecture contains another
layer between the client and server. In
this architecture, client can't directly
communicate with the server.
 The application on the client-end
interacts with an application server
which further communicates with the
database system.
 End user has no idea about the existence
of the database beyond the application
server. The database also has no idea
about any other user beyond the
application.
 The 3-tier architecture is used in case of
large web application.
Data Models
 Data Model is the modeling of the data description, data semantics, and
consistency constraints of the data.
 It provides the conceptual tools for describing the design of a database at
each level of data abstraction.
 Therefore, there are following four data models used for understanding
the structure of the database:
1) Relational Data Model: This type of model designs the data in the form
of rows and columns within a table. Thus, a relational model uses tables for
representing data and in-between relationships
2) Entity-Relationship Data Model: An ER model is the logical
representation of data as objects and relationships among them. These
objects are known as entities, and relationship is an association among these
entities
3) Object-based Data Model: An extension of the ER model with notions
of functions, encapsulation, and object identity, as well.
4) Semistructured Data Model: The semistructured data model allows the
data specifications at places where the individual data items of the same
type may have different attributes sets. The Extensible Markup Language,
also known as XML, is widely used for representing the semistructured
data.
Data Independence
 Data Independence is defined as a property of DBMS that
helps you to change the Database schema at one level of a
database system without requiring to change the schema at
the next higher level.
 Data independence helps you to keep data separated from all
programs that make use of it.
Types of Data Independence
 In DBMS there are two types of data independence
1. Logical Data Independence
2. Physical Data Independence
A. Logical Data Independence
 Logical Data Independence is the ability to change the
conceptual scheme without changing
 External views
 External API or programs

 Examples of changes under Logical Data Independence

 Due to Logical independence, any of the below change will
not affect the external layer.
 Add/Modify/Delete a new attribute, entity or relationship is
possible without a rewrite of existing application programs
 Merging two records into one
 Breaking an existing record into two or more records
B. Physical Data Independence
 Physical data independence helps you to separate conceptual levels from
the internal/physical levels. It allows you to provide a logical description
of the database without the Need to specify physical structures

 Examples of changes under physical data independence

 Due to physical independence, any of the below change will not affect the
conceptual layer.
 Using a new storage device like hard drive or magnetic tapes
 Modifying the file organization technique in the database
 Switching to different data structures.
 Changing the access method.
 Modifying indexes.
 Changes to compression techniques or hashing algorithms.
 Change of location of database from say c drive to d drive
.
Difference Between Physical And Logical Data Independence
Importance of Data Independence
 Helps you to improve the quality of the data
 Database system maintenance becomes affordable
 Enforcement of standards and improvement in database security
 You don’t need to alter data structure in application programs
 Permit developers to focus on the general structure of the Database
rather than worrying about the internal implementation
 It allows you to improve state which is undamaged or undivided
 Database incongruity is vastly reduced.
 Easily make modifications in the physical level is needed to improve
the performance of the system.
Database Language
1. Data Definition Language (DDL)
 It is used to create schema, tables, indexes, constraints,
etc. in the database.
 Data definition language is used to store the information
of metadata like the number of tables and schemas, their
names, indexes, columns in each table, constraints, etc.
 Here are some tasks that come under DDL:
 Create: It is used to create objects in the database.
 Alter: It is used to alter the structure of the database.
 Drop: It is used to delete objects from the database.
 Truncate: It is used to remove all records from a table.
 Rename: It is used to rename an object.
 Comment: It is used to comment on the data dictionary.
2. Data Manipulation Language (DML)
 It is used for accessing and manipulating data in a database.
It handles user requests.
 Here are some tasks that come under DML:
 Select: it is used to retrieve data from a database.
 Insert: it is used to insert data into a table.
 Update: it is used to update existing data within a table.
 Delete: it is used to delete all records from a table.
 Merge: it performs upsert operation, i.E., Insert or update
operations.
 Call: it is used to call a structured query language or a java
subprogram.
 Explain plan: it has the parameter of explaining data.
 Lock table: it controls concurrency.
3. Data Control Language (DCL)
 It is used to retrieve the stored or saved data.
 The DCL execution is transactional. It also has rollback
parameters.
 Here are some tasks that come under DML:
 Revoke: It is used to take back permissions from the user.
 Grant: It is used to give user access privileges to a
database..
4. Transaction Control Language (TCL)

 TCL is used to run the changes made by the DML statement.

TCL can be grouped into a logical transaction.
 Here are some tasks that come under TCL:
 Commit: It is used to save the transaction on the database.
 Rollback: It is used to restore the database to original since the last
Commit