Hackolade

SQL Server Physical Model

Schema for:

Model name: AdventureWorks

Author:

Version:

File name: Azure demo data.json

File path: C:\Users\Pascal\Documents\Hackolade\Models\MS SQL Server\Azure demo data.json

Printed On: Thu Apr 09 2020 01:43:26 GMT+0200 (Central European Summer Time)

Created with: Hackolade - Visual data modeling for NoSQL and multimodel databases

1. Model

2. Schemas

2.1 SalesLT
2.1.2. Tables
2.1.2.1 Address
2.1.2.2 Customer
2.1.2.3 CustomerAddress
2.1.2.4 JSONtbl
2.1.2.5 Product
2.1.2.6 ProductCategory
2.1.2.7 ProductDescription
2.1.2.8 ProductModel
2.1.2.9 ProductModelProductDescription
2.1.2.10 SalesOrderDetail
2.1.2.11 SalesOrderHeader

3. Views

4. Relationships

1. Model

1.1 Model AdventureWorks

1.1.1 AdventureWorks Entity Relationship Diagram

Schemas

Show all

SalesLT

1.1.2 AdventureWorks Properties

Property	Value
Model name	AdventureWorks
Description	From the AdventureWorks sample database described at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15
DB vendor	MSSQLServer
DB version	2019

1.1.3 AdventureWorks User-Defined Types

1.1.3.1 Column Name

1.1.3.1.1 Name properties

Property	Value
Business Name	Name
Type	char
Subtype	nvarchar
JSON Types	string
Length	50
Not null	true
Sparse	false
Primary key	false
Unique	false

1.1.3.2 Column NameStyle

1.1.3.2.1 NameStyle properties

Property	Value
Business Name	NameStyle
Type	numeric
Subtype	bit
Default	((0))
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false

1.1.3.3 Column Phone

1.1.3.3.1 Phone properties

Property	Value
Business Name	Phone
Type	char
Subtype	nvarchar
JSON Types	string
Length	25
Not null	false
Sparse	false
Primary key	false
Unique	false

1.1.3.4 Column Flag

1.1.3.4.1 Flag properties

Property	Value
Business Name	Flag
Type	numeric
Subtype	bit
Default	1
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false

1.1.3.5 Column OrderNumber

1.1.3.5.1 OrderNumber properties

Property	Value
Business Name	OrderNumber
Type	char
Subtype	nvarchar
JSON Types	string
Length	25
Not null	false
Sparse	false
Primary key	false
Unique	false

1.1.3.6 Column AccountNumber

1.1.3.6.1 AccountNumber properties

Property	Value
Business Name	AccountNumber
Type	char
Subtype	nvarchar
JSON Types	string
Length	15
Not null	false
Sparse	false
Primary key	false
Unique	false

2. Schemas

2.1 Schema SalesLT

2.1.1 SalesLT Properties

Property	Value
Schema name	SalesLT
Technical name
Comments
Remarks
Database name	HackDBSQL

2.1.2 SalesLT Tables

2.1.2.1 Table Address

2.1.2.1.1 Address Tree Diagram

{object}

Address

{123}

AddressID

{ABC}

AddressLine1

(I1.1)

{ABC}

AddressLine2

(I1.2)

{ABC}

City

(I1.3)

{ABC}

StateProvince

ref:

Name (m)

(I1.4, I2.1)

{ABC}

CountryRegion

ref:

Name (m)

(I1.6)

{ABC}

PostalCode

(I1.5)

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.1.2 Address Properties

Property	Value
Table	Address
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.1.3 Address Column

Column	Type	Req	Key	Description	Comments
AddressID	int	true
AddressLine1	nvarchar(60)	true		This is for house number, street, and optional apt/suite
AddressLine2	nvarchar(60)	true
City	nvarchar(30)	true
StateProvince	nvarchar(50)	false
CountryRegion	nvarchar(50)	false
PostalCode	nvarchar(15)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.1.3.1 Column AddressID

2.1.2.1.3.1.1 AddressID properties

Property	Value
Business Name	AddressID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	9

2.1.2.1.3.2 Column AddressLine1

2.1.2.1.3.2.1 AddressLine1 properties

Property	Value
Business Name	AddressLine1
Type	char
Subtype	nvarchar
JSON Types	string
Length	60
Comments	This is for house number, street, and optional apt/suite
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	8713 Yosemite Ct.

2.1.2.1.3.3 Column AddressLine2

2.1.2.1.3.3.1 AddressLine2 properties

Property	Value
Business Name	AddressLine2
Type	char
Subtype	nvarchar
JSON Types	string
Length	60
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.1.3.4 Column City

2.1.2.1.3.4.1 City properties

Property	Value
Business Name	City
Type	char
Subtype	nvarchar
JSON Types	string
Length	30
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	Bothell

2.1.2.1.3.5 Column StateProvince

2.1.2.1.3.5.1 StateProvince properties

Property	Value
Business Name	StateProvince
$ref	#model/definitions/Name
Reference type	model

2.1.2.1.3.6 Column CountryRegion

2.1.2.1.3.6.1 CountryRegion properties

Property	Value
Business Name	CountryRegion
$ref	#model/definitions/Name
Reference type	model

2.1.2.1.3.7 Column PostalCode

2.1.2.1.3.7.1 PostalCode properties

Property	Value
Business Name	PostalCode
Type	char
Subtype	nvarchar
JSON Types	string
Length	15
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	98011

2.1.2.1.3.8 Column rowguid

2.1.2.1.3.8.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_Address_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_Address_rowguid
Sample	268AF621-76D7-4C78-9441-144FD139821A

2.1.2.1.3.9 Column ModifiedDate

2.1.2.1.3.9.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_Address_ModifiedDate
Sample	2006-07-01T00:00:00.000Z

2.1.2.1.4 Address Indexes

2.1.2.1.4.1 Index

Property
Name	IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion
Type	Index
Unique
Clustered
Keys
	AddressLine1
	AddressLine2
	City
	StateProvince
	PostalCode
	CountryRegion
Include non-key columns
Description
Pad index
Fill factor
Ignore duplicate key
Included columns
Statistics no recompute
Statistics incremental
Allow row locks	true
Allow page locks	true
Optimize for sequential key
Compression delay (minutes)
Data compression	NONE
Comments

2.1.2.1.4.2 Index

Property
Name	IX_Address_StateProvince
Type	Index
Unique
Clustered
Keys
	StateProvince
Include non-key columns
Description
Pad index
Fill factor
Ignore duplicate key
Included columns
Statistics no recompute
Statistics incremental
Allow row locks	true
Allow page locks	true
Optimize for sequential key
Compression delay (minutes)
Data compression	NONE
Comments

2.1.2.1.5 Address Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[Address] (
	[AddressID] INT IDENTITY(1, 1) NOT NULL,
	[AddressLine1] NVARCHAR(60) NOT NULL,
	[AddressLine2] NVARCHAR(60) NOT NULL,
	[City] NVARCHAR(30) NOT NULL,
	[StateProvince] [SalesLT].[Name],
	[CountryRegion] [SalesLT].[Name],
	[PostalCode] NVARCHAR(15) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC) WITH (
		IGNORE_DUP_KEY = ON,
		STATISTICS_NORECOMPUTE = ON
	) ON [PRIMARY],
	CONSTRAINT [AK_Address_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[Address] ADD CONSTRAINT [DF_Address_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[Address] ADD CONSTRAINT [DF_Address_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion
	ON [SalesLT].[Address] ( [AddressLine1], [AddressLine2], [City], [StateProvince], [PostalCode], [CountryRegion] ) GO

CREATE INDEX IX_Address_StateProvince
	ON [SalesLT].[Address] ( [StateProvince] ) GO

2.1.2.2 Table Customer

2.1.2.2.1 Customer Tree Diagram

{object}

Customer

{123}

CustomerID

{123}

NameStyle

ref:

NameStyle (m)

{ABC}

Title

{ABC}

FirstName

ref:

Name (m)

{ABC}

MiddleName

ref:

Name (m)

{ABC}

LastName

ref:

Name (m)

{ABC}

Suffix

{ABC}

CompanyName

{ABC}

SalesPerson

{ABC}

EmailAddress

(I1.1)

{ABC}

Phone

ref:

Phone (m)

{ABC}

PasswordHash

{ABC}

PasswordSalt

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.2.2 Customer Properties

Property	Value
Table	Customer
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.2.3 Customer Column

Column	Type	Req	Key	Description	Comments
CustomerID	int	true
NameStyle	bit	false
Title	nvarchar(8)	true
FirstName	nvarchar(50)	false
MiddleName	nvarchar(50)	false
LastName	nvarchar(50)	false
Suffix	nvarchar(10)	true
CompanyName	nvarchar(128)	true
SalesPerson	nvarchar(256)	true
EmailAddress	nvarchar(50)	true
Phone	nvarchar(25)	false
PasswordHash	varchar(128)	true
PasswordSalt	varchar(10)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.2.3.1 Column CustomerID

2.1.2.2.3.1.1 CustomerID properties

Property	Value
Business Name	CustomerID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	1

2.1.2.2.3.2 Column NameStyle

2.1.2.2.3.2.1 NameStyle properties

Property	Value
Business Name	NameStyle
$ref	#model/definitions/NameStyle
Reference type	model

2.1.2.2.3.3 Column Title

2.1.2.2.3.3.1 Title properties

Property	Value
Business Name	Title
Type	char
Subtype	nvarchar
JSON Types	string
Length	8
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	Mr.

2.1.2.2.3.4 Column FirstName

2.1.2.2.3.4.1 FirstName properties

Property	Value
Business Name	FirstName
$ref	#model/definitions/Name
Reference type	model

2.1.2.2.3.5 Column MiddleName

2.1.2.2.3.5.1 MiddleName properties

Property	Value
Business Name	MiddleName
$ref	#model/definitions/Name
Reference type	model

2.1.2.2.3.6 Column LastName

2.1.2.2.3.6.1 LastName properties

Property	Value
Business Name	LastName
$ref	#model/definitions/Name
Reference type	model

2.1.2.2.3.7 Column Suffix

2.1.2.2.3.7.1 Suffix properties

Property	Value
Business Name	Suffix
Type	char
Subtype	nvarchar
JSON Types	string
Length	10
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.2.3.8 Column CompanyName

2.1.2.2.3.8.1 CompanyName properties

Property	Value
Business Name	CompanyName
Type	char
Subtype	nvarchar
JSON Types	string
Length	128
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	A Bike Store

2.1.2.2.3.9 Column SalesPerson

2.1.2.2.3.9.1 SalesPerson properties

Property	Value
Business Name	SalesPerson
Type	char
Subtype	nvarchar
JSON Types	string
Length	256
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	adventure-works\pamela0

2.1.2.2.3.10 Column EmailAddress

2.1.2.2.3.10.1 EmailAddress properties

Property	Value
Business Name	EmailAddress
Type	char
Subtype	nvarchar
JSON Types	string
Length	50
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	orlando0@adventure-works.com

2.1.2.2.3.11 Column Phone

2.1.2.2.3.11.1 Phone properties

Property	Value
Business Name	Phone
$ref	#model/definitions/Phone
Reference type	model

2.1.2.2.3.12 Column PasswordHash

2.1.2.2.3.12.1 PasswordHash properties

Property	Value
Business Name	PasswordHash
Type	char
Subtype	varchar
JSON Types	string
Length	128
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w=

2.1.2.2.3.13 Column PasswordSalt

2.1.2.2.3.13.1 PasswordSalt properties

Property	Value
Business Name	PasswordSalt
Type	char
Subtype	varchar
JSON Types	string
Length	10
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	1KjXYs4=

2.1.2.2.3.14 Column rowguid

2.1.2.2.3.14.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_Customer_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_Customer_rowguid
Sample	3F5AE95E-B87D-4AED-95B4-C3797AFCB74F

2.1.2.2.3.15 Column ModifiedDate

2.1.2.2.3.15.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_Customer_ModifiedDate
Sample	2005-08-01T00:00:00.000Z

2.1.2.2.4 Customer Indexes

2.1.2.2.4.1 Index

Property
Name	IX_Customer_EmailAddress
Type	Index
Unique
Clustered
Keys
	EmailAddress
Include non-key columns
Description
Pad index
Fill factor
Ignore duplicate key
Included columns
Statistics no recompute
Statistics incremental
Allow row locks	true
Allow page locks	true
Optimize for sequential key
Compression delay (minutes)
Data compression	NONE
Comments

2.1.2.2.5 Customer Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[Customer] (
	[CustomerID] INT IDENTITY(1, 1) NOT NULL,
	[NameStyle] [SalesLT].[NameStyle],
	[Title] NVARCHAR(8) NOT NULL,
	[FirstName] [SalesLT].[Name],
	[MiddleName] [SalesLT].[Name],
	[LastName] [SalesLT].[Name],
	[Suffix] NVARCHAR(10) NOT NULL,
	[CompanyName] NVARCHAR(128) NOT NULL,
	[SalesPerson] NVARCHAR(256) NOT NULL,
	[EmailAddress] NVARCHAR(50) NOT NULL,
	[Phone] [SalesLT].[Phone],
	[PasswordHash] VARCHAR(128) NOT NULL,
	[PasswordSalt] VARCHAR(10) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_Customer_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[Customer] ADD CONSTRAINT [DF_Customer_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[Customer] ADD CONSTRAINT [DF_Customer_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_Customer_EmailAddress
	ON [SalesLT].[Customer] ( [EmailAddress] ) GO

2.1.2.3 Table CustomerAddress

2.1.2.3.1 CustomerAddress Tree Diagram

{object}

CustomerAddress

{123}

CustomerID

(I1.1)

{123}

AddressID

(I1.2)

{ABC}

AddressType

ref:

Name (m)

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.3.2 CustomerAddress Properties

Property	Value
Table	CustomerAddress
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.3.3 CustomerAddress Column

Column	Type	Req	Key	Description	Comments
CustomerID	int	true
AddressID	int	true
AddressType	nvarchar(50)	false
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.3.3.1 Column CustomerID

2.1.2.3.3.1.1 CustomerID properties

Property	Value
Business Name	CustomerID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	true
Foreign table	Customer
Foreign field	CustomerID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	29485

2.1.2.3.3.2 Column AddressID

2.1.2.3.3.2.1 AddressID properties

Property	Value
Business Name	AddressID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	true
Foreign table	Address
Foreign field	AddressID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	1086

2.1.2.3.3.3 Column AddressType

2.1.2.3.3.3.1 AddressType properties

Property	Value
Business Name	AddressType
$ref	#model/definitions/Name
Reference type	model

2.1.2.3.3.4 Column rowguid

2.1.2.3.3.4.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_CustomerAddress_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_CustomerAddress_rowguid
Sample	16765338-DBE4-4421-B5E9-3836B9278E63

2.1.2.3.3.5 Column ModifiedDate

2.1.2.3.3.5.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_CustomerAddress_ModifiedDate
Sample	2007-09-01T00:00:00.000Z

2.1.2.3.4 CustomerAddress Composite keys

2.1.2.3.4.1 Primary key

Property
Constraint name	PK_CustomerAddress_CustomerID_AddressID
Key
	CustomerID
	AddressID
Partition name	PRIMARY
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered	true
Data compression	NONE

2.1.2.3.5 CustomerAddress Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[CustomerAddress] (
	[CustomerID] INT NOT NULL,
	[AddressID] INT NOT NULL,
	[AddressType] [SalesLT].[Name],
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_CustomerAddress_CustomerID_AddressID] PRIMARY KEY CLUSTERED ([CustomerID] ASC, [AddressID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_CustomerAddress_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [FK_CustomerAddress_Customer_CustomerID] FOREIGN KEY (CustomerID) REFERENCES [SalesLT].[Customer](CustomerID),
	CONSTRAINT [FK_CustomerAddress_Address_AddressID] FOREIGN KEY (AddressID) REFERENCES [SalesLT].[Address](AddressID)
) GO


ALTER TABLE [SalesLT].[CustomerAddress] ADD CONSTRAINT [DF_CustomerAddress_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[CustomerAddress] ADD CONSTRAINT [DF_CustomerAddress_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

2.1.2.4 Table JSONtbl

2.1.2.4.1 JSONtbl Tree Diagram

{object}

JSONtbl

{ABC}

JSONContent

{ABC}

JSON2

{...}

city

[...]

data

{123}

time

{ABC}

videoid

{ABC}

added_date

{ABC}

description

{ABC}

location

{123}

location_type

[...]

metadata

{ABC}

name

[...]

2.1.2.4.2 JSONtbl Properties

Property	Value
Table	JSONtbl
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.4.3 JSONtbl Column

Column	Type	Req	Key	Description	Comments
ID	nchar(10)	true
JSONContent	nvarchar(MAX)	false
city	object	true
coord	object	true
lat	number	true
lon	number	true
country	string	true
id	number	true
name	string	true
data	array	true
[0]	object	false
clouds	object	true
all	number	true
dt	number	true
dt_txt	string	true
main	object	true
grnd_level	number	true
humidity	number	true
pressure	number	true
sea_level	number	true
temp	number	true
temp_kf	number	false
temp_max	number	true
temp_min	number	true
snow	object	true
3h	number	true
sys	object	true
pod	string	true
weather	array	true
[0]	object	false
description	string	true
icon	string	true
id	number	true
main	string	true
wind	object	true
deg	number	true
speed	number	true
rain	object	false
3h	number	true
time	number	true
JSON2	nvarchar(MAX)	true
videoid	string	true
added_date	string	true
description	string	true
location	string	true
location_type	number	true
metadata	array	true
[0]	object	false
height	number	true
width	number	true
video_bit_rate	array	true
[0]	string	false
encoding	string	true
name	string	true
tags	array	true
[0]	string	false
userid	string	true

2.1.2.4.3.1 Column ID

2.1.2.4.3.1.1 ID properties

Property	Value
Business Name	ID
Type	char
Subtype	nchar
Length	10
Not null	true
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Sample	1

2.1.2.4.3.2 Column JSONContent

2.1.2.4.3.2.1 JSONContent Tree Diagram

{ABC}

JSONContent

{...}

city

[...]

data

{123}

time

2.1.2.4.3.2.2 JSONContent Hierarchy

Parent Column: JSONtbl

Child column(s):

Column	Type	Req	Key	Description	Comments
city	jsonObject	true
data	jsonArray	true
time	jsonNumber	true

2.1.2.4.3.2.3 JSONContent properties

Property	Value
Business Name	JSONContent
Type	char
Subtype	nvarchar
JSON Types	object
Has max length	true
Not null	false
Sparse	false
Primary key	false
Unique	false

2.1.2.4.3.3 Column city

2.1.2.4.3.3.1 city Tree Diagram

{object}

city

{...}

coord

{ABC}

country

{123}

{ABC}

name

2.1.2.4.3.3.2 city Hierarchy

Parent Column: JSONContent

Child column(s):

Column	Type	Req	Key	Description	Comments
coord	jsonObject	true
country	jsonString	true
id	jsonNumber	true
name	jsonString	true

2.1.2.4.3.3.3 city properties

Property	Value
Business Name	city
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.4 Column coord

2.1.2.4.3.4.1 coord Tree Diagram

{object}

coord

{123}

lat

{123}

lon

2.1.2.4.3.4.2 coord Hierarchy

Parent Column: city

Child column(s):

Column	Type	Req	Key	Description	Comments
lat	jsonNumber	true
lon	jsonNumber	true

2.1.2.4.3.4.3 coord properties

Property	Value
Business Name	coord
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.5 Column lat

2.1.2.4.3.5.1 lat properties

Property	Value
Business Name	lat
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	52.200001

2.1.2.4.3.6 Column lon

2.1.2.4.3.6.1 lon properties

Property	Value
Business Name	lon
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	8.8

2.1.2.4.3.7 Column country

2.1.2.4.3.7.1 country properties

Property	Value
Business Name	country
Type	jsonString
Required	true
Sample	DE

2.1.2.4.3.8 Column id

2.1.2.4.3.8.1 id properties

Property	Value
Business Name	id
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	2953386

2.1.2.4.3.9 Column name

2.1.2.4.3.9.1 name properties

Property	Value
Business Name	name
Type	jsonString
Required	true
Sample	Bad Oeynhausen

2.1.2.4.3.10 Column data

2.1.2.4.3.10.1 data Tree Diagram

[...]

data

{...}

[0]*

2.1.2.4.3.10.2 data Hierarchy

Parent Column: JSONContent

Child column(s):

Column	Type	Req	Key	Description	Comments
[0]	jsonObject	false

2.1.2.4.3.10.3 data properties

Property	Value
Business Name	data
Type	jsonArray
JSON Type	char
Required	true
Unique items	false
Additional items	true

2.1.2.4.3.11 Column [0]

2.1.2.4.3.11.1 [0] Tree Diagram

{object}

[0]

{...}

clouds

{123}

{ABC}

dt_txt

{...}

main

{...}

snow

{...}

sys

[...]

weather

{...}

wind

{...}

rain

2.1.2.4.3.11.2 [0] Hierarchy

Parent Column: data

Child column(s):

Column	Type	Req	Key	Description	Comments
clouds	jsonObject	true
dt	jsonNumber	true
dt_txt	jsonString	true
main	jsonObject	true
snow	jsonObject	true
sys	jsonObject	true
weather	jsonArray	true
wind	jsonObject	true
rain	jsonObject	false

2.1.2.4.3.11.3 [0] properties

Property	Value
Type	jsonObject
JSON Type	char
Additional properties	false

2.1.2.4.3.12 Column clouds

2.1.2.4.3.12.1 clouds Tree Diagram

{object}

clouds

{123}

all

2.1.2.4.3.12.2 clouds Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
all	jsonNumber	true

2.1.2.4.3.12.3 clouds properties

Property	Value
Business Name	clouds
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.13 Column all

2.1.2.4.3.13.1 all properties

Property	Value
Business Name	all
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	88

2.1.2.4.3.14 Column dt

2.1.2.4.3.14.1 dt properties

Property	Value
Business Name	dt
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	1518987600

2.1.2.4.3.15 Column dt_txt

2.1.2.4.3.15.1 dt_txt properties

Property	Value
Business Name	dt_txt
Type	jsonString
Required	true
Sample	2018-02-18 21:00:00

2.1.2.4.3.16 Column main

2.1.2.4.3.16.1 main Tree Diagram

{object}

main

{123}

grnd_level

{123}

humidity

{123}

pressure

{123}

sea_level

{123}

temp

{123}

temp_kf

{123}

temp_max

{123}

temp_min

2.1.2.4.3.16.2 main Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
grnd_level	jsonNumber	true
humidity	jsonNumber	true
pressure	jsonNumber	true
sea_level	jsonNumber	true
temp	jsonNumber	true
temp_kf	jsonNumber	false
temp_max	jsonNumber	true
temp_min	jsonNumber	true

2.1.2.4.3.16.3 main properties

Property	Value
Business Name	main
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.17 Column grnd_level

2.1.2.4.3.17.1 grnd_level properties

Property	Value
Business Name	grnd_level
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	1025.75

2.1.2.4.3.18 Column humidity

2.1.2.4.3.18.1 humidity properties

Property	Value
Business Name	humidity
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	98

2.1.2.4.3.19 Column pressure

2.1.2.4.3.19.1 pressure properties

Property	Value
Business Name	pressure
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	1025.75

2.1.2.4.3.20 Column sea_level

2.1.2.4.3.20.1 sea_level properties

Property	Value
Business Name	sea_level
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	1040.95

2.1.2.4.3.21 Column temp

2.1.2.4.3.21.1 temp properties

Property	Value
Business Name	temp
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	274.338

2.1.2.4.3.22 Column temp_kf

2.1.2.4.3.22.1 temp_kf properties

Property	Value
Business Name	temp_kf
Type	jsonNumber
Required	false
Excl min	false
Excl max	false

2.1.2.4.3.23 Column temp_max

2.1.2.4.3.23.1 temp_max properties

Property	Value
Business Name	temp_max
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	274.338

2.1.2.4.3.24 Column temp_min

2.1.2.4.3.24.1 temp_min properties

Property	Value
Business Name	temp_min
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	274.338

2.1.2.4.3.25 Column snow

2.1.2.4.3.25.1 snow Tree Diagram

{object}

snow

{123}

2.1.2.4.3.25.2 snow Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
3h	jsonNumber	true

2.1.2.4.3.25.3 snow properties

Property	Value
Business Name	snow
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.26 Column 3h

2.1.2.4.3.26.1 3h properties

Property	Value
Business Name	3h
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	0.06

2.1.2.4.3.27 Column sys

2.1.2.4.3.27.1 sys Tree Diagram

{object}

sys

{ABC}

pod

2.1.2.4.3.27.2 sys Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
pod	jsonString	true

2.1.2.4.3.27.3 sys properties

Property	Value
Business Name	sys
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.28 Column pod

2.1.2.4.3.28.1 pod properties

Property	Value
Business Name	pod
Type	jsonString
Required	true
Sample	n

2.1.2.4.3.29 Column weather

2.1.2.4.3.29.1 weather Tree Diagram

[...]

weather

{...}

[0]*

2.1.2.4.3.29.2 weather Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
[0]	jsonObject	false

2.1.2.4.3.29.3 weather properties

Property	Value
Business Name	weather
Type	jsonArray
JSON Type	char
Required	true
Unique items	false
Additional items	true

2.1.2.4.3.30 Column [0]

2.1.2.4.3.30.1 [0] Tree Diagram

{object}

[0]

{ABC}

description

{ABC}

icon

{123}

{ABC}

main

2.1.2.4.3.30.2 [0] Hierarchy

Parent Column: weather

Child column(s):

Column	Type	Req	Key	Description	Comments
description	jsonString	true
icon	jsonString	true
id	jsonNumber	true
main	jsonString	true

2.1.2.4.3.30.3 [0] properties

Property	Value
Type	jsonObject
JSON Type	char
Additional properties	false

2.1.2.4.3.31 Column description

2.1.2.4.3.31.1 description properties

Property	Value
Business Name	description
Type	jsonString
Required	true
Sample	light rain

2.1.2.4.3.32 Column icon

2.1.2.4.3.32.1 icon properties

Property	Value
Business Name	icon
Type	jsonString
Required	true
Sample	10n

2.1.2.4.3.33 Column id

2.1.2.4.3.33.1 id properties

Property	Value
Business Name	id
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	500

2.1.2.4.3.34 Column main

2.1.2.4.3.34.1 main properties

Property	Value
Business Name	main
Type	jsonString
Required	true
Sample	Rain

2.1.2.4.3.35 Column wind

2.1.2.4.3.35.1 wind Tree Diagram

{object}

wind

{123}

deg

{123}

speed

2.1.2.4.3.35.2 wind Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
deg	jsonNumber	true
speed	jsonNumber	true

2.1.2.4.3.35.3 wind properties

Property	Value
Business Name	wind
Type	jsonObject
JSON Type	char
Required	true
Additional properties	false

2.1.2.4.3.36 Column deg

2.1.2.4.3.36.1 deg properties

Property	Value
Business Name	deg
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	280.007

2.1.2.4.3.37 Column speed

2.1.2.4.3.37.1 speed properties

Property	Value
Business Name	speed
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	2.81

2.1.2.4.3.38 Column rain

2.1.2.4.3.38.1 rain Tree Diagram

{object}

rain

{123}

2.1.2.4.3.38.2 rain Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
3h	jsonNumber	true

2.1.2.4.3.38.3 rain properties

Property	Value
Business Name	rain
Type	jsonObject
JSON Type	char
Required	false
Additional properties	false

2.1.2.4.3.39 Column 3h

2.1.2.4.3.39.1 3h properties

Property	Value
Business Name	3h
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	0.2

2.1.2.4.3.40 Column time

2.1.2.4.3.40.1 time properties

Property	Value
Business Name	time
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	1518548489

2.1.2.4.3.41 Column JSON2

2.1.2.4.3.41.1 JSON2 Tree Diagram

{ABC}

JSON2

{ABC}

videoid

{ABC}

added_date

{ABC}

description

{ABC}

location

{123}

location_type

[...]

metadata

{ABC}

name

[...]

2.1.2.4.3.41.2 JSON2 Hierarchy

Parent Column: JSONtbl

Child column(s):

Column	Type	Req	Key	Description	Comments
videoid	jsonString	true
added_date	jsonString	true
description	jsonString	true
location	jsonString	true
location_type	jsonNumber	true
metadata	jsonArray	true
name	jsonString	true
tags	jsonArray	true
userid	jsonString	true

2.1.2.4.3.41.3 JSON2 properties

Property	Value
Business Name	JSON2
Type	char
Subtype	nvarchar
JSON Types	object
Has max length	true
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.4.3.42 Column videoid

2.1.2.4.3.42.1 videoid properties

Property	Value
Business Name	videoid
Type	jsonString
Required	true
Sample	50554d6e-29bb-11e5-b345-feff819cdc9e

2.1.2.4.3.43 Column added_date

2.1.2.4.3.43.1 added_date properties

Property	Value
Business Name	added_date
Type	jsonString
Required	true
Sample	2011-02-03 04:05:00+0000

2.1.2.4.3.44 Column description

2.1.2.4.3.44.1 description properties

Property	Value
Business Name	description
Type	jsonString
Required	true
Sample	Lorem

2.1.2.4.3.45 Column location

2.1.2.4.3.45.1 location properties

Property	Value
Business Name	location
Type	jsonString
Required	true
Sample	Lorem

2.1.2.4.3.46 Column location_type

2.1.2.4.3.46.1 location_type properties

Property	Value
Business Name	location_type
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	30

2.1.2.4.3.47 Column metadata

2.1.2.4.3.47.1 metadata Tree Diagram

[...]

metadata

{...}

[0]*

2.1.2.4.3.47.2 metadata Hierarchy

Parent Column: JSON2

Child column(s):

Column	Type	Req	Key	Description	Comments
[0]	jsonObject	false

2.1.2.4.3.47.3 metadata properties

Property	Value
Business Name	metadata
Type	jsonArray
JSON Type	char
Required	true
Unique items	false
Additional items	true

2.1.2.4.3.48 Column [0]

2.1.2.4.3.48.1 [0] Tree Diagram

{object}

[0]

{123}

height

{123}

width

[...]

video_bit_rate

{ABC}

encoding

2.1.2.4.3.48.2 [0] Hierarchy

Parent Column: metadata

Child column(s):

Column	Type	Req	Key	Description	Comments
height	jsonNumber	true
width	jsonNumber	true
video_bit_rate	jsonArray	true
encoding	jsonString	true

2.1.2.4.3.48.3 [0] properties

Property	Value
Type	jsonObject
JSON Type	char
Additional properties	false

2.1.2.4.3.49 Column height

2.1.2.4.3.49.1 height properties

Property	Value
Business Name	height
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	-17

2.1.2.4.3.50 Column width

2.1.2.4.3.50.1 width properties

Property	Value
Business Name	width
Type	jsonNumber
Required	true
Excl min	false
Excl max	false
Sample	-17

2.1.2.4.3.51 Column video_bit_rate

2.1.2.4.3.51.1 video_bit_rate Tree Diagram

[...]

video_bit_rate

{ABC}

[0]*

2.1.2.4.3.51.2 video_bit_rate Hierarchy

Parent Column:

Child column(s):

Column	Type	Req	Key	Description	Comments
[0]	jsonString	false

2.1.2.4.3.51.3 video_bit_rate properties

Property	Value
Business Name	video_bit_rate
Type	jsonArray
JSON Type	char
Required	true
Unique items	false
Additional items	true

2.1.2.4.3.52 Column [0]

2.1.2.4.3.52.1 [0] properties

Property	Value
Type	jsonString
Sample	Lorem

2.1.2.4.3.53 Column encoding

2.1.2.4.3.53.1 encoding properties

Property	Value
Business Name	encoding
Type	jsonString
Required	true
Sample	Lorem

2.1.2.4.3.54 Column name

2.1.2.4.3.54.1 name properties

Property	Value
Business Name	name
Type	jsonString
Required	true
Sample	Lorem

2.1.2.4.3.55 Column tags

2.1.2.4.3.55.1 tags Tree Diagram

[...]

2.1.2.4.3.55.2 tags Hierarchy

Parent Column: JSON2

Child column(s):

Column	Type	Req	Key	Description	Comments
[0]	jsonString	false

2.1.2.4.3.55.3 tags properties

Property	Value
Business Name	tags
Type	jsonArray
JSON Type	char
Required	true
Unique items	false
Additional items	true

2.1.2.4.3.56 Column [0]

2.1.2.4.3.56.1 [0] properties

Property	Value
Type	jsonString
Sample	Lorem

2.1.2.4.3.57 Column userid

2.1.2.4.3.57.1 userid properties

Property	Value
Business Name	userid
Type	jsonString
Required	true
Sample	50554d6e-29bb-11e5-b345-feff819cdc9e

2.1.2.4.4 JSONtbl Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[JSONtbl] (
	[ID] NCHAR(10) NOT NULL,
	[JSONContent] NVARCHAR(MAX),
	[JSON2] NVARCHAR(MAX) NOT NULL,
	CONSTRAINT [PK_JSONtbl] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]
) GO

2.1.2.5 Table Product

2.1.2.5.1 Product Tree Diagram

{object}

Product

{123}

ProductID

{ABC}

Name

ref:

Name (m)

{ABC}

ProductNumber

{ABC}

Color

{123}

StandardCost

{123}

ListPrice

{ABC}

Size

{123}

Weight

{123}

ProductCategoryID

{123}

ProductModelID

{dt}

SellStartDate

{dt}

SellEndDate

{dt}

DiscontinuedDate

{BIN}

ThumbNailPhoto

{ABC}

ThumbnailPhotoFileName

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.5.2 Product Properties

Property	Value
Table	Product
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.5.3 Product Column

Column	Type	Req	Key	Description	Comments
ProductID	int	true
Name	nvarchar(50)	false
ProductNumber	nvarchar(25)	true
Color	nvarchar(15)	true
StandardCost	money	true
ListPrice	money	true
Size	nvarchar(5)	true
Weight	decimal(8, 2)	true
ProductCategoryID	int	true
ProductModelID	int	true
SellStartDate	datetime	true
SellEndDate	datetime	true
DiscontinuedDate	datetime	true
ThumbNailPhoto	varbinary(MAX)	true
ThumbnailPhotoFileName	nvarchar(50)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.5.3.1 Column ProductID

2.1.2.5.3.1.1 ProductID properties

Property	Value
Business Name	ProductID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	680

2.1.2.5.3.2 Column Name

2.1.2.5.3.2.1 Name properties

Property	Value
Business Name	Name
$ref	#model/definitions/Name
Reference type	model

2.1.2.5.3.3 Column ProductNumber

2.1.2.5.3.3.1 ProductNumber properties

Property	Value
Business Name	ProductNumber
Type	char
Subtype	nvarchar
JSON Types	string
Length	25
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_Product_ProductNumber
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Sample	FR-R92B-58

2.1.2.5.3.4 Column Color

2.1.2.5.3.4.1 Color properties

Property	Value
Business Name	Color
Type	char
Subtype	nvarchar
JSON Types	string
Length	15
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	Black

2.1.2.5.3.5 Column StandardCost

2.1.2.5.3.5.1 StandardCost properties

Property	Value
Business Name	StandardCost
Type	numeric
Subtype	money
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	1059.31

2.1.2.5.3.6 Column ListPrice

2.1.2.5.3.6.1 ListPrice properties

Property	Value
Business Name	ListPrice
Type	numeric
Subtype	money
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	1431.5

2.1.2.5.3.7 Column Size

2.1.2.5.3.7.1 Size properties

Property	Value
Business Name	Size
Type	char
Subtype	nvarchar
JSON Types	string
Length	5
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	58

2.1.2.5.3.8 Column Weight

2.1.2.5.3.8.1 Weight properties

Property	Value
Business Name	Weight
Type	numeric
Subtype	decimal
Precision	8
Scale	2
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	1016.04

2.1.2.5.3.9 Column ProductCategoryID

2.1.2.5.3.9.1 ProductCategoryID properties

Property	Value
Business Name	ProductCategoryID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	ProductCategory
Foreign field	ProductCategoryID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	18

2.1.2.5.3.10 Column ProductModelID

2.1.2.5.3.10.1 ProductModelID properties

Property	Value
Business Name	ProductModelID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	ProductModel
Foreign field	ProductModelID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	6

2.1.2.5.3.11 Column SellStartDate

2.1.2.5.3.11.1 SellStartDate properties

Property	Value
Business Name	SellStartDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	2002-06-01T00:00:00.000Z

2.1.2.5.3.12 Column SellEndDate

2.1.2.5.3.12.1 SellEndDate properties

Property	Value
Business Name	SellEndDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.5.3.13 Column DiscontinuedDate

2.1.2.5.3.13.1 DiscontinuedDate properties

Property	Value
Business Name	DiscontinuedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.5.3.14 Column ThumbNailPhoto

2.1.2.5.3.14.1 ThumbNailPhoto properties

Property	Value
Business Name	ThumbNailPhoto
Type	binary
Subtype	varbinary
Has max length	true
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.5.3.15 Column ThumbnailPhotoFileName

2.1.2.5.3.15.1 ThumbnailPhotoFileName properties

Property	Value
Business Name	ThumbnailPhotoFileName
Type	char
Subtype	nvarchar
JSON Types	string
Length	50
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	no_image_available_small.gif

2.1.2.5.3.16 Column rowguid

2.1.2.5.3.16.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_Product_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_Product_rowguid
Sample	43DD68D6-14A4-461F-9069-55309D90EA7E

2.1.2.5.3.17 Column ModifiedDate

2.1.2.5.3.17.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_Product_ModifiedDate
Sample	2008-03-11T10:01:36.826Z

2.1.2.5.4 Product Check Constraints

2.1.2.5.4.1 Check Constraint

Property
Name	CK_Product_ListPrice
Expression	([ListPrice]>=(0.00))
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.5.4.2 Check Constraint

Property
Name	CK_Product_SellEndDate
Expression	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.5.4.3 Check Constraint

Property
Name	CK_Product_StandardCost
Expression	([StandardCost]>=(0.00))
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.5.4.4 Check Constraint

Property
Name	CK_Product_Weight
Expression	([Weight]>(0.00))
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.5.5 Product Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[Product] (
	[ProductID] INT IDENTITY(1, 1) NOT NULL,
	[Name] [SalesLT].[Name],
	[ProductNumber] NVARCHAR(25) NOT NULL,
	[Color] NVARCHAR(15) NOT NULL,
	[StandardCost] MONEY NOT NULL,
	[ListPrice] MONEY NOT NULL,
	[Size] NVARCHAR(5) NOT NULL,
	[Weight] DECIMAL(8,2) NOT NULL,
	[ProductCategoryID] INT NOT NULL,
	[ProductModelID] INT NOT NULL,
	[SellStartDate] DATETIME NOT NULL,
	[SellEndDate] DATETIME NOT NULL,
	[DiscontinuedDate] DATETIME NOT NULL,
	[ThumbNailPhoto] VARBINARY(MAX) NOT NULL,
	[ThumbnailPhotoFileName] NVARCHAR(50) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_Product_ProductNumber] UNIQUE NONCLUSTERED ([ProductNumber] ASC) ON [PRIMARY],
	CONSTRAINT [AK_Product_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [CK_Product_ListPrice] CHECK ([ListPrice]>=(0.00)),
	CONSTRAINT [CK_Product_SellEndDate] CHECK ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL),
	CONSTRAINT [CK_Product_StandardCost] CHECK ([StandardCost]>=(0.00)),
	CONSTRAINT [CK_Product_Weight] CHECK ([Weight]>(0.00)),
	CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY (ProductModelID) REFERENCES [SalesLT].[ProductModel](ProductModelID),
	CONSTRAINT [FK_Product_ProductCategory_ProductCategoryID] FOREIGN KEY (ProductCategoryID) REFERENCES [SalesLT].[ProductCategory](ProductCategoryID)
) GO


ALTER TABLE [SalesLT].[Product] ADD CONSTRAINT [DF_Product_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[Product] ADD CONSTRAINT [DF_Product_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

2.1.2.6 Table ProductCategory

2.1.2.6.1 ProductCategory Tree Diagram

{object}

ProductCategory

{123}

ProductCategoryID

{123}

ParentProductCategoryID

{ABC}

Name

ref:

Name (m)

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.6.2 ProductCategory Properties

Property	Value
Table	ProductCategory
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.6.3 ProductCategory Column

Column	Type	Req	Key	Description	Comments
ProductCategoryID	int	true
ParentProductCategoryID	int	true
Name	nvarchar(50)	false
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.6.3.1 Column ProductCategoryID

2.1.2.6.3.1.1 ProductCategoryID properties

Property	Value
Business Name	ProductCategoryID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	1

2.1.2.6.3.2 Column ParentProductCategoryID

2.1.2.6.3.2.1 ParentProductCategoryID properties

Property	Value
Business Name	ParentProductCategoryID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	ProductCategory
Foreign field	ProductCategoryID
Relationship type	Foreign Key
Excl min	false
Excl max	false

2.1.2.6.3.3 Column Name

2.1.2.6.3.3.1 Name properties

Property	Value
Business Name	Name
$ref	#model/definitions/Name
Reference type	model

2.1.2.6.3.4 Column rowguid

2.1.2.6.3.4.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_ProductCategory_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_ProductCategory_rowguid
Sample	CFBDA25C-DF71-47A7-B81B-64EE161AA37C

2.1.2.6.3.5 Column ModifiedDate

2.1.2.6.3.5.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_ProductCategory_ModifiedDate
Sample	2002-06-01T00:00:00.000Z

2.1.2.6.4 ProductCategory Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[ProductCategory] (
	[ProductCategoryID] INT IDENTITY(1, 1) NOT NULL,
	[ParentProductCategoryID] INT NOT NULL,
	[Name] [SalesLT].[Name],
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductCategory_ProductCategoryID] PRIMARY KEY CLUSTERED ([ProductCategoryID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductCategory_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID] FOREIGN KEY (ParentProductCategoryID) REFERENCES [SalesLT].[ProductCategory](ProductCategoryID)
) GO


ALTER TABLE [SalesLT].[ProductCategory] ADD CONSTRAINT [DF_ProductCategory_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductCategory] ADD CONSTRAINT [DF_ProductCategory_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

2.1.2.7 Table ProductDescription

2.1.2.7.1 ProductDescription Tree Diagram

{object}

ProductDescription

{123}

ProductDescriptionID

{ABC}

Description

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.7.2 ProductDescription Properties

Property	Value
Table	ProductDescription
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.7.3 ProductDescription Column

Column	Type	Req	Key	Description	Comments
ProductDescriptionID	int	true
Description	nvarchar(400)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.7.3.1 Column ProductDescriptionID

2.1.2.7.3.1.1 ProductDescriptionID properties

Property	Value
Business Name	ProductDescriptionID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	3

2.1.2.7.3.2 Column Description

2.1.2.7.3.2.1 Description properties

Property	Value
Business Name	Description
Type	char
Subtype	nvarchar
JSON Types	string
Length	400
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	Chromoly steel.

2.1.2.7.3.3 Column rowguid

2.1.2.7.3.3.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_ProductDescription_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_ProductDescription_rowguid
Sample	301EED3A-1A82-4855-99CB-2AFE8290D641

2.1.2.7.3.4 Column ModifiedDate

2.1.2.7.3.4.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_ProductDescription_ModifiedDate
Sample	2007-06-01T00:00:00.000Z

2.1.2.7.4 ProductDescription Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[ProductDescription] (
	[ProductDescriptionID] INT IDENTITY(1, 1) NOT NULL,
	[Description] NVARCHAR(400) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductDescription_ProductDescriptionID] PRIMARY KEY CLUSTERED ([ProductDescriptionID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductDescription_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[ProductDescription] ADD CONSTRAINT [DF_ProductDescription_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductDescription] ADD CONSTRAINT [DF_ProductDescription_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

2.1.2.8 Table ProductModel

2.1.2.8.1 ProductModel Tree Diagram

{object}

ProductModel

{123}

ProductModelID

{ABC}

Name

ref:

Name (m)

{xml}

CatalogDescription

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.8.2 ProductModel Properties

Property	Value
Table	ProductModel
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.8.3 ProductModel Column

Column	Type	Req	Key	Description	Comments
ProductModelID	int	true
Name	nvarchar(50)	false
CatalogDescription	xml	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.8.3.1 Column ProductModelID

2.1.2.8.3.1.1 ProductModelID properties

Property	Value
Business Name	ProductModelID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	1

2.1.2.8.3.2 Column Name

2.1.2.8.3.2.1 Name properties

Property	Value
Business Name	Name
$ref	#model/definitions/Name
Reference type	model

2.1.2.8.3.3 Column CatalogDescription

2.1.2.8.3.3.1 CatalogDescription properties

Property	Value
Business Name	CatalogDescription
Type	xml
Not null	true
Sparse	false

2.1.2.8.3.4 Column rowguid

2.1.2.8.3.4.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_ProductModel_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_ProductModel_rowguid
Sample	29321D47-1E4C-4AAC-887C-19634328C25E

2.1.2.8.3.5 Column ModifiedDate

2.1.2.8.3.5.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_ProductModel_ModifiedDate
Sample	2007-06-01T00:00:00.000Z

2.1.2.8.4 ProductModel Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[ProductModel] (
	[ProductModelID] INT IDENTITY(1, 1) NOT NULL,
	[Name] [SalesLT].[Name],
	[CatalogDescription] XML NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductModel_ProductModelID] PRIMARY KEY CLUSTERED ([ProductModelID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductModel_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[ProductModel] ADD CONSTRAINT [DF_ProductModel_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductModel] ADD CONSTRAINT [DF_ProductModel_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

2.1.2.9 Table ProductModelProductDescription

2.1.2.9.1 ProductModelProductDescription Tree Diagram

{object}

ProductModelProductDescription

{123}

ProductModelID

(I1.1)

{123}

ProductDescriptionID

(I1.2)

{ABC}

Culture

(I1.3)

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.9.2 ProductModelProductDescription Properties

Property	Value
Table	ProductModelProductDescription
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.9.3 ProductModelProductDescription Column

Column	Type	Req	Key	Description	Comments
ProductModelID	int	true
ProductDescriptionID	int	true
Culture	nchar(6)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.9.3.1 Column ProductModelID

2.1.2.9.3.1.1 ProductModelID properties

Property	Value
Business Name	ProductModelID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	true
Foreign table	ProductModel
Foreign field	ProductModelID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	1

2.1.2.9.3.2 Column ProductDescriptionID

2.1.2.9.3.2.1 ProductDescriptionID properties

Property	Value
Business Name	ProductDescriptionID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	true
Foreign table	ProductDescription
Foreign field	ProductDescriptionID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	1199

2.1.2.9.3.3 Column Culture

2.1.2.9.3.3.1 Culture properties

Property	Value
Business Name	Culture
Type	char
Subtype	nchar
Length	6
Not null	true
Sparse	false
Primary key	true
Sample	en

2.1.2.9.3.4 Column rowguid

2.1.2.9.3.4.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_ProductModelProductDescription_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_ProductModelProductDescription_rowguid
Sample	4D00B649-027A-4F99-A380-F22A46EC8638

2.1.2.9.3.5 Column ModifiedDate

2.1.2.9.3.5.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_ProductModelProductDescription_ModifiedDate
Sample	2007-06-01T00:00:00.000Z

2.1.2.9.4 ProductModelProductDescription Composite keys

2.1.2.9.4.1 Primary key

Property
Constraint name	PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture
Key
	ProductModelID
	ProductDescriptionID
	Culture
Partition name	PRIMARY
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered	true
Data compression	NONE

2.1.2.9.5 ProductModelProductDescription Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[ProductModelProductDescription] (
	[ProductModelID] INT NOT NULL,
	[ProductDescriptionID] INT NOT NULL,
	[Culture] NCHAR(6) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture] PRIMARY KEY CLUSTERED ([ProductModelID] ASC, [ProductDescriptionID] ASC, [Culture] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductModelProductDescription_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [FK_ProductModelProductDescription_ProductModel_ProductModelID] FOREIGN KEY (ProductModelID) REFERENCES [SalesLT].[ProductModel](ProductModelID),
	CONSTRAINT [FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID] FOREIGN KEY (ProductDescriptionID) REFERENCES [SalesLT].[ProductDescription](ProductDescriptionID)
) GO


ALTER TABLE [SalesLT].[ProductModelProductDescription] ADD CONSTRAINT [DF_ProductModelProductDescription_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductModelProductDescription] ADD CONSTRAINT [DF_ProductModelProductDescription_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

2.1.2.10 Table SalesOrderDetail

2.1.2.10.1 SalesOrderDetail Tree Diagram

{object}

SalesOrderDetail

{123}

SalesOrderID

(I1.1)

{123}

SalesOrderDetailID

(I1.2)

{123}

OrderQty

{123}

ProductID

(I1.1)

{123}

UnitPrice

{123}

UnitPriceDiscount

{123}

LineTotal

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.10.2 SalesOrderDetail Properties

Property	Value
Table	SalesOrderDetail
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.10.3 SalesOrderDetail Column

Column	Type	Req	Key	Description	Comments
SalesOrderID	int	true
SalesOrderDetailID	int	true
OrderQty	smallint	true
ProductID	int	true
UnitPrice	money	true
UnitPriceDiscount	money	true
LineTotal	numeric(38, 6)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.10.3.1 Column SalesOrderID

2.1.2.10.3.1.1 SalesOrderID properties

Property	Value
Business Name	SalesOrderID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	true
Foreign table	SalesOrderHeader
Foreign field	SalesOrderID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	71774

2.1.2.10.3.2 Column SalesOrderDetailID

2.1.2.10.3.2.1 SalesOrderDetailID properties

Property	Value
Business Name	SalesOrderDetailID
Type	numeric
Subtype	int
Not null	true
Identity	[object Object]
Sparse	false
Primary key	true
Excl min	false
Excl max	false
Sample	110562

2.1.2.10.3.3 Column OrderQty

2.1.2.10.3.3.1 OrderQty properties

Property	Value
Business Name	OrderQty
Type	numeric
Subtype	smallint
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	1

2.1.2.10.3.4 Column ProductID

2.1.2.10.3.4.1 ProductID properties

Property	Value
Business Name	ProductID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	Product
Foreign field	ProductID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	836

2.1.2.10.3.5 Column UnitPrice

2.1.2.10.3.5.1 UnitPrice properties

Property	Value
Business Name	UnitPrice
Type	numeric
Subtype	money
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	356.898

2.1.2.10.3.6 Column UnitPriceDiscount

2.1.2.10.3.6.1 UnitPriceDiscount properties

Property	Value
Business Name	UnitPriceDiscount
Type	numeric
Subtype	money
Default	((0.0))
Default constraint name	DF_SalesOrderDetail_UnitPriceDiscount
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	0

2.1.2.10.3.7 Column LineTotal

2.1.2.10.3.7.1 LineTotal properties

Property	Value
Business Name	LineTotal
Type	numeric
Subtype	numeric
Precision	38
Scale	6
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	356.898

2.1.2.10.3.8 Column rowguid

2.1.2.10.3.8.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_SalesOrderDetail_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_SalesOrderDetail_rowguid
Sample	E3A1994C-7A68-4CE8-96A3-77FDD3BBD730

2.1.2.10.3.9 Column ModifiedDate

2.1.2.10.3.9.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_SalesOrderDetail_ModifiedDate
Sample	2008-06-01T00:00:00.000Z

2.1.2.10.4 SalesOrderDetail Composite keys

2.1.2.10.4.1 Primary key

Property
Constraint name	PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
Key
	SalesOrderID
	SalesOrderDetailID
Partition name	PRIMARY
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered	true
Data compression	NONE

2.1.2.10.5 SalesOrderDetail Indexes

2.1.2.10.5.1 Index

Property
Name	IX_SalesOrderDetail_ProductID
Type	Index
Unique
Clustered
Keys
	ProductID
Include non-key columns
Description
Pad index
Fill factor
Ignore duplicate key
Included columns
Statistics no recompute
Statistics incremental
Allow row locks	true
Allow page locks	true
Optimize for sequential key
Compression delay (minutes)
Data compression	NONE
Comments

2.1.2.10.6 SalesOrderDetail Check Constraints

2.1.2.10.6.1 Check Constraint

Property
Name	CK_SalesOrderDetail_OrderQty
Expression	([OrderQty]>(0))
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.10.6.2 Check Constraint

Property
Name	CK_SalesOrderDetail_UnitPrice
Expression	([UnitPrice]>=(0.00))
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.10.6.3 Check Constraint

Property
Name	CK_SalesOrderDetail_UnitPriceDiscount
Expression	([UnitPriceDiscount]>=(0.00))
Description
Check Existing Data
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.10.7 SalesOrderDetail Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[SalesOrderDetail] (
	[SalesOrderID] INT NOT NULL,
	[SalesOrderDetailID] INT IDENTITY(1, 1) NOT NULL,
	[OrderQty] SMALLINT NOT NULL,
	[ProductID] INT NOT NULL,
	[UnitPrice] MONEY NOT NULL,
	[UnitPriceDiscount] MONEY NOT NULL,
	[LineTotal] NUMERIC(38,6) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED ([SalesOrderID] ASC, [SalesOrderDetailID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_SalesOrderDetail_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK ([OrderQty]>(0)),
	CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK ([UnitPrice]>=(0.00)),
	CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK ([UnitPriceDiscount]>=(0.00)),
	CONSTRAINT [FK_SalesOrderDetail_Product_ProductID] FOREIGN KEY (ProductID) REFERENCES [SalesLT].[Product](ProductID),
	CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY (SalesOrderID) REFERENCES [SalesLT].[SalesOrderHeader](SalesOrderID)
) GO


ALTER TABLE [SalesLT].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)) FOR [UnitPriceDiscount] GO

ALTER TABLE [SalesLT].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_SalesOrderDetail_ProductID
	ON [SalesLT].[SalesOrderDetail] ( [ProductID] ) GO

2.1.2.11 Table SalesOrderHeader

2.1.2.11.1 SalesOrderHeader Tree Diagram

{object}

SalesOrderHeader

{123}

SalesOrderID

{123}

RevisionNumber

{dt}

OrderDate

{dt}

DueDate

{dt}

ShipDate

{123}

Status

{123}

OnlineOrderFlag

ref:

Flag (m)

{ABC}

SalesOrderNumber

{ABC}

PurchaseOrderNumber

ref:

OrderNumber (m)

{ABC}

AccountNumber

ref:

AccountNumber (m)

{123}

CustomerID

(I1.1)

{123}

ShipToAddressID

{123}

BillToAddressID

{ABC}

ShipMethod

{ABC}

CreditCardApprovalCode

{123}

SubTotal

{123}

TaxAmt

{123}

Freight

{123}

TotalDue

{ABC}

Comment

{UUID}

rowguid

{dt}

ModifiedDate

2.1.2.11.2 SalesOrderHeader Properties

Property	Value
Table	SalesOrderHeader
Technical name
Id
Schema	SalesLT
Additional properties	false
Comments
Memory optimized	false
Remarks

2.1.2.11.3 SalesOrderHeader Column

Column	Type	Req	Key	Description	Comments
SalesOrderID	int	true
RevisionNumber	tinyint	true
OrderDate	datetime	true
DueDate	datetime	true
ShipDate	datetime	true
Status	tinyint	true
OnlineOrderFlag	bit	false
SalesOrderNumber	nvarchar(25)	true
PurchaseOrderNumber	nvarchar(25)	false
AccountNumber	nvarchar(15)	false
CustomerID	int	true
ShipToAddressID	int	true
BillToAddressID	int	true
ShipMethod	nvarchar(50)	true
CreditCardApprovalCode	varchar(15)	true
SubTotal	money	true
TaxAmt	money	true
Freight	money	true
TotalDue	money	true
Comment	nvarchar(MAX)	true
rowguid	uniqueidentifier	true
ModifiedDate	datetime	true

2.1.2.11.3.1 Column SalesOrderID

2.1.2.11.3.1.1 SalesOrderID properties

Property	Value
Business Name	SalesOrderID
Type	numeric
Subtype	int
Default	(NEXT VALUE FOR [SalesLT].[SalesOrderNumber])
Default constraint name	DF_SalesOrderHeader_OrderID
Not null	true
Sparse	false
Primary key	true
Primary key options	[object Object]
Unique	false
Excl min	false
Excl max	false
Sample	71774

2.1.2.11.3.2 Column RevisionNumber

2.1.2.11.3.2.1 RevisionNumber properties

Property	Value
Business Name	RevisionNumber
Type	numeric
Subtype	tinyint
Default	((0))
Default constraint name	DF_SalesOrderHeader_RevisionNumber
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	2

2.1.2.11.3.3 Column OrderDate

2.1.2.11.3.3.1 OrderDate properties

Property	Value
Business Name	OrderDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_SalesOrderHeader_OrderDate
Sample	2008-06-01T00:00:00.000Z

2.1.2.11.3.4 Column DueDate

2.1.2.11.3.4.1 DueDate properties

Property	Value
Business Name	DueDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	2008-06-13T00:00:00.000Z

2.1.2.11.3.5 Column ShipDate

2.1.2.11.3.5.1 ShipDate properties

Property	Value
Business Name	ShipDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	2008-06-08T00:00:00.000Z

2.1.2.11.3.6 Column Status

2.1.2.11.3.6.1 Status properties

Property	Value
Business Name	Status
Type	numeric
Subtype	tinyint
Default	1
Default constraint name	DF_SalesOrderHeader_Status
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	5

2.1.2.11.3.7 Column OnlineOrderFlag

2.1.2.11.3.7.1 OnlineOrderFlag properties

Property	Value
Business Name	OnlineOrderFlag
$ref	#model/definitions/Flag
Reference type	model

2.1.2.11.3.8 Column SalesOrderNumber

2.1.2.11.3.8.1 SalesOrderNumber properties

Property	Value
Business Name	SalesOrderNumber
Type	char
Subtype	nvarchar
JSON Types	string
Length	25
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_SalesOrderHeader_SalesOrderNumber
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Sample	SO71774

2.1.2.11.3.9 Column PurchaseOrderNumber

2.1.2.11.3.9.1 PurchaseOrderNumber properties

Property	Value
Business Name	PurchaseOrderNumber
$ref	#model/definitions/OrderNumber
Reference type	model

2.1.2.11.3.10 Column AccountNumber

2.1.2.11.3.10.1 AccountNumber properties

Property	Value
Business Name	AccountNumber
$ref	#model/definitions/AccountNumber
Reference type	model

2.1.2.11.3.11 Column CustomerID

2.1.2.11.3.11.1 CustomerID properties

Property	Value
Business Name	CustomerID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	Customer
Foreign field	CustomerID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	29847

2.1.2.11.3.12 Column ShipToAddressID

2.1.2.11.3.12.1 ShipToAddressID properties

Property	Value
Business Name	ShipToAddressID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	Address
Foreign field	AddressID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	1092

2.1.2.11.3.13 Column BillToAddressID

2.1.2.11.3.13.1 BillToAddressID properties

Property	Value
Business Name	BillToAddressID
Type	numeric
Subtype	int
Not null	true
Sparse	false
Primary key	false
Unique	false
Foreign table	Address
Foreign field	AddressID
Relationship type	Foreign Key
Excl min	false
Excl max	false
Sample	1092

2.1.2.11.3.14 Column ShipMethod

2.1.2.11.3.14.1 ShipMethod properties

Property	Value
Business Name	ShipMethod
Type	char
Subtype	nvarchar
JSON Types	string
Length	50
Not null	true
Sparse	false
Primary key	false
Unique	false
Sample	CARGO TRANSPORT 5

2.1.2.11.3.15 Column CreditCardApprovalCode

2.1.2.11.3.15.1 CreditCardApprovalCode properties

Property	Value
Business Name	CreditCardApprovalCode
Type	char
Subtype	varchar
JSON Types	string
Length	15
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.11.3.16 Column SubTotal

2.1.2.11.3.16.1 SubTotal properties

Property	Value
Business Name	SubTotal
Type	numeric
Subtype	money
Default	((0.00))
Default constraint name	DF_SalesOrderHeader_SubTotal
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	880.3484

2.1.2.11.3.17 Column TaxAmt

2.1.2.11.3.17.1 TaxAmt properties

Property	Value
Business Name	TaxAmt
Type	numeric
Subtype	money
Default	((0.00))
Default constraint name	DF_SalesOrderHeader_TaxAmt
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	70.4279

2.1.2.11.3.18 Column Freight

2.1.2.11.3.18.1 Freight properties

Property	Value
Business Name	Freight
Type	numeric
Subtype	money
Default	((0.00))
Default constraint name	DF_SalesOrderHeader_Freight
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	22.0087

2.1.2.11.3.19 Column TotalDue

2.1.2.11.3.19.1 TotalDue properties

Property	Value
Business Name	TotalDue
Type	numeric
Subtype	money
Not null	true
Sparse	false
Primary key	false
Unique	false
Excl min	false
Excl max	false
Sample	972.785

2.1.2.11.3.20 Column Comment

2.1.2.11.3.20.1 Comment properties

Property	Value
Business Name	Comment
Type	char
Subtype	nvarchar
JSON Types	string
Has max length	true
Not null	true
Sparse	false
Primary key	false
Unique	false

2.1.2.11.3.21 Column rowguid

2.1.2.11.3.21.1 rowguid properties

Property	Value
Business Name	rowguid
Type	uniqueidentifier
Not null	true
Sparse	false
Primary key	false
Unique	true
Unique key options
[1] Constraint name	AK_SalesOrderHeader_rowguid
Partition name	PRIMARY
Order	ASC
Statistics no recompute
Statistics incremental
Ignore duplicate
Allow row locks	true
Allow page locks	true
Padded
Fill factor
Optimize for sequential key
Clustered
Data compression	NONE
Default	(newid())
Default constraint name	DF_SalesOrderHeader_rowguid
Sample	89E42CDC-8506-48A2-B89B-EB3E64E3554E

2.1.2.11.3.22 Column ModifiedDate

2.1.2.11.3.22.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
Type	datetime
Subtype	datetime
Not null	true
Sparse	false
Primary key	false
Unique	false
Default	(getdate())
Default constraint name	DF_SalesOrderHeader_ModifiedDate
Sample	2008-06-08T00:00:00.000Z

2.1.2.11.4 SalesOrderHeader Indexes

2.1.2.11.4.1 Index

Property
Name	IX_SalesOrderHeader_CustomerID
Type	Index
Unique
Clustered
Keys
	CustomerID
Include non-key columns
Description
Pad index
Fill factor
Ignore duplicate key
Included columns
Statistics no recompute
Statistics incremental
Allow row locks	true
Allow page locks	true
Optimize for sequential key
Compression delay (minutes)
Data compression	NONE
Comments

2.1.2.11.5 SalesOrderHeader Check Constraints

2.1.2.11.5.1 Check Constraint

Property
Name	CK_SalesOrderHeader_DueDate
Expression	([DueDate]>=[OrderDate])
Description
Check Existing Data	true
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.11.5.2 Check Constraint

Property
Name	CK_SalesOrderHeader_Freight
Expression	([Freight]>=(0.00))
Description
Check Existing Data	true
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.11.5.3 Check Constraint

Property
Name	CK_SalesOrderHeader_ShipDate
Expression	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
Description
Check Existing Data	true
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.11.5.4 Check Constraint

Property
Name	CK_SalesOrderHeader_Status
Expression	([Status]>=(0) AND [Status]<=(8))
Description
Check Existing Data	true
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.11.5.5 Check Constraint

Property
Name	CK_SalesOrderHeader_SubTotal
Expression	([SubTotal]>=(0.00))
Description
Check Existing Data	true
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.11.5.6 Check Constraint

Property
Name	CK_SalesOrderHeader_TaxAmt
Expression	([TaxAmt]>=(0.00))
Description
Check Existing Data	true
Enforce for Upserts	true
Enforce for Replication	true
Comments

2.1.2.11.6 SalesOrderHeader Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[SalesOrderHeader] (
	[SalesOrderID] INT NOT NULL,
	[RevisionNumber] TINYINT NOT NULL,
	[OrderDate] DATETIME NOT NULL,
	[DueDate] DATETIME NOT NULL,
	[ShipDate] DATETIME NOT NULL,
	[Status] TINYINT NOT NULL,
	[OnlineOrderFlag] [SalesLT].[Flag],
	[SalesOrderNumber] NVARCHAR(25) NOT NULL,
	[PurchaseOrderNumber] [SalesLT].[OrderNumber],
	[AccountNumber] [SalesLT].[AccountNumber],
	[CustomerID] INT NOT NULL,
	[ShipToAddressID] INT NOT NULL,
	[BillToAddressID] INT NOT NULL,
	[ShipMethod] NVARCHAR(50) NOT NULL,
	[CreditCardApprovalCode] VARCHAR(15) NOT NULL,
	[SubTotal] MONEY NOT NULL,
	[TaxAmt] MONEY NOT NULL,
	[Freight] MONEY NOT NULL,
	[TotalDue] MONEY NOT NULL,
	[Comment] NVARCHAR(MAX) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED ([SalesOrderID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_SalesOrderHeader_SalesOrderNumber] UNIQUE NONCLUSTERED ([SalesOrderNumber] ASC) ON [PRIMARY],
	CONSTRAINT [AK_SalesOrderHeader_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [CK_SalesOrderHeader_DueDate] CHECK ([DueDate]>=[OrderDate]),
	CONSTRAINT [CK_SalesOrderHeader_Freight] CHECK ([Freight]>=(0.00)),
	CONSTRAINT [CK_SalesOrderHeader_ShipDate] CHECK ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL),
	CONSTRAINT [CK_SalesOrderHeader_Status] CHECK ([Status]>=(0) AND [Status]<=(8)),
	CONSTRAINT [CK_SalesOrderHeader_SubTotal] CHECK ([SubTotal]>=(0.00)),
	CONSTRAINT [CK_SalesOrderHeader_TaxAmt] CHECK ([TaxAmt]>=(0.00)),
	CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY (CustomerID) REFERENCES [SalesLT].[Customer](CustomerID),
	CONSTRAINT [FK_SalesOrderHeader_Address_BillTo_AddressID] FOREIGN KEY (BillToAddressID) REFERENCES [SalesLT].[Address](AddressID),
	CONSTRAINT [FK_SalesOrderHeader_Address_ShipTo_AddressID] FOREIGN KEY (ShipToAddressID) REFERENCES [SalesLT].[Address](AddressID)
) GO


ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_OrderID] DEFAULT (NEXT VALUE FOR [SalesLT].[SalesOrderNumber]) FOR [SalesOrderID] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)) FOR [RevisionNumber] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_OrderDate] DEFAULT (getdate()) FOR [OrderDate] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_Status] DEFAULT (1) FOR [Status] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_SubTotal] DEFAULT ((0.00)) FOR [SubTotal] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_TaxAmt] DEFAULT ((0.00)) FOR [TaxAmt] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_Freight] DEFAULT ((0.00)) FOR [Freight] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_SalesOrderHeader_CustomerID
	ON [SalesLT].[SalesOrderHeader] ( [CustomerID] ) GO

2.1.3 SalesLT Target Script

CREATE DATABASE [HackDBSQL];
GO
USE [HackDBSQL] GO

CREATE SCHEMA [SalesLT];
GO

CREATE TYPE [SalesLT].[Name] FROM NVARCHAR(50) NOT NULL GO

CREATE TYPE [SalesLT].[NameStyle] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[Phone] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[Flag] FROM BIT NOT NULL GO

CREATE TYPE [SalesLT].[OrderNumber] FROM NVARCHAR(25) GO

CREATE TYPE [SalesLT].[AccountNumber] FROM NVARCHAR(15) GO

CREATE TABLE [SalesLT].[Address] (
	[AddressID] INT IDENTITY(1, 1) NOT NULL,
	[AddressLine1] NVARCHAR(60) NOT NULL,
	[AddressLine2] NVARCHAR(60) NOT NULL,
	[City] NVARCHAR(30) NOT NULL,
	[StateProvince] [SalesLT].[Name],
	[CountryRegion] [SalesLT].[Name],
	[PostalCode] NVARCHAR(15) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC) WITH (
		IGNORE_DUP_KEY = ON,
		STATISTICS_NORECOMPUTE = ON
	) ON [PRIMARY],
	CONSTRAINT [AK_Address_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[Address] ADD CONSTRAINT [DF_Address_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[Address] ADD CONSTRAINT [DF_Address_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion
	ON [SalesLT].[Address] ( [AddressLine1], [AddressLine2], [City], [StateProvince], [PostalCode], [CountryRegion] ) GO

CREATE INDEX IX_Address_StateProvince
	ON [SalesLT].[Address] ( [StateProvince] ) GO

CREATE TABLE [SalesLT].[Customer] (
	[CustomerID] INT IDENTITY(1, 1) NOT NULL,
	[NameStyle] [SalesLT].[NameStyle],
	[Title] NVARCHAR(8) NOT NULL,
	[FirstName] [SalesLT].[Name],
	[MiddleName] [SalesLT].[Name],
	[LastName] [SalesLT].[Name],
	[Suffix] NVARCHAR(10) NOT NULL,
	[CompanyName] NVARCHAR(128) NOT NULL,
	[SalesPerson] NVARCHAR(256) NOT NULL,
	[EmailAddress] NVARCHAR(50) NOT NULL,
	[Phone] [SalesLT].[Phone],
	[PasswordHash] VARCHAR(128) NOT NULL,
	[PasswordSalt] VARCHAR(10) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_Customer_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[Customer] ADD CONSTRAINT [DF_Customer_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[Customer] ADD CONSTRAINT [DF_Customer_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_Customer_EmailAddress
	ON [SalesLT].[Customer] ( [EmailAddress] ) GO

CREATE TABLE [SalesLT].[CustomerAddress] (
	[CustomerID] INT NOT NULL,
	[AddressID] INT NOT NULL,
	[AddressType] [SalesLT].[Name],
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_CustomerAddress_CustomerID_AddressID] PRIMARY KEY CLUSTERED ([CustomerID] ASC, [AddressID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_CustomerAddress_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [FK_CustomerAddress_Customer_CustomerID] FOREIGN KEY (CustomerID) REFERENCES [SalesLT].[Customer](CustomerID),
	CONSTRAINT [FK_CustomerAddress_Address_AddressID] FOREIGN KEY (AddressID) REFERENCES [SalesLT].[Address](AddressID)
) GO


ALTER TABLE [SalesLT].[CustomerAddress] ADD CONSTRAINT [DF_CustomerAddress_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[CustomerAddress] ADD CONSTRAINT [DF_CustomerAddress_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE TABLE [SalesLT].[JSONtbl] (
	[ID] NCHAR(10) NOT NULL,
	[JSONContent] NVARCHAR(MAX),
	[JSON2] NVARCHAR(MAX) NOT NULL,
	CONSTRAINT [PK_JSONtbl] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]
) GO

CREATE TABLE [SalesLT].[ProductCategory] (
	[ProductCategoryID] INT IDENTITY(1, 1) NOT NULL,
	[ParentProductCategoryID] INT NOT NULL,
	[Name] [SalesLT].[Name],
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductCategory_ProductCategoryID] PRIMARY KEY CLUSTERED ([ProductCategoryID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductCategory_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID] FOREIGN KEY (ParentProductCategoryID) REFERENCES [SalesLT].[ProductCategory](ProductCategoryID)
) GO


ALTER TABLE [SalesLT].[ProductCategory] ADD CONSTRAINT [DF_ProductCategory_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductCategory] ADD CONSTRAINT [DF_ProductCategory_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE TABLE [SalesLT].[ProductModel] (
	[ProductModelID] INT IDENTITY(1, 1) NOT NULL,
	[Name] [SalesLT].[Name],
	[CatalogDescription] XML NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductModel_ProductModelID] PRIMARY KEY CLUSTERED ([ProductModelID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductModel_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[ProductModel] ADD CONSTRAINT [DF_ProductModel_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductModel] ADD CONSTRAINT [DF_ProductModel_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE TABLE [SalesLT].[ProductDescription] (
	[ProductDescriptionID] INT IDENTITY(1, 1) NOT NULL,
	[Description] NVARCHAR(400) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductDescription_ProductDescriptionID] PRIMARY KEY CLUSTERED ([ProductDescriptionID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductDescription_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY]
) GO


ALTER TABLE [SalesLT].[ProductDescription] ADD CONSTRAINT [DF_ProductDescription_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductDescription] ADD CONSTRAINT [DF_ProductDescription_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE TABLE [SalesLT].[Product] (
	[ProductID] INT IDENTITY(1, 1) NOT NULL,
	[Name] [SalesLT].[Name],
	[ProductNumber] NVARCHAR(25) NOT NULL,
	[Color] NVARCHAR(15) NOT NULL,
	[StandardCost] MONEY NOT NULL,
	[ListPrice] MONEY NOT NULL,
	[Size] NVARCHAR(5) NOT NULL,
	[Weight] DECIMAL(8,2) NOT NULL,
	[ProductCategoryID] INT NOT NULL,
	[ProductModelID] INT NOT NULL,
	[SellStartDate] DATETIME NOT NULL,
	[SellEndDate] DATETIME NOT NULL,
	[DiscontinuedDate] DATETIME NOT NULL,
	[ThumbNailPhoto] VARBINARY(MAX) NOT NULL,
	[ThumbnailPhotoFileName] NVARCHAR(50) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_Product_ProductNumber] UNIQUE NONCLUSTERED ([ProductNumber] ASC) ON [PRIMARY],
	CONSTRAINT [AK_Product_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [CK_Product_ListPrice] CHECK ([ListPrice]>=(0.00)),
	CONSTRAINT [CK_Product_SellEndDate] CHECK ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL),
	CONSTRAINT [CK_Product_StandardCost] CHECK ([StandardCost]>=(0.00)),
	CONSTRAINT [CK_Product_Weight] CHECK ([Weight]>(0.00)),
	CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY (ProductModelID) REFERENCES [SalesLT].[ProductModel](ProductModelID),
	CONSTRAINT [FK_Product_ProductCategory_ProductCategoryID] FOREIGN KEY (ProductCategoryID) REFERENCES [SalesLT].[ProductCategory](ProductCategoryID)
) GO


ALTER TABLE [SalesLT].[Product] ADD CONSTRAINT [DF_Product_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[Product] ADD CONSTRAINT [DF_Product_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE TABLE [SalesLT].[ProductModelProductDescription] (
	[ProductModelID] INT NOT NULL,
	[ProductDescriptionID] INT NOT NULL,
	[Culture] NCHAR(6) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture] PRIMARY KEY CLUSTERED ([ProductModelID] ASC, [ProductDescriptionID] ASC, [Culture] ASC) ON [PRIMARY],
	CONSTRAINT [AK_ProductModelProductDescription_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [FK_ProductModelProductDescription_ProductModel_ProductModelID] FOREIGN KEY (ProductModelID) REFERENCES [SalesLT].[ProductModel](ProductModelID),
	CONSTRAINT [FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID] FOREIGN KEY (ProductDescriptionID) REFERENCES [SalesLT].[ProductDescription](ProductDescriptionID)
) GO


ALTER TABLE [SalesLT].[ProductModelProductDescription] ADD CONSTRAINT [DF_ProductModelProductDescription_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[ProductModelProductDescription] ADD CONSTRAINT [DF_ProductModelProductDescription_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE TABLE [SalesLT].[SalesOrderHeader] (
	[SalesOrderID] INT NOT NULL,
	[RevisionNumber] TINYINT NOT NULL,
	[OrderDate] DATETIME NOT NULL,
	[DueDate] DATETIME NOT NULL,
	[ShipDate] DATETIME NOT NULL,
	[Status] TINYINT NOT NULL,
	[OnlineOrderFlag] [SalesLT].[Flag],
	[SalesOrderNumber] NVARCHAR(25) NOT NULL,
	[PurchaseOrderNumber] [SalesLT].[OrderNumber],
	[AccountNumber] [SalesLT].[AccountNumber],
	[CustomerID] INT NOT NULL,
	[ShipToAddressID] INT NOT NULL,
	[BillToAddressID] INT NOT NULL,
	[ShipMethod] NVARCHAR(50) NOT NULL,
	[CreditCardApprovalCode] VARCHAR(15) NOT NULL,
	[SubTotal] MONEY NOT NULL,
	[TaxAmt] MONEY NOT NULL,
	[Freight] MONEY NOT NULL,
	[TotalDue] MONEY NOT NULL,
	[Comment] NVARCHAR(MAX) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED ([SalesOrderID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_SalesOrderHeader_SalesOrderNumber] UNIQUE NONCLUSTERED ([SalesOrderNumber] ASC) ON [PRIMARY],
	CONSTRAINT [AK_SalesOrderHeader_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [CK_SalesOrderHeader_DueDate] CHECK ([DueDate]>=[OrderDate]),
	CONSTRAINT [CK_SalesOrderHeader_Freight] CHECK ([Freight]>=(0.00)),
	CONSTRAINT [CK_SalesOrderHeader_ShipDate] CHECK ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL),
	CONSTRAINT [CK_SalesOrderHeader_Status] CHECK ([Status]>=(0) AND [Status]<=(8)),
	CONSTRAINT [CK_SalesOrderHeader_SubTotal] CHECK ([SubTotal]>=(0.00)),
	CONSTRAINT [CK_SalesOrderHeader_TaxAmt] CHECK ([TaxAmt]>=(0.00)),
	CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY (CustomerID) REFERENCES [SalesLT].[Customer](CustomerID),
	CONSTRAINT [FK_SalesOrderHeader_Address_BillTo_AddressID] FOREIGN KEY (BillToAddressID) REFERENCES [SalesLT].[Address](AddressID),
	CONSTRAINT [FK_SalesOrderHeader_Address_ShipTo_AddressID] FOREIGN KEY (ShipToAddressID) REFERENCES [SalesLT].[Address](AddressID)
) GO


ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_OrderID] DEFAULT (NEXT VALUE FOR [SalesLT].[SalesOrderNumber]) FOR [SalesOrderID] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)) FOR [RevisionNumber] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_OrderDate] DEFAULT (getdate()) FOR [OrderDate] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_Status] DEFAULT (1) FOR [Status] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_SubTotal] DEFAULT ((0.00)) FOR [SubTotal] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_TaxAmt] DEFAULT ((0.00)) FOR [TaxAmt] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_Freight] DEFAULT ((0.00)) FOR [Freight] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[SalesOrderHeader] ADD CONSTRAINT [DF_SalesOrderHeader_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_SalesOrderHeader_CustomerID
	ON [SalesLT].[SalesOrderHeader] ( [CustomerID] ) GO

CREATE TABLE [SalesLT].[SalesOrderDetail] (
	[SalesOrderID] INT NOT NULL,
	[SalesOrderDetailID] INT IDENTITY(1, 1) NOT NULL,
	[OrderQty] SMALLINT NOT NULL,
	[ProductID] INT NOT NULL,
	[UnitPrice] MONEY NOT NULL,
	[UnitPriceDiscount] MONEY NOT NULL,
	[LineTotal] NUMERIC(38,6) NOT NULL,
	[rowguid] UNIQUEIDENTIFIER NOT NULL,
	[ModifiedDate] DATETIME NOT NULL,
	CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED ([SalesOrderID] ASC, [SalesOrderDetailID] ASC) ON [PRIMARY],
	CONSTRAINT [AK_SalesOrderDetail_rowguid] UNIQUE NONCLUSTERED ([rowguid] ASC) ON [PRIMARY],
	CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK ([OrderQty]>(0)),
	CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK ([UnitPrice]>=(0.00)),
	CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK ([UnitPriceDiscount]>=(0.00)),
	CONSTRAINT [FK_SalesOrderDetail_Product_ProductID] FOREIGN KEY (ProductID) REFERENCES [SalesLT].[Product](ProductID),
	CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY (SalesOrderID) REFERENCES [SalesLT].[SalesOrderHeader](SalesOrderID)
) GO


ALTER TABLE [SalesLT].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)) FOR [UnitPriceDiscount] GO

ALTER TABLE [SalesLT].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()) FOR [rowguid] GO

ALTER TABLE [SalesLT].[SalesOrderDetail] ADD CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate] GO

CREATE INDEX IX_SalesOrderDetail_ProductID
	ON [SalesLT].[SalesOrderDetail] ( [ProductID] ) GO

CREATE VIEW [SalesLT].[vProductAndDescription] AS
	SELECT [SalesLT].[ProductModelProductDescription].[Culture],
		[SalesLT].[ProductDescription].[Description],
		[SalesLT].[Product].[Name],
		[SalesLT].[Product].[ProductID],
		[SalesLT].[ProductModel].[Name] as [ProductModel]
	FROM [SalesLT].[ProductModelProductDescription], [SalesLT].[ProductDescription], [SalesLT].[Product], [SalesLT].[ProductModel]
GO
CREATE VIEW [SalesLT].[vGetAllCategories] AS
	SELECT [SalesLT].[ProductCategory].[Name] as [ParentProductCategoryName],
		[SalesLT].[ProductCategory].[Name] as [ProductCategoryID],
		[SalesLT].[ProductCategory].[Name] as [ProductCategoryName]
	FROM [SalesLT].[ProductCategory]
GO
CREATE VIEW [SalesLT].[vProductModelCatalogDescription] AS
	SELECT [SalesLT].[ProductModel].[CatalogDescription] as [BikeFrame],
		[SalesLT].[ProductModel].[CatalogDescription] as [Color],
		[SalesLT].[ProductModel].[CatalogDescription] as [Copyright],
		[SalesLT].[ProductModel].[CatalogDescription] as [Crankset],
		[SalesLT].[ProductModel].[CatalogDescription] as [MaintenanceDescription],
		[SalesLT].[ProductModel].[CatalogDescription] as [Manufacturer],
		[SalesLT].[ProductModel].[CatalogDescription] as [Material],
		[SalesLT].[ProductModel].[ModifiedDate],
		[SalesLT].[ProductModel].[Name],
		[SalesLT].[ProductModel].[CatalogDescription] as [NoOfYears],
		[SalesLT].[ProductModel].[CatalogDescription] as [Pedal],
		[SalesLT].[ProductModel].[CatalogDescription] as [PictureAngle],
		[SalesLT].[ProductModel].[CatalogDescription] as [PictureSize],
		[SalesLT].[ProductModel].[CatalogDescription] as [ProductLine],
		[SalesLT].[ProductModel].[ProductModelID],
		[SalesLT].[ProductModel].[CatalogDescription] as [ProductPhotoID],
		[SalesLT].[ProductModel].[CatalogDescription] as [ProductURL],
		[SalesLT].[ProductModel].[CatalogDescription] as [RiderExperience],
		[SalesLT].[ProductModel].[CatalogDescription] as [Saddle],
		[SalesLT].[ProductModel].[CatalogDescription] as [Style],
		[SalesLT].[ProductModel].[CatalogDescription] as [Summary],
		[SalesLT].[ProductModel].[CatalogDescription] as [WarrantyDescription],
		[SalesLT].[ProductModel].[CatalogDescription] as [WarrantyPeriod],
		[SalesLT].[ProductModel].[CatalogDescription] as [Wheel],
		[SalesLT].[ProductModel].[rowguid]
	FROM [SalesLT].[ProductModel]
GO

3. Views

3.1 View vGetAllCategories

3.1.1 vGetAllCategories Tree Diagram

{object}

vGetAllCategories

ParentProductCategoryName

viewRef

Name (v)

ProductCategoryID

viewRef

Name (v)

ProductCategoryName

viewRef

Name (v)

3.1.2 vGetAllCategories Properties

Property	Value
View name	vGetAllCategories
Technical name
Id
Database	SalesLT
View on	ProductCategory
Additional properties
Comments
Remarks

3.1.3 vGetAllCategories Column

Column	Type	Req	Key	Description	Comments
ParentProductCategoryName	reference	false
ProductCategoryID	reference	false
ProductCategoryName	reference	false

3.1.3.1 Column ParentProductCategoryName

3.1.3.1.1 ParentProductCategoryName properties

Property	Value
Business Name	ParentProductCategoryName
$ref	#collection/definitions/ProductCategory/Name
Reference type	collectionReference

3.1.3.2 Column ProductCategoryID

3.1.3.2.1 ProductCategoryID properties

Property	Value
Business Name	ProductCategoryID
$ref	#collection/definitions/ProductCategory/Name
Reference type	collectionReference

3.1.3.3 Column ProductCategoryName

3.1.3.3.1 ProductCategoryName properties

Property	Value
Business Name	ProductCategoryName
$ref	#collection/definitions/ProductCategory/Name
Reference type	collectionReference

3.2 View vProductAndDescription

3.2.1 vProductAndDescription Tree Diagram

{object}

vProductAndDescription

{ABC}

Culture

viewRef

Culture (v)

{ABC}

Description

viewRef

Description (v)

Name

viewRef

Name (v)

{123}

ProductID

viewRef

ProductID (v)

ProductModel

viewRef

Name (v)

3.2.2 vProductAndDescription Properties

Property	Value
View name	vProductAndDescription
Technical name
Id
Database	SalesLT
View on	Product
Additional properties
Comments
Remarks

3.2.3 vProductAndDescription Column

Column	Type	Req	Key	Description	Comments
Culture	nchar(6)	true
Description	nvarchar(400)	true
Name	reference	false
ProductID	int	true
ProductModel	reference	false

3.2.3.1 Column Culture

3.2.3.1.1 Culture properties

Property	Value
Business Name	Culture
$ref	#collection/definitions/ProductModelProductDescription/Culture
Reference type	collectionReference

3.2.3.2 Column Description

3.2.3.2.1 Description properties

Property	Value
Business Name	Description
$ref	#collection/definitions/ProductDescription/Description
Reference type	collectionReference

3.2.3.3 Column Name

3.2.3.3.1 Name properties

Property	Value
Business Name	Name
$ref	#collection/definitions/Product/Name
Reference type	collectionReference

3.2.3.4 Column ProductID

3.2.3.4.1 ProductID properties

Property	Value
Business Name	ProductID
$ref	#collection/definitions/Product/ProductID
Reference type	collectionReference

3.2.3.5 Column ProductModel

3.2.3.5.1 ProductModel properties

Property	Value
Business Name	ProductModel
$ref	#collection/definitions/ProductModel/Name
Reference type	collectionReference

3.3 View vProductModelCatalogDescription

3.3.1 vProductModelCatalogDescription Tree Diagram

{object}

vProductModelCatalogDescription

{xml}

BikeFrame

viewRef

CatalogDescription (v)

{xml}

Color

viewRef

CatalogDescription (v)

{xml}

viewRef

CatalogDescription (v)

{xml}

Crankset

viewRef

CatalogDescription (v)

{xml}

MaintenanceDescription

viewRef

CatalogDescription (v)

{xml}

Manufacturer

viewRef

CatalogDescription (v)

{xml}

Material

viewRef

CatalogDescription (v)

{dt}

ModifiedDate

viewRef

ModifiedDate (v)

Name

viewRef

Name (v)

{xml}

NoOfYears

viewRef

CatalogDescription (v)

{xml}

Pedal

viewRef

CatalogDescription (v)

{xml}

PictureAngle

viewRef

CatalogDescription (v)

{xml}

PictureSize

viewRef

CatalogDescription (v)

{xml}

ProductLine

viewRef

CatalogDescription (v)

{123}

ProductModelID

viewRef

ProductModelID (v)

{xml}

ProductPhotoID

viewRef

CatalogDescription (v)

{xml}

ProductURL

viewRef

CatalogDescription (v)

{xml}

RiderExperience

viewRef

CatalogDescription (v)

{xml}

Saddle

viewRef

CatalogDescription (v)

{xml}

Style

viewRef

CatalogDescription (v)

{xml}

Summary

viewRef

CatalogDescription (v)

{xml}

WarrantyDescription

viewRef

CatalogDescription (v)

{xml}

WarrantyPeriod

viewRef

CatalogDescription (v)

{xml}

Wheel

viewRef

CatalogDescription (v)

{UUID}

rowguid

viewRef

rowguid (v)

3.3.2 vProductModelCatalogDescription Properties

Property	Value
View name	vProductModelCatalogDescription
Technical name
Id
Database	SalesLT
View on	ProductModel
Additional properties
Comments
Remarks

3.3.3 vProductModelCatalogDescription Column

Column	Type	Req	Key	Description	Comments
BikeFrame	xml	false
Color	xml	false
Copyright	xml	false
Crankset	xml	false
MaintenanceDescription	xml	false
Manufacturer	xml	false
Material	xml	false
ModifiedDate	datetime	true
Name	reference	false
NoOfYears	xml	false
Pedal	xml	false
PictureAngle	xml	false
PictureSize	xml	false
ProductLine	xml	false
ProductModelID	int	true
ProductPhotoID	xml	false
ProductURL	xml	false
RiderExperience	xml	false
Saddle	xml	false
Style	xml	false
Summary	xml	false
WarrantyDescription	xml	false
WarrantyPeriod	xml	false
Wheel	xml	false
rowguid	uniqueidentifier	true

3.3.3.1 Column BikeFrame

3.3.3.1.1 BikeFrame properties

Property	Value
Business Name	BikeFrame
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.2 Column Color

3.3.3.2.1 Color properties

Property	Value
Business Name	Color
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.3 Column Copyright

3.3.3.3.1 Copyright properties

Property	Value
Business Name	Copyright
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.4 Column Crankset

3.3.3.4.1 Crankset properties

Property	Value
Business Name	Crankset
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.5 Column MaintenanceDescription

3.3.3.5.1 MaintenanceDescription properties

Property	Value
Business Name	MaintenanceDescription
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.6 Column Manufacturer

3.3.3.6.1 Manufacturer properties

Property	Value
Business Name	Manufacturer
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.7 Column Material

3.3.3.7.1 Material properties

Property	Value
Business Name	Material
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.8 Column ModifiedDate

3.3.3.8.1 ModifiedDate properties

Property	Value
Business Name	ModifiedDate
$ref	#collection/definitions/ProductModel/ModifiedDate
Reference type	collectionReference

3.3.3.9 Column Name

3.3.3.9.1 Name properties

Property	Value
Business Name	Name
$ref	#collection/definitions/ProductModel/Name
Reference type	collectionReference

3.3.3.10 Column NoOfYears

3.3.3.10.1 NoOfYears properties

Property	Value
Business Name	NoOfYears
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.11 Column Pedal

3.3.3.11.1 Pedal properties

Property	Value
Business Name	Pedal
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.12 Column PictureAngle

3.3.3.12.1 PictureAngle properties

Property	Value
Business Name	PictureAngle
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.13 Column PictureSize

3.3.3.13.1 PictureSize properties

Property	Value
Business Name	PictureSize
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.14 Column ProductLine

3.3.3.14.1 ProductLine properties

Property	Value
Business Name	ProductLine
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.15 Column ProductModelID

3.3.3.15.1 ProductModelID properties

Property	Value
Business Name	ProductModelID
$ref	#collection/definitions/ProductModel/ProductModelID
Reference type	collectionReference

3.3.3.16 Column ProductPhotoID

3.3.3.16.1 ProductPhotoID properties

Property	Value
Business Name	ProductPhotoID
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.17 Column ProductURL

3.3.3.17.1 ProductURL properties

Property	Value
Business Name	ProductURL
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.18 Column RiderExperience

3.3.3.18.1 RiderExperience properties

Property	Value
Business Name	RiderExperience
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.19 Column Saddle

3.3.3.19.1 Saddle properties

Property	Value
Business Name	Saddle
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.20 Column Style

3.3.3.20.1 Style properties

Property	Value
Business Name	Style
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.21 Column Summary

3.3.3.21.1 Summary properties

Property	Value
Business Name	Summary
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.22 Column WarrantyDescription

3.3.3.22.1 WarrantyDescription properties

Property	Value
Business Name	WarrantyDescription
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.23 Column WarrantyPeriod

3.3.3.23.1 WarrantyPeriod properties

Property	Value
Business Name	WarrantyPeriod
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.24 Column Wheel

3.3.3.24.1 Wheel properties

Property	Value
Business Name	Wheel
$ref	#collection/definitions/ProductModel/CatalogDescription
Reference type	collectionReference

3.3.3.25 Column rowguid

3.3.3.25.1 rowguid properties

Property	Value
Business Name	rowguid
$ref	#collection/definitions/ProductModel/rowguid
Reference type	collectionReference

4. Relationships

4.1 Relationship FK_CustomerAddress_Address_AddressID

4.1.1 FK_CustomerAddress_Address_AddressID Diagram

Parent Table	Parent Column
Address	AddressID

Child Table	Child Column
CustomerAddress	AddressID

4.1.2 FK_CustomerAddress_Address_AddressID Properties

Property	Value
Name	FK_CustomerAddress_Address_AddressID
Description
Parent Table	Address
Parent Column	AddressID
Parent Cardinality	1
Child Table	CustomerAddress
Child Column	AddressID
Child Cardinality	0..n
Comments

4.2 Relationship FK_CustomerAddress_Customer_CustomerID

4.2.1 FK_CustomerAddress_Customer_CustomerID Diagram

Parent Table	Parent Column
Customer	CustomerID

Child Table	Child Column
CustomerAddress	CustomerID

4.2.2 FK_CustomerAddress_Customer_CustomerID Properties

Property	Value
Name	FK_CustomerAddress_Customer_CustomerID
Description
Parent Table	Customer
Parent Column	CustomerID
Parent Cardinality	1
Child Table	CustomerAddress
Child Column	CustomerID
Child Cardinality	0..n
Comments

4.3 Relationship FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID

4.3.1 FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID Diagram

Parent Table	Parent Column
ProductCategory	ProductCategoryID

Child Table	Child Column
ProductCategory	ParentProductCategoryID

4.3.2 FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID Properties

Property	Value
Name	FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID
Description
Parent Table	ProductCategory
Parent Column	ProductCategoryID
Parent Cardinality	1
Child Table	ProductCategory
Child Column	ParentProductCategoryID
Child Cardinality	0..n
Comments

4.4 Relationship FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID

4.4.1 FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID Diagram

Parent Table	Parent Column
ProductDescription	ProductDescriptionID

Child Table	Child Column
ProductModelProductDescription	ProductDescriptionID

4.4.2 FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID Properties

Property	Value
Name	FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID
Description
Parent Table	ProductDescription
Parent Column	ProductDescriptionID
Parent Cardinality	1
Child Table	ProductModelProductDescription
Child Column	ProductDescriptionID
Child Cardinality	0..n
Comments

4.5 Relationship FK_ProductModelProductDescription_ProductModel_ProductModelID

4.5.1 FK_ProductModelProductDescription_ProductModel_ProductModelID Diagram

Parent Table	Parent Column
ProductModel	ProductModelID

Child Table	Child Column
ProductModelProductDescription	ProductModelID

4.5.2 FK_ProductModelProductDescription_ProductModel_ProductModelID Properties

Property	Value
Name	FK_ProductModelProductDescription_ProductModel_ProductModelID
Description
Parent Table	ProductModel
Parent Column	ProductModelID
Parent Cardinality	1
Child Table	ProductModelProductDescription
Child Column	ProductModelID
Child Cardinality	0..n
Comments

4.6 Relationship FK_Product_ProductCategory_ProductCategoryID

4.6.1 FK_Product_ProductCategory_ProductCategoryID Diagram

Parent Table	Parent Column
ProductCategory	ProductCategoryID

Child Table	Child Column
Product	ProductCategoryID

4.6.2 FK_Product_ProductCategory_ProductCategoryID Properties

Property	Value
Name	FK_Product_ProductCategory_ProductCategoryID
Description
Parent Table	ProductCategory
Parent Column	ProductCategoryID
Parent Cardinality	1
Child Table	Product
Child Column	ProductCategoryID
Child Cardinality	0..n
Comments

4.7 Relationship FK_Product_ProductModel_ProductModelID

4.7.1 FK_Product_ProductModel_ProductModelID Diagram

Parent Table	Parent Column
ProductModel	ProductModelID

Child Table	Child Column
Product	ProductModelID

4.7.2 FK_Product_ProductModel_ProductModelID Properties

Property	Value
Name	FK_Product_ProductModel_ProductModelID
Description
Parent Table	ProductModel
Parent Column	ProductModelID
Parent Cardinality	1
Child Table	Product
Child Column	ProductModelID
Child Cardinality	0..n
Comments

4.8 Relationship FK_SalesOrderDetail_Product_ProductID

4.8.1 FK_SalesOrderDetail_Product_ProductID Diagram

Parent Table	Parent Column
Product	ProductID

Child Table	Child Column
SalesOrderDetail	ProductID

4.8.2 FK_SalesOrderDetail_Product_ProductID Properties

Property	Value
Name	FK_SalesOrderDetail_Product_ProductID
Description
Parent Table	Product
Parent Column	ProductID
Parent Cardinality	1
Child Table	SalesOrderDetail
Child Column	ProductID
Child Cardinality	0..n
Comments

4.9 Relationship FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID

4.9.1 FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID Diagram

Parent Table	Parent Column
SalesOrderHeader	SalesOrderID

Child Table	Child Column
SalesOrderDetail	SalesOrderID

4.9.2 FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID Properties

Property	Value
Name	FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
Description
Parent Table	SalesOrderHeader
Parent Column	SalesOrderID
Parent Cardinality	1
Child Table	SalesOrderDetail
Child Column	SalesOrderID
Child Cardinality	0..n
Comments

4.10 Relationship FK_SalesOrderHeader_Address_BillTo_AddressID

4.10.1 FK_SalesOrderHeader_Address_BillTo_AddressID Diagram

Parent Table	Parent Column
Address	AddressID

Child Table	Child Column
SalesOrderHeader	BillToAddressID

4.10.2 FK_SalesOrderHeader_Address_BillTo_AddressID Properties

Property	Value
Name	FK_SalesOrderHeader_Address_BillTo_AddressID
Description
Parent Table	Address
Parent Column	AddressID
Parent Cardinality	1
Child Table	SalesOrderHeader
Child Column	BillToAddressID
Child Cardinality	0..n
Comments

4.11 Relationship FK_SalesOrderHeader_Address_ShipTo_AddressID

4.11.1 FK_SalesOrderHeader_Address_ShipTo_AddressID Diagram

Parent Table	Parent Column
Address	AddressID

Child Table	Child Column
SalesOrderHeader	ShipToAddressID

4.11.2 FK_SalesOrderHeader_Address_ShipTo_AddressID Properties

Property	Value
Name	FK_SalesOrderHeader_Address_ShipTo_AddressID
Description
Parent Table	Address
Parent Column	AddressID
Parent Cardinality	1
Child Table	SalesOrderHeader
Child Column	ShipToAddressID
Child Cardinality	0..n
Comments

4.12 Relationship FK_SalesOrderHeader_Customer_CustomerID

4.12.1 FK_SalesOrderHeader_Customer_CustomerID Diagram

Parent Table	Parent Column
Customer	CustomerID

Child Table	Child Column
SalesOrderHeader	CustomerID

4.12.2 FK_SalesOrderHeader_Customer_CustomerID Properties

Property	Value
Name	FK_SalesOrderHeader_Customer_CustomerID
Description
Parent Table	Customer
Parent Column	CustomerID
Parent Cardinality	1
Child Table	SalesOrderHeader
Child Column	CustomerID
Child Cardinality	0..n
Comments