Keywords: Software development, Backend development, Database Management, DBLC, DDLC, Database design, Relational database, SQL, non-relational database, NoSQL, Database administrator, Database developer, skills, tools
·
Database Management
is part of backend development that refers to the server-side of Software
development, Database Management include:
o
Database Administration
o
Database Optimization
o
Data Security and
Compliance
o
Database Development and
Design
o
Data Analysis and Reporting
§
Database Life Cycle (DBLC):
o
Database Initial Study
o
Database Design
o
Implementation and Loading
o
Testing and evaluation
o
Operations
o
Maintenance and evolution
·
Database Development
Lifecycle (DDLC):
o
Planning
o
Analysis
o
Design
o
Implementation
o
Testing
o
Maintenance
·
Database Design Types:
o
Conceptual Design: Database
modeling is used to create an abstract database structure, which permits you to
emphasize the big picture without getting into details
o
DBMS software selection
o
Logical Design: logical
design translates the conceptual design into an internal model of the chosen
DBMS. It specifies a high-level language, Tables, Entities, normalization,
indexes, views, transactions, etc.
o Physical Design: a relocation of the expected schema into the actual database structure, map the entities into tables, relationships to foreign keys, and unique identifiers to unique keys. Translating schemas into database structure requires creating partitions, indexes, constraints, access control, and implementing some business rule chat that could not be modeled earlier.
·
Database Design Goals:
o
Design for Performance:
§
The key to database and
application performance is design, not tuning. While tuning is quite valuable,
it cannot make up for poor design
§
design must start with an
efficient data model, well-defined performance goals and metrics, and a
sensible benchmarking strategy.
o Design for Scalability
§
Scalability is the ability
of a system to perform well as its load increases.
§
Load is a combination of
number of data volumes, number of users, and other relevant factors
§
To design for scalability,
you must use an effective benchmarking strategy, appropriate application
development techniques (such as bind variables) and appropriate Database
architectural features like shared server connections, clustering,
partitioning, and parallel operations.
o Design for Extensibility
§
Extensibility is the ease
with which a database or database application accommodates future growth
§
The more extensible the
database or application, the easier it is to add or change functionality with
minimal impact on existing functionality.
§
Extensibility differs from
forward compatibility (the ability of an application to accept data from a
future version of itself and use only the data that it was designed to accept).
§
To maximize extensibility,
you must design it into your database and applications by including mechanisms
that allow enhancement without major changes to infrastructure.
o Design for Security
§
Designing and implementing
security policies to protect the data of an organization, users, and
applications from accidental, inappropriate, or unauthorized actions
§
Creating and enforcing
policies and practices of auditing and accountability for inappropriate or
unauthorized actions
§
Creating, maintaining, and
terminating user accounts, passwords, roles, and privileges
§
Developing applications
that provide desired services securely in a variety of computational models,
leveraging database and directory services to maximize both efficiency and ease
of use
o Design for Availability
§
Availability is the degree
to which an application, service, or function is accessible on demand.
§
A system designed for high
availability provides uninterrupted computing services during essential time
periods, during most hours of the day throughout the year, with minimal
downtime for operations such as upgrading the system's hardware or software
§
The main characteristics of
a highly available system are: Reliability, Recoverability, Timely error
detection and Continuous operation
o Design for Portability
§
Design database should be highly
portable across operating systems, platform, and Programming languages.
§
When developed and deployed
on a database, the application can stay deployed on that database forever.
§
If the application is moved
to another database, the visual component and application logic can move
independently of the data logic in the stored procedures, which simplifies the
move
o Design for Diagnosability
§
Diagnosability means preventing,
detecting, diagnosing, and resolving database problems
§
Problems include critical
errors such as code bugs, metadata corruption, and customer data corruption.
§
The goals of the Diagnosability
infrastructure are to detect problems proactively, limit damage and
interruptions after a problem is detected, reduce the time required to diagnose
and resolve problems
o Design for Special Environments
§
Design for Data Warehouse (a
relational database that is designed for query and analysis rather than for
transaction processing).
§
Data warehouse includes Statistical
analysis, Reporting, Data mining capabilities, Client analysis tools and Applications
that manage the process of gathering data; transforming it into useful,
actionable information; and delivering it to business users
§
Data warehousing systems Use
many indexes, use some (but not many) joins, use denormalized or partially
denormalized schemas and Have workloads designed to accommodate ad hoc queries
and data analysis
§
Design for Online
Transaction Processing (OLTP) (optimized for fast and reliable transaction
handling)
§
OLTP systems Use few
indexes, use many joins, use fully normalized schemas to optimize update,
insert, and delete performance, and to guarantee data consistency and rarely
use derived data and aggregates
o Features for Special Scenarios
§
Special Scenarios such as SQL
Analytic Functions, Materialized Views, Partitioning and Temporal Validity
Support
§
A SQL analytic function
computes an aggregate value based on a group of rows
§
A SQL analytic function
differs from an aggregate function in that it returns multiple rows for each
group
§
Examples for analytic
functions include AVG, COUNT, MAX, MIN, SUM and so on
§
Materialized views are
query results that have been stored ("materialized") as schema
objects. Like tables and views, materialized views can appear in the FROM
clauses of queries.
§
Materialized views are used
to summarize, compute, replicate, and distribute data
§
Partitioning is the
database ability to physically break a very large table, index, or materialized
view into smaller pieces that it can manage independently
§
Partitioning is similar to
parallel processing, which breaks a large process into smaller pieces that can
be processed independently.
§ Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid.
·
Methods of data storage
for applications
o Relational databases (or SQL databases)
§
Stores data in tabular
format with rows and columns.
§
The columns contain data
attributes, and the rows have data values.
§
You can link the tables in
a relational database to gain deeper insights into the interconnection between
diverse data points.
o
Non-relational databases
(or NoSQL databases)
§
Use a variety of data
models for accessing and managing data.
§
They are optimized
specifically for applications that require large data volume, low latency, and
flexible data models, which is achieved by relaxing some of the data
consistency restrictions of other databases.
·
Database Management
Roles
o
Database Administrator
(DBA)
o Database Developer
·
Data Administrators
(DBAs) Responsibilities:
o
Focuses on managing,
securing, and overseeing the data storage systems of an organization.
o
Serve as the stewards of
data integrity, ensuring that databases are accessible, performant, and
safeguarded against breaches or loss.
o
With a deep understanding
of database software and architectures, DBAs play a pivotal role in the
seamless operation of data-driven applications and services.
o
Responsible for Installing
and upgrading database server and application tools
o
Allocating system storage
and planning future storage requirements for the database system
o
Implementing and
maintaining database security, including user management and role assignment
o
Ensuring data recovery,
backup, and restoration procedures are in place and effective
o
Monitoring and optimizing
the performance of the database
o
Planning and executing the
migration of databases to new technologies and platforms
o
Configuring, monitoring,
and maintaining database replication and clustering
o Working with database developers and IT project managers
·
Database Developers
Responsibilities:
o
Focuses on design and develop
databases that store, organize, and retrieve data efficiently
o
translating business needs
into robust database solutions
o
architects of robust
database systems, ensuring data integrity, security, and performance to meet
the needs of users and applications
o
With a deep understanding
of database languages like SQL, they design schemas, write queries, and develop
stored procedures and functions
o
Monitoring and tuning
database performance to maintain high levels of efficiency and reliability
o
Collaborating with Backend
developers to integrate databases with applications and establish proper
communication between systems
o
Implementing and
maintaining database security measures to safeguard sensitive information
o Conducting data migrations and upgrades to ensure databases remain up to date with technological advancements
·
Database Management Tools:
o
Database Systems: SQL
Server, Oracle, MySQL, PostgreSQL,
o
Database Languages: T-SQL,
PL/SQL, XQuery
o
Database Management Systems:
SQL Server Management Studio (SSMS), Oracle SQL Developer, MySQL Workbench
o
NoSQL database: MongoDB, Oracle
NoSQL Database, Apache Cassandra
o Cloud-based database Services: AWS RDS, Azure SQL Database, and Google Cloud SQL
