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
}
}
}
}
}Last updated