Yaakov Lapciuc Software Portfolio DB Schema Liquor Store Management

Liquor Store Management

The Liquor Store Database

if not exists(select * from sys.databases d where d.name = 'LiquorStoreManagement')
begin
create database LiquorStoreManagement
end
go
use LiquorStoreManagement
go
create table dbo.country(
iCountryId int not null identity primary key,
chCountryCode char(2) not null
constraint c_country_code_must_be_two_characters check(chCountryCode not like '% %')
constraint u_country_name_must_be_unique unique
)
go
create table dbo.region(
iRegionId int not null identity primary key,
iCountryId int constraint f_country_region foreign key references country(iCountryId),
vchRegionDesc varchar(25) not null
constraint c_region_desc_cannot_be_blank check(vchRegionDesc <> ''),
constraint u_region_country_region_name_must_be_unique unique(iCountryId, vchRegionDesc)
)
go
create table dbo.ParentCompany(
iParentCompanyId int not null identity primary key,
vchParentCompanyName varchar(500)
constraint u_parentcompany_name_must_be_unique unique
constraint c_parentcompany_name_cannot_be_blank check(vchParentCompanyName <> '')
)
go
create table dbo.store(
iStoreId int not null identity primary key,
iRegionId int not null constraint f_store_region foreign key references region(iRegionId),
iParentCompanyId int constraint f_store_parentcompany foreign key references parentCompany(iParentCompanyId),
iStoreNo int not null
constraint u_store_no_must_be_unique unique
constraint c_store_no_must_be_greater_than_0 check(iStoreNo > 0),
vchStoreName varchar(500)
constraint u_store_name_must_be_unique unique
constraint c_store_name_cannot_be_blank check(vchStoreName <> '')
)
go
create table dbo.shelfset(
iShelfSetId int not null identity primary key,
iStoreId int not null constraint f_shelfSet_store foreign key references store(iStoreId),
vchShelfSetName varchar(25) not null
constraint c_shelftSet_Name_cannot_be_blank check(vchshelfsetName <> ''),
iShelfSetSequence int not null,
bStorage bit,
constraint u_store_shelfset_Name_must_be_unique unique(iStoreId, vchShelfSetName),
constraint u_store_shelfset_ShelfSetSequence_must_be_unique unique(iStoreId, iShelfSetSequence)
)
go
create table dbo.shelf(
iShelfId int not null identity primary key,
iShelfSetId int not null constraint f_shelfset_shelf foreign key references shelfset(iShelfSetId),
iSequence int not null constraint c_shelf_sequence_must_be_greater_than_0 check(iSequence > 0),
iSubUnitCapacity int not null constraint c_shelf_subUnit_capacity_must_be_greater_than_zero check(iSubUnitCapacity > 0)
constraint u_shelf_sequence_must_be_unique unique (iShelfSetId, iSequence)
)
go
create table dbo.Brand(
iBrandId int not null identity primary key,
vchBrandName varchar(250) not null
constraint c_Brand_Name_cannot_be_blank check(vchBrandName <> '')
constraint u_Brand_Name_must_be_unique unique
)
go
create table dbo.BeverageType(
iBeverageTypeId int not null identity primary key,
vchBeverageTypeName varchar(50) not null
constraint c_Beverage_Type_Name_cannot_be_blank check(vchBeverageTypeName <> '')
constraint u_vchBeverage_Type_Name__must_be_unique unique,
vchAlcoholDescriptor varchar(20) not null
constraint c_beverage_type_alcohol_descriptor_cannot_be_blank check(vchAlcoholDescriptor <> ''),
dcMinAlcoholContent decimal(5,2) not null
constraint c_beveragetype_min_alcohol_content_must_be_greater_than_zero check(dcMinAlcoholContent > =0),
dcMaxAlcoholContent decimal(5,2) not null,
constraint c_beveragetype_max_alcohol_content_must_be_greater_than_zero check(dcMinAlcoholContent > =0)
)
go
create table dbo.PackageType(
iPackageTypeId int not null identity primary key,
vchPackageTypeDesc varchar(20)
constraint u_packageType_Desc_must_be_unique unique
constraint c_packageType_Desc_cannot_be_blank check(vchPackageTypeDesc <> '')
)
go
create table dbo.beverageSubtype(
ibeverageSubtypeId int not null identity primary key,
iBeverageTypeId int not null constraint f_BeverageSubtype_BeverageType foreign key references BeverageType(iBeverageTypeId),
vchBeverageSubtypeName varchar(25)
constraint u_BeverageSubtype_Name_must_be_unique unique
constraint c_BeverageSubtype_Name_cannot_be_blank check(vchBeverageSubtypeName <> '')
)
go
create table dbo.beverage(
iBeverageId int not null identity primary key,
iBeverageSubtypeId int not null constraint f_beverage_beverageSubtype foreign key references BeverageSubtype(iBeverageSubtypeId),
iBrandId int not null constraint f_beverage_brand foreign key references brand(iBrandId),
vchBeverageName varchar(50) not null
constraint c_beverage_Name_cannot_be_blank check(vchBeverageName <> ''),
iYear int null,
dcAlcoholContent decimal(5,2) not null constraint c_beverage_dcAlcoholContent_cannot_be_less_than_0 check(dcAlcoholContent > 0),
mBeveragePrice money constraint c_beverage_price_must_be_greater_than_0 check(mBeveragePrice > 0),
constraint u_beverage_Name_and_Year_must_be_unique unique(vchBeverageName, iYear)
)
go
create table dbo.SaleUnit(
iSaleUnitId int not null identity primary key,
iPackageTypeId int not null constraint f_SaleUnit_PackageType foreign key references PackageType(iPackageTypeId),
iBeverageId int not null constraint f_SaleUnit_Beverage foreign key references Beverage(iBeverageId),
iSaleUnitQty int not null constraint c_SaleUnit_Qty_must_be_greater_than_0 check(iSaleUnitQty > 0)
)
go
create table dbo.BeverageTypeRegionProhibited(
iBeverageTypeRegionProhibitedId int not null identity primary key,
iRegionId int not null constraint f_region_BeverageTypeRegionProhibited foreign key references region(iRegionId),
iBeverageTypeId int constraint f_BeverageTypeRegionProhibited_beverage foreign key references beverageType(iBeverageTypeId),
constraint u_BeverageTypeRegionProhibited_Region_Beverage_must_be_unique unique(iRegionId, iBeverageTypeId)
)
go
create table dbo.customer(
iCustomerId int not null identity primary key,
iStoreId int not null constraint f_store_customer foreign key references store(iStoreId),
vchCustomerCode uniqueidentifier not null default newid(),
vchCustomerLastName varchar(50) constraint c_customer_last_name_cannot_be_blank check(vchCustomerLastName <> ''),
vchCustomerFirstName varchar(50)constraint c_customer_First_name_cannot_be_blank check(vchCustomerFirstName <> ''),
vchCustomerPhoneNumber varchar(25) null default '',-- constraint u_customer_phone_number_must_be_unique unique,
)
go
create table dbo.orders(
iOrderId int not null identity primary key,
iCustomerId int null constraint f_orders_customer foreign key references customer(iCustomerId),
dtOrdered datetime not null default getdate(),
vchOrderNo uniqueidentifier not null default newid()
)
go
--d2) add computed colum to inventory table that displays the store from the function in (d)
create table dbo.inventory(
iInventoryId int not null identity primary key,
iSaleUnitId int not null constraint f_inventory_SaleUnit foreign key references SaleUnit(iSaleUnitId),
iShelfId int null constraint f_inventory_Shelf foreign key references shelf(iShelfId),
iOrderId int null constraint f_inventory_orders foreign key references orders(iOrderId),
vchBarcode uniqueidentifier not null default newid(),
constraint c_inventory_shelf_or_order_must_be_provided_but_not_both
check((iShelfId is null and iOrderId is not null) or (iShelfId is not null and iOrderId is null))
--constraint c_beverage_type_is_valid_in_region check(dbo.fnIsBeverageTypeValidInRegion(iSaleUnitId int, iShelfId int) = 1)
)
go ;