538 lines
17 KiB
Python
538 lines
17 KiB
Python
import sqlite3
|
|
import sys
|
|
|
|
def create_tables():
|
|
"""Create all core database tables"""
|
|
|
|
conn = sqlite3.connect('school_schedule.db')
|
|
cursor = conn.cursor()
|
|
|
|
# Drop tables if they exist (for testing)
|
|
tables = [
|
|
'students', 'teachers', 'subjects', 'rooms',
|
|
'classes', 'timeslots', 'schedule_entries',
|
|
'tech_tracks', 'english_levels', 'special_groups',
|
|
'users', 'audit_log'
|
|
]
|
|
|
|
for table in tables:
|
|
try:
|
|
cursor.execute(f"DROP TABLE IF EXISTS {table}")
|
|
except:
|
|
pass
|
|
|
|
# 1. Core Entities
|
|
cursor.execute('''
|
|
CREATE TABLE students (
|
|
student_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
full_name TEXT NOT NULL,
|
|
date_of_birth DATE,
|
|
class_group TEXT,
|
|
emergency_contact TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE teachers (
|
|
teacher_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
full_name TEXT NOT NULL,
|
|
department TEXT,
|
|
email TEXT,
|
|
qualifications TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE subjects (
|
|
subject_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
subject_name TEXT NOT NULL,
|
|
department TEXT,
|
|
credits INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE rooms (
|
|
room_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_number TEXT NOT NULL,
|
|
capacity INTEGER,
|
|
equipment TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
# 2. Academic Structure
|
|
cursor.execute('''
|
|
CREATE TABLE classes (
|
|
class_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
class_name TEXT NOT NULL,
|
|
homeroom_teacher_id INTEGER,
|
|
academic_year TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (homeroom_teacher_id) REFERENCES teachers(teacher_id)
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE tech_tracks (
|
|
track_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
track_name TEXT NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE english_levels (
|
|
level_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
level_code TEXT NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE special_groups (
|
|
group_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
group_name TEXT NOT NULL,
|
|
group_type TEXT,
|
|
teacher_in_charge_id INTEGER,
|
|
max_participants INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (teacher_in_charge_id) REFERENCES teachers(teacher_id)
|
|
)
|
|
''')
|
|
|
|
# 3. Scheduling Core
|
|
cursor.execute('''
|
|
CREATE TABLE timeslots (
|
|
timeslot_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
day_of_week TEXT NOT NULL,
|
|
period_number INTEGER,
|
|
start_time TIME NOT NULL,
|
|
end_time TIME NOT NULL,
|
|
academic_module TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE schedule_entries (
|
|
entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
student_id INTEGER NOT NULL,
|
|
teacher_id INTEGER NOT NULL,
|
|
subject_id INTEGER NOT NULL,
|
|
room_id INTEGER,
|
|
timeslot_id INTEGER NOT NULL,
|
|
academic_year TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(student_id),
|
|
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),
|
|
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id),
|
|
FOREIGN KEY (room_id) REFERENCES rooms(room_id),
|
|
FOREIGN KEY (timeslot_id) REFERENCES timeslots(timeslot_id)
|
|
)
|
|
''')
|
|
|
|
# 4. Security System
|
|
cursor.execute('''
|
|
CREATE TABLE users (
|
|
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
email TEXT,
|
|
last_login TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE audit_log (
|
|
log_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
action TEXT NOT NULL,
|
|
table_name TEXT NOT NULL,
|
|
record_id INTEGER,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id)
|
|
)
|
|
''')
|
|
|
|
# 5. Relationship tables
|
|
cursor.execute('''
|
|
CREATE TABLE student_subjects (
|
|
student_id INTEGER NOT NULL,
|
|
subject_id INTEGER NOT NULL,
|
|
PRIMARY KEY (student_id, subject_id),
|
|
FOREIGN KEY (student_id) REFERENCES students(student_id),
|
|
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE student_tech_tracks (
|
|
student_id INTEGER NOT NULL,
|
|
track_id INTEGER NOT NULL,
|
|
PRIMARY KEY (student_id, track_id),
|
|
FOREIGN KEY (student_id) REFERENCES students(student_id),
|
|
FOREIGN KEY (track_id) REFERENCES tech_tracks(track_id)
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE student_english_levels (
|
|
student_id INTEGER NOT NULL,
|
|
level_id INTEGER NOT NULL,
|
|
PRIMARY KEY (student_id, level_id),
|
|
FOREIGN KEY (student_id) REFERENCES students(student_id),
|
|
FOREIGN KEY (level_id) REFERENCES english_levels(level_id)
|
|
)
|
|
''')
|
|
|
|
cursor.execute('''
|
|
CREATE TABLE student_groups (
|
|
student_id INTEGER NOT NULL,
|
|
group_id INTEGER NOT NULL,
|
|
PRIMARY KEY (student_id, group_id),
|
|
FOREIGN KEY (student_id) REFERENCES students(student_id),
|
|
FOREIGN KEY (group_id) REFERENCES special_groups(group_id)
|
|
)
|
|
''')
|
|
|
|
conn.commit()
|
|
print("✅ All tables created successfully!")
|
|
return conn
|
|
|
|
def insert_dummy_data(conn):
|
|
"""Insert sample data into all tables"""
|
|
|
|
cursor = conn.cursor()
|
|
|
|
# 1. Insert Teachers
|
|
teachers = [
|
|
('Ms. Johnson', 'Mathematics', 'johnson@school.edu', 'MSc Mathematics'),
|
|
('Mr. Smith', 'Science', 'smith@school.edu', 'PhD Physics'),
|
|
('Ms. Davis', 'English', 'davis@school.edu', 'MA English Literature'),
|
|
('Mr. Wilson', 'Technology', 'wilson@school.edu', 'BSc Computer Science'),
|
|
('Ms. Garcia', 'Arts', 'garcia@school.edu', 'MFA Visual Arts')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO teachers (full_name, department, email, qualifications)
|
|
VALUES (?, ?, ?, ?)
|
|
''', teachers)
|
|
|
|
# 2. Insert Students
|
|
students = [
|
|
('Alex Chen', '2009-03-15', '4A', '+1-555-0101'),
|
|
('Sofia Rodriguez', '2009-07-22', '4A', '+1-555-0102'),
|
|
('James Wilson', '2009-01-10', '4B', '+1-555-0103'),
|
|
('Maya Patel', '2009-11-30', '4B', '+1-555-0104'),
|
|
('Ethan Brown', '2009-05-18', '4C', '+1-555-0105'),
|
|
('Isabella Kim', '2009-09-05', '4C', '+1-555-0106')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO students (full_name, date_of_birth, class_group, emergency_contact)
|
|
VALUES (?, ?, ?, ?)
|
|
''', students)
|
|
|
|
# 3. Insert Subjects
|
|
subjects = [
|
|
('Mathematics', 'STEM', 4),
|
|
('Physics', 'STEM', 4),
|
|
('English Language', 'Languages', 4),
|
|
('Computer Science', 'STEM', 3),
|
|
('Art', 'Arts', 2),
|
|
('Physical Education', 'Sports', 2)
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO subjects (subject_name, department, credits)
|
|
VALUES (?, ?, ?)
|
|
''', subjects)
|
|
|
|
# 4. Insert Rooms
|
|
rooms = [
|
|
('204', 30, 'Projector, Whiteboard'),
|
|
('105', 25, 'Lab Equipment, Computers'),
|
|
('312', 35, 'Whiteboard, Audio System'),
|
|
('101', 20, 'Art Supplies, Easels'),
|
|
('Gym', 100, 'Sports Equipment'),
|
|
('Library', 50, 'Computers, Books')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO rooms (room_number, capacity, equipment)
|
|
VALUES (?, ?, ?)
|
|
''', rooms)
|
|
|
|
# 5. Insert Classes
|
|
classes = [
|
|
('4A', 1, '2025-2026'),
|
|
('4B', 2, '2025-2026'),
|
|
('4C', 3, '2025-2026')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO classes (class_name, homeroom_teacher_id, academic_year)
|
|
VALUES (?, ?, ?)
|
|
''', classes)
|
|
|
|
# 6. Insert Tech Tracks
|
|
tech_tracks = [
|
|
('Design & Creativity', 'Graphic design, product design'),
|
|
('Culinary Arts', 'Cooking, baking, food science'),
|
|
('Industrial Technology', 'Engineering, manufacturing')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO tech_tracks (track_name, description)
|
|
VALUES (?, ?)
|
|
''', tech_tracks)
|
|
|
|
# 7. Insert English Levels
|
|
english_levels = [
|
|
('E1', 'Beginner English'),
|
|
('E2', 'Elementary English'),
|
|
('E3', 'Pre-Intermediate'),
|
|
('E4', 'Intermediate'),
|
|
('E5', 'Upper-Intermediate'),
|
|
('E6', 'Advanced')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO english_levels (level_code, description)
|
|
VALUES (?, ?)
|
|
''', english_levels)
|
|
|
|
# 8. Insert Special Groups
|
|
special_groups = [
|
|
('Math Olympiad', 'Competition', 1, 15),
|
|
('Science Club', 'Club', 2, 20),
|
|
('Debate Team', 'Competition', 3, 10),
|
|
('Robotics Club', 'Club', 4, 12)
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO special_groups (group_name, group_type, teacher_in_charge_id, max_participants)
|
|
VALUES (?, ?, ?, ?)
|
|
''', special_groups)
|
|
|
|
# 9. Insert Timeslots
|
|
timeslots = [
|
|
('Monday', 1, '08:30', '09:15', 'Module 1'),
|
|
('Monday', 2, '09:20', '10:05', 'Module 1'),
|
|
('Monday', 3, '10:30', '11:15', 'Module 1'),
|
|
('Monday', 4, '11:20', '12:05', 'Module 1'),
|
|
('Tuesday', 1, '08:30', '09:15', 'Module 1'),
|
|
('Tuesday', 2, '09:20', '10:05', 'Module 1'),
|
|
('Wednesday', 1, '08:30', '09:15', 'Module 1'),
|
|
('Thursday', 1, '08:30', '09:15', 'Module 1'),
|
|
('Friday', 1, '08:30', '09:15', 'Module 1')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO timeslots (day_of_week, period_number, start_time, end_time, academic_module)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
''', timeslots)
|
|
|
|
# 10. Insert Schedule Entries
|
|
schedule_entries = [
|
|
(1, 1, 1, 1, 1, '2025-2026'), # Alex, Ms. Johnson, Math, Room 204, Monday 8:30
|
|
(2, 2, 3, 2, 2, '2025-2026'), # Sofia, Mr. Smith, English, Room 105, Monday 9:20
|
|
(3, 3, 2, 3, 3, '2025-2026'), # James, Ms. Davis, Physics, Room 312, Monday 10:30
|
|
(4, 4, 4, 4, 4, '2025-2026'), # Maya, Mr. Wilson, CS, Room 101, Monday 11:20
|
|
(5, 5, 5, 5, 5, '2025-2026'), # Ethan, Ms. Garcia, Art, Gym, Tuesday 8:30
|
|
(6, 1, 6, 6, 6, '2025-2026') # Isabella, Ms. Johnson, PE, Library, Tuesday 9:20
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO schedule_entries (student_id, teacher_id, subject_id, room_id, timeslot_id, academic_year)
|
|
VALUES (?, ?, ?, ?, ?, ?)
|
|
''', schedule_entries)
|
|
|
|
# 11. Insert Relationship Data
|
|
# Student-Subject relationships (students taking multiple subjects)
|
|
student_subjects = [
|
|
(1, 1), (1, 2), (1, 3), # Alex takes Math, Physics, English
|
|
(2, 1), (2, 4), (2, 5), # Sofia takes Math, CS, Art
|
|
(3, 2), (3, 3), (3, 6), # James takes Physics, English, PE
|
|
(4, 4), (4, 5), (4, 6), # Maya takes CS, Art, PE
|
|
(5, 1), (5, 2), (5, 4), # Ethan takes Math, Physics, CS
|
|
(6, 3), (6, 5), (6, 6) # Isabella takes English, Art, PE
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO student_subjects (student_id, subject_id)
|
|
VALUES (?, ?)
|
|
''', student_subjects)
|
|
|
|
# Student-Tech Track relationships
|
|
student_tech_tracks = [
|
|
(1, 1), # Alex in Design
|
|
(2, 2), # Sofia in Culinary
|
|
(3, 3), # James in Industrial
|
|
(4, 1), # Maya in Design
|
|
(5, 2), # Ethan in Culinary
|
|
(6, 3) # Isabella in Industrial
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO student_tech_tracks (student_id, track_id)
|
|
VALUES (?, ?)
|
|
''', student_tech_tracks)
|
|
|
|
# Student-English Level relationships
|
|
student_english_levels = [
|
|
(1, 4), # Alex in E4
|
|
(2, 5), # Sofia in E5
|
|
(3, 3), # James in E3
|
|
(4, 6), # Maya in E6
|
|
(5, 2), # Ethan in E2
|
|
(6, 4) # Isabella in E4
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO student_english_levels (student_id, level_id)
|
|
VALUES (?, ?)
|
|
''', student_english_levels)
|
|
|
|
# Student-Group relationships
|
|
student_groups = [
|
|
(1, 1), (1, 3), # Alex in Math Olympiad & Debate
|
|
(2, 2), (2, 4), # Sofia in Science & Robotics
|
|
(3, 1), (3, 4), # James in Math Olympiad & Robotics
|
|
(4, 2), (4, 3), # Maya in Science & Debate
|
|
(5, 1), # Ethan in Math Olympiad
|
|
(6, 2), (6, 4) # Isabella in Science & Robotics
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO student_groups (student_id, group_id)
|
|
VALUES (?, ?)
|
|
''', student_groups)
|
|
|
|
# 12. Insert Users
|
|
users = [
|
|
('admin', 'hashed_password_123', 'administrator', 'admin@school.edu'),
|
|
('teacher1', 'hashed_password_456', 'teacher', 'johnson@school.edu'),
|
|
('teacher2', 'hashed_password_789', 'teacher', 'smith@school.edu'),
|
|
('student1', 'hashed_password_abc', 'student', 'alex@school.edu')
|
|
]
|
|
|
|
cursor.executemany('''
|
|
INSERT INTO users (username, password_hash, role, email)
|
|
VALUES (?, ?, ?, ?)
|
|
''', users)
|
|
|
|
conn.commit()
|
|
print("✅ Dummy data inserted successfully!")
|
|
|
|
# Show table counts
|
|
tables = ['students', 'teachers', 'subjects', 'rooms', 'classes',
|
|
'tech_tracks', 'english_levels', 'special_groups',
|
|
'timeslots', 'schedule_entries', 'users']
|
|
|
|
print("\n📊 Database Statistics:")
|
|
for table in tables:
|
|
cursor.execute(f"SELECT COUNT(*) FROM {table}")
|
|
count = cursor.fetchone()[0]
|
|
print(f" {table}: {count} records")
|
|
|
|
def main():
|
|
"""Main function to create database and insert data"""
|
|
print("🏫 School Schedule Database Setup")
|
|
print("=" * 40)
|
|
|
|
try:
|
|
# Create database and tables
|
|
conn = create_tables()
|
|
|
|
# Insert dummy data
|
|
insert_dummy_data(conn)
|
|
|
|
# Test queries
|
|
cursor = conn.cursor()
|
|
|
|
print("\n🔍 Sample Queries:")
|
|
print("=" * 40)
|
|
|
|
# Query 1: Get student schedule
|
|
print("\n1. Alex Chen's Schedule:")
|
|
cursor.execute('''
|
|
SELECT s.full_name, t.full_name as teacher, sub.subject_name,
|
|
r.room_number, ts.day_of_week, ts.start_time, ts.end_time
|
|
FROM schedule_entries se
|
|
JOIN students s ON se.student_id = s.student_id
|
|
JOIN teachers t ON se.teacher_id = t.teacher_id
|
|
JOIN subjects sub ON se.subject_id = sub.subject_id
|
|
JOIN rooms r ON se.room_id = r.room_id
|
|
JOIN timeslots ts ON se.timeslot_id = ts.timeslot_id
|
|
WHERE s.full_name = 'Alex Chen'
|
|
ORDER BY ts.day_of_week, ts.start_time
|
|
''')
|
|
|
|
for row in cursor.fetchall():
|
|
print(f" {row[0]} has {row[2]} with {row[1]} in Room {row[3]} on {row[4]} {row[5]}-{row[6]}")
|
|
|
|
# Query 2: Count students in each group
|
|
print("\n2. Group Participation:")
|
|
cursor.execute('''
|
|
SELECT sg.group_name, COUNT(sg.student_id) as student_count
|
|
FROM student_groups sg
|
|
JOIN special_groups g ON sg.group_id = g.group_id
|
|
GROUP BY g.group_name
|
|
ORDER BY student_count DESC
|
|
''')
|
|
|
|
for row in cursor.fetchall():
|
|
print(f" {row[0]}: {row[1]} students")
|
|
|
|
# Query 3: Find available rooms at a specific time
|
|
print("\n3. Room 204 Schedule on Monday:")
|
|
cursor.execute('''
|
|
SELECT ts.start_time, ts.end_time, t.full_name as teacher,
|
|
s.full_name as student, sub.subject_name
|
|
FROM schedule_entries se
|
|
JOIN timeslots ts ON se.timeslot_id = ts.timeslot_id
|
|
JOIN teachers t ON se.teacher_id = t.teacher_id
|
|
JOIN students s ON se.student_id = s.student_id
|
|
JOIN subjects sub ON se.subject_id = sub.subject_id
|
|
JOIN rooms r ON se.room_id = r.room_id
|
|
WHERE r.room_number = '204' AND ts.day_of_week = 'Monday'
|
|
ORDER BY ts.start_time
|
|
''')
|
|
|
|
for row in cursor.fetchall():
|
|
print(f" {row[0]}-{row[1]}: {row[2]} teaching {row[4]} to {row[3]}")
|
|
|
|
# Save SQL file
|
|
print("\n💾 Exporting SQL statements to 'database_setup.sql'...")
|
|
with open('database_setup.sql', 'w') as f:
|
|
# Get schema
|
|
cursor.execute("SELECT sql FROM sqlite_master WHERE type='table'")
|
|
for row in cursor.fetchall():
|
|
f.write(row[0] + ";\n\n")
|
|
|
|
# Get data (simplified)
|
|
f.write("-- Sample INSERT statements would go here\n")
|
|
|
|
print("✅ Database setup complete!")
|
|
print(f"📁 Database file: school_schedule.db")
|
|
print(f"📄 SQL file: database_setup.sql")
|
|
|
|
conn.close()
|
|
|
|
except Exception as e:
|
|
print(f"❌ Error: {e}")
|
|
sys.exit(1)
|
|
|
|
if __name__ == "__main__":
|
|
main() |