# Example 3 - "Bookstore” application

### Schema

We are going to use this schema as the sample schema for the examples that follow - This is the Gravity Bookstore sample database from [DatabaseStar](https://www.databasestar.com/sample-bookstore-database/).

This schema contains the following tables:

* **book**: a list of all books available in the store.
* **book\_author**: stores the authors for each book, which is a many-to-many relationship.
* **author**: a list of all authors.
* **book\_language**: a list of possible languages of books.
* **publisher**: a list of publishers for books.
* **customer**: a list of the customers of the Gravity Bookstore.
* **customer\_address**: a list of addresses for customers, as a customer can have more than one address, and an address has more than one customer.
* **address\_status**: a list of statuses for an address, because addresses can be current or old.
* **address**: a list of addresses in the system.
* **country**: a list of countries that addresses are in.
* **cust\_order**: a list of orders placed by customers.
* **order\_line**: a list of books that are a part of each order.
* **shipping\_method**: the possible shipping methods for an order.
* **order\_history**: the history of an order, such as ordered, cancelled, delivered.
* **order\_status**: the possible statuses of an order.

Here is the ERD of this sample bookstore database:

<figure><img src="/files/pp5Q55QjNom9RTWR5lLx" alt=""><figcaption></figcaption></figure>

* This is the schema in the Neurelo Schema Language JSON Specification format

<details>

<summary>JSON Schema</summary>

```json
{
  "objects": {
    "address": {
      "properties": {
        "address_id": {
          "type": "integer",
          "identifier": true
        },
        "city": {
          "type": "string",
          "sourceType": [
            "VarChar",
            100
          ],
          "nullable": true
        },
        "country": {
          "nullable": true,
          "$ref": "#/objects/country",
          "relation": {
            "attrKey": [
              "country_id"
            ],
            "foreignAttrKey": [
              "country_id"
            ],
            "sourceName": "fk_addr_ctry",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "country_id": {
          "type": "integer",
          "nullable": true
        },
        "cust_order": {
          "type": "array",
          "items": {
            "$ref": "#/objects/cust_order"
          }
        },
        "customer_address": {
          "type": "array",
          "items": {
            "$ref": "#/objects/customer_address"
          }
        },
        "street_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            200
          ],
          "nullable": true
        },
        "street_number": {
          "type": "string",
          "sourceType": [
            "VarChar",
            10
          ],
          "nullable": true
        }
      }
    },
    "address_status": {
      "properties": {
        "address_status": {
          "type": "string",
          "sourceType": [
            "VarChar",
            30
          ],
          "nullable": true
        },
        "status_id": {
          "type": "integer",
          "identifier": true
        }
      }
    },
    "author": {
      "properties": {
        "author_id": {
          "type": "integer",
          "identifier": true
        },
        "author_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            400
          ],
          "nullable": true
        },
        "book_author": {
          "type": "array",
          "items": {
            "$ref": "#/objects/book_author"
          }
        }
      }
    },
    "book": {
      "properties": {
        "book_author": {
          "type": "array",
          "items": {
            "$ref": "#/objects/book_author"
          }
        },
        "book_id": {
          "type": "integer",
          "identifier": true
        },
        "book_language": {
          "nullable": true,
          "$ref": "#/objects/book_language",
          "relation": {
            "attrKey": [
              "language_id"
            ],
            "foreignAttrKey": [
              "language_id"
            ],
            "sourceName": "fk_book_lang",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "isbn13": {
          "type": "string",
          "sourceType": [
            "VarChar",
            13
          ],
          "nullable": true
        },
        "language_id": {
          "type": "integer",
          "nullable": true
        },
        "num_pages": {
          "type": "integer",
          "nullable": true
        },
        "order_line": {
          "type": "array",
          "items": {
            "$ref": "#/objects/order_line"
          }
        },
        "publication_date": {
          "type": "string",
          "format": "date",
          "sourceType": "Date",
          "nullable": true
        },
        "publisher": {
          "nullable": true,
          "$ref": "#/objects/publisher",
          "relation": {
            "attrKey": [
              "publisher_id"
            ],
            "foreignAttrKey": [
              "publisher_id"
            ],
            "sourceName": "fk_book_pub",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "publisher_id": {
          "type": "integer",
          "nullable": true
        },
        "title": {
          "type": "string",
          "sourceType": [
            "VarChar",
            400
          ],
          "nullable": true
        }
      }
    },
    "book_author": {
      "properties": {
        "author": {
          "$ref": "#/objects/author",
          "relation": {
            "attrKey": [
              "author_id"
            ],
            "foreignAttrKey": [
              "author_id"
            ],
            "sourceName": "fk_ba_author",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "author_id": {
          "type": "integer"
        },
        "book": {
          "$ref": "#/objects/book",
          "relation": {
            "attrKey": [
              "book_id"
            ],
            "foreignAttrKey": [
              "book_id"
            ],
            "sourceName": "fk_ba_book",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "book_id": {
          "type": "integer"
        }
      },
      "constraints": [
        {
          "type": "identifier",
          "property": [
            "book_id",
            "author_id"
          ],
          "sourceName": "pk_bookauthor"
        }
      ]
    },
    "book_language": {
      "properties": {
        "book": {
          "type": "array",
          "items": {
            "$ref": "#/objects/book"
          }
        },
        "language_code": {
          "type": "string",
          "sourceType": [
            "VarChar",
            8
          ],
          "nullable": true
        },
        "language_id": {
          "type": "integer",
          "identifier": true
        },
        "language_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            50
          ],
          "nullable": true
        }
      }
    },
    "country": {
      "properties": {
        "address": {
          "type": "array",
          "items": {
            "$ref": "#/objects/address"
          }
        },
        "country_id": {
          "type": "integer",
          "identifier": true
        },
        "country_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            200
          ],
          "nullable": true
        }
      }
    },
    "cust_order": {
      "properties": {
        "address": {
          "nullable": true,
          "$ref": "#/objects/address",
          "relation": {
            "attrKey": [
              "dest_address_id"
            ],
            "foreignAttrKey": [
              "address_id"
            ],
            "sourceName": "fk_order_addr",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "customer": {
          "nullable": true,
          "$ref": "#/objects/customer",
          "relation": {
            "attrKey": [
              "customer_id"
            ],
            "foreignAttrKey": [
              "customer_id"
            ],
            "sourceName": "fk_order_cust",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "customer_id": {
          "type": "integer",
          "nullable": true
        },
        "dest_address_id": {
          "type": "integer",
          "nullable": true
        },
        "order_date": {
          "type": "string",
          "format": "date-time",
          "sourceType": [
            "Timestamp",
            6
          ],
          "nullable": true
        },
        "order_history": {
          "type": "array",
          "items": {
            "$ref": "#/objects/order_history"
          }
        },
        "order_id": {
          "type": "integer",
          "default": {
            "function": "autoincrement"
          },
          "identifier": true
        },
        "order_line": {
          "type": "array",
          "items": {
            "$ref": "#/objects/order_line"
          }
        },
        "shipping_method": {
          "nullable": true,
          "$ref": "#/objects/shipping_method",
          "relation": {
            "attrKey": [
              "shipping_method_id"
            ],
            "foreignAttrKey": [
              "method_id"
            ],
            "sourceName": "fk_order_ship",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "shipping_method_id": {
          "type": "integer",
          "nullable": true
        }
      }
    },
    "customer": {
      "properties": {
        "cust_order": {
          "type": "array",
          "items": {
            "$ref": "#/objects/cust_order"
          }
        },
        "customer_address": {
          "type": "array",
          "items": {
            "$ref": "#/objects/customer_address"
          }
        },
        "customer_id": {
          "type": "integer",
          "identifier": true
        },
        "email": {
          "type": "string",
          "sourceType": [
            "VarChar",
            350
          ],
          "nullable": true
        },
        "first_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            200
          ],
          "nullable": true
        },
        "last_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            200
          ],
          "nullable": true
        }
      }
    },
    "customer_address": {
      "properties": {
        "address": {
          "$ref": "#/objects/address",
          "relation": {
            "attrKey": [
              "address_id"
            ],
            "foreignAttrKey": [
              "address_id"
            ],
            "sourceName": "fk_ca_addr",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "address_id": {
          "type": "integer"
        },
        "customer": {
          "$ref": "#/objects/customer",
          "relation": {
            "attrKey": [
              "customer_id"
            ],
            "foreignAttrKey": [
              "customer_id"
            ],
            "sourceName": "fk_ca_cust",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "customer_id": {
          "type": "integer"
        },
        "status_id": {
          "type": "integer",
          "nullable": true
        }
      },
      "constraints": [
        {
          "type": "identifier",
          "property": [
            "customer_id",
            "address_id"
          ],
          "sourceName": "pk_custaddr"
        }
      ]
    },
    "order_history": {
      "properties": {
        "cust_order": {
          "nullable": true,
          "$ref": "#/objects/cust_order",
          "relation": {
            "attrKey": [
              "order_id"
            ],
            "foreignAttrKey": [
              "order_id"
            ],
            "sourceName": "fk_oh_order",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "history_id": {
          "type": "integer",
          "default": {
            "function": "autoincrement"
          },
          "identifier": true
        },
        "order_id": {
          "type": "integer",
          "nullable": true
        },
        "order_status": {
          "nullable": true,
          "$ref": "#/objects/order_status",
          "relation": {
            "attrKey": [
              "status_id"
            ],
            "foreignAttrKey": [
              "status_id"
            ],
            "sourceName": "fk_oh_status",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "status_date": {
          "type": "string",
          "format": "date-time",
          "sourceType": [
            "Timestamp",
            6
          ],
          "nullable": true
        },
        "status_id": {
          "type": "integer",
          "nullable": true
        }
      }
    },
    "order_line": {
      "properties": {
        "book": {
          "nullable": true,
          "$ref": "#/objects/book",
          "relation": {
            "attrKey": [
              "book_id"
            ],
            "foreignAttrKey": [
              "book_id"
            ],
            "sourceName": "fk_ol_book",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "book_id": {
          "type": "integer",
          "nullable": true
        },
        "cust_order": {
          "nullable": true,
          "$ref": "#/objects/cust_order",
          "relation": {
            "attrKey": [
              "order_id"
            ],
            "foreignAttrKey": [
              "order_id"
            ],
            "sourceName": "fk_ol_order",
            "onUpdate": "NoAction",
            "onDelete": "NoAction"
          }
        },
        "line_id": {
          "type": "integer",
          "default": {
            "function": "autoincrement"
          },
          "identifier": true
        },
        "order_id": {
          "type": "integer",
          "nullable": true
        },
        "price": {
          "type": "string",
          "format": "decimal",
          "sourceType": [
            "Decimal",
            5,
            2
          ],
          "nullable": true
        }
      }
    },
    "order_status": {
      "properties": {
        "order_history": {
          "type": "array",
          "items": {
            "$ref": "#/objects/order_history"
          }
        },
        "status_id": {
          "type": "integer",
          "identifier": true
        },
        "status_value": {
          "type": "string",
          "sourceType": [
            "VarChar",
            20
          ],
          "nullable": true
        }
      }
    },
    "publisher": {
      "properties": {
        "book": {
          "type": "array",
          "items": {
            "$ref": "#/objects/book"
          }
        },
        "publisher_id": {
          "type": "integer",
          "identifier": true
        },
        "publisher_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            400
          ],
          "nullable": true
        }
      }
    },
    "shipping_method": {
      "properties": {
        "cost": {
          "type": "string",
          "format": "decimal",
          "sourceType": [
            "Decimal",
            6,
            2
          ],
          "nullable": true
        },
        "cust_order": {
          "type": "array",
          "items": {
            "$ref": "#/objects/cust_order"
          }
        },
        "method_id": {
          "type": "integer",
          "identifier": true
        },
        "method_name": {
          "type": "string",
          "sourceType": [
            "VarChar",
            100
          ],
          "nullable": true
        }
      }
    }
  }
}
```

</details>

### <mark style="color:green;">GET</mark> endpoint examples

* Get one author by id 19283

  ```json
  GET https://$ENV_API_URL/rest/author/19283
  ?select={"$scalars": true, "$related": true}
  ```
* Select first 10 publisher and books ordered by publisher name

  ```json
  GET https://$ENV_API_URL/rest/publisher
  ?select={"$scalars": true, "book": {"$scalars": true}}
  &order_by=[{"publisher_name": "asc"}]
  &take=10
  ```
* Select customer scalars and their orders. Filter by email containing "[cdbaby.com](http://cdbaby.com/)"

  ```json
  GET https://$ENV_API_URL/rest/customer
  ?select={"$scalars": true, "cust_order": {"order_id": true, "order_line": {"$scalars": true}}}
  &filter={"email": {"contains": "@cdbaby.com"}}
  ```
* Select book\_id and the number of books sold where sales > 8

  ```json
  GET https://$ENV_API_URL/rest/order_line
  ?select={"book_id": true}
  &aggregate={"_count": ["line_id"]}
  &group_by=["book_id"]
  &having={"book_id": {"_count": {"gt": 8}}}
  ```
* Get list of customer order with order\_history and scalars. Filter by status is Order Received

  ```json
  GET https://$ENV_API_URL/rest/cust_order
  ?select={"order_history": {"order_status": {"$scalars": true}, "status_date": true}, "$scalars": true}
  &filter={"order_history": {"every": {"order_status": {"status_value": {"equals": "Order Received"}}}}}
  &take=3
  ```
* Get list of books not translated to "English” (.*English.* - includes “English”, “British English”, “United States English”), or “Spanish

  ```json
  GET https://$ENV_API_URL/rest/book
  ?select={"book_id": true, "title": true}
  &filter={"book_language": {"NOT": [{"language_name": {"contains": "English"}}, {"language_name": "Spanish"}]}}
  ```

### <mark style="color:blue;">POST</mark> endpoint examples

* Create a new book with author

  ```json
  POST https://$ENV_API_URL/rest/book
  ?select={"$scalars": true, "$related": true}

  {
      "book_id": 19283,
      "title": "DUNE: A new hope",
      "book_author": {
          "create": {
              "author": {
                  "create": {
                      "author_id": 19283,
                      "author_name": "Frank Lucas"
                  }
              }
          }
      }
  }
  ```
* Create many authors

  ```json
  POST https://$ENV_API_URL/rest/author

  [
      {
          "author_id": 19284,
          "author_name": "Francis Hube"
      },
      {
          "author_id": 19285,
          "author_name": "Brandon Stevens"
      },
      {
          "author_id": 19286,
          "author_name": "Red Roberts"
      }
  ]
  ```
* Create a new author with many books

  ```json
  POST https://$ENV_API_URL/rest/author
  ?select={"$scalars": true, "$related": true}

  {
      "author_id": 19288,
      "author_name": "Arthur Doyle",
      "book_author": {
          "create": [
              {
                  "book": {
                      "create": {
                          "book_id": 19286,
                          "title": "Sherlock Holmes: Mistery one"
                      }
                  }
              },
              {
                  "book": {
                      "create": {
                          "book_id": 19287,
                          "title": "Sherlock Holmes: Mistery two"
                      }
                  }
              }
          ]
      }
  }
  ```
* Create a customer with address and address status

  ```json
  POST https://$ENV_API_URL/rest/customer
  ?select={"$scalars": true, "customer_address": {"address": {"$scalars": true}}}

  {
      "customer_id": 19201,
      "first_name": "Watson",
      "email": "watson@solvesmysteries.com",
      "customer_address": {
          "create": {
              "status_id": 1,
              "address": {
                  "create": {
                      "address_id": 19201,
                      "city": "San Francisco",
                      "street_name": "Big Street",
                      "street_number": "1"
                  }
              }
          }
      }
  }
  ```

### <mark style="color:purple;">PATCH</mark> endpoint examples

* Update a single address

  ```json
  PATCH https://$ENV_API_URL/rest/address/19200
  ?select={"$scalars": true}

  {
      "city": "London",
      "street_name": "Baker Street",
      "street_number": "221B"
  }
  ```
* Update multiple addresses based on filter

  ```json
  PATCH https://$ENV_API_URL/rest/address
  ?filter={"city": "London"}

  {
      "city": "London",
      "street_name": "Baker Street",
      "street_number": "221B"
  }
  ```
* Update book title where title and language

  ```json
  PATCH https://$ENV_API_URL/rest/book
  ?filter={"AND": {"title": "Bed time stories", "book_language": {"language_name": "Russian"}}}

  {
      "isbn13": "the-new-isbn"
  }
  ```

### <mark style="color:red;">DELETE</mark> endpoint examples

* Delete publishers that have forbidden books published

  ```json
  DELETE https://$ENV_API_URL/rest/publisher
  ?filter={"book": {"some": {"title": "Forbidden text as of now"}}}
  ```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.neurelo.com/neurelo-api-reference-rest/examples-of-neurelo-auto-generated-rest-api-endpoints/example-3-bookstore-application.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.