Example 2 - "DVD Rentals" application
Schema
This is a DVD Rentals example database schema from PostgreSQL Tutorial.
There are 15 tables in the DVD Rental database:
actor – stores actors data including first name and last name.
film – stores film data such as title, release year, length, rating, etc.
film_actor – stores the relationships between films and actors.
category – stores film’s categories data.
film_category- stores the relationships between films and categories.
store – contains the store data including manager staff and address.
inventory – stores inventory data.
rental – stores rental data.
payment – stores customer’s payments.
staff – stores staff data.
customer – stores customer data.
address – stores address data for staff and customers
city – stores city names.
country – stores country names.
Here is the ERD of the sample DVD rentals database:
This is the schema in the Neurelo Schema Language JSON Specification format
JSON Schema
{
"objects": {
"actor": {
"properties": {
"actor_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"film_actor": {
"type": "array",
"items": {
"$ref": "#/objects/film_actor"
}
},
"first_name": {
"type": "string",
"sourceType": [
"VarChar",
45
]
},
"last_name": {
"type": "string",
"sourceType": [
"VarChar",
45
]
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
}
},
"constraints": [
{
"type": "index",
"property": [
"last_name"
],
"sourceName": "idx_actor_last_name"
}
]
},
"address": {
"properties": {
"address": {
"type": "string",
"sourceType": [
"VarChar",
50
]
},
"address2": {
"type": "string",
"sourceType": [
"VarChar",
50
],
"nullable": true
},
"address_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"city": {
"$ref": "#/objects/city",
"relation": {
"attrKey": [
"city_id"
],
"foreignAttrKey": [
"city_id"
],
"sourceName": "fk_address_city",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"city_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"customer": {
"type": "array",
"items": {
"$ref": "#/objects/customer"
}
},
"district": {
"type": "string",
"sourceType": [
"VarChar",
20
]
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"phone": {
"type": "string",
"sourceType": [
"VarChar",
20
]
},
"postal_code": {
"type": "string",
"sourceType": [
"VarChar",
10
],
"nullable": true
},
"staff": {
"type": "array",
"items": {
"$ref": "#/objects/staff"
}
},
"store": {
"type": "array",
"items": {
"$ref": "#/objects/store"
}
}
},
"constraints": [
{
"type": "index",
"property": [
"city_id"
],
"sourceName": "idx_fk_city_id"
}
]
},
"category": {
"properties": {
"category_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"film_category": {
"type": "array",
"items": {
"$ref": "#/objects/film_category"
}
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"name": {
"type": "string",
"sourceType": [
"VarChar",
25
]
}
}
},
"city": {
"properties": {
"address": {
"type": "array",
"items": {
"$ref": "#/objects/address"
}
},
"city": {
"type": "string",
"sourceType": [
"VarChar",
50
]
},
"city_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"country": {
"$ref": "#/objects/country",
"relation": {
"attrKey": [
"country_id"
],
"foreignAttrKey": [
"country_id"
],
"sourceName": "fk_city",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"country_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
}
},
"constraints": [
{
"type": "index",
"property": [
"country_id"
],
"sourceName": "idx_fk_country_id"
}
]
},
"country": {
"properties": {
"city": {
"type": "array",
"items": {
"$ref": "#/objects/city"
}
},
"country": {
"type": "string",
"sourceType": [
"VarChar",
50
]
},
"country_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
}
}
},
"customer": {
"properties": {
"active": {
"type": "integer",
"nullable": true
},
"activebool": {
"type": "boolean",
"default": {
"value": true
}
},
"address": {
"$ref": "#/objects/address",
"relation": {
"attrKey": [
"address_id"
],
"foreignAttrKey": [
"address_id"
]
}
},
"address_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"create_date": {
"type": "string",
"format": "date",
"sourceType": "Date",
"default": {
"function": "dbgenerated",
"rawDbValue": "('now'::text)::date"
}
},
"customer_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"email": {
"type": "string",
"sourceType": [
"VarChar",
50
],
"nullable": true
},
"first_name": {
"type": "string",
"sourceType": [
"VarChar",
45
]
},
"last_name": {
"type": "string",
"sourceType": [
"VarChar",
45
]
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
},
"nullable": true
},
"payment": {
"type": "array",
"items": {
"$ref": "#/objects/payment"
}
},
"rental": {
"type": "array",
"items": {
"$ref": "#/objects/rental"
}
},
"store_id": {
"type": "integer",
"sourceType": "SmallInt"
}
},
"constraints": [
{
"type": "index",
"property": [
"address_id"
],
"sourceName": "idx_fk_address_id"
},
{
"type": "index",
"property": [
"store_id"
],
"sourceName": "idx_fk_store_id"
},
{
"type": "index",
"property": [
"last_name"
],
"sourceName": "idx_last_name"
}
]
},
"film": {
"properties": {
"description": {
"type": "string",
"nullable": true
},
"film_actor": {
"type": "array",
"items": {
"$ref": "#/objects/film_actor"
}
},
"film_category": {
"type": "array",
"items": {
"$ref": "#/objects/film_category"
}
},
"film_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"inventory": {
"type": "array",
"items": {
"$ref": "#/objects/inventory"
}
},
"language": {
"$ref": "#/objects/language",
"relation": {
"attrKey": [
"language_id"
],
"foreignAttrKey": [
"language_id"
]
}
},
"language_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"length": {
"type": "integer",
"sourceType": "SmallInt",
"nullable": true
},
"rating": {
"default": {
"value": "G"
},
"nullable": true,
"$ref": "#/enums/mpaa_rating"
},
"release_year": {
"type": "integer",
"nullable": true
},
"rental_duration": {
"type": "integer",
"sourceType": "SmallInt",
"default": {
"value": 3
}
},
"rental_rate": {
"type": "string",
"format": "decimal",
"sourceType": [
"Decimal",
4,
2
],
"default": {
"value": "4.99"
}
},
"replacement_cost": {
"type": "string",
"format": "decimal",
"sourceType": [
"Decimal",
5,
2
],
"default": {
"value": "19.99"
}
},
"special_features": {
"type": "array",
"items": {
"type": "string"
}
},
"title": {
"type": "string",
"sourceType": [
"VarChar",
255
]
}
},
"constraints": [
{
"type": "index",
"property": [
"language_id"
],
"sourceName": "idx_fk_language_id"
},
{
"type": "index",
"property": [
"title"
],
"sourceName": "idx_title"
}
]
},
"film_actor": {
"properties": {
"actor": {
"$ref": "#/objects/actor",
"relation": {
"attrKey": [
"actor_id"
],
"foreignAttrKey": [
"actor_id"
]
}
},
"actor_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"film": {
"$ref": "#/objects/film",
"relation": {
"attrKey": [
"film_id"
],
"foreignAttrKey": [
"film_id"
]
}
},
"film_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
}
},
"constraints": [
{
"type": "identifier",
"property": [
"actor_id",
"film_id"
]
},
{
"type": "index",
"property": [
"film_id"
],
"sourceName": "idx_fk_film_id"
}
]
},
"film_category": {
"properties": {
"category": {
"$ref": "#/objects/category",
"relation": {
"attrKey": [
"category_id"
],
"foreignAttrKey": [
"category_id"
]
}
},
"category_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"film": {
"$ref": "#/objects/film",
"relation": {
"attrKey": [
"film_id"
],
"foreignAttrKey": [
"film_id"
]
}
},
"film_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
}
},
"constraints": [
{
"type": "identifier",
"property": [
"film_id",
"category_id"
]
}
]
},
"inventory": {
"properties": {
"film": {
"$ref": "#/objects/film",
"relation": {
"attrKey": [
"film_id"
],
"foreignAttrKey": [
"film_id"
]
}
},
"film_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"inventory_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"rental": {
"type": "array",
"items": {
"$ref": "#/objects/rental"
}
},
"store_id": {
"type": "integer",
"sourceType": "SmallInt"
}
},
"constraints": [
{
"type": "index",
"property": [
"store_id",
"film_id"
],
"sourceName": "idx_store_id_film_id"
}
]
},
"language": {
"properties": {
"film": {
"type": "array",
"items": {
"$ref": "#/objects/film"
}
},
"language_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"name": {
"type": "string",
"sourceType": [
"Char",
20
]
}
}
},
"payment": {
"properties": {
"amount": {
"type": "string",
"format": "decimal",
"sourceType": [
"Decimal",
5,
2
]
},
"customer": {
"$ref": "#/objects/customer",
"relation": {
"attrKey": [
"customer_id"
],
"foreignAttrKey": [
"customer_id"
]
}
},
"customer_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"payment_date": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
]
},
"payment_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"rental": {
"$ref": "#/objects/rental",
"relation": {
"attrKey": [
"rental_id"
],
"foreignAttrKey": [
"rental_id"
],
"onDelete": "SetNull"
}
},
"rental_id": {
"type": "integer"
},
"staff": {
"$ref": "#/objects/staff",
"relation": {
"attrKey": [
"staff_id"
],
"foreignAttrKey": [
"staff_id"
]
}
},
"staff_id": {
"type": "integer",
"sourceType": "SmallInt"
}
},
"constraints": [
{
"type": "index",
"property": [
"customer_id"
],
"sourceName": "idx_fk_customer_id"
},
{
"type": "index",
"property": [
"rental_id"
],
"sourceName": "idx_fk_rental_id"
},
{
"type": "index",
"property": [
"staff_id"
],
"sourceName": "idx_fk_staff_id"
}
]
},
"rental": {
"properties": {
"customer": {
"$ref": "#/objects/customer",
"relation": {
"attrKey": [
"customer_id"
],
"foreignAttrKey": [
"customer_id"
]
}
},
"customer_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"inventory": {
"$ref": "#/objects/inventory",
"relation": {
"attrKey": [
"inventory_id"
],
"foreignAttrKey": [
"inventory_id"
]
}
},
"inventory_id": {
"type": "integer"
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"payment": {
"type": "array",
"items": {
"$ref": "#/objects/payment"
}
},
"rental_date": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
]
},
"rental_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"return_date": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"nullable": true
},
"staff": {
"$ref": "#/objects/staff",
"relation": {
"attrKey": [
"staff_id"
],
"foreignAttrKey": [
"staff_id"
],
"sourceName": "rental_staff_id_key",
"onUpdate": "NoAction",
"onDelete": "NoAction"
}
},
"staff_id": {
"type": "integer",
"sourceType": "SmallInt"
}
},
"constraints": [
{
"type": "index",
"property": [
"inventory_id"
],
"sourceName": "idx_fk_inventory_id"
},
{
"type": "unique",
"property": [
"rental_date",
"inventory_id",
"customer_id"
],
"sourceName": "idx_unq_rental_rental_date_inventory_id_customer_id"
}
]
},
"staff": {
"properties": {
"active": {
"type": "boolean",
"default": {
"value": true
}
},
"address": {
"$ref": "#/objects/address",
"relation": {
"attrKey": [
"address_id"
],
"foreignAttrKey": [
"address_id"
]
}
},
"address_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"email": {
"type": "string",
"sourceType": [
"VarChar",
50
],
"nullable": true
},
"first_name": {
"type": "string",
"sourceType": [
"VarChar",
45
]
},
"last_name": {
"type": "string",
"sourceType": [
"VarChar",
45
]
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"password": {
"type": "string",
"sourceType": [
"VarChar",
40
],
"nullable": true
},
"payment": {
"type": "array",
"items": {
"$ref": "#/objects/payment"
}
},
"picture": {
"type": "string",
"format": "bytes",
"nullable": true
},
"rental": {
"type": "array",
"items": {
"$ref": "#/objects/rental"
}
},
"staff_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
},
"store": {
"nullable": true,
"$ref": "#/objects/store"
},
"store_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"username": {
"type": "string",
"sourceType": [
"VarChar",
16
]
}
}
},
"store": {
"properties": {
"address": {
"$ref": "#/objects/address",
"relation": {
"attrKey": [
"address_id"
],
"foreignAttrKey": [
"address_id"
]
}
},
"address_id": {
"type": "integer",
"sourceType": "SmallInt"
},
"last_update": {
"type": "string",
"format": "date-time",
"sourceType": [
"Timestamp",
6
],
"default": {
"function": "now"
}
},
"manager_staff_id": {
"type": "integer",
"sourceType": "SmallInt",
"unique": true
},
"staff": {
"$ref": "#/objects/staff",
"relation": {
"attrKey": [
"manager_staff_id"
],
"foreignAttrKey": [
"staff_id"
]
}
},
"store_id": {
"type": "integer",
"default": {
"function": "autoincrement"
},
"identifier": true
}
}
}
},
"enums": {
"mpaa_rating": [
"G",
"PG",
"PG_13",
"R",
"NC_17"
]
}
}GET endpoint examples
Retrieve all actors
GET https://$ENV_API_URL/rest/actorsRetrieve a specific actor by id
GET https://$ENV_API_URL/rest/actor/159or
GET https://$ENV_API_URL/rest/actor?filter={"actor_id":159}Retrieve a specific actor
GET https://$ENV_API_URL/rest/actor?filter={"first_name":"John", "last_name":"Wayne"}Retrieve 5 customers
GET https://$ENV_API_URL/rest/customer?take=5Retrieve all films and their actors
GET https://$ENV_API_URL/rest/film?select={"film_actor": {"actor":{"$scalars": true}}, "$scalars": true}Retrieve stores and their employees
GET https://$ENV_API_URL/rest/store?select={"staff":{"$scalars": true}, "$scalars": true}Retrieve rentals and films
GET https://$ENV_API_URL/rest/rental?select={"inventory":{"film": {"$scalars": true}}, "$scalars": true}&take=5Retrieve films by category
GET https://$ENV_API_URL/rest/category?select={"film_category":{"film": {"$scalars": true}}, "$scalars": true}&take=5Retrieve customers that have rented a movie after 2006-01-01
GET https://$ENV_API_URL/rest/category?select={"$scalars": true}&filter={"rental": {"some": {"rental_date": {"gte": "2006-01-01T00:00:00.000Z"}}}}Retrieve customers that have more than 35 rentals
GET https://$ENV_API_URL/rest/category?select={"customer_id": true}&aggregate={"_count": ["rental_id"]}&group_by=["customer_id"]&having={"rental_id": {"_count": {"gt": 35}}}Retrieve count of films in english by category
GET https://$ENV_API_URL/rest/category?select={"category_id": true}&filter={"film": {"language": {"name": {"contains": "English"}}}}&aggregate={"_count": ["film_id"]}&group_by=["category_id"]Get all films where (special features contains "Commentaries" OR length > 160) AND replacement cost > 29
GET https://$ENV_API_URL/rest/category?select={"$scalars": true}&filter={"OR": [{"special_features": {"has": "Commentaries"}}, {"length": {"gt": 160}}], "replacement_cost": {"gte": 29}}
POST endpoint examples
Create a new actor
POST https://$ENV_API_URL/rest/actor { "content": "This is my first blog post", "title": "My first blog post" }Create multiple rentals
POST https://$ENV_API_URL/rest/rental [ { "customer_id": 2, "inventory_id": 1, "rental_date": "2023-07-11T14:50:58.951Z", "staff_id": 1 }, { "customer_id": 524, "inventory_id": 2, "rental_date": "2023-07-11T14:50:58.951Z", "staff_id": 1 } ]Create a film and related data
POST https://$ENV_API_URL/rest/film?select={"$scalars": true, "language": {"$scalars": true}, "film_category": {"category": {"$scalars": true}}, "film_actor": {"actor": {"$scalars": true}}} { "title": "Shrek 3", "language": { "connect": { "language_id": 2 } }, "film_category": { "create": { "category": { "create": { "name": "Swamp" } } } }, "film_actor": { "create": { "actor": { "create": { "first_name": "Prince", "last_name": "Arthur" } } } } }
PATCH endpoint examples
Update a film
PATCH https://$ENV_API_URL/rest/film/1001?select={"film_actor": {"actor":{"$scalars": true}}, "$scalars": true} { "description": "Book of Life" }
DELETE endpoint examples
Delete an actor
DELETE https://$ENV_API_URL/rest/actor/201?select={"$scalars": true, "$related": true}Delete films by name
DELETE https://$ENV_API_URL/rest/film?filter={"title": {"contains": "Shark"}}
Last updated
