Normal Form Violations

-- Define once
CREATE TYPE Email FROM VARCHAR(254) NOT NULL;
CREATE TYPE CustomerNo FROM INT NOT NULL;

-- Use everywhere — consistency is enforced by the type
CustomerEmail   Email,
ContactEmail    Email,
BillingEmail    Email

-- Before (SNF violation):
CREATE TABLE SupplierSales (
    SupplierNo  SupplierNo  PRIMARY KEY,
    SalesJan    _Money      NOT NULL,
    SalesFeb    _Money      NOT NULL,
    -- ... x12
);

-- After (SNF compliant):
CREATE TABLE SupplierSales (
    SupplierNo  SupplierNo  NOT NULL,
    Year        _Year       NOT NULL,
    Month       _Month      NOT NULL,
    Sales       _Money      NOT NULL,
    CONSTRAINT SupplierSales_PK PRIMARY KEY (SupplierNo, Year, Month),
    CONSTRAINT SupplierSales_Is_Supplier FOREIGN KEY (SupplierNo) REFERENCES Supplier(SupplierNo)
);

-- Before (RKNF violation — surrogate severs child from parent):
CREATE TABLE OrderLine (
    Id          INT IDENTITY PRIMARY KEY,
    OrderId     INT NOT NULL FOREIGN KEY REFERENCES [Order](Id),
    Quantity    _Quantity NOT NULL
);

-- After (RKNF compliant — child key encodes full lineage):
CREATE TABLE OrderLine (
    CustomerNo  CustomerNo  NOT NULL,
    OrderNo     OrderNo     NOT NULL,
    LineNo      LineNo      NOT NULL,
    Quantity    _Quantity   NOT NULL,
    CONSTRAINT OrderLine_PK PRIMARY KEY (CustomerNo, OrderNo, LineNo),
    CONSTRAINT OrderLine_Belongs_To_Order
        FOREIGN KEY (CustomerNo, OrderNo) REFERENCES [Order](CustomerNo, OrderNo)
);

-- Before (KDNF violation — CustomerCity depends on CustomerNo, not OrderNo):
CREATE TABLE [Order] (
    CustomerNo      CustomerNo  NOT NULL,
    OrderNo         OrderNo     NOT NULL,
    CustomerCity    _City       NOT NULL,  -- belongs in Customer
    OrderDate       _Date       NOT NULL,
    ...
);

-- After:
-- CustomerCity lives in Customer where it belongs.
-- Order gets it via JOIN when needed.

-- Before (nullable optional column):
CREATE TABLE Customer (
    CustomerNo  CustomerNo  PRIMARY KEY,
    Name        _Name       NOT NULL,
    Bio         VARCHAR(MAX) NULL  -- most customers have no bio
);

-- After (IDNF — isolated, optional, NOT NULL):
CREATE TABLE Customer (
    CustomerNo  CustomerNo  PRIMARY KEY,
    Name        _Name       NOT NULL
);

CREATE TABLE CustomerBio (
    CustomerNo  CustomerNo   PRIMARY KEY,
    Bio         _LongText    NOT NULL,
    CONSTRAINT CustomerBio_Is_Customer
        FOREIGN KEY (CustomerNo) REFERENCES Customer(CustomerNo)
);

-- Functional constraint enforcing cross-table type rule:
CREATE FUNCTION dbo.Account_IsType_fn (
    @AccountNo AccountNo,
    @ExpectedType _Type
)
RETURNS BIT AS BEGIN
    RETURN (SELECT CASE WHEN EXISTS (
        SELECT 1 FROM Account WHERE AccountNo = @AccountNo AND [Type] = @ExpectedType
    ) THEN 1 ELSE 0 END);
END;

CREATE TABLE SavingsAccount (
    AccountNo   AccountNo   PRIMARY KEY,
    -- ...
    CONSTRAINT SavingsAccount_Is_Account
        FOREIGN KEY (AccountNo) REFERENCES Account(AccountNo),
    CONSTRAINT SavingsAccount_IsAccountType
        CHECK (dbo.Account_IsType_fn(AccountNo, 'Savings') = 1)
);

-- Before (surrogate on associative table — BFNF violation):
CREATE TABLE UserRole (
    Id      INT IDENTITY PRIMARY KEY,
    UserId  INT NOT NULL FOREIGN KEY REFERENCES [User](Id),
    RoleId  INT NOT NULL FOREIGN KEY REFERENCES [Role](Id)
);

-- After (BFNF compliant — PK is both parents):
CREATE TABLE UserRole (
    UserNo  UserNo  NOT NULL,
    RoleNo  RoleNo  NOT NULL,
    CONSTRAINT UserRole_PK PRIMARY KEY (UserNo, RoleNo),
    CONSTRAINT UserRole_Has_User FOREIGN KEY (UserNo) REFERENCES [User](UserNo),
    CONSTRAINT UserRole_Has_Role FOREIGN KEY (RoleNo) REFERENCES [Role](RoleNo)
);

-- Every IDENTITY FK pattern is a breach:
OrderLine.Id INT IDENTITY PRIMARY KEY
OrderLine.OrderId INT NOT NULL FK -> Order.Id