Build a Relational Database for Employee Computer Tracking: A Step-by-Step SQL Project Guide

Introduction: Why Track Computers in a Database?

In today's tech-driven workplace, managing IT assets like laptops and desktops is crucial. Imagine a company like Wedgewood Pacific (WP) where employees use different computers for their daily tasks. Without a proper database, tracking who has which machine becomes chaotic. This tutorial walks you through a common database project: extending an existing employee database to include computer assignments. You'll learn to create tables, enforce referential integrity, write SQL queries, and visualize the schema using an E-R diagram. By the end, you'll have a practical skill set applicable to real-world inventory management systems.

Prerequisites: Setting Up MySQL

Before diving in, ensure you have MySQL Community Server 8.0 and MySQL Workbench installed. Create a folder C:/Documents/MySQLWorkbench/Schemas to store your SQL scripts. We'll work with the Wedgewood Pacific (WP) database schema as described in textbook Chapter 3.

Step 1: Create the WP Database and Tables

First, create the WP schema and set it as default. Then, run an SQL script to create the base tables: DEPARTMENT, EMPLOYEE, PROJECT, and ASSIGNMENT. For this tutorial, we focus on adding two new tables: COMPUTER and COMPUTER_ASSIGNMENT.

Creating the COMPUTER Table

The COMPUTER table stores details about each computer. Use the following SQL:

CREATE TABLE COMPUTER (
  SerialNumber CHAR(12) PRIMARY KEY,
  Make VARCHAR(20) NOT NULL,
  Model VARCHAR(30) NOT NULL,
  PurchaseDate DATE
);

Creating the COMPUTER_ASSIGNMENT Table

This table links computers to employees. It includes a foreign key referencing EMPLOYEE.EmployeeID and COMPUTER.SerialNumber:

CREATE TABLE COMPUTER_ASSIGNMENT (
  EmployeeID INT NOT NULL,
  SerialNumber CHAR(12) NOT NULL,
  AssignmentDate DATE NOT NULL,
  PRIMARY KEY (EmployeeID, SerialNumber),
  FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
  FOREIGN KEY (SerialNumber) REFERENCES COMPUTER(SerialNumber)
);

Referential integrity ensures that every assignment points to an existing employee and computer.

Step 2: Insert Sample Data

Populate the tables with realistic data. For example:

INSERT INTO COMPUTER VALUES
('SN1001', 'Dell', 'Latitude 5420', '2025-01-15'),
('SN1002', 'Apple', 'MacBook Pro 14', '2025-03-01');

INSERT INTO COMPUTER_ASSIGNMENT VALUES
(101, 'SN1001', '2025-02-01'),
(102, 'SN1002', '2025-03-15');

Adjust EmployeeID values to match your existing EMPLOYEE table.

Step 3: Write the Query to Track Computer Usage

The main assignment asks: Who is currently using which computer at WP? The result should be sorted by Department then employee LastName, showing SerialNumber, Make, Model, EmployeeID, LastName, FirstName, Department, OfficePhone.

Here's the SQL query using JOINs:

SELECT
  ca.SerialNumber,
  c.Make,
  c.Model,
  e.EmployeeID,
  e.LastName,
  e.FirstName,
  d.DepartmentName AS Department,
  e.OfficePhone
FROM
  COMPUTER_ASSIGNMENT ca
  JOIN COMPUTER c ON ca.SerialNumber = c.SerialNumber
  JOIN EMPLOYEE e ON ca.EmployeeID = e.EmployeeID
  JOIN DEPARTMENT d ON e.DepartmentID = d.DepartmentID
ORDER BY
  d.DepartmentName,
  e.LastName;

This query joins four tables to bring together employee details, computer info, and department names. Sorting by Department and LastName makes it easy to find users per department.

Step 4: Enhance the E-R Diagram

Using MySQL Workbench, create an E-R diagram that includes the existing tables plus COMPUTER and COMPUTER_ASSIGNMENT. Use the IE Crow's Foot notation. The relationships:

• EMPLOYEE to COMPUTER_ASSIGNMENT: one-to-many (an employee can have multiple assignments over time, but typically one current assignment).
• COMPUTER to COMPUTER_ASSIGNMENT: one-to-many (a computer can be assigned to different employees over time).

Ensure you include the recursive relationship for EMPLOYEE (as shown in Figure 5-17) if required.

Real-World Relevance: IT Asset Management

This project mirrors real-world IT asset tracking systems used by companies like Google or Microsoft. With the rise of remote work, knowing who has which device is critical for security and inventory. For example, during the 2026 FIFA World Cup, a company might loan laptops to employees covering the event; a database like this ensures every device is accounted for.

Common Pitfalls and Tips

• Double-check foreign key constraints: Ensure the referenced tables and columns exist.
• Use consistent data types: e.g., EmployeeID should match exactly between tables.
• Test queries incrementally: Start with a simple SELECT before adding JOINs.

Conclusion

You've successfully extended a database to track computer assignments, written a complex SQL query with sorting and joins, and designed an E-R diagram. These skills are foundational for database administration and backend development. Practice by adding more tables or queries to handle laptop returns or maintenance logs.