-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Oct 06, 2023 at 01:03 AM -- Server version: 5.7.43 -- PHP Version: 8.1.16 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: `demyun_schedule` -- -- -------------------------------------------------------- -- -- Stand-in structure for view `all_data_view` -- (See below for the actual view) -- CREATE TABLE `all_data_view` ( `date` date ,`start_time` varchar(25) ,`end_time` varchar(25) ,`location` varchar(20) ,`type` varchar(30) ,`course` varchar(25) ); -- -------------------------------------------------------- -- -- Table structure for table `class_schedule` -- CREATE TABLE `class_schedule` ( `class_id` int(11) NOT NULL, `date` date NOT NULL, `start_time_id` int(11) NOT NULL, `end_time_id` int(11) NOT NULL, `location_id` int(11) NOT NULL, `type_id` int(11) NOT NULL, `course_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `class_schedule` -- INSERT INTO `class_schedule` (`class_id`, `date`, `start_time_id`, `end_time_id`, `location_id`, `type_id`, `course_id`) VALUES (1, '2023-09-25', 1, 1, 1, 1, 1), (2, '2023-09-27', 1, 1, 1, 1, 1), (3, '2023-10-02', 1, 1, 1, 1, 1), (4, '2023-10-04', 1, 1, 1, 1, 1), (5, '2023-10-09', 1, 1, 1, 1, 1), (6, '2023-10-11', 1, 1, 1, 1, 1), (7, '2023-10-16', 1, 1, 1, 1, 1), (8, '2023-10-18', 1, 1, 1, 1, 1), (11, '2023-09-26', 2, 2, 2, 3, 2), (12, '2023-09-28', 2, 2, 2, 3, 2), (13, '2023-10-03', 2, 2, 2, 3, 2), (14, '2023-10-05', 2, 2, 2, 3, 2), (15, '2023-10-10', 2, 2, 2, 3, 2), (16, '2023-10-12', 2, 2, 2, 3, 2), (17, '2023-10-17', 2, 2, 2, 3, 2), (18, '2023-10-19', 2, 2, 2, 3, 2), (19, '2023-09-26', 3, 4, 1, 1, 3), (20, '2023-09-28', 3, 4, 1, 1, 3), (21, '2023-10-03', 3, 4, 1, 1, 3), (22, '2023-10-05', 3, 4, 1, 1, 3), (23, '2023-10-10', 3, 4, 1, 1, 3), (24, '2023-10-12', 3, 4, 1, 1, 3), (25, '2023-10-17', 3, 4, 1, 1, 3), (26, '2023-10-19', 3, 4, 1, 1, 3), (27, '2023-09-27', 2, 3, 3, 1, 4), (28, '2023-10-04', 2, 3, 3, 1, 4), (29, '2023-10-11', 2, 3, 3, 1, 4), (30, '2023-10-18', 2, 3, 3, 1, 4), (31, '2023-09-24', 3, 5, 4, 2, 2), (32, '2023-10-01', 3, 5, 4, 2, 2), (33, '2023-10-08', 3, 5, 4, 2, 2), (34, '2023-10-15', 3, 5, 4, 2, 2), (35, '2023-09-26', 4, 6, 2, 2, 2), (36, '2023-10-03', 4, 6, 2, 2, 2), (37, '2023-10-10', 4, 6, 2, 2, 2), (38, '2023-10-17', 4, 6, 2, 2, 2), (39, '2023-09-27', 5, 7, 4, 2, 2), (40, '2023-10-04', 5, 7, 4, 2, 2), (41, '2023-10-11', 5, 7, 4, 2, 2), (42, '2023-10-18', 5, 7, 4, 2, 2), (43, '2023-09-28', 4, 6, 2, 2, 2), (44, '2023-10-05', 4, 6, 2, 2, 2), (45, '2023-10-12', 4, 6, 2, 2, 2), (46, '2023-10-19', 4, 6, 2, 2, 2); -- -------------------------------------------------------- -- -- Table structure for table `courses` -- CREATE TABLE `courses` ( `course` varchar(25) NOT NULL, `course_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `courses` -- INSERT INTO `courses` (`course`, `course_id`) VALUES ('ACAD 324', 1), ('ACAD 276', 2), ('ACAD 240', 3), ('CTIN 452', 4); -- -------------------------------------------------------- -- -- Table structure for table `end_times` -- CREATE TABLE `end_times` ( `end_time` varchar(25) NOT NULL, `end_time_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `end_times` -- INSERT INTO `end_times` (`end_time`, `end_time_id`) VALUES ('11:50am', 1), ('4:50pm', 2), ('5:50pm', 3), ('7:50pm', 4), ('8:00pm', 5), ('1:50pm', 6), ('3:00pm', 7); -- -------------------------------------------------------- -- -- Table structure for table `locations` -- CREATE TABLE `locations` ( `location` varchar(20) NOT NULL, `location_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `locations` -- INSERT INTO `locations` (`location`, `location_id`) VALUES ('IYH 212', 1), ('IYH 210', 2), ('SCIL 114', 3), ('Zoom', 4); -- -------------------------------------------------------- -- -- Table structure for table `start_times` -- CREATE TABLE `start_times` ( `start_time` varchar(25) NOT NULL, `start_time_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `start_times` -- INSERT INTO `start_times` (`start_time`, `start_time_id`) VALUES ('9:00am', 1), ('2:00pm', 2), ('6:00pm', 3), ('12:00pm', 4), ('1:00pm', 5), ('3:30pm', 6); -- -------------------------------------------------------- -- -- Table structure for table `types` -- CREATE TABLE `types` ( `type` varchar(30) NOT NULL, `type_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `types` -- INSERT INTO `types` (`type`, `type_id`) VALUES ('Lecture', 1), ('Lab', 2), ('Lecture and Lab', 3); -- -------------------------------------------------------- -- -- Structure for view `all_data_view` -- DROP TABLE IF EXISTS `all_data_view`; CREATE ALGORITHM=UNDEFINED DEFINER=`demyun`@`localhost` SQL SECURITY DEFINER VIEW `all_data_view` AS SELECT `class_schedule`.`date` AS `date`, `start_times`.`start_time` AS `start_time`, `end_times`.`end_time` AS `end_time`, `locations`.`location` AS `location`, `types`.`type` AS `type`, `courses`.`course` AS `course` FROM (((((`class_schedule` join `courses`) join `end_times`) join `locations`) join `start_times`) join `types`) WHERE ((`class_schedule`.`start_time_id` = `start_times`.`start_time_id`) AND (`class_schedule`.`end_time_id` = `end_times`.`end_time_id`) AND (`class_schedule`.`location_id` = `locations`.`location_id`) AND (`class_schedule`.`type_id` = `types`.`type_id`) AND (`class_schedule`.`course_id` = `courses`.`course_id`)) ; -- -- Indexes for dumped tables -- -- -- Indexes for table `class_schedule` -- ALTER TABLE `class_schedule` ADD PRIMARY KEY (`class_id`), ADD KEY `course_id` (`course_id`), ADD KEY `end_time_id` (`end_time_id`), ADD KEY `start_time_id` (`start_time_id`), ADD KEY `location_id` (`location_id`), ADD KEY `type_id` (`type_id`); -- -- Indexes for table `courses` -- ALTER TABLE `courses` ADD PRIMARY KEY (`course_id`); -- -- Indexes for table `end_times` -- ALTER TABLE `end_times` ADD PRIMARY KEY (`end_time_id`); -- -- Indexes for table `locations` -- ALTER TABLE `locations` ADD PRIMARY KEY (`location_id`); -- -- Indexes for table `start_times` -- ALTER TABLE `start_times` ADD PRIMARY KEY (`start_time_id`); -- -- Indexes for table `types` -- ALTER TABLE `types` ADD PRIMARY KEY (`type_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `class_schedule` -- ALTER TABLE `class_schedule` MODIFY `class_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=47; -- -- AUTO_INCREMENT for table `courses` -- ALTER TABLE `courses` MODIFY `course_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT for table `end_times` -- ALTER TABLE `end_times` MODIFY `end_time_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `locations` -- ALTER TABLE `locations` MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT for table `start_times` -- ALTER TABLE `start_times` MODIFY `start_time_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `types` -- ALTER TABLE `types` MODIFY `type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- Constraints for dumped tables -- -- -- Constraints for table `class_schedule` -- ALTER TABLE `class_schedule` ADD CONSTRAINT `class_schedule_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), ADD CONSTRAINT `class_schedule_ibfk_2` FOREIGN KEY (`end_time_id`) REFERENCES `end_times` (`end_time_id`), ADD CONSTRAINT `class_schedule_ibfk_3` FOREIGN KEY (`start_time_id`) REFERENCES `start_times` (`start_time_id`), ADD CONSTRAINT `class_schedule_ibfk_4` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`), ADD CONSTRAINT `class_schedule_ibfk_5` FOREIGN KEY (`type_id`) REFERENCES `types` (`type_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 */;