CREATE TABLE address_status
(
    status_id      INTEGER
            primary key,
    address_status TEXT
);
address_status
CREATE TABLE author
(
    author_id   INTEGER
            primary key,
    author_name TEXT
);
author
CREATE TABLE book_language
(
    language_id   INTEGER
            primary key,
    language_code TEXT,
    language_name TEXT
);
book_language
CREATE TABLE country
(
    country_id   INTEGER
            primary key,
    country_name TEXT
);
country
CREATE TABLE address
(
    address_id    INTEGER
            primary key,
    street_number TEXT,
    street_name   TEXT,
    city          TEXT,
    country_id    INTEGER,
    foreign key (country_id) references country(country_id)
);
address
CREATE TABLE customer
(
    customer_id INTEGER
            primary key,
    first_name  TEXT,
    last_name   TEXT,
    email       TEXT
);
customer
CREATE TABLE customer_address
(
    customer_id INTEGER,
    address_id  INTEGER,
    status_id   INTEGER,
    primary key (customer_id, address_id),
    foreign key (address_id) references address(address_id),
    foreign key (customer_id) references customer(customer_id)
);
customer_address
CREATE TABLE order_status
(
    status_id    INTEGER
            primary key,
    status_value TEXT
);
order_status
CREATE TABLE publisher
(
    publisher_id   INTEGER
            primary key,
    publisher_name TEXT
);
publisher
CREATE TABLE book
(
    book_id          INTEGER
            primary key,
    title            TEXT,
    isbn13           TEXT,
    language_id      INTEGER,
    num_pages        INTEGER,
    publication_date DATE,
    publisher_id     INTEGER,
    foreign key (language_id) references book_language(language_id),
    foreign key (publisher_id) references publisher(publisher_id)
);
book
CREATE TABLE book_author
(
    book_id   INTEGER,
    author_id INTEGER,
    primary key (book_id, author_id),
    foreign key (author_id) references author(author_id),
    foreign key (book_id) references book(book_id)
);
book_author
CREATE TABLE shipping_method
(
    method_id   INTEGER
            primary key,
    method_name TEXT,
    cost        REAL
);
shipping_method
CREATE TABLE "cust_order"
(
    order_id           INTEGER
        primary key autoincrement,
    order_date         DATETIME,
    customer_id        INTEGER
        references customer,
    shipping_method_id INTEGER
        references shipping_method,
    dest_address_id    INTEGER
        references address
);
cust_order
CREATE TABLE "order_history"
(
    history_id  INTEGER
        primary key autoincrement,
    order_id    INTEGER
        references cust_order,
    status_id   INTEGER
        references order_status,
    status_date DATETIME
);
order_history
CREATE TABLE "order_line"
(
    line_id  INTEGER
        primary key autoincrement,
    order_id INTEGER
        references cust_order,
    book_id  INTEGER
        references book,
    price    REAL
);
order_line
original_column_name,column_name,column_description,data_format,value_description
customer_id,customer id,the unique identifier of the customer,integer,
first_name,first name,the first name of the customer,text,
last_name,last name,the last name of the customer,text,"commonsense evidence:
A person's full name is the first name, middle name (if applicable), and last name. "
email,,the email of the customer,text,
customer
original_column_name,column_name,column_description,data_format,value_description
book_id,book id ,the unique identifier of the book,integer,
title,,the title of the book ,text,
isbn13,,the International Standard Book Number of the book,text,"commonsense evidence:
An ISBN is a unique 13-digit number assigned to each book to identify it internationally. The ISBN13 of a book is the specific version of the ISBN that uses 13 digits. It is typically displayed on the back cover of a book, along with the barcode and other information."
language_id,language id,"the id of the book language
Maps to book_language (language_id)",integer,
num_pages,number pages,the number of the pages,integer,
publication_date,publication date,the publication date of the book,date,"commonsense evidence:
The publication date of a book can provide information about when the book was released to the public. This can be useful for understanding the context in which the book was written, as well as for determining how current or outdated the information in the book might be. Additionally, the publication date can provide insight into the age of the book and its potential value as a collectible."
publisher_id,,"the id of the publisher
Maps to publisher (publisher_id)",integer,
book
original_column_name,column_name,column_description,data_format,value_description
author_id,author id,the unique identifier of the author,integer,
author_name,author name,the name of the author,text,
author
original_column_name,column_name,column_description,data_format,value_description
publisher_id,publisher id,the unique identifier of the publisher,integer,
publisher_name,publisher name,the name of the publisher,text,
publisher
original_column_name,column_name,column_description,data_format,value_description
history_id,history id,the unique identifier of the order history,integer ,
order_id,order_id,"the id of the order
Maps to cust_order(order_id)",integer,
status_id,status id ,"the id of the order
Maps to order_status(status_id)",integer,"commonsense evidence:
The order statuses include order received, pending delivery, delivery in progress, delivered, canceled, and returned."
status_date,status date,the date of the status updated ,datetime,
order_history
original_column_name,column_name,column_description,data_format,value_description
status_id,status id ,the unique identifier of the order status,integer,
status_value,status value,the status value,text,"commonsense evidence:
The order statuses include order received, pending delivery, delivery in progress, delivered, canceled, and returned. "
order_status
original_column_name,column_name,column_description,data_format,value_description
address_id,address id ,the unique identifier of the address,integer,
street_number,street number,the street number of the address,text,"commonsense evidence:
The street number is typically used to identify a specific building or residence on a street, with higher numbers generally indicating a location further down the street from the starting point. For example, if a street starts at number 1 and goes up to number 100, a building with the number 50 would be closer to the starting point than a building with the number 75."
street_name,street name,the street name,text,
city,,the city where the address belongs,text,
country_id,country id,the id of the country where the address belongs ,integer,"commonsense evidence:
Maps to the country (country id). The full address of the customer is 'No.street_number street_name, city, country'"
address
original_column_name,column_name,column_description,data_format,value_description
book_id,book id,"the id of the book
Maps to book(book_id)",integer,
author_id,author id,"the id of the author
Maps to author(author_id)",integer,"commonsense evidence:
Books with the same author id are written by the same author. "
book_author
original_column_name,column_name,column_description,data_format,value_description
status_id,status id,the unique identifier of the status,integer,
address_status,address status,the status of the address,text,"commonsense evidence:
	active: the address is still in use
	inactive: the address is not in use anymore"
address_status
original_column_name,column_name,column_description,data_format,value_description
language_id,language id ,the unique identifier of the language ,integer,
language_code,language code,the language code,text,"commonsense evidence:
A language code is a unique identifier for a specific language. It is typically used to identify a language in computing and other technical contexts. Some common language codes include ""en"" for English, ""fr"" for French, and ""es"" for Spanish. The specific codes used for each language can vary depending on the context and the system in which they are being used."
language_name,language name,the language name,text,
book_language
original_column_name,column_name,column_description,data_format,value_description
line_id,line id,the unique identifier of the order line,integer ,
order_id,order id ,"the id of the order
Maps to cust_order(order_id)",integer,
book_id,book id,"the id of the book
Maps to book(book_id)",integer,
price,,the price of the order,real,"commonsense evidence:
Even though the customer ordered the book with the same book id, the price could be different. The price of the order may be influenced by the shipping method, seasonal discount, and the number of books the customer ordered. "
order_line
original_column_name,column_name,column_description,data_format,value_description
order_id,order id,the unique identifier of the customer order,integer ,
order_date,order date,the date of the order,datetime,
customer_id,customer id ,"the id of the customer
Maps to customer(customer_Id)",integer,"commonsense evidence:
The number of orders ordered by the customer = the show-up times of the relative customer id in the table"
shipping_method_id,shipping method id,"the id of the shipping method
Maps to shipping_method(method_id)",integer,
dest_address_id,destination address id ,"the id of the destination address
Maps to address(address_id)",integer,
cust_order
original_column_name,column_name,column_description,data_format,value_description
customer_id,customer id,"the id of the customer
Maps to customer(customer_id)",integer,
address_id,address id,"the id of the address
Maps to address(address_id)",integer,
status_id,status id ,the id of the address status,integer,"commonsense evidence:
A customer may have several addresses. If a customer has several addresses, the address that the status_id = 1 is the customer's current address that is in use. The other addresses with 2 as status_id is abandoned addresses. "
customer_address
original_column_name,column_name,column_description,data_format,value_description
country_id,country id,the unique identifier of the country,integer,
country_name,country name,the country name,text,
country
original_column_name,column_name,column_description,data_format,value_description
method_id,method id,the unique identifier of the method,integer,
method_name,method name,the method name,text,
cost,,the cost of the shipping method,real,"commonsense evidence:
The main difference between the various shipping methods, such as standard, priority, express, and international, is the speed at which the item is delivered. Standard shipping is the slowest and least expensive option, while express and priority shipping are faster and more expensive. International shipping is for items that are being shipped to a destination outside of the country where they originated."
shipping_method