Modern Software Development and Database Fundamentals

Posted on Feb 26, 2026 in Software Engineering

Relational Database Design and Normalization

The relational model stores data in tables (relations), where rows represent tuples and columns represent attributes.

  • Key components: Tables, attributes, tuples, and constraints (PK, FK, UNIQUE, NOT NULL).
  • Mapping ER to Relational: Entity becomes a table, 1:many uses a foreign key (FK) on the many side, many:many requires a junction table, and multivalued attributes move to separate tables.

Functional Dependency (FD): X → Y means X uniquely determines Y. Armstrong’s axioms include reflexivity (if Y ⊆ X then X → Y), augmentation (X → Y ⇒ XZ → YZ), and transitivity (X → Y and Y → Z ⇒ X → Z).

Keys and Indexing: A superkey identifies a row uniquely, while a key is a minimal superkey. Primary Keys (PK) must be unique and not null. Indexes speed up searches but slow down write operations. A B-tree index keeps keys sorted in a balanced tree for fast O(log n) searches and range queries.

Normalization Levels:

  • 1NF: Atomic values.
  • 2NF: No partial dependency on a composite key.
  • 3NF: No transitive dependency.
  • BCNF: Every determinant is a candidate key.

Transactions, ACID, and SQL Fundamentals

A transaction is a logical unit of work that must be all-or-nothing. The ACID properties ensure reliability:

  • Atomicity: All or nothing.
  • Consistency: Valid state transitions.
  • Isolation: Concurrent transactions do not interfere.
  • Durability: Committed data is permanent.

Concurrency and Isolation: Common problems include lost updates, dirty reads, non-repeatable reads, and phantoms. Isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

SQL Categories:

  • DDL (Data Definition Language): Changes schema (CREATE, ALTER, DROP); usually auto-commits.
  • DML (Data Manipulation Language): Changes data (SELECT, INSERT, UPDATE, DELETE, MERGE); transactional.

Advanced Objects: Stored Procedures are server-side programs, UDFs are functions used in SQL, and Triggers are automatic actions on data changes.

Version Control Systems and Git Workflow

VCS stores change history and enables collaboration. Centralized systems (SVN) use one server, while Distributed systems (Git) provide every developer with a full repository history.

  • Git Basics: Commit (snapshot), Branch (parallel line), Tag (named release point), and Merge (combining histories).
  • Rebase: Rewrites commits for a linear history.
  • Fetch vs. Pull: Fetch downloads updates; Pull is fetch plus merge/rebase.

Git Flow: Utilizes main/master and develop branches, along with feature, release, and hotfix branches. Lifecycle management involves unit/integration/e2e testing and CI pipelines (Jira/GitHub).

Layered Application Architecture and Responsibilities

Layered architecture splits applications into distinct parts for better maintenance:

  • Presentation Layer: Handles UI, controllers, and DTO mapping.
  • Business/Service Layer: Contains business rules, transactions, and orchestration.
  • Data Access Layer (DAO/Repository): Communicates with the database via SQL or ORM.
  • Database Layer: Physical data storage.

This separation of concerns improves testability, scalability, and the ability to replace individual layers.

Web Services, SOA, and REST vs RPC

Web services allow applications to communicate over a network. Common types include REST (HTTP-based resources), SOAP (XML-based strict contracts), and RPC (Remote Procedure Calls).

  • Formats: JSON is standard for REST; XML is used for SOAP; gRPC uses Protobuf.
  • SOA (Service-Oriented Architecture): Systems built from independent services with clear contracts, emphasizing loose coupling and reusability.
  • REST vs. RPC: REST is resource-based (using GET, POST, PUT, DELETE), while RPC is action-based (calling methods over a network).

OOP Principles and SOLID Design

Object-Oriented Programming (OOP) groups data and behavior into objects. Key concepts include classes (blueprints), objects (instances), and interfaces (contracts).

  • Relations: Inheritance (is-a), Association (uses), Aggregation (weak has-a), and Composition (strong has-a).
  • SOLID Principles:
    • SRP: Single Responsibility Principle.
    • OCP: Open/Closed Principle (extend, don’t modify).
    • LSP: Liskov Substitution Principle.
    • ISP: Interface Segregation Principle.
    • DIP: Dependency Inversion Principle.

Server-Side Development with Spring and Threads

Server-side applications handle requests and business logic. In Spring Boot, the Controller → Service → Repository pattern is standard, with Dependency Injection (DI) managing beans.

Thread Management: Servers use thread pools. Potential issues include race conditions and deadlocks. Solutions include:

  • Synchronization and thread-safe structures.
  • Immutability and proper pooling.
  • Asynchronous processing.

Reflection and Annotations: Reflection inspects classes at runtime, while annotations (like @Controller or @Service) provide metadata for DI and routing.

Software Development Models and Agile Scrum

  • Waterfall: Strict, sequential phases; inflexible.
  • Evolutionary: Early working versions with step-by-step improvements.
  • Iterative: Repeated cycles of planning, building, and testing.
  • Spiral: Iterative development with heavy risk analysis.
  • RUP: Phases include Inception, Elaboration, Construction, and Transition.
  • Agile: Focuses on short iterations, frequent delivery, and customer feedback.
  • Scrum: Uses sprints, backlogs, and daily standups with roles like Product Owner and Scrum Master.

Framework Architecture, DI, and IoC Containers

A framework provides a reusable structure that implements Inversion of Control (IoC). Dependency Injection (DI) provides dependencies from the outside (e.g., constructor injection).

  • Scopes: Singleton (one instance), Prototype (new instance each time), and Request/Session (web-specific).
  • IoC Container: Manages object lifecycles and dependency injection.
  • Spring Modules: Includes Core (DI), MVC (web), Boot (auto-config), Data (DB), Security, AOP, and Test.

Application Security and Network Protection

Security must be applied at every layer, from input validation to database permissions.

  • SQL Injection: Prevented using prepared statements, parameterized queries, and the principle of least privilege.
  • Network Security: Employs HTTPS/TLS, secure cookies, CORS rules, and rate limiting.
  • Encryption: AES (symmetric), RSA (asymmetric), and Diffie-Hellman (key agreement).
  • Authentication: Uses sessions, cookies, or JWT tokens. Passwords should be stored as salted hashes (e.g., bcrypt or argon2).