For Data & Information Management, the brief was to design a relational database for an organisation and build it in Oracle SQL. This one models a whole company from the CEO down: every executive, department, project, product and client.
22 entity tables
27 link tables
83 foreign keys
640 rows of test data
How the company is organised
Everyone starts as a row in Employee. A CEO, CFO, COO or CTO is an employee with extra details in their own table, and each department hangs off the executive who runs it.
From Employee down to the departments. Swipe sideways to see all of it.
Projects tie it all together
Project is the busiest table: it points at eleven others. And whenever two things relate many-to-many, like which client deals with which department, the relationship gets its own link table. There are 27 of those, such as CTO Project and Finance Sale.
Project and the tables around it. Swipe sideways to see all of it.
Try it yourself
This is the real schema and data, rebuilt in SQLite and running in your browser. Pick one of the original queries, open a table, or write your own and press Run (or Ctrl+Enter). Nothing leaves this page, and reloading puts everything back.
Scroll here to load the database.
Browse a table
SELECT "E_department" AS department, count(*) AS people
FROM "Employee"
GROUP BY 1
ORDER BY 2 DESC;
SELECT CTO."CTO_specialization", IT."IT_service_name"
FROM "CTO" CTO, "IT" IT
WHERE CTO."CTO_ID" = IT."CTO_ID";
SELECT CTO."CTO_experience", IT."IT_availability_percentage", proj."Pro_budget", serv."S_duration"
FROM "CTO" CTO
INNER JOIN "IT" IT ON IT."CTO_ID" = CTO."CTO_ID"
INNER JOIN "CTO Project" CTProj ON CTProj."CTO_ID" = CTO."CTO_ID"
INNER JOIN "Project" proj ON proj."Pro_ID" = CTProj."Pro_ID"
INNER JOIN "Service" serv ON serv."Pro_ID" = proj."Pro_ID"
WHERE "CTO_specialization" = 'Information Technology' AND "IT_maintenance_status" = 'Complete'
GROUP BY CTO."CTO_experience", IT."IT_availability_percentage", serv."S_duration", proj."Pro_budget"
ORDER BY "Pro_budget";
SELECT emp."E_name", emp."E_age", CFO."CFO_skills", CFO."CFO_total_current_assetRM", fin."F_profit_percentage", fin."F_financing_period", sal."Sal_payment_method", sal."Sal_bonusRM", sale."Sale_total_saleRM", sale."Sale_targetRM", inv."Inv_item_name", inv."Inv_item_availability_units"
FROM "Employee" emp
INNER JOIN "CFO" CFO ON CFO."E_ID" = emp."E_ID"
INNER JOIN "Finance" fin ON fin."CFO_ID" = CFO."CFO_ID"
INNER JOIN "Salary" sal ON sal."F_ID" = fin."F_ID"
INNER JOIN "Finance Sale" fisal ON fisal."F_ID" = fin."F_ID"
INNER JOIN "Sale" sale ON sale."Sale_ID" = fisal."Sale_ID"
INNER JOIN "Inventory" inv ON inv."Sale_ID" = sale."Sale_ID"
WHERE "F_financing_period" = '5 years' AND "Inv_damaged_items_count" < 9 AND "E_age" > 25;
SELECT emp."E_name", CFO."CFO_specialization", fin."F_total_expenseRM", proj."Pro_due_date", prod."P_total_available", inv."Inv_location", log."L_total_weight_kg", cli."Client_name"
FROM "Employee" emp
INNER JOIN "CFO" CFO ON CFO."E_ID" = emp."E_ID"
INNER JOIN "Finance" fin ON fin."CFO_ID" = CFO."CFO_ID"
INNER JOIN "Project" proj ON proj."F_ID" = fin."F_ID"
INNER JOIN "Product" prod ON prod."Pro_ID" = proj."Pro_ID"
INNER JOIN "Inventory" inv ON inv."Inv_ID" = prod."Inv_ID"
INNER JOIN "Logistic" log ON log."L_ID" = prod."L_ID"
INNER JOIN "Client" cli ON cli."Client_ID" = proj."Client_ID"
WHERE "Client_marital_status" = 'Single' AND "L_cost_RM" < 5500 AND "F_financing_period" = '5 years' AND "E_gender" = 'Female'
ORDER BY "P_total_available" DESC;
DROP VIEW IF EXISTS "COO project";
CREATE VIEW "COO project"
AS SELECT emp."E_ID", COO."COO_ID", proj."Pro_ID"
FROM "Employee" emp
JOIN "COO" COO ON COO."E_ID" = emp."E_ID"
JOIN "Project" proj ON proj."COO_ID" = COO."COO_ID"
WHERE "COO_specialization" = 'PMO';
SELECT * FROM "COO project";
Bringing it back
The original was written for Oracle, so rebuilding it meant turning Oracle's INSERT ALL … SELECT * FROM dual blocks into plain inserts and its TO_DATE calls into ordinary dates. All 640 rows loaded, and every original join returns the same kind of answer it did then. The people in it were always made up (there's a John Wick in Finance), but I swapped the phone numbers, emails and house numbers for obviously fake ones anyway.
What it taught me
Keys first, then everything else. Once every table knew exactly what it pointed at, an eight-table join was just following the arrows.
Test data is part of the design. A query only proves something if there are rows that pass the filter and rows that don't.
Many-to-many deserves its own table. Link tables felt like overkill at first, and then they were the reason every question had an answer.
