DATABASE

MANAGEMENT SYSTEM
CSC403
 CHAPTER 1.INTRODUCTION TO
DATABASE CONCEPTS
🞂 Introduction
🞂 Characteristics of databases
🞂 File system v/s Database system
🞂 Data abstraction and data Independence
🞂 DBMS system architecture
🞂 Database Administrator
 INTRODUCTION
🞂 A database is an organized collection of structured information,
or data, typically stored electronically in a computer system.
🞂 A database is usually controlled by a database management
system (DBMS).
🞂 The data and the DBMS, along with the applications that are
associated with them, are referred to as a database system, often
shortened to just database.
🞂 Data within the most common types of databases in operation
today is typically modeled in rows and columns in a series of
tables to make processing and data querying efficient.
🞂 The data can then be easily accessed, managed, modified,
updated, controlled, and organized. Most databases use
structured query language (SQL) for writing and querying data.
 INTRODUCTION cont..
🞂 A database is a collection of interrelated data that helps in the
efficient retrieval, insertion, and deletion of data from the
database and organizes the data in the form of tables, views,
schemas, reports, etc.
🞂 For Example, a university database organizes the data about
students, faculty, admin staff, etc. which helps in the efficient
retrieval, insertion, and deletion of data from it.
Introduction cont..
⮚ What is DBMS?
🞂 A Database Management System (DBMS) is a software
system that is designed to manage and organize data in
a structured manner.
🞂 It allows users to create,modify,and query a database,
as well as manage the security and access controls for
that database.
🞂 DBMS provides an environment to store and retrieve
data in convenient and efficient manner.
Introduction cont..
Introduction cont..
1) Centralized Database
🞂 It is the type of database that stores data at a centralized database system. It
comforts the users to access the stored data from different locations through several
applications. These applications contain the authentication process to let users
access data securely. An example of a Centralized database can be Central Library
that carries a central database of each library in a college/university.
Advantages of Centralized Database
🞂 It has decreased the risk of data management, i.e., manipulation of data will not
affect the core data.
🞂 Data consistency is maintained as it manages data in a central repository.
🞂 It provides better data quality, which enables organizations to establish data
standards.
🞂 It is less costly because fewer vendors are required to handle the data sets.
Disadvantages of Centralized Database
🞂 The size of the centralized database is large, which increases the response time for
fetching the data.
🞂 It is not easy to update such an extensive database system.
🞂 If any server failure occurs, entire data will be lost, which could be a huge loss.
Introduction cont..
2) Distributed Database
🞂 Unlike a centralized database system, in distributed systems, data is
distributed among different database systems of an organization. These
database systems are connected via communication links. Such links help
the end-users to access the data easily. Examples of the Distributed
database are Apache Cassandra, HBase, Ignite, etc.
🞂 We can further divide a distributed database system into:
⮚ Homogeneous DDB: Those database systems which execute on the same
operating system and use the same application process and carry the same
hardware devices.
⮚ Heterogeneous DDB: Those database systems which execute on different
operating systems under different application procedures, and carries
different hardware devices.
Introduction cont..
🞂Advantages of Distributed Database
Modular development is possible in a distributed database, i.e., the system
can be expanded by including new computers and connecting them to the
distributed system. One server failure will not affect the entire data set.
Introduction cont..
3) Relational Database
🞂 This database is based on the relational data model,
which stores data in the form of rows(tuple) and
columns(attributes), and together forms a table(relation).
A relational database uses SQL for storing, manipulating,
as well as maintaining the data. E.F. Codd invented the
database in 1970. Each table in the database carries a key
that makes the data unique from others. Examples of
Relational databases are MySQL, Microsoft SQL Server,
Oracle, etc.
🞂
Introduction cont..
🞂 Properties of Relational Database
🞂 There are following four commonly known properties of a relational model
known as ACID properties, where:
🞂 A means Atomicity: This ensures the data operation will complete either
with success or with failure. It follows the 'all or nothing' strategy. For
example, a transaction will either be committed or will abort.
🞂 C means Consistency: If we perform any operation over the data, its value
before and after the operation should be preserved. For example, the
account balance before and after the transaction should be correct, i.e., it
should remain conserved.
🞂 I means Isolation: There can be concurrent users for accessing data at the
same time from the database. Thus, isolation between the data should
remain isolated. For example, when multiple transactions occur at the same
time, one transaction effects should not be visible to the other transactions
in the database.
🞂 D means Durability: It ensures that once it completes the operation and
commits the data, data changes should remain permanent.
🞂
Introduction cont..
Introduction cont..
4) NoSQL Database
🞂 Non-SQL/Not Only SQL is a type of database that is used for
storing a wide range of data sets. It is not a relational database
as it stores data not only in tabular form but in several
different ways. It came into existence when the demand for
building modern applications increased. Thus, NoSQL
presented a wide variety of database technologies in response
to the demands. We can further divide a NoSQL database into
the following four types:
🞂 Key-value storage: It is the simplest type of database storage
where it stores every single item as a key (or attribute name)
holding its value, together .
Introduction cont..
🞂 Document-oriented Database: A type of database used to store data as
JSON-like document. It helps developers in storing data by using the same
document-model format as used in the application code.
🞂 Graph Databases: It is used for storing vast amounts of data in a graph-like
structure. Most commonly, social networking websites use the graph
database.
🞂 Wide-column stores: It is similar to the data represented in relational
databases. Here, data is stored in large columns together, instead of storing
in rows.
🞂 Advantages of NoSQL Database
🞂 It enables good productivity in the application development as it is not
required to store data in a structured format.
🞂 It is a better option for managing and handling large data sets.
🞂 It provides high scalability.
🞂 Users can quickly access data from the database through key-value.
Introduction cont..
5) Cloud Database
🞂 A type of database where data is stored in a virtual
environment and executes over the cloud computing
platform. It provides users with various cloud computing
services (SaaS, PaaS, IaaS, etc.) for accessing the
database. There are numerous cloud platforms, but the
best options are:
🞂 Amazon Web Services(AWS)
🞂 Microsoft Azure
🞂 Kamatera
🞂 PhonixNAP
🞂 ScienceSoft
🞂 Google Cloud SQL, etc.
Introduction cont..
Introduction cont..
Introduction cont..
6) Object-oriented Databases
🞂 The type of database that uses the object-based data model approach for
storing data in the database system. The data is represented and stored as
objects which are similar to the objects used in the object-oriented
programming language.
7) Network Databases
🞂 It is the database that typically follows the network data model. Here, the
representation of data is in the form of nodes connected via links between
them. Unlike the hierarchical database, it allows each record to have
multiple children and parent nodes to form a generalized graph structure.
8) Operational Database
🞂 The type of database which creates and updates the database in real-time. It
is basically designed for executing and handling the daily data operations
in several businesses. For example, An organization uses operational
databases for managing per day transactions.
Introduction cont..
Introduction cont..
9)Hierarchical Databases
🞂 It is the type of database that stores data in the form of
parent-children relationship nodes. Here, it organizes
data in a tree-like structure.
🞂 Data get stored in the form of records that are
connected via links. Each child record in the tree will
contain only one parent. On the other hand, each parent
record can have multiple child records.
Introduction cont..
Introduction cont..
10) Enterprise Database
🞂 Large organizations or enterprises use this database for
managing a massive amount of data. It helps organizations
to increase and improve their efficiency. Such a database
allows simultaneous access to users.
🞂 Advantages of Enterprise Database:
🞂 Multi processes are supportable over the Enterprise
database.
🞂 It allows executing parallel queries on the system.
CHARACTERISTICS OF A
DATABASE
Here are some characteristics of a database:
🞂 Structured organization: Data is organized in a
structured way to allow for efficient storage and
retrieval.
🞂 Data independence: Data is separated from the
application, allowing for changes without
affecting the application.
🞂 Data integrity: Data is accurate and reliable.
🞂 Self-describing: The database system contains
metadata that defines and describes the data and
relationships between tables.
CHARACTERISTICS OF A
DATABASE
🞂 Insulation between program and data: The data structure is
stored in the system catalog, not in the programs.
🞂 Support for multiple views of data: Different views are
provided to different users.
🞂 Sharing of data and multiuser system: Database systems are
designed for multiple users.
🞂 Control of data redundancy: Each data item is ideally stored
in only one place in the database.
🞂 Restriction of unauthorized access: Access to the database is
restricted.
🞂 Backup and recovery facilities: Facilities are available for
backing up and recovering data.
🞂 ACID transactions: Transactions are defined by the four
properties of Atomicity, Consistency, Isolation, and
Durability.
Difference Between File System and DBMS
Difference Between File System and DBMS
cont..
Difference Between File System and DBMS
cont..
Difference Between File System and DBMS
cont..
Difference Between File System and DBMS
cont..
🞂 A file system manages storage, while a DBMS provides efficient data
management.
🞂 The main difference between a file system and a DBMS (Database
Management System) is the way they organize and manage data. File
systems are used to manage files and directories, and provide basic operations
for creating, deleting, renaming, and accessing files. They typically store data
in a hierarchical structure, where files are organized in directories and
subdirectories. File systems are simple and efficient, but they lack the ability to
manage complex data relationships and ensure data consistency.
🞂 On the other hand, DBMS is a software system designed to manage large
amounts of structured data, and provide advanced operations for storing,
retrieving, and manipulating data. DBMS provides a centralized and organized
way of storing data, which can be accessed and modified by multiple users or
applications. DBMS offers advanced features like data validation, indexing ,
transactions, concurrency control , and backup and recovery mechanisms.
DBMS ensures data consistency, accuracy, and integrity by enforcing data
constraints, such as primary keys, foreign keys, and data types.
Difference Between File System and DBMS
cont..
🞂 In summary, file systems are suitable for managing small amounts
of unstructured data , while DBMS is designed for managing large
amounts of structured data, and offers more advanced features for
ensuring data integrity, security, and performance.
Conclusion
🞂 On balance, a File Systemfocuses more on organizing, creating,
storing, retrieving, renaming and deleting files at a storage device and
mainly deals with fundamental levels of data operations. It is user-
friendly and convenient for dealing with various files
and directories but does not support complex data handling. In contrast,
a DBMS is intended for comprehensive data storage, providing
organization, efficient data access, and reliable information integrity.
DBMS is appropriate for complex cases of data management, with
many records that require storage, searching and updating.
DATA ABSTRACTION AND DATA
INDEPENDENCE
🞂 Data abstraction
🞂 Database systems comprise complex data structures. In order
to make the system efficient in terms of retrieval of data, and
reduce complexity in terms of usability of users, developers
use abstraction i.e. hide irrelevant details from the users. This
approach simplifies database design.
🞂 Level of Abstraction in a DBMS

There are mainly 3 levels of data abstraction:

✔ Physical or Internal Level

✔ Logical or Conceptual Level

✔ View or External Level

DATA ABSTRACTION AND DATA
INDEPENDENCE cont..
🞂 Physical or Internal Level
🞂 This is the lowest level of data abstraction.
🞂 It tells us how the data is actually stored in memory.
🞂 Access methods like sequential or random access and file
organization methods like B+ trees and hashing are used for
the same.
🞂 Usability, size of memory, and the number of times the
records are factors that we need to know while designing the
database.
🞂 Suppose we need to store the details of an employee.
Blocks of storage and the amount of memory used for
these purposes are kept hidden from the user.
DATA ABSTRACTION AND DATA
INDEPENDENCE cont..
🞂 Logical or Conceptual Level
🞂 This level comprises the information that is actually stored in the database
in the form of tables.
🞂 It also stores the relationship among the data entities in relatively simple
structures.
🞂 At this level, the information available to the user at the view level is
unknown.
🞂 We can store the various attributes of an employee and relationships, e.g.
with the manager can also be stored.
🞂 The logical level thus describes the entire database in terms of a small
number of relatively simple structures.
🞂 Although implementation of the simple structures at the logical level may
involve complex physical-level structures, the user of the logical level does
not need to be aware of this complexity.
🞂 This is referred to as physical data independence. Database administrators,
who must decide what information to keep in the database, use the logical
level of abstraction.
DATA ABSTRACTION AND DATA
INDEPENDENCE cont..
🞂 View or External Level
🞂 This is the highest level of abstraction.
🞂 Only a part of the actual database is viewed by the users.
🞂 This level exists to ease the accessibility of the database by an individual
user.
🞂 Users view data in the form of rows and columns.
🞂 Tables and relations are used to store data.
🞂 Multiple views of the same database may exist.
🞂 Users can just view the data and interact with the database, storage and
implementation details are hidden from them.
🞂 Even though the logical level uses simpler structures, complexity remains
because of the variety of information stored in a large database.
🞂 Many users of the database system do not need all this information;
instead, they need to access only a part of the database.
🞂 The view level of abstraction exists to simplify their interaction with the
system
DATABASE ADMINISTRATOR
🞂 A Database Administrator (DBA) is a professional
responsible for managing and maintaining database systems
within an organization.
🞂 The DBA ensures that databases are running efficiently,
securely, and are available for use by various applications and
users.
🞂 The role of a DBA is critical in managing the health,
performance, and security of databases, which are central to
many business operations.
DATABASE ADMINISTRATOR
cont..
🞂 Key Responsibilities of a Database Administrator
🞂 Database Design and Architecture:
◦ Schema Design: A DBA is involved in designing the database schema,
including defining tables, views, indexes, and relationships between data. This
design ensures that the data is organized efficiently and that the database can
handle the expected workload.
◦ Normalization/Denormalization: DBAs ensure that the database is normalized
to eliminate data redundancy while considering whether denormalization might
be needed for performance reasons.
🞂 Database Installation and Configuration:
◦ A DBA installs and configures the database management system (DBMS),
ensuring that the system is set up properly and optimized for the organization's
needs.
◦ Configuring and managing the underlying hardware (e.g., storage, servers) and
software for optimal performance is also part of the job.
DATABASE ADMINISTRATOR
cont..
🞂 Key Responsibilities of a Database Administrator
🞂 Data Security and Access Control:
◦ User Authentication: The DBA defines who can access the database and
assigns roles and permissions, ensuring that only authorized users can perform
certain actions (e.g., read, write, update, delete).
◦ Encryption: Sensitive data is often encrypted by DBAs to ensure data privacy
and integrity, both during storage and transmission.
◦ Audit Trails: DBAs set up systems to track database access and modifications
to meet regulatory and security requirements.
🞂 Backup and Recovery:
◦ Backup Strategies: DBAs implement regular backup strategies to prevent data
loss. This can include full, incremental, and differential backups, depending on
the needs of the organization.
◦ Disaster Recovery: In case of hardware failure, corruption, or disaster, the DBA
ensures there are reliable and tested recovery procedures to restore the database
to a consistent state.
DATABASE ADMINISTRATOR
cont..
🞂 Key Responsibilities of a Database Administrator
🞂 Performance Tuning and Optimization:
◦ Indexing: The DBA optimizes the performance of database queries by creating and
managing indexes, improving the speed of data retrieval operations.
◦ Query Optimization: DBAs work with developers to ensure that queries are written
efficiently to minimize resource usage and improve performance.
◦ Monitoring and Analysis: Continuous monitoring of database performance is essential.
DBAs use tools to analyze query execution plans, database load, and resource utilization
to identify bottlenecks and optimize performance.
🞂 Database Maintenance:
◦ Routine Maintenance: Regular tasks such as updating statistics, reorganizing fragmented
tables, and performing database health checks are essential for smooth operations.
◦ Patch Management: DBAs apply updates and patches to the DBMS to fix security
vulnerabilities, bugs, or performance issues.
🞂 Database Migration and Upgrades:
◦ Version Upgrades: A DBA is responsible for upgrading the DBMS to newer versions or
migrating databases from one version or platform to another while ensuring minimal
downtime and data integrity.
◦ Data Migration: In cases where organizations switch to a new DBMS or integrate new
applications, the DBA ensures that data is accurately migrated without disruption.
DATABASE ADMINISTRATOR
cont..
🞂 Key Responsibilities of a Database Administrator
🞂 Troubleshooting and Support:
◦ DBAs are responsible for diagnosing and troubleshooting database-related issues
such as performance degradation, connectivity issues, or data corruption.
◦ They provide support to developers and users in resolving database-related
issues, ensuring that operations continue smoothly.
🞂 Capacity Planning:
◦ A DBA needs to forecast database storage and resource needs based on usage
patterns. This involves ensuring that the database can scale as the organization
grows.
◦ Regularly analyzing disk space usage, I/O patterns, and hardware requirements
ensures that the database will not run into performance issues as it expands.
🞂 Compliance and Regulatory Requirements:
◦ DBAs ensure that the database meets legal and regulatory requirements,
including data privacy regulations such as GDPR, HIPAA, and SOX. They
ensure proper access controls, auditing, and data protection measures are in
place.
DATABASE ADMINISTRATOR
cont..
🞂 Skills Required for a Database Administrator
🞂 Technical Skills:
◦ DBMS Knowledge: Proficiency in major database systems like Oracle,
SQL Server, MySQL, PostgreSQL, or MongoDB.
◦ SQL: Strong knowledge of SQL (Structured Query Language) for
creating, querying, and managing databases.
◦ Backup and Recovery Tools: Experience with backup tools, recovery
strategies, and disaster recovery procedures.
◦ Performance Tuning: Knowledge of performance tuning techniques
like indexing, query optimization, and resource management.
◦ Scripting and Automation: Familiarity with scripting languages like
Shell, Python, or PowerShell to automate routine tasks.
🞂 Analytical Skills:
◦ Problem-Solving: Ability to diagnose and troubleshoot database issues,
often under pressure.
◦ Capacity Planning and Optimization: Understanding the factors that
affect performance and the ability to plan for future growth.
DATABASE ADMINISTRATOR
cont..
⮚ Skills Required for a Database Administrator
🞂 Security Expertise:
◦ Understanding of encryption methods, access control, and security
practices to ensure the integrity and confidentiality of data.
🞂 Soft Skills:
◦ Communication: A DBA needs to communicate effectively with
developers, system administrators, and business leaders to understand
requirements and explain technical issues.
◦ Attention to Detail: Precision is essential in database administration to
prevent errors and ensure the accuracy of data operations.
◦ Time Management: A DBA needs to prioritize tasks efficiently, as they
often juggle multiple responsibilities simultaneously.
DATABASE ADMINISTRATOR
cont..
⮚ Types of Database Administrators
🞂 System DBA:
◦ Focuses on the overall health of the database system, including its installation,
configuration, and maintenance. They are responsible for the physical aspects of
database management.
🞂 Application DBA:
◦ Works closely with the development team to optimize database usage for
specific applications. Their focus is more on performance tuning and ensuring
that applications use the database efficiently.
🞂 Development DBA:
◦ Assists in database design and development by working with developers to
create and optimize database schemas, queries, and other structures that support
application development.
🞂 Cloud DBA:
◦ Specializes in managing databases hosted on cloud platforms like AWS, Azure,
or Google Cloud. Cloud DBAs need to understand cloud-specific tools and
architectures.
DATABASE ADMINISTRATOR
cont..
⮚ Tools and Technologies Used by DBAs
🞂 Database Management Systems (DBMS): Oracle, SQL Server, MySQL,
PostgreSQL, MongoDB, etc.
🞂 Performance Monitoring Tools: SolarWinds Database Performance
Analyzer, Oracle Enterprise Manager, SQL Diagnostic Manager, etc.
🞂 Backup and Recovery Tools: RMAN (Recovery Manager) for Oracle,
SQL Server Backup, Veritas NetBackup, etc.
🞂 Security Tools: Transparent Data Encryption (TDE), Audit Vault,
Firewalls, and encryption tools.
🞂 Automation Tools: Ansible, Chef, Puppet, or custom scripts.
⮚ Conclusion
🞂 A Database Administrator (DBA) plays a critical role in ensuring that an
organization's database systems are reliable, secure, and optimized for
performance. The role involves not just technical expertise but also strong
analytical skills, an understanding of data security, and the ability to
manage large and complex database environments effectively. DBAs are
essential for maintaining the integrity and efficiency of the databases that
organizations rely on for their day-to-day operations.
🞂