Relational Database Model: Tables, Keys, Constraints & Schema Design

TAKEAWAYS:

• The relational model represents data as tables (relations) with rows (tuples) and columns (attributes), replacing hierarchical/network models.
• Keys (primary, foreign, candidate) enforce uniqueness and relationships: a primary key identifies a row, a foreign key links tables, and candidate keys are potential primary keys.
• Constraints (entity integrity, referential integrity, domain constraints) ensure data accuracy and consistency in relational databases.
• Schema design involves defining tables, attributes, keys, and constraints to model real-world scenarios (e.g., Ncell’s customer-service orders).
• Normalization (covered in Unit 5) builds on relational concepts to eliminate redundancy, but this unit focuses on logical design (tables, keys, constraints).
• SQL DDL (CREATE TABLE, ALTER TABLE) implements relational schemas, while DML (INSERT, UPDATE) manipulates data.

1. The Relational Model: Tables as Mathematical Relations

The relational model, introduced by E.F. Codd in 1970, represents data as tables (relations) where:

• Each row (tuple) is a unique record.
• Each column (attribute) has a data type (e.g., INT, VARCHAR).
• No duplicate rows are allowed (uniqueness enforced by keys).

Why Tables?

Before relations, databases used:

• Hierarchical models (e.g., IMS): Tree-like structures (parent-child relationships).
• Network models (e.g., CODASYL): Graph-like structures (many-to-many pointers). Problem: Complex navigation, redundancy, and poor scalability. Solution: The relational model’s flat tables + keys simplify queries and updates.

erDiagram
    Restaurant ||--o{ Worksat : "has"
    Worksat ||--|| Cook : "works for"
    Worksat ||--|| Food : "prepares"

    Restaurant {
        string Rname PK "Restaurant Name"
        string Rlocation "Location"
        decimal Rfund "Fund (NPR)"
    }
    Cook {
        string Cname PK "Cook Name"
        string Cspeciality "Speciality"
    }
    Worksat {
        string Cname PK, FK "Cook Name"
        string Rname PK, FK "Restaurant Name"
        int Workinghrs "Hours"
        string Shift "Shift"
    }
    Food {
        string Fname PK "Food Name"
        string Cname FK "Cook Name"
        string Category "Category"
    }

ER-to-relational mapping showing primary/foreign keys and relationships Figure 1: ER-to-relational conversion for a restaurant system (Unit 2 → Unit 3). Key insight: Weak entities (e.g., Worksat) become tables with a foreign key to their owner (Restaurant or Cook).

2. Keys: The Backbone of Relationships

Keys define uniqueness and relationships between tables.

Types of Keys

How Keys Work: A Trace with Ncell’s Order System

Scenario: Ncell tracks orders for postpaid customers.

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Phone VARCHAR(15) UNIQUE  -- Alternate key
);

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    CustomerID INT REFERENCES Customer(CustomerID),  -- FK
    ProductID INT,
    OrderDate DATE,
    Status VARCHAR(20)
);

Trace:

1. A customer (CustomerID=101) places an order (OrderID=501).
2. The Order table’s CustomerID must match a CustomerID in Customer (FK constraint).
3. If you try to insert Order(502, 999, ...), it fails: referential integrity violation.

3. Constraints: Rules to Keep Data Clean

Constraints are rules applied to tables to enforce integrity.

Types of Constraints

Example: eSewa’s Transaction System

CREATE TABLE User (
    UserID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(50) UNIQUE,
    Balance DECIMAL(10,2) DEFAULT 0.00 CHECK (Balance >= 0)
);

```figure
{"type":"network","nodes":["User","Transaction","Account","Bank"],"edges":[["User","Transaction","initiates"],["Transaction","Account","debits"],["Transaction","Account","credits"],["Account","Bank","belongs to"]],"directed":true,"caption":"Constraints in action: eSewa’s transaction flow with referential integrity"}

CREATE TABLE Transaction ( TransactionID INT PRIMARY KEY, SenderID INT REFERENCES User(UserID), ReceiverID INT REFERENCES User(UserID), Amount DECIMAL(10,2) CHECK (Amount > 0), TransactionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); Constraints in Action:

1. UserID is PK → No duplicate users.
2. Email is UNIQUE → One account per email.
3. Balance cannot be negative (CHECK).
4. SenderID/ReceiverID must exist in User (FOREIGN KEY).

4. Schema Design: From ER to Relational Tables

Schema: The logical structure of a database (tables, keys, constraints). Steps to Design a Schema:

1. Draw an ER diagram (Unit 2).
2. Convert entities to tables:
   • Strong entities → Tables with PK.
   • Weak entities → Tables with FK to owner + partial PK.
3. Add attributes as columns.
4. Define keys and constraints.

Figure 2: Schema design workflow.

Worked Example: Kathmandu Traffic Routes

Scenario: Model traffic routes with:

• Junctions (intersections, roundabouts).
• Roads connecting junctions.
• Traffic lights at junctions.

ER Diagram → Relational Schema:

erDiagram
    Junction ||--o{ Road : "has"
    Junction ||--o{ TrafficLight : "has"
    Junction {
        string JunctionID PK
        string Name
        string Location
    }
    Road {
        string RoadID PK
        string JunctionID1 FK
        string JunctionID2 FK
        string Type "One-way/Two-way"
    }
    TrafficLight {
        string LightID PK
        string JunctionID FK
        string ColorSequence
    }

Key Observations:

• Road has a composite FK (JunctionID1, JunctionID2) to model connections.
• TrafficLight has a FK to Junction (one-to-many relationship).

5. Relational Algebra vs. SQL: How They Differ

SQL DDL (Data Definition Language)

Used to define schemas:

-- Create tables with constraints
CREATE TABLE BankAccount (
    AccountNumber INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Balance DECIMAL(12,2) CHECK (Balance >= 0),
    BranchID INT REFERENCES Branch(BranchID)
);

-- Alter tables (add/modify constraints)
ALTER TABLE BankAccount ADD CONSTRAINT CHK_MinBalance CHECK (Balance >= 1000);

SQL DML (Data Manipulation Language)

Used to insert/update data:

-- Insert with constraints enforced
INSERT INTO BankAccount (AccountNumber, CustomerName, Balance, BranchID)
VALUES (1001, 'Ramesh', 5000.00, 101);

-- Update with CHECK constraint
UPDATE BankAccount SET Balance = 1500 WHERE AccountNumber = 1001;
-- Fails if Balance < 0 (but passes if ≥ 0).

6. Real-World Applications

In the Real World

1. eSewa (Nepal)

   • Idea Used: Foreign keys + referential integrity.
   • How: When you transfer money, eSewa checks:
     • SenderID exists in User (FK constraint).
     • ReceiverID exists in User.
     • Balance ≥ transfer amount (CHECK constraint).
   • Impact: Prevents fraudulent transactions.

2. Ncell’s Order System

   • Idea Used: Composite keys + normalization.
   • How: Orders are tracked with:

     CREATE TABLE Order (
         OrderID INT,
         CustomerID INT,
         ProductID INT,
         PRIMARY KEY (OrderID, CustomerID, ProductID)  -- Composite PK
     );
     

   • Why: Ensures a customer can order the same product multiple times (unlike a single OrderID PK).

3. Nepal Stock Exchange (NEPSE)

   • Idea Used: Primary keys + triggers.
   • How: Stock trades are logged with:

     CREATE TABLE Trade (
         TradeID INT PRIMARY KEY,
         StockID INT REFERENCES Stock(StockID),
         BuyerID INT REFERENCES Investor(InvestorID),
         SellerID INT REFERENCES Investor(InvestorID),
         Price DECIMAL(10,2),
         Quantity INT,
         TradeDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );
     

   • Constraint: BuyerID ≠ SellerID (enforced via CHECK or trigger).

7. Common Pitfalls and Best Practices

Mistakes to Avoid

• Missing Foreign Keys: Leads to "orphaned" records (e.g., an Order with no Customer).
• Weak Primary Keys: Using non-unique attributes like Name as PK.
• Ignoring Constraints: Letting NULL or invalid data slip in (e.g., negative Balance).
• Over-normalizing Early: Design schemas for logical correctness first, then optimize for performance.

Best Practices

• Use Surrogate Keys: Auto-incremented IDs (e.g., INT AUTO_INCREMENT) for PKs.
• Document Constraints: Add comments to explain CHECK rules (e.g., /* Age must be 18+ */).
• Test with Edge Cases: Insert NULL, duplicate values, and invalid data to verify constraints.
• Leverage Defaults: Set DEFAULT values for optional fields (e.g., Status DEFAULT 'Active').

Exam Tip

How This Unit is Tested:

1. Design Questions (30-40%):

   • Convert ER diagrams to relational schemas (e.g., "Design tables for a library with books, members, and loans").
   • Key Check: Ensure all entities are tables, weak entities have FKs, and attributes are columns.
   • Example: For a University ER diagram, Student and Course are tables with Enrollment as a junction table (composite PK).

2. SQL Implementation (30-40%):

   • Write CREATE TABLE with all constraints (PK, FK, NOT NULL, CHECK).
   • Common Exam Tricks:
     • Asks for a table with a weak entity → Include FK + partial PK.
     • Asks for referential integrity → Use FOREIGN KEY with ON DELETE CASCADE (if needed).
   • Example:

     -- Expected answer for a "bank account" table:
     CREATE TABLE Account (
         AccountNumber INT PRIMARY KEY,
         CustomerID INT NOT NULL,
         Balance DECIMAL(10,2) CHECK (Balance >= 0),
         FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
     );
     

3. Conceptual Questions (20-30%):

   • Define primary key, foreign key, referential integrity.
   • Explain why tables are better than hierarchical models (simplicity, scalability).
   • Compare keys vs. constraints (keys enforce uniqueness; constraints enforce rules).
   • Example Answer:

     "A foreign key is a column in Table A that references a primary key in Table B. It enforces referential integrity, ensuring that a row in Table A cannot reference a non-existent row in Table B. For example, in an Order table, CustomerID is a FK to Customer(CustomerID)."

4. Scenario-Based (10-20%):

   • Given a real-world scenario (e.g., "Design a database for Daraz’s order system"), identify:
     • Tables (Customer, Product, Order).
     • Keys (OrderID PK, CustomerID FK).
     • Constraints (Quantity > 0, OrderDate ≤ CURRENT_DATE).
   • Tip: Use bullet points in exams to list tables, keys, and constraints clearly.

Pro Tip for Full Marks:

• Draw diagrams (even in text exams, describe them clearly).
• Use SQL syntax exactly as shown in examples (e.g., FOREIGN KEY (col) REFERENCES Table(col)).
• Link to real-world systems (e.g., "Like eSewa’s transaction system, we need FKs to validate users.").