Database Schemas

Owned by Selvyn Wright
Last updated: May 09, 2024
4 min read

Connecting to the database using RazorSQL

user: sa

pwd: @someThingComplicated1234

 

  1. image-20240509-114158.png

  2. Setup as shown here

  3. image-20240509-114408.png

  4. Once a connection is established, expand either of the DBs shown here and then expand the dbo schemas

 

Catalog Database

CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo."__EFMigrationsHistory" ( MigrationId nvarchar(150) NOT NULL, ProductVersion nvarchar(32) NOT NULL, PRIMARY KEY (MigrationId) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.BasketItems ( Id int NOT NULL, UnitPrice decimal(18,2) NOT NULL, Quantity int NOT NULL, CatalogItemId int NOT NULL, BasketId int NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Baskets ( Id int NOT NULL, BuyerId nvarchar(256) NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Catalog ( Id int NOT NULL, Name nvarchar(50) NOT NULL, Description nvarchar(max), Price decimal(18,2) NOT NULL, PictureUri nvarchar(max), CatalogTypeId int NOT NULL, CatalogBrandId int NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.CatalogBrands ( Id int NOT NULL, Brand nvarchar(100) NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.CatalogTypes ( Id int NOT NULL, Type nvarchar(100) NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.OrderItems ( Id int NOT NULL, ItemOrdered_CatalogItemId int, ItemOrdered_ProductName nvarchar(50), ItemOrdered_PictureUri nvarchar(max), UnitPrice decimal(18,2) NOT NULL, Units int NOT NULL, OrderId int, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Orders ( Id int NOT NULL, BuyerId nvarchar(256) DEFAULT (N'') NOT NULL, OrderDate datetimeoffset NOT NULL, ShipToAddress_Street nvarchar(180) DEFAULT (N'') NOT NULL, ShipToAddress_City nvarchar(100) DEFAULT (N'') NOT NULL, ShipToAddress_State nvarchar(60), ShipToAddress_Country nvarchar(90) DEFAULT (N'') NOT NULL, ShipToAddress_ZipCode nvarchar(18) DEFAULT (N'') NOT NULL, PRIMARY KEY (Id) ); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.BasketItems ADD FOREIGN KEY (BasketId) REFERENCES dbo.Baskets (Id); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Catalog ADD FOREIGN KEY (CatalogBrandId) REFERENCES dbo.CatalogBrands (Id); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Catalog ADD FOREIGN KEY (CatalogTypeId) REFERENCES dbo.CatalogTypes (Id); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.OrderItems ADD FOREIGN KEY (OrderId) REFERENCES dbo.Orders (Id); CREATE INDEX IX_BasketItems_BasketId ON dbo.BasketItems (BasketId); CREATE INDEX IX_Catalog_CatalogBrandId ON dbo.Catalog (CatalogBrandId); CREATE INDEX IX_Catalog_CatalogTypeId ON dbo.Catalog (CatalogTypeId); CREATE INDEX IX_OrderItems_OrderId ON dbo.OrderItems (OrderId);

 

Identity Database

CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo."__EFMigrationsHistory" ( MigrationId nvarchar(150) NOT NULL, ProductVersion nvarchar(32) NOT NULL, PRIMARY KEY (MigrationId) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.BasketItems ( Id int NOT NULL, UnitPrice decimal(18,2) NOT NULL, Quantity int NOT NULL, CatalogItemId int NOT NULL, BasketId int NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Baskets ( Id int NOT NULL, BuyerId nvarchar(256) NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Catalog ( Id int NOT NULL, Name nvarchar(50) NOT NULL, Description nvarchar(max), Price decimal(18,2) NOT NULL, PictureUri nvarchar(max), CatalogTypeId int NOT NULL, CatalogBrandId int NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.CatalogBrands ( Id int NOT NULL, Brand nvarchar(100) NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.CatalogTypes ( Id int NOT NULL, Type nvarchar(100) NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.OrderItems ( Id int NOT NULL, ItemOrdered_CatalogItemId int, ItemOrdered_ProductName nvarchar(50), ItemOrdered_PictureUri nvarchar(max), UnitPrice decimal(18,2) NOT NULL, Units int NOT NULL, OrderId int, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Orders ( Id int NOT NULL, BuyerId nvarchar(256) DEFAULT (N'') NOT NULL, OrderDate datetimeoffset NOT NULL, ShipToAddress_Street nvarchar(180) DEFAULT (N'') NOT NULL, ShipToAddress_City nvarchar(100) DEFAULT (N'') NOT NULL, ShipToAddress_State nvarchar(60), ShipToAddress_Country nvarchar(90) DEFAULT (N'') NOT NULL, ShipToAddress_ZipCode nvarchar(18) DEFAULT (N'') NOT NULL, PRIMARY KEY (Id) ); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.BasketItems ADD FOREIGN KEY (BasketId) REFERENCES dbo.Baskets (Id); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Catalog ADD FOREIGN KEY (CatalogBrandId) REFERENCES dbo.CatalogBrands (Id); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.Catalog ADD FOREIGN KEY (CatalogTypeId) REFERENCES dbo.CatalogTypes (Id); ALTER TABLE "Microsoft.eShopOnWeb.CatalogDb".dbo.OrderItems ADD FOREIGN KEY (OrderId) REFERENCES dbo.Orders (Id); CREATE INDEX IX_BasketItems_BasketId ON dbo.BasketItems (BasketId); CREATE INDEX IX_Catalog_CatalogBrandId ON dbo.Catalog (CatalogBrandId); CREATE INDEX IX_Catalog_CatalogTypeId ON dbo.Catalog (CatalogTypeId); CREATE INDEX IX_OrderItems_OrderId ON dbo.OrderItems (OrderId); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo."__EFMigrationsHistory" ( MigrationId nvarchar(150) NOT NULL, ProductVersion nvarchar(32) NOT NULL, PRIMARY KEY (MigrationId) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetRoleClaims ( Id int NOT NULL, RoleId nvarchar(450) NOT NULL, ClaimType nvarchar(max), ClaimValue nvarchar(max), PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetRoles ( Id nvarchar(450) NOT NULL, Name nvarchar(256), NormalizedName nvarchar(256), ConcurrencyStamp nvarchar(max), PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserClaims ( Id int NOT NULL, UserId nvarchar(450) NOT NULL, ClaimType nvarchar(max), ClaimValue nvarchar(max), PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserLogins ( LoginProvider nvarchar(450) NOT NULL, ProviderKey nvarchar(450) NOT NULL, ProviderDisplayName nvarchar(max), UserId nvarchar(450) NOT NULL, PRIMARY KEY (LoginProvider,ProviderKey) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserRoles ( UserId nvarchar(450) NOT NULL, RoleId nvarchar(450) NOT NULL, PRIMARY KEY (RoleId,UserId) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUsers ( Id nvarchar(450) NOT NULL, UserName nvarchar(256), NormalizedUserName nvarchar(256), Email nvarchar(256), NormalizedEmail nvarchar(256), EmailConfirmed bit NOT NULL, PasswordHash nvarchar(max), SecurityStamp nvarchar(max), ConcurrencyStamp nvarchar(max), PhoneNumber nvarchar(max), PhoneNumberConfirmed bit NOT NULL, TwoFactorEnabled bit NOT NULL, LockoutEnd datetimeoffset, LockoutEnabled bit NOT NULL, AccessFailedCount int NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserTokens ( UserId nvarchar(450) NOT NULL, LoginProvider nvarchar(450) NOT NULL, Name nvarchar(450) NOT NULL, Value nvarchar(max), PRIMARY KEY (LoginProvider,Name,UserId) ); ALTER TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetRoleClaims ADD FOREIGN KEY (RoleId) REFERENCES dbo.AspNetRoles (Id); ALTER TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserClaims ADD FOREIGN KEY (UserId) REFERENCES dbo.AspNetUsers (Id); ALTER TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserLogins ADD FOREIGN KEY (UserId) REFERENCES dbo.AspNetUsers (Id); ALTER TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserRoles ADD FOREIGN KEY (RoleId) REFERENCES dbo.AspNetRoles (Id); ALTER TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserRoles ADD FOREIGN KEY (UserId) REFERENCES dbo.AspNetUsers (Id); ALTER TABLE "Microsoft.eShopOnWeb.Identity".dbo.AspNetUserTokens ADD FOREIGN KEY (UserId) REFERENCES dbo.AspNetUsers (Id); CREATE INDEX EmailIndex ON dbo.AspNetUsers (NormalizedEmail); CREATE INDEX IX_AspNetRoleClaims_RoleId ON dbo.AspNetRoleClaims (RoleId); CREATE INDEX IX_AspNetUserClaims_UserId ON dbo.AspNetUserClaims (UserId); CREATE INDEX IX_AspNetUserLogins_UserId ON dbo.AspNetUserLogins (UserId); CREATE INDEX IX_AspNetUserRoles_RoleId ON dbo.AspNetUserRoles (RoleId); CREATE UNIQUE INDEX RoleNameIndex ON dbo.AspNetRoles (NormalizedName); CREATE UNIQUE INDEX UserNameIndex ON dbo.AspNetUsers (NormalizedUserName);