Final Project Guidelines for Database

Comprehensive guidelines for designing, implementing, optimizing, and documenting a database project. Ideal for final-year DBA students working with PostgreSQL or similar RDBMS.

1. Design & Modeling Phase

Before writing any code, you must understand the data requirements.

• Identify Entities: Focus on the core “nouns” of the organization (e.g., Staff, Assets, Transactions).
• Normalization: Aim for 3rd Normal Form (3NF) to reduce redundancy. Ensure every table has a clear Primary Key.
• Relationship Mapping: Clearly define if relationships are 1:1, 1:N, or M:N. Use an ERD to visualize these connections before creating tables.

2. Database Implementation

When building the schema, prioritize data integrity.

• Constraints: Use NOT NULL for required fields, UNIQUE for identifiers like emails, and CHECK constraints to enforce business rules (e.g., salary > 0).
• Referential Integrity: Always define FOREIGN KEY actions. Decide whether to use ON DELETE CASCADE or SET NULL based on how the data should behave when a parent record is removed.
• Naming Conventions: Be consistent. Use snake_case for table and column names to avoid issues in different SQL environments.

3. Optimization & Logic (The DBA Layer)

A professional database does more than just store data; it manages it.

• Views vs. Materialized Views: Use standard Views for security and simplicity. Use Materialized Views only when dealing with massive datasets where pre-computed results are necessary for performance.
• Automation: Use Triggers for auditing (logging changes) or maintaining complex integrity that simple constraints can’t handle.
• Encapsulation: Write Stored Procedures for multi-step operations (like transferring funds or enrolling a student) to ensure the logic stays within the database.

4. Security & Access Control

Never use the superuser/admin account for your application.

• Principle of Least Privilege: Create specific roles (e.g., app_user, reporting_role).
• Permissions: Grant only what is necessary. An application user usually needs SELECT, INSERT, UPDATE but should rarely have permission to DROP tables.

5. Application Integration (Python/CRUD)

When connecting your Python app to the database:

• Connection Management: Always use a try-except-finally block or a context manager to ensure database connections are closed properly, even if an error occurs.
• Prevent SQL Injection: Never use string formatting (e.g., f"SELECT...{user_input}") to build queries. Always use parameterized queries (e.g., %s placeholders in psycopg2).
• Environment Variables: Store your database credentials (host, user, password) in an .env file rather than hardcoding them into your scripts.

6. Documentation & Testing

• Comment Your Code: Explain why a specific trigger or complex join is necessary.
• Test Edge Cases: What happens if a user tries to delete a department that still has employees? What happens if the database is offline?
• Version Control: Keep your .sql schema files and your .py files in a repository (like GitHub) to track changes.