CS 499 Capstone ePortfolio

title: Databases and Data Structures —

Overview

The application that I chose to enhance for the next part of the capstone is my CS360 classes Android Application that is a Inventory Tracker app that would work on a phone. This particular applicati9on uses a simple database with only 2 tables and no relationships.

What I reviewed

1 AppDatabase.java - The entire database configuration happens here.

@Database(
        entities = { InventoryItem.class, User.class },
        version = 1,
        exportSchema = false
)

- Has 2 entity classes
  - InventoryItem and User
- Has one database schema
- Disallows schema export

@Database(
        entities = { InventoryItem.class, User.class },
        version = 1,
        exportSchema = false
)

- Room generates the implementation of these methods automatically 
- DAO or Data Access Objects define how the database is queried   - getInstance
- Creates the actual database

Database Schema

DATABASE: inventory.db
VERSION: 1
TABLES: 2
RELATIONSHIPS: 0 (ZERO!)

┌──────────────────────┐
│     inventory        │
├──────────────────────┤
│ id (PK)              │
│ name                 │
│ description          │
│ quantity             │
└──────────────────────┘

┌──────────────────────┐
│       users          │
├──────────────────────┤
│ id (PK)              │
│ username             │
│ passwordHash         │
└──────────────────────┘

InventoryItem Entity

@Entity(tableName = "inventory")

Creates a table named ‘Inventory’ based on the class

Fields are as follows:

@PrimaryKey(autoGenerate = true)
private long id;

private final String name;
private final String description;
private int quantity;

SQL generates this SQL:

CREATE TABLE inventory (
    id          INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name        TEXT,
    description TEXT,
    quantity    INTEGER NOT NULL
);

User.java

@Entity(tableName = "users")
public class User {
    @PrimaryKey(autoGenerate = true)
    public long id;

    @NonNull
    public String username;

    @NonNull
    public String passwordHash;

Stores passwordHash not the actual password

DAO Layer

Defines how to query the database

@Dao
public interface InventoryDao {
    @Query("SELECT * FROM inventory ORDER BY id DESC")
    List<InventoryItem> getAll();

    @Query("SELECT * FROM inventory WHERE id = :id LIMIT 1")
    InventoryItem getById(Long id);

    @Insert
    long insert(InventoryItem item);

    @Update
    int update(InventoryItem item);

    @Delete
    int delete(InventoryItem item);
}

This DAO provides basic CRUD operations:

CREATE: insert() method
READ: getAll() and getById() methods
UPDATE: update() method
DELETE: delete() method

Planned Enhancements

Enhancement 1: Normalized Database Schema

Redesigning the database to follow the Third Normal Form with propoer relationships

New Schema as follows:

DATABASE: inventory.db
VERSION: 2 (migrated from version 1)
TABLES: 6 (was 2)
RELATIONSHIPS: 7 foreign keys

┌─────────────────────┐
│     categories      │
├─────────────────────┤
│ id (PK)             │
│ name                │
│ description         │
│ created_at          │
└─────────────────────┘
         ↓ 1:N
┌──────────────────────────────┐
│       inventory              │
├──────────────────────────────┤
│ id (PK)                      │
│ name                         │
│ description                  │
│ quantity                     │
│ price                        │ ← NEW
│ sku                          │ ← NEW
│ min_stock_level              │ ← NEW
│ category_id (FK) ────────────┘ ← NEW foreign key
│ supplier_id (FK) ────────┐    ← NEW foreign key
│ location_id (FK) ──────┐ │    ← NEW foreign key
│ created_at              │ │  ← NEW
│ updated_at              │ │  ← NEW
└─────────────────────────┼─┼──┘
                          │ │
         ┌────────────────┘ │
         ↓ N:1              │
┌─────────────────────┐     │
│     suppliers       │     │
├─────────────────────┤     │
│ id (PK)             │     │
│ name                │     │
│ contact_email       │     │
│ contact_phone       │     │
│ created_at          │     │
└─────────────────────┘     │
                            │
         ┌──────────────────┘
         ↓ N:1
┌─────────────────────┐
│     locations       │
├─────────────────────┤
│ id (PK)             │
│ name                │
│ warehouse           │
│ aisle               │
│ shelf               │
│ created_at          │
└─────────────────────┘

┌──────────────────────────────┐
│    inventory_history         │ ← NEW audit table
├──────────────────────────────┤
│ id (PK)                      │
│ inventory_id (FK) ───────────┤ → Points to inventory
│ user_id (FK) ────────────────┤ → Points to user
│ action                       │
│ old_quantity                 │
│ new_quantity                 │
│ timestamp                    │
└──────────────────────────────┘

┌─────────────────────┐
│       users         │
├─────────────────────┤
│ id (PK)             │
│ username (UNIQUE!)  │ ← NOW UNIQUE
│ passwordHash        │
│ created_at          │ ← NEW
└─────────────────────┘

Organizes items into groups
Query all items ina category
Generate reports by category
No data duplication

Suppliers Table

Tracks where items come from:
- Track which supplier provides which items
- Reorder from correct supplier
- Manage supplier contact information
- Query all itesm from a supplier

Location Table

Tracks where items are stored:
- Find items physically in warehouse
- Organize warehouse layout
- Optimize picking/packing
- Track item movements

Enhanced Inventory Table

Adds foreign keys and additional fields
- category_id: references categories table
- supplier_id: references suppliers table
- location_id: references location table
‘onDelete’
- cannot delete category/supplier if items use it
- if location is deleted, sets location_id to null

Inventory History Table

Tracks all changes:
- Full audit trail of all changes
- See who changed what and when
- Detect unauthorized modifications
- Compliance for regulated industries
- Undo capability

Enhanced User Table

Added Unique contraints:
- Username uniqueness enforced at database level
- Cannot create duplicate users
- Login works correctly
- Data integrity maintained

Enhancement 2: Safe Migration Strategy

Instead of destructive migration. Use a proper migration object:
- Creates new tables:
  - categories
  - suppliers
  - locations
  - inventory_history
- Creates a default ‘Uncategorized’ category.
- Uses ALTER TABLE to add new columns to the existing inventory table
- Checks for duplicate usernames
- handles any conflicts in user name creation
- add unique index
- Creates indices on foreign key columns for query performance
- User updates app from version 1 to version 2
- Migration runs automatically
- All exisitng data preserved
- New features available
- User never knows migration happened
- No data loss

Enhancement 3: Advanced Query Support

Adds new queries to support the new schema
JOIN queries:
- Combine data from multiple tables:

SELECT i.*, c.name as categoryName
FROM inventory i
LEFT JOIN categories c ON i.category_id = c.id

Gets inventory items along with category name
Aggregation Queries: Calculate totals, counts, averages

SELECT SUM(price * quantity) FROM inventory

SELECT c.name, COUNT(i.id) as itemCount
FROM categories c
LEFT JOIN inventory i ON c.id = i.category_id
GROUP BY c.id, c.name

Calculates total inventory value.
Shows how many items in each category
Filtering Queries

SELECT * FROM inventory
WHERE quantity < min_stock_level
ORDER BY (min_stock_level - quantity) DESC

Finds items that are below their minimum stock level, ordered by urgency.
- Search Queries:

SELECT * FROM inventory
WHERE name LIKE '%' || :searchTerm || '%'

Allows users to search for items by name

Enhancement 4: Transaction Support

Example:

@Transaction
@Query("SELECT * FROM inventory WHERE id = :id")
InventoryItemComplete getCompleteItem(long id);

// In InventoryActivity or Repository:
public void transferItem(long itemId, long fromLocationId, long toLocationId, int quantity) {
    db.runInTransaction(() -> {
        // Step 1: Get current item
        InventoryItem item = dao.getById(itemId);

        // Step 2: Verify sufficient quantity at source
        if (item.getQuantity() < quantity) {
            throw new IllegalStateException("Insufficient quantity");
        }

        // Step 3: Update item location
        item.setLocationId(toLocationId);
        dao.update(item);

        // Step 4: Record in history
        InventoryHistory history = new InventoryHistory();
        history.setInventoryId(itemId);
        history.setAction("TRANSFERRED");
        history.setOldQuantity(item.getQuantity());
        history.setNewQuantity(quantity);
        history.setTimestamp(System.currentTimeMillis());
        historyDao.insert(history);

        // ALL steps succeed or ALL fail (atomicity)
    });
}

Transactions ensure ACID properties:
- Automicity: All operations succed or all fail
- Consistency: Database remains in vald state
- Isolation: Concurrent transactions don’t interfere
- Durability: Committed changes are permanent

Normalization Demonstration

First Normal Form (1NF)

“First Normal Form (1NF): No repeating groups, atomic values

Before (Violates 1NF if we stored multiple suppliers):

inventory:
id | name    | suppliers
1  | Laptop  | Dell, HP, Lenovo  ← Multiple values in one field!

After (Meets 1NF):

inventory:
id | name    | supplier_id
1  | Laptop  | 1              ← Single atomic value

suppliers:
id | name
1  | Dell
2  | HP
3  | Lenovo

Second Normal Form (2NF)

“Second Normal Form (2NF): Meets 1NF + no partial dependencies

Before (If we had composite key):

inventory:
(category_id, item_id) | item_name | category_name
(1, 1)                 | Laptop    | Electronics  ← category_name depends only on category_id

After (Meets 2NF):

inventory:
id | name    | category_id
1  | Laptop  | 1

categories:
id | name
1  | Electronics  ← Stored once

Third Normal Form (3NF)

“Third Normal Form (3NF): Meets 2NF + no transitive dependencies

Before (Violates 3NF):

inventory:
id | name    | supplier_id | supplier_name | supplier_email
1  | Laptop  | 1           | Dell          | dell@example.com
2  | Monitor | 1           | Dell          | dell@example.com
   ↑                        ↑ supplier_name depends on supplier_id (transitive!)

After (Meets 3NF):

inventory:
id | name    | supplier_id
1  | Laptop  | 1
2  | Monitor | 1

suppliers:
id | name | email
1  | Dell | dell@example.com  ← Stored once, no redundancy

[Summary]

My new schema meets Third Normal Form (3NF):

Atomic values (1NF)
No partial dependencies (2NF)
No transitive dependencies (3NF)
Minimal redundancy
Maximum data integrity

SECTION 5: SKILLS DEMONSTRATION

This database enhancement demonstrates several important database and software engineering skills.

Database Design

“Database Design Skills:

Entity-Relationship Modeling
- Identifying entities (Category, Supplier, Location)
- Defining relationships (1:N, N:1)
- Drawing ER diagrams
- Understanding cardinality
Normalization
- Applying First, Second, Third Normal Forms
- Eliminating data redundancy
- Ensuring data integrity
- Preventing update/insertion/deletion anomalies
Schema Design
- Choosing appropriate data types
- Defining primary and foreign keys
- Creating indices for performance
- Setting up constraints (UNIQUE, NOT NULL, CHECK)

SQL & Query Optimization

“SQL Skills:

DDL (Data Definition Language)
- CREATE TABLE statements
- ALTER TABLE for migrations
- CREATE INDEX for performance
- Foreign key constraints
DML (Data Manipulation Language)
- INSERT, UPDATE, DELETE
- Complex WHERE clauses
- Transaction control
DQL (Data Query Language)
- JOIN queries (INNER, LEFT, RIGHT)
- Aggregation (SUM, COUNT, AVG, MIN, MAX)
- GROUP BY and HAVING
- Subqueries
- Performance optimization”

ORM (Object-Relational Mapping)

“Room/ORM Skills:

Entity Mapping
- @Entity annotations
- @ColumnInfo for column names
- @PrimaryKey and auto-generation
- @ForeignKey with cascade rules
DAO Design
- @Query with SQL
- @Insert, @Update, @Delete
- @Transaction for ACID
- LiveData and Flow integration
Migration Strategy
- Version management
- Safe data migration
- Schema evolution
- Backward compatibility”

Database Best Practices

“Professional Practices:

Data Integrity
- Referential integrity with foreign keys
- Unique constraints
- NOT NULL constraints
- Check constraints
Performance
- Index strategic columns
- Avoid N+1 query problem
- Use proper JOIN types
- Pagination for large datasets
Security & Audit
- Password hashing (already implemented)
- Audit trail (history table)
- Track who changed what when
- Data validation
Scalability
- Normalized structure
- Efficient queries
- Proper indexing
- Transaction management”

SECTION 6: COURSE OUTCOMES ALIGNMENT

Outcome 4: Well-Founded Techniques (PRIMARY)

“Primary Alignment: Outcome 4 - Demonstrate an ability to use well-founded techniques

This enhancement demonstrates:

Industry-Standard Database Design
- Third Normal Form (3NF) - taught in every database course
- Foreign key constraints - fundamental database concept
- ACID transactions - core database principle
- ER modeling - standard design methodology
Professional Migration Practices
- Safe data migration preserving user data
- Version control with schema export
- Backward compatibility considerations
- Used in all professional database applications
SQL Best Practices
- JOIN queries for related data
- Aggregation for reporting
- Indices for performance
- Constraints for data integrity
Android/Room Best Practices
- Proper entity annotations
- Background thread operations (remove allowMainThreadQueries)
- DAO pattern
- Migration objects”

Outcome 3: Algorithmic Principles

“Secondary Alignment: Outcome 3 - Algorithmic principles

Database operations involve algorithms:

Query optimization: Database uses B-tree (O(log n)) for indexed lookups
JOIN algorithms: Hash join, sort-merge join
Sorting: Database ORDER BY uses efficient sorting algorithms
Search: Index lookups are O(log n) vs O(n) table scans”

Outcome 5: Security Mindset

“Alignment: Outcome 5 - Security mindset

Password hashing (already implemented, maintained)
Audit trail (track all changes)
Referential integrity (prevent invalid data)
Data validation constraints
Transaction ACID properties prevent corruption”

Outcome 2: Professional Communication

“Alignment: Outcome 2 - Professional communication

This comprehensive 50+ page code review document
ER diagrams and schema documentation
Migration documentation
Professional technical writing”

Summary

“Current State Issues:

Destructive migration DELETES all user data on updates
Only 2 tables with zero relationships
Denormalized flat structure
No username uniqueness
Missing critical fields
Main thread database queries
No advanced query capabilities

Enhanced Solution:

Safe migration preserving ALL data
6 tables with proper relationships
Third Normal Form (3NF) normalization
Foreign keys for referential integrity
Complete field set (price, SKU, timestamps, etc.)
Background database operations
Advanced JOINs, aggregation, searching
Full audit trail
Transaction support for ACID properties”

Expected Results

“When this enhancement is complete:

For Users:

Zero data loss on app updates
Organize items by category
Track suppliers and locations
Calculate total inventory value
See full change history
Search and filter capabilities
Better performance

For Business:

Professional database design
Scalable architecture
Regulatory compliance (audit trail)
Reporting capabilities
Data integrity guaranteed

For My Skills Demonstration:

Database design expertise (ER modeling, normalization)
SQL proficiency (DDL, DML, DQL)
ORM mastery (Room/Android)
Migration strategies
Best practices implementation
Professional-quality code”

Conclusion

This database enhancement transforms my simple 2-table structure into a professional, normalized, relational database that follows industry best practices. It demonstrates comprehensive understanding of database design principles, SQL, ORMs, and data integrity - all while ensuring zero data loss for users. Thank you for watching this presentation on my Database Design and Implementation enhancement.