abhi-bit-2/Genetic_Algorithm
0
1import sqlite32import random3 4DAYS = 5 # Monday to Friday5SLOTS_PER_DAY = 9 # 9 slots per day6DB_PATH = "timetable.db"7 8def get_course_classes():9 """Get the number of classes per course from the database."""10 with sqlite3.connect(DB_PATH) as conn:11 cursor = conn.cursor()12 cursor.execute("""13 SELECT cc.course_code, cc.num_classes, c.course_name, c.branch_name14 FROM course_classes cc15 JOIN courses c ON cc.course_code = c.course_code16 ORDER BY c.branch_name, c.course_code17 """)18 return cursor.fetchall()19 20def set_course_classes(course_code, num_classes):21 """Set the number of classes per course in the database."""22 with sqlite3.connect(DB_PATH) as conn:23 cursor = conn.cursor()24 try:25 cursor.execute(26 "UPDATE course_classes SET num_classes = ? WHERE course_code = ?",27 (num_classes, course_code)28 )29 conn.commit()30 return True31 except sqlite3.Error as e:32 conn.rollback()33 print(f" Database error: {e}")34 return False35 36def fetch_data_from_db():37 """Fetch courses, teachers, rooms, and branch mappings from the database."""38 with sqlite3.connect(DB_PATH) as conn:39 cursor = conn.cursor()40 41 # Fetch all required data in one function42 cursor.execute("SELECT course_code, branch_name, course_name FROM courses")43 courses = cursor.fetchall()44 45 cursor.execute("""46 SELECT t.teacher_name, c.course_code, t.branch_name47 FROM branch_teacher_courses btc48 JOIN teachers t ON btc.teacher_id = t.teacher_id49 JOIN courses c ON btc.course_code = c.course_code50 WHERE t.branch_name = c.branch_name51 """)52 teachers = cursor.fetchall()53 54 cursor.execute("SELECT room_name FROM rooms")55 rooms = [room[0] for room in cursor.fetchall()]56 57 # Print debug information58 print(f"Fetched {len(courses)} courses, {len(teachers)} teacher-course-branch mappings, and {len(rooms)} rooms")59 60 # Check if we have valid data61 if not courses or not teachers or not rooms:62 print(" WARNING: Missing data in the database!")63 if not courses: print(" - No courses found")64 if not teachers: print(" - No teacher-course-branch mappings found")65 if not rooms: print(" - No rooms found")66 67 return courses, teachers, rooms68 69def add_professor(teacher_name, branch_name):70 """Add a new professor to the database with branch assignment."""71 with sqlite3.connect(DB_PATH) as conn:72 cursor = conn.cursor()73 try:74 # Check if branch exists75 cursor.execute("SELECT branch_id FROM branches WHERE branch_name = ?", (branch_name,))76 if not cursor.fetchone():77 print(f" Branch '{branch_name}' does not exist. Please add it first.")78 return False79 80 cursor.execute(81 "INSERT INTO teachers (teacher_name, branch_name) VALUES (?, ?)",82 (teacher_name, branch_name),83 )84 conn.commit()85 print(f" Professor {teacher_name} added successfully to branch {branch_name}")86 return True87 except sqlite3.Error as e:88 print(f"Database error: {e}")89 return False90 91def get_all_professors():92 """Get all professors from the database."""93 with sqlite3.connect(DB_PATH) as conn:94 cursor = conn.cursor()95 cursor.execute("""96 SELECT teacher_id, teacher_name, branch_name97 FROM teachers98 ORDER BY branch_name, teacher_name99 """)100 return cursor.fetchall()101 102def get_professor_courses(teacher_id):103 """Get all courses taught by a professor."""104 with sqlite3.connect(DB_PATH) as conn:105 cursor = conn.cursor()106 cursor.execute("""107 SELECT btc.course_code, c.course_name, c.branch_name108 FROM branch_teacher_courses btc109 JOIN courses c ON btc.course_code = c.course_code110 WHERE btc.teacher_id = ?111 ORDER BY c.branch_name, c.course_name112 """, (teacher_id,))113 return cursor.fetchall()114 115def get_available_courses_for_professor(teacher_id):116 """Get all courses available for a professor to teach based on their branch."""117 with sqlite3.connect(DB_PATH) as conn:118 cursor = conn.cursor()119 # Get professor's branch and available courses in one transaction120 cursor.execute("SELECT branch_name FROM teachers WHERE teacher_id = ?", (teacher_id,))121 branch = cursor.fetchone()122 123 if not branch:124 return []125 126 cursor.execute("""127 SELECT c.course_code, c.course_name128 FROM courses c129 WHERE c.branch_name = ?130 AND c.course_code NOT IN (131 SELECT btc.course_code132 FROM branch_teacher_courses btc133 WHERE btc.teacher_id = ?134 )135 ORDER BY c.course_name136 """, (branch[0], teacher_id))137 return cursor.fetchall()138 139def add_course_to_professor(teacher_id, course_code):140 """Assign a course to a professor."""141 with sqlite3.connect(DB_PATH) as conn:142 cursor = conn.cursor()143 try:144 # Validate professor and course in the same branch145 cursor.execute("SELECT branch_name FROM teachers WHERE teacher_id = ?", (teacher_id,))146 prof_result = cursor.fetchone()147 if not prof_result:148 print(f" Professor with ID {teacher_id} not found.")149 return False150 prof_branch = prof_result[0]151 152 cursor.execute("SELECT branch_name FROM courses WHERE course_code = ?", (course_code,))153 course_result = cursor.fetchone()154 if not course_result:155 print(f" Course with code {course_code} not found.")156 return False157 course_branch = course_result[0]158 159 if prof_branch != course_branch:160 print(f" Professor's branch ({prof_branch}) does not match course's branch ({course_branch}).")161 return False162 163 # Check for existing mapping164 cursor.execute(165 "SELECT 1 FROM branch_teacher_courses WHERE teacher_id = ? AND course_code = ?",166 (teacher_id, course_code)167 )168 if cursor.fetchone():169 print(f" Professor already teaches this course.")170 return False171 172 # Add the mapping173 cursor.execute(174 "INSERT INTO branch_teacher_courses (teacher_id, course_code) VALUES (?, ?)",175 (teacher_id, course_code)176 )177 conn.commit()178 print(f" Course {course_code} assigned to professor successfully.")179 return True180 except sqlite3.Error as e:181 conn.rollback()182 print(f"Database error: {e}")183 return False184 185def remove_course_from_professor(teacher_id, course_code):186 """Remove a course assignment from a professor."""187 with sqlite3.connect(DB_PATH) as conn:188 cursor = conn.cursor()189 try:190 # Check if mapping exists and delete in one transaction191 cursor.execute(192 "SELECT 1 FROM branch_teacher_courses WHERE teacher_id = ? AND course_code = ?",193 (teacher_id, course_code)194 )195 if not cursor.fetchone():196 print(f" Professor does not teach this course.")197 return False198 199 cursor.execute(200 "DELETE FROM branch_teacher_courses WHERE teacher_id = ? AND course_code = ?",201 (teacher_id, course_code)202 )203 conn.commit()204 print(f" Course {course_code} removed from professor successfully.")205 return True206 except sqlite3.Error as e:207 conn.rollback()208 print(f"Database error: {e}")209 return False210 211def add_course(course_code, course_name, branch_name):212 """Add a new course to the database."""213 with sqlite3.connect(DB_PATH) as conn:214 cursor = conn.cursor()215 try:216 cursor.execute(217 "INSERT INTO courses (course_code, course_name, branch_name) VALUES (?, ?, ?)",218 (course_code, course_name, branch_name),219 )220 # Also add default entry in course_classes221 cursor.execute(222 "INSERT INTO course_classes (course_code, num_classes) VALUES (?, ?)",223 (course_code, 2) # Default to 2 classes per week224 )225 conn.commit()226 print(f" Course '{course_code}' added successfully to branch '{branch_name}'!")227 return True228 except sqlite3.Error as e:229 conn.rollback()230 print(f"Database error: {e}")231 return False232 233def check_teacher_by_course_code(course_code):234 """Check if any teacher is associated with the given course code."""235 with sqlite3.connect(DB_PATH) as conn:236 cursor = conn.cursor()237 try:238 # Get course info and teachers in one transaction239 cursor.execute("SELECT branch_name FROM courses WHERE course_code = ?", (course_code,))240 branch_result = cursor.fetchone()241 242 if not branch_result:243 print(f"❌ Course code '{course_code}' does not exist.")244 return False245 246 branch_name = branch_result[0]247 248 cursor.execute("""249 SELECT t.teacher_name250 FROM branch_teacher_courses btc251 JOIN teachers t ON btc.teacher_id = t.teacher_id252 WHERE btc.course_code = ? AND t.branch_name = ?253 """, (course_code, branch_name))254 255 results = cursor.fetchall()256 257 if results:258 print(f" Teachers associated with course '{course_code}' (Branch: {branch_name}):")259 for row in results:260 print(f"- {row[0]}")261 return True262 else:263 print(f" No teachers found for course '{course_code}' in branch {branch_name}.")264 return False265 266 except sqlite3.Error as e:267 print(f"Database error: {e}")268 return False269 270def fetch_branch_teacher_courses(branch_id):271 """Fetch teachers and courses for a specific branch."""272 with sqlite3.connect(DB_PATH) as conn:273 cursor = conn.cursor()274 cursor.execute("""275 SELECT t.teacher_name, c.course_code, c.course_name276 FROM branch_teacher_courses btc277 JOIN teachers t ON btc.teacher_id = t.teacher_id278 JOIN courses c ON btc.course_code = c.course_code279 WHERE btc.branch_id = ?280 """, (branch_id,))281 return cursor.fetchall()282 283 284def insert_timetable_into_db(schedule):285 """Insert the final timetable into the database."""286 with sqlite3.connect(DB_PATH) as conn:287 cursor = conn.cursor()288 try:289 # Clear existing timetable data290 cursor.execute("DELETE FROM timetable")291 292 # SQL for inserting timetable entries293 insert_sql = """294 INSERT INTO timetable (course_code, course_name, teacher, room, branch, day, slot, class_index)295 VALUES (?, ?, ?, ?, ?, ?, ?, ?)296 """297 298 for day in range(DAYS):299 for slot in range(SLOTS_PER_DAY):300 # Skip empty slots301 if schedule[day][slot] is None:302 continue303 304 # Process the slot data305 slot_data = schedule[day][slot]306 307 # Skip lunch slots308 if (isinstance(slot_data, list) and slot_data and309 hasattr(slot_data[0], 'course_code') and slot_data[0].course_code == "LUNCH"):310 continue311 elif (not isinstance(slot_data, list) and hasattr(slot_data, 'course_code') and312 slot_data.course_code == "LUNCH"):313 continue314 315 # Handle list of classes in a slot316 if isinstance(slot_data, list):317 for class_index, class_data in enumerate(slot_data):318 if class_data is None:319 continue320 321 # Extract data from class_data322 if hasattr(class_data, 'to_tuple'):323 data_tuple = class_data.to_tuple()324 if len(data_tuple) >= 7:325 course_code, course_name, teacher, room, branch, d, s = data_tuple326 cursor.execute(insert_sql,327 (course_code, course_name, teacher, room, branch, d, s, class_index))328 elif isinstance(class_data, tuple) and len(class_data) >= 7:329 course_code, course_name, teacher, room, branch, d, s = class_data330 cursor.execute(insert_sql,331 (course_code, course_name, teacher, room, branch, d, s, class_index))332 333 # Handle single class in a slot334 elif hasattr(slot_data, 'to_tuple'):335 data_tuple = slot_data.to_tuple()336 if len(data_tuple) >= 7:337 course_code, course_name, teacher, room, branch, d, s = data_tuple338 cursor.execute(insert_sql,339 (course_code, course_name, teacher, room, branch, d, s, 0))340 341 # Handle tuple case (backward compatibility)342 elif isinstance(slot_data, tuple) and len(slot_data) >= 7:343 course_code, course_name, teacher, room, branch, d, s = slot_data344 cursor.execute(insert_sql,345 (course_code, course_name, teacher, room, branch, d, s, 0))346 347 conn.commit()348 print(" Timetable inserted successfully into the database!")349 return True350 except sqlite3.Error as e:351 conn.rollback()352 print(f" Database error: {e}")353 return False354 355 356def add_branch_teacher_course_mapping(branch_name, teacher_name, course_code):357 """Add a mapping between a branch, teacher, and course."""358 with sqlite3.connect(DB_PATH) as conn:359 cursor = conn.cursor()360 try:361 # Validate branch, teacher, and course in one transaction362 cursor.execute("""363 SELECT b.branch_id, t.teacher_id, t.branch_name, c.branch_name364 FROM branches b365 LEFT JOIN teachers t ON t.teacher_name = ? AND t.branch_name = b.branch_name366 LEFT JOIN courses c ON c.course_code = ? AND c.branch_name = b.branch_name367 WHERE b.branch_name = ?368 """, (teacher_name, course_code, branch_name))369 370 result = cursor.fetchone()371 if not result:372 print(f" Branch '{branch_name}' does not exist.")373 return False374 375 branch_id, teacher_id, teacher_branch, course_branch = result376 377 # Validate teacher378 if not teacher_id:379 print(f" Teacher '{teacher_name}' does not exist or is not in branch '{branch_name}'.")380 return False381 382 # Validate course383 if not course_branch:384 print(f" Course '{course_code}' does not exist or is not in branch '{branch_name}'.")385 return False386 387 # Check if mapping already exists388 cursor.execute(389 "SELECT 1 FROM branch_teacher_courses WHERE branch_id = ? AND teacher_id = ? AND course_code = ?",390 (branch_id, teacher_id, course_code)391 )392 if cursor.fetchone():393 print(f" Mapping already exists for branch '{branch_name}', teacher '{teacher_name}', and course '{course_code}'.")394 return False395 396 # Add mapping397 cursor.execute(398 "INSERT INTO branch_teacher_courses (branch_id, teacher_id, course_code) VALUES (?, ?, ?)",399 (branch_id, teacher_id, course_code)400 )401 conn.commit()402 print(f" Added mapping: Branch '{branch_name}', Teacher '{teacher_name}', Course '{course_code}'")403 return True404 405 except sqlite3.Error as e:406 conn.rollback()407 print(f"Database error: {e}")408 return False409 410 411def add_branch(branch_name):412 """Add a new branch to the database."""413 with sqlite3.connect(DB_PATH) as conn:414 cursor = conn.cursor()415 try:416 # Check if branch already exists and add in one transaction417 cursor.execute("SELECT branch_id FROM branches WHERE branch_name = ?", (branch_name,))418 if cursor.fetchone():419 print(f" Branch '{branch_name}' already exists.")420 return False421 422 cursor.execute("INSERT INTO branches (branch_name) VALUES (?)", (branch_name,))423 conn.commit()424 print(f" Added branch: {branch_name}")425 return True426 427 except sqlite3.Error as e:428 conn.rollback()429 print(f"Database error: {e}")430 return False431 432def delete_course(course_code):433 """Delete a course from the database with all related records."""434 with sqlite3.connect(DB_PATH) as conn:435 cursor = conn.cursor()436 try:437 # Check if course exists438 cursor.execute("SELECT course_name, branch_name FROM courses WHERE course_code = ?", (course_code,))439 course_result = cursor.fetchone()440 if not course_result:441 print(f" Course '{course_code}' does not exist.")442 return False443 444 course_name, branch_name = course_result445 446 # Begin transaction447 conn.execute("BEGIN TRANSACTION")448 449 # Get all teacher mappings for this course450 cursor.execute("""451 SELECT btc.id, t.teacher_name452 FROM branch_teacher_courses btc453 JOIN teachers t ON btc.teacher_id = t.teacher_id454 WHERE btc.course_code = ?455 """, (course_code,))456 457 mappings = cursor.fetchall()458 mapping_count = len(mappings)459 460 # Delete all mappings461 if mapping_count > 0:462 cursor.execute("DELETE FROM branch_teacher_courses WHERE course_code = ?", (course_code,))463 print(f" - Removed {mapping_count} teacher-course mappings:")464 for _, teacher_name in mappings:465 print(f" * {teacher_name}")466 467 # Delete from course_classes468 cursor.execute("DELETE FROM course_classes WHERE course_code = ?", (course_code,))469 470 # Delete from timetable471 cursor.execute("SELECT COUNT(*) FROM timetable WHERE course_code = ?", (course_code,))472 timetable_count = cursor.fetchone()[0]473 if timetable_count > 0:474 cursor.execute("DELETE FROM timetable WHERE course_code = ?", (course_code,))475 print(f" - Removed {timetable_count} timetable entries for this course")476 477 # Finally delete the course478 cursor.execute("DELETE FROM courses WHERE course_code = ?", (course_code,))479 480 conn.commit()481 print(f" Successfully deleted course '{course_code}' ({course_name}) from branch '{branch_name}'")482 return True483 484 except sqlite3.Error as e:485 conn.rollback()486 print(f" Database error: {e}")487 return False488 489def delete_course_teacher_mapping(branch_name, teacher_name, course_code):490 """Delete a specific course-teacher mapping."""491 with sqlite3.connect(DB_PATH) as conn:492 cursor = conn.cursor()493 try:494 # Get all required IDs in one query495 cursor.execute("""496 SELECT b.branch_id, t.teacher_id, btc.id497 FROM branches b498 JOIN teachers t ON t.branch_name = b.branch_name AND t.teacher_name = ?499 JOIN branch_teacher_courses btc ON btc.branch_id = b.branch_id500 AND btc.teacher_id = t.teacher_id AND btc.course_code = ?501 WHERE b.branch_name = ?502 """, (teacher_name, course_code, branch_name))503 504 result = cursor.fetchone()505 if not result:506 # Check which part of the mapping doesn't exist507 cursor.execute("SELECT 1 FROM branches WHERE branch_name = ?", (branch_name,))508 if not cursor.fetchone():509 print(f" Branch '{branch_name}' does not exist.")510 return False511 512 cursor.execute("SELECT 1 FROM teachers WHERE teacher_name = ? AND branch_name = ?",513 (teacher_name, branch_name))514 if not cursor.fetchone():515 print(f" Teacher '{teacher_name}' does not exist in branch '{branch_name}'.")516 return False517 518 cursor.execute("SELECT 1 FROM courses WHERE course_code = ?", (course_code,))519 if not cursor.fetchone():520 print(f" Course '{course_code}' does not exist.")521 return False522 523 print(f" No mapping exists for branch '{branch_name}', teacher '{teacher_name}', and course '{course_code}'.")524 return False525 526 branch_id, teacher_id, mapping_id = result527 528 # Begin transaction529 conn.execute("BEGIN TRANSACTION")530 531 # Check if this mapping is used in timetable532 cursor.execute("""533 SELECT COUNT(*) FROM timetable534 WHERE course_code = ? AND teacher = ? AND branch = ?535 """, (course_code, teacher_name, branch_name))536 537 timetable_count = cursor.fetchone()[0]538 if timetable_count > 0:539 print(f" Warning: This mapping is used in {timetable_count} timetable entries.")540 print(" These entries will remain but may cause issues in future timetable generation.")541 542 # Delete the mapping543 cursor.execute("DELETE FROM branch_teacher_courses WHERE id = ?", (mapping_id,))544 545 conn.commit()546 print(f" Successfully deleted mapping: Branch '{branch_name}', Teacher '{teacher_name}', Course '{course_code}'")547 return True548 549 except sqlite3.Error as e:550 conn.rollback()551 print(f" Database error: {e}")552 return False553 554def initialize_database():555 """Initialize the database with required tables and sample data."""556 with sqlite3.connect(DB_PATH) as conn:557 cursor = conn.cursor()558 559 # Drop existing tables if they exist560 cursor.execute("DROP TABLE IF EXISTS timetable")561 cursor.execute("DROP TABLE IF EXISTS branch_teacher_courses")562 cursor.execute("DROP TABLE IF EXISTS teachers")563 cursor.execute("DROP TABLE IF EXISTS course_classes")564 cursor.execute("DROP TABLE IF EXISTS courses")565 cursor.execute("DROP TABLE IF EXISTS rooms")566 cursor.execute("DROP TABLE IF EXISTS branches")567 568 # Create branches table569 cursor.execute("""570 CREATE TABLE branches (571 branch_id INTEGER PRIMARY KEY,572 branch_name TEXT NOT NULL UNIQUE573 )574 """)575 576 # Create courses table577 cursor.execute("""578 CREATE TABLE courses (579 course_code TEXT PRIMARY KEY,580 course_name TEXT NOT NULL,581 branch_name TEXT NOT NULL582 )583 """)584 585 # Create course_classes table to store the number of classes per course586 cursor.execute("""587 CREATE TABLE course_classes (588 course_code TEXT PRIMARY KEY,589 num_classes INTEGER NOT NULL DEFAULT 2,590 FOREIGN KEY (course_code) REFERENCES courses (course_code)591 )592 """)593 594 # Create teachers table with branch assignment595 cursor.execute("""596 CREATE TABLE teachers (597 teacher_id INTEGER PRIMARY KEY,598 teacher_name TEXT NOT NULL,599 branch_name TEXT NOT NULL,600 FOREIGN KEY (branch_name) REFERENCES branches (branch_name)601 )602 """)603 604 # Create rooms table605 cursor.execute("""606 CREATE TABLE rooms (607 room_id INTEGER PRIMARY KEY,608 room_name TEXT NOT NULL UNIQUE609 )610 """)611 612 # Create branch_teacher_courses table (mapping table)613 cursor.execute("""614 CREATE TABLE branch_teacher_courses (615 id INTEGER PRIMARY KEY,616 branch_id INTEGER,617 teacher_id INTEGER,618 course_code TEXT,619 FOREIGN KEY (branch_id) REFERENCES branches (branch_id),620 FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id),621 FOREIGN KEY (course_code) REFERENCES courses (course_code)622 )623 """)624 625 # Create timetable table626 cursor.execute("""627 CREATE TABLE timetable (628 id INTEGER PRIMARY KEY,629 course_code TEXT,630 course_name TEXT,631 teacher TEXT,632 room TEXT,633 branch TEXT,634 day INTEGER,635 slot INTEGER,636 class_index INTEGER, -- To track multiple classes in the same slot637 FOREIGN KEY (course_code) REFERENCES courses (course_code)638 )639 """)640 641 # Add sample data642 # Add branches643 branches = [644 ("CSE",),645 ("ME",),646 ("ECE",),647 ("CE",),648 ("EEE",)649 ]650 cursor.executemany("INSERT INTO branches (branch_name) VALUES (?)", branches)651 652 # Get branch IDs653 cursor.execute("SELECT branch_id, branch_name FROM branches")654 branch_ids = {name: id for id, name in cursor.fetchall()}655 656 # Add rooms657 rooms = [658 ("Room 101",),659 ("Room 102",),660 ("Room 103",),661 ("Room 104",),662 ("Room 105",),663 ("Room 201",),664 ("Room 202",),665 ("Room 203",),666 ("Room 204",),667 ("Room 205",)668 ]669 cursor.executemany("INSERT INTO rooms (room_name) VALUES (?)", rooms)670 671 # Add courses with branch association672 courses = [673 # CSE courses674 ("CSE101", "Data Structures", "CSE"),675 ("CSE102", "Operating Systems", "CSE"),676 ("CSE103", "Algorithms", "CSE"),677 ("CSE104", "Database Systems", "CSE"),678 ("CSE105", "Computer Networks", "CSE"),679 680 # ME courses681 ("ME101", "Thermodynamics", "ME"),682 ("ME102", "Fluid Mechanics", "ME"),683 ("ME103", "Machine Design", "ME"),684 ("ME104", "Heat Transfer", "ME"),685 ("ME105", "Manufacturing Processes", "ME"),686 687 # ECE courses688 ("ECE101", "Digital Electronics", "ECE"),689 ("ECE102", "Analog Circuits", "ECE"),690 ("ECE103", "Signals and Systems", "ECE"),691 ("ECE104", "Communication Systems", "ECE"),692 ("ECE105", "Microprocessors", "ECE"),693 694 # CE courses695 ("CE101", "Structural Analysis", "CE"),696 ("CE102", "Geotechnical Engineering", "CE"),697 ("CE103", "Transportation Engineering", "CE"),698 ("CE104", "Environmental Engineering", "CE"),699 ("CE105", "Construction Management", "CE"),700 701 # EEE courses702 ("EEE101", "Power Systems", "EEE"),703 ("EEE102", "Control Systems", "EEE"),704 ("EEE103", "Electrical Machines", "EEE"),705 ("EEE104", "Power Electronics", "EEE"),706 ("EEE105", "High Voltage Engineering", "EEE")707 ]708 cursor.executemany("INSERT INTO courses (course_code, course_name, branch_name) VALUES (?, ?, ?)", courses)709 710 # Initialize course_classes table with default values (2 classes per week for each course)711 for course_code, _, _ in courses:712 cursor.execute("INSERT INTO course_classes (course_code, num_classes) VALUES (?, ?)", (course_code, 2))713 714 # Add teachers with branch assignments715 teachers = [716 # CSE teachers717 ("Prof. Smith", "CSE"),718 ("Prof. Johnson", "CSE"),719 ("Prof. Williams", "CSE"),720 ("Prof. Brown", "CSE"),721 722 # ME teachers723 ("Prof. Jones", "ME"),724 ("Prof. Miller", "ME"),725 ("Prof. Davis", "ME"),726 ("Prof. Garcia", "ME"),727 728 # ECE teachers729 ("Prof. Rodriguez", "ECE"),730 ("Prof. Wilson", "ECE"),731 ("Prof. Martinez", "ECE"),732 ("Prof. Anderson", "ECE"),733 734 # CE teachers735 ("Prof. Taylor", "CE"),736 ("Prof. Thomas", "CE"),737 ("Prof. Hernandez", "CE"),738 ("Prof. Moore", "CE"),739 740 # EEE teachers741 ("Prof. Martin", "EEE"),742 ("Prof. Jackson", "EEE"),743 ("Prof. Thompson", "EEE"),744 ("Prof. White", "EEE")745 ]746 cursor.executemany("INSERT INTO teachers (teacher_name, branch_name) VALUES (?, ?)", teachers)747 748 # Get teacher IDs with their branch749 cursor.execute("SELECT teacher_id, teacher_name, branch_name FROM teachers")750 teachers_data = cursor.fetchall()751 752 # Create dictionaries for easy lookup753 teacher_ids = {name: id for id, name, _ in teachers_data}754 teacher_branches = {id: branch for id, _, branch in teachers_data}755 756 # Group teachers by branch757 branch_teachers = {}758 for teacher_id, _, branch in teachers_data:759 if branch not in branch_teachers:760 branch_teachers[branch] = []761 branch_teachers[branch].append(teacher_id)762 763 # Create branch-teacher-course mappings764 # Each teacher can teach multiple courses in their branch765 mappings = []766 767 # For each branch768 for branch_name, branch_id in branch_ids.items():769 # Get courses for this branch770 cursor.execute("SELECT course_code FROM courses WHERE branch_name = ?", (branch_name,))771 branch_courses = [row[0] for row in cursor.fetchall()]772 773 # Get teachers for this branch774 if branch_name not in branch_teachers or not branch_teachers[branch_name]:775 print(f"Warning: No teachers assigned to branch {branch_name}")776 continue777 778 branch_teacher_ids = branch_teachers[branch_name]779 780 # Assign teachers to courses for this branch781 # Each course should have at least 2 teachers who can teach it (or all teachers if fewer than 2)782 for course_code in branch_courses:783 # Select 2-3 random teachers for this course (or all if fewer)784 num_teachers = min(len(branch_teacher_ids), random.randint(2, 3))785 selected_teachers = random.sample(branch_teacher_ids, num_teachers)786 787 for teacher_id in selected_teachers:788 # Verify teacher belongs to this branch789 if teacher_branches[teacher_id] == branch_name:790 mappings.append((branch_id, teacher_id, course_code))791 792 # Insert mappings793 cursor.executemany(794 "INSERT INTO branch_teacher_courses (branch_id, teacher_id, course_code) VALUES (?, ?, ?)",795 mappings796 )797 798 conn.commit()799 print(" Database initialized successfully with sample data!")800 print(f"Added {len(branches)} branches, {len(rooms)} rooms, {len(courses)} courses, {len(teachers)} teachers, and {len(mappings)} branch-teacher-course mappings.")