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

START TRANSACTION;



--
-- Database: `relovsky_schedule`
--

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

--
-- Stand-in structure for view `all_sessions`
-- (See below for the actual view)
--
CREATE TABLE `all_sessions` (
`date` date
,`startTime` time
,`endTime` time
,`locationName` varchar(10)
,`typeName` varchar(20)
,`className` varchar(30)
);

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

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

CREATE TABLE `classes` (
  `class_id` int(11) NOT NULL,
  `className` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `classes` (`class_id`, `className`) VALUES
(1, 'ACAD276: Dev 2'),
(2, 'ACAD324: Practice of Design'),
(3, 'ACAD207: Narrative Technology'),
(4, 'NAUT301A: Seamanship and Nav'),
(5, 'ITP180: Video Game Production'),
(13, 'test');

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

--
-- Stand-in structure for view `drill_sessions`
-- (See below for the actual view)
--
CREATE TABLE `drill_sessions` (
`session_id` int(11)
,`date` date
,`startTime` time
,`endTime` time
,`className` varchar(30)
,`typeName` varchar(20)
,`locationName` varchar(10)
);

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

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL,
  `locationName` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`location_id`, `locationName`) VALUES
(1, 'IYH210'),
(2, 'IYH212'),
(3, 'THH118'),
(4, 'EGG108');

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

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `session_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `startTime` time NOT NULL,
  `endTime` time NOT NULL,
  `location_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sessions`
--

INSERT INTO `sessions` (`session_id`, `date`, `startTime`, `endTime`, `location_id`, `type_id`, `class_id`) VALUES
(1, '2023-09-11', '09:00:00', '11:50:00', 2, 1, 2),
(2, '2023-09-13', '09:00:00', '11:50:00', 2, 1, 2),
(3, '2023-09-18', '09:00:00', '11:50:00', 2, 1, 2),
(4, '2023-09-20', '09:00:00', '11:50:00', 2, 1, 2),
(5, '2023-09-25', '09:00:00', '11:50:00', 2, 1, 2),
(6, '2023-09-27', '09:00:00', '11:50:00', 2, 1, 2),
(7, '2023-10-02', '09:00:00', '11:50:00', 2, 1, 2),
(8, '2023-10-04', '09:00:00', '11:50:00', 2, 1, 2),
(9, '2023-09-11', '06:00:00', '08:20:00', 2, 1, 3),
(10, '2023-09-13', '06:00:00', '08:20:00', 2, 1, 3),
(11, '2023-09-18', '06:00:00', '08:20:00', 2, 1, 3),
(12, '2023-09-20', '06:00:00', '08:20:00', 2, 1, 3),
(13, '2023-09-25', '06:00:00', '08:20:00', 2, 1, 3),
(14, '2023-09-27', '06:00:00', '08:20:00', 2, 1, 3),
(15, '2023-10-02', '06:00:00', '08:20:00', 2, 1, 3),
(16, '2023-10-04', '06:00:00', '08:20:00', 2, 1, 3),
(17, '2023-09-13', '13:00:00', '14:50:00', 4, 1, 5),
(18, '2023-09-20', '13:00:00', '14:50:00', 4, 1, 5),
(19, '2023-09-27', '13:00:00', '14:50:00', 4, 1, 5),
(20, '2023-10-04', '13:00:00', '14:50:00', 4, 1, 5),
(21, '2023-09-12', '18:00:00', '20:50:00', 3, 1, 4),
(22, '2023-09-19', '18:00:00', '20:50:00', 3, 1, 4),
(23, '2023-09-26', '18:00:00', '20:50:00', 3, 1, 4),
(24, '2023-10-03', '18:00:00', '20:50:00', 3, 1, 4),
(25, '2023-09-12', '14:00:00', '16:50:00', 1, 1, 1),
(26, '2023-09-14', '14:00:00', '16:50:00', 1, 1, 1),
(27, '2023-09-19', '14:00:00', '16:50:00', 1, 1, 1),
(28, '2023-09-21', '14:00:00', '16:50:00', 1, 1, 1),
(29, '2023-09-26', '14:00:00', '16:50:00', 1, 1, 1),
(30, '2023-09-28', '14:00:00', '16:50:00', 1, 1, 1),
(31, '2023-10-03', '14:00:00', '16:50:00', 1, 1, 1),
(32, '2023-10-05', '14:00:00', '16:50:00', 1, 1, 1),
(52, '2023-09-23', '00:00:00', '01:00:00', 1, 1, 13);

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

--
-- Table structure for table `types`
--

CREATE TABLE `types` (
  `type_id` int(11) NOT NULL,
  `typeName` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `types`
--

INSERT INTO `types` (`type_id`, `typeName`) VALUES
(1, 'Lecture');

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

--
-- Structure for view `all_sessions`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`relovsky`@`localhost` SQL SECURITY DEFINER VIEW `all_sessions`  AS SELECT `sessions`.`date` AS `date`, `sessions`.`startTime` AS `startTime`, `sessions`.`endTime` AS `endTime`, `locations`.`locationName` AS `locationName`, `types`.`typeName` AS `typeName`, `classes`.`className` AS `className` FROM (((`sessions` join `types`) join `locations`) join `classes`) WHERE ((`sessions`.`location_id` = `locations`.`location_id`) AND (`sessions`.`type_id` = `types`.`type_id`) AND (`sessions`.`class_id` = `classes`.`class_id`)) ;

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

--
-- Structure for view `drill_sessions`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`relovsky`@`localhost` SQL SECURITY DEFINER VIEW `drill_sessions`  AS SELECT `sessions`.`session_id` AS `session_id`, `sessions`.`date` AS `date`, `sessions`.`startTime` AS `startTime`, `sessions`.`endTime` AS `endTime`, `classes`.`className` AS `className`, `types`.`typeName` AS `typeName`, `locations`.`locationName` AS `locationName` FROM (((`sessions` join `types`) join `locations`) join `classes`) WHERE ((`sessions`.`location_id` = `locations`.`location_id`) AND (`sessions`.`type_id` = `types`.`type_id`) AND (`sessions`.`class_id` = `classes`.`class_id`)) ;

--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`session_id`),
  ADD KEY `class` (`class_id`),
  ADD KEY `location` (`location_id`),
  ADD KEY `type` (`type_id`);

--
-- Indexes for table `types`
--
ALTER TABLE `types`
  ADD PRIMARY KEY (`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=14;

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

--
-- AUTO_INCREMENT for table `sessions`
--
ALTER TABLE `sessions`
  MODIFY `session_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=53;

--
-- AUTO_INCREMENT for table `types`
--
ALTER TABLE `types`
  MODIFY `type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `sessions`
--
ALTER TABLE `sessions`
  ADD CONSTRAINT `class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`),
  ADD CONSTRAINT `location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  ADD CONSTRAINT `type` FOREIGN KEY (`type_id`) REFERENCES `types` (`type_id`);
COMMIT;

