-- 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');

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

--
-- 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 `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`)) ;

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 `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 `types`
--
ALTER TABLE `types`
  MODIFY `type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

