-- phpMyAdmin SQL Dump -- version 5.2.3 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Sep 24, 2026 at 11:08 AM -- Server version: 5.7.44 -- PHP Version: 8.1.34 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `johnhpar_schedule` -- -- -------------------------------------------------------- -- -- Table structure for table `class_types` -- CREATE TABLE `class_types` ( `type_id` int(11) NOT NULL, `type_name` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `class_types` -- INSERT INTO `class_types` (`type_id`, `type_name`) VALUES (1, 'Lecture'), (2, 'Lecture/Lab'); -- -------------------------------------------------------- -- -- Table structure for table `courses` -- CREATE TABLE `courses` ( `course_id` int(11) NOT NULL, `course_code` varchar(20) NOT NULL, `course_name` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `courses` -- INSERT INTO `courses` (`course_id`, `course_code`, `course_name`) VALUES (1, 'PHIL166', 'Moral and Ethical Issues in Society'), (2, 'ACAD177', 'Digital Toolbox Design'), (3, 'ACAD276', 'Dev 2'), (4, 'MOR452', 'Product Management'), (5, 'BUAD497', 'Applied Business Strategy'); -- -------------------------------------------------------- -- -- Table structure for table `locations` -- CREATE TABLE `locations` ( `location_id` int(11) NOT NULL, `location_name` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `locations` -- INSERT INTO `locations` (`location_id`, `location_name`) VALUES (1, 'IYH112'), (2, 'IYH210'), (3, 'IYH211'), (4, 'JFF239'), (5, 'JFF241'), (6, 'THH201'); -- -------------------------------------------------------- -- -- Table structure for table `schedule` -- CREATE TABLE `schedule` ( `schedule_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `type_id` int(11) NOT NULL, `location_id` int(11) NOT NULL, `meeting_date` date NOT NULL, `start_time` time NOT NULL, `end_time` time NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `schedule` -- INSERT INTO `schedule` (`schedule_id`, `course_id`, `type_id`, `location_id`, `meeting_date`, `start_time`, `end_time`) VALUES (1, 1, 1, 1, '2026-09-14', '14:00:00', '15:30:00'), (2, 5, 2, 6, '2026-09-15', '16:00:00', '17:50:00'), (3, 4, 2, 5, '2026-09-15', '14:00:00', '15:50:00'), (4, 3, 2, 3, '2026-09-15', '12:00:00', '13:50:00'), (5, 2, 1, 2, '2026-09-15', '10:00:00', '11:50:00'), (6, 1, 1, 1, '2026-09-16', '14:00:00', '15:30:00'), (7, 5, 2, 6, '2026-09-17', '16:00:00', '17:50:00'), (8, 4, 2, 5, '2026-09-17', '14:00:00', '15:50:00'), (9, 3, 2, 4, '2026-09-17', '12:00:00', '13:50:00'), (10, 1, 1, 1, '2026-09-21', '14:00:00', '15:30:00'), (11, 2, 1, 2, '2026-09-22', '10:00:00', '11:50:00'), (12, 5, 2, 6, '2026-09-22', '16:00:00', '17:50:00'), (13, 3, 2, 3, '2026-09-22', '12:00:00', '13:50:00'), (14, 4, 2, 5, '2026-09-22', '14:00:00', '15:50:00'), (15, 1, 1, 1, '2026-09-23', '14:00:00', '15:30:00'), (16, 5, 2, 6, '2026-09-24', '16:00:00', '17:50:00'), (17, 3, 2, 4, '2026-09-24', '12:00:00', '13:50:00'), (18, 4, 2, 5, '2026-09-24', '14:00:00', '15:50:00'), (19, 1, 1, 1, '2026-09-28', '14:00:00', '15:30:00'), (20, 2, 1, 2, '2026-09-29', '10:00:00', '11:50:00'), (21, 4, 2, 5, '2026-09-29', '14:00:00', '15:50:00'), (22, 5, 2, 6, '2026-09-29', '16:00:00', '17:50:00'), (23, 3, 2, 3, '2026-09-29', '12:00:00', '13:50:00'), (24, 1, 1, 1, '2026-09-30', '14:00:00', '15:30:00'), (25, 3, 2, 4, '2026-10-01', '12:00:00', '13:50:00'), (26, 4, 2, 5, '2026-10-01', '14:00:00', '15:50:00'), (27, 5, 2, 6, '2026-10-01', '16:00:00', '17:50:00'), (28, 1, 1, 1, '2026-10-05', '14:00:00', '15:30:00'), (29, 5, 2, 6, '2026-10-06', '16:00:00', '17:50:00'), (30, 3, 2, 3, '2026-10-06', '12:00:00', '13:50:00'), (31, 4, 2, 5, '2026-10-06', '14:00:00', '15:50:00'), (32, 2, 1, 2, '2026-10-06', '10:00:00', '11:50:00'), (33, 1, 1, 1, '2026-10-07', '14:00:00', '15:30:00'), (34, 4, 2, 5, '2026-10-08', '14:00:00', '15:50:00'), (35, 3, 2, 4, '2026-10-08', '12:00:00', '13:50:00'), (36, 5, 2, 6, '2026-10-08', '16:00:00', '17:50:00'); -- -------------------------------------------------------- -- -- Stand-in structure for view `schedule_view` -- (See below for the actual view) -- CREATE TABLE `schedule_view` ( `schedule_id` int(11) ,`meeting_date` date ,`start_time` time ,`end_time` time ,`course_code` varchar(20) ,`type_name` varchar(50) ,`course_name` varchar(100) ,`location_name` varchar(100) ); -- -- Indexes for dumped tables -- -- -- Indexes for table `class_types` -- ALTER TABLE `class_types` ADD PRIMARY KEY (`type_id`), ADD UNIQUE KEY `type_name` (`type_name`); -- -- Indexes for table `courses` -- ALTER TABLE `courses` ADD PRIMARY KEY (`course_id`), ADD UNIQUE KEY `course_code` (`course_code`); -- -- Indexes for table `locations` -- ALTER TABLE `locations` ADD PRIMARY KEY (`location_id`), ADD UNIQUE KEY `location_name` (`location_name`); -- -- Indexes for table `schedule` -- ALTER TABLE `schedule` ADD PRIMARY KEY (`schedule_id`), ADD KEY `course_id` (`course_id`), ADD KEY `type_id` (`type_id`), ADD KEY `location_id` (`location_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `class_types` -- ALTER TABLE `class_types` MODIFY `type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `courses` -- ALTER TABLE `courses` MODIFY `course_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6; -- -- AUTO_INCREMENT for table `locations` -- ALTER TABLE `locations` MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `schedule` -- ALTER TABLE `schedule` MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37; -- -------------------------------------------------------- -- -- Structure for view `schedule_view` -- DROP TABLE IF EXISTS `schedule_view`; CREATE ALGORITHM=UNDEFINED DEFINER=`johnhpar`@`localhost` SQL SECURITY DEFINER VIEW `schedule_view` AS SELECT `s`.`schedule_id` AS `schedule_id`, `s`.`meeting_date` AS `meeting_date`, `s`.`start_time` AS `start_time`, `s`.`end_time` AS `end_time`, `c`.`course_code` AS `course_code`, `t`.`type_name` AS `type_name`, `c`.`course_name` AS `course_name`, `l`.`location_name` AS `location_name` FROM (((`schedule` `s` join `courses` `c` on((`s`.`course_id` = `c`.`course_id`))) join `class_types` `t` on((`s`.`type_id` = `t`.`type_id`))) join `locations` `l` on((`s`.`location_id` = `l`.`location_id`))) ORDER BY `s`.`meeting_date` ASC, `s`.`start_time` ASC ; -- -- Constraints for dumped tables -- -- -- Constraints for table `schedule` -- ALTER TABLE `schedule` ADD CONSTRAINT `schedule_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), ADD CONSTRAINT `schedule_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `class_types` (`type_id`), ADD CONSTRAINT `schedule_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`); COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;