applecare_analytics.sql

{\rtf1\ansi\ansicpg1252\cocoartf2709
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\margl1440\margr1440\vieww11520\viewh8400\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0

\f0\fs24 \cf0 -- =========================================================\
-- AppleCare Analytics Database (SQL + ERD Structure)\
-- Author: Sehaj (Sam) Malhotra\
-- Description: Relational schema for repair, inventory,\
--              customer, and service analytics.\
-- =========================================================\
\
-- -----------------------------\
-- 1. Create Database\
-- -----------------------------\
CREATE DATABASE applecare_analytics;\
\\c applecare_analytics;\
\
-- -----------------------------\
-- 2. Customers\
-- -----------------------------\
CREATE TABLE customers (\
    customer_id         SERIAL PRIMARY KEY,\
    first_name          VARCHAR(50),\
    last_name           VARCHAR(50),\
    email               VARCHAR(100) UNIQUE,\
    phone               VARCHAR(20),\
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP\
);\
\
-- -----------------------------\
-- 3. Devices\
-- -----------------------------\
CREATE TABLE devices (\
    device_id           SERIAL PRIMARY KEY,\
    customer_id         INT REFERENCES customers(customer_id),\
    serial_number       VARCHAR(100) UNIQUE NOT NULL,\
    model               VARCHAR(50),\
    purchase_date       DATE,\
    warranty_status     VARCHAR(30) -- In Warranty / Out of Warranty\
);\
\
CREATE INDEX idx_devices_customer ON devices(customer_id);\
\
-- -----------------------------\
-- 4. Service Centers\
-- -----------------------------\
CREATE TABLE service_centers (\
    center_id           SERIAL PRIMARY KEY,\
    center_name         VARCHAR(100),\
    location            VARCHAR(150),\
    standard_sla_hours  INT DEFAULT 48\
);\
\
-- -----------------------------\
-- 5. Technicians\
-- -----------------------------\
CREATE TABLE technicians (\
    technician_id       SERIAL PRIMARY KEY,\
    center_id           INT REFERENCES service_centers(center_id),\
    tech_name           VARCHAR(100),\
    certification       VARCHAR(100)\
);\
\
CREATE INDEX idx_technicians_center ON technicians(center_id);\
\
-- -----------------------------\
-- 6. Repair Jobs\
-- -----------------------------\
CREATE TABLE repair_jobs (\
    repair_id           SERIAL PRIMARY KEY,\
    device_id           INT REFERENCES devices(device_id),\
    center_id           INT REFERENCES service_centers(center_id),\
    technician_id       INT REFERENCES technicians(technician_id),\
    issue_description   TEXT,\
    received_at         TIMESTAMP,\
    completed_at        TIMESTAMP,\
    repair_time_hours   INT,\
    status              VARCHAR(30) -- Completed / Pending / SLA Breach\
);\
\
CREATE INDEX idx_repairs_device ON repair_jobs(device_id);\
CREATE INDEX idx_repairs_center ON repair_jobs(center_id);\
\
-- -----------------------------\
-- 7. Parts\
-- -----------------------------\
CREATE TABLE parts (\
    part_id             SERIAL PRIMARY KEY,\
    part_name           VARCHAR(100),\
    category            VARCHAR(50),\
    cost                DECIMAL(10,2)\
);\
\
-- -----------------------------\
-- 8. Inventory Per Service Center\
-- -----------------------------\
CREATE TABLE inventory (\
    inventory_id        SERIAL PRIMARY KEY,\
    center_id           INT REFERENCES service_centers(center_id),\
    part_id             INT REFERENCES parts(part_id),\
    quantity_available  INT,\
    UNIQUE(center_id, part_id)\
);\
\
CREATE INDEX idx_inventory_center ON inventory(center_id);\
\
-- -----------------------------\
-- 9. Repair-Part Usage Junction\
-- -----------------------------\
CREATE TABLE repair_part_usage (\
    usage_id            SERIAL PRIMARY KEY,\
    repair_id           INT REFERENCES repair_jobs(repair_id),\
    part_id             INT REFERENCES parts(part_id),\
    quantity_used       INT DEFAULT 1\
);\
\
CREATE INDEX idx_rpu_repair ON repair_part_usage(repair_id);\
\
-- -----------------------------\
-- END OF FILE\
-- -----------------------------\
}