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

START TRANSACTION;



--
-- Database: `chanquin_schedule`
--

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

--
-- Table structure for table `Classes`
--

CREATE TABLE `Classes` (
  `class_id` int(11) NOT NULL,
  `class_name` varchar(100) NOT NULL,
  `location` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Classes`
--

INSERT INTO `Classes` (`class_id`, `class_name`, `location`) VALUES
(1, 'ACAD302:The Hacker Imagination', 'IYH 210'),
(2, 'ACAD376:Industry Practicum I', 'IYH 210'),
(3, 'CSCI100:Explorations in Computing', 'SAL 101'),
(4, 'ACAD276:Dev II', 'IYH 210'),
(5, 'CSCI100:Explorations in Computing', 'MRF 229');

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

--
-- Table structure for table `ClassMeetings`
--

CREATE TABLE `ClassMeetings` (
  `meeting_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `meeting_type_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 `ClassMeetings`
--

INSERT INTO `ClassMeetings` (`meeting_id`, `class_id`, `meeting_type_id`, `meeting_date`, `start_time`, `end_time`) VALUES
(1, 1, 2, '2023-09-11', '11:00:00', '12:50:00'),
(2, 2, 2, '2023-09-11', '15:00:00', '16:50:00'),
(3, 3, 2, '2023-09-12', '11:00:00', '12:20:00'),
(4, 4, 2, '2023-09-12', '14:00:00', '16:50:00'),
(5, 5, 3, '2023-09-12', '13:00:00', '13:50:00'),
(6, 1, 2, '2023-09-18', '11:00:00', '12:50:00'),
(7, 2, 2, '2023-09-18', '15:00:00', '16:50:00'),
(8, 3, 2, '2023-09-19', '11:00:00', '12:20:00'),
(9, 4, 2, '2023-09-19', '14:00:00', '16:50:00'),
(10, 5, 3, '2023-09-19', '13:00:00', '13:50:00'),
(11, 1, 2, '2023-09-25', '11:00:00', '12:50:00'),
(12, 2, 2, '2023-09-25', '15:00:00', '16:50:00'),
(13, 3, 2, '2023-09-26', '11:00:00', '12:20:00'),
(14, 4, 2, '2023-09-26', '14:00:00', '16:50:00'),
(15, 5, 3, '2023-09-26', '13:00:00', '13:50:00'),
(16, 1, 2, '2023-10-02', '11:00:00', '12:50:00'),
(17, 2, 2, '2023-10-02', '15:00:00', '16:50:00'),
(18, 3, 2, '2023-10-03', '11:00:00', '12:20:00'),
(19, 4, 2, '2023-10-03', '14:00:00', '16:50:00'),
(20, 5, 3, '2023-10-03', '13:00:00', '13:50:00');

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

--
-- Stand-in structure for view `FullSchedule`
-- (See below for the actual view)
--
CREATE TABLE `FullSchedule` (
`meeting_date` date
,`start_time` time
,`end_time` time
,`class_name` varchar(100)
,`location` varchar(100)
,`meeting_type_name` varchar(50)
);

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

--
-- Table structure for table `Meetings`
--

CREATE TABLE `Meetings` (
  `meeting_type_id` int(11) NOT NULL,
  `meeting_type_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Meetings`
--

INSERT INTO `Meetings` (`meeting_type_id`, `meeting_type_name`) VALUES
(1, 'Lecture/Lab'),
(2, 'Lecture'),
(3, 'Lab');

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

--
-- Structure for view `FullSchedule`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`chanquin`@`%` SQL SECURITY DEFINER VIEW `FullSchedule`  AS SELECT `ClassMeetings`.`meeting_date` AS `meeting_date`, `ClassMeetings`.`start_time` AS `start_time`, `ClassMeetings`.`end_time` AS `end_time`, `Classes`.`class_name` AS `class_name`, `Classes`.`location` AS `location`, `Meetings`.`meeting_type_name` AS `meeting_type_name` FROM ((`ClassMeetings` join `Classes`) join `Meetings`) WHERE ((`ClassMeetings`.`class_id` = `Classes`.`class_id`) AND (`ClassMeetings`.`meeting_type_id` = `Meetings`.`meeting_type_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Classes`
--
ALTER TABLE `Classes`
  ADD PRIMARY KEY (`class_id`);

--
-- Indexes for table `ClassMeetings`
--
ALTER TABLE `ClassMeetings`
  ADD PRIMARY KEY (`meeting_id`);

--
-- Indexes for table `Meetings`
--
ALTER TABLE `Meetings`
  ADD PRIMARY KEY (`meeting_type_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 `ClassMeetings`
--
ALTER TABLE `ClassMeetings`
  MODIFY `meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- AUTO_INCREMENT for table `Meetings`
--
ALTER TABLE `Meetings`
  MODIFY `meeting_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

