Create a Table That Protects Its Data · capstone
Capstone project: Project: Build the Members Table
The items table can now protect the facts about each lendable item. The people who borrow those items need their own table, with rules that keep member records trustworthy.
Your schema.sql already contains the finished table below:
CREATE TABLE items (
id INTEGER PRIMARY KEY,
asset_tag TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
category TEXT NOT NULL,
loan_days INTEGER NOT NULL DEFAULT 7 CHECK (loan_days > 0)
);
This time, you will build the next table from its required behavior instead of copying a worked statement.
Build the members table
Open schema.sql. Leave the items table intact, then write a second CREATE TABLE statement named members beneath it.
The table needs four columns:
| Column | Kind of | Required behavior |
|---|---|---|
id | INTEGER | Every explicitly supplied ID identifies one row and cannot be repeated |
member_code | TEXT | Required, and an exact code cannot be repeated |
name | TEXT | Required, but two members may share a name |
loan_limit | INTEGER | Required, uses 3 when omitted, and, for numeric values, accepts only positive numbers |
You have already used every SQL rule needed here. Decide which rule or rules make each behavior true, and place them with the matching column.
Press Run as you work. When SQLite accepts both statements, the output shows definitions for items and members. If it reports an error, read the message and check the commas, parentheses, and spelling near the part it identifies.
Before you submit, compare your table with these record attempts:
| Attempt | Expected result |
|---|---|
ID 41, code M-210, name Riley Chen, limit omitted | Saved with a limit of 3 |
ID 42, code M-211, name Riley Chen, limit 5 | Saved, even though the name repeats |
ID 43, code M-212, name Morgan Lee, limit 2.5 | Saved with 2.5 |
ID 41 used again | Refused |
Exact code M-210 used again | Refused |
Code or name omitted, or member_code, name, or loan_limit supplied as NULL | Refused |
Limit 0 or -1 | Refused |
All IDs in this project are supplied explicitly. You do not need to make SQLite generate them.
As with loan_days, the positive-number comparison is not general type enforcement. It is the numeric rule this project needs, and positive real values such as 2.5 are allowed.
When both definitions load, select Submit and see whether the record attempts above behave as expected. The original items table must still protect its asset tags and loan periods, so add the new statement without weakening the first one.
When all of these records behave as expected, you have designed a second protected table from requirements alone. The two tables now give items and members separate, reliable homes in the lending database.
Task
Keep the completed items table unchanged. Beneath it, create a members table with these four columns and behaviors:
idusesINTEGERand rejects repeated explicit IDs.member_codeusesTEXT, is required, and rejects exact duplicates.nameusesTEXTand is required, but duplicate names are allowed.loan_limitusesINTEGER, is required, defaults to3when omitted, and, for numeric values, accepts only positive values.
Press Run and confirm that SQLite shows both table definitions. Then submit your schema. Positive real limits such as 2.5 are allowed; NULL, zero, and negative limits are not.