Database Management for Academic Records
Uploaded by
hagertamer1620Database Management for Academic Records
Uploaded by
hagertamer1620delete from prereq;
delete from time_slot;
delete from advisor;
delete from takes;
delete from student;
delete from teaches;
delete from section;
delete from instructor;
delete from course;
delete from department;
delete from classroom;
insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Salem', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Waled', 'Finance', '90000');
insert into instructor values ('15151', 'Motaz', 'Music', '40000');
insert into instructor values ('22222', 'Einas', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Glal', 'Physics', '87000');
insert into instructor values ('45565', 'Karem', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Camel', 'History', '62000');
insert into instructor values ('76543', 'Sameh', 'Finance', '80000');
insert into instructor values ('76766', 'Ramy', 'Biology', '72000');
insert into instructor values ('83821', 'Basm', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kmal', 'Elec. Eng.', '80000');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zaki', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shady', 'Comp. Sci.', '32');
insert into student values ('19991', 'Berlant', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peter', 'Physics', '56');
insert into student values ('45678', 'Lbeb', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sameh', 'Music', '38');
insert into student values ('70557', 'Slamh', 'Physics', '0');
insert into student values ('76543', 'Badee', 'Comp. Sci.', '58');
insert into student values ('76653', 'Ali', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bumy', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tantawy', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45 ');
insert into time_slot values ('E', 'R', '10', '30', '11', '45 ');
insert into time_slot values ('F', 'T', '14', '30', '15', '45 ');
insert into time_slot values ('F', 'R', '14', '30', '15', '45 ');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');
Common questions
Powered by AIClassroom allocations, such as 'Packard' being used for CS-101 and other courses, alongside multiple sessions at the same time and limited availability, create potential scheduling conflicts. If overlapping course times are scheduled in the same room, prioritization and rescheduling must occur, highlighting the challenge of managing limited resources efficiently .
Each department has a budget which seems to relate to the salaries of its instructors. For instance, the 'Comp. Sci.' department, which has a substantial budget of 100,000, employs instructors like 'Salem' who earn 65,000. Departments must manage instructor salaries within their budget, affecting hiring and departmental growth .
Advisors are assigned to students, such as '00128' being advised by '45565', to provide guidance tailored to individual academic needs. Potential issues may arise if advisors are overloaded with advisees, leading to insufficient guidance, or if their expertise does not align with the student's field of study, potentially leading to misdirection .
The prerequisite for the course "CS-315" is "CS-101". This requirement is critical as it ensures that students have a foundational understanding of computer science, which is necessary for successfully grasping the advanced concepts in robotics taught in "CS-315" .
Classroom capacities, ranging from 'Packard' (500) to 'Painter' (10), influence course offerings by dictating how many students can be accommodated per session. This can impact enrolment positively or negatively, potentially causing class size constraints or excess demand leading to student dissatisfaction. Effective use of space is crucial to balance offerings with demand .
Classrooms are allocated with varying capacities, such as Max Capacity (500) for 'Packard' and smaller sizes like 'Painter' (10). Departments with large enrollments, like 'Comp. Sci.' and 'Biology' which offer popular courses (e.g., CS-101, BIO-101), likely make use of larger or multiple sessions to accommodate students, while smaller courses can use smaller classrooms like 'Painter'. Strategic scheduling is necessary to make efficient use of space .
Time slots are distributed throughout the week at various times, such as 'A' (MWF 8:00-8:50) and 'F' (TR 14:30-15:45). This variety allows students to choose courses that fit their personal schedules, boosting enrollment flexibility. However, it may also require students to manage variable daily schedules, affecting consistent attendance and academic performance .
Managing prerequisites, such as 'CS-101' for several courses including 'CS-319', challenges curricular planning by requiring students to complete foundational courses before advancing. This structured pathway ensures preparedness but may delay progress if prerequisite courses are over-enrolled or not offered regularly. Students must plan ahead to align their schedules with prerequisite offerings to progress efficiently .
Instructor allocations, such as 'Salem' teaching multiple 'Comp. Sci.' courses, highlight departmental focus, reflecting prioritization of core courses like 'CS-101'. This strategic allocation ensures that experienced instructors lead key courses, maintaining educational quality and departmental prestige, thereby attracting students and funding .
Departmental budgets directly impact faculty salaries, as evident with 'Comp. Sci.' having a high budget (100,000) and relatively high faculty salaries like 'Salem' at 65,000. Departments with restricted budgets have limited room for salary negotiations and hiring, potentially affecting faculty quality and availability, impacting course delivery and departmental success .