-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `twilcher_schedule`
--

-- --------------------------------------------------------

--
-- Table structure for table `classes`
--

CREATE TABLE `classes` (
  `class_id` int(11) NOT NULL,
  `class_name` varchar(255) DEFAULT NULL,
  `class_code` varchar(50) DEFAULT NULL,
  `class_type_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` (`class_id`, `class_name`, `class_code`, `class_type_id`, `location_id`) VALUES
(1, 'Dev 2', 'Acad276', 3, 1),
(2, 'Introduction to 3D Modeling and Animation', 'ITP215', 3, 2),
(3, 'Beginning Pop/Rock Guitar', 'MPGU 120', 1, 3),
(4, 'Blockchain', 'ITP256', 1, 4),
(5, 'The Practice of Design, Ideation to Innovation', 'ACAD324', 1, 5);

-- --------------------------------------------------------

--
-- Stand-in structure for view `class_schedule`
-- (See below for the actual view)
--
CREATE TABLE `class_schedule` (
`meeting_date` date
,`start_time` time
,`end_time` time
,`location_name` varchar(255)
,`class_type_name` varchar(255)
,`class_name` varchar(255)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `class_schedule_view`
-- (See below for the actual view)
--
CREATE TABLE `class_schedule_view` (
`day_of_month` date
,`start_time` time
,`class_location` varchar(255)
,`class_name` varchar(255)
,`class_type` varchar(255)
);

-- --------------------------------------------------------

--
-- Table structure for table `class_type`
--

CREATE TABLE `class_type` (
  `class_type_id` int(11) NOT NULL,
  `class_type_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class_type`
--

INSERT INTO `class_type` (`class_type_id`, `class_type_name`) VALUES
(1, 'Lecture'),
(2, 'Lab'),
(3, 'Lecture/Lab');

-- --------------------------------------------------------

--
-- Table structure for table `location`
--

CREATE TABLE `location` (
  `location_id` int(11) NOT NULL,
  `location_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `location`
--

INSERT INTO `location` (`location_id`, `location_name`) VALUES
(1, 'IYH210'),
(2, 'GFS223'),
(3, 'BMH200'),
(4, 'KAP160'),
(5, 'IYH212');

-- --------------------------------------------------------

--
-- Table structure for table `meetings`
--

CREATE TABLE `meetings` (
  `meeting_id` int(11) NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  `meeting_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `meetings`
--

INSERT INTO `meetings` (`meeting_id`, `class_id`, `meeting_date`, `start_time`, `end_time`) VALUES
(1, 2, '2024-09-09', '12:00:00', '13:50:00'),
(2, 2, '2024-09-11', '12:00:00', '13:50:00'),
(3, 5, '2024-09-10', '09:00:00', '11:50:00'),
(4, 5, '2024-09-12', '09:00:00', '11:50:00'),
(5, 3, '2024-09-09', '14:00:00', '15:50:00'),
(6, 1, '2024-09-10', '14:00:00', '16:50:00'),
(7, 1, '2024-09-12', '14:00:00', '16:50:00'),
(8, 4, '2024-09-09', '17:00:00', '18:50:00'),
(9, 4, '2024-09-11', '17:00:00', '18:50:00'),
(10, 2, '2024-09-16', '12:00:00', '13:50:00'),
(11, 3, '2024-09-16', '14:00:00', '15:50:00'),
(12, 4, '2024-09-16', '17:00:00', '18:50:00'),
(13, 1, '2024-09-17', '14:00:00', '16:50:00'),
(14, 5, '2024-09-17', '09:00:00', '11:50:00'),
(15, 2, '2024-09-18', '12:00:00', '13:50:00'),
(16, 4, '2024-09-18', '17:00:00', '18:50:00'),
(17, 1, '2024-09-19', '14:00:00', '16:50:00'),
(18, 5, '2024-09-19', '09:00:00', '11:50:00'),
(19, 2, '2024-09-23', '12:00:00', '13:50:00'),
(20, 3, '2024-09-23', '14:00:00', '15:50:00'),
(21, 4, '2024-09-23', '17:00:00', '18:50:00'),
(22, 1, '2024-09-24', '14:00:00', '16:50:00'),
(23, 5, '2024-09-24', '09:00:00', '11:50:00'),
(24, 2, '2024-09-25', '12:00:00', '13:50:00'),
(25, 4, '2024-09-25', '17:00:00', '18:50:00'),
(26, 1, '2024-09-26', '14:00:00', '16:50:00'),
(27, 5, '2024-09-26', '09:00:00', '11:50:00'),
(28, 2, '2024-09-30', '12:00:00', '13:50:00'),
(29, 3, '2024-09-30', '14:00:00', '15:50:00'),
(30, 4, '2024-09-30', '17:00:00', '18:50:00'),
(31, 1, '2024-10-01', '14:00:00', '16:50:00'),
(32, 5, '2024-10-01', '09:00:00', '11:50:00'),
(33, 2, '2024-10-02', '12:00:00', '13:50:00'),
(34, 4, '2024-10-02', '17:00:00', '18:50:00'),
(35, 1, '2024-10-03', '14:00:00', '16:50:00'),
(36, 5, '2024-10-03', '09:00:00', '11:50:00'),
(37, 2, '2024-10-07', '12:00:00', '13:50:00'),
(38, 3, '2024-10-07', '14:00:00', '15:50:00'),
(39, 4, '2024-10-07', '17:00:00', '18:50:00'),
(40, 1, '2024-10-08', '14:00:00', '16:50:00'),
(41, 5, '2024-10-08', '09:00:00', '11:50:00'),
(42, 2, '2024-10-09', '12:00:00', '13:50:00'),
(43, 4, '2024-10-09', '17:00:00', '18:50:00'),
(44, 1, '2024-10-10', '14:00:00', '16:50:00'),
(45, 5, '2024-10-10', '09:00:00', '11:50:00');

-- --------------------------------------------------------

--
-- Structure for view `class_schedule`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`twilcher`@`localhost` SQL SECURITY DEFINER VIEW `class_schedule`  AS SELECT `meetings`.`meeting_date` AS `meeting_date`, `meetings`.`start_time` AS `start_time`, `meetings`.`end_time` AS `end_time`, `location`.`location_name` AS `location_name`, `class_type`.`class_type_name` AS `class_type_name`, `classes`.`class_name` AS `class_name` FROM (((`meetings` join `classes` on((`meetings`.`class_id` = `classes`.`class_id`))) join `class_type` on((`classes`.`class_type_id` = `class_type`.`class_type_id`))) join `location` on((`classes`.`location_id` = `location`.`location_id`))) ;

-- --------------------------------------------------------

--
-- Structure for view `class_schedule_view`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`twilcher`@`localhost` SQL SECURITY DEFINER VIEW `class_schedule_view`  AS SELECT `class_schedule`.`meeting_date` AS `day_of_month`, `class_schedule`.`start_time` AS `start_time`, `class_schedule`.`location_name` AS `class_location`, `class_schedule`.`class_name` AS `class_name`, `class_schedule`.`class_type_name` AS `class_type` FROM `class_schedule` ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
  ADD PRIMARY KEY (`class_id`),
  ADD KEY `class_type_id` (`class_type_id`),
  ADD KEY `location_id` (`location_id`);

--
-- Indexes for table `class_type`
--
ALTER TABLE `class_type`
  ADD PRIMARY KEY (`class_type_id`);

--
-- Indexes for table `location`
--
ALTER TABLE `location`
  ADD PRIMARY KEY (`location_id`);

--
-- Indexes for table `meetings`
--
ALTER TABLE `meetings`
  ADD PRIMARY KEY (`meeting_id`),
  ADD KEY `class_id` (`class_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `classes`
--
ALTER TABLE `classes`
  MODIFY `class_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `class_type`
--
ALTER TABLE `class_type`
  MODIFY `class_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `location`
--
ALTER TABLE `location`
  MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `meetings`
--
ALTER TABLE `meetings`
  MODIFY `meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `classes`
--
ALTER TABLE `classes`
  ADD CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`class_type_id`) REFERENCES `class_type` (`class_type_id`),
  ADD CONSTRAINT `classes_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`);

--
-- Constraints for table `meetings`
--
ALTER TABLE `meetings`
  ADD CONSTRAINT `meetings_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`);
COMMIT;

