TechSolutions Inc. - Database Project

This repository contains the full database schema, data, and programmable objects for a sample IT consulting company, "TechSolutions Inc." This was a project for a database module.

Database Design and Development Process

This project was developed by following a structured database design lifecycle, translating a set of business requirements into a fully functional relational database.

1. Requirement Analysis & Business Rules

The initial phase involved analyzing the operational needs of "TechSolutions Inc.", a growing IT consultancy. The key requirement was to create a centralized system to manage disparate business functions, including HR, sales, project management, and inventory.

From this analysis, a clear set of business rules was derived, which governed the relationships between different data entities. Examples include:

• An employee belongs to one department but can be assigned to multiple projects.
• A client can have multiple contracts and projects.
• Support tickets can be submitted by either an employee or a client.
• Access to sensitive data like payroll and contracts must be restricted based on an employee's role.

2. Conceptual and Logical Design

With the business rules defined, an Entity-Relationship Diagram (ERD) was created (see docs/ERD Diagram.pdf). This diagram served as the blueprint for the database, mapping out entities like Employee, Project, Client, and Support_Ticket and the relationships between them.

The logical design phase involved normalizing this structure to reduce redundancy and improve data integrity. This led to the creation of junction tables (e.g., Project_Assignment) and polymorphic tables (e.g., Contact for both clients and employees).

3. Physical Implementation

The logical schema was translated into a physical database using SQL. Key implementation details include:

• Schema Organization: The database is organized into five distinct schemas (HR, Sales, Projects, Support, Inventory) to group related tables and enforce a clean separation of concerns.
• Table Creation: The script database/schemas/TechSolutionSchema.sql defines all tables, columns, data types, and primary/foreign key constraints.
• Data Population: A comprehensive set of sample data was created to simulate company growth over three years, allowing for realistic testing and querying.

4. Feature and Security Implementation

To make the database functional and secure, several additional features were implemented:

• SQL Functions: A suite of functions was developed to simplify common data retrieval tasks, such as fetching project details or employee assignments.
• Role-Based Access Control (RBAC): A security model was created using SQL Roles (IT_Support_Role, Software_Dev_Manager). These roles have granular permissions, ensuring users can only access the data relevant to their job function, with explicit DENY rules on sensitive information.

Project Structure

The project's files are organized as follows:

/
|-- database/
|   |-- schemas/        # Contains the main table creation script
|   |-- data/           # Contains all scripts to insert sample data
|   |-- functions/      # SQL functions for common queries
|   |-- roles/          # SQL role creation scripts
|   |-- security/       # Scripts for setting role permissions
|
|-- docs/               # Project documentation (Data Dictionary, ERD, etc.)
|
|-- README.md           # This file

How to Use

To recreate the database, execute the scripts in the following order:

1. Schema: Run the script in database/schemas/.
2. Data: Run all scripts in database/data/.
3. Functions: Run all scripts in database/functions/.
4. Roles & Security: Run the scripts in database/roles/ and database/security/.