Published / updated29 Aug 2026

Guided Database Project

Before you start

You need: all of Articles 01–09.

In SSMS: work in a database you can drop and rebuild. Keep every script in a file — the deliverable is the scripts, not the database.

Time: 6–10 hours across a week.

Goal

Demonstrate that you can take a set of requirements, design a normalized schema whose constraints prevent invalid data, and deliver the queries, views and procedures an application would need.

Assignment

The syllabus offers Employee Management Database, Order Management Database or Inventory Management Database as mini projects. This course maps all of them onto the School Management System, so the schema you build here is the one every later track uses — Staff stands in for Employee, FeeAccount and FeePayment for Orders.

Build the complete NexCoding Academy database from the requirements below. Deliver six scripts and one document:

Deliverable	Contents
01-schema.sql	All tables, keys, constraints, named
02-indexes.sql	Indexes, each with a comment naming the query it serves
03-seed.sql	Realistic test data
04-views.sql	Reporting views
05-procedures.sql	CRUD and reporting procedures
06-queries.sql	The report queries below
DESIGN.md	Decisions, with reasons

Every script must run from an empty server, in order, without manual editing.

Requirements

NexCoding Academy runs a school administration system. Several schools use one deployment, and no school may ever see another's data.

• A school has a name, code, board, address and contact details.
• A student belongs to one school, has a roll number unique within that school, a class, a section, a date of birth, and parent name and phone. A student is Active, Inactive, Graduated or Transferred.
• A teacher belongs to one school with an employee code unique within it.
• Staff belong to one school with a designation and department.
• A subject belongs to a school and a class, has a code unique within its school and class, maximum and passing marks, and one assigned teacher — assignment is optional.
• An exam belongs to a subject, with a name, type, date, maximum marks and duration.
• An exam result records one student's marks for one exam. A student can have at most one result per exam. An absent student has no marks recorded; a present student must have marks.
• A fee account belongs to a student for one academic year, with total fees, discount, paid amount and a due date.
• A fee payment belongs to a fee account, with an amount, date, mode, receipt number and who collected it. A payment can be cancelled. Payments must never exceed the outstanding balance.
• Attendance records whether a student was present for a subject on a date. One record per student, subject and date.

Reports required

1. Class-wise student count, active only.
2. Every student's marks for one exam, including those with no result recorded.
3. Class-wise pass percentage for one exam, absentees excluded from the denominator.
4. Top 5 scorers per class for one exam, ties included.
5. Students with fees outstanding, highest first, excluding cancelled payments.
6. Monthly fee collection totals for one academic year.
7. Students below 75% attendance in a given month.
8. Teachers with the subjects they teach, as one comma-separated column.
9. Students with no fee account for the current academic year.
10. Duplicate exam results, if any exist.

Worked example: design decisions

DESIGN.md should read like this — a decision, then why.

Keys

Id       INT IDENTITY(1,1)  -- clustered PK: narrow, unique, ever-increasing
PublicId UNIQUEIDENTIFIER   -- unique nonclustered; goes in URLs

Sequential ids in a URL let anyone enumerate records by incrementing. PublicId removes the guessability. It is not the clustered key, because random GUIDs fragment the index on every insert.

Tenant isolation

SchoolId on every table except School, and in every uniqueness constraint, every query's WHERE, and the leading position of every index:

CONSTRAINT UQ_Student_Roll UNIQUE (SchoolId, RollNumber)

A global constraint on RollNumber would stop the second school using a roll number the first already has. Every uniqueness rule in a multi-tenant system is scoped to the tenant. This is invisible with one school of test data and blocks the product with two.

The absent rule

CONSTRAINT CK_ExamResult_Absent
    CHECK ((IsAbsent = 1 AND MarksObtained IS NULL)
        OR (IsAbsent = 0 AND MarksObtained IS NOT NULL))

Without it, an absent student stored with MarksObtained = 0 is indistinguishable from one who genuinely scored zero — and prints as a fail on their result sheet. The database makes that state impossible rather than relying on whichever application is writing today.

Money

DECIMAL(18,2) everywhere. FLOAT cannot represent most decimal fractions exactly, so fee totals drift and receipts stop reconciling with balances.

Denormalization, deliberately

FeeAccount.PaidAmount duplicates SUM(FeePayment.Amount). Justified because the balance is read on every fee screen and summing payments each time is wasteful.

The obligation it creates: every payment must update it inside the same transaction, and only usp_RecordFeePayment may write it. One writer, or the value drifts and nobody knows which number is correct.

Deletion

Soft delete via Status. A student has results, attendance, fee accounts and payments; a hard delete is either blocked by foreign keys — correctly — or, with cascades, destroys records the school must keep.

All foreign keys are NO ACTION. The rejection is the feature.

The obligation: every query must filter Status <> 1. Miss it once and removed students reappear in a report.

Worked example: schema fragment

CREATE TABLE dbo.ExamResult
(
    Id            INT              IDENTITY(1,1) NOT NULL,
    PublicId      UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT DF_ExamResult_PublicId DEFAULT NEWID(),
    SchoolId      INT              NOT NULL,
    StudentId     INT              NOT NULL,
    ExamId        INT              NOT NULL,
    MarksObtained DECIMAL(5,2)     NULL,
    IsAbsent      BIT              NOT NULL
        CONSTRAINT DF_ExamResult_IsAbsent DEFAULT 0,
    Remarks       NVARCHAR(200)    NULL,

    CONSTRAINT PK_ExamResult PRIMARY KEY CLUSTERED (Id),

    CONSTRAINT UQ_ExamResult_PublicId UNIQUE NONCLUSTERED (PublicId),
    CONSTRAINT UQ_ExamResult_Student  UNIQUE NONCLUSTERED (ExamId, StudentId),

    CONSTRAINT FK_ExamResult_School  FOREIGN KEY (SchoolId)  REFERENCES dbo.School  (Id),
    CONSTRAINT FK_ExamResult_Student FOREIGN KEY (StudentId) REFERENCES dbo.Student (Id),
    CONSTRAINT FK_ExamResult_Exam    FOREIGN KEY (ExamId)    REFERENCES dbo.Exam    (Id),

    CONSTRAINT CK_ExamResult_Marks
        CHECK (MarksObtained IS NULL OR MarksObtained >= 0),

    CONSTRAINT CK_ExamResult_Absent
        CHECK ((IsAbsent = 1 AND MarksObtained IS NULL)
            OR (IsAbsent = 0 AND MarksObtained IS NOT NULL))
);
GO

Every constraint is named, so an error message identifies the rule that was broken:

Violation of UNIQUE KEY constraint 'UQ_ExamResult_Student'.
The duplicate key value is (5, 12).

Worked example: one report

Report 5 — students with fees outstanding.

WITH PaidByAccount AS
(
    SELECT   p.FeeAccountId,
             SUM(p.Amount) AS TotalPaid
    FROM     dbo.FeePayment AS p
    WHERE    p.SchoolId = @SchoolId
      AND    p.IsCancelled = 0            -- cancelled payments must not count
    GROUP BY p.FeeAccountId
)
SELECT   s.RollNumber,
         s.Name,
         s.ClassName + ' - ' + s.Section              AS ClassSection,
         fa.TotalFees,
         fa.DiscountAmount,
         ISNULL(pba.TotalPaid, 0)                     AS TotalPaid,
         fa.TotalFees - fa.DiscountAmount
           - ISNULL(pba.TotalPaid, 0)                 AS Outstanding,
         fa.DueDate
FROM     dbo.FeeAccount AS fa
JOIN     dbo.Student    AS s   ON s.Id = fa.StudentId
LEFT JOIN PaidByAccount AS pba ON pba.FeeAccountId = fa.Id
WHERE    fa.SchoolId = @SchoolId
  AND    fa.AcademicYear = @AcademicYear
  AND    s.Status <> 1
  AND    fa.TotalFees - fa.DiscountAmount - ISNULL(pba.TotalPaid, 0) > 0
ORDER BY Outstanding DESC, s.Name;

Four things this gets right, each of which is a defect if omitted:

Detail	What breaks without it
Payments aggregated in a CTE before joining	A student with four payments appears four times and their fees are counted four times
IsCancelled = 0	A reversed payment still counts, and the student shows as paid while owing money
ISNULL(pba.TotalPaid, 0)	A student with no payments gets NULL outstanding and vanishes from the report
s.Status <> 1	Removed students appear on the fee chase list

The supporting index:

-- Serves the outstanding-fees report and the fee collection screen
CREATE NONCLUSTERED INDEX IX_FeePayment_Account_Active
ON dbo.FeePayment (FeeAccountId)
INCLUDE (Amount)
WHERE IsCancelled = 0;

Filtered, because every fee query excludes cancelled payments. INCLUDE (Amount) covers the SUM so the table is never touched.

Submission template

DESIGN.md

Schema overview:
  Table list and relationships:
  ER description (parent → child, one-to-many or many-to-many):

Normalization:
  Normal form reached:
  Any deliberate denormalization, with the measurement or reason:
  Single writer for each denormalized value:

Key strategy:
  Clustered key choice and why:
  PublicId purpose:

Multi-tenant isolation:
  Where SchoolId appears (constraints / queries / indexes):
  What breaks if it is omitted from each:

Constraint inventory:
  Rule → constraint name → what invalid state it prevents:

Deletion strategy:
  Soft or hard, per table, with reason:
  Obligation this creates on every query:

Index inventory:
  Index name → query it serves → why these key columns in this order:
  Any INCLUDE columns and the lookup they remove:

Transactions:
  Which operations are multi-statement:
  How atomicity is guaranteed:

Reports:
  Report 1..10 → file and line → verified result:

Verification evidence:
  Constraint tests attempted and their error numbers:
  Plan before and after each index:

Verification

Run every one of these and record the result.

Scripts run clean. Drop the database, run 01 to 06 in order on an empty server. No manual edits, no errors.

Constraints reject invalid data. Attempt each and record the error number:

Attempt	Expected
Duplicate roll number, same school	2627
Same roll number, different school	Succeeds — proves the constraint is composite
Exam result for a non-existent student	547
Delete a student who has results	547
IsAbsent = 1 with MarksObtained = 0	547
Two results for the same student and exam	2627
PaidAmount exceeding TotalFees	547
Student with no ParentPhone	515

Reports return correct data. For each of the ten, hand-check at least one row against the seed data. Report 2 must include a student with no result; report 5 must include a student with no payments.

Transactions are atomic. Force a failure between the payment insert and the balance update. Confirm neither happened.

Indexes are used. For each index, capture the plan before and after. An index that does not change a plan should be deleted, not kept "just in case".

Tenant isolation holds. Seed two schools. For every report and procedure, confirm that passing school 1 never returns a row belonging to school 2. Then deliberately remove SchoolId from one query's WHERE and confirm the leak appears — that is the test that proves the filter was doing work.

AI practice

Three AI exercises from this track's syllabus. Do each after the schema is built, and apply Track 18's discipline — every answer is a hypothesis until you have run it.

1. Ask AI to generate test data, then verify it. Ask for 40 ExamResult rows across three subjects, including absentees. Check that absent rows have MarksObtained as NULL with IsAbsent = 1, never 0. Generated data almost always uses 0, which makes every class average wrong with no error.
2. Review an AI-written query for correctness and duplication. Ask for a fee report joining Student, FeeAccount and FeePayment. Run SELECT COUNT(*) before and after the join. A student with two fee accounts appears twice, and any SUM over that result is inflated.
3. Compare two schema designs and defend one. Ask for the trade-offs between one FeeAccount row per academic year and a single row with a year column — the trade-off, not a recommendation. Then argue your choice in your own words.

Also check every generated query for WHERE SchoolId = @SchoolId. Its absence returns another school's records with a perfectly normal result set.

Track 18 — Reviewing AI-generated code — has the full checklist.

Self-assessment

Your submission is complete when someone who has never seen it can run your scripts on an empty server, read DESIGN.md, understand why each constraint exists, run each report, and reproduce your verification.

Four specific tests of quality:

• Does the schema make the absent/marks contradiction impossible? Not "does the application prevent it" — can you INSERT it directly in SSMS?
• Is RollNumber unique per school rather than globally? Verification row 2 answers this. It is the difference between a product that onboards a second customer and one that cannot.
• Does every index name the query it serves? An index with no stated purpose is one nobody can safely remove later.
• Does DESIGN.md say what you chose not to do? Denormalization you rejected, indexes you removed after measuring, a fourth normal form you decided was not worth it.

Track completion criteria

You can design normalized beginner-to-intermediate schemas, write joins, views and stored procedures, use transactions and basic indexes, and explain how application data is stored and queried.

Specifically, you can:

• Explain the four anomalies that repeating data causes, and design them away
• Choose the correct type and size for any column, and predict NULL behaviour
• Write constraints that make invalid data impossible to store
• Write correct CRUD with sargable filters and deterministic ordering
• Join any number of tables and diagnose both missing and duplicated rows
• Build summary reports with GROUP BY, HAVING and conditional aggregation
• Write stored procedures with parameters, transactions and error handling
• Add an index and prove from the execution plan that it changed the query
• Normalize to 3NF, and justify any denormalization you keep

The syllabus recommends Track 07 — ADO.NET & Dapper Data Access or Track 10 — ASP.NET Core Development next. Track 07 is the direct continuation — it calls the schema and procedures you just built.