Capstone · SQL and Databases
Capstone: a sales database for a kirana chain
The brief
Annapurna Kirana runs three shops in Bengaluru and Mysuru and wants one database instead of three billing exports. Design the schema with keys and rules, load the exports, write the owner's reports with joins, CTEs and window functions, then add a view, an index, a stock trigger and a safe transaction for new bills.
Why it matters
Retail chains, from a three-shop kirana business to DMart, run on exactly this design: stores, products, stock, bills and bill lines. Building it end to end, from CREATE TABLE to a trigger that keeps stock right, is the project that shows an employer you can own a database, not just query one.
Skills you practise
- schema design with keys and constraints
- composite primary keys
- INSERT … SELECT and DISTINCT
- joins across four tables
- CTEs and ROW_NUMBER
- running totals with window frames
- views
- indexes
- triggers
- transactions
What you will have at the end
A complete retail database in five scripts (schema, load, reports, objects, a new sale), plus a README with the table diagram, the owner's three reports and what each constraint, the view, the index and the trigger are for. Put it on GitHub to show employers.
How it is marked (rubric, 100 points)
| Everything worksAll eleven checks pass and the scripts run from an empty database in order without errors. | 35 |
|---|---|
| Schema and rulesKeys, foreign keys, CHECK and UNIQUE stop wrong rates, negative stock, unknown payment modes and duplicate lines. | 25 |
| ReportsThe reports count bills correctly, use CTEs with clear names and use window functions where they fit. | 15 |
| Database objectsThe view, index, trigger and transaction each solve a stated problem, and you can show the index in a query plan. | 10 |
| README for an employerA clear write-up with the diagram, sample output of each report and the design choices, in plain English. | 15 |
0/11 steps done
Step 1 of 11
Stores and products
01-schema.sql creates stores (id, name, city) with unique names, and products (id, name, category, price, gst_rate) with a price above 0 and a GST rate of 0, 5, 18 or 40.
Start with the two master tables. stores: id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, city TEXT NOT NULL. products: id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, category TEXT NOT NULL, price INTEGER NOT NULL CHECK (price > 0), gst_rate INTEGER NOT NULL CHECK (gst_rate IN (0, 5, 18, 40)).
Since 22 September 2025 the main GST slabs for goods are 0, 5, 18 and 40 percent (a few items such as gold have special rates). A CHECK on the rate catches a typo like 12 or 50 at the billing counter. The check tries a product at 12%, one priced at 0, and a second store with the same name.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.