KodeKloud Notes

In a relational database environment, clear separation of duties ensures data security, integrity, and availability. Below, we outline each role’s scope, permission level, and core tasks.

User Accounts and Permission Models

Regular users require finely tuned access to perform their daily tasks without overstepping boundaries.

Permission Type	Description
Read Only	View existing rows and schema definitions only
Insert Without Select	Add new rows without seeing existing data
Delete Rows	Remove rows under controlled conditions

Best Practice

Grant the minimum necessary permissions following the principle of least privilege. Regularly audit roles to ensure they match current job functions.

Applications and Reporting Tools

Applications and BI tools should inherit or explicitly receive the same permissions as individual users.

Honor end-user credentials by default
Require explicit grants when operating under a service account
Prevent unauthorized data operations through role-based access

Database Administrators

DBAs oversee the entire lifecycle of database operations:

Assign and revoke user permissions
Configure automated backups and restore procedures
Monitor system health and performance metrics
Plan and execute disaster recovery drills

Data Analysts

Analysts focus on schema design and data model optimization:

Use Data Definition Language (DDL) to create or modify tables
Implement normalization rules to reduce redundancy
Typically cannot SELECT sensitive production data directly

Data Engineers

Engineers specialize in deployment and performance tuning:

Configure database clusters and edge deployments (e.g., Azure IoT Edge)
Optimize indexing, partitioning, and query plans
Maintain schema integrity without viewing or exporting raw data

The image outlines roles and responsibilities for Users, Database Administrators, Analysts, and Engineers, detailing their tasks in data management and system configuration.

Core Characteristics of Structured Data

Structured data lives in relational tables with well-defined schemas and relationships:

Stored in relational database management systems (RDBMS)
Supports Online Transaction Processing (OLTP) and batch workflows
Organized into normalized tables with consistent column definitions
Relationships enforced via primary keys (PK) and foreign keys (FK)
Accessed and manipulated using SQL (SELECT, INSERT, UPDATE, DELETE)

The image summarizes key characteristics of structured data, highlighting its storage in relational databases, support for transaction processing, use of normalized tables, relationships with keys, and interaction through SQL statements.

Deployment Options and Considerations

Choosing the right infrastructure impacts cost, scalability, and reliability:

Deployment Type	Pros	Cons
Virtual Machines + DBMS	Full control, custom tuning	Requires server management
Serverless Databases	Automatic scaling, pay-per-use	Potential cold starts

Scale horizontally or vertically as transaction volume increases
Enforce ACID properties to maintain consistency across updates
Administrators manage network access, encryption, and patching

Warning

Improper configuration of network security groups or firewall rules can expose your database to external threats. Always restrict access to known IP ranges.

The image is a summary of structured data, highlighting solutions like VMs with DBMSes, transaction limits, integrity for consistency, and administrator roles in access and security management.

Watch Video

Watch video content