Languages

%xmode minimal
import datajoint as dj
dj.config['display.limit'] = 6  # keep output concise

# Create schema
schema = dj.Schema('languages_example')

@schema
class Language(dj.Lookup):
    definition = """
    lang_code : char(2)  # ISO 639-1 language code (e.g., 'en', 'es', 'ja')
    ---
    language : varchar(30)  # Full language name
    native_name : varchar(50)  # Language name in its native script
    """
    contents = [
        # Format: (code, language, native_name, family)
        ('ar', 'Arabic', 'العربية'), ('da', 'Danish', 'Dansk'),
        ('de', 'German', 'Deutsch'), ('el', 'Greek', 'Ελληνικά'),
        ('en', 'English', 'English'), ('es', 'Spanish', 'Español'),
        ('fi', 'Finnish', 'Suomi'), ('fr', 'French', 'Français'),
        ('he', 'Hebrew', 'עברית'), ('hi', 'Hindi', 'हिन्दी'),
        ('id', 'Indonesian', 'Bahasa Indonesia'),
        ('it', 'Italian', 'Italiano'), ('ja', 'Japanese', '日本語'),
        ('ko', 'Korean', '한국어'), ('ms', 'Malay', 'Bahasa Melayu'),
        ('nl', 'Dutch', 'Nederlands'), ('no', 'Norwegian', 'Norsk'),
        ('ph', 'Filipino', 'Tagalog'), ('pl', 'Polish', 'Polski'),
        ('pt', 'Portuguese', 'Português'), ('ru', 'Russian', 'Русский'),
        ('sa', 'Sanskrit', 'संस्कृतम्'), ('sv', 'Swedish', 'Svenska'),
        ('th', 'Thai', 'ไทย'), ('tr', 'Turkish', 'Türkçe'),
        ('uk', 'Ukrainian', 'Українська'),
        ('vi', 'Vietnamese', 'Tiếng Việt'), ('zh', 'Chinese', '中文')]

@schema
class CEFRLevel(dj.Lookup):
    definition = """
    cefr_level : char(2)  # CEFR proficiency level code (A1, A2, B1, B2, C1, C2)
    ---
    level_name : varchar(30)  # Full name of the level
    category : enum('Basic', 'Independent', 'Proficient')  # User category
    description : varchar(255)  # Brief description of abilities at this level
    """
    contents = [
        ('A1', 'Beginner', 'Basic', 
         'Can understand and use familiar everyday expressions and very basic phrases'),
        ('A2', 'Elementary', 'Basic',
         'Can communicate in simple routine tasks requiring direct exchange of information'),
        ('B1', 'Intermediate', 'Independent',
         'Can deal with most situations while traveling and produce simple connected text'),
        ('B2', 'Upper Intermediate', 'Independent',
         'Can interact with fluency and spontaneity and produce clear, detailed text'),
        ('C1', 'Advanced', 'Proficient',
         'Can express ideas fluently and use language flexibly for social and professional purposes'),
        ('C2', 'Mastery', 'Proficient',
         'Can understand virtually everything and express themselves with precision')
    ]

@schema
class Person(dj.Manual):
    definition = """
    person_id : int  # Unique identifier for each person
    ---
    name : varchar(60)  # Person's name
    date_of_birth : date  # Date of birth
    """

@schema
class Proficiency(dj.Manual):
    definition = """
    -> Person
    -> Language
    ---
    -> CEFRLevel
    """

dj.Diagram(schema)

# Generate sample people data using Faker
import numpy as np
from faker import Faker

fake = Faker()

# Set seed for reproducible results
np.random.seed(42)
fake.seed_instance(42)

# Generate n people with diverse backgrounds
n = 500  # number of people to generate
Person.insert(
    {
        'person_id': i,
        'name': fake.name(),
        'date_of_birth': fake.date_of_birth(minimum_age=18, maximum_age=80)
    } for i in range(n))

Person()

lang_keys = Language.fetch("KEY")
cefr_keys = CEFRLevel.fetch("KEY")
# Weight probabilities: more people at intermediate levels than extremes
cefr_probabilities = [0.08, 0.12, 0.13, 0.17, 0.20, 0.30]
average_languages = 2.5

for person_key in Person.fetch("KEY"):
    num_languages = np.random.poisson(average_languages)
    Proficiency.insert(
        {
            **person_key,
            **lang_key,
            **np.random.choice(cefr_keys, p=cefr_probabilities)
        } for lang_key in np.random.choice(lang_keys, num_languages, replace=False)
    )

Proficiency()

# Query 1: Find the names of all proficient English speakers (C1 or C2)
Person.proj('name') & (Proficiency & {'lang_code': 'en'} & 'cefr_level in ("C1", "C2")')

# Query 2: Names of people who speak English or Spanish at any level 
Person.proj('name') & (Proficiency & 'lang_code in ("en", "es")')

# Query 3: Names of people who speak English AND Spanish at any level 
Person.proj('name') & (Proficiency & {'lang_code': 'en'}) & (Proficiency & {'lang_code': 'es'}) 

# Query 4: Show the peole who speak at least four languages
Person.aggr(Proficiency, 'name',
            nlanguages='count(lang_code)', languages='GROUP_CONCAT(lang_code)'
            ) & 'nlanguages >= 4'

# Query 5: Show the top 3 people by number of languages spoken
Person.aggr(Proficiency, 'name',
            nlanguages='count(lang_code)'
            ) & dj.Top(3, order_by='nlanguages desc')

# Query 6: Show all the people Lindsay Roman (person_id=7) can communicate wtih

Person & (
    Proficiency * Proficiency.proj(other_person='person_id') & {'other_person': 7}
    )

# Query 7: Find people with at least intermediate proficiency (B1+) in Spanish
Person.proj('name') & (Proficiency & {'lang_code': 'es'} & 'cefr_level >= "B1"')

# Query 8: Show all languages and the number of people who speak them
Language.aggr(Proficiency, 'language', nspeakers='count(person_id)')

# Query 9: Count people at each CEFR level for English
(CEFRLevel).aggr(Proficiency & {'lang_code': 'en'}, nspeakers='count(person_id)')

# Query 10: Show the top 3 languages by number of speakers
Language.aggr(Proficiency, 'language', nspeakers='count(person_id)') & dj.Top(3, order_by='nspeakers desc')

# Query 12: Show language skills with full CEFR level descriptions for person 0
# This demonstrates the benefit of having CEFRLevel as a lookup table
(Person * Proficiency * Language * CEFRLevel).proj(
    'name', 'language', 'level_name', 'category', 'description'
) & {'person_id': 0}

# Query 11: Find polyglots with C1 and C2 (mastery) level in multiple languages
Person.aggr(
    Proficiency & 'cefr_level>="C1"',
    'name',
    num_mastered='count(*)'
) & 'num_mastered >= 2'

# If you need to re-run this example, you can drop the schema by uncommenting the following line:

# schema.drop()