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

START TRANSACTION;



--
-- Database: `hgroome_schedule`
--

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

--
-- Table structure for table `Class_Locations`
--

CREATE TABLE `Class_Locations` (
  `location_ID` int(11) NOT NULL,
  `class_location` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Class_Locations`
--

INSERT INTO `Class_Locations` (`location_ID`, `class_location`) VALUES
(1, 'IYH 212'),
(2, 'DMC 101'),
(3, 'THH 116'),
(4, 'IYH 210'),
(5, 'DMC 111');

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

--
-- Table structure for table `Class_Names`
--

CREATE TABLE `Class_Names` (
  `class_ID` int(11) NOT NULL,
  `class_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Class_Names`
--

INSERT INTO `Class_Names` (`class_ID`, `class_name`) VALUES
(1, 'The Practice of Design: Ideation to Innovation'),
(2, 'The Water Planet'),
(3, 'Accelerated Programming in Python'),
(4, 'Dev II'),
(5, 'Materials: Denim to Nanotubes');

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

--
-- Table structure for table `Class_Types`
--

CREATE TABLE `Class_Types` (
  `type_ID` int(11) NOT NULL,
  `class_type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Class_Types`
--

INSERT INTO `Class_Types` (`type_ID`, `class_type`) VALUES
(1, 'Lecture'),
(2, 'Lab'),
(3, 'Lecture/Lab');

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

--
-- Table structure for table `Schedule`
--

CREATE TABLE `Schedule` (
  `schedule_ID` int(11) NOT NULL,
  `class_ID` int(11) NOT NULL,
  `type_ID` int(11) NOT NULL,
  `location_ID` int(11) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Schedule`
--

INSERT INTO `Schedule` (`schedule_ID`, `class_ID`, `type_ID`, `location_ID`, `start_time`, `end_time`, `date`) VALUES
(1, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-11'),
(2, 2, 2, 5, '12:00:00', '13:50:00', '2023-09-11'),
(3, 3, 3, 3, '15:00:00', '15:50:00', '2023-09-11'),
(4, 2, 1, 2, '11:00:00', '12:20:00', '2023-09-12'),
(5, 4, 1, 4, '14:00:00', '16:50:00', '2023-09-12'),
(6, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-12'),
(7, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-13'),
(8, 3, 3, 3, '15:00:00', '15:50:00', '2023-09-13'),
(9, 2, 1, 2, '11:00:00', '12:20:00', '2023-09-14'),
(10, 4, 1, 4, '14:00:00', '16:50:00', '2023-09-14'),
(11, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-14'),
(12, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-18'),
(13, 2, 2, 5, '12:00:00', '13:50:00', '2023-09-18'),
(14, 3, 3, 3, '15:00:00', '15:50:00', '2023-09-18'),
(15, 2, 1, 2, '11:00:00', '12:20:00', '2023-09-19'),
(16, 4, 1, 4, '14:00:00', '16:50:00', '2023-09-19'),
(17, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-19'),
(18, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-20'),
(19, 3, 3, 3, '15:00:00', '15:50:00', '2023-09-20'),
(20, 2, 1, 2, '11:00:00', '12:20:00', '2023-09-21'),
(21, 4, 1, 4, '14:00:00', '16:50:00', '2023-09-21'),
(22, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-21'),
(23, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-25'),
(24, 2, 2, 5, '12:00:00', '13:50:00', '2023-09-25'),
(25, 3, 3, 3, '15:00:00', '15:50:00', '2023-09-25'),
(26, 2, 1, 2, '11:00:00', '12:20:00', '2023-09-26'),
(27, 4, 1, 4, '14:00:00', '16:50:00', '2023-09-26'),
(28, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-26'),
(29, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-27'),
(30, 3, 3, 3, '15:00:00', '15:50:00', '2023-09-27'),
(31, 2, 1, 2, '11:00:00', '12:20:00', '2023-09-28'),
(32, 4, 1, 4, '14:00:00', '16:50:00', '2023-09-28'),
(33, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-28'),
(34, 1, 1, 1, '09:00:00', '11:50:00', '2023-10-02'),
(35, 2, 2, 5, '12:00:00', '13:50:00', '2023-10-02'),
(36, 3, 3, 3, '15:00:00', '15:50:00', '2023-10-02'),
(37, 2, 1, 2, '11:00:00', '12:20:00', '2023-10-03'),
(38, 4, 1, 4, '14:00:00', '16:50:00', '2023-10-03'),
(39, 5, 1, 1, '18:00:00', '19:50:00', '2023-10-03'),
(40, 1, 1, 1, '09:00:00', '11:50:00', '2023-10-04'),
(41, 3, 3, 3, '15:00:00', '15:50:00', '2023-10-04'),
(42, 2, 1, 2, '11:00:00', '12:20:00', '2023-10-05'),
(43, 4, 1, 4, '14:00:00', '16:50:00', '2023-10-05'),
(44, 5, 1, 1, '18:00:00', '19:50:00', '2023-10-05');

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

--
-- Stand-in structure for view `Schedule_View`
-- (See below for the actual view)
--
CREATE TABLE `Schedule_View` (
`class_name` varchar(50)
,`class_location` varchar(20)
,`class_type` varchar(30)
,`start_time` time
,`end_time` time
,`date` date
);

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

--
-- Structure for view `Schedule_View`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`hgroome`@`localhost` SQL SECURITY DEFINER VIEW `Schedule_View`  AS SELECT `Class_Names`.`class_name` AS `class_name`, `Class_Locations`.`class_location` AS `class_location`, `Class_Types`.`class_type` AS `class_type`, `Schedule`.`start_time` AS `start_time`, `Schedule`.`end_time` AS `end_time`, `Schedule`.`date` AS `date` FROM (((`Class_Types` join `Class_Names`) join `Class_Locations`) join `Schedule`) WHERE ((`Schedule`.`class_ID` = `Class_Names`.`class_ID`) AND (`Schedule`.`type_ID` = `Class_Types`.`type_ID`) AND (`Schedule`.`location_ID` = `Class_Locations`.`location_ID`)) ;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `Class_Types`
--
ALTER TABLE `Class_Types`
  ADD PRIMARY KEY (`type_ID`);

--
-- Indexes for table `Schedule`
--
ALTER TABLE `Schedule`
  ADD PRIMARY KEY (`schedule_ID`),
  ADD KEY `class_ID` (`class_ID`),
  ADD KEY `location_ID` (`location_ID`),
  ADD KEY `type_ID` (`type_ID`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `Class_Types`
--
ALTER TABLE `Class_Types`
  MODIFY `type_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `Schedule`
--
ALTER TABLE `Schedule`
  MODIFY `schedule_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=45;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Schedule`
--
ALTER TABLE `Schedule`
  ADD CONSTRAINT `Schedule_ibfk_1` FOREIGN KEY (`class_ID`) REFERENCES `Class_Names` (`class_ID`),
  ADD CONSTRAINT `Schedule_ibfk_2` FOREIGN KEY (`location_ID`) REFERENCES `Class_Locations` (`location_ID`),
  ADD CONSTRAINT `Schedule_ibfk_3` FOREIGN KEY (`type_ID`) REFERENCES `Class_Types` (`type_ID`);
COMMIT;

