Standards-Compliant Schema Design and Constraint Checks

Goal

You will be able to design a schema according to the data standard (word → domain → term), verify that constraints actually work, create indexes that fit the query patterns, and generate the table definition automatically from metadata.

Why it matters

A system where the same customer name is scattered across CUST_NAME(50), CUSTOMER_NM(100), and CUST_NM(30) struggles with integrated lookups, silently truncates data in the shorter columns, and three years later a person has to build the mapping table by hand during migration. And if the table definition says "required" but the DDL has no NOT NULL, that document is not enforced. A constraint is enforced only when you put it on the DB. Finally, a definition maintained by hand inevitably drifts from reality by the time development ends. The habit of extracting it from metadata removes this problem.

Steps

1. Requirements: /opt/lab/fixtures/dbo/req/schema-req.md Standard words: /opt/lab/fixtures/dbo/std/standard-words.csv
2. Create /root/db and create the sqlite DB /root/db/si.db. (The file is created only if there is at least one table. You may do it together with step 3.)
3. Create /root/db/naming.csv. The first line is logical,physical,domain. Combine the logical names from the requirements with the standard words to decide the physical names. It must have at least 10 rows, physical names use only uppercase letters and underscores, and domain must not be empty. Always include the five logical names 고객명, 주문번호, 주문일자, 주문금액, and 상품코드 (customer name, order number, order date, order amount, and product code).
4. Create the four tables below. CUSTOMER, PRODUCT, ORDERS, ORDER_ITEM Requirements:
   • A PRIMARY KEY on every table
   • FOREIGN KEYs from ORDERS.CUST_ID → CUSTOMER, ORDER_ITEM.ORD_NO → ORDERS, and ORDER_ITEM.PROD_CD → PRODUCT
   • A CHECK on ORDER_ITEM.QTY that allows only positive values
   • A CHECK on ORDERS.ORD_STS_CD that allows only '01','02','03','09'
   • A REG_DT column on every table, with NOT NULL and a default
   • UNIQUE on CUSTOMER.CUST_EMAIL
5. Create /root/db/constraint.txt. It has three lines, and each line holds the first line of the result message of a violation attempt.

   qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>
   status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>
   email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>
   

   (Do not dirty the original DB; test on a copy.)
6. Create three indexes.
   • IX_ORDERS_01 : for WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?
   • IX_ORDERS_02 : for WHERE ORD_DT = ? AND ORD_STS_CD = ?
   • IX_ORDER_ITEM_01 : for WHERE PROD_CD = ? The column order must match as well.
7. Load the four CSVs in /opt/lab/fixtures/dbo/seed/ into their tables. There must be 200 rows in CUSTOMER, 50 in PRODUCT, 1000 in ORDERS, and 2400 in ORDER_ITEM.
8. Create the view V_DAILY_SALES. The columns are ORD_DT, ORD_CNT, and AMT_SUM, it aggregates only orders excluding the canceled status (09), and it is in ascending ORD_DT order.
9. Create /root/db/table-def.csv. The first line is table,column,type,notnull,pk. Extract from the DB metadata and include every column of the four tables. Keep the table names and column order as they are.

Notes

• Enabling sqlite foreign keys: PRAGMA foreign_keys = ON; (you must set it on every connection)
• Metadata: PRAGMA table_info(<테이블>);, PRAGMA foreign_key_list(<테이블>); (the placeholder is the table name)
• Loading CSV: .mode csv / .import --skip 1 <파일> <테이블> (the placeholders are the file and the table)
• Common mistake 1: not turning on PRAGMA foreign_keys, so FKs are not checked. sqlite has it off by default.
• Common mistake 2: writing a CHECK only in the document and not putting it in the DDL.
• Common mistake 3: building a composite index with the column order reversed.

Create the DB

Create /root/db and create the sqlite DB /root/db/si.db. (The file is created only if there is at least one table. You may do it together with step 3.)

In sqlite, a single file is the DB. Remember that foreign key checking is off by default.

Standard term mapping table

Create /root/db/naming.csv. The first line is logical,physical,domain. Combine the logical names from the requirements with the standard words to decide the physical names. It must have at least 10 rows, physical names use only uppercase letters and underscores, and domain must not be empty. Always include the five logical names 고객명, 주문번호, 주문일자, 주문금액, and 상품코드 (customer name, order number, order date, order amount, and product code).

Build the physical names by combining the standard word dictionary. If a word with the same meaning is used twice, its abbreviation must be the same too.

Create the tables

Create the four tables below. CUSTOMER, PRODUCT, ORDERS, ORDER_ITEM Requirements:

• A PRIMARY KEY on every table
• FOREIGN KEYs from ORDERS.CUST_ID → CUSTOMER, ORDER_ITEM.ORD_NO → ORDERS, and ORDER_ITEM.PROD_CD → PRODUCT
• A CHECK on ORDER_ITEM.QTY that allows only positive values
• A CHECK on ORDERS.ORD_STS_CD that allows only '01','02','03','09'
• A REG_DT column on every table, with NOT NULL and a default
• UNIQUE on CUSTOMER.CUST_EMAIL

A constraint is enforced only if it is in the DDL, not in a document. Think about which constraint expresses each of these: required fields, the range of code values, and prevention of duplicate business keys.

Verify the constraints work

Create /root/db/constraint.txt. It has three lines, and each line holds the first line of the result message of a violation attempt.

qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>
status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>
email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>

(Do not dirty the original DB; test on a copy.)

To confirm that a constraint really blocks, you have to insert violating data. Get into the habit of testing on a copy so you do not dirty the original DB.

Indexes based on query patterns

Create three indexes.

• IX_ORDERS_01 : for WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?
• IX_ORDERS_02 : for WHERE ORD_DT = ? AND ORD_STS_CD = ?
• IX_ORDER_ITEM_01 : for WHERE PROD_CD = ? The column order must match as well.

For a composite index, column order is everything. The basic rule is to put equality-condition columns first and range-condition columns after.

Load sample data

Load the four CSVs in /opt/lab/fixtures/dbo/seed/ into their tables. There must be 200 rows in CUSTOMER, 50 in PRODUCT, 1000 in ORDERS, and 2400 in ORDER_ITEM.

When loading CSV, watch how the header line is handled and how the delimiter is specified. Always check the row counts after loading.

Create an aggregate view

Create the view V_DAILY_SALES. The columns are ORD_DT, ORD_CNT, and AMT_SUM, it aggregates only orders excluding the canceled status (09), and it is in ascending ORD_DT order.

A view is also a means of enforcing a query standard. If you put the logical-deletion condition in the view, you can reduce incidents caused by a missing condition.

Generate the table definition automatically

Create /root/db/table-def.csv. The first line is table,column,type,notnull,pk. Extract from the DB metadata and include every column of the four tables. Keep the table names and column order as they are.

A document maintained by hand soon becomes a lie. If you extract it from the DB metadata, it always matches reality.