Database:

    ExampleData

- Database Table Index ( 4 Tables )
Tables
No.Schema Table NamePrimary KeyColumnsKeysIndicesTriggersRowID
1carsID3Yes
2NumbersID511Yes
3countrylistSortOrder142Yes
4citiesCity_Key311Yes
- Data-Views Index ( 2 Views )
Data Views
No.Schema View NameKindColumnsReferencesComment
1Cities_of_the_WorldPersist21World Capital Cities
2Independent_CountriesPersist71Lists only the Independent countries as of Jan-2015.
+ Database Properties
Properties
Property Name Property Value
Application ID:00000000
Incremental-Vacuum:-
Page Size:1 024
Pages in DB:81
File Size:82 944
Default Page Cache:0
SQLite Version:3 008 008
User version:0
Schema Format:4
Database Encoding:UTF-8
Pragma Settings
Database Pragma Pragma Value
application_id:0
auto_vacuum:0
automatic_index:1
compile_options:ENABLE_COLUMN_METADATA,ENABLE_FTS3,ENABLE_RTREE,SYSTEM_MALLOC,THREADSAFE=1
collation_list:0,1,2
defer_foreign_keys:0
encoding:UTF-8
foreign_keys:1
freelist_count:9
journal_mode:delete
journal_size_limit:-1
max_page_count:1073741823
page_count:81
page_size:1024
read_uncommitted:0
recursive_triggers:0
schema_version:112
secure_delete:0
user_version:0
+     cars

Table Properties
Primary KeyColumnsKeysIndicesTriggersRow_ID
ID3Yes
Columns
No.Column/Field NameTypeCollationColumn ConstraintDefaultObject ReferencesComment
1IDINTEGERPrimary Key Manufacturer DB ID
2MakeVARCHAR(32)NOCASE Unique Not NULL Manufacturer
3URLVARCHAR(255)http://www. Company web-url

Table Schema SQL
CREATE TABLE "cars" (
    "ID" INTEGER PRIMARY KEY /* Manufacturer DB ID */,
    "Make" VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE /* Manufacturer */,
    "URL" VARCHAR(255) DEFAULT 'http://www.' /* Company web-url */
);

+     Numbers

Table Properties
Primary KeyColumnsKeysIndicesTriggersRow_ID
ID511Yes
Columns
No.Column/Field NameTypeCollationColumn ConstraintDefaultObject ReferencesComment
1IDINTEGERPrimary KeyUsed in Trigger - Trig_Numbers_Int1 Number
2Str1VARCHAR(32)NOCASE Unique Not NULLUsed in Index - Idx_Numbers_Str1 Text Representation
3Int1TINYINTBINARY0Used in Trigger - Trig_Numbers_Int1 Value
4Sqr1INTEGERBINARY0Used in Trigger - Trig_Numbers_Int1 Value Squared
5CommTEXTNULL Odd/Even

Table Indices
No.TypeNameColumnsComment
1IndexIdx_Numbers_Str1Str1Number Name Idx

Table Triggers
No.NameEvent TypeTrigger ProgramComment
1Trig_Numbers_Int1After Update UPDATE Numbers SET `Sqr1` = (`Int1`*`Int1`) WHERE ID = NEW.ID;Auto-Set Values

Table Schema SQL
CREATE TABLE "Numbers" (
    "ID" INTEGER PRIMARY KEY /* Number */,
    "Str1" VARCHAR(32) NOT NULL UNIQUE COLLATE NOCASE /* Text Representation */,
    "Int1" TINYINT DEFAULT 0 COLLATE BINARY /* Value */,
    "Sqr1" INTEGER DEFAULT 0 COLLATE BINARY /* Value Squared */,
    "Comm" TEXT DEFAULT NULL /* Odd/Even */
);
DROP INDEX IF EXISTS Idx_Numbers_Str1;
CREATE INDEX Idx_Numbers_Str1 /* Number Name Idx */ ON Numbers (Str1 COLLATE NOCASE);

DROP TRIGGER IF EXISTS Trig_Numbers_Int1;
CREATE TRIGGER IF NOT EXISTS Trig_Numbers_Int1
    AFTER UPDATE OF Int1 /* Auto-Set Values */
    ON Numbers FOR EACH ROW
BEGIN
    UPDATE Numbers SET `Sqr1` = (`Int1`*`Int1`) WHERE ID = NEW.ID;
END;

+     countrylist

Table Properties
Primary KeyColumnsKeysIndicesTriggersRow_ID
SortOrder142Yes
Columns
No.Column/Field NameTypeCollationColumn ConstraintDefaultObject ReferencesComment
1SortOrderINTEGERPrimary Key ID and Order
2CommonNameVARCHAR(128)NOCASE Not NULLUsed in Unique Index - Idx_countrylist_CommonName Name known by
3FormalNameVARCHAR(128) Proper / Legal
4TypeVARCHAR(64)NOCASE Governing Structure
5SubTypeVARCHAR(64)NOCASE
6SovereigntyVARCHAR(64)NOCASE Attached to
7CapitalVARCHAR(128)NOCASE City
8Currency_CodeVARCHAR(3)NOCASE ISO4217CurrencyCode
9CurrencyVARCHAR(32)NOCASE ISO4217CurrencyName
10Dial_CodeVARCHAR(8) ITUTTelephoneCode
11ISO_2Char_CodeVARCHAR(2)NOCASE ISO316612LetterCode
12ISO_3Char_CodeVARCHAR(3)NOCASEReferenced in Index - Idx_countrylist_ISO_Code ISO316613LetterCode
13Country_NumberINTEGER ISO31661Number
14IANA_CodeVARCHAR(4)NOCASE IANACountryCodeTLD

Table Indices
No.TypeNameColumnsComment
1IndexIdx_countrylist_ISO_CodeISO_3Char_CodeResolving Code-base Searches - Unique when available
2Unique IndexIdx_countrylist_CommonNameCommonNameResolving Name-base Searches

Table Schema SQL
CREATE TABLE "countrylist" (
    "SortOrder" INTEGER PRIMARY KEY /* ID and Order */,
    "CommonName" VARCHAR(128) NOT NULL COLLATE NOCASE /* Name known by */,
    "FormalName" VARCHAR(128) /* Proper / Legal */,
    "Type" VARCHAR(64) COLLATE NOCASE /* Governing Structure */,
    "SubType" VARCHAR(64) COLLATE NOCASE,
    "Sovereignty" VARCHAR(64) COLLATE NOCASE /* Attached to */,
    "Capital" VARCHAR(128) COLLATE NOCASE /* City */,
    "Currency_Code" VARCHAR(3) COLLATE NOCASE /* ISO4217CurrencyCode */,
    "Currency" VARCHAR(32) COLLATE NOCASE /* ISO4217CurrencyName */,
    "Dial_Code" VARCHAR(8) /* ITUTTelephoneCode */,
    "ISO_2Char_Code" VARCHAR(2) COLLATE NOCASE /* ISO316612LetterCode */,
    "ISO_3Char_Code" VARCHAR(3) COLLATE NOCASE /* ISO316613LetterCode */,
    "Country_Number" INTEGER /* ISO31661Number */,
    "IANA_Code" VARCHAR(4) COLLATE NOCASE /* IANACountryCodeTLD */
);
DROP INDEX IF EXISTS Idx_countrylist_ISO_Code;
CREATE INDEX Idx_countrylist_ISO_Code /* Resolving Code-base Searches - Unique when available */ ON countrylist (ISO_3Char_Code COLLATE NOCASE) WHERE ISO_3Char_Code<>'';
DROP INDEX IF EXISTS Idx_countrylist_CommonName;
CREATE UNIQUE INDEX Idx_countrylist_CommonName /* Resolving Name-base Searches */ ON countrylist (CommonName COLLATE NOCASE);

+     cities

Table Properties
Primary KeyColumnsKeysIndicesTriggersRow_ID
City_Key311Yes
Columns
No.Column/Field NameTypeCollationColumn ConstraintDefaultObject ReferencesComment
1City_KeyINTEGERPrimary Key
2CityTEXTUsed in Index - Idx_cities_City Official City-name
3CountryNameTEXTNOCASE Not NULLUsed in Foreign Key constraint Country CommonName

Table Constraints / Keys
No.NameDescriptionComment
1Foreign Key on (CountryName) referencing table "countrylist"Country-Name link

Table Indices
No.TypeNameColumnsComment
1IndexIdx_cities_CityCityUse for City-name searches

Table Schema SQL
CREATE TABLE "cities" (
    "City_Key" INTEGER PRIMARY KEY,
    "City" TEXT /* Official City-name */,
    "CountryName" TEXT NOT NULL COLLATE NOCASE /* Country CommonName */,
FOREIGN KEY (CountryName)
    REFERENCES "countrylist"(CommonName)
    ON DELETE SET NULL ON UPDATE CASCADE
    /* Country-Name link */
);
DROP INDEX IF EXISTS Idx_cities_City;
CREATE INDEX Idx_cities_City /* Use for City-name searches */ ON cities (City COLLATE NOCASE);

+       View: Cities_of_the_World

Data View Properties
KindColumnsReferencesComment
Persist2countrylistWorld Capital Cities
Columns
No.Column/Field Name/DefinitionInferred Type
1CityTEXT
2Capital ofTEXT

Data View SQL
CREATE VIEW Cities_of_the_World /* World Capital Cities */ AS
SELECT Capital AS 'City', CommonName AS 'Capital of'
    FROM countrylist
  WHERE City<>'' ORDER BY Capital

+       View: Independent_Countries

Data View Properties
KindColumnsReferencesComment
Persist7countrylistLists only the Independent countries as of Jan-2015.
Columns
No.Column/Field Name/DefinitionInferred Type
1SortOrderINTEGER
2CodeTEXT
3NameTEXT
4FormalNameTEXT
5CapitalTEXT
6CurrencyTEXT
7Dial_CodeTEXT

Data View SQL
CREATE VIEW Independent_Countries /* Lists only the Independent countries as of Jan-2015. */ AS
SELECT SortOrder, ISO_3Char_Code AS 'Code', CommonName AS 'Name', FormalName, Capital, Currency, Dial_Code
    FROM countrylist
  WHERE Type LIKE 'Independent%'