ShopLite project 2: CREATE script and seed data

60 min · Intermediate

A database script is finished when one command builds the whole database from nothing, fills it with data that exercises every rule, and gives the same result every time you run it.

What you'll build

You extend shoplite.sql from project 1 so that it also loads seed data: a small, realistic set of rows that developers load to test and demonstrate an application. ShopLite gets 8 customers, 9 addresses, 7 categories, 15 products, 16 orders and 28 order items from July to September 2026. The rows are chosen on purpose: a customer with no address and no orders, customers without a phone, a sold-out product, a hidden product, a product that never sold, cancelled orders and a price that changed after it was ordered.

Then you prove the rules work. A second file, bad-data.sql, tries rows that break the rules (a duplicate e-mail, a zero price, a 5-digit PIN code, an unknown status) and you record the exact MySQL error for each. shoplite.sql ends with a row count per table, so one look tells you the load worked.

Output of shoplite.sql

+-----------+-----------+------------+----------+--------+-------------+
| customers | addresses | categories | products | orders | order_items |
+-----------+-----------+------------+----------+--------+-------------+
|         8 |         9 |          7 |       15 |     16 |          28 |
+-----------+-----------+------------+----------+--------+-------------+

Requirements

Your program should do all of these.

shoplite.sql drops the old tables children first, creates all six tables and inserts the seed rows, in one run. It does not create or select a database: the checker already provides an empty database named shop.
Names and types are exact. Tables and columns: customers (customer_id INT, full_name VARCHAR(100), email VARCHAR(255), phone VARCHAR(15) NULL, created_at DATETIME); addresses (address_id INT, customer_id INT, label VARCHAR(20), line1 VARCHAR(150), city VARCHAR(60), state VARCHAR(60), pincode CHAR(6), is_default BOOLEAN); categories (category_id INT, name VARCHAR(50), parent_id INT NULL); products (product_id INT, sku VARCHAR(20), name VARCHAR(120), category_id INT, price DECIMAL(10,2), stock INT, is_active BOOLEAN); orders (order_id INT, customer_id INT, address_id INT, status VARCHAR(10), ordered_at DATETIME); order_items (order_item_id INT, order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2)). Every other column is NOT NULL, each first column is the AUTO_INCREMENT primary key, and column order is as listed.
Constraint names are exact: uq_customers_email, fk_addresses_customer, chk_addresses_pincode, uq_categories_name, fk_categories_parent, uq_products_sku, fk_products_category, chk_products_price, chk_products_stock, fk_orders_customer, fk_orders_address, chk_orders_status, uq_order_items_order_product, fk_order_items_order, fk_order_items_product, chk_order_items_quantity, chk_order_items_price. Defaults: label 'Home', is_default FALSE, stock 0, is_active TRUE, status 'PLACED', created_at and ordered_at CURRENT_TIMESTAMP. Deleting a customer deletes their addresses and deleting an order deletes its items; nothing else cascades.
Running it again gives exactly the same database, with the same ids.
The inserts never write ids: AUTO_INCREMENT numbers a fresh table 1, 2, 3 ... in insert order, and later inserts refer to those numbers.
Every order has at least one item, and every order ships to an address that belongs to its own customer.
The seed covers the edge cases: no orders, no address, no phone, stock 0, a hidden product, a product never sold, cancelled orders and a changed price.
bad-data.sql holds at least five rule-breaking statements, each with the exact error it produces as a comment. The script ends with one query that counts the rows of every table.

Build it step by step in the Build tab (6 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

Next step

Start building

6 milestones

What you'll build

Output of shoplite.sql

+-----------+-----------+------------+----------+--------+-------------+
| customers | addresses | categories | products | orders | order_items |
+-----------+-----------+------------+----------+--------+-------------+
|         8 |         9 |          7 |       15 |     16 |          28 |
+-----------+-----------+------------+----------+--------+-------------+

Requirements

Your program should do all of these.

shoplite.sql drops the old tables children first, creates all six tables and inserts the seed rows, in one run. It does not create or select a database: the checker already provides an empty database named shop.

Names and types are exact. Tables and columns: customers (customer_id INT, full_name VARCHAR(100), email VARCHAR(255), phone VARCHAR(15) NULL, created_at DATETIME); addresses (address_id INT, customer_id INT, label VARCHAR(20), line1 VARCHAR(150), city VARCHAR(60), state VARCHAR(60), pincode CHAR(6), is_default BOOLEAN); categories (category_id INT, name VARCHAR(50), parent_id INT NULL); products (product_id INT, sku VARCHAR(20), name VARCHAR(120), category_id INT, price DECIMAL(10,2), stock INT, is_active BOOLEAN); orders (order_id INT, customer_id INT, address_id INT, status VARCHAR(10), ordered_at DATETIME); order_items (order_item_id INT, order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2)). Every other column is NOT NULL, each first column is the AUTO_INCREMENT primary key, and column order is as listed.

Constraint names are exact: uq_customers_email, fk_addresses_customer, chk_addresses_pincode, uq_categories_name, fk_categories_parent, uq_products_sku, fk_products_category, chk_products_price, chk_products_stock, fk_orders_customer, fk_orders_address, chk_orders_status, uq_order_items_order_product, fk_order_items_order, fk_order_items_product, chk_order_items_quantity, chk_order_items_price. Defaults: label 'Home', is_default FALSE, stock 0, is_active TRUE, status 'PLACED', created_at and ordered_at CURRENT_TIMESTAMP. Deleting a customer deletes their addresses and deleting an order deletes its items; nothing else cascades.

Running it again gives exactly the same database, with the same ids.

The inserts never write ids: AUTO_INCREMENT numbers a fresh table 1, 2, 3 ... in insert order, and later inserts refer to those numbers.

Every order has at least one item, and every order ships to an address that belongs to its own customer.

The seed covers the edge cases: no orders, no address, no phone, stock 0, a hidden product, a product never sold, cancelled orders and a changed price.

bad-data.sql holds at least five rule-breaking statements, each with the exact error it produces as a comment. The script ends with one query that counts the rows of every table.