Designing a Normalised Manufacturing Database in SQL Server

Coursework for LSBU's Big Data & Databases module: design and build a relational database for a fictional manufacturer, LSBU Manufacturing Ltd, then actually query it in anger.

Modelling

I modelled the domain across nine entities — Department, Employee, Manager, Machine, Maintenance, Production, Operator, ShiftAssignment and Product — starting from an ER diagram in Chen notation. From there I derived the functional dependencies and normalised the schema to 3NF/BCNF, then implemented it in Microsoft SQL Server with proper constraints and bulk-loaded sample data.

Querying

The point of a clean schema is what you can ask of it. I wrote five parameterised T-SQL queries covering CTEs, stored procedures, triggers and window functions:

• shift-frequency ranking across operators
• maintenance-threshold detection, with indexing to keep it fast
• a self-join trigger that validates manager salaries against their reports
• rolling department-salary aggregations with window functions

Then a Power BI dashboard on top, turning the query output into something an operations team could read at a glance.

What I took from it

Normalisation isn't academic tidiness — it's what makes the interesting queries possible without fighting the schema. Getting to BCNF first meant the triggers and window functions were straightforward to write, rather than contorted around redundant data.