Two production-style SQL Server projects built end to end in T-SQL: a normalised online-banking transactional database, and an analytics workload over real NHS prescribing data. Together they span the full database lifecycle, from relational design and normalisation, through programmable objects (stored procedures, functions, views, triggers), to analytical querying and business reporting.
Stack: Microsoft SQL Server, T-SQL, SQL Server Management Studio (SSMS)
For the business problem behind each project and how the solution addresses it, see PROJECT_BRIEF.md.
.
├── data/ # Source CSV files for the prescribing project (NHS Bolton extract)
│ ├── Medical_Practice.csv
│ ├── Drugs.csv
│ ├── Prescriptions.csv
│ └── Prescription_Summary.csv
├── online-bank-db/ # OLTP design, implementation & management
│ ├── sql/online_bank_db.sql # Full build script: schema, data, objects, tests
│ ├── sql/review_tests.sql # Seven self-checking rule tests, run inside a rolled-back transaction
│ ├── sql/hardening.sql # Fixes for every defect the review tests expose
│ ├── diagrams/erd.png # Entity-relationship diagram
│ └── backup/OnlineBankDB.bak # SQL Server backup (restore to reproduce)
├── prescriptions-db/ # Analytics on NHS prescribing data
│ ├── sql/prescriptions_db.sql
│ ├── diagrams/schema.png
│ └── backup/PrescriptionsDB.bak
├── report/ # Full technical report (design rationale, results)
│ ├── full_report.pdf
│ └── full_report.docx
├── project_brief.md
└── README.md
A transactional database for a retail online bank, designed from a business requirements specification and normalised to Third Normal Form (3NF). The schema manages customers, accounts, transactions, overdue fees, and repayments, and is built to keep balances and transaction records accurate under high daily transaction volume.
Design
- 9 related tables (
Customer,Address,Account,AccountType,CustomerAccount,Transaction,OverdueFee,Repayment,ArchivedCustomer). Addressseparated fromCustomerto remove a transitive dependency;AccountTypeextracted into a lookup table so new account types can be added without schema change.- Tables created in dependency order so every foreign key references an existing object.
- Integrity enforced through
PRIMARY KEY,FOREIGN KEY,UNIQUE, andCHECKconstraints (email-format validation, minimum-age rule, restricted account-type values). - Credentials stored as a salted hash (
BINARY(64)with aUNIQUEIDENTIFIERsalt) rather than plain text.
Programmable objects
- Stored procedures: search accounts by name (most recent first), insert a new customer with address handling, update an existing customer.
- User-defined function: returns loan and credit payments due within 5 days.
- View: a snapshot of all transactions carrying overdue fees.
- Triggers: auto-close a loan or credit-card account when its final payment completes; archive a customer record on deactivation while retaining it for marketing.
- A full test suite exercising every object, with before/after table states.
Operational guidance (in the report): data integrity and concurrency, database security, and backup and recovery, each applied to this specific banking scenario.
Review and hardening (October 2026)
I reviewed the design against the rules a bank depends on and wrote them as seven self-checking tests in review_tests.sql. The tests run inside one transaction that is always rolled back, so they never change the data. On the original build all seven fail. hardening.sql fixes each one, and after it all seven pass on SQL Server 2025.
| # | Rule | Original behaviour | Fix |
|---|---|---|---|
| 1 | A multi-row update closes every account whose last payment completed | Only one account closed, because the trigger copied a single AccountID into a variable |
Set-based trigger over inserted and deleted |
| 2 | An account with unpaid overdue fees stays open | Closed with fees outstanding | The close trigger also checks for unpaid fees |
| 3 | Each flat keeps its own address | A new customer in Flat 3 was linked to Flat 2's row, because uniqueness ignored AddressLine2 |
Unique on line 1, line 2 and postcode, and both procedures match on line 2 |
| 4 | Only an account holder can transact on the account | Any customer could be paired with any account | Composite foreign key to CustomerAccount |
| 5 | A completed transaction moves the balance | Balance never changed |
Trigger applies each transaction once, when it completes |
| 6 | A repayment updates the fee's total repaid | TotalRepaid had to be updated by hand |
Trigger recalculates it from Repayment |
| 7 | An overpayment is rejected | Accepted | Follows from 6, because the existing CHECK (TotalRepaid <= TotalOwed) now sees every repayment |
hardening.sql also indexes the five foreign-key columns that every join and trigger goes through, since SQL Server doesn't index them on its own.
Querying and analysis over a real NHS prescribing dataset for the Bolton region, framed around the needs of a pharmaceutical company that wants to understand which medications are prescribed, by which organisations, and in what quantities.
The data/ folder holds the four source CSVs, an extract of prescribing data published by the NHS in England, narrowed to Bolton:
| File | Records | Description |
|---|---|---|
Medical_Practice.csv |
60 | Practice names and addresses; PRACTICE_CODE is the unique identifier. |
Drugs.csv |
6,148 | Drug details including chemical substance and product description, categorised by BNF_CHAPTER_PLUS_CODE (20 chapters); BNF_CODE is the unique identifier. |
Prescriptions.csv |
116,815 | One row per prescription line, linked to a practice (PRACTICE_CODE) and a drug (BNF_CODE), with ITEMS, QUANTITY, and ACTUAL_COST. |
Prescription_Summary.csv |
116,815 | Despite the name, not a summary. It holds the same rows as Prescriptions.csv in the same order, with REPORT_MONTH added. |
Every prescription references a practice and a drug that exist, so no rows are lost to the foreign keys. Two things in the data need care. The month labels don't behave like real months, because 9,303 rows repeat a practice, drug and month, and the flu vaccine's busiest month is April. And the import stored costs as 4-byte floats, which moves the year's £4.54 million total by under a penny but is still a reason to keep money in DECIMAL.
- CSV import, then primary and foreign keys added across
Medical_Practice,Drugs,Prescriptions, andPrescription_Summary. - Filtering and aggregation: drugs by form, rounded total quantity per prescription, and cost statistics (count, average, min, max) per BNF chapter.
- Window functions (
ROW_NUMBER) to surface the most-prescribed chemical substance per month. The month comes fromPrescription_Summary, joined row for row onSUMMARY_ID - 1 = PRESCRIPTION_CODE. A join onPRACTICE_CODEalone pairs every prescription with every summary row from its practice, 315 million rows, and inflates each month's top count between 2,267 and 2,966 times. - Correlated subqueries to find each practice's single most expensive prescription (above a £4,000 threshold).
- Outlier detection using z-scores (cost more than three standard deviations above a drug's mean) to flag potentially erroneous prescriptions.
- Month-on-month trend analysis using self-joins to compute volume and percentage change per practice.
Each analytical query is paired with a business interpretation: specialist-clinic targeting, bulk-purchasing opportunities, anomaly investigation, and supply planning.
Option A: restore from backup (fastest)
- In SSMS: Databases → Restore Database → Device, then select the
.bakfile from the relevantbackup/folder. - Restore as
OnlineBankDBorPrescriptionsDB.
Option B: run the scripts
- Open the
.sqlfile from the project'ssql/folder in SSMS. - Execute top to bottom. The script creates the database, schema, sample data, and all objects.
- For Project 2, import the four CSVs from
data/via the Import Flat File Wizard before running the constraint and query sections. Column names are kept exactly as they appear in the source files so the scripts re-run cleanly. - For Project 1, run
review_tests.sqlto see the seven rules fail, thenhardening.sql, thenreview_tests.sqlagain to see them pass.
The scripts create filtered indexes, which need QUOTED_IDENTIFIER ON. SSMS sets it by default. From sqlcmd, pass -I.
Relational database design and 3NF normalisation, entity-relationship modelling, T-SQL implementation of tables, views, stored procedures, user-defined functions and triggers, constraint-based data integrity, transaction and concurrency considerations, database security and recovery planning, and advanced analytical querying (joins, subqueries, window functions, statistical outlier detection) on a real-world dataset.

