Lesson 8 (Lab): Design a Small Schema and Enforce Constraints Properly

Section 2 — Lesson 8 (Lab): Design a Small Schema and Enforce Constraints Properly

Welcome to the final lesson of Section 2. Up to this point, you learned the core theory that separates a real DBA from
a “button-clicker”: data vs information, tables/columns/constraints, keys, relationships, transactions/ACID,
normalization, and index concepts.

Now you will put everything together in a practical lab. Even though this course is self-paced and reading-based,
you will still learn like a professional: build a small schema, enforce integrity,
and verify with evidence.

Lesson description: You will design a small but realistic relational schema, choose correct data types,
build primary keys and foreign keys, enforce uniqueness and valid ranges using constraints, apply normalization rules,
add a few high-value indexes, and test enforcement by attempting invalid inserts. By the end, you will have a clean,
DBA-grade example schema you can reuse for practice in later sections.

Lab Overview: What You Are Building

You will build a simple “Service Booking” schema. This is a realistic pattern used in many industries:
transportation, scheduling, repairs, installations, consulting, delivery, and more.

Your schema will include:

• Customer (who is booking)
• Service (what type of service is offered)
• Booking (the transaction record)
• Payment (record of payment attempts/receipts)

This design intentionally gives you opportunities to apply:
normalization, keys, referential integrity, and indexing.

Step 1) Design Before You Build (DBA Habit)

Before creating any tables, write this in your DBA notebook:

1. Entities: Customer, Service, Booking, Payment
2. Primary keys: Use surrogate keys (INT IDENTITY) for stable row identity
3. Relationships:
   • Customer (1) → Booking (many)
   • Service (1) → Booking (many)
   • Booking (1) → Payment (many) (payments can be attempted multiple times)
4. Critical business uniqueness:
   • Customer email should be unique (if your rules require one account per email)
   • Booking confirmation code should be unique
5. Validation rules:
   • Amounts must be non-negative
   • Status must be a controlled list
   • Required fields must be NOT NULL

DBA principle: A small design step up front prevents major redesign later.

Step 2) Create the Database and Schema (Safe Defaults)

Use a dedicated practice database so you can safely experiment.

-- Create a lab database
CREATE DATABASE DBAFundamentalsLab;
GO

USE DBAFundamentalsLab;
GO

Step 3) Build the Tables With DBA-Grade Constraints

3A) Customer table

This table stores customer identity. Notice the deliberate choices:
NOT NULL for required fields, DEFAULT for CreatedAt,
and UNIQUE for Email to prevent duplicates.

CREATE TABLE dbo.Customer
(
    CustomerID     INT IDENTITY(1,1) NOT NULL,
    FullName       NVARCHAR(120)     NOT NULL,
    Email          NVARCHAR(255)     NOT NULL,
    Phone          NVARCHAR(30)      NULL,
    CreatedAt      DATETIME2(0)      NOT NULL CONSTRAINT DF_Customer_CreatedAt DEFAULT (SYSDATETIME()),

    CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerID),
    CONSTRAINT UQ_Customer_Email UNIQUE (Email)
);

DBA note: Email uniqueness is a business rule decision. If your system allows multiple accounts per email,
you would not enforce it as UNIQUE. The DBA’s job is to align constraints with real business rules.

3B) Service table

This table stores service types and base pricing. We enforce non-negative pricing with a CHECK constraint.

CREATE TABLE dbo.Service
(
    ServiceID      INT IDENTITY(1,1) NOT NULL,
    ServiceName    NVARCHAR(120)     NOT NULL,
    BasePriceUSD   DECIMAL(10,2)     NOT NULL,
    IsActive       BIT               NOT NULL CONSTRAINT DF_Service_IsActive DEFAULT (1),
    CreatedAt      DATETIME2(0)      NOT NULL CONSTRAINT DF_Service_CreatedAt DEFAULT (SYSDATETIME()),

    CONSTRAINT PK_Service PRIMARY KEY CLUSTERED (ServiceID),
    CONSTRAINT UQ_Service_ServiceName UNIQUE (ServiceName),
    CONSTRAINT CK_Service_BasePrice_NonNegative CHECK (BasePriceUSD >= 0)
);

DBA note: We used DECIMAL for currency, not FLOAT. This prevents rounding surprises.

3C) Booking table (core transaction entity)

This is the core table. Notice:

• FKs enforce valid references to Customer and Service.
• Status is controlled by a CHECK constraint.
• PickupAt is DATETIME2 (not text).
• ConfirmationCode is UNIQUE (business-safe identifier).
• TotalAmountUSD must be non-negative.

CREATE TABLE dbo.Booking
(
    BookingID           INT IDENTITY(1,1) NOT NULL,
    CustomerID          INT               NOT NULL,
    ServiceID           INT               NOT NULL,

    ConfirmationCode    VARCHAR(20)       NOT NULL,
    PickupAt            DATETIME2(0)      NOT NULL,
    Notes               NVARCHAR(500)     NULL,

    Status              VARCHAR(20)       NOT NULL CONSTRAINT DF_Booking_Status DEFAULT ('NEW'),
    TotalAmountUSD      DECIMAL(10,2)     NOT NULL CONSTRAINT DF_Booking_Total DEFAULT (0),

    CreatedAt           DATETIME2(0)      NOT NULL CONSTRAINT DF_Booking_CreatedAt DEFAULT (SYSDATETIME()),

    CONSTRAINT PK_Booking PRIMARY KEY CLUSTERED (BookingID),

    CONSTRAINT UQ_Booking_ConfirmationCode UNIQUE (ConfirmationCode),

    CONSTRAINT CK_Booking_Status CHECK (Status IN ('NEW','CONFIRMED','COMPLETED','CANCELED')),
    CONSTRAINT CK_Booking_Total_NonNegative CHECK (TotalAmountUSD >= 0),

    CONSTRAINT FK_Booking_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customer(CustomerID),
    CONSTRAINT FK_Booking_Service  FOREIGN KEY (ServiceID)  REFERENCES dbo.Service(ServiceID)
);

DBA note: We did not use ON DELETE CASCADE here. In many production systems,
DBAs prefer to block deletes that could remove history. Instead of deleting, you often mark records as canceled/inactive.

3D) Payment table (many payments per booking)

Payments can be attempted multiple times (failed card, retry, partial payments, etc.).
This table demonstrates a clean one-to-many relationship.

CREATE TABLE dbo.Payment
(
    PaymentID        INT IDENTITY(1,1) NOT NULL,
    BookingID        INT               NOT NULL,

    AmountUSD        DECIMAL(10,2)     NOT NULL,
    PaymentMethod    VARCHAR(20)       NOT NULL,
    PaymentStatus    VARCHAR(20)       NOT NULL,
    ProviderRef      NVARCHAR(100)     NULL,

    PaidAt           DATETIME2(0)      NOT NULL CONSTRAINT DF_Payment_PaidAt DEFAULT (SYSDATETIME()),

    CONSTRAINT PK_Payment PRIMARY KEY CLUSTERED (PaymentID),

    CONSTRAINT CK_Payment_Amount_NonNegative CHECK (AmountUSD >= 0),
    CONSTRAINT CK_Payment_Method CHECK (PaymentMethod IN ('CARD','PAYPAL','BANK','CASH')),
    CONSTRAINT CK_Payment_Status CHECK (PaymentStatus IN ('PENDING','SUCCEEDED','FAILED','REFUNDED')),

    CONSTRAINT FK_Payment_Booking FOREIGN KEY (BookingID) REFERENCES dbo.Booking(BookingID)
);

DBA note: We controlled PaymentMethod and PaymentStatus values with CHECK constraints.
In large enterprise systems, you might use reference tables instead. The principle is the same: controlled, validated values.

Step 4) Add a Few High-Value Indexes (Workload-Aware)

You now have clustered primary keys, which create clustered indexes. Next, we add a few nonclustered indexes that match
common access patterns:

• Look up customer by email (common in applications)
• Find bookings by customer, ordered by time
• Find bookings by pickup time (dispatch/reporting scenario)
• Find payments by booking

-- Customer lookup by email (already UNIQUE, but this illustrates the access pattern)
-- The UNIQUE constraint already creates an index; this is typically not needed again.

-- Bookings by customer and pickup time
CREATE INDEX IX_Booking_CustomerID_PickupAt
ON dbo.Booking (CustomerID, PickupAt);

-- Bookings by pickup time (useful for schedules/dispatch/reporting)
CREATE INDEX IX_Booking_PickupAt
ON dbo.Booking (PickupAt)
INCLUDE (Status, TotalAmountUSD, CustomerID, ServiceID);

-- Payments by booking
CREATE INDEX IX_Payment_BookingID
ON dbo.Payment (BookingID);

DBA reminder: Indexes are chosen to support real query patterns. Do not add indexes “just because.”
Measure and validate performance improvements later (you will do this in the performance sections).

Step 5) Insert Sample Data (So You Can Test Constraints)

Insert a small amount of data. Keep it simple—your purpose is to verify relationships and constraints work.

-- Customers
INSERT INTO dbo.Customer (FullName, Email, Phone)
VALUES
('Jordan Smith','jordan.smith@example.com','716-111-2222'),
('Amina Hassan','amina.hassan@example.com','716-333-4444');

-- Services
INSERT INTO dbo.Service (ServiceName, BasePriceUSD)
VALUES
('Airport Transfer', 95.00),
('Hourly Service', 60.00);

-- Booking
INSERT INTO dbo.Booking (CustomerID, ServiceID, ConfirmationCode, PickupAt, Notes, Status, TotalAmountUSD)
VALUES
(1, 1, 'BK-10001', DATEADD(HOUR, 4, SYSDATETIME()), 'Meet at arrivals', 'CONFIRMED', 95.00);

-- Payment
INSERT INTO dbo.Payment (BookingID, AmountUSD, PaymentMethod, PaymentStatus, ProviderRef)
VALUES
(1, 95.00, 'PAYPAL', 'SUCCEEDED', 'PAYPAL-REF-ABC123');

Step 6) Evidence-First Tests (Try to Break Your Own Rules)

Now you will do what a professional DBA does: attempt invalid actions and confirm SQL Server blocks them.
Run these tests one at a time and read the errors carefully.

Test A: Duplicate email should fail (UNIQUE constraint)

-- Should FAIL due to UQ_Customer_Email
INSERT INTO dbo.Customer (FullName, Email)
VALUES ('Duplicate User', 'jordan.smith@example.com');

Test B: Booking referencing a non-existent customer should fail (FK)

-- Should FAIL due to FK_Booking_Customer
INSERT INTO dbo.Booking (CustomerID, ServiceID, ConfirmationCode, PickupAt, Status, TotalAmountUSD)
VALUES (999, 1, 'BK-FAIL-001', SYSDATETIME(), 'NEW', 95.00);

Test C: Invalid status should fail (CHECK constraint)

-- Should FAIL due to CK_Booking_Status
INSERT INTO dbo.Booking (CustomerID, ServiceID, ConfirmationCode, PickupAt, Status, TotalAmountUSD)
VALUES (1, 1, 'BK-FAIL-002', SYSDATETIME(), 'IN_PROGRESS', 95.00);

Test D: Negative total should fail (CHECK constraint)

-- Should FAIL due to CK_Booking_Total_NonNegative
INSERT INTO dbo.Booking (CustomerID, ServiceID, ConfirmationCode, PickupAt, Status, TotalAmountUSD)
VALUES (1, 1, 'BK-FAIL-003', SYSDATETIME(), 'NEW', -10.00);

What you should learn from these failures

• The database is enforcing rules, not “hoping the app validates.”
• The errors are evidence that your design is protecting production.
• Constraints and FKs are your first line of defense against data corruption.

Step 7) Validate Relationships With Simple Queries

Run these SELECT statements to confirm your schema behaves the way a real application would use it.

-- View bookings with customer and service details
SELECT
    b.BookingID,
    b.ConfirmationCode,
    b.PickupAt,
    b.Status,
    b.TotalAmountUSD,
    c.FullName,
    c.Email,
    s.ServiceName
FROM dbo.Booking AS b
JOIN dbo.Customer AS c ON b.CustomerID = c.CustomerID
JOIN dbo.Service  AS s ON b.ServiceID  = s.ServiceID
ORDER BY b.PickupAt;

-- View payments for a booking
SELECT
    p.PaymentID,
    p.BookingID,
    p.AmountUSD,
    p.PaymentMethod,
    p.PaymentStatus,
    p.PaidAt
FROM dbo.Payment AS p
WHERE p.BookingID = 1
ORDER BY p.PaidAt;

DBA skill: Being able to validate quickly with queries is a real-world requirement.
You will do much more of this in Section 3 (T-SQL Foundations).

Step 8) Document Like a DBA (Small, Clear, Useful)

In your DBA notebook, write a short design note that includes:

• What each table represents (Customer, Service, Booking, Payment)
• The primary key for each table
• The foreign key relationships
• The controlled values (Status, PaymentMethod, PaymentStatus)
• The indexes you created and the query pattern each supports

DBA advantage: Documentation is what makes your work explainable and professional.
It also becomes portfolio proof for employers.

Lesson Summary (What You Just Proved You Can Do)

• You designed a schema using real DBA thinking (entities, keys, relationships).
• You enforced integrity using PK/FK/UNIQUE/CHECK/DEFAULT constraints.
• You added a few high-value indexes aligned to access patterns.
• You verified enforcement by attempting invalid inserts (evidence-first validation).
• You practiced the habit of documenting decisions like a professional DBA.

What Comes Next: Section 3 — T-SQL Foundations (DBA Must-Haves)

In Section 3, you will become fluent in the SQL statements DBAs use constantly:
SELECT filtering and sorting, joins, grouping, aggregates, safe data modifications,
and practical patterns that prevent mistakes in production.

You now have the correct foundation to write SQL with confidence because you understand
how data is structured, protected, and related under the hood.