Hugging Face's logo

Spaces:
laudes
/
ai_eee_sql_gen
Runtime error

App Files Community
ai_eee_sql_gen / schema.md
laudes's picture
laudes
Upload folder using huggingface_hub
909cddd verified
preview code
|
raw
history blame contribute delete
8.47 kB

A newer version of the Gradio SDK is available: 6.5.1

Upgrade

Request

IMPORTANT ! Use the "Database Schema" below

Here’s a list describing what I want you to asist me with:

  1. Generate Demo Data: Create demo data for a PostgreSQL database with at least 25 entries per table, considering the relationships between the tables.

  2. Focus on South African Context: The demo data must reflect South African specifics, such as:

    • South African names for agents, clients, and learners.
    • South African locations (cities and towns) for agents, clients, and class sites.
    • South African phone numbers (+27 format).
    • Realistic South African ID numbers.

  3. Tables to Include: Populate demo data for the following 13 tables:

    • agents
    • clients
    • classes
    • learners
    • assessments
    • progressions
    • tasks
    • deliveries
    • events
    • reports
    • agent_assignments
    • agent_availability
    • agent_work_history

  4. SQL Format: The data should be returned in SQL INSERT INTO statements with multiple rows, following a format like:

    INSERT INTO agents (name, email, phone, specialization, experience, current_workload, location, status, created_at, updated_at) VALUES
('Thabo Mokoena', 'thabo.mokoena@example.co.za', '+27831234567', 'Math Tutor', 5, 3, 'Johannesburg', 'Active', NOW(), NOW()),
('Naledi Khumalo', 'naledi.khumalo@example.co.za', '+27836543210', 'Science Instructor', 8, 2, 'Pretoria', 'Active', NOW(), NOW());

  5. Ensure Data Consistency: The generated data should respect the foreign key relationships between the tables, ensuring that IDs referenced in one table (e.g., class_id) exist in the appropriate related table.

  6. Provide the Data as a Downloadable SQL File: Finally, package the SQL insertions for all 13 tables into a downloadable .sql file.

Database Schema:

Table agent_availability (Comment: None):

  • id (integer) (Comment: None)
  • agent_id (integer) (Comment: None)
  • available_date (date) (Comment: None)
  • availability_status (character varying) (Comment: None)
  • reason (text) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Table agent_work_history (Comment: None):
  • id (integer) (Comment: None)
  • agent_id (integer) (Comment: None)
  • class_id (integer) (Comment: None)
  • task_id (integer) (Comment: None)
  • start_date (date) (Comment: None)
  • end_date (date) (Comment: None)
  • role (character varying) (Comment: None)
  • performance_notes (text) (Comment: None)
  • reassignment_id (integer) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id)
    • task_id references tasks(id) Table agents (Comment: None):
  • id (integer) (Comment: None)
  • name (character varying) (Comment: None)
  • email (character varying) (Comment: None)
  • phone (character varying) (Comment: None)
  • specialization (character varying) (Comment: None)
  • experience (integer) (Comment: None)
  • current_workload (integer) (Comment: None)
  • location (character varying) (Comment: None)
  • status (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None)
  • sms (character varying) (Comment: None) Table assessments (Comment: None):
  • id (integer) (Comment: None)
  • class_id (integer) (Comment: None)
  • assessment_type (character varying) (Comment: None)
  • assessment_date (date) (Comment: None)
  • result (character varying) (Comment: None)
  • assessor_name (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id) Table classes (Comment: None):
  • id (integer) (Comment: None)
  • client_id (integer) (Comment: None)
  • site (character varying) (Comment: None)
  • start_date (date) (Comment: None)
  • end_date (date) (Comment: None)
  • status (character varying) (Comment: None)
  • attendance_status (character varying) (Comment: None)
  • progression_status (character varying) (Comment: None)
  • subject (character varying) (Comment: None)
  • phase (character varying) (Comment: None)
  • marketer (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Table clients (Comment: None):
  • id (integer) (Comment: None)
  • name (character varying) (Comment: None)
  • contact_person (character varying) (Comment: None)
  • email (character varying) (Comment: None)
  • phone (character varying) (Comment: None)
  • address (text) (Comment: None)
  • status (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Table deliveries (Comment: None):
  • id (integer) (Comment: None)
  • class_id (integer) (Comment: None)
  • delivery_type (character varying) (Comment: None)
  • delivery_date (date) (Comment: None)
  • status (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id) Table events (Comment: None):
  • id (integer) (Comment: None)
  • name (character varying) (Comment: None)
  • client_id (integer) (Comment: None)
  • class_id (integer) (Comment: None)
  • event_type (character varying) (Comment: None)
  • event_date (date) (Comment: None)
  • reminder_date (date) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id) Table learners (Comment: None):
  • id (integer) (Comment: None)
  • name (character varying) (Comment: None)
  • gender (character varying) (Comment: None)
  • race (character varying) (Comment: None)
  • id_number (character varying) (Comment: None)
  • class_id (integer) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id) Table progressions (Comment: None):
  • id (integer) (Comment: None)
  • class_id (integer) (Comment: None)
  • learner_id (integer) (Comment: None)
  • progression_level (character varying) (Comment: None)
  • completion_date (date) (Comment: None)
  • status (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id)
    • learner_id references learners(id) Table reports (Comment: None):
  • id (integer) (Comment: None)
  • report_type (character varying) (Comment: None)
  • related_class_id (integer) (Comment: None)
  • related_client_id (integer) (Comment: None)
  • content (text) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • related_class_id references classes(id) Table tasks (Comment: None):
  • id (integer) (Comment: None)
  • event_id (integer) (Comment: None)
  • description (text) (Comment: None)
  • due_date (date) (Comment: None)
  • status (character varying) (Comment: None)
  • assigned_to (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • event_id references events(id) Table agent_assignments (Comment: None):
  • id (integer) (Comment: None)
  • agent_id (integer) (Comment: None)
  • class_id (integer) (Comment: None)
  • task_id (integer) (Comment: None)
  • assignment_date (date) (Comment: None)
  • reassigned_agent_id (integer) (Comment: None)
  • reassignment_date (date) (Comment: None)
  • reassignment_reason (text) (Comment: None)
  • status (character varying) (Comment: None)
  • created_at (timestamp without time zone) (Comment: None)
  • updated_at (timestamp without time zone) (Comment: None) Foreign Keys:
    • class_id references classes(id)
    • task_id references tasks(id)