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

START TRANSACTION;



--
-- Database: `nnhoang_schedule`
--

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

--
-- Table structure for table `class_end`
--

CREATE TABLE `class_end` (
  `end_id` int(11) NOT NULL,
  `end` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class_end`
--

INSERT INTO `class_end` (`end_id`, `end`) VALUES
(1, '11:20AM'),
(2, '11:50AM'),
(3, '1:20PM'),
(4, '1:50PM'),
(5, '4:50PM'),
(6, '5:50PM');

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

--
-- Table structure for table `class_locations`
--

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

--
-- Dumping data for table `class_locations`
--

INSERT INTO `class_locations` (`location_id`, `location`) VALUES
(1, 'SGM101'),
(2, 'THH301'),
(3, 'SSL202'),
(4, 'IYH211'),
(5, 'IYH210/211'),
(6, 'WPH205');

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

--
-- Table structure for table `class_names`
--

CREATE TABLE `class_names` (
  `name_id` int(11) NOT NULL,
  `name` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class_names`
--

INSERT INTO `class_names` (`name_id`, `name`) VALUES
(1, 'Law 200: Law & Society'),
(2, 'Psyc 301: Cognitive Processes'),
(3, 'Psyc 305: Learning & Memory'),
(4, 'Acad 178: Motion Graphics'),
(5, 'Acad 276: Dev II'),
(6, 'dev'),
(7, 'test');

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

--
-- Table structure for table `class_schedule`
--

CREATE TABLE `class_schedule` (
  `class_id` int(11) NOT NULL,
  `class_date` varchar(11) NOT NULL,
  `start_id` int(11) NOT NULL,
  `end_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `name_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class_schedule`
--

INSERT INTO `class_schedule` (`class_id`, `class_date`, `start_id`, `end_id`, `location_id`, `type_id`, `name_id`) VALUES
(1, '9/11/2023', 1, 1, 1, 1, 1),
(2, '9/11/2023', 5, 6, 2, 3, 2),
(3, '9/12/2023', 1, 2, 3, 1, 3),
(4, '9/12/2023', 2, 3, 4, 3, 4),
(5, '9/12/2023', 4, 5, 5, 3, 5),
(6, '9/13/2023', 1, 1, 1, 1, 1),
(7, '9/13/2023', 3, 4, 6, 4, 1),
(8, '9/13/2023', 5, 6, 2, 3, 2),
(9, '9/14/2023', 1, 2, 3, 1, 3),
(10, '9/14/2023', 2, 3, 4, 3, 4),
(11, '9/14/2023', 4, 5, 5, 3, 5),
(12, '9/18/2023', 1, 1, 1, 1, 1),
(13, '9/18/2023', 5, 6, 2, 3, 2),
(14, '9/19/2023', 1, 2, 3, 1, 3),
(15, '9/19/2023', 2, 3, 4, 3, 4),
(16, '9/19/2023', 4, 5, 5, 3, 5),
(17, '9/20/2023', 1, 1, 1, 1, 1),
(18, '9/20/2023', 3, 4, 6, 4, 1),
(19, '9/20/2023', 5, 6, 2, 3, 2),
(20, '9/21/2023', 1, 2, 3, 1, 3),
(21, '9/21/2023', 2, 3, 4, 3, 4),
(22, '9/21/2023', 4, 5, 5, 3, 5),
(23, '9/25/2023', 1, 1, 1, 1, 1),
(24, '9/25/2023', 5, 6, 2, 3, 2),
(25, '9/26/2023', 1, 2, 3, 1, 3),
(26, '9/26/2023', 2, 3, 4, 3, 4),
(27, '9/26/2023', 4, 5, 5, 3, 5),
(28, '9/27/2023', 1, 1, 1, 1, 1),
(29, '9/27/2023', 3, 4, 6, 4, 1),
(30, '9/27/2023', 5, 6, 2, 3, 2),
(31, '9/28/2023', 1, 2, 3, 1, 3),
(32, '9/28/2023', 2, 3, 4, 3, 4),
(33, '9/28/2023', 4, 5, 5, 3, 5),
(34, '10/2/2023', 1, 1, 1, 1, 1),
(35, '10/2/2023', 5, 6, 2, 3, 2),
(36, '10/3/2023', 1, 2, 3, 1, 3),
(37, '10/3/2023', 2, 3, 4, 3, 4),
(38, '10/3/2023', 4, 5, 5, 3, 5),
(39, '10/4/2023', 1, 1, 1, 1, 1),
(40, '10/4/2023', 3, 4, 6, 4, 1),
(41, '10/4/2023', 5, 6, 2, 3, 2),
(42, '10/5/2023', 1, 2, 3, 1, 3),
(43, '10/5/2023', 2, 3, 4, 3, 4),
(44, '10/5/2023', 4, 5, 5, 3, 5),
(45, '2023-09-18', 5, 6, 2, 3, 2),
(46, '2023-09-18', 5, 6, 2, 3, 2),
(47, '2023-09-18', 5, 6, 2, 3, 2),
(48, '2023-09-18', 1, 1, 1, 1, 1),
(49, '2023-09-18', 1, 1, 1, 1, 1),
(50, '2023-09-23', 1, 1, 4, 1, 7);

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

--
-- Table structure for table `class_start`
--

CREATE TABLE `class_start` (
  `start_id` int(11) NOT NULL,
  `start` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class_start`
--

INSERT INTO `class_start` (`start_id`, `start`) VALUES
(1, '10:00AM'),
(2, '12:00PM'),
(3, '1:00PM'),
(4, '2:00AM'),
(5, '4:00PM');

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

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

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

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

INSERT INTO `class_type` (`type_id`, `type`) VALUES
(1, 'lecture'),
(2, 'lab'),
(3, 'lecture/lab'),
(4, 'discussion');

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

--
-- Stand-in structure for view `fixedView1`
-- (See below for the actual view)
--
CREATE TABLE `fixedView1` (
`end` varchar(10)
,`location` varchar(20)
,`name` varchar(40)
,`start` varchar(10)
,`type` varchar(20)
,`class_date` varchar(11)
);

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

--
-- Stand-in structure for view `view1`
-- (See below for the actual view)
--
CREATE TABLE `view1` (
`end` varchar(10)
,`location` varchar(20)
,`name` varchar(40)
,`start` varchar(10)
,`type` varchar(20)
);

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

--
-- Stand-in structure for view `view2`
-- (See below for the actual view)
--
CREATE TABLE `view2` (
`end` varchar(10)
,`location` varchar(20)
,`name` varchar(40)
,`start` varchar(10)
,`type` varchar(20)
,`class_date` varchar(11)
,`class_ID` int(11)
);

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

--
-- Stand-in structure for view `view3`
-- (See below for the actual view)
--
CREATE TABLE `view3` (
`class_id` int(11)
,`class_date` varchar(11)
,`start_id` int(11)
,`end_id` int(11)
,`location_id` int(11)
,`type_id` int(11)
,`name_id` int(11)
,`end` varchar(10)
,`location` varchar(20)
,`name` varchar(40)
,`start` varchar(10)
,`type` varchar(20)
);

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

--
-- Structure for view `fixedView1`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`nnhoang`@`localhost` SQL SECURITY DEFINER VIEW `fixedView1`  AS SELECT `class_end`.`end` AS `end`, `class_locations`.`location` AS `location`, `class_names`.`name` AS `name`, `class_start`.`start` AS `start`, `class_type`.`type` AS `type`, `class_schedule`.`class_date` AS `class_date` FROM (((((`class_end` join `class_locations`) join `class_names`) join `class_start`) join `class_type`) join `class_schedule`) WHERE ((`class_schedule`.`location_id` = `class_locations`.`location_id`) AND (`class_schedule`.`name_id` = `class_names`.`name_id`) AND (`class_schedule`.`end_id` = `class_end`.`end_id`) AND (`class_schedule`.`type_id` = `class_type`.`type_id`)) ;

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

--
-- Structure for view `view1`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`nnhoang`@`localhost` SQL SECURITY DEFINER VIEW `view1`  AS SELECT `class_end`.`end` AS `end`, `class_locations`.`location` AS `location`, `class_names`.`name` AS `name`, `class_start`.`start` AS `start`, `class_type`.`type` AS `type` FROM (((((`class_end` join `class_locations`) join `class_names`) join `class_start`) join `class_type`) join `class_schedule`) WHERE ((`class_schedule`.`start_id` = `class_start`.`start_id`) AND (`class_schedule`.`location_id` = `class_locations`.`location_id`) AND (`class_schedule`.`name_id` = `class_names`.`name_id`) AND (`class_schedule`.`end_id` = `class_end`.`end_id`) AND (`class_schedule`.`type_id` = `class_type`.`type_id`)) ;

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

--
-- Structure for view `view2`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`nnhoang`@`localhost` SQL SECURITY DEFINER VIEW `view2`  AS SELECT `class_end`.`end` AS `end`, `class_locations`.`location` AS `location`, `class_names`.`name` AS `name`, `class_start`.`start` AS `start`, `class_type`.`type` AS `type`, `class_schedule`.`class_date` AS `class_date`, `class_schedule`.`class_id` AS `class_ID` FROM (((((`class_end` join `class_locations`) join `class_names`) join `class_start`) join `class_type`) join `class_schedule`) WHERE ((`class_schedule`.`start_id` = `class_start`.`start_id`) AND (`class_schedule`.`location_id` = `class_locations`.`location_id`) AND (`class_schedule`.`name_id` = `class_names`.`name_id`) AND (`class_schedule`.`end_id` = `class_end`.`end_id`) AND (`class_schedule`.`type_id` = `class_type`.`type_id`)) ;

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

--
-- Structure for view `view3`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view3`  AS SELECT `class_schedule`.`class_id` AS `class_id`, `class_schedule`.`class_date` AS `class_date`, `class_schedule`.`start_id` AS `start_id`, `class_schedule`.`end_id` AS `end_id`, `class_schedule`.`location_id` AS `location_id`, `class_schedule`.`type_id` AS `type_id`, `class_schedule`.`name_id` AS `name_id`, `class_end`.`end` AS `end`, `class_locations`.`location` AS `location`, `class_names`.`name` AS `name`, `class_start`.`start` AS `start`, `class_type`.`type` AS `type` FROM (((((`class_end` join `class_locations`) join `class_names`) join `class_start`) join `class_type`) join `class_schedule`) WHERE ((`class_schedule`.`start_id` = `class_start`.`start_id`) AND (`class_schedule`.`location_id` = `class_locations`.`location_id`) AND (`class_schedule`.`name_id` = `class_names`.`name_id`) AND (`class_schedule`.`end_id` = `class_end`.`end_id`) AND (`class_schedule`.`type_id` = `class_type`.`type_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `class_end`
--
ALTER TABLE `class_end`
  ADD PRIMARY KEY (`end_id`);

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

--
-- Indexes for table `class_names`
--
ALTER TABLE `class_names`
  ADD PRIMARY KEY (`name_id`);

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

--
-- Indexes for table `class_start`
--
ALTER TABLE `class_start`
  ADD PRIMARY KEY (`start_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `class_end`
--
ALTER TABLE `class_end`
  MODIFY `end_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

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

--
-- AUTO_INCREMENT for table `class_names`
--
ALTER TABLE `class_names`
  MODIFY `name_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

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

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

--
-- AUTO_INCREMENT for table `class_type`
--
ALTER TABLE `class_type`
  MODIFY `type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

