Database Management: Models, Design, SQL & Applications

TAKEAWAYS

• Relational databases store data in tables (relations) linked by keys (primary/foreign), ensuring integrity and efficiency.
• Normalization (1NF–3NF) eliminates redundancy by organizing tables into logical structures (e.g., separating Guest and Booking tables).
• SQL (Structured Query Language) is the standard for querying/managing data (e.g., SELECT, JOIN, GROUP BY for hotel analytics).
• Database design follows a lifecycle: requirements → conceptual (ER diagrams) → logical (tables) → physical (SQL implementation).
• NoSQL (e.g., MongoDB) handles unstructured data (e.g., guest reviews) but lacks ACID transactions.
• Security risks (SQL injection, unauthorized access) require firewalls, encryption, and role-based access control (RBAC).

1. What Is a Database?

A database is an organized collection of structured data stored electronically, designed for efficient retrieval, updates, and management. Unlike spreadsheets (e.g., Excel), databases:

• Scale: Handle millions of records (e.g., NEPSE’s stock data).
• Share: Allow simultaneous access (e.g., multiple front-desk agents updating room availability).
• Integrate: Link data across systems (e.g., POS + inventory in a hotel).

Types of Databases

Why Relational Databases Dominate Hospitality?

• ACID Properties: Ensure transactions (e.g., booking a room) are Atomic (all-or-nothing), Consistent, Isolated, and Durable.
• Standardization: SQL is universally supported (e.g., Oracle in luxury hotels, SQLite in small guesthouses).

2. Database Models

A. Relational Model (Most Common)

Data is stored in tables (relations) with rows (tuples) and columns (attributes). Relationships are defined via keys:

• Primary Key (PK): Unique identifier (e.g., guest_id in the Guest table).
• Foreign Key (FK): Links to a PK in another table (e.g., room_id in Booking references Room(room_id)).

erDiagram
    Guest ||--o{ Booking : "makes"
    Booking ||--|| Room : "reserves"
    Room }|--|| Amenity : "includes"
    Guest {
        guest_id PK
        name
        contact
    }
    Room {
        room_id PK
        type
        price
    }

B. NoSQL Models (For Unstructured Data)

When to Use NoSQL?

• Data is unstructured (e.g., social media posts, IoT sensor data).
• Scalability is critical (e.g., Daraz’s product catalog).
• Flexibility > strict schema (e.g., adding new fields to guest profiles without altering tables).

3. Database Design Process

Step 1: Requirements Gathering

Identify data needs from stakeholders (e.g., front desk, kitchen, management). Example for a Hotel:

• Front Desk: Guest check-in/check-out, room assignments.
• Housekeeping: Room status (occupied/clean/vacant).
• Accounts: Billing and payments.

Step 2: Conceptual Design (ER Diagrams)

Model entities and relationships without worrying about tables. Example:

erDiagram
    Guest ||--o{ Booking : "has"
    Booking ||--|| Room : "books"
    Room ||--|| Amenity : "has"
    Booking {
        booking_id PK
        check_in
        check_out
        total_cost
    }

Step 3: Logical Design (Tables)

Convert ER diagrams to tables with keys. Table: Booking

Step 4: Physical Design (SQL Implementation)

Write CREATE TABLE statements and optimize for performance (e.g., indexing room_id for fast lookups).

Worked Example: Daraz’s Order Queue Daraz uses a relational database to track orders with:

• Tables: Order, Customer, Product, Order_Item.
• Key Relationships:
  • Order.customer_id → Customer.customer_id (FK).
  • Order_Item.order_id → Order.order_id (FK).
• SQL Query to Find Pending Orders:

  SELECT c.name, o.order_id, o.status, SUM(oi.quantity * oi.unit_price) AS total
  FROM Order o
  JOIN Customer c ON o.customer_id = c.customer_id
  JOIN Order_Item oi ON o.order_id = oi.order_id
  WHERE o.status = 'pending'
  GROUP BY o.order_id;
  

4. Normalization (Eliminating Redundancy)

Normalization reduces data duplication by organizing tables into normal forms (NF).

5. SQL for Database Management

SQL is the language to query, update, and administer databases.

A. Data Query Language (DQL)

• SELECT: Retrieve data.

  -- Find all guests from Kathmandu with bookings in 2023
  SELECT g.name, g.contact, COUNT(b.booking_id) AS total_bookings
  FROM Guest g
  JOIN Booking b ON g.guest_id = b.guest_id
  WHERE g.city = 'Kathmandu' AND YEAR(b.check_in) = 2023
  GROUP BY g.guest_id;
  

• JOIN: Combine tables.

  -- List all bookings with room details
  SELECT b.booking_id, r.room_type, r.price, b.check_in, b.check_out
  FROM Booking b
  JOIN Room r ON b.room_id = r.room_id;
  

B. Data Manipulation Language (DML)

• INSERT: Add records.

  INSERT INTO Guest (guest_id, name, contact, city)
  VALUES (101, 'Rajesh Shrestha', '9843210000', 'Lalitpur');
  

• UPDATE: Modify records.

  -- Update room price after inflation
  UPDATE Room SET price = price * 1.1 WHERE room_type = 'Deluxe';
  

• DELETE: Remove records.

  DELETE FROM Booking WHERE check_out < '2023-01-01'; -- Clean up old bookings
  

C. Data Control Language (DCL)

• GRANT/REVOKE: Manage permissions.

  GRANT SELECT ON Room TO 'FrontDeskUser';
  REVOKE DELETE ON Booking FROM 'Housekeeping';
  

6. Database Security

Threats in Hospitality

flowchart TD
    A["User"] --> B["Firewall"]
    B --> C["Authentication\n(RBAC)"]
    C --> D["Encrypted\nConnection"]
    D --> E["Database\n(MySQL)"]
    E --> F["Backup\nSystem"]

Real-World Example: Nabil Bank’s Database Security

• Problem: High-value transactions (e.g., loan approvals) require immutable audit logs.
• Solution:
  • ACID compliance for transactions.
  • Blockchain-like ledger for critical data (e.g., loan disbursements).
  • Multi-factor authentication (MFA) for admin access.

7. Database Management Systems (DBMS) in Hospitality

Case Study: Himalayan Java’s Database

• Challenge: Manage 50+ properties with varying room types, seasonal menus, and loyalty programs.
• Solution:
  • Centralized MySQL database with replication across locations.
  • Stored procedures for complex queries (e.g., "Find all guests who booked a suite in Kathmandu last month").
  • Automated backups to AWS S3 (daily snapshots).

8. Database Administration (DBA) Tasks

DBAs ensure databases run smoothly:

1. Backup and Recovery: Schedule backups (e.g., daily at 2 AM) and test restores.
2. Performance Tuning:
   • Add indexes to frequently queried columns (e.g., room_id in Booking).
   • Optimize queries (e.g., avoid SELECT *).
3. User Management: Create roles like:
   • Receptionist: Read/write Booking, Guest.
   • Chef: Read-only Menu, Inventory.
4. Monitoring: Use tools like MySQL Workbench or pgAdmin to track:
   • Query execution time.
   • Lock contention (e.g., two users updating the same room status).

## In the Real World

1. eSewa’s Payment Database

   • Idea Used: Relational model + ACID transactions.
   • How: Stores transactions in normalized tables (User, Transaction, Payment_Gateway) to ensure no double-charging or lost funds. Uses foreign keys to link users to their payments.
   • Example Query:

     -- Verify a successful payment
     SELECT t.transaction_id, u.name, t.amount, t.status
     FROM Transaction t
     JOIN User u ON t.user_id = u.user_id
     WHERE t.transaction_id = 'TXN12345';
     

2. Daraz’s Inventory System

   • Idea Used: Normalization (3NF) + Indexing.
   • How: Separates Product, Supplier, and Inventory tables to avoid redundancy. Uses indexes on product_id for fast stock checks.
   • Real Scenario: When you order a product, Daraz’s system:
     1. Checks Inventory table for stock.
     2. Updates Order and Order_Item tables atomically (ACID).
     3. Triggers a low-stock alert if quantity < 5.

3. Ncell’s Customer Data

   • Idea Used: NoSQL (MongoDB) for unstructured data + SQL for structured data.
   • How:
     • SQL Database: Stores billing records (Customer, Plan, Payment).
     • MongoDB: Stores customer support chats (JSON: {chat_id, customer_id, messages: [...]}).
   • Example: If a customer complains about slow internet, Ncell’s system:
     • Joins SQL tables to fetch their plan details.
     • Retrieves chat history from MongoDB to personalize support.

## Exam Tip

How This Unit Is Tested in TU Exams:

1. Theory Questions (30%):

   • Define normalization, primary key, and ACID properties.
   • Compare SQL vs. NoSQL (use the table above).
   • Explain ER diagrams with an example (draw one for a hotel).

2. Short Problems (40%):

   • Normalization: Given an unnormalized table, convert it to 3NF. Example: Start with a table GuestBooking(guest_name, room_type, price, check_in) and normalize it.
   • SQL Queries: Write queries for:
     • Finding all guests who booked a room in January 2023.
     • Updating room prices by 10% for deluxe rooms.
   • Design: Draw an ER diagram for a restaurant management system (entities: Customer, Order, Menu_Item, Table).

3. Long Case Study (30%):

   • Scenario: "A 5-star hotel wants to design a database for reservations, housekeeping, and billing. Describe the tables, relationships, and sample SQL queries."
   • How to Score Full Marks:
     1. Identify entities: Guest, Room, Booking, Payment, Staff.
     2. Draw an ER diagram (use Mermaid in exams if allowed).
     3. Normalize tables (show 1NF → 3NF steps).
     4. Write 2–3 SQL queries (e.g., "Find all overbooked dates").
     5. Discuss security: "Use RBAC to restrict housekeeping staff from viewing guest payment details."

Common Mistakes to Avoid:

• ❌ Forgetting to include foreign keys in table designs.
• ❌ Writing SQL without JOIN clauses (e.g., querying Guest and Booking separately).
• ❌ Ignoring data types (e.g., using VARCHAR for dates).
• ❌ Overcomplicating normalization (stick to 3NF unless asked for BCNF).

Pro Tip:

• Memorize these SQL keywords: SELECT, JOIN, GROUP BY, HAVING, INSERT, UPDATE, DELETE, GRANT, REVOKE.
• Practice on SQL Fiddle or DB Fiddle.
• For case studies, assume a mid-sized hotel (e.g., 100 rooms, 50 staff) unless specified otherwise.