Day 4 Tables

Kedeisha November 23, 2023

Employees Table

Table Structure:
- employeeID (INT): A unique identifier for each employee.
- name (VARCHAR): The name of the employee.
- departmentID (INT): The ID of the department the employee belongs to.
- email (VARCHAR): The email address of the employee.
- hireDate (DATE): The date when the employee was hired.

CREATE TABLE employees (
  employeeID INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  departmentID INT,
  email VARCHAR(100),
  hireDate DATE
)
INSERT INTO employees (name, departmentID, email, hireDate) VALUES
('Alice Johnson', 1, 'alice.johnson@example.com', '2019-06-15'),
('Bob Smith', 2, 'bob.smith@example.com', '2018-07-22'),
('Charlie Davis', 1, 'charlie.davis@example.com', '2020-03-30');

Departments Table

Table Structure:
- departmentID (INT): A unique identifier for each department.
- departmentName (VARCHAR): The name of the department.

CREATE TABLE departments (
  departmentID INT AUTO_INCREMENT PRIMARY KEY,
  departmentName VARCHAR(100)
)

INSERT INTO departments (departmentName) VALUES
('IT'),
('HR'),
('Marketing');

Projects Table

Table Structure:
- projectID (INT): A unique identifier for each project.
- projectName (VARCHAR): The name of the project.
- startDate (DATE): The start date of the project.
- endDate (DATE): The end date of the project.

CREATE TABLE projects (
  projectID INT AUTO_INCREMENT PRIMARY KEY,
  projectName VARCHAR(100),
  startDate DATE,
  endDate DATE
)

INSERT INTO projects (projectName, startDate, endDate) VALUES
('Website Redesign', '2021-01-01', '2021-06-30'),
('Product Launch', '2021-03-01', '2021-09-30');

Employee_Projects Table (Many-to-Many Relationship)

Table Structure:
- employeeID (INT): The ID of the employee (foreign key).
- projectID (INT): The ID of the project (foreign key).

CREATE TABLE employee_projects (
  employeeID INT,
  projectID INT,
  FOREIGN KEY (employeeID) REFERENCES employees(employeeID),
  FOREIGN KEY (projectID) REFERENCES projects(projectID)
)
INSERT INTO employee_projects (employeeID, projectID) VALUES
(1, 1),
(2, 2),
(3, 1);