Example 3 - Bookstore

This is the Gravity Bookstore sample database from DatabaseStar (https://www.databasestar.com/sample-bookstore-database)

Overview

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.

ERD

Here is the ERD of this sample bookstore database:

Neurelo Schema

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

{
  "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
        }
      }
    }
  }
}