CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    enrollment_year INT
);

CREATE TABLE exams (
    exam_id SERIAL PRIMARY KEY,
    exam_name TEXT,
    exam_date DATE
);

CREATE TABLE applications (
    application_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    exam_id INT REFERENCES exams(exam_id),
    score NUMERIC(5,2),
    status TEXT 
);

INSERT INTO students (first_name, last_name, enrollment_year)
SELECT
    (ARRAY['Alice','Bob','Charlie','Diana','Eve','Frank','Grace','Hank','Ivy','Jack'])[floor(random()*10+1)],
    (ARRAY['Smith','Johnson','Williams','Brown','Jones','Miller','Davis','Garcia','Rodriguez','Wilson'])[floor(random()*10+1)],
    (2020 + floor(random()*5))
FROM generate_series(1, 100);

INSERT INTO exams (exam_name, exam_date)
SELECT
    (ARRAY['Math','Physics','Chemistry','Biology','History','English','Computer Science'])[floor(random()*7+1)],
    CURRENT_DATE - (floor(random()*365)) * INTERVAL '1 day'
FROM generate_series(1, 20);

INSERT INTO applications (student_id, exam_id, score, status)
SELECT
    floor(random()*100 + 1),  
    floor(random()*20 + 1),    
    round((random()*100)::numeric, 2),
    (ARRAY['passed','failed','absent'])[floor(random()*3+1)]
FROM generate_series(1, 500);