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

START TRANSACTION;



--
-- Database: `malingue_schedule`
--

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

--
-- Stand-in structure for view `classdata`
-- (See below for the actual view)
--
CREATE TABLE `classdata` (
`end` time
,`start` time
,`date` date
,`classnames` varchar(30)
,`classroom` varchar(23)
,`type` varchar(25)
);

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

--
-- Table structure for table `classname`
--

CREATE TABLE `classname` (
  `classname_id` int(11) NOT NULL,
  `classnames` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classname`
--

INSERT INTO `classname` (`classname_id`, `classnames`) VALUES
(1, 'ACAD 324: PoD'),
(2, 'ACAD 207: Narratives in Tech'),
(3, 'ACAD 276: Dev 2'),
(4, 'ACAD 240: Materials'),
(5, 'DANC 180: African');

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

--
-- Table structure for table `classroom`
--

CREATE TABLE `classroom` (
  `classroomid` int(11) NOT NULL,
  `classroom` varchar(23) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classroom`
--

INSERT INTO `classroom` (`classroomid`, `classroom`) VALUES
(1, 'IYH212/213'),
(2, 'IYH212'),
(3, 'IYH210/211'),
(4, 'KDC107');

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

--
-- Table structure for table `SSchedule`
--

CREATE TABLE `SSchedule` (
  `schedule_id` int(11) NOT NULL,
  `classname` int(11) NOT NULL,
  `date` date NOT NULL,
  `start` time NOT NULL,
  `end` time NOT NULL,
  `classroom` int(11) NOT NULL,
  `type` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `SSchedule`
--

INSERT INTO `SSchedule` (`schedule_id`, `classname`, `date`, `start`, `end`, `classroom`, `type`) VALUES
(1, 1, '2023-09-04', '09:00:00', '11:00:00', 1, 1),
(2, 2, '2023-09-04', '18:00:00', '20:30:00', 1, 1),
(3, 4, '2023-09-05', '18:00:00', '20:30:00', 1, 2),
(4, 3, '2023-09-05', '14:00:00', '17:00:00', 3, 2),
(5, 5, '2023-09-06', '14:00:00', '16:00:00', 4, 2),
(6, 1, '2023-09-06', '09:00:00', '11:00:00', 1, 1),
(7, 2, '2023-09-06', '18:00:00', '20:30:00', 1, 1),
(8, 4, '2023-09-07', '18:00:00', '20:30:00', 1, 2),
(9, 3, '2023-09-07', '14:00:00', '17:00:00', 3, 2),
(10, 5, '2023-09-08', '14:00:00', '16:00:00', 4, 2),
(11, 1, '2023-09-11', '09:00:00', '11:00:00', 1, 1),
(12, 2, '2023-09-11', '18:00:00', '20:30:00', 1, 1),
(13, 4, '2023-09-12', '18:00:00', '20:30:00', 1, 2),
(14, 3, '2023-09-12', '14:00:00', '17:00:00', 3, 2),
(15, 5, '2023-09-15', '14:00:00', '16:00:00', 4, 2),
(16, 1, '2023-09-13', '09:00:00', '11:00:00', 1, 1),
(17, 2, '2023-09-13', '18:00:00', '20:30:00', 1, 1),
(18, 4, '2023-09-14', '18:00:00', '20:30:00', 1, 2),
(19, 3, '2023-09-14', '14:00:00', '17:00:00', 3, 2),
(20, 5, '2023-09-13', '14:00:00', '16:00:00', 4, 2),
(21, 1, '2023-09-18', '09:00:00', '11:00:00', 1, 1),
(22, 2, '2023-09-18', '18:00:00', '20:30:00', 1, 1),
(23, 4, '2023-09-19', '18:00:00', '20:30:00', 1, 2),
(24, 3, '2023-09-19', '14:00:00', '17:00:00', 3, 2),
(25, 5, '2023-09-20', '14:00:00', '16:00:00', 4, 2),
(26, 1, '2023-09-20', '09:00:00', '11:00:00', 1, 1),
(27, 2, '2023-09-20', '18:00:00', '20:30:00', 1, 1),
(29, 4, '2023-09-21', '18:00:00', '20:30:00', 1, 2),
(30, 3, '2023-09-21', '14:00:00', '17:00:00', 3, 2),
(31, 5, '2023-09-22', '14:00:00', '16:00:00', 4, 2),
(32, 1, '2023-09-25', '09:00:00', '11:00:00', 1, 1),
(33, 2, '2023-09-25', '18:00:00', '20:30:00', 1, 1),
(34, 4, '2023-09-26', '18:00:00', '20:30:00', 1, 2),
(35, 3, '2023-09-26', '14:00:00', '17:00:00', 3, 2),
(36, 5, '2023-09-22', '14:00:00', '16:00:00', 4, 2),
(37, 1, '2023-09-27', '09:00:00', '11:00:00', 1, 1),
(38, 2, '2023-09-27', '18:00:00', '20:30:00', 1, 1),
(39, 4, '2023-09-28', '18:00:00', '20:30:00', 1, 2),
(40, 3, '2023-09-28', '14:00:00', '17:00:00', 3, 2),
(41, 5, '2023-09-29', '14:00:00', '16:00:00', 4, 2);

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

--
-- Table structure for table `type`
--

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

--
-- Dumping data for table `type`
--

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

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

--
-- Structure for view `classdata`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`malingue`@`localhost` SQL SECURITY DEFINER VIEW `classdata`  AS SELECT `SSchedule`.`end` AS `end`, `SSchedule`.`start` AS `start`, `SSchedule`.`date` AS `date`, `classname`.`classnames` AS `classnames`, `classroom`.`classroom` AS `classroom`, `type`.`type` AS `type` FROM (((`SSchedule` join `classname`) join `type`) join `classroom`) WHERE ((`SSchedule`.`classname` = `classname`.`classname_id`) AND (`SSchedule`.`classroom` = `classroom`.`classroomid`) AND (`SSchedule`.`type` = `type`.`type_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `classname`
--
ALTER TABLE `classname`
  ADD PRIMARY KEY (`classname_id`);

--
-- Indexes for table `classroom`
--
ALTER TABLE `classroom`
  ADD PRIMARY KEY (`classroomid`);

--
-- Indexes for table `SSchedule`
--
ALTER TABLE `SSchedule`
  ADD PRIMARY KEY (`schedule_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `SSchedule`
--
ALTER TABLE `SSchedule`
  MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=42;

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

