Rainbow Creative Designs

Relational Database Design, SQL Querying, and Schema Optimization

Relational Schema Design & Implementation

• Schema Development: Designing normalized relational structures to ensure data integrity and reduce redundancy.
• SQL Proficiency: Crafting complex queries, joins, and sub-queries to extract actionable insights from multi-table datasets.
• Data Administration: Implementing user roles, permissions, and security protocols to protect sensitive information.
• Optimization: Analyzing query performance and indexing to improve system response times.

Retail Workflow Optimization (Cupcake Store)

This conceptual model was designed to handle a high-volume retail environment with multiple fulfillment channels. By decoupling the Order Type (In-store vs. Online) from the Delivery Type, the system allows for real-time inventory tracking and clear invoice status management.

• Key Logic: Implemented a many-to-one relationship between Customers and Orders to ensure a seamless ‘repeat customer’ experience.
• Optimization: Integrated a ‘Payment Type’ entity to track cash vs. card transactions, providing the foundation for daily financial reconciliation.

Case Study: Library System Optimization

• Customers: Managing IDs and membership dates. 
• Books: Tracking ISBNs and publication details. 
• Transactions: Handling checkouts and fine tracking. 

This project focuses on the physical schema and referential integrity required to manage a circular economy of assets (books) and users (customers).

• Integrity Constraints: Utilized Primary Key (PK) and Foreign Key (FK) relationships to link Transactions directly to specific ISBNs and Customer IDs, preventing data orphans.
• Data Types: Defined strict data types (Integers for IDs, Characters for Names) to ensure high-speed querying and reduce storage overhead.
• Functional Result: The ‘Transactions’ table acts as the central junction, tracking checkouts, checkout length, and fines owed in a single, high-performance view.
• Key Achievement: Designed a central ‘Transactions’ junction table to manage checkouts, return dates, and fine tracking in real-time.
• Technical Focus: Ensured 3rd Normal Form (3NF) to eliminate update anomalies and maintain a clean record of customer history.