Time Sheet Database course project

For the course project, I will be referring to a timesheet database that can be used to track time and bill hours to a client or supervisor. Because many of you may be consultants or contractors, I thought it would be fun to build a real-world database that enables you to track your time and bill your supervisor or clients accurately.

Here's a real-world timesheet database design for your SQL Server 2022 course project, tailored to:

• Track time for consultants and contractors
• Accurately bill supervisors or clients

🗂️ High-Level Requirements

1. Track individual work sessions
   • date
   • hours
   • task
   • etc.
2. Distinguish between
   • contractors
   • consultants
3. Associate work sessions with
   • projects
   • clients
   • supervisors
4. Generate billing reports
   • total hours
   • amount owed
5. Handle
   • hourly rates
   • billing status

1. Employees

   CREATE TABLE Employees (
       EmployeeID INT IDENTITY PRIMARY KEY,
       FirstName NVARCHAR(50),
       LastName NVARCHAR(50),
       RoleType VARCHAR(20) CHECK (RoleType IN ('Consultant', 'Contractor')),
       Email NVARCHAR(100),
       HourlyRate DECIMAL(10,2),
       IsActive BIT DEFAULT 1
   );
           

2. Clients

   CREATE TABLE Clients (
       ClientID INT IDENTITY PRIMARY KEY,
       ClientName NVARCHAR(100),
       ContactPerson NVARCHAR(100),
       Email NVARCHAR(100),
       Phone NVARCHAR(20)
   );
           

3. Projects

   CREATE TABLE Projects (
       ProjectID INT IDENTITY PRIMARY KEY,
       ProjectName NVARCHAR(100),
       ClientID INT FOREIGN KEY REFERENCES Clients(ClientID),
       StartDate DATE,
       EndDate DATE NULL
   );
           

4. TimeEntries

   CREATE TABLE TimeEntries (
       TimeEntryID INT IDENTITY PRIMARY KEY,
       EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID),
       ProjectID INT FOREIGN KEY REFERENCES Projects(ProjectID),
       WorkDate DATE,
       HoursWorked DECIMAL(5,2),
       WorkDescription NVARCHAR(255),
       IsBilled BIT DEFAULT 0
   );
           

5. Invoices

   CREATE TABLE Invoices (
       InvoiceID INT IDENTITY PRIMARY KEY,
       ClientID INT FOREIGN KEY REFERENCES Clients(ClientID),
       InvoiceDate DATE,
       TotalAmount DECIMAL(10,2),
       IsPaid BIT DEFAULT 0
   );
           

6. InvoiceDetails

   CREATE TABLE InvoiceDetails (
       InvoiceDetailID INT IDENTITY PRIMARY KEY,
       InvoiceID INT FOREIGN KEY REFERENCES Invoices(InvoiceID),
       TimeEntryID INT FOREIGN KEY REFERENCES TimeEntries(TimeEntryID),
       BilledAmount AS (HoursWorked * Rate) PERSISTED,
       Rate DECIMAL(10,2)
   );
           

View: `vw_BillingSummary`

CREATE VIEW vw_BillingSummary AS
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    c.ClientName,
    p.ProjectName,
    SUM(t.HoursWorked) AS TotalHours,
    SUM(t.HoursWorked * e.HourlyRate) AS TotalBilled
FROM TimeEntries t
JOIN Employees e ON t.EmployeeID = e.EmployeeID
JOIN Projects p ON t.ProjectID = p.ProjectID
JOIN Clients c ON p.ClientID = c.ClientID
WHERE t.IsBilled = 0
GROUP BY e.EmployeeID, e.FirstName, e.LastName, c.ClientName, p.ProjectName;

SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS). SQL, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements.
What can SQL do?

1. SQL can execute queries against a database
2. SQL can retrieve data from a database
3. SQL can insert records in a database
4. SQL can update records in a database
5. SQL can delete records from a database