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

START TRANSACTION;



--
-- Database: `onajobi_2023Schedule`
--

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

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

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

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

INSERT INTO `courses` (`course`, `courseID`) VALUES
('WRIT 340', 3),
('ACAD 276', 4),
('ACAD 499', 5),
('ITP 116', 6),
('ACAD 324', 7);

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

--
-- Table structure for table `lectureTypes`
--

CREATE TABLE `lectureTypes` (
  `lectureID` int(11) NOT NULL,
  `lectureType` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `lectureTypes`
--

INSERT INTO `lectureTypes` (`lectureID`, `lectureType`) VALUES
(1, 'lecture/lab'),
(2, 'lecture');

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

--
-- Table structure for table `location`
--

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

--
-- Dumping data for table `location`
--

INSERT INTO `location` (`locationID`, `building`) VALUES
(1, 'GFS111'),
(3, 'IYH111'),
(4, 'IYH210'),
(5, 'IYH212'),
(6, 'THH116');

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

--
-- Table structure for table `schedule`
--

CREATE TABLE `schedule` (
  `startTime` time NOT NULL,
  `endTime` time NOT NULL,
  `date` date NOT NULL,
  `courseID` varchar(25) NOT NULL,
  `scheduleID` int(11) NOT NULL,
  `locationID` int(11) NOT NULL,
  `lectureID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `schedule`
--

INSERT INTO `schedule` (`startTime`, `endTime`, `date`, `courseID`, `scheduleID`, `locationID`, `lectureID`) VALUES
('08:00:00', '09:00:00', '2023-08-21', '3', 1, 1, 1),
('08:00:00', '09:00:00', '2023-08-22', '3', 2, 1, 1),
('08:00:00', '09:00:00', '2023-08-29', '3', 4, 1, 1),
('08:00:00', '09:00:00', '2023-08-31', '3', 5, 1, 1),
('08:00:00', '09:00:00', '2023-09-05', '3', 6, 1, 1),
('08:00:00', '09:00:00', '2023-09-08', '3', 7, 1, 1),
('08:00:00', '09:00:00', '2023-09-12', '3', 8, 1, 1),
('11:00:00', '12:50:00', '2023-08-22', '5', 23, 3, 1),
('11:00:00', '12:50:00', '2023-08-24', '5', 24, 3, 1),
('11:00:00', '12:50:00', '2023-08-29', '5', 25, 3, 1),
('11:00:00', '12:50:00', '2023-08-31', '5', 26, 3, 1),
('11:00:00', '12:50:00', '2023-09-05', '5', 27, 3, 1),
('11:00:00', '12:50:00', '2023-08-07', '5', 28, 3, 1),
('11:00:00', '12:50:00', '2023-09-12', '5', 29, 3, 1),
('11:00:00', '12:50:00', '2023-09-14', '5', 30, 3, 1),
('14:00:00', '16:00:00', '2023-08-22', '4', 31, 4, 1),
('14:00:00', '16:00:00', '2023-08-24', '4', 32, 4, 1),
('14:00:00', '16:00:00', '2023-08-29', '4', 33, 4, 1),
('14:00:00', '16:00:00', '2023-08-31', '4', 34, 4, 1),
('14:00:00', '16:00:00', '2023-09-05', '4', 35, 4, 1),
('14:00:00', '16:00:00', '2023-09-07', '4', 36, 4, 1),
('14:00:00', '16:00:00', '2023-09-12', '4', 37, 4, 1),
('09:00:00', '11:50:00', '2023-08-21', '7', 38, 5, 1),
('09:00:00', '11:50:00', '2023-08-23', '7', 39, 5, 1),
('09:00:00', '11:50:00', '2023-08-28', '7', 40, 5, 1),
('09:00:00', '11:50:00', '2023-08-30', '7', 41, 5, 1),
('09:00:00', '11:50:00', '2023-09-06', '7', 42, 5, 1),
('09:00:00', '11:50:00', '2023-09-11', '7', 43, 5, 1),
('09:00:00', '11:50:00', '2023-09-13', '7', 44, 5, 1),
('15:00:00', '16:00:00', '2023-08-21', '6', 45, 6, 2),
('15:00:00', '16:00:00', '2023-08-23', '6', 46, 6, 2),
('15:00:00', '16:00:00', '2023-08-28', '6', 47, 6, 2),
('15:00:00', '16:00:00', '2023-08-30', '6', 48, 6, 2),
('15:00:00', '16:00:00', '2023-09-06', '6', 49, 6, 2),
('15:00:00', '16:00:00', '2023-09-11', '6', 50, 6, 2),
('15:00:00', '16:00:00', '2023-09-13', '6', 51, 6, 2);

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

--
-- Stand-in structure for view `scheduleView`
-- (See below for the actual view)
--
CREATE TABLE `scheduleView` (
`course` varchar(8)
,`building` varchar(8)
,`lectureType` varchar(30)
,`startTime` time
,`endTime` time
,`date` date
);

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

--
-- Structure for view `scheduleView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`onajobi`@`localhost` SQL SECURITY DEFINER VIEW `scheduleView`  AS SELECT `courses`.`course` AS `course`, `location`.`building` AS `building`, `lectureTypes`.`lectureType` AS `lectureType`, `schedule`.`startTime` AS `startTime`, `schedule`.`endTime` AS `endTime`, `schedule`.`date` AS `date` FROM (((`courses` join `location`) join `lectureTypes`) join `schedule`) WHERE ((`courses`.`courseID` = `schedule`.`courseID`) AND (`location`.`locationID` = `schedule`.`locationID`) AND (`lectureTypes`.`lectureID` = `schedule`.`lectureID`)) ;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `lectureTypes`
--
ALTER TABLE `lectureTypes`
  ADD PRIMARY KEY (`lectureID`);

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

--
-- Indexes for table `schedule`
--
ALTER TABLE `schedule`
  ADD PRIMARY KEY (`scheduleID`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `lectureTypes`
--
ALTER TABLE `lectureTypes`
  MODIFY `lectureID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

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

--
-- AUTO_INCREMENT for table `schedule`
--
ALTER TABLE `schedule`
  MODIFY `scheduleID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=52;
COMMIT;

