-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Oct 06, 2023 at 01:03 AM
-- Server version: 5.7.43
-- PHP Version: 8.1.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `demyun_schedule`
--

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

--
-- Stand-in structure for view `all_data_view`
-- (See below for the actual view)
--
CREATE TABLE `all_data_view` (
`date` date
,`start_time` varchar(25)
,`end_time` varchar(25)
,`location` varchar(20)
,`type` varchar(30)
,`course` varchar(25)
);

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

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

CREATE TABLE `class_schedule` (
`class_id` int(11) NOT NULL,
`date` date NOT NULL,
`start_time_id` int(11) NOT NULL,
`end_time_id` int(11) NOT NULL,
`location_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `class_schedule` (`class_id`, `date`, `start_time_id`, `end_time_id`, `location_id`, `type_id`, `course_id`) VALUES
(1, '2023-09-25', 1, 1, 1, 1, 1),
(2, '2023-09-27', 1, 1, 1, 1, 1),
(3, '2023-10-02', 1, 1, 1, 1, 1),
(4, '2023-10-04', 1, 1, 1, 1, 1),
(5, '2023-10-09', 1, 1, 1, 1, 1),
(6, '2023-10-11', 1, 1, 1, 1, 1),
(7, '2023-10-16', 1, 1, 1, 1, 1),
(8, '2023-10-18', 1, 1, 1, 1, 1),
(11, '2023-09-26', 2, 2, 2, 3, 2),
(12, '2023-09-28', 2, 2, 2, 3, 2),
(13, '2023-10-03', 2, 2, 2, 3, 2),
(14, '2023-10-05', 2, 2, 2, 3, 2),
(15, '2023-10-10', 2, 2, 2, 3, 2),
(16, '2023-10-12', 2, 2, 2, 3, 2),
(17, '2023-10-17', 2, 2, 2, 3, 2),
(18, '2023-10-19', 2, 2, 2, 3, 2),
(19, '2023-09-26', 3, 4, 1, 1, 3),
(20, '2023-09-28', 3, 4, 1, 1, 3),
(21, '2023-10-03', 3, 4, 1, 1, 3),
(22, '2023-10-05', 3, 4, 1, 1, 3),
(23, '2023-10-10', 3, 4, 1, 1, 3),
(24, '2023-10-12', 3, 4, 1, 1, 3),
(25, '2023-10-17', 3, 4, 1, 1, 3),
(26, '2023-10-19', 3, 4, 1, 1, 3),
(27, '2023-09-27', 2, 3, 3, 1, 4),
(28, '2023-10-04', 2, 3, 3, 1, 4),
(29, '2023-10-11', 2, 3, 3, 1, 4),
(30, '2023-10-18', 2, 3, 3, 1, 4),
(31, '2023-09-24', 3, 5, 4, 2, 2),
(32, '2023-10-01', 3, 5, 4, 2, 2),
(33, '2023-10-08', 3, 5, 4, 2, 2),
(34, '2023-10-15', 3, 5, 4, 2, 2),
(35, '2023-09-26', 4, 6, 2, 2, 2),
(36, '2023-10-03', 4, 6, 2, 2, 2),
(37, '2023-10-10', 4, 6, 2, 2, 2),
(38, '2023-10-17', 4, 6, 2, 2, 2),
(39, '2023-09-27', 5, 7, 4, 2, 2),
(40, '2023-10-04', 5, 7, 4, 2, 2),
(41, '2023-10-11', 5, 7, 4, 2, 2),
(42, '2023-10-18', 5, 7, 4, 2, 2),
(43, '2023-09-28', 4, 6, 2, 2, 2),
(44, '2023-10-05', 4, 6, 2, 2, 2),
(45, '2023-10-12', 4, 6, 2, 2, 2),
(46, '2023-10-19', 4, 6, 2, 2, 2);

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

--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
`course` varchar(25) NOT NULL,
`course_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` (`course`, `course_id`) VALUES
('ACAD 324', 1),
('ACAD 276', 2),
('ACAD 240', 3),
('CTIN 452', 4);

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

--
-- Table structure for table `end_times`
--

CREATE TABLE `end_times` (
`end_time` varchar(25) NOT NULL,
`end_time_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `end_times`
--

INSERT INTO `end_times` (`end_time`, `end_time_id`) VALUES
('11:50am', 1),
('4:50pm', 2),
('5:50pm', 3),
('7:50pm', 4),
('8:00pm', 5),
('1:50pm', 6),
('3:00pm', 7);

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

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

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

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

INSERT INTO `locations` (`location`, `location_id`) VALUES
('IYH 212', 1),
('IYH 210', 2),
('SCIL 114', 3),
('Zoom', 4);

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

--
-- Table structure for table `start_times`
--

CREATE TABLE `start_times` (
`start_time` varchar(25) NOT NULL,
`start_time_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `start_times`
--

INSERT INTO `start_times` (`start_time`, `start_time_id`) VALUES
('9:00am', 1),
('2:00pm', 2),
('6:00pm', 3),
('12:00pm', 4),
('1:00pm', 5),
('3:30pm', 6);

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

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

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

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

INSERT INTO `types` (`type`, `type_id`) VALUES
('Lecture', 1),
('Lab', 2),
('Lecture and Lab', 3);

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

--
-- Structure for view `all_data_view`
--
DROP TABLE IF EXISTS `all_data_view`;

CREATE ALGORITHM=UNDEFINED DEFINER=`demyun`@`localhost` SQL SECURITY DEFINER VIEW `all_data_view`  AS SELECT `class_schedule`.`date` AS `date`, `start_times`.`start_time` AS `start_time`, `end_times`.`end_time` AS `end_time`, `locations`.`location` AS `location`, `types`.`type` AS `type`, `courses`.`course` AS `course` FROM (((((`class_schedule` join `courses`) join `end_times`) join `locations`) join `start_times`) join `types`) WHERE ((`class_schedule`.`start_time_id` = `start_times`.`start_time_id`) AND (`class_schedule`.`end_time_id` = `end_times`.`end_time_id`) AND (`class_schedule`.`location_id` = `locations`.`location_id`) AND (`class_schedule`.`type_id` = `types`.`type_id`) AND (`class_schedule`.`course_id` = `courses`.`course_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `class_schedule`
--
ALTER TABLE `class_schedule`
ADD PRIMARY KEY (`class_id`),
ADD KEY `course_id` (`course_id`),
ADD KEY `end_time_id` (`end_time_id`),
ADD KEY `start_time_id` (`start_time_id`),
ADD KEY `location_id` (`location_id`),
ADD KEY `type_id` (`type_id`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
ADD PRIMARY KEY (`course_id`);

--
-- Indexes for table `end_times`
--
ALTER TABLE `end_times`
ADD PRIMARY KEY (`end_time_id`);

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

--
-- Indexes for table `start_times`
--
ALTER TABLE `start_times`
ADD PRIMARY KEY (`start_time_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

--
-- AUTO_INCREMENT for table `start_times`
--
ALTER TABLE `start_times`
MODIFY `start_time_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=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `class_schedule`
--
ALTER TABLE `class_schedule`
ADD CONSTRAINT `class_schedule_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`),
ADD CONSTRAINT `class_schedule_ibfk_2` FOREIGN KEY (`end_time_id`) REFERENCES `end_times` (`end_time_id`),
ADD CONSTRAINT `class_schedule_ibfk_3` FOREIGN KEY (`start_time_id`) REFERENCES `start_times` (`start_time_id`),
ADD CONSTRAINT `class_schedule_ibfk_4` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
ADD CONSTRAINT `class_schedule_ibfk_5` FOREIGN KEY (`type_id`) REFERENCES `types` (`type_id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;