Database Creation and Updates

Introduction: Setting Up a Database Environment with SQL Scripts

I have prepared a comprehensive set of SQL scripts to assist you in creating a database, defining schemas, and setting up the data environment. These scripts are tailored to meet the requirements of given scenario, ensuring a seamless and efficient setup process.

Key Aspects of the SQL Scripts:

1. Database Creation:

   • The initial step involves creating a new database, providing a foundational structure for storing and organizing your data.

2. Table Definition:

   • We define three main tables within the database: 'departments', 'employees', and 'projects'. Each table is designed to capture specific aspects of your organizational structure and project management needs.

3. Data Population:

   • The scripts include commands to populate the tables with sample data, reflecting a realistic scenario. This data will serve as a basis for testing and analysis within your database environment.

4. Data Relationships:

   • Relationships between tables are established using foreign key constraints, ensuring data integrity and coherence across different entities.

5. Updates and Modifications:

   • Additionally, the scripts include updates to set department manager IDs based on the provided employee data, aligning with the hierarchical structure of your organization.

Step-by-Step Execution:

To ensure a smooth execution of the SQL scripts, please follow the provided step-by-step process:

1. Begin by executing the script to create the database.

2. Proceed to run the scripts for table creation, including 'departments', 'employees', and 'projects'.

3. Populate the tables with sample data using the provided insert commands.

4. Finally, execute the updates to set department manager IDs accordingly.

These SQL scripts have been meticulously crafted to streamline the setup process, saving you time and effort while ensuring the integrity and coherence of your database environment.

Should you require any further assistance or clarification, please do not hesitate to reach out. I remain at your disposal to provide any additional support you may need throughout this process.

Step-by-Step Process:

1. Create Database:

CREATE DATABASE YourDatabaseName;
GO

2. Use the Newly Created Database:

USE YourDatabaseName;
GO

3. Create 'departments' Table:

CREATE TABLE departments (
    id INT IDENTITY PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

4. Create 'employees' Table:

CREATE TABLE employees (
    id INT IDENTITY PRIMARY KEY,
    name VARCHAR(50),
    hire_date DATE,
    job_title VARCHAR(50),
    department_id INT REFERENCES departments(id)
);

5. Create 'projects' Table:

CREATE TABLE projects (
    id INT IDENTITY PRIMARY KEY,
    name VARCHAR(50),
    start_date DATE,
    end_date DATE,
    department_id INT REFERENCES departments(id)
);

6. Insert Data into 'departments' Table:

INSERT INTO departments (name, manager_id)
VALUES 
    ('HR', 1),
    ('IT', 2),
    ('Sales', 3);

7. Insert Data into 'employees' Table:

INSERT INTO employees (name, hire_date, job_title, department_id)
VALUES 
    ('John Doe', '2018-06-20', 'HR Manager', 1),
    ('Jane Smith', '2019-07-15', 'IT Manager', 2),
    ('Alice Johnson', '2020-01-10', 'Sales Manager', 3),
    ('Bob Miller', '2021-04-30', 'HR Associate', 1),
    ('Charlie Brown', '2022-10-01', 'IT Associate', 2),
    ('Dave Davis', '2023-03-15', 'Sales Associate', 3);

8. Insert Data into 'projects' Table:

INSERT INTO projects (name, start_date, end_date, department_id)
VALUES 
    ('HR Project 1', '2023-01-01', '2023-06-30', 1),
    ('IT Project 1', '2023-02-01', '2023-07-31', 2),
    ('Sales Project 1', '2023-03-01', '2023-08-31', 3);

9. Update Department Manager IDs:

UPDATE departments 
SET manager_id = (SELECT id FROM employees WHERE name = 'John Doe') 
WHERE name = 'HR';

UPDATE departments 
SET manager_id = (SELECT id FROM employees WHERE name = 'Jane Smith') 
WHERE name = 'IT';

UPDATE departments 
SET manager_id = (SELECT id FROM employees WHERE name = 'Alice Johnson') 
WHERE name = 'Sales';