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

START TRANSACTION;



--
-- Database: `aryatand__schedule`
--

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

--
-- Table structure for table `classNames`
--

CREATE TABLE `classNames` (
  `classNameID` int(11) NOT NULL,
  `className` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classNames`
--

INSERT INTO `classNames` (`classNameID`, `className`) VALUES
(1, 'LING210: Introduction to Linguistics'),
(2, 'AMST252: Black Social Movements in the U.S.'),
(3, 'PSYC304: Sensation and Perception'),
(4, 'ACAD276: Dev II');

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

--
-- Table structure for table `classTypes`
--

CREATE TABLE `classTypes` (
  `classTypeID` int(11) NOT NULL,
  `classType` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classTypes`
--

INSERT INTO `classTypes` (`classTypeID`, `classType`) VALUES
(1, 'LECTURE'),
(2, 'DISCUSSION'),
(3, 'LECTURE/LAB');

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

--
-- Stand-in structure for view `classView`
-- (See below for the actual view)
--
CREATE TABLE `classView` (
`classID` int(11)
,`date` date
,`startTime` time
,`endTime` time
,`className` varchar(50)
,`classType` varchar(20)
,`location` varchar(15)
);

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

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

CREATE TABLE `class_schedule` (
  `classID` int(11) NOT NULL,
  `date` date NOT NULL,
  `startTime` time NOT NULL,
  `endTime` time NOT NULL,
  `classNameID` int(11) NOT NULL,
  `classTypeID` int(11) NOT NULL,
  `locationID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `class_schedule` (`classID`, `date`, `startTime`, `endTime`, `classNameID`, `classTypeID`, `locationID`) VALUES
(1, '2023-09-11', '10:00:00', '11:20:00', 1, 1, 1),
(2, '2023-09-11', '12:00:00', '13:50:00', 2, 1, 2),
(3, '2023-09-12', '11:00:00', '11:50:00', 2, 2, 3),
(4, '2023-09-12', '12:00:00', '13:50:00', 3, 3, 4),
(5, '2023-09-12', '14:00:00', '16:50:00', 4, 1, 5),
(6, '2023-09-13', '10:00:00', '11:20:00', 1, 1, 1),
(7, '2023-09-13', '12:00:00', '13:50:00', 2, 1, 2),
(8, '2023-09-14', '12:00:00', '13:50:00', 3, 3, 4),
(9, '2023-09-14', '14:00:00', '16:50:00', 4, 1, 5),
(10, '2023-09-15', '11:00:00', '12:20:00', 1, 2, 6),
(11, '2023-09-18', '10:00:00', '11:20:00', 1, 1, 1),
(12, '2023-09-18', '12:00:00', '13:50:00', 2, 1, 2),
(13, '2023-09-19', '11:00:00', '11:50:00', 2, 2, 3),
(14, '2023-09-19', '12:00:00', '13:50:00', 3, 3, 4),
(15, '2023-09-19', '14:00:00', '16:50:00', 4, 1, 5),
(16, '2023-09-20', '10:00:00', '11:20:00', 1, 1, 1),
(17, '2023-09-20', '12:00:00', '13:50:00', 2, 1, 2),
(18, '2023-09-21', '12:00:00', '13:50:00', 3, 3, 4),
(19, '2023-09-21', '14:00:00', '16:50:00', 4, 1, 5),
(20, '2023-09-22', '11:00:00', '12:20:00', 1, 2, 6),
(21, '2023-09-25', '10:00:00', '11:20:00', 1, 1, 1),
(22, '2023-09-25', '12:00:00', '13:50:00', 2, 1, 2),
(23, '2023-09-26', '11:00:00', '11:50:00', 2, 2, 3),
(24, '2023-09-26', '12:00:00', '13:50:00', 3, 3, 4),
(25, '2023-09-26', '14:00:00', '16:50:00', 4, 1, 5),
(26, '2023-09-27', '10:00:00', '11:20:00', 1, 1, 1),
(27, '2023-09-27', '12:00:00', '13:50:00', 2, 1, 2),
(28, '2023-09-28', '12:00:00', '13:50:00', 3, 3, 4),
(29, '2023-09-28', '14:00:00', '16:50:00', 4, 1, 5),
(30, '2023-09-29', '11:00:00', '12:20:00', 1, 2, 6),
(31, '2023-10-02', '10:00:00', '11:20:00', 1, 1, 1),
(32, '2023-10-02', '12:00:00', '13:50:00', 2, 1, 2),
(33, '2023-10-03', '11:00:00', '11:50:00', 2, 2, 3),
(34, '2023-10-03', '12:00:00', '13:50:00', 3, 3, 4),
(35, '2023-10-03', '14:00:00', '16:50:00', 4, 1, 5),
(36, '2023-10-04', '10:00:00', '11:20:00', 1, 1, 1),
(37, '2023-10-04', '12:00:00', '13:50:00', 2, 1, 2),
(38, '2023-10-05', '12:00:00', '13:50:00', 3, 3, 4),
(39, '2023-10-05', '14:00:00', '16:50:00', 4, 1, 5),
(40, '2023-10-06', '11:00:00', '12:20:00', 1, 2, 6);

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

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

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

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

INSERT INTO `locations` (`locationID`, `location`) VALUES
(1, 'ONLINE'),
(2, 'THH202'),
(3, 'GFS229'),
(4, 'VPD116'),
(5, 'IYH210/211'),
(6, 'THH119');

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

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

CREATE ALGORITHM=UNDEFINED DEFINER=`aryatand`@`%` SQL SECURITY DEFINER VIEW `classView`  AS SELECT `class_schedule`.`classID` AS `classID`, `class_schedule`.`date` AS `date`, `class_schedule`.`startTime` AS `startTime`, `class_schedule`.`endTime` AS `endTime`, `classNames`.`className` AS `className`, `classTypes`.`classType` AS `classType`, `locations`.`location` AS `location` FROM (((`class_schedule` join `classNames`) join `classTypes`) join `locations`) WHERE ((`class_schedule`.`classNameID` = `classNames`.`classNameID`) AND (`class_schedule`.`classTypeID` = `classTypes`.`classTypeID`) AND (`class_schedule`.`locationID` = `locations`.`locationID`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `classNames`
--
ALTER TABLE `classNames`
  ADD PRIMARY KEY (`classNameID`);

--
-- Indexes for table `classTypes`
--
ALTER TABLE `classTypes`
  ADD PRIMARY KEY (`classTypeID`);

--
-- Indexes for table `class_schedule`
--
ALTER TABLE `class_schedule`
  ADD PRIMARY KEY (`classID`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
  MODIFY `locationID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

