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

START TRANSACTION;



--
-- Database: `easontan_tang_schedule`
--

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

--
-- Stand-in structure for view `classView`
-- (See below for the actual view)
--
CREATE TABLE `classView` (
`class_name` varchar(200)
,`start_date` time
,`class_type` varchar(100)
,`class_location` varchar(100)
,`date` date
);

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

--
-- Table structure for table `end_dates`
--

CREATE TABLE `end_dates` (
  `end_dates_id` int(11) NOT NULL,
  `end_time_copy1` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `end_dates`
--

INSERT INTO `end_dates` (`end_dates_id`, `end_time_copy1`) VALUES
(1, '13:50:00'),
(2, '15:50:00'),
(3, '18:50:00'),
(4, '11:50:00'),
(5, '16:50:00'),
(6, '18:20:00');

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

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

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

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

INSERT INTO `locations` (`location_id`, `location_name`) VALUES
(1, 'HOH1'),
(2, 'RRI321'),
(3, 'IYH212/213'),
(4, 'IYH210/211'),
(5, 'ZHS252');

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

--
-- Table structure for table `main_schedule`
--

CREATE TABLE `main_schedule` (
  `mainschedule_id` int(11) NOT NULL,
  `mainschedule_date` date NOT NULL,
  `mainschedule_start_time` int(11) NOT NULL,
  `mainschedule_end_time` int(11) NOT NULL,
  `mainschedule_location` int(11) NOT NULL,
  `mainschedule_type` int(11) NOT NULL,
  `mainschedule_name` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `main_schedule`
--

INSERT INTO `main_schedule` (`mainschedule_id`, `mainschedule_date`, `mainschedule_start_time`, `mainschedule_end_time`, `mainschedule_location`, `mainschedule_type`, `mainschedule_name`) VALUES
(1, '2024-09-16', 1, 1, 1, 1, 1),
(2, '2024-09-16', 2, 2, 2, 2, 2),
(3, '2024-09-16', 3, 3, 3, 1, 3),
(4, '2024-09-17', 4, 4, 3, 1, 4),
(5, '2024-09-17', 5, 5, 5, 1, 5),
(6, '2024-09-17', 6, 6, 5, 1, 2),
(7, '2024-09-18', 1, 1, 1, 1, 1),
(8, '2024-09-19', 4, 4, 3, 1, 4),
(9, '2024-09-19', 5, 5, 5, 1, 5),
(10, '2024-09-19', 6, 6, 5, 1, 2),
(11, '2024-09-23', 1, 1, 1, 1, 1),
(12, '2024-09-23', 2, 2, 2, 2, 2),
(13, '2024-09-23', 3, 3, 3, 1, 3),
(14, '2024-09-24', 4, 4, 3, 1, 4),
(15, '2024-09-24', 5, 5, 5, 1, 5),
(16, '2024-09-24', 6, 6, 5, 1, 2),
(17, '2024-09-25', 1, 1, 1, 1, 1),
(18, '2024-09-26', 4, 4, 3, 1, 4),
(19, '2024-09-26', 5, 5, 5, 1, 5),
(20, '2024-09-26', 6, 6, 5, 1, 2),
(21, '2024-09-30', 1, 1, 1, 1, 1),
(22, '2024-09-30', 2, 2, 2, 2, 2),
(23, '2024-09-30', 3, 3, 3, 1, 3),
(24, '2024-10-01', 4, 4, 3, 1, 4),
(25, '2024-10-01', 5, 5, 5, 1, 5),
(26, '2024-10-01', 6, 6, 5, 1, 2),
(27, '2024-10-02', 1, 1, 1, 1, 1),
(30, '2024-10-02', 6, 6, 5, 1, 2),
(28, '2024-10-03', 4, 4, 3, 1, 4),
(29, '2024-10-03', 5, 5, 5, 1, 5);

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

--
-- Table structure for table `names`
--

CREATE TABLE `names` (
  `name_id` int(11) NOT NULL,
  `name_name` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `names`
--

INSERT INTO `names` (`name_id`, `name_name`) VALUES
(1, 'BUAD 308: Advanced Business Finance'),
(2, 'QBIO 305: Statistics for Biological Sciences '),
(3, 'IDSN 521: Designing Brand Identity for Startups'),
(4, 'ACAD 324: The Practice of Design: Ideation to Innovation'),
(5, 'ACAD 276: Dev II ');

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

--
-- Table structure for table `start_dates`
--

CREATE TABLE `start_dates` (
  `date_id` int(11) NOT NULL,
  `start_time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `start_dates`
--

INSERT INTO `start_dates` (`date_id`, `start_time`) VALUES
(1, '12:00:00'),
(2, '15:00:00'),
(3, '17:00:00'),
(4, '09:00:00'),
(5, '14:00:00'),
(6, '17:00:00');

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

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

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

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

INSERT INTO `types` (`type_id`, `type_name`) VALUES
(1, 'lecture'),
(2, 'discussion');

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

--
-- Structure for view `classView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`easontan`@`localhost` SQL SECURITY DEFINER VIEW `classView`  AS SELECT `names`.`name_name` AS `class_name`, `start_dates`.`start_time` AS `start_date`, `types`.`type_name` AS `class_type`, `locations`.`location_name` AS `class_location`, `main_schedule`.`mainschedule_date` AS `date` FROM ((((`main_schedule` join `start_dates` on((`main_schedule`.`mainschedule_start_time` = `start_dates`.`date_id`))) join `locations` on((`main_schedule`.`mainschedule_location` = `locations`.`location_id`))) join `names` on((`main_schedule`.`mainschedule_name` = `names`.`name_id`))) join `types` on((`main_schedule`.`mainschedule_type` = `types`.`type_id`))) LIMIT 0, 25 ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `end_dates`
--
ALTER TABLE `end_dates`
  ADD PRIMARY KEY (`end_dates_id`);

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

--
-- Indexes for table `main_schedule`
--
ALTER TABLE `main_schedule`
  ADD PRIMARY KEY (`mainschedule_id`),
  ADD KEY `mainschedule_date` (`mainschedule_date`,`mainschedule_start_time`,`mainschedule_end_time`,`mainschedule_location`,`mainschedule_type`,`mainschedule_name`),
  ADD KEY `mainschedule_type` (`mainschedule_type`),
  ADD KEY `main_schedule_ibfk_1` (`mainschedule_name`),
  ADD KEY `mainschedule_location` (`mainschedule_location`),
  ADD KEY `mainschedule_start_time` (`mainschedule_start_time`),
  ADD KEY `mainschedule_end_time` (`mainschedule_end_time`);

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

--
-- Indexes for table `start_dates`
--
ALTER TABLE `start_dates`
  ADD PRIMARY KEY (`date_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `main_schedule`
--
ALTER TABLE `main_schedule`
  MODIFY `mainschedule_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `main_schedule`
--
ALTER TABLE `main_schedule`
  ADD CONSTRAINT `main_schedule_ibfk_1` FOREIGN KEY (`mainschedule_name`) REFERENCES `names` (`name_id`),
  ADD CONSTRAINT `main_schedule_ibfk_2` FOREIGN KEY (`mainschedule_type`) REFERENCES `types` (`type_id`),
  ADD CONSTRAINT `main_schedule_ibfk_3` FOREIGN KEY (`mainschedule_location`) REFERENCES `locations` (`location_id`),
  ADD CONSTRAINT `main_schedule_ibfk_5` FOREIGN KEY (`mainschedule_start_time`) REFERENCES `start_dates` (`date_id`),
  ADD CONSTRAINT `main_schedule_ibfk_6` FOREIGN KEY (`mainschedule_end_time`) REFERENCES `start_dates` (`date_id`);
COMMIT;

