sqladvanced40 minutes

Design and Query an Employee Shift Scheduling System

Create a normalized SQL data model and implement complex queries to manage and analyze employee shift schedules for a medium-sized company.

Challenge prompt

You have been tasked with designing a relational database schema to manage employee shift schedules for a company. The system should track employees, departments, shifts, and the assignments of employees to shifts across multiple days. After designing the schema, write SQL queries to fetch detailed scheduling reports. Requirements: 1. Design tables to represent employees, departments, shifts (with start and end times), and shift assignments (which employee works which shift on which day). 2. The schema should be normalized to at least 3NF to avoid redundancy. 3. Populate the tables with sample data for at least 5 employees, 2 departments, 3 different shifts per day, and shift assignments covering one week. 4. Write a query to list each employee's total scheduled hours per week. 5. Write a query to find employees who have overlapping shifts in the same day (which should not happen). 6. Write a query to output a weekly schedule grid that shows days as columns and employees as rows with shift names filled in. Submit the SQL statements for table creation, sample data insertion, and the three requested queries.

Guidance

• Focus on designing separate tables for Employees, Departments, Shifts, and ShiftAssignments, with appropriate foreign keys.
• Make sure the shift time fields use an efficient and consistent time representation (e.g., TIME or DATETIME data types).
• Approach the queries by considering joins and aggregate functions, and use window functions or self-joins for overlap detection.

Hints

• Use composite unique constraints on ShiftAssignments to avoid duplicate assignments for the same employee and shift on the same day.
• To detect overlapping shifts, consider comparing shift time intervals for the same employee on the same day using self-joins.
• For the weekly schedule grid, pivot data by using conditional aggregation or CASE statements.

Starter code

-- Create tables
CREATE TABLE Departments (
  dept_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  dept_id INT NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

CREATE TABLE Shifts (
  shift_id INT PRIMARY KEY,
  shift_name VARCHAR(50),
  start_time TIME NOT NULL,
  end_time TIME NOT NULL
);

-- Note: You will need to create a ShiftAssignments table and write the requested queries based on your schema design.

Expected output

-- Example output for total scheduled hours per employee per week | emp_id | name | total_hours | |--------|--------------|-------------| | 1 | Alice Smith | 40 | | 2 | Bob Johnson | 35 | | 3 | Carol Lee | 30 | -- For overlap detection, output should list employees and days where overlap exists | emp_id | name | date | overlapping_shifts | |--------|--------------|------------|--------------------| | 3 | Carol Lee | 2024-06-15 | Morning, Afternoon | -- Weekly schedule grid with employees as rows and days as columns filled with shift names | emp_id | name | Mon | Tue | Wed | Thu | Fri | Sat | Sun | |--------|-------------|-----------|-----------|-----------|-----------|-----------|-----------|-----------| | 1 | Alice Smith | Morning | Morning | Morning | Morning | Morning | Off | Off |

Core concepts

Database normalizationComplex SQL queriesTime interval overlap detectionPivoting data in SQL

Challenge a Friend

Send this duel to someone else and see if they can solve it.