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

START TRANSACTION;



--
-- Database: `krbarber_schedule`
--

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

--
-- Table structure for table `ClassNames`
--

CREATE TABLE `ClassNames` (
  `ClassNameID` int(11) NOT NULL,
  `ClassName` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ClassNames`
--

INSERT INTO `ClassNames` (`ClassNameID`, `ClassName`) VALUES
(1, 'ACAD 178: Digital Toolbox: Motion Graphics (Lecture)'),
(2, 'ACAD 230: Introduction to Interactive Physical Computing'),
(3, 'ACAD 422: Cybersecurity, Privacy and Trust in Connective Healthcare'),
(4, 'ACAD 276: Dev II'),
(5, 'ACAD 324: The Practice of Design: Ideation to Innovation');

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

--
-- Table structure for table `Locations`
--

CREATE TABLE `Locations` (
  `LocationID` int(11) NOT NULL,
  `LocationName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Locations`
--

INSERT INTO `Locations` (`LocationID`, `LocationName`) VALUES
(1, 'IYH210'),
(2, 'IYH110'),
(3, 'IYH111'),
(4, 'IYH210/211'),
(5, 'IYH212/213');

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

--
-- Table structure for table `MainTable`
--

CREATE TABLE `MainTable` (
  `RecordID` int(11) NOT NULL,
  `Date` date NOT NULL,
  `StartTime` time NOT NULL,
  `EndTime` time NOT NULL,
  `ClassNameID` int(11) NOT NULL,
  `LocationID` int(11) NOT NULL,
  `TypeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `MainTable`
--

INSERT INTO `MainTable` (`RecordID`, `Date`, `StartTime`, `EndTime`, `ClassNameID`, `LocationID`, `TypeID`) VALUES
(1, '2024-08-26', '10:00:00', '11:50:00', 1, 1, 1),
(2, '2024-08-26', '12:00:00', '13:50:00', 2, 2, 1),
(3, '2024-08-26', '14:00:00', '16:50:00', 3, 3, 1),
(4, '2024-08-27', '09:00:00', '11:50:00', 5, 5, 1),
(5, '2024-08-27', '14:00:00', '16:50:00', 4, 4, 3),
(6, '2024-08-28', '12:00:00', '13:50:00', 2, 2, 1),
(7, '2024-08-29', '09:00:00', '11:50:00', 5, 5, 1),
(8, '2024-08-29', '14:00:00', '16:50:00', 4, 4, 3),
(9, '2024-09-03', '09:00:00', '11:50:00', 5, 5, 1),
(10, '2024-09-03', '14:00:00', '16:50:00', 4, 4, 3),
(11, '2024-09-04', '12:00:00', '13:50:00', 2, 2, 1),
(12, '2024-09-05', '09:00:00', '12:00:00', 5, 5, 1),
(13, '2024-09-12', '14:00:00', '16:50:00', 4, 4, 3),
(14, '2024-09-09', '10:00:00', '11:50:00', 1, 1, 1),
(15, '2024-09-09', '12:00:00', '13:50:00', 2, 2, 1),
(16, '2024-09-09', '14:00:00', '16:50:00', 3, 3, 1),
(17, '2024-09-10', '09:00:00', '11:50:00', 5, 5, 1),
(18, '2024-09-10', '14:00:00', '16:50:00', 4, 4, 3),
(19, '2024-09-11', '12:00:00', '13:50:00', 2, 2, 1),
(20, '2024-09-12', '09:00:00', '11:50:00', 5, 5, 1),
(21, '2024-09-12', '14:00:00', '16:50:00', 4, 4, 3),
(22, '2024-09-16', '10:00:00', '11:50:00', 1, 1, 1),
(23, '2024-09-16', '12:00:00', '13:50:00', 2, 2, 1),
(24, '2024-09-16', '14:00:00', '16:50:00', 3, 3, 1),
(25, '2024-09-17', '09:00:00', '11:50:00', 5, 5, 1),
(26, '2024-09-17', '14:00:00', '16:50:00', 4, 4, 3),
(27, '2024-09-18', '12:00:00', '13:50:00', 2, 2, 1),
(28, '2024-09-19', '09:00:00', '11:50:00', 5, 5, 1),
(29, '2024-09-19', '14:00:00', '16:50:00', 4, 4, 3);

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

--
-- Stand-in structure for view `ScheduleView`
-- (See below for the actual view)
--
CREATE TABLE `ScheduleView` (
`ClassName` varchar(100)
,`Date` date
,`StartTime` time
,`EndTime` time
,`LocationName` varchar(50)
,`TypeName` varchar(50)
);

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

--
-- Table structure for table `Types`
--

CREATE TABLE `Types` (
  `TypeID` int(11) NOT NULL,
  `TypeName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Types`
--

INSERT INTO `Types` (`TypeID`, `TypeName`) VALUES
(1, 'Lecture'),
(2, 'Lab'),
(3, 'Lecture/Lab');

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

--
-- Structure for view `ScheduleView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`krbarber`@`localhost` SQL SECURITY DEFINER VIEW `ScheduleView`  AS SELECT `ClassNames`.`ClassName` AS `ClassName`, `MainTable`.`Date` AS `Date`, `MainTable`.`StartTime` AS `StartTime`, `MainTable`.`EndTime` AS `EndTime`, `Locations`.`LocationName` AS `LocationName`, `Types`.`TypeName` AS `TypeName` FROM (((`MainTable` join `Types`) join `Locations`) join `ClassNames`) WHERE ((`MainTable`.`TypeID` = `Types`.`TypeID`) AND (`MainTable`.`LocationID` = `Locations`.`LocationID`) AND (`MainTable`.`ClassNameID` = `ClassNames`.`ClassNameID`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ClassNames`
--
ALTER TABLE `ClassNames`
  ADD PRIMARY KEY (`ClassNameID`);

--
-- Indexes for table `Locations`
--
ALTER TABLE `Locations`
  ADD PRIMARY KEY (`LocationID`);

--
-- Indexes for table `MainTable`
--
ALTER TABLE `MainTable`
  ADD PRIMARY KEY (`RecordID`);

--
-- Indexes for table `Types`
--
ALTER TABLE `Types`
  ADD PRIMARY KEY (`TypeID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ClassNames`
--
ALTER TABLE `ClassNames`
  MODIFY `ClassNameID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `Locations`
--
ALTER TABLE `Locations`
  MODIFY `LocationID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `MainTable`
--
ALTER TABLE `MainTable`
  MODIFY `RecordID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;

--
-- AUTO_INCREMENT for table `Types`
--
ALTER TABLE `Types`
  MODIFY `TypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

