← All projectsProject Eli →
Academic case study · ITS3102

Database Systems

Relational database design, SQL queries, and a dry-cleaning business database.

Adapted for web presentation from the original course portfolio submission. Content, code, tables, and available diagrams are retained; the original academic documents remain unchanged.

Professional Portfolio Summary

ITS3102 Database Systems – Week 5 Project

This assignment demonstrates my ability to create and work with relational databases using SQL. The project included a hands-on DryCleaning database and a final TMI Tabletop database. I created tables for customers, invoices, products, publishers, orders, and order items while applying primary keys, foreign keys, data types, constraints, and relationships. I also inserted records and wrote queries using filtering, sorting, aggregate functions, concatenation, subqueries, joins, and EXISTS conditions. The objective was to show that I could design database structures that organize information correctly and retrieve useful data from them. This piece is relevant to my professional portfolio because databases support nearly every modern IT environment. For an IT support or service desk position, understanding how applications store and retrieve information helps me troubleshoot problems beyond the user interface. It also demonstrates that I can read technical syntax, identify relationships between systems and data, and verify whether information is being processed correctly. These skills are useful when investigating application errors, escalating database-related incidents, documenting technical issues, and communicating with administrators or developers. The project shows both practical SQL experience and the analytical troubleshooting skills required in technical support.

Assignment Statement

The original assignment instructions were not retained with the available Week 5 submission. The original course work is reproduced below without replacing or inventing the missing assignment statement.

Original Week 5 Work

SU_ITS3102_W5_Project_Fleming_J

Collated Hands-on Lab and Final Course Project

Part I—Hands-on Lab (DryCleaning Database)

CREATE DATABASE IF NOT EXISTS DryCleaning;
USE DryCleaning;
CREATE TABLE IF NOT EXISTS Customers ( CustomerNumber INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(25) NOT NULL, LastName VARCHAR(25) NOT NULL, Phone VARCHAR(12) NOT NULL, Email VARCHAR(100) );
CREATE TABLE IF NOT EXISTS Invoice ( InvoiceNumber INT PRIMARY KEY, CustomerNumber INT NOT NULL, DateIn DATETIME NOT NULL, DateOut DATETIME, TotalAmount DECIMAL(10,2), FOREIGN KEY (CustomerNumber) REFERENCES Customers(CustomerNumber) ON UPDATE CASCADE ON DELETE RESTRICT );
CREATE TABLE IF NOT EXISTS Invoice_Item ( ItemNumber INT AUTO_INCREMENT PRIMARY KEY, InvoiceNumber INT NOT NULL, Item VARCHAR(50) NOT NULL, Quantity INT NOT NULL, UnitPrice DECIMAL(10,2) NOT NULL, FOREIGN KEY (InvoiceNumber) REFERENCES Invoice(InvoiceNumber) ON UPDATE CASCADE ON DELETE CASCADE );
INSERT INTO Invoice (InvoiceNumber, CustomerNumber, DateIn, DateOut, TotalAmount) VALUES (1001, 1, '2025-10-10 09:15:00', '2025-10-12 16:30:00', 78.50), (1002, 2, '2025-10-11 10:05:00', NULL, 45.00), (1003, 1, '2025-10-13 14:20:00', '2025-10-14 11:00:00', 129.99);
INSERT INTO Invoice_Item (InvoiceNumber, Item, Quantity, UnitPrice) VALUES (1001, 'Dress Shirt', 2, 12.00), (1001, 'Jacket', 1, 54.50), (1002, 'Pants', 1, 45.00), (1003, 'Dress Shirt', 3, 15.00);

01CustomerPhone-LastName

SELECT Phone, LastName FROM Customers;

02NikkiPhoneLastName

SELECT Phone, LastName FROM Customers WHERE FirstName = 'Nikki';

03PhoneWith?23

SELECT Phone, FirstName, LastName FROM Customers WHERE Phone LIKE '_23%';

04AverageTotalAmount

SELECT MAX(TotalAmount) AS MaxTotalAmount, MIN(TotalAmount) AS MinTotalAmount FROM Invoice;

05CustomersTotalAmountGreaterThan100

SELECT FirstName, LastName FROM Customers WHERE CustomerNumber IN (SELECT CustomerNumber FROM Invoice WHERE TotalAmount > 100.00) ORDER BY LastName ASC, FirstName DESC;

06CustomerLastFirstNamesConcantenated

SELECT CONCAT(LastName, ', ', FirstName) AS FullName FROM Customers;

07FirstLastNamesDressShirtSortedLastName

SELECT FirstName, LastName FROM Customers WHERE CustomerNumber IN (SELECT i.CustomerNumber FROM Invoice i WHERE i.InvoiceNumber IN (SELECT InvoiceNumber FROM Invoice_Item WHERE Item = 'Dress Shirt')) ORDER BY LastName ASC, FirstName ASC;

08FirstLastNamesDressShirtSortedFirstNameDescending

SELECT FirstName, LastName FROM Customers WHERE CustomerNumber IN (SELECT i.CustomerNumber FROM Invoice i WHERE i.InvoiceNumber IN (SELECT InvoiceNumber FROM Invoice_Item WHERE Item = 'Dress Shirt')) ORDER BY FirstName DESC, LastName ASC;

09FirstLastNamesDressShirtSortedLastNameWithTotal

SELECT c.FirstName, c.LastName, i.TotalAmount FROM Customers c JOIN Invoice i ON i.CustomerNumber = c.CustomerNumber WHERE EXISTS (SELECT 1 FROM Invoice_Item ii WHERE ii.InvoiceNumber = i.InvoiceNumber AND ii.Item = 'Dress Shirt') ORDER BY c.LastName ASC, c.FirstName ASC;

10FirstLastNamesDressShirtSortedFirstNameDescending

SELECT c.FirstName, c.LastName, i.TotalAmount FROM Customers c JOIN Invoice i ON i.CustomerNumber = c.CustomerNumber WHERE EXISTS (SELECT 1 FROM Invoice_Item ii WHERE ii.InvoiceNumber = i.InvoiceNumber AND ii.Item = 'Dress Shirt') ORDER BY c.FirstName DESC, c.LastName ASC;

11AverageTotalAmount

SELECT AVG(TotalAmount) AS AverageTotalAmount FROM Invoice;

Part II—Final Course Project (TMI_Tabletop Database)

CREATE DATABASE IF NOT EXISTS TMI_Tabletop_Final;

USE TMI_Tabletop_Final;

CREATE TABLE IF NOT EXISTS Categories ( CategoryID INT AUTO_INCREMENT PRIMARY KEY, CategoryName VARCHAR(50) NOT NULL );
CREATE TABLE IF NOT EXISTS Publishers ( PublisherID INT AUTO_INCREMENT PRIMARY KEY, PublisherName VARCHAR(100) NOT NULL, Contact VARCHAR(100), Phone VARCHAR(20) );
CREATE TABLE IF NOT EXISTS Products ( ProductID INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(100) NOT NULL, CategoryID INT, PublisherID INT, SKU VARCHAR(40) UNIQUE, Price DECIMAL(10,2) NOT NULL, FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) ON UPDATE CASCADE ON DELETE SET NULL, FOREIGN KEY (PublisherID) REFERENCES Publishers(PublisherID) ON UPDATE CASCADE ON DELETE SET NULL );
CREATE TABLE IF NOT EXISTS Customers ( CustomerID INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE, State VARCHAR(50) );
CREATE TABLE IF NOT EXISTS Orders ( OrderID INT AUTO_INCREMENT PRIMARY KEY, CustomerID INT NOT NULL, OrderDate DATE NOT NULL, TotalAmount DECIMAL(10,2) NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON UPDATE CASCADE ON DELETE RESTRICT );
CREATE TABLE IF NOT EXISTS OrderItems ( OrderItemID INT AUTO_INCREMENT PRIMARY KEY, OrderID INT NOT NULL, ProductID INT NOT NULL, Qty INT NOT NULL, UnitPrice DECIMAL(10,2) NOT NULL, FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON UPDATE CASCADE ON DELETE RESTRICT );
INSERT INTO Categories (CategoryName) VALUES ('Accessories'), ('Miniatures'), ('Books'), ('Playmats');
INSERT INTO Products (Name, CategoryID, PublisherID, SKU, Price) VALUES ('Polyhedral Dice Set (7)', 1, 1, 'DICE-7SET-NEB', 12.99), ('Miniature: Elf Ranger', 2, 3, 'MINI-ELF-RANGER', 7.49), ('Core Rulebook (5e Compatible)', 3, 2, 'BOOK-CORE-5E', 39.95), ('Adventure Module: Crimson Carnival', 3, 2, 'BOOK-ADV-CRIMSON', 24.99), ('Playmat: Galaxy Grid', 4, 1, 'MAT-GALAXY', 19.99);
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES (1, '2025-09-28', 52.94), (2, '2025-09-29', 44.98);
INSERT INTO OrderItems (OrderID, ProductID, Qty, UnitPrice) VALUES (1, 1, 2, 12.99), (1, 3, 1, 39.95), (2, 2, 1, 7.49), (2, 5, 1, 19.99), (2, 1, 1, 12.99);
← All projectsProject Eli →