Activity One: What is a Database - Build Your Personal Contacts Database

# Option 1: Supabase (Recommended - Real Production PostgreSQL)
# 1. Visit supabase.com and create free account
# 2. Create new project (takes 2 minutes)  
# 3. Open SQL Editor in dashboard
# 4. You now have production PostgreSQL in the cloud!

-- Create a professional contacts table using PostgreSQL features
CREATE TABLE contacts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,  -- Industry standard UUIDs
    first_name TEXT NOT NULL,
    last_name TEXT,
    nickname TEXT,
    phone_number TEXT,
    email TEXT UNIQUE,  -- Prevent duplicate emails at database level
    birthday DATE,
    category TEXT CHECK(category IN ('Family', 'Friend', 'Work', 'Other')),
    company TEXT,
    job_title TEXT,
    linkedin_url TEXT,
    tags TEXT[],  -- PostgreSQL array feature - store multiple tags
    favorite_color TEXT,
    notes TEXT,
    last_contacted TIMESTAMPTZ,  -- Track relationship engagement
    date_added TIMESTAMPTZ DEFAULT NOW(),  -- Auto-timestamp creation
    updated_at TIMESTAMPTZ DEFAULT NOW()   -- Track last modification
);

-- Create indexes for fast searches (production practice)
CREATE INDEX idx_contacts_email ON contacts(email);
CREATE INDEX idx_contacts_category ON contacts(category);
CREATE INDEX idx_contacts_company ON contacts(company);
CREATE INDEX idx_contacts_name ON contacts(first_name, last_name);

-- Full-text search index for powerful contact search
CREATE INDEX idx_contacts_search ON contacts 
USING GIN(to_tsvector('english', first_name || ' ' || last_name || ' ' || COALESCE(company, '')));

-- Test your database connection and schema
SELECT 
    COUNT(*) as total_contacts,
    COUNT(DISTINCT category) as categories_used,
    COUNT(CASE WHEN email IS NOT NULL THEN 1 END) as contacts_with_email
FROM contacts;

-- Should return 0 contacts initially - ready to populate!

-- Add your first contact
INSERT INTO contacts (first_name, last_name, email, category, tags, notes)
VALUES ('Alice', 'Johnson', 'alice@email.com', 'Friend', 
        ARRAY['college', 'photography'], 'Met at photography club');

-- Find specific contacts
SELECT first_name, last_name, email, category 
FROM contacts 
WHERE category = 'Friend';

-- Search across all contact information
SELECT * FROM contacts 
WHERE to_tsvector('english', first_name || ' ' || last_name || ' ' || COALESCE(company, ''))
@@ plainto_tsquery('english', 'photography');

-- Update contact information
UPDATE contacts 
SET job_title = 'Senior Developer', company = 'TechCorp', updated_at = NOW()
WHERE email = 'alice@email.com';

-- Remove outdated contacts (be careful!)
DELETE FROM contacts 
WHERE last_contacted < NOW() - INTERVAL '2 years'
AND category = 'Work';

-- Data validation with constraints
ALTER TABLE contacts 
ADD CONSTRAINT valid_email 
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}

## 💻 Hands-On Practice: Build Your Contact Database (100 lines)

### Challenge 1: Add Your Inner Circle
```sql
-- Add 5 people you know with different categories
INSERT INTO contacts (first_name, last_name, email, category, phone_number, tags, notes) VALUES
('John', 'Smith', 'john.smith@email.com', 'Family', '+1-555-0101', 
 ARRAY['brother', 'gaming'], 'Lives in Seattle, loves video games'),
('Sarah', 'Davis', 'sarah.davis@email.com', 'Friend', '+1-555-0102', 
 ARRAY['college', 'running'], 'Running partner from university'),
('Mike', 'Wilson', 'mike.wilson@techcorp.com', 'Work', '+1-555-0103', 
 ARRAY['colleague', 'mentor'], 'Senior developer, great mentor'),
('Emma', 'Brown', 'emma.brown@email.com', 'Friend', '+1-555-0104', 
 ARRAY['art', 'creative'], 'Artist friend, amazing paintings'),
('Dr. Lisa', 'Miller', 'lisa.miller@clinic.com', 'Other', '+1-555-0105', 
 ARRAY['healthcare', 'professional'], 'Family doctor, very helpful');

-- Find all work contacts
SELECT first_name, last_name, company, job_title 
FROM contacts 
WHERE category = 'Work'
ORDER BY company, last_name;

-- Find contacts with specific skills/interests
SELECT first_name, last_name, tags 
FROM contacts 
WHERE tags && ARRAY['gaming', 'art', 'running'];  -- PostgreSQL array overlap

-- Find contacts you haven't spoken to recently
SELECT first_name, last_name, last_contacted,
       CASE 
           WHEN last_contacted IS NULL THEN 'Never contacted'
           WHEN last_contacted < NOW() - INTERVAL '6 months' THEN 'Contact soon'
           ELSE 'Recently contacted'
       END as contact_status
FROM contacts
ORDER BY last_contacted DESC NULLS LAST;

-- Get insights about your network
SELECT 
    category,
    COUNT(*) as contact_count,
    COUNT(CASE WHEN email IS NOT NULL THEN 1 END) as with_email,
    COUNT(CASE WHEN phone_number IS NOT NULL THEN 1 END) as with_phone,
    ROUND(AVG(array_length(tags, 1)), 1) as avg_tags_per_contact
FROM contacts
GROUP BY category
ORDER BY contact_count DESC;

-- Find your most connected categories
SELECT 
    unnest(tags) as tag,
    COUNT(*) as usage_count
FROM contacts
WHERE tags IS NOT NULL
GROUP BY unnest(tags)
ORDER BY usage_count DESC
LIMIT 10;

-- Update professional information
UPDATE contacts 
SET company = 'Google', 
    job_title = 'Software Engineer',
    linkedin_url = 'https://linkedin.com/in/johnsmith',
    tags = tags || ARRAY['tech', 'google'],  -- Add tags
    updated_at = NOW()
WHERE first_name = 'John' AND last_name = 'Smith';

-- Track communication
UPDATE contacts 
SET last_contacted = NOW(),
    notes = notes || E'\n' || 'Had coffee on ' || NOW()::date::text
WHERE email = 'sarah.davis@email.com';

-- Find networking opportunities  
SELECT 
    c1.first_name || ' ' || c1.last_name as contact_1,
    c2.first_name || ' ' || c2.last_name as contact_2,
    c1.company as company_1,
    c2.company as company_2,
    array_to_string(c1.tags & c2.tags, ', ') as common_interests
FROM contacts c1
JOIN contacts c2 ON c1.id < c2.id  -- Avoid duplicate pairs
WHERE c1.tags && c2.tags  -- Have common tags
AND c1.category = 'Work' AND c2.category = 'Work'
AND c1.company != c2.company;  -- Different companies

-- 1. Verify data quality
SELECT 
    'Total Contacts' as metric,
    COUNT(*) as value
FROM contacts

UNION ALL

SELECT 
    'Contacts with Email',
    COUNT(CASE WHEN email IS NOT NULL THEN 1 END)
FROM contacts

UNION ALL

SELECT 
    'Categories Used',
    COUNT(DISTINCT category)
FROM contacts

UNION ALL

SELECT 
    'Average Tags per Contact',
    ROUND(AVG(array_length(tags, 1)), 1)::TEXT
FROM contacts
WHERE tags IS NOT NULL;

## 💻 Hands-On Practice: Build Your Contact Database (100 lines)

### Challenge 1: Add Your Inner Circle
__CODE_BLOCK_8__

### Challenge 2: Smart Contact Searches
__CODE_BLOCK_9__

### Challenge 3: Contact Analytics
__CODE_BLOCK_10__

### Challenge 4: Professional Network Management
__CODE_BLOCK_11__

## ✅ Verify Results (25 lines)

### Database Health Check
__CODE_BLOCK_12__

**✅ Success Criteria:**
- Successfully created professional database schema
- Added multiple contacts with different data types
- Performed complex searches using PostgreSQL features
- Used advanced database features (arrays, full-text search)
- Demonstrated CRUD operations in practice

## 📚 Deep Dive: Why Databases Matter (50 lines)

### Real-World Database Applications

**Social Media** (Facebook, Instagram, Twitter):
- Store billions of user profiles, posts, and relationships
- Handle millions of simultaneous users
- Provide instant search across massive datasets

**E-commerce** (Amazon, eBay, Shopify):
- Manage product catalogs with millions of items
- Track inventory levels in real-time
- Process thousands of orders per second

**Financial Services** (Banks, PayPal, Venmo):
- Secure storage of sensitive financial data
- Ensure transaction accuracy and consistency
- Provide instant balance updates and fraud detection

**Healthcare** (Electronic Health Records):
- Store patient medical histories securely
- Enable instant access to critical health information
- Maintain data integrity for life-critical decisions

### Database Types and Use Cases
**Relational Databases** (PostgreSQL, MySQL): Structured data with relationships
**NoSQL Databases** (MongoDB, Redis): Flexible schemas for rapid development  
**Graph Databases** (Neo4j): Social networks and recommendation engines
**Time-Series Databases** (InfluxDB): IoT sensors and monitoring data

### Career Impact
Understanding databases is crucial for:

**Software Engineering** ($70K-150K): Building applications that store and retrieve data
**Data Science** ($80K-140K): Analyzing large datasets for business insights
**Database Administration** ($65K-120K): Managing and optimizing database systems
**Product Management** ($90K-160K): Understanding technical constraints and capabilities

**Key Skills Developed:**
- Data modeling and schema design
- Query optimization for performance
- Data integrity and security principles
- Understanding of scalability challenges

### Next Steps in Database Learning
1. **Advanced SQL**: Joins, subqueries, window functions
2. **Database Design**: Normalization, indexing strategies
3. **Performance Tuning**: Query optimization, monitoring
4. **NoSQL Systems**: Document stores, key-value databases
5. **Data Architecture**: Microservices, distributed systems

Your contact database is just the beginning - you've now experienced the power of organized, searchable, and reliable data storage that powers every modern application!

## 📤 Submit Your Work

Complete this activity and submit your work through the [Activity Submission Form](https://forms.gle/JWJS5CwTqb3YX8776)