Database Schema Design: Employee and Department Tables

This challenge focuses on the fundamental SQL operation of creating tables. You will be tasked with designing and implementing the SQL CREATE TABLE statements to represent two related entities: Employees and Departments. This is a core skill for anyone working with relational databases, enabling you to structure and store data effectively.

Problem Description

Your task is to write the SQL CREATE TABLE statements for two tables: Departments and Employees. These tables will store information about company departments and the employees within them.

Key Requirements

1. Departments Table:

   • Must have a unique identifier for each department.
   • Must store the name of the department.
   • The department identifier should be an integer and automatically increment.

2. Employees Table:

   • Must have a unique identifier for each employee.
   • Must store the employee's first name, last name, and email address.
   • Must include a way to link each employee to their respective department. This link should be a foreign key referencing the Departments table.
   • The employee identifier should be an integer and automatically increment.
   • The email address should be unique across all employees.

Expected Behavior

When the CREATE TABLE statements are executed, the database should successfully create two tables with the specified schema and constraints.

Important Edge Cases to Consider

• Data Type Selection: Choose appropriate data types for each column to ensure data integrity and efficiency.
• Primary Keys: Identify and implement primary keys correctly for both tables.
• Foreign Keys: Ensure the foreign key relationship between Employees and Departments is correctly defined, including referential integrity.
• Uniqueness: Implement uniqueness constraints where required.

Examples

Example 1: Basic Table Creation (Departments)

• Input: A requirement to create a table for storing department information. The table should have a unique integer ID and a department name. The ID should auto-generate.

• Output (Pseudocode):

  CREATE TABLE Departments (
      department_id INT AUTO_INCREMENT PRIMARY KEY,
      department_name VARCHAR(255) NOT NULL
  );
  

• Explanation: This statement creates a Departments table with an auto-incrementing integer primary key department_id and a non-null department_name field.

Example 2: Creating a Related Table (Employees) with a Foreign Key

• Input: A requirement to create a table for storing employee information. Each employee needs a unique ID, first name, last name, email, and a link to their department. The employee ID should auto-generate, and email must be unique.

• Output (Pseudocode):

  CREATE TABLE Employees (
      employee_id INT AUTO_INCREMENT PRIMARY KEY,
      first_name VARCHAR(255) NOT NULL,
      last_name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      department_id INT,
      FOREIGN KEY (department_id) REFERENCES Departments(department_id)
  );
  

• Explanation: This statement creates an Employees table with an auto-incrementing primary key employee_id, fields for name and email (with a unique constraint on email), and a department_id column that references the department_id in the Departments table.

Constraints

• department_id and employee_id will be integers.
• department_name, first_name, last_name, and email will be strings.
• String lengths for names and email will not exceed 255 characters.
• The CREATE TABLE statements should be standard SQL, compatible with most relational database systems.

Notes

• Consider what happens if an employee is assigned to a department that doesn't exist. Your foreign key definition will handle this.
• Think about the order in which you might create the tables.
• The pseudocode uses AUTO_INCREMENT as a common convention, but the specific keyword might vary slightly between SQL dialects (e.g., SERIAL in PostgreSQL). Focus on the concept of an auto-generating primary key.
• VARCHAR(255) is a common choice for variable-length strings, but you might encounter other string types.
• NOT NULL ensures a value is always provided for that column.
• UNIQUE ensures that all values in a column are distinct.